Physical streaming synchronous replication
Physical streaming synchronous replication
postgres$ rm -rf /var/lib/postgresql/11/beta/*
postgres$ pg_basebackup --pgdata=/var/lib/postgresql/11/beta -R
postgres$ echo 'port = 5433' >> /var/lib/postgresql/11/beta/postgresql.auto.conf
postgres$ echo 'hot_standby = on' >> /var/lib/postgresql/11/beta/postgresql.auto.conf
postgres$ echo 'standby_mode = on' > /var/lib/postgresql/11/beta/recovery.conf
postgres$ echo "primary_conninfo = 'user=postgres port=5432 application_name=replica'" >> /var/lib/postgresql/11/beta/recovery.conf
student$ sudo pg_ctlcluster 11 beta start
student$ psql
α=> ALTER SYSTEM SET synchronous_commit = on;
ALTER SYSTEM
α=> ALTER SYSTEM SET synchronous_standby_names = 'replica';
ALTER SYSTEM
student$ sudo pg_ctlcluster 11 alpha reload
Physical replication check
α=> CREATE DATABASE replica_overview;
CREATE DATABASE
α=> \c replica_overview
You are now connected to database "replica_overview" as user "student".
α=> CREATE TABLE t(n integer);
CREATE TABLE
α=> INSERT INTO t VALUES (1);
INSERT 0 1
α=> SELECT * FROM pg_stat_replication \gx
-[ RECORD 1 ]----+------------------------------
pid | 23726
usesysid | 10
usename | postgres
application_name | replica
client_addr |
client_hostname |
client_port | -1
backend_start | 2019-02-27 15:39:29.351226+03
backend_xmin |
state | streaming
sent_lsn | 0/58022018
write_lsn | 0/58022018
flush_lsn | 0/58022018
replay_lsn | 0/58022018
write_lag | 00:00:00.000121
flush_lag | 00:00:00.000813
replay_lag | 00:00:00.000893
sync_priority | 1
sync_state | sync
"sync_state: sync" tells us that replication works in the synchronous mode.
student$ psql -p 5433 -d replica_overview
β=> SELECT * FROM t;
n
---
1
(1 row)
student$ sudo pg_ctlcluster 11 beta stop
α=> BEGIN;
BEGIN
α=> INSERT INTO t VALUES (2);
INSERT 0 1
α=> COMMIT;
Commit waits for the synchronous standby to appear.
student$ sudo pg_ctlcluster 11 beta start
COMMIT
student$ psql -p 5433 -d replica_overview
β=> SELECT * FROM t;
n
---
1
2
(2 rows)
Promoting
student$ sudo pg_ctlcluster 11 beta promote
Synchronous mode must be turned off on Alpha:
α=> ALTER SYSTEM RESET synchronous_standby_names;
ALTER SYSTEM
student$ sudo pg_ctlcluster 11 alpha reload
Tables for logical replication check
α=> 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
Logical replica_overview setup
α=> ALTER SYSTEM SET wal_level = logical;
ALTER SYSTEM
student$ sudo pg_ctlcluster 11 alpha restart
β=> ALTER SYSTEM SET wal_level = logical;
ALTER SYSTEM
student$ sudo pg_ctlcluster 11 beta restart
student$ psql -d replica_overview
α=> CREATE PUBLICATION a_pub FOR TABLE a;
CREATE PUBLICATION
student$ psql -p 5433 -d replica_overview
β=> CREATE PUBLICATION b_pub FOR TABLE b;
CREATE PUBLICATION
α=> CREATE SUBSCRIPTION b_sub
CONNECTION '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 'port=5432 user=postgres dbname=replica_overview'
PUBLICATION a_pub;
NOTICE: created replication slot "a_sub" on publisher
CREATE SUBSCRIPTION
Logical replication check
α=> INSERT INTO a VALUES (1);
INSERT 0 1
β=> SELECT * FROM a;
id
----
1
(1 row)
β=> INSERT INTO b VALUES ('One');
INSERT 0 1
α=> SELECT * FROM b;
s
-----
One
(1 row)
Dropping subscriptions
α=> 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