Логическая репликация между версиями PostgreSQL

Автор оригинала: Avinash Vallarapu, Fernando Laudares Camargos, Jobin Augustine, Nickolay Ihalainen
  • Перевод


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


В предыдущих статьях мы уже говорили о методах обновления PostgreSQL (Обновление версии PostgreSQL с помощью pg_dumpall и Обновление версии PostgreSQL с помощью pg_dump/pg_restore) в рамках серии Обновление или миграция старых версий PostgreSQL в новые. Но оба этих метода не исключают простоя.


Типы логической репликации


Здесь мы обсудим 2 типа репликации:


  • Репликация между PostgreSQL 10 и 11 с помощью встроенной логической репликации.
  • Репликация между PostgreSQL 9.4 (или до PG 11) и PostgreSQL 11 с помощью расширения pglogical.

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


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


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


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


Репликация между версиями PostgreSQL 10 и 11


Начиная с PostgreSQL 10 логическая репликация доступна по умолчанию. Поэтому вы легко реплицируете базу данных PostgreSQL 10 в PostgreSQL 11. При логической репликации используется модель публикации и подписки. Узел, отправляющий изменения, становится издателем. А узел, подписывающийся на эти изменения, становится подписчиком. На одну публикацию может быть несколько подписок.


Публикация


Публикация — это массив изменений, созданных группой таблиц. Она называется набором изменений или набором репликации. Публикации могут содержать только таблицы, но не другие объекты. DML в этих таблицах реплицировать можно, а DDL — нельзя.


В публикации можно выбрать, DML какого типа реплицировать: INSERT, DELETE, UPDATE или ALL. По умолчанию выбрано ALL. У таблицы должен быть идентификатор реплики, чтобы реплицировать подписчику операции UPDATE и DELETE. Идентификаторы реплики помогают находить строки, которые обновляются или удаляются.


Первичный ключ таблицы — это идентификатор реплики по умолчанию. Или вы можете сделать идентификатором уникальный индекс со значениями NOT NULL. Если у вас нет первичного ключа или уникального индекса со значениями NO NULL, установите для replica_identity значение FULL. В таком случае Postgres использует всю строку в качестве ключа. Но это не очень рационально.


Если таблица без первичного ключа и идентификатора реплики по умолчанию добавляется в публикацию после операции UPDATE или DELETE, могут возникнуть ошибки.


Подписка


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


Пример логической репликации


В следующем примере описывается логическая репликация только между версиями PostgreSQL 10 и 11.


Создайте публикацию на узле-издателе. Добавьте в публикацию все или только некоторые таблицы.


-- For adding ALL Tables in Database
CREATE PUBLICATION percpub FOR ALL TABLES;

-- For adding Selected Tables in Database
CREATE PUBLICATION percpub FOR TABLE scott.employee scott.departments;

На узле-подписчике создайте подписку на эту публикацию. Выполните DDL-дамп таблиц подписчику, прежде чем создать подписку, как говорилось выше.


$ pg_dump -h publisher_server_ip -p 5432 -d percona -Fc -s -U postgres | pg_restore -d percona -h subscriber_node_ip -p 5432 -U postgres
CREATE SUBSCRIPTION percsub CONNECTION 'host=publisher_server_ip dbname=percona user=postgres password=secret port=5432' PUBLICATION percpub;

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


CREATE SUBSCRIPTION percsub CONNECTION 'host=publisher_server_ip dbname=percona user=postgres password=oracle port=5432' PUBLICATION percpub WITH (copy_data = false);

Отслеживайте репликацию с помощью следующей команды в узле-издателе:


$ psql
\x
select * from pg_stat_replication;

Репликация между PostgreSQL 9.4 и PostgreSQL 11


Что делать с версиями до PostgreSQL 10? Для версий с 9.4 по 11 есть специальное расширение — pglogical. С помощью pglogical можно в два счета реплицировать PostgreSQL 9.4 в PostgreSQL 11.


Ниже приводятся общие инструкции по настройке репликации между PG 9.4 и PG 11 с помощью расширения pglogical.


Шаг 1. Считайте, что pgserver_94 — это исходный сервер с базой данных percona_94 на PostgreSQL 9.4. Создайте следующее расширение.
код


[pgserver_94:] $psql -d percona_94 -c "CREATE EXTENSION pglogical_origin"
CREATE EXTENSION
[pgserver_94:] $psql -d percona_94 -c "CREATE EXTENSION pglogical"
CREATE EXTENSION

Шаг 2. Теперь добавьте все или некоторые таблицы в схему или несколько схем для репликации. В следующем примере вы видите ошибку, потому что у одной из таблиц нет первичного ключа.


[pgserver_94:] $psql -d percona_94
psql (9.4.21)
Type "help" for help.
percona_94=# SELECT pglogical.create_node(node_name := 'provider1',dsn := 'host=192.168.0.24 port=5432 dbname=percona_94');
create_node
-------------
2976894835
(1 row)
percona_94=# SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);
ERROR: table pgbench_history cannot be added to replication set default
DETAIL: table does not have PRIMARY KEY and given replication set is configured to replicate UPDATEs and/or DELETEs
HINT: Add a PRIMARY KEY to the table
percona_94=# ALTER TABLE pgbench_history ADD PRIMARY KEY (tid,aid,delta);
ALTER TABLE
percona_94=# SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);
replication_set_add_all_tables
--------------------------------
t
(1 row)

Шаг 3. На узле-подписчике, то есть в базе данных PostgreSQL 11, выполните следующие команды.


[pgserver_11:] $psql -d percona_11
psql (11.2)
Type "help" for help.
percona_11=# SELECT pglogical.create_node(node_name := 'subscriber1',dsn := 'host=127.0.0.1 port=5432 dbname=percona_11 password=secret');
 create_node
-------------
   330520249
(1 row)
percona_11=# SELECT pglogical.create_subscription(subscription_name := 'subscription1',provider_dsn := 'host=192.168.0.24 port=5432 dbname=percona_94 password=secret');
 create_subscription
---------------------
          1763399739
(1 row)

Шаг 4. Затем проверьте статус репликации, отправив запрос в несколько таблиц, которые pglogical всегда обновляет:


percona_11=# select * from pglogical.local_sync_status;
 sync_kind | sync_subid | sync_nspname |   sync_relname   | sync_status | sync_statuslsn
-----------+------------+--------------+------------------+-------------+----------------
 f         | 1763399739 | public       | pgbench_accounts | r           | 0/2EB7D48
 f         | 1763399739 | public       | pgbench_history  | r           | 0/2EB7D48
 f         | 1763399739 | public       | pgbench_tellers  | r           | 0/2EB7D48
 f         | 1763399739 | public       | pgbench_branches | r           | 0/2EB7D48
 d         | 1763399739 |              |                  | r           | 0/0
(5 rows)
percona_11=# select * from pglogical.subscription;
   sub_id   |   sub_name    | sub_origin | sub_target | sub_origin_if | sub_target_if | sub_enabled |             sub_slot_name              |         sub_rep
lication_sets          | sub_forward_origins | sub_apply_delay
------------+---------------+------------+------------+---------------+---------------+-------------+----------------------------------------+----------------
-----------------------+---------------------+-----------------
 1763399739 | subscription1 | 2976894835 |  330520249 |    2402836775 |    2049915666 | t           | pgl_percona_11_provider1_subscription1 | {default,defaul
t_insert_only,ddl_sql} | {all}               | 00:00:00
(1 row)

Выбор первичного ключа


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


[pgserver_94:] $pgbench -c 10 -T 300 -n percona_94
Client 7 aborted in state 12: ERROR: duplicate key value violates unique constraint "pgbench_history_pkey"
DETAIL: Key (tid, aid, delta)=(7, 63268, 2491) already exists.

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

  • +16
  • 3,4k
  • 3
Southbridge
351,47
Обеспечиваем стабильную работу серверов
Поделиться публикацией

Похожие публикации

Комментарии 3

    0
    А не подскажите, встречались ли вы с решениями, которые реализуют следующую задачу: представим, что мы сделали копию работающего мастера1 и назвали её мастер2, тем самым мы создали грубо говоря форк основной базы данных (таких форков может быть много) далее с течением времени производится работа (без изменения схемы таблиц) как в мастере1, так и в мастере2 (то есть только добавляются новые записи, изменяются или удаляются существующие). Затем спустя какое-то время, принимается решение слить изменения сделанные в мастере2 в мастер1. Как итог — в мастере1 должны сохраниться изменения, которые были сделаны в нем ранее и примениться изменения, которые были сделаны в мастере2.
      0
      Возможно так. Сделать на серверах таблицы tbl_master1 и tbl_master2. На первом сервере рабочая таблица tbl_master1, а на втором — tbl_master2 (можно настроить через представление). И репликация tbl_master1 идет с первого на второй, а tbl_master2 — со второго на первый.
      В таблицах сделать некое поле idserv — на первом оно по-умолчанию имеет значение «1», а на втором -«2».
      Обработку реплик сделать либо отдельной процедурой в приложении, либо повесить триггер на таблицы именно на операции реплицирования. А чтоб зацикливания не произошло — проверять поле idserv, по которому определять, кто из серверов добавлял/модифицировал запись и свою же операцию не повторять по кольцу.
      Если синхронизацию надо делать не постоянно, а только эпизодически, то использовать не триггеры, а приложение, запускаемое по крону или по команде пользователя.
      Если хочется update записей без коллизий, то можно добавить либо блокировки из приложения на обоих серверах через что-то типа pg_try_advisory_lock на время правки записи, либо в таблицу сохранять начало и конец идентификаторов транзакции с обоих серверов (можно конечно время, но это не надежно), чтоб потом при накате реплицированной записи разрешать корректно имеющиеся коллизии.
      В такой схеме объединить три и более сервера уже сложнее, понадобится tbl_master3 и т.д. Если б логические репликации можно было делать между таблицами с разными именами, то у всех можно было бы использовать рабочую tbl_master, а подписки на нее со всех серверов оформлять на таблицу tbl_master_rpl.
      Кстати, последовательность для первичного ключа сделать на первом сервере нечетной, на втором — четной. Или просто задать разные диапазоны.
      Сам я делал слив таблицы с нескольких серверов на один архивный и обработку на нем реплицируемых данных с помощью триггера в другую таблицу.
      0
      Эх. Жаль, что логическая репликация не работает с фрагментированными таблицами, а создавать подписку и подписчика для каждого фрагмента муторно.

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

      Самое читаемое