Записки Админа

Поскольку PostgreSQL использует кодировку UTF-8 по умолчанию, для избежания проблем в дальнейшем, последуем совету и для пользователя pgsql (в новых версиях PostgreSQL работает от имени пользователя pgsql, а не postgres) немного подкорректируем локаль. Для этого в внесем такой блок в файл /etc/login.conf:

pgsql:\
:lang=en_US.UTF-8:\
:setenv=LC_COLLATE=C:\
:tc=default:

Далее:

# cap_mkdb /etc/login.conf

Базы данных будем размещать в каталоге /var/db/pgsql. Соответственно создадим необходимый каталог и выставим права доступа:

# mkdir /var/db/pgsql
# chown pgsql:pgsql /var/db/pgsql

Вносим изменения в rc.conf, указывая каталог хранения данных. Ну и добавим запуск PostgreSQL при старте системы:

# echo ‘# PostgreSQL’ >> /etc/rc.conf # echo ‘postgresql_enable=»YES»‘ >> /etc/rc.conf # echo ‘postgresql_data=»/var/db/pgsql»‘ >> /etc/rc.conf # echo ‘postgresql_class=»postgres»‘ >> /etc/rc.conf

После этих манипуляций выполним инициализацию базы данных:

# /usr/local/etc/rc.d/postgresql initdb

Скрипт по ходу своей работы будет выводить сообщения на консоль, в конце должен написать:

Success. You can now start the database server using:

    /usr/local/bin/postgres -D /var/db/pgsql
or
    /usr/local/bin/pg_ctl -D /var/db/pgsql -l logfile start

После инициализации БД запускаем PostgreSQL сервер:
# sh /usr/local/etc/rc.d/postgresql start

 

Воспользуемся интерактивным терминалом для настройки прав доступа. Подключимся от имени пользователся pgsql. Пароль не нужен.

# psql -U pgsql template1

 

Советую ознакомиться со справкой по операторам SQL и командам psql. Теперь создадим тестового пользователя и базу данных, к которой он будет иметь доступ.

template1=# CREATE USER username;
CREATE ROLE

Проверим список ролей:

template1-# \du

А вот пароли для пользователя — вещь обязательная. Зададим пароль для созданного пользователя:

template1=# \password username

Также стоит установить пароль для пользователя pgsql. После этого включаем проверку пароля при подключении к PostgreSQL-серверу. Неоходимо внести изменения в файл /var/db/pgsql/pg_hba.conf. Метод подключения trust необходимо изменить на md5 (также полностью удаляем строку для IPv6). В результате имеем такой результат:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

Для того, чтобы изменения вступили в силу, необходимо перезапустить PostgreSQL-сервер:
# sh /usr/local/etc/rc.d/postgresql restart

Повторно подключаемся к интерактивной оболочке:

# psql -U pgsql template1
Пароль пользователя pgsql:

Теперь сервер при подключении запрашивает пароль. Ну и напоследок создадим базу данных и дадим на нее права пользователю username. Сделать это можно двумя способами.

Первый способ:

template1=# CREATE DATABASE userdb owner=username; CREATE DATABASE

Второй способ:

template1=# CREATE DATABASE userdb; CREATE DATABASE template1=# ALTER DATABASE userdb owner to username; ALTER DATABASE

Просмотрим список имеющихся баз данных:

template1=# \l

 

В принципе, все интуитивно понятно. Используйте в своей работе документацию и подсказки, и работа с сервером PostgreSQL не должна вызывать больших проблем.

Последний штрих — тюнинг PostgreSQL-сервера. Все основные настройки хранятся в файле /var/db/pgsql/postgresql.conf. Отметим опции, на которые стоит обратить внимание (перевод описания опций взят здесь):

• max_connections — максимальное количество одновременных подключений к серверу БД. Используйте этот параметр, чтобы не допустить запуска большого количества процессов сервера. Иначе есть вероятность, что серер БД исчерпает весь объем ОЗУ и будет активно использоваться подкачка, что в свою очередь повлечет за собой падение производительности.
• shared_buffers — объем разделяемой памяти, используемый сервером БД. По умолчанию — 32 Мб, но может быть меньше, если настройки вашего ядра не позволяют выделить такой объем памяти, определяется при инициализации кластера БД утилитой initdb. Большие значения этого параметра положительно сказываются на производительности сервера БД.
  Если у вас выделенный компьютер с ОЗУ 1 Гб и больше под сервер БД, то хорошим значением этой переменной будет 25% от объема памяти. При большой нагрузке даже большие значения этого параметра могут быть эффективными, но так как PostgreSQL полагается на кэш ОС, то выделение более 40% от объема памяти вряд ли имеет смысл. Для больших значений этого параметра требуется так же увеличить значение параметра checkpoint_segments.
  На системах с ОЗУ меньше 1 Гб правильнее будет использовать меньший объем памяти (чем 25%), чтобы не исчерпать весь объем оперативной памяти. На винде большие значения этого параметра могут оказаться эффективными. Вы можете добиться большей производительности сохраняя это значение маленьким и больше используя средства кэширования ОС. Хорошим диапазоном значений для Windows является 64-512 Мб.
• temp_buffers — максимальный размер временных буферов для каждой сессии. Эта память используется только локально в сессии для временных таблиц. По умолчанию — 8 МБ. Значение может быть изменено во время сессии, но только до первого использования этой памяти.
• max_prepared_transactions — максимальное количество «prepared» транзакций (смотрите описание SQL команды PREPARE TRANSACTION в документации). Чтобы отключить эту фишку, поставьте значение в 0.
• work_mem — определяет объем памяти, который будет использоваться внутренними операциями сортировки и хэш-таблицами прежде, чем переключиться на временные дисковые файлы. Учтите, что для сложных запросов несколько внутренних операций сортировки и работа с хэш-таблицами могут работать параллельно (одновременно). Кроме того, несколько сессий могут делать такие операции одновременно. В итоге необходимый объем памяти для этих операций может в несколько раз превышать значение параметра work_mem. Учтите это при выборе значения для этого параметра. Под внутренними операциями сортировки подразумевается — ORDER BY, DISTINCT и слияния.
• maintenance_work_mem — максимальный объем памяти, используемый для внутренних операций, таких как VACUUM, CREATE INDEX и ALTER TABLE ADD FOREIGN KEY. По умолчанию — 16 Мб. Эти команды выполняются только во во время сессии, так что можно выбирать большие значения для этого параметра, чем для параметра work_mem. Большие значения могут положительно сказаться на производительности vacuuming и скорости восстановления БД из дампа. Только учтите, что процесс autovacuum запускается autovacuum_max_workers раз, поэтому может потребоваться больше свободной памяти.
• max_stack_depth — максимальная глубина стека. Хорошим значением этого параметра является максимально разрешенная глубина стека в системе.
• max_fsm_pages — с помощью этого параметра можно управлять картой свободного пространства. Когда что-то удаляется из таблицы, то место занимаемое этим что-то не освобождается на диске, вместо этого занимаемое место просто помечается как «свободно» в карте свободного пространства. Потом это место используется для новых записей. Если на вашем сервере очень много удаляется/добавляется данных из/в таблицы, то большие значения этого параметра могут положительно сказаться на производительности.