Postgresql replication in windows

Логическая репликация — это метод репликации объектов данных и изменений в них, использующий репликационные идентификаторы (обычно это первичный ключ). Мы называем такую репликацию «логической», в отличие от физической, которая построена на точных адресах блоков и побайтовом копировании. PostgreSQL поддерживает оба механизма одновременно; см. Главу 26. Логическая репликация позволяет более детально управлять репликацией данных и аспектами безопасности.

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

Логическая репликация таблицы обычно начинается с создания снимка данных в публикуемой базе данных и копирования её подписчику. После этого изменения на стороне публикации передаются подписчику в реальном времени, когда они происходят. Подписчик применяет изменения в том же порядке, что и узел публикации, так что для публикаций в рамках одной подписки гарантируется транзакционная целостность. Этот метод репликации данных иногда называется транзакционной репликацией.

Типичные сценарии использования логической репликации:

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

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

Объединение нескольких баз данных в одну (например, для целей анализа).

Репликация между разными основными версиями PostgreSQL.

Репликация между экземплярами PostgreSQL на разных платформах (например, с Linux на Windows)

Предоставление доступа к реплицированным данным другим группам пользователей.

Разделение подмножества базы данных между несколькими базами данных.

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

Настройка потоковой репликации PostgreSQL

Репликация PostgreSQL представляет из себя способ реализации отказоустойчивого кластера. Инструкция написана на примере PostgreSQL 9.6 и 10, также она будет работать для PostgreSQL 9.2 (все нюансы будут отмечены отдельными комментариями).

В данном примере мы настроим потоковую (streaming) репликацию. Другой тип репликации (логическая) добавлена в PostgreSQL 10. Она позволяет реплицировать разные базы данных и таблицы на разные реплики.

Также, мы будем применять асинхронную репликацию — это вид репликации, при котором запросы выполняются сначала на мастере, затем попадают в журнал операций (WAL) и только после этого — на slave. При синхронной репликации запросы сначала попадают в WAL — после в мастер и слейв.

Используемые в данном руководстве команды, применимы для операционных систем Linux. Если Postgre работает под Windows, данную инструкцию можно использовать как шпаргалку для настройки конфигурационных файлов СУБД.

1. Подготовка серверов

Для начала, готовим наши серверы к настройке кластера.

PostgreSQL

На всех серверах баз данных должна быть установлена одна и та же версия PostgreSQL. Также, все серверы должны иметь одну и ту же архитектуру процессора.

Вот пример установки сервера PostgreSQL на CentOS 7.

Брандмауэр

При использовании брандмауэра, необходимо открыть TCP-порт 5432 — он используется сервером postgre.

а) Если управление выполняется с помощью Firewalld:

firewall-cmd —permanent —add-port=5432/tcp

б) Если используем Iptables:

iptables -A INPUT -p tcp —dport 5432 -j ACCEPT

в) Если используем UFW:

ufw allow 5432/tcp

SELinux

Если активирована система безопасности SELinux (по умолчанию в системах Red Hat / CentOS / Fedora), отключаем ее:

sed -i ‘s/^SELINUX=.*/SELINUX=disabled/g’ /etc/selinux/config

Если необходимо, чтобы SELinux работал, настраиваем его.

2. Настройки на Master

В данной статье мы будем настраивать серверы с IP-адресами 192.168.1.10 (первичный или master) и 192.168.1.11 (вторичный или slave).

Переходим на сервер, с которого будем реплицировать данные (мастер) и выполняем следующие действия.

Создаем пользователя в PostgreSQL

Входим в систему под пользователем postgres:

Создаем нового пользователя для репликации:

createuser —replication -P repluser

* система запросит пароль — его нужно придумать и ввести дважды. В данном примере мы создаем пользователя repluser.

Выходим из оболочки пользователя postgres:

Настраиваем postgresql

Смотрим расположение конфигурационного файла postgresql.conf командой:

su — postgres -c «psql -c ‘SHOW config_file;'»

В моем случае система вернула строку:

* конфигурационный файл находится по пути /etc/postgresql/9.6/main/postgresql.conf.

Открываем конфигурационный файл postgresql.conf.

* мы открываем файл, который получили sql-командой SHOW config_file;.

Редактируем следующие параметры:

listen_addresses = ‘localhost, 192.168.1.10’
wal_level = replica
max_wal_senders = 2
max_replication_slots = 2
hot_standby = on
hot_standby_feedback = on

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

Открываем конфигурационный файл pg_hba.conf — он находитсяч в том же каталоге, что и файл postgresql.conf:

Добавляем следующие строки:

host replication repluser 127.0.0.1/32 md5
host replication repluser 192.168.1.10/32 md5
host replication repluser 192.168.1.11/32 md5

* данной настройкой мы разрешаем подключение к базе данных replication пользователю repluser с локального сервера (localhost и 192.168.1.10) и сервера 192.168.1.11.

Перезапускаем службу postgresql:

systemctl restart postgresql

* обратите внимание, что название для сервиса в системах Linux может различаться.

3. Настройки на Slave

Смотрим путь до конфигурационного файла postgresql:

su — postgres -c «psql -c ‘SHOW data_directory;'»

В моем случае путь был:

Также смотрим путь до конфигурационного файла postgresql.conf (нам это понадобиться ниже):

su — postgres -c «psql -c ‘SHOW config_file;'»

Останавливаем сервис postgresql:

systemctl stop postgresql

На всякий случай, создаем архив базы:

tar -czvf /tmp/data_pgsql.tar.gz /var/lib/pgsql/9.6/data

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

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

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

И реплицируем данные с master сервера.

а) Если у нас postgresql 9:

su -u postgres -с «pg_basebackup -h 192.168.1.10 -U repluser -D /var/lib/pgsql/9.6/data —xlog-method=stream —write-recovery-conf»

* где 192.168.1.10 — IP-адрес мастера; /var/lib/pgsql/9.6/data — путь до каталога с данными.

б) Если у нас postgresql 10:

su — postgres -c «pg_basebackup —host=192.168.1.10 —username=repluser —pgdata=/var/lib/pgsql/10/data —wal-method=stream —write-recovery-conf»

* где 192.168.1.10 — IP-адрес мастера; /var/lib/pgsql/10/data — путь до каталога с данными.

После ввода команды система запросит пароль для созданной ранее учетной записи repluser — вводим его. Начнется процесс клонирования данных.

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

И редактируем следующие параметры:

listen_addresses = ‘localhost, 192.168.1.11’

* где 192.168.1.11 — IP-адрес нашего вторичного сервера.

Снова запускаем сервис postgresql:

systemctl start postgresql

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

Посмотреть статус

Статус работы репликации можно посмотреть следующими командами.

=# select * from pg_stat_replication;

=# select * from pg_stat_wal_receiver;

Создать тестовую базу

На мастере заходим в командную оболочку Postgre:

su — postgres -c «psql»

Создаем новую базу данных:

=# CREATE DATABASE repltest ENCODING=’UTF8′;

Теперь на вторичном сервере смотрим список баз:

su — postgres -c «psql»

Мы должны увидеть среди баз ту, которую создали на первичном сервере:

Postgresql replication in windows

Эти параметры управляют поведением встроенного механизма потоковой репликации (см. Подраздел 26.2.5). Когда он применяется, один сервер является ведущим, а другие — ведомыми. Ведущий сервер всегда передаёт данные, а ведомые всегда принимают данные репликации, но когда настроена каскадная репликация (см. Подраздел 26.2.7), ведомые серверы также могут быть и передающими. Большинство следующих параметров относится к передающим и ведомым серверам, но есть несколько параметров, которые имеют смысл только для ведущего. Все эти параметры вполне могут быть разными в рамках одного кластера, если это требуется.

19.6.1. Передающие серверы

Эти параметры можно задать на любом сервере, который передаёт данные репликации одному или нескольким ведомым. Ведущий сервер всегда является передающим, так что на нём они должны задаваться всегда. Роль и значение этих параметров не меняются после того, как ведомый сервер становится ведущим.

Задаёт максимально допустимое число одновременных подключений ведомых серверов или клиентов потокового копирования (т. е. максимальное количество одновременно работающих процессов передачи WAL). Значение по умолчанию — 10. Нулевое значение отключает репликацию. Процессы-передатчики WAL учитываются в общем числе соединений, так что этот параметр не может превышать max_connections. В случае неожиданного отключения клиента потоковой передачи слот подключения может оставаться занятым до достижения тайм-аута, так что этот параметр должен быть немного больше максимально допустимого числа клиентов, чтобы отключившиеся клиенты могли переподключиться немедленно. Задать этот параметр можно только при запуске сервера. Чтобы к данному серверу могли подключаться ведомые, уровень wal_level должен быть replica или выше. max_replication_slots ( integer )

Задаёт максимальное число слотов репликации (см. Подраздел 26.2.6), которое сможет поддерживать сервер. Значение по умолчанию — 10. Этот параметр можно задать только при запуске сервера. Чтобы эти слоты репликации можно было использовать, уровень wal_level должен быть replica или выше. Если заданное значение данного параметра будет меньше, чем число уже существующих слотов репликации, сервер не запустится. wal_keep_segments ( integer )

Задаёт минимальное число файлов прошлых сегментов журнала, которые будут сохраняться в каталоге pg_wal , чтобы ведомый сервер мог выбрать их при потоковой репликации. Обычно сегмент имеет размер 16 мегабайт. Если ведомый сервер, подключённый к передающему, отстаёт больше чем на wal_keep_segments сегментов, передающий удаляет сегменты WAL, всё ещё необходимые ведомому, и в этом случае соединение репликации прерывается. В результате этого затем также будут прерваны зависимые соединения. (Однако ведомый сервер сможет восстановиться, выбрав этот сегмент из архива, если осуществляется архивация WAL.)

Этот параметр задаёт только минимальное число сегментов, сохраняемое в каталоге pg_wal ; система может сохранить больше сегментов для архивации WAL или для восстановления с момента контрольной точки. Если wal_keep_segments равен нулю (это значение по умолчанию), система не сохраняет никакие дополнительные сегменты для ведомых серверов, поэтому число старых сегментов WAL, доступных для ведомых, зависит от положения предыдущей контрольной точки и состояния архивации WAL. Задать этот параметр можно только в postgresql.conf или в командной строке при запуске сервера. wal_sender_timeout ( integer )

Задаёт период времени (в миллисекундах), по истечении которого прерываются неактивные соединения репликации. Это помогает передающему серверу обнаружить сбой ведомого или разрывы сети. При значении, равном нулю, тайм-аут отключается. Задать этот параметр можно только в postgresql.conf или в командной строке при запуске сервера. Значение по умолчанию — 60 секунд. track_commit_timestamp ( boolean )

Включает запись времени фиксации транзакций. Этот параметр можно задать только в postgresql.conf или в командной строке при запуске сервера. По умолчанию этот параметр имеет значение off .

19.6.2. Главный сервер

Эти параметры можно задать на главном/ведущем сервере, который должен передавать данные репликации одному или нескольким ведомым. Заметьте, что помимо этих параметров на ведущем сервере должен быть правильно установлен wal_level, а также может быть включена архивация WAL (см. Подраздел 19.5.3). Значения этих параметров на ведомых серверах не важны, хотя их можно подготовить заранее, на случай, если ведомый сервер придётся сделать ведущим.

Определяет список ведомых серверов, которые могут поддерживать синхронную репликацию, как описано в Подразделе 26.2.8. Активных синхронных ведомых серверов может быть один или несколько; транзакции, ожидающие фиксации, будут завершаться только после того, как эти ведомые подтвердят получение их данных. Синхронными ведомыми будут те, имена которых указаны в этом списке и которые подключены к ведущему и принимают поток данных в реальном времени (что показывает признак streaming в представлении pg_stat_replication ). Указание нескольких имён ведомых серверов позволяет обеспечить очень высокую степень доступности и защиту от потери данных.

Именем ведомого сервера в этом контексте считается значение application_name этого сервера, задаваемое в свойствах подключения. При организации физической репликации оно задаётся в строке primary_conninfo в recovery.conf (по умолчанию — walreceiver ). Для логической репликации его можно задать в строке подключения для подписки (по умолчанию это имя подписки). Как задать его для других потребителей потоков репликации, вы можете узнать в их документации.

Этот параметр принимает список ведомых серверов в одной из следующих форм:

здесь число_синхронных — число синхронных ведомых серверов, от которых необходимо дожидаться ответов для завершения транзакций, а имя_ведомого — имя ведомого сервера. Слова FIRST и ANY задают метод выбора синхронных ведомых из перечисленных серверов.

Ключевое слово FIRST , в сочетании с числом_синхронных , выбирает синхронную репликацию на основе приоритетов, когда транзакции фиксируются только после того, как их записи в WAL реплицируются на число_синхронных ведомых серверов, выбираемых согласно приоритетам. Например, со значением FIRST 3 (s1, s2, s3, s4) для фиксации транзакции необходимо дождаться ответа от трёх наиболее приоритетных из серверов s1 , s2 , s3 и s4 . Ведомые серверы, имена которых идут в этом списке первыми, будут иметь больший приоритет и будут считаться синхронными. Серверы, следующие в списке за ними, будут считаться потенциальными синхронными. Если один из текущих синхронных серверов по какой-то причине отключается, он немедленно будет заменён следующим сервером с наибольшим приоритетом. Ключевое слово FIRST может быть опущено.

Ключевое слово ANY , в сочетании с числом_синхронных , выбирает синхронную репликацию на основе кворума, когда транзакции фиксируются только после того, как их записи в WAL реплицируются на как минимум число_синхронных перечисленных серверов. Например, со значением ANY 3 (s1, s2, s3, s4) для фиксации транзакции необходимо дождаться ответа от как минимум трёх из серверов s1 , s2 , s3 и s4 .

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

Третья форма использовалась в PostgreSQL до версии 9.6 и по-прежнему поддерживается. По сути она равнозначна первой с FIRST и числом_синхронным , равным 1. Например, FIRST 1 (s1, s2) и s1, s2 действуют одинаково: в качестве синхронного ведомого выбирается либо s1 , либо s2 .

Специальному элементу * соответствует имя любого ведомого.

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

Примечание

Каждое имя_ведомого должно задаваться в виде допустимого идентификатора SQL, кроме * . При необходимости его можно заключать в кавычки. Но заметьте, что идентификаторы имя_ведомого сравниваются с именами приложений без учёта регистра, независимо от того, заключены ли они в кавычки или нет.

Если имена синхронных ведомых серверов не определены, синхронная репликация не включается и фиксируемые транзакции не будут ждать репликации. Это поведение по умолчанию. Даже когда синхронная репликация включена, для отдельных транзакций можно отключить ожидание репликации, задав для параметра synchronous_commit значение local или off .

Задать этот параметр можно только в postgresql.conf или в командной строке при запуске сервера. vacuum_defer_cleanup_age ( integer )

Задаёт число транзакций, на которое будет отложена очистка старых версий строк при VACUUM и изменениях HOT . По умолчанию это число равно нулю, то есть старые версии строк могут удаляться сразу, как только перестанут быть видимыми в открытых транзакциях. Это значение можно сделать ненулевым на ведущем сервере, работающим с серверами горячего резерва, как описано в Разделе 26.5. В результате увеличится время, в течение которого будут успешно выполняться запросы на ведомом сервере без конфликтов из-за ранней очистки строк. Однако ввиду того, что эта отсрочка определяется числом записывающих транзакций, выполняющихся на ведущем сервере, сложно предсказать, каким будет дополнительное время отсрочки на ведомом сервере. Задать этот параметр можно только в postgresql.conf или в командной строке при запуске сервера.

В качестве альтернативы этому параметру можно также рассмотреть hot_standby_feedback на ведомом сервере.

Этот параметр не предотвращает очистку старых строк, которые достигли возраста, заданного параметром old_snapshot_threshold .

19.6.3. Ведомые серверы

Эти параметры управляют поведением ведомого сервера, который будет получать данные репликации. На ведущем сервере они не играют никакой роли.

Определяет, можно ли будет подключаться к серверу и выполнять запросы в процессе восстановления, как описано в Разделе 26.5. Значение по умолчанию — on (подключения разрешаются). Задать этот параметр можно только при запуске сервера. Данный параметр играет роль только в режиме ведомого сервера или при восстановлении архива. max_standby_archive_delay ( integer )

В режиме горячего резерва этот параметр определяет, как долго должен ждать ведомый сервер, прежде чем отменять запросы, конфликтующие с очередными изменениями в WAL, как описано в Подразделе 26.5.2. Задержка max_standby_archive_delay применяется при обработке данных WAL, считываемых из архива (не текущих данных). Значение этого параметра задаётся в миллисекундах (если явно не указаны другие единицы) и по умолчанию равно 30 секундам. При значении, равном -1, ведомый может ждать завершения конфликтующих запросов неограниченное время. Задать этот параметр можно только в postgresql.conf или в командной строке при запуске сервера.

Заметьте, что параметр max_standby_archive_delay определяет не максимальное время, которое отводится для выполнения каждого запроса, а максимальное общее время, за которое должны быть применены изменения из одного сегмента WAL. Таким образом, если один запрос привёл к значительной задержке при обработке сегмента WAL, остальным конфликтующим запросам будет отведено гораздо меньше времени. max_standby_streaming_delay ( integer )

В режиме горячего резерва этот параметр определяет, как долго должен ждать ведомый сервер, прежде чем отменять запросы, конфликтующие с очередными изменениями в WAL, как описано в Подразделе 26.5.2. Задержка max_standby_streaming_delay применяется при обработке данных WAL, поступающих при потоковой репликации. Значение этого параметра задаётся в миллисекундах (если явно не указаны другие единицы) и по умолчанию равно 30 секундам. При значении, равном -1, ведомый может ждать завершения конфликтующих запросов неограниченное время. Задать этот параметр можно только в postgresql.conf или в командной строке при запуске сервера.

Заметьте, что параметр max_standby_streaming_delay определяет не максимальное время, которое отводится для выполнения каждого запроса, а максимальное общее время, за которое должны быть применены изменения из WAL после получения от главного сервера. Таким образом, если один запрос привёл к значительной задержке, остальным конфликтующим запросам будет отводиться гораздо меньше времени, пока резервный сервер не догонит главный. wal_receiver_status_interval ( integer )

Определяет минимальную частоту, с которой процесс, принимающий WAL на ведомом сервере, будет сообщать о состоянии репликации ведущему или вышестоящему ведомому, где это состояние можно наблюдать в представлении pg_stat_replication . В этом сообщении передаются следующие позиции в журнале предзаписи: позиция изменений записанных, изменений, сохранённых на диске, и изменений применённых. Значение параметра задаётся в секундах и определяет максимальный интервал между сообщениями. Сообщения о состоянии передаются при каждом продвижении позиций записанных или сохранённых на диске изменений, но с промежутком не больше, чем заданный этим параметром. Таким образом, последняя переданная позиция применённых изменений может немного отставать от фактической в текущий момент. При нулевом значении этого параметра передача состояния полностью отключается. Задать этот параметр можно только в postgresql.conf или в командной строке при запуске сервера. По умолчанию его значение равно 10 секундам. hot_standby_feedback ( boolean )

Определяет, будет ли сервер горячего резерва сообщать ведущему или вышестоящему ведомому о запросах, которые он выполняет в данный момент. Это позволяет исключить необходимость отмены запросов, вызванную очисткой записей, но при некоторых типах нагрузки это может приводить к раздуванию базы данных на ведущем сервере. Эти сообщения о запросах будут отправляться не чаще, чем раз в интервал, задаваемый параметром wal_receiver_status_interval . Значение данного параметра по умолчанию — off . Задать этот параметр можно только в postgresql.conf или в командной строке при запуске сервера.

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

Этот параметр не переопределяет поведение old_snapshot_threshold , установленное на ведущем сервере; снимок на ведомом сервере, имеющий возраст больше заданного указанным параметром на ведущем, может стать недействительным, что приведёт к отмене транзакций на ведомом. Это объясняется тем, что предназначение old_snapshot_threshold заключается в указании абсолютного ограничения времени, в течение которого могут накапливаться мёртвые строки, которое иначе могло бы нарушаться из-за конфигурации ведомого. wal_receiver_timeout ( integer )

Задаёт период времени (в миллисекундах), по истечении которого прерываются неактивные соединения репликации. Это помогает принимающему ведомому серверу обнаружить сбой ведущего или разрыв сети. При значении, равном нулю, тайм-аут отключается. Задать этот параметр можно только в postgresql.conf или в командной строке при запуске сервера. Значение по умолчанию — 60 секунд. wal_retrieve_retry_interval ( integer )

Определяет, сколько ведомый сервер должен ждать поступления данных WAL из любых источников (потоковая репликация, локальный pg_wal или архив WAL), прежде чем повторять попытку получения WAL. Задать этот параметр можно только в postgresql.conf или в командной строке сервера. Значение по умолчанию — 5 секунд. Если единицы не задаются, подразумеваются миллисекунды.

Этот параметр полезен в конфигурациях, когда для узла в схеме восстановления нужно регулировать время ожидания новых данных WAL. Например, при восстановлении архива можно ускорить реакцию на появление нового файла WAL, уменьшив значение этого параметра. В системе с низкой активностью WAL увеличение этого параметра приведёт к сокращению числа запросов, необходимых для отслеживания архивов WAL, что может быть полезно в облачных окружениях, где учитывается число обращений к инфраструктуре.

19.6.4. Подписчики

Эти параметры управляют поведением подписчика логической репликации. На публикующем сервере они не играют роли.

Заметьте, что параметры конфигурации wal_receiver_timeout , wal_receiver_status_interval и wal_retrieve_retry_interval также воздействуют на рабочие процессы логической репликации.

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

Рабочие процессы логической репликации берутся из пула, контролируемого параметром max_worker_processes .

Значение по умолчанию — 4. max_sync_workers_per_subscription ( integer )

Максимальное число рабочих процессов, выполняющих синхронизацию, для одной подписки. Этот параметр управляет степенью распараллеливания копирования начальных данных в процессе инициализации подписки или при добавлении новых таблиц.

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

Рабочие процессы синхронизации берутся из пула, контролируемого параметром max_logical_replication_workers .