Репликация PostgreSQL master slave

Введение

Цель - настроить автоматическое копирование (репликацию) баз данных с сервера.

Тот сервер, с которого копируется информация принято называть ведущим или первичным. В англоязычной литературе принято называть его master.

Сервер на который сохраняется копия (реплика) принято называть ведомым или вторичным. На английском это раньше называлось slave а сейчас внедряют более политкорректный термин standby.

[andrei@localhost ~]$ в начале строки означает, что команды выполняются от имени обычного пользователя CentOS 7, входящего в группу sudo

-bash-4.2$ в начале строки означает, что команды выполняются от пользователя postgres

-bash-4.2$ в начале строки означает, что команды выполняются от пользователя postgres

Это указано не всегда, но если указано - значит нужно обратить на это внимание.

Настройка сильно зависит от версии, даже от минорной.

Напомню, что политика PostgreSQL по присваиванию версий немного изменилась между 9.6 и 10.

То есть 9.6 отличается не только от 10 но и от 9.2. Некоторых параметров, доступных в 9.6 просто нет в 9.2

Подготовка рабочей среды

Нужно установить и настроить две базы данных PostgreSQL.

В этом примере вы узнаете как это сделать с помощью двух виртуальных CentOS Linux на VirtualBox

В этом примере:

у ведущего сервера (master) IP адрес 192.168.56.109 (master_ip)

у ведомого (slave/stand by) 192.168.56.110 (slave_ip)

Темы с которыми рекомендуется познакомиться для создания аналогичного сетапа:

• CentOS Linux
• VirtualBox
• CentOS7 на VirtualBox
• SSH

На каждом хосте установите PostgreSQL

sudo yum install postgresql-server postgresql-contrib

Задайте пароль пользователю postgres

sudo passwd postgres

Залогиньтесь под этим пользователем

sudo su - postgres

Создайте ssh-ключ для этого пользователя.

ssh-keygen

На все появившиеся уведомления нажмите Enter.

Передайте ключ на другой сервер:

ssh-copy-id IP_адрес_другого_сервера

Инициализируйте базу данных и запустите сервер

cd /var/lib/pgsql
postgresql-setup initdb
/usr/bin/pg_ctl -D /var/lib/pgsql/data -l logfile start

На обоих хостах отройте порт 5432. Если вы не знаете как это сделать - инструкцию можно найти в статье

«CentOS Firewall - открыть порт»

PostgreSQL 9.2

PostgreSQL 9.2 уже не поддерживается .

На момент написания статьи самая близкая поддерживаемая версия это 9.6

Настройка master

Сперва нужно настроить ведущий сервер (master)

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

-bash-4.2$ su - postgres -c "psql -c 'SHOW config_file;'"

Password: config_file ------------------------------------- /var/lib/pgsql/data/postgresql.conf (1 row)

vi /var/lib/pgsql/data/postgresql.conf

wal_level = hot_standby
max_wal_senders = 1
wal_keep_segments = 50

# - Connection Settings - listen_addresses = 'localhost, 192.168.56.109' # what IP address(es) to listen on;

wal_level = hot_standby # minimal, archive, or hot_standby

max_wal_senders = 1 # max number of walsender processes # (change requires restart) wal_keep_segments = 50 # in logfile segments, 16MB each; 0 disables

• 192.168.56.109 — IP-адрес сервера, на котором он будем слушать запросы Postgre;
• wal_level указывает, сколько информации записывается в WAL (Write Ahead Log - журнал операций, который используется для репликации);
• max_wal_senders — количество планируемых слейвов;
• hot_standby — определяет, можно или нет подключаться к postgresql для выполнения запросов в процессе восстановления;
• hot_standby_feedback — определяет, будет или нет сервер slave сообщать мастеру о запросах, которые он выполняет.

Перезапустить postgresql от обычного пользователя

[andrei@host ~]$ sudo systemctl restart postgresql

Снова залогиньтесь под postgres

sudo su - postgres

В файле pg_hba.conf добавьте slave_ip

vi /var/lib/pgsql/data/pg_hba.conf

host replication postgres 192.168.56.110/32 trust

Теперь нужно сделать копию данных с мастера и отправить на слейв

Если вы вышли из пользователя postgres - залогиньтесь снова

su - postgres

Бэкап (я делаю с пустой базы, сразу после установки)

psql -c "SELECT pg_start_backup('replbackup');"

pg_start_backup ----------------- 0/2000020 (1 row)

tar cfP /tmp/db_file_backup.tar /var/lib/pgsql/data
psql -c "SELECT pg_stop_backup();"

Скорее всего появится ошибка

NOTICE: WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to complete the backup pg_stop_backup ---------------- 0/20000E0 (1 row)

Так как скопирована будет вся директория, можно сказать, что мы пользуемся other means

Отправьте на слейв

scp /tmp/db_file_backup.tar andrei@192.168.56.110:/tmp/

Настройка slave

Настройка slave или, как сейчас стало модно писать, stand by сервера начинается также с определения директории с настройками.

su - postgres -c "psql -c 'SHOW data_directory;'"

Password: data_directory --------------------- /var/lib/pgsql/data (1 row)

Или

echo $PGDATA

/var/lib/pgsql/data

-bash-4.2$ su - postgres -c "psql -c 'SHOW config_file;'"

Password: config_file ------------------------------------- /var/lib/pgsql/data/postgresql.conf (1 row)

Залогиньтесь под обычным пользователем (не postgres)

Остановите сервис postgresql:

sudo systemctl stop postgresql

==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units === Authentication is required to manage system services or units. Multiple identities can be used for authentication: 1. andrei 2. tester Choose identity to authenticate as (1-2): 1 Password: ==== AUTHENTICATION COMPLETE ===

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

sudo su - postgres
tar -czvf /tmp/data_pgsql.tar.gz /var/lib/pgsql/data

В данном примере мы сохраним всё содержимое каталога /var/lib/pgsql/data в виде архива /tmp/data_pgsql.tar.gz.

ls /tmp | grep pgsql

data_pgsql.tar.gz

Либо просто скопируете в другую папку

sudo su - postgres
bash-4.2$ mv /var/lib/pgsql/data/ /var/lib/pgsql/data.old

Удаляем содержимое каталога с данными:

rm -rf /var/lib/pgsql/data/*
ls /var/lib/pgsql/data

Теперь нужно распаковать архив, который получен с мастера

tar xvfP /tmp/db_file_backup.tar

Удалите postmaster.pid чтобы слейв не видел pid мастера как свой

rm -f /var/lib/pgsql/data/postmaster.pid

В файле postgresql.conf

Укажите listen_addresses и включите hot_standby

vi /var/lib/pgsql/data/postgresql.conf

# - Connection Settings - listen_addresses = 'localhost, 192.168.56.110' # what IP address(es) to listen on;

# - Standby Servers - # These settings are ignored on a master server. hot_standby = on # "on" allows queries during recovery # (change requires restart) hot_standby_feedback = on # send info from standby to prevent

recovery.conf

Теперь на слейве нужно отредактировать файл recovery.conf

Возможно обазец лежит в директории share тогда его можно скопировать оттуда

cp /usr/pgsql/share/recovery.conf.sample /var/lib/pgsql/data/recovery.conf

Если его нет - нужно создать его в той же диретории где лежит postgresql.conf

В данном примере это

/var/lib/pgsql/data

Создать файл можно командой

touch recovery.conf

Мой пример файла можно посмотреть здесь

Пример, который я брал за основу - на сайте pgpool

vi /var/lib/pgsql/data/recovery.conf

standby_mode = on primary_conninfo = 'host=192.168.56.110 port=5432'

Обновите разрешения доступа и запустите сервер

chown postgres.postgres /var/lib/pgsql/data/recovery.conf
systemctl start postgresql

PostgreSQL 9.6

Для 9.6 инструкция в разработке

Создайте пользователя repluser

Это нужно делать из-под пользователя postgres

su - postgres

Password:
Last login: Wed Jun 9 16:33:50 EEST 2021 on pts/1
-bash-4.2$

createuser --replication -P repluser

Enter password for new role:
Enter it again:

-bash-4.2$ exit

logout

Если у вас PostgreSQL версии 9.6

# - Connection Settings - listen_addresses = 'localhost, 192.168.56.109' # what IP address(es) to listen on;

wal_level = replica

max_wal_senders = 1 # max number of walsender processes

hot_standby = on # "on" allows queries during recovery

hot_standby_feedback = on # send info from standby to prevent

• 192.168.56.109 — IP-адрес сервера, на котором он будем слушать запросы Postgre;
• wal_level указывает, сколько информации записывается в WAL (Write Ahead Log - журнал операций, который используется для репликации);
• max_wal_senders — количество планируемых слейвов;
• hot_standby — определяет, можно или нет подключаться к postgresql для выполнения запросов в процессе восстановления;
• hot_standby_feedback — определяет, будет или нет сервер slave сообщать мастеру о запросах, которые он выполняет.
• Дополнительно в некоторых версиях PostgreSQL нужно указать
  
  max_replication_slots — максимальное число слотов репликации (данный параметр не нужен для postgresql 9.2 — с ним сервер не запустится);

Теперь нужно скопировать содержимое аналогичной директории с сервера

В PostgreSQL 9.6 можно добавить опцию --write-recovery-conf

pg_basebackup -h 192.168.56.109 -U repluser -D /var/lib/pgsql/data --xlog-method=stream --write-recovery-conf

Теперь нужно скопировать содержимое аналогичной директории с сервера

pg_basebackup -h 192.168.56.109 -U repluser -D /var/lib/pgsql/data --xlog-method=stream

Password:

ls /var/lib/pgsql/data

backup_label global pg_hba.conf pg_log pg_notify pg_snapshots pg_subtrans pg_twophase pg_xlog base pg_clog pg_ident.conf pg_multixact pg_serial pg_stat_tmp pg_tblspc PG_VERSION postgresql.conf

В файле postgresql.conf замените master_ip на slave_ip

Проверка репликации

Проверка репликации не отличается для 9.2 и 9.6

На слейве зайдите как postgres и выполните psql

su postgres
psql

Изучите список существующих баз данных

\l

List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (3 rows)

Теперь на мастере создайте новую базу данных

postgres=# CREATE DATABASE "repltest" WITH OWNER "postgres" ENCODING 'UTF8';

Подождите немного и проверьте появилась ли новая база данных на слейве

\l

List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | repltest | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (4 rows)