20 апреля в 14:59

Логическая репликация в PostgreSQL 10

PG Day’17 продолжает радовать вас авторскими статьями. Сегодня, наш старый друг и бессменный автор провокационных статей о Web-разработке varanio расскажет о логической репликации.


Сначала я хотел назвать статью "Гарри Поттер и философский камень", потому что много лет при сравнении PostgreSQL с MySQL кто-нибудь всегда появлялся и замечал, что в Посгресе нет логической репликации (можно реплицировать только всю базу целиком, причем реплика read only), а в MySQL их целых два вида: statement based и row based.


И если statement based — это бомба замедленного действия с лазерным прицелом в ногу, то row based действительно очень не хватало в PG. Т.е. вопрос репликации — как философский камень у любителей баз.


Точнее, в посгресе всегда можно было использовать slony для того, чтобы, например, реплицировать только одну-две нужных таблицы. Но slony — это хитрое поделие на триггерах, которое работает по принципу: работает — не трогай. Т.е. например, нельзя просто взять и сделать ALTER TABLE ADD COLUMN, это надо делать через специальные механизмы. Если же всё-таки кто-то случайно это сделал, а потом, что еще хуже, через какое-то время в панике вернул как было, то быстро разрулить эту ситуацию может только чёрный маг 80lvl. Помимо slony, начиная с 9.4 стало возможно писать свои расширения для логической репликации через wal, вроде бы, пример такого расширения — pglogical.


Но это всё не то!


Когда я узнал, что в dev-ветку PostgreSQL 10 упал коммит, который позволяет из коробки, без экстеншенов и плагинов, логически реплицировать отдельные таблицы, я решил посмотреть, а как оно там работает.


Ставим PostgreSQL из исходников на убунту


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


sudo apt-get install avada kedavra expelliarmus
sudo apt-get install -y build-essential libreadline-dev zlib1g-dev flex bison libxml2-dev libxslt-dev libssl-dev


Качаем исходники:


git clone git://git.postgresql.org/git/postgresql.git


Собираем все это дело:


cd postgresql
./configure 
make 
make install 

Я написал make install, это безвозвратно загадит ваш /usr/local, так что лучше это делайте в вируалке или докер-контейнере, или же спросите настоящего сварщика, как это сделать аккуратно. Я совершенно не админ, так что извините.


Запускаем тестовые демоны


Зайдем под юзером postgres. Если у вас его еще нет, то создайте. Если есть, но не залогиниться под ним, то наверно просто не задан пароль, тогда надо сделать sudo passwd postgres. Итак, зайдем под юзером postgres:


su - postgres


Создадим где-нибудь папки master и slave и проинитим там бд:


/usr/local/pgsql/bin/initdb -D ~/master
/usr/local/pgsql/bin/initdb -D ~/slave

Т.е. у нас будет два локальных демона pg, которые будут друг другу реплицировать отдельные таблицы. Пусть один будет работать на порту 5433, другой — на 5434.


Для этого надо вписать в ~/master/postgresql.conf строку port = 5433, в ~/slave/postgresql.conf — строку port = 5434, соответственно.


В обоих конфигах postgresql.conf надо указать:


wal_level = logical 

Кроме того, чтобы репликация работала, надо раскомментировать строчку в pg_hba.conf:


local   replication     postgres                                trust

Запускаем оба демона:


/usr/local/pgsql/bin/pg_ctl start -D ~/master -l ~/master.log
/usr/local/pgsql/bin/pg_ctl start -D ~/slave -l ~/slave.log

Настраиваем репликацию


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


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


/usr/local/pgsql/bin/psql -p 5433

Cоздадим таблицу и "публикацию":


CREATE TABLE repl (
   id int, 
   name text, 
   primary key(id)
);
CREATE PUBLICATION testpub;

Добавляем к публикации все необходимые таблицы (в данном случае — одну):


ALTER PUBLICATION testpub ADD TABLE repl;

Теперь на стороне слейва:


/usr/local/pgsql/bin/psql -p 5434

Тоже создадим таблицу:


CREATE TABLE repl (
    id int, 
    name text, 
    primary key(id)
);

Теперь надо создать подписку на публикацию, в которой указываем строку коннекта до другой базы и имя PUBLICATION:


CREATE SUBSCRIPTION testsub CONNECTION 'port=5433 dbname=postgres' PUBLICATION testpub;

Проверяем


Вставляем на мастере:


INSERT INTO repl (id, name) VALUES (1, 'Вася');

Читаем на реплике:


postgres=# select * from repl;
 id | name 
----+------
  1 | Вася
(1 row)

It works!


Теперь остановим реплику:


/usr/local/pgsql/bin/pg_ctl stop -D ~/slave

На мастере сделаем:


delete from repl;
 insert into repl (id, name) values (10, 'test');

Запускаем слейв и проверяем:


/usr/local/pgsql/bin/pg_ctl start -D ~/slave -l ~/slave.log

postgres=# select * from repl;
 id | name 
----+------
 10 | test
(1 row)

Всё сработало.


Более того, я добавил новую колонку на мастер и на слейв, вставил записи, и это тоже сработало.


Если вы хотите узнать больше примеров использования, посмотрите в исходниках файл src/test/subscription/t/001_rep_changes.pl. Он на перле, но там всё понятно.


src/test/subscription/t/001_rep_changes.pl
# Basic logical replication test
use strict;
use warnings;
use PostgresNode;
use TestLib;
use Test::More tests => 11;

# Initialize publisher node
my $node_publisher = get_new_node('publisher');
$node_publisher->init(allows_streaming => 'logical');
$node_publisher->start;

# Create subscriber node
my $node_subscriber = get_new_node('subscriber');
$node_subscriber->init(allows_streaming => 'logical');
$node_subscriber->start;

# Create some preexisting content on publisher
$node_publisher->safe_psql('postgres',
    "CREATE TABLE tab_notrep AS SELECT generate_series(1,10) AS a");
$node_publisher->safe_psql('postgres',
    "CREATE TABLE tab_ins (a int)");
$node_publisher->safe_psql('postgres',
    "CREATE TABLE tab_full AS SELECT generate_series(1,10) AS a");
$node_publisher->safe_psql('postgres',
    "CREATE TABLE tab_rep (a int primary key)");

# Setup structure on subscriber
$node_subscriber->safe_psql('postgres',
    "CREATE TABLE tab_notrep (a int)");
$node_subscriber->safe_psql('postgres',
    "CREATE TABLE tab_ins (a int)");
$node_subscriber->safe_psql('postgres',
    "CREATE TABLE tab_full (a int)");
$node_subscriber->safe_psql('postgres',
    "CREATE TABLE tab_rep (a int primary key)");

# Setup logical replication
my $publisher_connstr = $node_publisher->connstr . ' dbname=postgres';
$node_publisher->safe_psql('postgres',
    "CREATE PUBLICATION tap_pub");
$node_publisher->safe_psql('postgres',
    "CREATE PUBLICATION tap_pub_ins_only WITH (nopublish delete, nopublish update)");
$node_publisher->safe_psql('postgres',
    "ALTER PUBLICATION tap_pub ADD TABLE tab_rep, tab_full");
$node_publisher->safe_psql('postgres',
    "ALTER PUBLICATION tap_pub_ins_only ADD TABLE tab_ins");

my $appname = 'tap_sub';
$node_subscriber->safe_psql('postgres',
    "CREATE SUBSCRIPTION tap_sub CONNECTION '$publisher_connstr application_name=$appname' PUBLICATION tap_pub, tap_pub_ins_only");

# Wait for subscriber to finish initialization
my $caughtup_query =
"SELECT pg_current_xlog_location() <= replay_location FROM pg_stat_replication WHERE application_name = '$appname';";
$node_publisher->poll_query_until('postgres', $caughtup_query)
  or die "Timed out while waiting for subscriber to catch up";

my $result =
  $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM tab_notrep");
is($result, qq(0), 'check non-replicated table is empty on subscriber');

$node_publisher->safe_psql('postgres',
    "INSERT INTO tab_ins SELECT generate_series(1,50)");
$node_publisher->safe_psql('postgres',
    "DELETE FROM tab_ins WHERE a > 20");
$node_publisher->safe_psql('postgres',
    "UPDATE tab_ins SET a = -a");

$node_publisher->safe_psql('postgres',
    "INSERT INTO tab_rep SELECT generate_series(1,50)");
$node_publisher->safe_psql('postgres',
    "DELETE FROM tab_rep WHERE a > 20");
$node_publisher->safe_psql('postgres',
    "UPDATE tab_rep SET a = -a");

$node_publisher->poll_query_until('postgres', $caughtup_query)
  or die "Timed out while waiting for subscriber to catch up";

$result =
  $node_subscriber->safe_psql('postgres', "SELECT count(*), min(a), max(a) FROM tab_ins");
is($result, qq(50|1|50), 'check replicated inserts on subscriber');

$result =
  $node_subscriber->safe_psql('postgres', "SELECT count(*), min(a), max(a) FROM tab_rep");
is($result, qq(20|-20|-1), 'check replicated changes on subscriber');

# insert some duplicate rows
$node_publisher->safe_psql('postgres',
    "INSERT INTO tab_full SELECT generate_series(1,10)");

# add REPLICA IDENTITY FULL so we can update
$node_publisher->safe_psql('postgres',
    "ALTER TABLE tab_full REPLICA IDENTITY FULL");
$node_subscriber->safe_psql('postgres',
    "ALTER TABLE tab_full REPLICA IDENTITY FULL");
$node_publisher->safe_psql('postgres',
    "ALTER TABLE tab_ins REPLICA IDENTITY FULL");
$node_subscriber->safe_psql('postgres',
    "ALTER TABLE tab_ins REPLICA IDENTITY FULL");

# and do the update
$node_publisher->safe_psql('postgres',
    "UPDATE tab_full SET a = a * a");

# Wait for subscription to catch up
$node_publisher->poll_query_until('postgres', $caughtup_query)
  or die "Timed out while waiting for subscriber to catch up";

$result =
  $node_subscriber->safe_psql('postgres', "SELECT count(*), min(a), max(a) FROM tab_full");
is($result, qq(10|1|100), 'update works with REPLICA IDENTITY FULL and duplicate tuples');

# check that change of connection string and/or publication list causes
# restart of subscription workers. Not all of these are registered as tests
# as we need to poll for a change but the test suite will fail none the less
# when something goes wrong.
my $oldpid = $node_publisher->safe_psql('postgres',
    "SELECT pid FROM pg_stat_replication WHERE application_name = '$appname';");
$node_subscriber->safe_psql('postgres',
    "ALTER SUBSCRIPTION tap_sub CONNECTION 'application_name=$appname $publisher_connstr'");
$node_publisher->poll_query_until('postgres',
    "SELECT pid != $oldpid FROM pg_stat_replication WHERE application_name = '$appname';")
  or die "Timed out while waiting for apply to restart";

$oldpid = $node_publisher->safe_psql('postgres',
    "SELECT pid FROM pg_stat_replication WHERE application_name = '$appname';");
$node_subscriber->safe_psql('postgres',
    "ALTER SUBSCRIPTION tap_sub SET PUBLICATION tap_pub_ins_only");
$node_publisher->poll_query_until('postgres',
    "SELECT pid != $oldpid FROM pg_stat_replication WHERE application_name = '$appname';")
  or die "Timed out while waiting for apply to restart";

$node_publisher->safe_psql('postgres',
    "INSERT INTO tab_ins SELECT generate_series(1001,1100)");
$node_publisher->safe_psql('postgres',
    "DELETE FROM tab_rep");

$node_publisher->poll_query_until('postgres', $caughtup_query)
  or die "Timed out while waiting for subscriber to catch up";

$result =
  $node_subscriber->safe_psql('postgres', "SELECT count(*), min(a), max(a) FROM tab_ins");
is($result, qq(150|1|1100), 'check replicated inserts after subscription publication change');

$result =
  $node_subscriber->safe_psql('postgres', "SELECT count(*), min(a), max(a) FROM tab_rep");
is($result, qq(20|-20|-1), 'check changes skipped after subscription publication change');

# check alter publication (relcache invalidation etc)
$node_publisher->safe_psql('postgres',
    "ALTER PUBLICATION tap_pub_ins_only WITH (publish delete)");
$node_publisher->safe_psql('postgres',
    "ALTER PUBLICATION tap_pub_ins_only ADD TABLE tab_full");
$node_publisher->safe_psql('postgres',
    "DELETE FROM tab_ins WHERE a > 0");
$node_publisher->safe_psql('postgres',
    "INSERT INTO tab_full VALUES(0)");

$node_publisher->poll_query_until('postgres', $caughtup_query)
  or die "Timed out while waiting for subscriber to catch up";

# note that data are different on provider and subscriber
$result =
  $node_subscriber->safe_psql('postgres', "SELECT count(*), min(a), max(a) FROM tab_ins");
is($result, qq(50|1|50), 'check replicated deletes after alter publication');

$result =
  $node_subscriber->safe_psql('postgres', "SELECT count(*), min(a), max(a) FROM tab_full");
is($result, qq(11|0|100), 'check replicated insert after alter publication');

# check all the cleanup
$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub");

$result =
  $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM pg_subscription");
is($result, qq(0), 'check subscription was dropped on subscriber');

$result =
  $node_publisher->safe_psql('postgres', "SELECT count(*) FROM pg_replication_slots");
is($result, qq(0), 'check replication slot was dropped on publisher');

$result =
  $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM pg_replication_origin");
is($result, qq(0), 'check replication origin was dropped on subscriber');

$node_subscriber->stop('fast');
$node_publisher->stop('fast');

В частности, если создать таблицу без primary key, то, чтобы удалять из нее значения, надо написать:


ALTER TABLE tablename REPLICA IDENTITY FULL

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


Выводы


Вывод очень прост: я очень жду PostgreSQL 10 в состоянии production-ready, так как это решит целый пласт организационных проблем (можно будет выкинуть slony). Для кого-то, возможно, это будет последней каплей для перехода с MySQL на Postgres.


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


А пока мы ждем PostgreSQL 10, наверняка, у вас много вопросов по дрессировке текущих методов репликации. На PG Day'17 вас ждет большое количество интересных докладов и мастер-классов по PostgreSQL. Например, Илья Космодемьянский расскажет все о настройке ПГ, обработке транзакций, автовакууме и, конечно, подскажет, как избежать распространенных ошибок. Спешите зарегистрироваться!

Автор: @rdruzyagin
PG Day'17 Russia
рейтинг 174,33

Комментарии (12)

  • +1
    ыыы опередил меня)))
    • +1

      Двое нас :-)

      • +1
        Извиняйте, мужики )
    • +7
      Как я понял вот что будет в PostgreSQL «10.0» (в работе)
      ● BDR — двунаправленная репликация
      http://2ndquadrant.com/en/resources/bdr/
      ● Pglogical (5x быстрее slony, londiste3)
      http://2ndquadrant.com/en/resources/pglogical/
      ● Declarative partitioning (+pg_pathman)
      ● Highly Available multi-master
      ● Инкрементальный бэкап
      ● Миллисекундный полнотекстовый поиск
      ● In-memory

      Можно же ведь и на другую тему написать статью.
      • 0

        Муза — женщина ветренная :-)

      • +1
        Круто!!!
  • +1

    Спасибо за статью. Пара вопросов — я правильно понял, что логическая репликация в слейв будет работать начиная со времени подписки на мастер? И совмещать PITR и логическую репликацию нельзя?

  • 0

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

    • 0
      Тоже жду не дождусь
  • 0

    Насколько я слышал, логическая репликация в 10-ке — это обкатанный на 9.4-9.6 pglogical, который теперь не расширение, а прямо в ядре (плюс новые операции в SQL'е, а не страшные функции для настройки, как видно из статьи).


    Я пытался завести pglogical на 9.5 и у меня не получилось нормально синхронизировать данные мастера и новой реплики. Т.е. мы берём, дампим мастер (pg_dump, pg_basebackup, что угодно), разворачиваем реплику из дампа и настраиваем репликацию, но вот как быть с изменениями, произошедшими на мастере в промежутке между началом дампа и запуском репликации? Как это решается в 10-ке? У pglogical, кстати, документация тоже не бог весть какая подробная. Возможно, что начиная с 9.6 меня бы спасли слоты репликации, но в 9.5 их ещё не было.

    • 0
      тут же присоединюсь с вопросом о delayed replication. можно так делать?
      • 0
        я попробую на выходных

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

Самое читаемое Разработка