Установка и первоначальная настройка PostgreSQL 10.0 на Debian 9

Буквально несколько дней назад разработчики PostgreSQL выпустили версию 10.0
В новой версии добавлено много новшеств и сделано немало улучшений, более детально о них можно почитать в официальном пресс-релизе.

Я же опишу ниже как установить и быстро настроить PostgreSQL 10.0 на Debian 9.1

Исходные данные: Debian 9.1 Stretch (amd64)

Предварительная настройка сервера:
Добавляем на сервер русскую локаль, для начала проверяем её отсутствие/присутствие командой

locale -a | grep ru

если в ответ ничего нет, то запускаем

dpkg-reconfigure locales

выбираем в списке локаль ru_RU.UTF-8
и жмем Yes
выбираем локаль по умолчанию en_US.UTF-8

Теперь начинаем установку PostgreSQL 10.0:

echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -sc)-pgdg main" > /etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
apt-get update
apt-get install postgresql-10 -y

Краткая справка по местоположению основных файлов Pg10:
Местоположение баз данных:
/var/lib/postgresql/10
Местоположение логов:
/var/log/postgresql/postgresql-10-main.log
Настройка ротации логов:
/etc/logrotate.d/postgresql-common
Основные файлы конфигурации:
/etc/postgresql/10/main/postgresql.conf
/etc/postgresql/10/main/pg_hba.conf

Первым делом меняем пароль пользователя postgres:

su - postgres
psql
postgres=# \password postgres
postgres=# \q

Теперь займемся небольшим тюнингом настроек PostgreSQL.

По умолчанию PostgreSQL принимает соединения только с локальных служб, т.к. слушает интерфейс localhost. Если Вы планируете подключения к серверу извне или из локальной сети, то Вам потребуется поменять параметр listen_addresses

Для Pg 10.0 открываем основной файл настроек

vi /etc/postgresql/10/main/postgresql.conf

и раскомментируем строку

listen_addresses = 'localhost'

исправим её на

listen_addresses = 'localhost,192.168.100.4'

таким образом мы укажем Pg слушать сетевые соединения на интерфейсе localhost и на нашем внутреннем интерфейсе локальной сети с IP адресом 192.168.100.4

Далее смотрим на параметр max_connections, который определяет максимальное количество одновременных соединений, которые будет обслуживать сервер Pg. В принципе, это число должно определяться исходя из требований к системе. Этот параметр в большей степени влияет на использование ресурсов. Если Вы только запустили БД, устанавливайте это значение небольшим (16…32), по умолчанию он установлен 100. Постепенно можно увеличивать max_connections (по мере необходимости — такой мерой будет получение ошибок от postgresql «too many clients»).
Учтите! На поддержку каждого активного клиента, postgresql тратит немалое количество ресурсов, и если Вам необходимо добиться производительности в несколько тысяч активных соединений, то стоит использовать менеджеры соединений, например: Pgpool.
Важно! Значение параметра max_wal_senders должно быть меньше max_connections, поэтому если Вы установили max_connections = 10, то max_wal_senders нужно поменять к примеру на 5

Смотрим на параметр shared_buffers
Этот параметр определяет, сколько памяти будет выделяться postgresql для кеширования данных.
В стандартной поставке значение этого параметра 128Mb, то есть по сути мизерное — для обеспечения совместимости. В практических условиях это значение следует установить в 15..25% от всей доступной оперативной памяти сервера. Учтите, слово всей доступной, то есть учитывайте память которую занимают текущие процессы и могут занять в случае роста потребления, к примеру у нас 8Gb ОЗУ и стоит MySQL, который в текущем состоянии занять 1Gb ОЗУ, а при росте количества соединений исходя из настроек может занять все 6Gb ОЗУ, тогда для Pg остается не так много памяти и shared_buffers никак нельзя поставить 2Gb.

Параметр effective_cache_size
Этот параметр помогает планировщику postgresql определить количество доступной памяти для дискового кеширования. На основе того, доступна память или нет, планировщик будет делать выбор между использованием индексов и использованием сканирования таблицы. Это значение следует устанавливать в 50%…75% всей доступной оперативной памяти, в зависимости от того, сколько памяти доступно для системного кеша. Еще раз — этот параметр не влияет на выделяемые ресурсы — это оценочная информация для планировщика.

Параметры min_wal_size, max_wal_size, checkpoint_timeout, checkpoint_completion_target, wal_buffers
Существует несколько параметров конфигурации, связанных с WAL, которые влияют на производительность базы данных. На эти и некоторые другие настройки стоит обратить внимание, если у Вас происходит немалое количество записей в БД (для высоконагруженных систем это нормальная ситуация).
Более подробно следует прочитать официальную документацию.

Параметр work_mem
Важный параметр для запросов, использующих всевозможные сложные выборки и сортировки. Увеличение его
позволяет выполнять эти операции в оперативной памяти, что гораздо более эффективно, чем на диске (еще бы).
Будьте внимательны! Этот параметр указывает, сколько памяти выделять на каждую подобную операцию! Следовательно, если у Вас 10 активных клиентов и каждый выполняет 1 сложный запрос, то значение в 10Мб для этого параметра скушает 100Мб оперативной памяти. Этот параметр стоит увеличивать, если у Вас большое количество памяти в распоряжении. Чем больше max_connections тем меньше должен быть work_mem.

Параметр synchronous_commit
Обратите особое внимание на этот параметр! Он включает/выключает синхронную запись в лог файлы после каждой транзакции. Это защищает от возможной потери данных. Но это накладывает ограничение на пропускную способность сервера.
Допустим, в Вашей системе не критична потенциально низкая возможность потери небольшого количества изменений при крахе системы. Но жизненно важно обеспечить в несколько раз большую производительность по количеству транзакций в секунду. В этом случае устанавливайте этот параметр в off (отключение синхронной записи).

Более детально о всех параметрах можно почитать в официальной документации.

Давайте настроим эти параметры исходя из
— объема свободного ОЗУ в 1Gb;
— максимального количества одновременных соединений 10;
— сервер будет выполнять задачи БД для Web-приложений;

max_connections = 10
max_wal_senders = 5
shared_buffers = 256MB
effective_cache_size = 768MB
work_mem = 25600kB
min_wal_size = 1GB
max_wal_size = 2GB
checkpoint_completion_target = 0.7
wal_buffers = 8000kB

Хочу заметить, что многие настройки Pg зависят не только от аппаратной конфигурации, но и от размера базы данных, числа клиентов и сложности запросов, так что оптимально настроить базу данных возможно только учитывая все параметры системы и приложения (например учитывать, SSD диски и влезает ли база в память).

Теперь разрешим подключение из локальной сети с любых хостов и к любым БД, для этого в конец файла /etc/postgresql/10/main/pg_hba.conf добавим:

# localnet
host    all         all         192.168.100.0/24          md5

Далее выходим из под пользователя postgres и рестартуем PostgreSQL:

service postgresql restart

Проверяем открытые порты

netstat -ltupn |grep postgre
tcp        0      0 192.168.100.4:5432      0.0.0.0:*               LISTEN      32658/postgres
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      32658/postgres

Отлично! Теперь мы можем подключиться к PostgreSQL c локального сервера и из нашей локальной сети.

На этом все, до скорых встреч. Если у Вас возникли вопросы или Вы хотите чтобы я помог Вам, то Вы всегда можете связаться со мной разными доступными способами.