Applications: PostgreSQL 9.5 и PgBouncer.
Задача: установка и настройка сервера СУБД PostgreSQL, предназначенного для обслуживающего годами накапливающихся производственных данных предприятия, добиваясь при этом минимально допустимого уровня отказоустойчивости.
Выделим три условных этапа реализации поставленной цели:
1. Сформируем системное окружение, оптимизировав по возможности подсистемы, могущие стать узким местом в дальнейшей эксплуатации СУБД.
2. На входе поставим мультиплексор запросов PgBouncer, позволяющий снизить нагрузку на СУБД, беря на себя задачи по открытию и закрытию соединений клиентских транзакций, что позволит серверу СУБД не страдать от наплывов запросов множественных, но коротких и однотипных.
3. Запустим вторичный сервер СУБД, который станет зеркальной "репликой" первичного, поддерживаемой посредством функционала "Streaming Replication (SR)". С "зеркала" данных появится возможность снимать произвольной глубины и охвата резервные копии данных, не блокируя при этом и не снижая производительности сервисов первичного сервера СУБД.
2. На входе поставим мультиплексор запросов PgBouncer, позволяющий снизить нагрузку на СУБД, беря на себя задачи по открытию и закрытию соединений клиентских транзакций, что позволит серверу СУБД не страдать от наплывов запросов множественных, но коротких и однотипных.
3. Запустим вторичный сервер СУБД, который станет зеркальной "репликой" первичного, поддерживаемой посредством функционала "Streaming Replication (SR)". С "зеркала" данных появится возможность снимать произвольной глубины и охвата резервные копии данных, не блокируя при этом и не снижая производительности сервисов первичного сервера СУБД.
Подготовка системного окружения.
Прежде всего, без некоторых утилит работать некомфортно - так что инсталлируем их:
# apt-get install aptitude sudo acl psmisc net-tools host curl screen htop iotop tree mc vim pwgen ntpdate dirmngr nfs-client arping
Перед тем, как приступать к развёртыванию СУБД, необходимо разметить файловые структуры для оптимального хранения данных. Прежде всего в распоряжении PostgreSQL должно быть не менее двух дисков. Если диск один, то дальше лучше не двигаться, ибо потом придётся переделывать.
На одном (первом), самом скоростном диске (SAS, SSD) - вероятно он же является несущим для операционной системы и всего прикладного программного обеспечения - мы разместим "базы данных". На второй диск (SATA, NFS, SMB) вынесем директории хранения журналов транзакций и архивных файлов.
В рамках задуманного разделения ресурсов в таблице монтирования файловых систем как минимум будет что-то подобное:
# vi /etc/fstab
....
# Main, Operating System Disk
/dev/mapper/vg0-lvroot / ext4 rw,noatime,nodiratime,errors=remount-ro 0 1
# Secondary Disk, helping PostgreSQL (WAL and archives)
/dev/mapper/vg1-lvdb /mnt/dsk1 ext4 rw,noatime,nodiratime 0 0
....
# Main, Operating System Disk
/dev/mapper/vg0-lvroot / ext4 rw,noatime,nodiratime,errors=remount-ro 0 1
# Secondary Disk, helping PostgreSQL (WAL and archives)
/dev/mapper/vg1-lvdb /mnt/dsk1 ext4 rw,noatime,nodiratime 0 0
....
В примере выше мы ещё и подкорректировали параметры монтирования файловых систем, на которых располагаются файлы СУБД, отключив фиксацию атрибута времени обращения к файлам (естественно, что СУБД дёргает их непрерывно, и незачем тратить на отметку этого факта ресурсы дискового контроллера).
Создаём точку монтирования и включаем файловую систему в работу:
# mkdir -p /mnt/dsk1
# mount /mnt/dsk1
# mount /mnt/dsk1
Создаём систему локального именования сетевых ресурсов.
Полагаю, что читающим это нет смысла объяснять, почему для обращения к сетевым ресурсам никогда не следует использовать IP-адреса, а всегда опираться на заранее анонсированные "символические (доменные) имена".
В "/etc/hosts" на всех задействованных в схеме серверах СУБД задаем привязку символических имён к IP-адресам таковых (учитывая то, что эти имена нужны исключительно внутри схемы обмена данными между серверами БД, нет смысла их публиковать в общей DNS):
....
10.20.30.41 db.local # Потенциально перемещаемый адрес указывающий на точку обслуживания клиентских запросов
10.20.30.41 db1.local # Сервер первичной (primary) СУБД
10.20.30.42 db2.local # Сервер вторичной (slave) СУБД
10.20.30.41 db.local # Потенциально перемещаемый адрес указывающий на точку обслуживания клиентских запросов
10.20.30.41 db1.local # Сервер первичной (primary) СУБД
10.20.30.42 db2.local # Сервер вторичной (slave) СУБД
Символическое имя "db.local", указывающее в нормальной ситуации на сервер "db1.local" используется для того, чтобы в случае выхода из строя первичного сервера для перемещения на вторичный клиентских запросов достаточно было бы скорректировать DNS-записи, сопоставив имя "db.local" со вторичным сервером "db2.local".
Установка СУБД PostgreSQL и распределение данных в файловой системе.
Несмотря на выход в свет 10-ой версии, в пакетных дистрибутивах стабильных версий Linux поставляется PostgreSQL версии 9.5 (кое где можно получить 9.6, но принципиальных отличий от предыдущей нет). Инсталлируем:
# aptitude install postgresql postgresql-contrib pgtop
В "Linux Debian/Ubuntu" файлы данных PostgreSQL по умолчанию располагаются в директории, в имени которой явно указана текущая версия сервера СУБД, что доставляет неудобство при обновлении до следующей минорной версии - придётся переносить файлы в новую директорию или жить с путаницей, когда программное обеспечение уже новое, а файлы данных располагаются в директории со старым именем - особенно сильно это затруднит автоматизацию процедур через скрипты. Я предпочитаю сразу же изменить место хранения данных сервера на менее зависимое от версионности ПО.
Останавливаем СУБД и перемещаем директорию с данными выше по иерархии дерева файловой системы:
# /etc/init.d/postgresql stop
# mv /var/lib/postgresql/9.5/main /var/lib/postgresql/main
# rm /var/lib/postgresql/9.5
# mv /var/lib/postgresql/9.5/main /var/lib/postgresql/main
# rm /var/lib/postgresql/9.5
В основном конфигурационном файле СУБД указываем новое месторасположение директории с данными:
# vi /etc/postgresql/9.5/main/postgresql.conf
....
data_directory = '/var/lib/postgresql/main'
....
data_directory = '/var/lib/postgresql/main'
....
Ещё перед тем, как запустить PostgreSQL в эксплуатацию, в обязательном порядке выносим месторасположение журналов транзакций (WAL) и архивов таковых на вспомогательный диск, чтобы множественные операции записи сопроводительных данных не конкурировали за ресурсы физического носителя поддерживающим основной рабочий процесс.
Создаём файловую структуру и выносим туда директорию хранения журналов WAL:
# mkdir -p /mnt/dsk1/postgresql/main
# mv /var/lib/postgresql/main/pg_xlog /mnt/dsk1/postgresql/main/
# ln -s /mnt/dsk1/postgresql/main/pg_xlog /var/lib/postgresql/main/pg_xlog
# mv /var/lib/postgresql/main/pg_xlog /mnt/dsk1/postgresql/main/
# ln -s /mnt/dsk1/postgresql/main/pg_xlog /var/lib/postgresql/main/pg_xlog
Создаём аналогичную файловую структуру для хранения архивов WAL:
# mkdir -p /mnt/dsk1/postgresql/archive
# ln -s /mnt/dsk1/postgresql/archive /var/lib/postgresql/archive
# ln -s /mnt/dsk1/postgresql/archive /var/lib/postgresql/archive
Передаём вспомогательное хранилище во владение пользователя, от имени которого запускается PostgreSQL:
# chown -R postgres:postgres /mnt/dsk1/postgresql
# chmod -R go-rwx /mnt/dsk1/postgresql
# chmod -R go-rwx /mnt/dsk1/postgresql
Символические ссылки в переопределении места хранения данных удобны тем, что не приходится при этом менять конфигурации самого PostgreSQL.
Запускаем СУБД:
# /etc/init.d/postgresql start
Проверяем, видит ли СУБД новое месторасположение директории данных:
# sudo -u postgres psql
postgres=# SHOW config_file;
config_file
------------------------------------------
/etc/postgresql/9.5/main/postgresql.conf
postgres=#
postgres=# SHOW data_directory;
data_directory
------------------------------
/var/lib/postgresql/main
postgres=# \q
config_file
------------------------------------------
/etc/postgresql/9.5/main/postgresql.conf
postgres=#
postgres=# SHOW data_directory;
data_directory
------------------------------
/var/lib/postgresql/main
postgres=# \q
Настройка сетевых сетевых подключений СУБД и аутентификации.
Изначально СУБД принимает и обслуживает подключения только через локальный "файловый сокет" (обычно "/var/run/postgresql"). Однако выделенный сервер должен работать через TCP/IP, так что явно разрешаем приём соединений отовсюду (точнее разрешения на доступ определим позже при настройке параметров аутентификации) в основном конфигурационном файле:
# vi /etc/postgresql/9.5/main/postgresql.conf
....
#listen_addresses = 'localhost'
listen_addresses = '0.0.0.0'
#port = 5432
port = 5433
....
#listen_addresses = 'localhost'
listen_addresses = '0.0.0.0'
#port = 5432
port = 5433
....
Настройками выше я также перевожу сервер PostgreSQL на прослушивание TCP-порта с номером отличным от принятого по умолчанию. Это нужно потому, что поверх серверов СУБД будет работать оптимизатор соединений PgBouncer, с точкой входа как раз на привычном пользователям порту. Удобнее наружу показывать то, что не потребует перенастройки клиентских приложений.
Большая часть изменений конфигурационного файла PostgreSQL требует его перезагрузки, так что лучше сразу внести туда нужные корректировки.
Как и все современные СУБД, PostgreSQL поддерживает множество методов аутентификации, но на момент установки используются только два: "PEER" и "MD5", регулирующие доступ к данным для подключающихся через "файловый сокет" локальных системных пользователей и пользователей аутентифицирующихся через встроенную в СУБД базу пользователей. Иногда вместо метода "PEER" (для подключений через локальный "файловый сокет") применяется метод "IDENT" (для подключений через TCP/IP). Суть способа аутентификации у них одинакова: если пользователь уже аутентифицирован в несущей операционной системе, то СУБД ему доверяет и пропускает внутрь без дополнительного подтверждения подлинности.
Лично я для унификации методов доступа и повышения пассивной безопасности предпочитаю даже при обращении через локальный файловый сокет требовать аутентификации паролем используемым внутри СУБД - это позволяет не смешивать локальных системных пользователей и пользователей СУБД. Потому рекомендую в конфигурации всем, кроме суперпользователя "postgres" (допускаемого только через локальный "файловый сокет"), установить вместо "peer" метод аутентификации "md5". Ну и конечно, для обеспечения возможности работы в среде фермы серверов с балансировкой запросов, нужно будет разрешить сетевые подключения между членами группировки СУБД:
# vi /etc/postgresql/9.5/main/pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
# ---------------------------------------------------
# Database administrative login by Unix domain socket
local all postgres peer
# "local" is for Unix domain socket connections only
local all all md5
# IPv4 connections:
host all all 127.0.0.1/32 md5
# IPv6 connections:
host all all ::1/128 md5
# ---------------------------------------------------
# Database administrative login by Unix domain socket
local all postgres peer
# "local" is for Unix domain socket connections only
local all all md5
# IPv4 connections:
host all all 127.0.0.1/32 md5
# IPv6 connections:
host all all ::1/128 md5
Внесённые изменения требуют перезапуска сервера:
# /etc/init.d/postgresql restart
Проверяем успешность изменения сетевых параметров:
# netstat -apn | grep -i tcp | grep -i postgres
tcp 0 0 0.0.0.0:5433 0.0.0.0:* LISTEN 23748/postgres
Дополнительный способ аутентификации.
В рамках наладки автоматического функционала обычно стараются обеспечить взаимодействие между подсистемами без применения паролей (опираясь на аутентификацию через SSH-ключи или выделение отношений в доверительные), но иногда всё же требуется применение пароля. В таком случае его проще сохранить в специальном файле, размещённом в домашней директории пользователя, от имени которого исполняются работы - утилиты комплекта ПО PostgreSQL научены брать его отсюда:
# vi /var/lib/postgresql/.pgpass
# hostname:port:database:username:password
*:*:*:postgres:userPassword
*:*:*:postgres:userPassword
Конечно же, обязательно защитим парольный файл от чтения его посторонними:
# chown postgres:postgres /var/lib/postgresql/.pgpass
# chmod go-rwx /var/lib/postgresql/.pgpass
# chmod go-rwx /var/lib/postgresql/.pgpass
Создание структуры хранения данных в PostgreSQL.
Сейчас мы уже на этапе, когда СУБД можно запустить и полноценно ей пользоваться. Полагаю, пора зайти в консоль PostgreSQL и завести пользователя, от имени которого с БД будет работать приложение, потом "базу данных", с которой будет работать приложение, и дать право полного доступа новому пользователю к его базе данных:
# sudo -u postgres psql
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5433".
postgres=#
postgres=# CREATE USER userName WITH PASSWORD 'userPassword';
postgres=# CREATE DATABASE userDB;
postgres=# GRANT ALL PRIVILEGES ON DATABASE userDB TO userName;
postgres=# \c userDB;
You are now connected to database "userDB" as user "postgres".
userDB=# \q
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5433".
postgres=#
postgres=# CREATE USER userName WITH PASSWORD 'userPassword';
postgres=# CREATE DATABASE userDB;
postgres=# GRANT ALL PRIVILEGES ON DATABASE userDB TO userName;
postgres=# \c userDB;
You are now connected to database "userDB" as user "postgres".
userDB=# \q
Если потребуется сменить пароль пользователя, то делаем это там же, в консоли СУБД:
postgres=# ALTER USER userName WITH ENCRYPTED PASSWORD 'newUserPassword';
Теперь у нас в распоряжении не связанный с локальными системными учётными записями аккаунт в СУБД, через который можно работать с данными:
$ psql -h localhost -p 5433 -U userName -W -d userDB
userDB=> \conninfo
You are connected to database "userDB" as user "userName" on host "localhost" at port "5433".
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
userDB=> \q
You are connected to database "userDB" as user "userName" on host "localhost" at port "5433".
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
userDB=> \q
Навскидку, перечень команд, полезных при изучении текущих параметров БД:
"\du" - перечень зарегистрированных в СУБД пользователей
"\l" - список баз данных сервера.
"\c baseName" - переключение в нужную базу данных.
"\dt" - список таблиц в базе.
"\d+ tableName" - описание таблицы.
"\d tableName" - список столбцов таблицы.
"\l" - список баз данных сервера.
"\c baseName" - переключение в нужную базу данных.
"\dt" - список таблиц в базе.
"\d+ tableName" - описание таблицы.
"\d tableName" - список столбцов таблицы.
Установка и настройка мультиплексора PgBouncer.
Растекусь немного мыслью по древу вариантов оптимизации производительности СУБД.
Большинство серверов со сложной внутренней логикой имеет многопроцессную или многопотоковую архитектуру - при этом каждый процесс или поток занимается обслуживанием либо одного соединения клиента, либо одного запроса в рамках установленного соединения.
СУБД PostgreSQL построена на многопроцессной архитектуре. Каждый раз, когда клиент подключается к СУБД, создаётся новый дочерний процесс PostgreSQL, на что расходуются ресурсы процессорного времени, ОЗУ и дисковой подсистемы несущего сервера. Чем чаще клиентские подключения и меньше объём запрашиваемых посредством их данных, тем сильнее заметен нерациональный расход ресурсов, затрачиваемых на обслуживание процедур порождения и уничтожения процессов краткосрочных соединений. На практике может доходить до того, что создание и закрытие клиентских соединений нагружает несущий сервер больше, чем непосредственная обработка запросов как таковых - особенно ярко это выражено при обслуживании web-приложений, которые на каждый запрос пользователем страницы сайта открывают отдельное соединение к серверу БД.
Одна из методик экономии ресурсов в условиях превалирования краткосрочных клиентских соединений состоит в многократном использовании однажды порождённого процесса или потока, для чего отработавшие в рамках исполнения запроса пользователя процессы или потоки не уничтожаются немедленно, а на некоторое время остаются в ожидании следующего подобного запроса. Специально для помощи в этом PostgreSQL создан легковесный мультиплексор PgBouncer, позволяющий существенно снизить частоту порождения и уничтожения процессов СУБД как таковой, беря на себя терминирование и выстраивание в очередь запросов пользователей, уменьшая время простоя уже порождённых процессов, задействуя их в обработке поступающих новых запросов.
Нужное нам приложение давно разрабатывается, стабильно и присутствует во всех пакетных дистрибутивах Linux:
# aptitude install pgbouncer
Корректируем единственный файл настройки PgBouncer, приводя его параметры к следующему виду (деактивируя всё остальное символом комментирования ";"):
# vi /etc/pgbouncer/pgbouncer.ini
# Описываем параметры подключения к СУБД (переопределяя пользовательские только в критичных местах)
[databases]
;pseudo_db = host=1.2.3.4 port=5433 dbname=real_db
* = port=5433 auth_user=postgres
# Описываем конфигурацию мультиплексора как такового
[pgbouncer]
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
# Выдвигаем PgBouncer на передний план, принимать подключения пользователей на принятом по умолчанию TCP-порту PostgreSQL
listen_addr = *
listen_port = 5432
# Кроме TCP-портов IPv4 и IPv6 PgBouncer может принимать подключения и через локальный файловый сокет
unix_socket_dir = /var/run/postgresql
# Задаём параметры SSL/TLS-шифрования соединений между клиентом и мультиплексором
;client_tls_sslmode = allow
client_tls_sslmode = require
client_tls_ciphers = normal
client_tls_key_file = /etc/ssl/private/ssl-cert-snakeoil.key
client_tls_cert_file = /etc/ssl/certs/ssl-cert-snakeoil.pem
;client_tls_ca_file = root.crt
# Явно указываем пропускать только пользователей прошедших аутентификацию с паролем зашифрованным посредством MD5
auth_type = md5
# Предварительную аутентификацию проводить, загружая реальные данные подключающегося пользователя из таблицы с паролями целевой БД
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1
# Указываем файл дополнительного перечня пользователей, аутентифицируемых на уровне PgBouncer (для управления таковым)
auth_file = /etc/pgbouncer/userlist.txt
# Перечисляем пользователей PostgreSQL, которым разрешён доступ к управлению PgBouncer (из перечня пользователей в предыдущем файле)
;admin_users = root
stats_users = stats
# Выбираем режим мультиплексирования соединений (pooling) и параметры очистки ресурсов соединения по завершению запроса
pool_mode = session
server_reset_query = DISCARD ALL
# Задаём параметры количества обслуживаемых клиентов
max_client_conn = 500
default_pool_size = 32
[databases]
;pseudo_db = host=1.2.3.4 port=5433 dbname=real_db
* = port=5433 auth_user=postgres
# Описываем конфигурацию мультиплексора как такового
[pgbouncer]
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
# Выдвигаем PgBouncer на передний план, принимать подключения пользователей на принятом по умолчанию TCP-порту PostgreSQL
listen_addr = *
listen_port = 5432
# Кроме TCP-портов IPv4 и IPv6 PgBouncer может принимать подключения и через локальный файловый сокет
unix_socket_dir = /var/run/postgresql
# Задаём параметры SSL/TLS-шифрования соединений между клиентом и мультиплексором
;client_tls_sslmode = allow
client_tls_sslmode = require
client_tls_ciphers = normal
client_tls_key_file = /etc/ssl/private/ssl-cert-snakeoil.key
client_tls_cert_file = /etc/ssl/certs/ssl-cert-snakeoil.pem
;client_tls_ca_file = root.crt
# Явно указываем пропускать только пользователей прошедших аутентификацию с паролем зашифрованным посредством MD5
auth_type = md5
# Предварительную аутентификацию проводить, загружая реальные данные подключающегося пользователя из таблицы с паролями целевой БД
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1
# Указываем файл дополнительного перечня пользователей, аутентифицируемых на уровне PgBouncer (для управления таковым)
auth_file = /etc/pgbouncer/userlist.txt
# Перечисляем пользователей PostgreSQL, которым разрешён доступ к управлению PgBouncer (из перечня пользователей в предыдущем файле)
;admin_users = root
stats_users = stats
# Выбираем режим мультиплексирования соединений (pooling) и параметры очистки ресурсов соединения по завершению запроса
pool_mode = session
server_reset_query = DISCARD ALL
# Задаём параметры количества обслуживаемых клиентов
max_client_conn = 500
default_pool_size = 32
Учитывая то, что сервер СУБД скорее всего уже имеет набор сертификатов для шифрования соединений с клиентами, при настройке PgBouncer (пример конфигурации выше) проще всего указать на таковые. Посмотреть текущую SSL/TLS конфигурацию PostgreSQL можно так:
# cat /etc/postgresql/9.5/main/postgresql.conf | grep -i ssl | grep -v "^#"
На этом настройка PgBouncer завершена и для принятия в работу изменений конфигурации его потребуется перезапустить:
# /etc/init.d/pgbouncer restart
Удостоверимся, что мультиплексор заработал и прослушивает заданные TCP-порты:
# netstat -apn | grep -i tcp | grep -i pgbouncer
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 4329/pgbouncer
tcp6 0 0 :::5432 :::* LISTEN 4329/pgbouncer
tcp6 0 0 :::5432 :::* LISTEN 4329/pgbouncer
Проверяем возможность соединения с сервером БД через мультиплексор (явно подключаясь на обслуживаемый им сетевой порт):
$ psql -h localhost -p 5432 -U userName -W -d userDB
userDB=> \conninfo
You are connected to database "userDB" as user "userName" on host "localhost" at port "5432".
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128, compression: off)
userDB=> \q
You are connected to database "userDB" as user "userName" on host "localhost" at port "5432".
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128, compression: off)
userDB=> \q
PgBouncer может управляться через подобие CLI посредством SQL-запросов в среде виртуальной базы данных с зарезервированным именем "pgbouncer" от имени пользователей, явно перечисленных с указанием паролей в соответствующем конфигурационном файле "auth_file".
С генерацией контрольной суммы MD5 паролей пользователей формата PostgreSQL не всё просто - "хеш" вычисляется не из пароля, а комбинации такового с именем пользователя, да ещё потом подставляется префикс "md5":
$ echo "md5`echo -n <userPassword><userName> | md5sum`" | cut -d " " -f -1
Формируем перечень пользователей PgBouncer (обязательно в двойных кавычках - иначе учётная запись игнорируется):
# vi /etc/pgbouncer/userlist.txt
# "<userName>" "<PG-MD5_Hash_of_userPassword>"
"stats" "md5..."
....
"stats" "md5..."
....
Подключаемся любым доступным PSQL-клиентом к БД "pgbouncer" и отдаём команды (уровень доступа регламентируется параметрами "admin_users" и "stats_users" в конфигурационном файле мультиплексора):
$ psql -h localhost -p 5432 -U stats -W -d pgbouncer
pgbouncer=# SHOW HELP;
DETAIL:
SHOW HELP|CONFIG|...|VERSION
....
SHUTDOWN
pgbouncer=# KILL;
ERROR: admin access needed
pgbouncer=#
pgbouncer=# SHOW LISTS;
list | items
---------------+-------
databases | 2
users | 3
pools | 3
free_clients | 99
used_clients | 1
login_clients | 0
free_servers | 29
used_servers | 21
dns_names | 0
dns_zones | 0
dns_queries | 0
dns_pending | 0
(12 rows)
pgbouncer=# \q
DETAIL:
SHOW HELP|CONFIG|...|VERSION
....
SHUTDOWN
pgbouncer=# KILL;
ERROR: admin access needed
pgbouncer=#
pgbouncer=# SHOW LISTS;
list | items
---------------+-------
databases | 2
users | 3
pools | 3
free_clients | 99
used_clients | 1
login_clients | 0
free_servers | 29
used_servers | 21
dns_names | 0
dns_zones | 0
dns_queries | 0
dns_pending | 0
(12 rows)
pgbouncer=# \q
Понятно, что если подключаться напрямую к СУБД, то управление мультиплексором будет недоступно, так как БД "pgbouncer" виртуальная и поддерживается самим мультиплексором путём перехвата обращений по ключевым словам и подстановки своих ответов.
Тюнить PgBouncer можно опираясь на рекомендации разработчиков - но мне этого пока ни разу не потребовалось.
Связка серверов СУБД доверительными отношениями через SSH-ключи.
Если предыдущие этапы успешно пройдены, то сейчас в нашем распоряжении уже имеется вполне работоспособный сервер СУБД. Однако мы идём к цели создания схемы с повышенным уровнем надёжности за счёт поддержания вторичного сервера, с полностью зеркальными настройками и набором обновляемых в "горячем режиме" данных.
Исходя из того соображения, что исходный (первичный) и получающий (вторичный) серверы обладают идентичным набором данных, их отношения можно считать доверительными. Для упрощения взаимодействий обеспечиваем полную связность между серверами баз данных, позволяя им ходить друг к другу по SSH для выгрузки файлов на этапе первичной синхронизации. Если схема не усложнена, то СУБД работает в среде пользователя "postgres", от имени которого также запускаются обслуживающие скрипты.
Генерируем для этого пользователя набор SSH-ключей:
# su postgres
$ ssh-keygen -t rsa -b 2048 -f $HOME/.ssh/id_rsa -P ""
$ ssh-keygen -t rsa -b 2048 -f $HOME/.ssh/id_rsa -P ""
Generating public/private rsa key pair.
Created directory '/var/lib/postgresql/.ssh'.
Your identification has been saved in /var/lib/postgresql/.ssh/id_rsa.
Your public key has been saved in /var/lib/postgresql/.ssh/id_rsa.pub.
Created directory '/var/lib/postgresql/.ssh'.
Your identification has been saved in /var/lib/postgresql/.ssh/id_rsa.
Your public key has been saved in /var/lib/postgresql/.ssh/id_rsa.pub.
Проверяем корректность данных созданного SSH-ключа:
$ ssh-keygen -l -f $HOME/.ssh/id_rsa
Сразу добавляем публичный ключ из полученного набора в список разрешений для доступа к этому же серверу:
$ cat $HOME/.ssh/id_rsa.pub >> $HOME/.ssh/authorized_keys
Далее просто копируем директорию "$HOME/.ssh" с первичного сервера СУБД на вторичный ("$HOME" в "Linux Debian/Ubuntu" скоре всего будет указывать на директорию "/var/lib/postgresql"), делая их разрешения доступа зеркально разрешающими. Ясное дело, что если схема усложнена другими ключами доступа, то нужно её дополнить новыми, не затирая имеющиеся простым копированием.
Аналогичную процедуру распространения готового набора SSH-ключей доступа проводим для всех вторичных серверов БД.
Неплохо удостоверится, что пользователь действительно может перемещаться между серверами с использованием SSH-ключей, заодно явно пополняя список известных узлов в файле "$HOME/.ssh/known_hosts":
# sudo -u postgres ssh db2.local
Теперь пользователь "postgres" способен ходить между серверами без указания пароля и функционал синхронизации баз данных через загрузку набора файлов журналов транзакций (WAL) может быть полностью автоматизирован.
Настройка функционала потоковой репликации "Streaming Replication (SR)".
Начиная с 9-ой версии PostgreSQL научился настолько хорошо самостоятельно реплицировать данные между серверами, используя для этого функционал журналирования транзакций WAL (Write-Ahead Log), что нужда в надстройках типа "Slony" или "Londiste" отпала. Будем использовать встроенный механизм "Streaming replication", выгружая данные с первичного на вторичный сервер в асинхронном режиме (важно не блокировать работу СУБД ожиданием подтверждений о приёме "зеркалируемых" данных).
Кстати, в документации PostgreSQL упоминается термин "кластер баз данных (Database Cluster)", который многие русскоговорящие молодые специалисты понимают как группировку серверов СУБД. Считается, что по умолчанию PostgreSQL запускается в одном экземпляре, но стоит только "правильно" настроить, как СУБД заработает в многосерверном режиме, волшебно и бесперебойно обслуживая базу данных, с разных точек подключения принимая запросы, да ещё и с гарантией доступности в случае выхода из строя инстансов. Так вот, всё с точность до наоборот. Чтение документации разъясняет, что под "кластером" разработчики PostgreSQL подразумевают набор баз данных, обслуживаемых единственным экземпляром сервера.
Прежде всего понадобится разрешить пользователю "postgres" сквозное доверительное хождение между серверами СУБД в рамках исполнения функционала репликации:
# vi /etc/postgresql/9.5/main/pg_hba.conf
....
# TYPE DATABASE USER ADDRESS METHOD
# --------------------------------------------------
....
# For database replication allow login by host trust
#host replication postgres 10.20.30.0/24 trust
host replication postgres 10.20.30.41/32 trust
host replication postgres 10.20.30.42/32 trust
# TYPE DATABASE USER ADDRESS METHOD
# --------------------------------------------------
....
# For database replication allow login by host trust
#host replication postgres 10.20.30.0/24 trust
host replication postgres 10.20.30.41/32 trust
host replication postgres 10.20.30.42/32 trust
# /etc/init.d/postgresql reload
С учётом того, что в перспективе мы стремимся к максимально простому горизонтальному масштабированию и возможности смещения роли ведущего сервера на доступный вторичный в случае выхода из строя первичного, конфигурационные файлы СУБД должны быть максимально идентичными, за исключением минимума опций, определяющих их роли. Потому изначально делаем настройки репликации одинаковыми, вначале на первичном (primary) сервере:
# vi /etc/postgresql/9.5/main/postgresql.conf
....
# Активируем расширенный режим журналирования транзакций WAL (Write-Ahead Log)
wal_level = hot_standby
# Ограничиваем число одновременно обслуживаемых "реплик" (некритично, и лучше больше требующегося, чтобы не переделывать)
max_wal_senders = 5
# Задаём количество хранящихся в директории "pg_xlog" файлов журнала WAL, ротируемых по мере появления свежих данных
# (в среднем один файл по 16MB, то есть в примере мы забираем место до 4GB)
# (фактически здесь мы определяем, насколько длительный разрыв связи могут пережить вторичные серверы без необходимости прямой загрузки файлов архивов с первичного сервера и скачкообразного восстановления до синхронного состояния)
wal_keep_segments = 256
# Включаем режим избыточной, но надёжной записи в журнал WAL изменённого блока данных целиком
full_page_writes = on
# Перестраховываясь от быстрого устаревания WAL включаем режим сохранения старых файлов журналов в отдельный архив
# (делаем это только на ведущем сервере, автоматически проверяя отсутствие обязательного для вторичных серверов файла)
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/main/recovery.conf && test ! -f /var/lib/postgresql/archive/%f && cp %p /var/lib/postgresql/archive/%f || /bin/true'
# Указываем серверу БД, чтобы он даже в режиме восстановления отвечал на запросы выборки данных (это важно, так как вторичные серверы "реплики" по сути непрерывно находятся в режиме восстановления и по умолчанию не обслуживают запросы клиентов)
# (параметр актуален только для вторичных серверов и игнорируется первичным, но для унификации конфигураций объявляем его везде)
hot_standby = on
# Указываем серверу уведомлять вышестоящего в схеме репликации о своих операциях, связанных с изменениями состояния для минимизации вероятности конфликтов вызова пересекающихся по функционалу процедур
# (параметр актуален только для вторичных серверов и игнорируется первичным, но для унификации конфигураций объявляем его везде)
hot_standby_feedback = on
....
# Активируем расширенный режим журналирования транзакций WAL (Write-Ahead Log)
wal_level = hot_standby
# Ограничиваем число одновременно обслуживаемых "реплик" (некритично, и лучше больше требующегося, чтобы не переделывать)
max_wal_senders = 5
# Задаём количество хранящихся в директории "pg_xlog" файлов журнала WAL, ротируемых по мере появления свежих данных
# (в среднем один файл по 16MB, то есть в примере мы забираем место до 4GB)
# (фактически здесь мы определяем, насколько длительный разрыв связи могут пережить вторичные серверы без необходимости прямой загрузки файлов архивов с первичного сервера и скачкообразного восстановления до синхронного состояния)
wal_keep_segments = 256
# Включаем режим избыточной, но надёжной записи в журнал WAL изменённого блока данных целиком
full_page_writes = on
# Перестраховываясь от быстрого устаревания WAL включаем режим сохранения старых файлов журналов в отдельный архив
# (делаем это только на ведущем сервере, автоматически проверяя отсутствие обязательного для вторичных серверов файла)
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/main/recovery.conf && test ! -f /var/lib/postgresql/archive/%f && cp %p /var/lib/postgresql/archive/%f || /bin/true'
# Указываем серверу БД, чтобы он даже в режиме восстановления отвечал на запросы выборки данных (это важно, так как вторичные серверы "реплики" по сути непрерывно находятся в режиме восстановления и по умолчанию не обслуживают запросы клиентов)
# (параметр актуален только для вторичных серверов и игнорируется первичным, но для унификации конфигураций объявляем его везде)
hot_standby = on
# Указываем серверу уведомлять вышестоящего в схеме репликации о своих операциях, связанных с изменениями состояния для минимизации вероятности конфликтов вызова пересекающихся по функционалу процедур
# (параметр актуален только для вторичных серверов и игнорируется первичным, но для унификации конфигураций объявляем его везде)
hot_standby_feedback = on
....
На этом настройка первичного (primary) сервера в рамках наладки репликации завешена и для применения новых параметров его нужно перезапустить:
# /etc/init.d/postgresql restart
Первоначальное зеркалирование баз данных PostgreSQL.
Пока вторичный сервер ещё не настроен, его нужно остановить - мы будем менять тут всё и работающей СУБД это не понравится очень сильно:
# /etc/init.d/pgbouncer stop
# /etc/init.d/postgresql stop
# /etc/init.d/postgresql stop
Перед запуском инстанса PostgreSQL в роли вторичного (slave) нужно привести его конфигурацию (в основном сосредоточенную в "postgresql.conf") к полному подобию первичного сервера и загрузить оттуда действующую базу данных. Учитывая, что выше мы решили сделать их полностью зеркальными, то элементарно копируем конфигурационные файлы с первичного сервера на вторичный:
# sudo -u postgres scp postgres@db1.local:/etc/postgresql/9.5/main/pg_hba.conf /etc/postgresql/9.5/main/pg_hba.conf
# sudo -u postgres scp postgres@db1.local:/etc/postgresql/9.5/main/postgresql.conf /etc/postgresql/9.5/main/postgresql.conf
# sudo -u postgres scp postgres@db1.local:/etc/pgbouncer/pgbouncer.ini /etc/pgbouncer/pgbouncer.ini
# sudo -u postgres scp postgres@db1.local:/etc/pgbouncer/userlist.txt /etc/pgbouncer/userlist.txt
# sudo -u postgres scp postgres@db1.local:/etc/postgresql/9.5/main/postgresql.conf /etc/postgresql/9.5/main/postgresql.conf
# sudo -u postgres scp postgres@db1.local:/etc/pgbouncer/pgbouncer.ini /etc/pgbouncer/pgbouncer.ini
# sudo -u postgres scp postgres@db1.local:/etc/pgbouncer/userlist.txt /etc/pgbouncer/userlist.txt
Для полной синхронизации серверов (без остановки исходного) лучше всего применить утилиту "pg_basebackup", через механизм репликации вытаскивающей "бинарный (файловый) бэкап" (не набор SQL-команд, как в привычном "дампе базы"). Удобство в том, что для её работы не требуется самостоятельных выкрутасов с вызовом на первичном сервере функций "pg_start_backup/pg_stop_backup", предваряющих и фиксирующих завершение синхронизации данных посредством простых инструментов копирования вроде "scp" или "rsync".
Утилитой "pg_basebackup" данные синхронизируются в полном объёме, включая вообще всё внутри директории таковых (даже свежие журналы WAL, успевшие накопиться за время процедуры транспортировки), так что есть смысл предварительно зачистить место:
# mv /var/lib/postgresql/main /var/lib/postgresql/main.old
Запускаем процедуру загрузки данных с первичного сервера и раскладывания их по местам в локальной файловой системе вторичного:
# sudo -u postgres pg_basebackup -X fetch -P -h db1.local -p 5433 -U postgres -w -D /var/lib/postgresql/main
Опция "-X fetch" в команде выше указывает осуществлять загрузку в два этапа: вначале файлы базы данных как таковой, а потом отдельно добирать файлы журнала WAL, содержащие транзакции случившиеся уже после начала копирования файлов баз данных. Если выбрать тип загрузки данных "-X stream" (используемый по умолчанию, кстати), то файлы журналов WAL не будут загружаться, а случающиеся во время резервного копирования транзакции будут передаваться параллельным основному потоком и применяться к загруженным файлам данных "на лету". Второй способ быстрее, но его применение внесло бы разнобой в идеологию применяемых здесь методов восстановления, один из которых ориентирован исключительно на архивы WAL-файлов.
Если синхронизация запускалась от имени суперпользователя, то подкорректируем права доступа к полученным файлам:
# chown -R postgres:postgres /var/lib/postgresql/main
# chmod -R go-rwx /var/lib/postgresql/main
# chmod -R go-rwx /var/lib/postgresql/main
Итак, после успешного завершения выгрузки данных мы получили зеркальную копию первичного сервера БД, отстающую от оригинала с каждой новой транзакцией (выше мы заложили большой период ротации журналов WAL, так что часик-другой несинхронизированной работы серверов будет легко исправлен автоматически загрузкой обновлений). Если хочется экспериментов, то прямо сейчас можно запустить PostgreSQL на нашем вторичном сервере и он должен корректно стартовать, как минимум сообщив в журнале событий о своём хорошем самочувствии:
# tail /var/log/postgresql/postgresql-9.5-main.log
....
LOG: database system was interrupted; last known up ...
LOG: redo starts at 0/13000028
LOG: consistent recovery state reached at 0/130000F8
LOG: redo done at 0/130000F8
LOG: MultiXact member wraparound protections are now enabled
LOG: database system is ready to accept connections
LOG: database system was interrupted; last known up ...
LOG: redo starts at 0/13000028
LOG: consistent recovery state reached at 0/130000F8
LOG: redo done at 0/130000F8
LOG: MultiXact member wraparound protections are now enabled
LOG: database system is ready to accept connections
Запуск вторичного сервера PostgreSQL.
Все отличия настройки вторичного сервера сосредоточены в файле "recovery.conf", располагающемся в директории с данными сервера. Изначальная идея конфигурации заключалась в том, чтобы при обнаружении этого файла с зарезервированным именем сервер СУБД переходил в режим восстановления баз данных, руководствуясь заданными в этом файле параметрами проводил все необходимые процедуры, после чего файл переименовывался бы в "recovery.conf.done", а сервер СУБД переходил в нормальный режим обслуживания клиентов. Со временем возможности репликации стали использоваться шире, и в нашем случае для вторичных серверов режим восстановления и непрерывной синхронизации длится весь срок их службы.
Потребный нам режим включается парой параметров специализированного конфигурационного файла:
# vi /var/lib/postgresql/main/recovery.conf
# Явно указываем СУБД работать в режиме восстановления с одновременным обслуживанием RO-запросов
standby_mode = on
# Описываем параметры подключения к первичному (primary) серверу СУБД, необходимые для непрерывной репликации
primary_conninfo = 'host=db1.local port=5433 user=postgres'
# Описываем команду, которая предоставит по запросам PostgreSQL архивные файлы журналов транзакций WAL
# (это бывает необходимо при скачкообразной синхронизации)
restore_command = 'scp postgres@db1.local:/var/lib/postgresql/archive/%f "%p"'
# Явно указываем имя файла (по умолчанию создаётся в директории $PGDATA), при появлении которого сервер должен будет выйти из режима восстановления в режим полноценного обслуживания клиентов
trigger_file = 'stop_recovery'
standby_mode = on
# Описываем параметры подключения к первичному (primary) серверу СУБД, необходимые для непрерывной репликации
primary_conninfo = 'host=db1.local port=5433 user=postgres'
# Описываем команду, которая предоставит по запросам PostgreSQL архивные файлы журналов транзакций WAL
# (это бывает необходимо при скачкообразной синхронизации)
restore_command = 'scp postgres@db1.local:/var/lib/postgresql/archive/%f "%p"'
# Явно указываем имя файла (по умолчанию создаётся в директории $PGDATA), при появлении которого сервер должен будет выйти из режима восстановления в режим полноценного обслуживания клиентов
trigger_file = 'stop_recovery'
На этом настройка вторичного (slave) сервера в рамках наладки репликации завешена и его можно запустить:
# /etc/init.d/postgresql start
# /etc/init.d/pgbouncer start
# /etc/init.d/pgbouncer start
Элементарно проверяем, работают ли процессы отправки и приёмки данных WAL.
На первичном (primary) сервере:
# ps wax | grep -i postgres | grep -i sender
...postgres: wal sender process postgres db2.local(58364) streaming 0/A000060
На вторичном (slave) сервере:
# ps wax | grep -i postgres | grep -i receiver
...postgres: wal receiver process streaming 0/A000060
Детальную информацию о состоянии процессов репликации можно получить через интерфейс SQL-запросов:
# sudo -u postgres psql
postgres=# SELECT * FROM pg_stat_replication;
postgres=# SELECT * from pg_stat_database_conflicts;
postgres=# SELECT * from pg_stat_database_conflicts;
Элементарная проверка работы "горячей" репликации.
На первичном сервере БД PostgreSQL создаём тестовую таблицу и вносим туда строку данных:
$ psql -h db1.local -p 5432 -U userName -W -d userDB
userDB=> CREATE TABLE test_one(id INT NOT NULL PRIMARY KEY, name VARCHAR(20));
userDB=> INSERT INTO test_one(id, name) VALUES('1', 'test1');
userDB=> INSERT INTO test_one(id, name) VALUES('1', 'test1');
На вторичном сервере считываем содержимое тестовой таблицы:
$ psql -h db2.local -p 5432 -U userName -W -d userDB
userDB=# SELECT * FROM test_one;
id | name
−−−−+-------
1 | test1
(1 row)
id | name
−−−−+-------
1 | test1
(1 row)
Практически данные зеркалируются на вторичный сервер с незаметной пользователю задержкой.
Оптимизация архивного хранилища WAL-файлов.
В настройках PostgreSQL максимальный объём занимаемый текущими журналами транзакций (в директории "pg_xlog") определяется количеством (параметр "wal_keep_segments") и размером файлов (по умолчанию 16MB) таковых. Аналогичного используемому для ротации рабочих WAL-файлов встроенного механизма контроля за уже вынесенными на архивное хранение журналов нет. Приходится придумывать, как усекать архивное хранилище, чтобы оно не разбухло слишком сильно. Напоминаю, что WAL-журналы вынесенные на архивное хранение уже не требуются для работы СУБД и сохраняются на случай крайне маловероятной необходимости восстановления актуальности данных залежавшегося вторичного сервера СУБД при полном выходе из строя БД первичного - в общем, их наличие не критично, хоть и полезно в очень редких случаях эксплуатации.
На мой взгляд проще всего решить, сколько места можно выделить для хранения архивов и по расписанию удалять вылезшие за грань установленного лимита, благо очерёдность создания журналов чётко фиксируется в именах файлов, формируемых на основе уникального нарастающего порядкового номера в трёхкомпонентном шестнадцатеричном формате.
Пишем скрипт удаления лишних архивных файлов WAL:
# mkdir -p /usr/local/etc/postgresql
# vi /usr/local/etc/postgresql/pg_archive_truncation.sh
# chmod ug+x /usr/local/etc/postgresql/pg_archive_truncation.sh
# vi /usr/local/etc/postgresql/pg_archive_truncation.sh
# chmod ug+x /usr/local/etc/postgresql/pg_archive_truncation.sh
#!/bin/bash
# Дополняем набор переменных окружения путём к утилитам PostgreSQL
PATH=${PATH}:/usr/lib/postgresql/9.5/bin
# Указываем выделяемый на архивы объём (в Мегабайтах; пример для лимита в 10GB)
# (принимая во внимание, что средний размер одного файла журнала 16M)
ALLOCATED=10240
# Явно указываем пользователя, от имени которого будет осуществляться процедура
USER="postgres"
# Указываем директорию архива
ARCHIVEDIR="/var/lib/postgresql/archive"
# Указываем файл для журнала событий
LOG="/var/log/pgarchtrunc.log"
# Фиксируем дату и время запуска процедуры
DATE=$(date +"%Y%m%d.%H%M")
echo >> ${LOG}; echo "${DATE}" >> ${LOG}
# Подсчитываем максимальное количество архивных файлов, могущих заполнить указанный выше допустимый объём
let "AMOUNT = $(echo ${ALLOCATED} | sed -e 's/[^0-9]//g') / 16"
# Ищем самый старый файл в диапазоне допустимого количества
POINT=$(ls "${ARCHIVEDIR}" | tail -n ${AMOUNT} | head -n 1)
# Если файл на пределе допустимого количества найден, то запускаем усечение архива
if [ "${POINT}" != "" ]; then
# Отдаём команду удалить все архивные файлы старше указанного
pg_archivecleanup -d "${ARCHIVEDIR}" "${POINT}" 2>> ${LOG}
fi
exit 0
# Дополняем набор переменных окружения путём к утилитам PostgreSQL
PATH=${PATH}:/usr/lib/postgresql/9.5/bin
# Указываем выделяемый на архивы объём (в Мегабайтах; пример для лимита в 10GB)
# (принимая во внимание, что средний размер одного файла журнала 16M)
ALLOCATED=10240
# Явно указываем пользователя, от имени которого будет осуществляться процедура
USER="postgres"
# Указываем директорию архива
ARCHIVEDIR="/var/lib/postgresql/archive"
# Указываем файл для журнала событий
LOG="/var/log/pgarchtrunc.log"
# Фиксируем дату и время запуска процедуры
DATE=$(date +"%Y%m%d.%H%M")
echo >> ${LOG}; echo "${DATE}" >> ${LOG}
# Подсчитываем максимальное количество архивных файлов, могущих заполнить указанный выше допустимый объём
let "AMOUNT = $(echo ${ALLOCATED} | sed -e 's/[^0-9]//g') / 16"
# Ищем самый старый файл в диапазоне допустимого количества
POINT=$(ls "${ARCHIVEDIR}" | tail -n ${AMOUNT} | head -n 1)
# Если файл на пределе допустимого количества найден, то запускаем усечение архива
if [ "${POINT}" != "" ]; then
# Отдаём команду удалить все архивные файлы старше указанного
pg_archivecleanup -d "${ARCHIVEDIR}" "${POINT}" 2>> ${LOG}
fi
exit 0
# chown -R postgres:postgres /usr/local/etc/postgresql
# chmod -R o-rwx /usr/local/etc/postgresql
# chmod -R o-rwx /usr/local/etc/postgresql
Внесём в таблицу "/etc/crontab" задание запуска скрипта усечения архива журналов с определённой периодичностью (каждую ночь, в пять утра):
# vi /etc/crontab
....
# PostgreSQL old archive truncation
0 5 * * * root /usr/local/etc/postgresql/pg_archive_truncation.sh &
....
# PostgreSQL old archive truncation
0 5 * * * root /usr/local/etc/postgresql/pg_archive_truncation.sh &
....
Итоги.
По умолчанию PostgreSQL и PgBouncer сконфигурированы таким образом, чтобы они могли быть запущены практически на любом компьютере и не слишком мешали работе других приложений. При этом СУБД вполне эффективно обслуживает базы данных объёмом до 20-40GB - а вот для более тяжёлых придётся взяться за учебники и приступить к оптимизации настроек. В изучении принципов работы поможет книжка Васильева А.Ю. "Работа с PostgreSQL: настройка и масштабирование".
