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;


---

 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;


---

 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