Установка и настройка PostgreSQL в CentOS

В данной статье мы проведем установку СУБД PostgreSQL 11 в Linux CentOS 7, выполним базовую настройку сервера и СУБД, рассмотрим основные параметры конфигурационного файла, а так же способы тюнинга производительности. PostgreSQL – популярная свободная объектно-реляционная система управления базами данных. Не смотря на то, что она не так распространена как MySQL/MariDB, она является самой профессиональной.

Сильные стороны PostgreSQL:

• Полное соответствие стандартам SQL;
• Высокая производительность за счет управления многовариантным параллелизмом (MVCC);
• Масштабируемость (широко используется в высоконагруженных средах);
• Поддержка множества языков программирования;
• Надёжные механизмы транзакций и репликации;
• Поддержка данных в формате JSON.

Установка PostgreSQL в CentOS/RHEL

Хотя PostgreSQL можно установить из базового репозитория CentOS, мы выполним установку репозитория от разработчиков, так как в нем всегда присутствует актуальная версия пакета.

Первым шагом устанавливаем репозиторий PosgreSQL (на данный момент он устанавливается следующим образом):

yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

В данном репозитории есть как самые новые версии PostgreSQL, так и более старые версии. Информация о репозитории выглядит следующим образом:

Установим последнюю доступную версию версию (PostrgeSQL 11) c помощью yum.

yum install postgresql11-server -y

В процессе установки устаналивается сам сервере PostgreSQL и необходимые библотеки:

Installing : libicu-50.2-3.el7.x86_64 1/4
Installing : postgresql11-libs-11.5-1PGDG.rhel7.x86_64 2/4
Installing : postgresql11-11.5-1PGDG.rhel7.x86_64 3/4
Installing : postgresql11-server-11.5-1PGDG.rhel7.x86_64 4/4

После установки пакетов, нужно произвести инициализацию базы данных:

/usr/pgsql-11/bin/postgresql-11-setup initdb

Так же сразу добавим сервер БД в автозагрузку и запустим его:

systemctl enable postgresql-11

systemctl start postgresql-11

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

[root@server ~]# systemctl status postgresql-11

● postgresql-11.service - PostgreSQL 11 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; enabled; vendor preset: disabled)
Active: active (running) since Wed 2019-09-18 13:01:56 +06; 26s ago
Docs: https://www.postgresql.org/docs/11/static/
 Process: 6614 ExecStartPre=/usr/pgsql-11/bin/postgresql-11-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 6619 (postmaster)
CGroup: /system.slice/postgresql-11.service
├─6619 /usr/pgsql-11/bin/postmaster -D /var/lib/pgsql/11/data/
├─6621 postgres: logger
├─6623 postgres: checkpointer
├─6624 postgres: background writer
├─6625 postgres: walwriter
├─6626 postgres: autovacuum launcher
├─6627 postgres: stats collector
└─6628 postgres: logical replication launcher
Sep 18 13:01:56 server.1.com systemd[1]: Starting PostgreSQL 11 database server...
Sep 18 13:01:56 server.1.com postmaster[6619]: 2019-09-18 13:01:56.399 +06 [6619] LOG: listening on IPv6 address "::1", port 5432
Sep 18 13:01:56 server.1.com postmaster[6619]: 2019-09-18 13:01:56.399 +06 [6619] LOG: listening on IPv4 address "127.0.0.1", port 5432
Sep 18 13:01:56 server.1.com postmaster[6619]: 2019-09-18 13:01:56.401 +06 [6619] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
Sep 18 13:01:56 server.1.com postmaster[6619]: 2019-09-18 13:01:56.409 +06 [6619] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
Sep 18 13:01:56 server.1.com postmaster[6619]: 2019-09-18 13:01:56.427 +06 [6619] LOG: redirecting log output to logging collector process
Sep 18 13:01:56 server.1.com postmaster[6619]: 2019-09-18 13:01:56.427 +06 [6619] HINT: Future log output will appear in directory "log".
Sep 18 13:01:56 server.1.com systemd[1]: Started PostgreSQL 11 database server.

Если вам нужен доступ к PostgreSQL снаружи, вам нужно открыть порт TCP/5432, в стандартном firewall в Centos 7:

# firewall-cmd --get-active-zones

public
interfaces: eth0

# firewall-cmd --zone=public --add-port=5432/tcp --permanent
# firewall-cmd --reload

Или через iptables:

#iptables-A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT

#service iptables restart

Если включен SELinux, выполните:

setsebool -P httpd_can_network_connect_db 1

Подключение к PostgreSQL, создание БД, пользователя

По умолчанию при установке PostgreSQL в системе есть один пользователь —postgres.

Я не рекомендую использовать его для работы с базами данных, лучше создавать пользователей для каждой БД отдельно.

Чтобы подключиться к серверу postgres нужно ввести команду:

[root@server /]# sudo -u postgres psql

psql (11.5)
Type "help" for help.

postgres=#

Открылась консоль PostgreSQL. Покажем несколько простых примеров управления PostgreSQL из консоли psql.

Т.к. любой пользователь системы может авторизоваться в postrgesql, сначала нужно изменить пароль пользователя postgres.

ALTER ROLE postgres WITH PASSWORD 'super_str0ng_pa$$word';

Сразу создадим новую базу данных, пользователя и дадим ему полные права на эту БД:

postgres=# CREATE DATABASE mydbtest;

postgres=# CREATE USER mydbuser WITH password '123456789';

postgres=# GRANT ALL PRIVILEGES ON DATABASE mydbtest TO mydbuser;

Подключиться к БД:

postgres=# c databasename

Вывести список таблиц:

postgres=# dt

Вывести список запросов к базе:

postgres=# select * from pg_stat_activity where datname='dbname'

Сбросить все подключения к базе:

postgres=# select pg_terminate_backend(pid) from pg_stat_activity where datname = 'dbname'

Информацию о текущей сессии можно получить так:

postgres=# conninfo

Для завершения работой с консолью psql, выполните:

postgres=# q

Как вы уже заметили, синтаксис не отличается от той же MariaDB или MySQL и поэтому особо останавливаться на однотипных командах мы не будем.

Отметим, что для более удобного управления базами PostgreSQL из веб-интерфейса рекомендуется использовать pgAdmin4 (написан на Python и Javascript/jQuery). Это аналог привычному многим веб разработчикам PhpMyAdmin.

Основные параметры конфигурационных файлов PostgreSQL

Файлы конфигурации postgresql находятся в директории /var/lib/pgsql/11/data:

• postgresql.conf — непосредственно сам файл конфигурации postgresql;
• pg_hba.conf — файл с настройками доступа. В данном файле, можно выставлять различные ограничения для пользователей, устанавливать политику подключения к БД;
• pg_ident.conf — этот файл используется при идентификации клиентов по протоколу ident.

Чтобы запретить локальным пользователям вход в postgres без авторизации, в файле pg_hba.conf укажите:

local all all md5
host all all 127.0.0.1/32 md5

Рассмотрим наиболее важные параметры в конфигурационном файле postgresql.conf:

• listen_addresses — указывает на каких IP адресах сервер будет принимать клиентские подключения. По умолчанию указано localhost, это означает, что возможно только локальное подключение. Чтобы случашать на всех IPv4 интерфейсах, укажите 0.0.0.0
• max_connections – как и в других СУБД, это максимальное количество одновременных подключения к серверу БД;
• temp_buffers – максимальный размер временных буферов;
• shared_buffers — объем разделяемой памяти используемый сервером баз данных. Обычно выставляется в размере 25% от памяти, установленной на сервере;
• effective_cache_size – параметр, который помогает планировщику postgres определить количество доступной памяти для кеширования на диск. Обычно параметр выставляется размером в 50-75% от всей ОЗУ на сервере;
• work_mem – объем памяти, который будет использоваться внутренними операциями сортировки СУБД — ORDER BY, DISTINCT и слияния;
• maintenance_work_mem – объем памяти, который будет использоваться внутренними операциями — VACUUM, CREATE INDEX и ALTER TABLE ADD FOREIGN KEY;
• fsync – если этот параметр включен, то СУБД будет дожидаться физической записи данных на жесткий диск. При включенном fsynс вам будет проще восстановить БД после системного или аппаратного сбоя. Естественно, что включение данного параметра значительно снижает производительность СУБД, но повышает надежность хранения. При отключении этого параметра стоит отключать и full_page_writes;
• max_stack_depth — максимальный размер стека (2 Мб по умолчанию);
• max_fsm_pages — с помощью данного параметра, можно управлять свободным дисковым пространством на сервере. К примеру, после удаления данных из таблицы, занимаемое ранее место не освобождается на диске, а помечается на карте свободного пространства с меткой «свободно» и далее используется для новых записей в таблице. Если на сервере активно ведется запись/удаление данных в таблицах, увеличение данного параметра, положительно повлияет на производительность;
• wal_buffers – объем из разделяемой памяти (shared_buffers), который используется для хранения данных WAL;
• wal_writer_delay – время между периодами записи WAL на диск;
• commit_delay — задержка между записью транзакции в буфер WAL и сбросом его на диск;
• synchronous_commit — параметр определяет, что результат об успешном завершении транзакции будет отправлен тогда, когда данные WAL физически запишутся на диск.

Резевное копирование и восстановление БД в PostgreSQL

Создать резервную копию в PostgreSQL БД можно несколькими способами. Рассмотрим самый простой вариант.

Для начала проверим, какие БД запущены на сервере:

postgres=# list

У нас имеются 4 базы данных, 3 из которых системные (postgres и template).

Ранее мы создавали БД с именем “mydbtest”, на ее примере и выполним резервное копирование.

Один из способов резервного копирования, это выполнение его с помощью утилиты pg_dump:

sudo -u postgres pg_dump mydbtest > /root/dupm.sql
— выполняем запрос от пользователя postgres, указываем нужную БД и путь до файла в который нужно сохранить дамп базы. Дамп базы может забрать ваша система резевного копирования, или в случае использования веб сервера, вы можете отправить его в ваше облачное хранилище.

Чтобы восстановить указанный дамп в нужную БД, можно воспользоваться утилитой psql:

sudo -u postgres psql mydbtest


Так же можно создать бэкап в специальном формате дампа и сжатом с применением gzip:

sudo -u postgres pg_dump -Fc mydbtest > /root/dumptest.sql

Восстанавливается такой дамп с помощью утилиты pg_restore:

sudo -u postgres pg_restore -d mydbtest /root/dumptest.sql

Более расширенные настройки можно посмотреть в справке по данным утилитам:

man psql
man pg_dump
man pg_restore

Оптимизация и тюниг PostgreSQL

В предыдущей статье о MariaDB, мы показывали, как можно привести практически к идеалу параметры конфигурационного файла my.cnf с помощью тюнеров. Для PostgreSQL существует, хотя правильнее сказать существовала такая утилита как PgTun, но к сожалению она уже давно не обновляется. В тоже время есть масса онлайн сервисов, с помощью которых вы можете настроить оптимальную конфигурацию для вашего PostgreSQL. Мне нравится сервис pgtune.leopard.in.ua.

Интерфейс очень прост. Вам нужно указать параметры вашего сервера (профиль, процессоры, память, тип дисков) и нажать кнопку “Generate”. В результате вам будет предложен вариант конфигурационного файла postgresql.conf с рекомендуемыми значениями основных параметров СУБД.

Например, для VPS SSD сервера с 2 Гб оперативной памятью, 2 CPU для запуска нескольких сайтов рекомендуются следующие настройки в postgresql.conf:

# DB Version: 11
# OS Type: linux
# DB Type: web
# Total Memory (RAM): 2 GB
# CPUs num: 2
# Connections num: 20
# Data Storage: ssd
max_connections = 20
shared_buffers = 512MB
effective_cache_size = 1536MB
maintenance_work_mem = 128MB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 26214kB
min_wal_size = 1GB
max_wal_size = 2GB
max_worker_processes = 2
max_parallel_workers_per_gather = 1
max_parallel_workers = 2

И это на самом деле не единственный ресурс, на момент написания статьи, были достпны аналогичные сервисы:

• Cybertec PostgreSQL Configurator
• PostgreSQL Configuration Tool

С помощью подобных сервисов, можно быстро настроить начальные параметры СУБД для вашего оборудования и выполняемых задач. В дальнейшем уже нужно опираться не только на ресурсы сервера, но и анализировать в целом работу БД, ее размер, количество коннектов и на основе этого, выполнять дальнейшую тонкую донастройку параметров PostgreSQL.