Sokolov blog

Изменение уровня дебага в консоли:
SET client_min_messages TO DEBUG;

http://www.sai.msu.su/~megera/postgres/talks/fts_pgsql_intro.htmll

Для увеличения максимального к-ва коннектов, осбслуживаемых постгресом:

sysctl.conf

kern.ipc.shmmax=2147483648
kern.ipc.shmall=2097152

loader.conf

kern.ipc.semmni=256
kern.ipc.semmns=32000
kern.ipc.semmnu=256

postgresql.conf

max_connections = 250

Хорошие заметки по postgres.

Массивы:

Преобразование массивов:
Оригинал тут

select client_id, array_agg(order_id) from simple_table group by client_id;
select * from explode_array(array[1,2,3,4]);

Парсинг массивов postgres в php:
http://dklab.ru/lib/DB_Pgsql_Type/

Служебная информация:

Описание схемы:
http://www.postgresql.org/docs/8.4/interactive/information-schema.html

Выбор информации обо всех таблицах:
SELECT c.table_schema, c.table_name,
array_agg(CAST(c.column_name AS text)) as col_names,
array_agg(DISTINCT CAST(c.data_type AS text)) As dat_types
FROM information_schema.columns AS c
GROUP BY c.table_schema, c.table_name;


Либа для автоматических тестов PGUNIT:
http://dklab.ru/lib/dklab_pgunit/

Бэкапы

Хорошая статья про ON-LINE backup http://www.opennet.ru/base/sys/postgresql_online_backup.txt.html
Для того, чтобы сделать простой бэкап в формате SQL:
pg_dump --inserts -U pavel -W database_name > /home/pavel/backup.sql

Преобразование строки в число:

Исходный код

CREATE OR REPLACE FUNCTION pc_chartoint(chartoconvert character varying)
RETURNS integer AS
$BODY$
SELECT CASE WHEN trim($1) SIMILAR TO '[0-9]+'
THEN CAST(trim($1) AS integer)
ELSE NULL END;
$BODY$
LANGUAGE 'sql' IMMUTABLE STRICT;

Установка

pkg_add -r postgresql90-server
echo postgresql_enable="YES" > /etc/rc.conf
/usr/local/etc/rc.d/postgresql initdb
#теперь устанавливаем pl/perl
cd /usr/ports/databases/p5-postgresql-plperl
make install clean
#стартуем
/usr/local/etc/rc.d/postgresql start
# создание plperl для базы:
# sudo -u pgsql createlang plperl ИМЯ_БАЗЫ

# Устанавливаем модули для perl
cd /usr/ports/databases/p5-DBD-Pg
make install clean

#Устанавливаем доп пакеты (для vacuumlo)
cd /usr/ports/databases/postgresql90-contrib
make install clean

Настройка бэкапа

Ежедневный скрипт, выполняющий backup и vacuum находиться здесь: /usr/local/etc/periodic/daily/502.pgsql
echo daily_pgsql_backup_enable=YES >> /etc/periodic.conf
echo daily_pgsql_backupdir=/usr/backup/postgres >> /etc/periodic.conf
echo daily_pgsql_vacuum_enable=YES >> /etc/periodic.conf


Создание юзера и базы:
sudo -u pgsql createuser -s -W pavel
sudo -u pgsql createdb test

UTF8:

To set limits, environment stuff like locale and collation and other
things, you can set up a class in /etc/login.conf before initializing
the database. Add something similar to this to /etc/login.conf:
---
postgres:\
:lang=en_US.UTF-8:\
:setenv=LC_COLLATE=C:\
:tc=default:
---
and run `cap_mkdb /etc/login.conf'.
Then add 'postgresql_class="postgres"' to /etc/rc.conf
## ОБЯЗАТЕЛЬНО утсановить класс postgres для юзера pgsql
pgsql:*:70:70:postgres:0:0:PostgreSQL Daemon:/usr/local/pgsql:/bin/sh

postgresql.conf:

custom_variable_classes = 'plperl'
plperl.use_strict = true
max_connections = 200
shared_buffers = 500MB

UPGRADE

Хорошая статья про апгрейд

Разное

Тюнинг:
Отличная статья по тюнингу http://postgresmen.ru/articles/view/38

Тема по руссификации: http://www.sql.ru/forum/actualthread.aspx?tid=677731
Откуда можно коннектиться
host all all a.b.c.d/24 trust (/usr/home/pgsql/data/pg_hba.conf)
На каких IP слушать
listen_addresses='*' (/usr/local/pgsql/data/postgresql.conf)

В apache должен быть обязательно прописан ServerName, иначе postgres как модуль php будет ронять apache в core dump

Установка и настройка Asterisk 1.6 + PostgreSQL на Debian » Все для системного администратора. Софт. Мануалы..