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

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 -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              | 15915
usesysid         | 10
usename          | postgres
application_name | replica
client_addr      | 
client_hostname  | 
client_port      | -1
backend_start    | 2019-03-31 15:24:47.086152+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.000126
flush_lag        | 00:00:00.000733
replay_lag       | 00:00:00.000807
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 -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 -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 -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