Физическая потоковая репликация в синхронном режиме

postgres$ rm -rf /usr/local/pgsql2/data/*
postgres$ pg_basebackup --pgdata=/usr/local/pgsql2/data -R
postgres$ echo 'port = 5433' >> /usr/local/pgsql2/data/postgresql.auto.conf
postgres$ echo 'hot_standby = on' >> /usr/local/pgsql2/data/postgresql.auto.conf
postgres$ echo 'standby_mode = on' > /usr/local/pgsql2/data/recovery.conf
postgres$ echo "primary_conninfo = 'user=postgres port=5432 application_name=replica'" >> /usr/local/pgsql2/data/recovery.conf
postgres$ pg_ctl -w -l /home/postgres/logfile2 -D /usr/local/pgsql2/data start
waiting for server to start.... done
server started
postgres$ psql 
=> ALTER SYSTEM SET synchronous_commit = on;
ALTER SYSTEM
=> ALTER SYSTEM SET synchronous_standby_names = 'replica';
ALTER SYSTEM
postgres$ pg_ctl -w -l /home/postgres/logfile -D /usr/local/pgsql/data reload
server signaled

Проверка физической репликации

=> CREATE DATABASE replica_overview;
CREATE DATABASE
=> \c replica_overview
You are now connected to database "replica_overview" as user "postgres".
=> CREATE TABLE t(n integer);
CREATE TABLE
=> INSERT INTO t VALUES (1);
INSERT 0 1
=> SELECT * FROM pg_stat_replication \gx
-[ RECORD 1 ]----+------------------------------
pid              | 12907
usesysid         | 10
usename          | postgres
application_name | replica
client_addr      | 
client_hostname  | 
client_port      | -1
backend_start    | 2018-12-24 17:28:56.264432+03
backend_xmin     | 
state            | streaming
sent_lsn         | 0/B01ADD8
write_lsn        | 0/B01ADD8
flush_lsn        | 0/B01ADD8
replay_lsn       | 0/B01ADD8
write_lag        | 00:00:00.000115
flush_lag        | 00:00:00.000852
replay_lag       | 00:00:00.000932
sync_priority    | 1
sync_state       | sync

sync_state: sync говорит о том, что репликация работает в синхронном режиме.

postgres$ psql -p 5433 -d replica_overview
=> SELECT * FROM t;
 n 
---
 1
(1 row)

postgres$ pg_ctl -w -l /home/postgres/logfile2 -D /usr/local/pgsql2/data stop
waiting for server to shut down.... done
server stopped
=> BEGIN;
BEGIN
=> INSERT INTO t VALUES (2);
INSERT 0 1
=> COMMIT;

Фиксация ждет появления синхронной реплики.

postgres$ pg_ctl -w -l /home/postgres/logfile2 -D /usr/local/pgsql2/data start
waiting for server to start.... done
server started

COMMIT
postgres$ psql -p 5433 -d replica_overview
=> SELECT * FROM t;
 n 
---
 1
 2
(2 rows)

Окончание восстановления

postgres$ pg_ctl -w -l /home/postgres/logfile2 -D /usr/local/pgsql2/data promote
waiting for server to promote.... done
server promoted

У первого сервера необходимо отменить синхронный режим:

=> ALTER SYSTEM RESET synchronous_standby_names;
ALTER SYSTEM
postgres$ pg_ctl -w -l /home/postgres/logfile -D /usr/local/pgsql/data reload
server signaled

Таблицы для проверки логической репликации

=> CREATE TABLE a(id integer);
CREATE TABLE
=> CREATE TABLE b(s text);
CREATE TABLE
=> CREATE TABLE a(id integer);
CREATE TABLE
=> CREATE TABLE b(s text);
CREATE TABLE

Настройка логической репликации

=> ALTER SYSTEM SET wal_level = logical;
ALTER SYSTEM
postgres$ pg_ctl -w -l /home/postgres/logfile -D /usr/local/pgsql/data restart
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started
=> ALTER SYSTEM SET wal_level = logical;
ALTER SYSTEM
postgres$ pg_ctl -w -l /home/postgres/logfile2 -D /usr/local/pgsql2/data restart
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started
postgres$ psql -d replica_overview
=> CREATE PUBLICATION a_pub FOR TABLE a;
CREATE PUBLICATION
postgres$ psql -p 5433 -d replica_overview
=> CREATE PUBLICATION b_pub FOR TABLE b;
CREATE PUBLICATION
=> CREATE SUBSCRIPTION b_sub
CONNECTION 'host=localhost port=5433 user=postgres dbname=replica_overview'
PUBLICATION b_pub;
NOTICE:  created replication slot "b_sub" on publisher
CREATE SUBSCRIPTION
=> CREATE SUBSCRIPTION a_sub
CONNECTION 'host=localhost port=5432 user=postgres dbname=replica_overview'
PUBLICATION a_pub;
NOTICE:  created replication slot "a_sub" on publisher
CREATE SUBSCRIPTION

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

=> INSERT INTO a VALUES (1);
INSERT 0 1
=> SELECT * FROM a;
 id 
----
  1
(1 row)

=> INSERT INTO b VALUES ('Раз');
INSERT 0 1
=> SELECT * FROM b;
  s  
-----
 Раз
(1 row)

Завершение логической репликации

=> DROP SUBSCRIPTION b_sub;
NOTICE:  dropped replication slot "b_sub" on publisher
DROP SUBSCRIPTION
=> DROP SUBSCRIPTION a_sub;
NOTICE:  dropped replication slot "a_sub" on publisher
DROP SUBSCRIPTION