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 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;

---

(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;

----

(1 row)

β=> INSERT INTO b VALUES ('One');

INSERT 0 1

α=> SELECT * FROM b;

-----

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