Блог о системном администрировании Сисадмин - это не профессия, это призвание.

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

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

Исходные данные: Debian 9 Stretch (amd64)
Задача: Установить PostgreSQL 10.x

1. Предварительная настройка сервера:

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

locale -a | grep ru

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

dpkg-reconfigure locales

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

2. Начинаем установку PostgreSQL 10:

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

Краткая справка по местоположению основных файлов PostgreSQL 10:
Местоположение баз данных:
/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

3. Тюнинг настроек PostgreSQL:

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

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

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

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

listen_addresses = 'localhost'

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

listen_addresses = 'localhost,192.168.100.1'

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

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

Смотрим на параметр shared_buffers
Этот параметр определяет, сколько памяти будет выделяться PostgreSQL для кеширования данных.
В стандартной поставке значение этого параметра 128 МБ, то есть по сути мизерное — для обеспечения совместимости. В практических условиях это значение следует установить в 15..25% от всей доступной оперативной памяти сервера. Учтите, слово всей доступной, то есть учитывайте память которую занимают текущие процессы и могут занять в случае роста потребления, к примеру у нас 8 ГБ ОЗУ и стоит MySQL, который в текущем состоянии занять 1 ГБ ОЗУ, а при росте количества соединений исходя из настроек может занять все 6 ГБ ОЗУ, тогда для PostgreSQL остается не так много памяти и shared_buffers никак нельзя поставить 2 ГБ. Если у Вас большие активные порции базы данных, сложные запросы, большое число одновременных соединений, длительные транзакции, Вам доступен большой объем ОЗУ или большее количество процессоров, то можно увеличивать значение shared_buffers и смотреть результат, чтобы не привести к «деградации» (падению) производительности. Выделив слишком много памяти для shared_buffers, мы можем получить ухудшение производительности, поскольку PostgreSQL также использует кэш операционной системы (увеличение данного параметра более 40% оперативной памяти может давать «нулевой» прирост производительности).

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

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

Параметр work_mem
Важный параметр для запросов, использующих всевозможные сложные выборки и сортировки. Увеличение его
позволяет выполнять эти операции в оперативной памяти, что гораздо более эффективно, чем на диске (еще бы).
Если объём памяти недостаточен для сортировки некоторого результата, то серверный процесс будет использовать временные файлы. Если же объём памяти слишком велик, то это может привести к своппингу.
Будьте внимательны! Это не разделяемая память, work_mem выделяется отдельно на каждую операцию (от одного до нескольких раз за один запрос). Следовательно, если у Вас 10 активных клиентов и каждый выполняет 1 сложный запрос, то значение в 10 МБ для этого параметра скушает 100 МБ оперативной памяти.
Этот параметр стоит увеличивать, если у Вас большое количество памяти в распоряжении. Чем больше max_connections тем меньше должен быть work_mem. В качестве начального значения для параметра можете взять 2–4% доступной памяти. Для веб-приложений обычно устанавливают низкие значения work_mem, так как запросов обычно много, но они простые, обычно хватает от 512 до 2048 КБ. С другой стороны, приложения для поддержки принятия решений с сотнями строк в каждом запросе и десятками миллионов столбцов в таблицах фактов часто требуют work_mem порядка 500 Мб. Для баз данных, которые используются и так, и так, этот параметр можно устанавливать для каждого запроса индивидуально, используя настройки сессии. Например, при памяти 1–4 ГБ рекомендуется устанавливать 32–128 МБ.

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

Параметр maintenance_work_mem
Задаёт максимальный объём памяти для операций обслуживания БД, в частности VACUUM, CREATE INDEX и ALTER TABLE ADD FOREIGN KEY. По умолчанию его значение — 64 мегабайта (64MB). Так как в один момент времени в сеансе может выполняться только одна такая операция, и обычно они не запускаются параллельно, это значение вполне может быть гораздо больше work_mem. Увеличение этого значения может привести к ускорению операций очистки и восстановления БД из копии, но слишком большие значения приведут к использованию свопа.
Учтите, что когда выполняется автоочистка, этот объём может быть выделен autovacuum_max_workers раз, поэтому не стоит устанавливать значение по умолчанию слишком большим. Возможно, будет лучше управлять объёмом памяти для автоочистки отдельно, изменяя autovacuum_work_mem.
Чтобы операции выполнялись максимально быстро, нужно устанавливать этот параметр тем выше, чем больше размер таблиц в вашей БД. Неплохо бы устанавливать его значение от 50 до 75% размера вашей самой большой таблицы или индекса или, если точно определить невозможно, от 32 до 256 МБ. Например, при памяти 1–4 ГБ рекомендуется устанавливать 128–512 МБ.
Временное увеличение maintenance_work_mem рекомендуют для ускорения загрузки больших объёмов данных в БД. Это приведёт к увеличению быстродействия CREATE INDEX и ALTER TABLE ADD FOREIGN KEY. На скорость самой команды COPY это не повлияет, так что этот совет будет полезен, только если вы применяете какой-либо из двух вышеописанных приёмов.

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

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

max_connections = 10
shared_buffers = 256MB
effective_cache_size = 768MB
maintenance_work_mem = 64MB
checkpoint_completion_target = 0.7
wal_buffers = 7864kB
default_statistics_target = 100
random_page_cost = 4
effective_io_concurrency = 2
work_mem = 26214kB
min_wal_size = 1GB
max_wal_size = 2GB

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

Теперь разрешим подключение из локальной сети с любых хостов и к любым БД, для этого в конец файла /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 локального сервера и из нашей локальной сети.

На этом все, до скорых встреч.

Дополнительные статьи:
Начало работы с PostgreSQL 10
Полное удаление PostgreSQL 10 на Debian 9
Как удалить все таблицы из БД PostgreSQL и MySQL?

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

Михаил Григорьев

Профессионально занимаюсь системным администрированием Linux -серверов и баз данных (MySQL, PostgreSQL) на протяжении последних 24 лет.