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