PG Day’17 продолжает радовать вас авторскими статьями. Сегодня, наш старый друг и бессменный автор провокационных статей о Web-разработке varanio расскажет о логической репликации.*
Сначала я хотел назвать статью «Гарри Поттер и философский камень», потому что много лет при сравнении PostgreSQL с MySQL кто-нибудь всегда появлялся и замечал, что в Посгресе нет логической репликации (можно реплицировать только всю базу целиком, причем реплика read only), а в MySQL их целых два вида: statement based и row based.
И если statement based — это бомба замедленного действия с лазерным прицелом в ногу, то row based действительно очень не хватало в PG. Т.е. вопрос репликации — как философский камень у любителей баз.
Точнее, в посгресе всегда можно было использовать [slony](http://www.slony.info/) для того, чтобы, например, реплицировать только одну-две нужных таблицы. Но 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оздадим таблицу и «публикацию»:
«`sql
CREATE TABLE repl (
id int,
name text,
primary key(id)
);
CREATE PUBLICATION testpub;
«`
Добавляем к публикации все необходимые таблицы (в данном случае — одну):
«`sql
ALTER PUBLICATION testpub ADD TABLE repl;
«`
Теперь на стороне слейва:
«`
/usr/local/pgsql/bin/psql -p 5434
«`
Тоже создадим таблицу:
«`sql
CREATE TABLE repl (
id int,
name text,
primary key(id)
);
«`
Теперь надо создать подписку на публикацию, в которой указываем строку коннекта до другой базы и имя PUBLICATION:
«`sql
CREATE SUBSCRIPTION testsub CONNECTION ‘port=5433 dbname=postgres’ PUBLICATION testpub;
«`
Проверяем
===============
Вставляем на мастере:
«`sql
INSERT INTO repl (id, name) VALUES (1, ‘Вася’);
«`
Читаем на реплике:
«`sql
postgres=# select * from repl;
id | name
—-+——
1 | Вася
(1 row)
«`
It works!
Теперь остановим реплику:
«`
/usr/local/pgsql/bin/pg_ctl stop -D ~/slave
«`
На мастере сделаем:
«`sql
delete from repl;
insert into repl (id, name) values (10, ‘test’);
«`
Запускаем слейв и проверяем:
«`
/usr/local/pgsql/bin/pg_ctl start -D ~/slave -l ~/slave.log
«`
«`sql
postgres=# select * from repl;
id | name
—-+——
10 | test
(1 row)
«`
Всё сработало.
Более того, я добавил новую колонку на мастер и на слейв, вставил записи, и это тоже сработало.
Если вы хотите узнать больше примеров использования, посмотрите в исходниках файл `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, то, чтобы удалять из нее значения, надо написать:
«`sql
ALTER TABLE tablename REPLICA IDENTITY FULL
«`
Я не знаю, как это работает, видимо генерятся какие-то id на лету. Если у кого-то есть больше информации по логической репликации, поделитесь плиз в коментах.
Выводы
==========
Вывод очень прост: я очень жду PostgreSQL 10 в состоянии production-ready, так как это решит целый пласт организационных проблем (можно будет выкинуть slony). Для кого-то, возможно, это будет последней каплей для перехода с MySQL на Postgres.
С другой стороны, как это будет работать на практике, пока что никто не знает. Будет ли это достаточно быстро, удобно в обслуживании и так далее. Если у кого-то есть больше информации по теме, поделитесь плиз в коментах.
А пока мы ждем PostgreSQL 10, наверняка, у вас много вопросов по дрессировке текущих методов репликации. На [**PG Day’17**](https://pgday.ru/ru/2017/request/registration?utm_source=habrahabr&utm_medium=blog&utm_campaign=varanio2) вас ждет большое количество интересных докладов и мастер-классов по PostgreSQL. Например, **Илья Космодемьянский** расскажет все о настройке ПГ, обработке транзакций, автовакууме и, конечно, подскажет, как избежать распространенных ошибок. Спешите [**зарегистрироваться**](https://pgday.ru/ru/2017/request/registration?utm_source=habrahabr&utm_medium=blog&utm_campaign=varanio2)!
ссылка на оригинал статьи https://habrahabr.ru/post/326996/
Добавить комментарий