Posts Tagged ‘ PostgreSQL

Asterisk: импорт из Master.csv в Postgresql

postgres=# CREATE USER ast_user with password 'xxxxxxx';
CREATE ROLE
postgres=# CREATE DATABASE ast_db with owner ast_user;
CREATE DATABASE
postgres=#exit
 
psql ast_db ast_user
ast_db=> CREATE TABLE cdr (
calldate TIMESTAMP WITH TIME zone DEFAULT now() NOT NULL,
clid CHARACTER VARYING(80) DEFAULT '' NOT NULL,
src CHARACTER VARYING(80) DEFAULT '' NOT NULL,
dst CHARACTER VARYING(80) DEFAULT '' NOT NULL,
dcontext CHARACTER VARYING(80) DEFAULT '' NOT NULL,
channel CHARACTER VARYING(80) DEFAULT '' NOT NULL,
dstchannel CHARACTER VARYING(80) DEFAULT '' NOT NULL,
lastapp CHARACTER VARYING(80) DEFAULT '' NOT NULL,
lastdata CHARACTER VARYING(80) DEFAULT '' NOT NULL,
duration BIGINT DEFAULT 0::BIGINT NOT NULL,
billsec BIGINT DEFAULT 0::BIGINT NOT NULL,
disposition CHARACTER VARYING(45) DEFAULT '' NOT NULL,
amaflags BIGINT DEFAULT 0::BIGINT NOT NULL,
accountcode CHARACTER VARYING(20) DEFAULT '' NOT NULL,
uniqueid CHARACTER VARYING(32) DEFAULT '' NOT NULL,
userfield CHARACTER VARYING(255) DEFAULT '' NOT NULL
);
CREATE TABLE

Устанавливаем поддержку постгреса в Perl:

perl -e shell -MCPAN
install DBI::DBD
install DBD::Pg

Создаем скрипт для переноса даных:
Read more

Подборка SQL запросов

Допустим у вас есть номер телефона рекламного отдела и PR-манагеры пустили какую-то рекламу в сентябре.
Задача: найти всех клиентов которые позвонили в сентябре первый раз в жизни, но при этом им, что-то не понравилось и они не звонили после сентября.
Инфа нужна, чтоб промыть им мозги… 🙂
Вот, что у меня получилось сходу:

SELECT c.*,d.*
FROM
(SELECT a.callerid AS callerid1,a.count AS count1 FROM
(SELECT callerid,dst,COUNT(*) FROM calls WHERE dt BETWEEN '2010-09-01 00:00:00' AND '2010-10-01 00:00:00' AND dst='5555555' GROUP BY callerid,dst) AS a 
 
LEFT OUTER JOIN
(SELECT callerid,dst,operator FROM calls WHERE dt BETWEEN '2009-02-01 00:00:00' AND '2010-09-01 00:00:00' AND dst='5555555') AS b
 
ON a.callerid=b.callerid
WHERE b.dst IS NULL) AS c
 
LEFT OUTER JOIN
(SELECT callerid AS callerid2,dst,COUNT(*) AS count2 FROM calls WHERE dt BETWEEN '2010-10-01 00:00:00' AND '2010-11-01 00:00:00' AND dst='5555555' GROUP BY callerid,dst) AS d
 
ON c.callerid1=d.callerid2
WHERE d.dst IS NULL

Запрос не сильно легкий, требует оптимизации, но результат дает 🙂

Работа с Postgresql: настройка, масштабирование

Книга Работа с Postgresql: настройка, масштабирование является справочным пособием по настройке и масштабированию Postgresql. В книге иследуются вопросы по настройки производительности Postgresql, репликации и кластеризации. Изобилие реальных примеров позволит как начинающим, так и опытным разработчикам быстро разобратся с особенностями масштабирования Postgresql для своих приложений.
http://postgresql.leopard.in.ua/

ссылка на книгу если с сайта не грузится

postgresql

Миграция Postgres с 8.4 на 9.0

Решились потренироваться делать репликацию Master-Slave, которая реализована в версии 9.0
Значит так-с…
1. Обновляем Portage

emerge --sync

2. Ставим новую БД

LINGUAS="ru" ACCEPT_KEYWORDS="~amd64" emerge -av  /usr/portage/dev-db/postgresql-server/postgresql-server-9.0.1.ebuild

3. Конфигурим

emerge --config =dev-db/postgresql-server-9.0.1

4. правим конфиг и запускаем на порту 6543, не выключая старый постгресс
5. производим миграцию БД

pg_dumpall -p 5432 | psql -d postgres -p 6543

6. стартуем новую БД на порту 5432 предварительно выключив старую

После чего устанавливаем Postgres на втором хосте, который будет использоваться как Slave

LINGUAS="ru" ACCEPT_KEYWORDS="~amd64" emerge -av  /usr/portage/dev-db/postgresql-server/postgresql-server-9.0.1.ebuild

Вносим изменения в Master, чтобы слэйв мог к нему подключится:

$ $EDITOR postgresql.conf
listen_addresses = '10.0.xx.yy'
 
$ $EDITOR pg_hba.conf
# The standby server must have superuser access privileges.
host  replication  postgres  10.0.xx.zz/22  trust

Read more

Как через SELECT запрос в PostgreSQL посмотреть структуру таблицы

SELECT a.attname, format_type(a.atttypid, a.atttypmod), a.attnotnull, a.atthasdef, a.attnum 
  FROM pg_class c, pg_attribute a 
  WHERE c.relname ='имя таблицы' AND a.attnum > 0 AND a.attrelid = c.oid ORDER BY a.attnum;

15 команд для управления PostgreSQL

1. Как изменить root пароль в PostgreSQL?

$ /usr/local/pgsql/bin/psql postgres postgres
Password: (oldpassword)
ALTER USER postgres WITH PASSWORD ‘tmppassword’;
$ /usr/local/pgsql/bin/psql postgres postgres
Password: (tmppassword)

Read more

how to login to postgres console

sudo sh
su – postgres
#psql database username
psql bitmountain postgres