Starting from version 10 all necessary parameter's values are already set by default:
We'll start with a standalone backup. Destination of the backup is the PGDATA directory of the backup server (beta). The -R argument will create a draft of the recovery.conf control file.
postgres$ rm -rf /var/lib/postgresql/11/beta/*
postgres$ pg_basebackup --pgdata=/var/lib/postgresql/11/beta -R
The backup server is already built and installed in the VM.
We are going to have both primary and backup servers running on the same hardware, so we need to change the port number:
postgres$ echo 'port = 5433' >> /var/lib/postgresql/11/beta/postgresql.auto.conf
Also we are adding the hot_standby parameter, which tells the server to accept connections during recovery:
postgres$ echo 'hot_standby = on' >> /var/lib/postgresql/11/beta/postgresql.auto.conf
A draft of the recovery.conf file was created automatically by pg_basebackup, and it suits us:
postgres$ cat /var/lib/postgresql/11/beta/recovery.conf
standby_mode = 'on' primary_conninfo = 'user=postgres passfile=''/var/lib/postgresql/.pgpass'' host=''/var/run/postgresql'' port=5432 sslmode=prefer sslcompression=0 krbsrvname=postgres target_session_attrs=any'
We are ready to start the server.
student$ sudo pg_ctlcluster 11 beta start
Let's have a look at the processes of the standby server.
postgres$ ps -o pid,command --ppid `head -n 1 /var/lib/postgresql/11/beta/postmaster.pid`
PID COMMAND 18466 postgres: 11/beta: startup recovering 00000001000000000000004F 18467 postgres: 11/beta: checkpointer 18468 postgres: 11/beta: background writer 18469 postgres: 11/beta: stats collector 18470 postgres: 11/beta: walreceiver streaming 0/4F017438
The wal receiver process receives WAL records via replication protocol, and the startup process applies them.
Compare with the primary server.
postgres$ ps -o pid,command --ppid `head -n 1 /var/lib/postgresql/11/alpha/postmaster.pid`
PID COMMAND 9260 postgres: 11/alpha: checkpointer 9261 postgres: 11/alpha: background writer 9262 postgres: 11/alpha: walwriter 9263 postgres: 11/alpha: autovacuum launcher 9264 postgres: 11/alpha: stats collector 9265 postgres: 11/alpha: logical replication launcher 18360 postgres: 11/alpha: student student [local] idle 18471 postgres: 11/alpha: walsender postgres [local] streaming 0/4F017438
Here we can see the wal sender process.
The state of replication is shown in the following view on the primary:
student$ psql -p 5432
α=> SELECT * FROM pg_stat_replication \gx
-[ RECORD 1 ]----+------------------------------ pid | 18471 usesysid | 10 usename | postgres application_name | walreceiver client_addr | client_hostname | client_port | -1 backend_start | 2019-02-27 15:38:32.696414+03 backend_xmin | state | streaming sent_lsn | 0/4F017438 write_lsn | 0/4F017438 flush_lsn | 0/4F017438 replay_lsn | 0/4F017438 write_lag | 00:00:00.000172 flush_lag | 00:00:00.00353 replay_lag | 00:00:00.003579 sync_priority | 0 sync_state | async
Let's perform some statements on the primary:
α=> CREATE DATABASE replica_overview;
CREATE DATABASE
α=> \c replica_overview;
You are now connected to database "replica_overview" as user "student".
α=> CREATE TABLE test(id integer PRIMARY KEY, descr text);
CREATE TABLE
And check the standby:
student$ psql -p 5433 -d replica_overview
β=> SELECT * FROM test;
id | descr ----+------- (0 rows)
α=> INSERT INTO test VALUES (1, 'One');
INSERT 0 1
β=> SELECT * FROM test;
id | descr ----+------- 1 | One (1 row)
The standby server does not accept any modifications of data:
β=> INSERT INTO test VALUES (2, 'Two');
ERROR: cannot execute INSERT in a read-only transaction
Now let's promote the standby. Thus we'll get two independent servers.
student$ sudo pg_ctlcluster 11 beta promote
β=> INSERT INTO test VALUES (2, 'Two');
INSERT 0 1
The next step is to setup logical replication between the servers. We have to change the log level for it.
α=> ALTER SYSTEM SET wal_level = logical;
ALTER SYSTEM
student$ sudo pg_ctlcluster 11 alpha restart
On Alpha we create a publication:
student$ psql -d replica_overview
α=> CREATE PUBLICATION test_pub FOR TABLE test;
CREATE PUBLICATION
α=> \dRp+
Publication test_pub
Owner | All tables | Inserts | Updates | Deletes | Truncates
---------+------------+---------+---------+---------+-----------
student | f | t | t | t | t
Tables:
"public.test"
On Beta we subscribe to the publication. Initial synchronization is not used in this case:
β=> CREATE SUBSCRIPTION test_sub CONNECTION 'port=5432 user=postgres dbname=replica_overview' PUBLICATION test_pub WITH (copy_data = false);
NOTICE: created replication slot "test_sub" on publisher CREATE SUBSCRIPTION
β=> \dRs
List of subscriptions
Name | Owner | Enabled | Publication
----------+---------+---------+-------------
test_sub | student | t | {test_pub}
(1 row)
α=> INSERT INTO test VALUES (3, 'Three');
INSERT 0 1
β=> SELECT * FROM test;
id | descr ----+------- 1 | One 2 | Two 3 | Three (3 rows)
The state of subscription is shown in the view:
β=> SELECT * FROM pg_stat_subscription \gx
-[ RECORD 1 ]---------+------------------------------ subid | 25743 subname | test_sub pid | 18990 relid | received_lsn | 0/4F049CA4 last_msg_send_time | last_msg_receipt_time | 2019-02-27 15:38:41.101637+03 latest_end_lsn | 0/4F049B48 latest_end_time | 2019-02-27 15:38:41.013193+03
There is one more process now, logical replication worker (its identifier was shown in pg_stat_subscription.pid):
postgres$ ps -o pid,command --ppid `head -n 1 /var/lib/postgresql/11/beta/postmaster.pid`
PID COMMAND 18467 postgres: 11/beta: checkpointer 18468 postgres: 11/beta: background writer 18469 postgres: 11/beta: stats collector 18722 postgres: 11/beta: student replica_overview [local] idle 18814 postgres: 11/beta: walwriter 18815 postgres: 11/beta: autovacuum launcher 18816 postgres: 11/beta: logical replication launcher 18990 postgres: 11/beta: logical replication worker for subscription 25743
When replication is no longer needed, the subscription should be dropped to close the previously opened replication slot on the publisher.
β=> DROP SUBSCRIPTION test_sub;
NOTICE: dropped replication slot "test_sub" on publisher DROP SUBSCRIPTION
The end of demonstration.