Логическая репликация в 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. Например, Илья Космодемьянский расскажет все о настройке ПГ, обработке транзакций, автовакууме и, конечно, подскажет, как избежать распространенных ошибок. Спешите зарегистрироваться!

    PG Day'17 Russia 34,43
    Компания
    Поделиться публикацией
    Комментарии 12
    • +1
      ыыы опередил меня)))
      • +1

        Двое нас :-)

        • +1
          Извиняйте, мужики )
        • +8
          Как я понял вот что будет в 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

          Можно же ведь и на другую тему написать статью.
      • +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
                я попробую на выходных

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

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