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

Let's block ads! (Why?)