This article covers the new feature "Allow logical replication from standby servers", introduced in EPAS 16.
This feature allows users to stream data to other PostgreSQL instances, giving developers new options for workload distribution. Additionally, it enables standby servers to publish logical changes to other servers. We can setup logical replication from standby server which was not possible in versions below 16. This is the most useful features in PostgreSQL 16 that adds the ability to perform logical replication from physical replication standbys.
In this example, for simplicity, we have 3 EPAS instances running on the same server, on three different ports. The same can be achieved using 3 different servers.
- Primary server listening to port
5444
and data directorydata
; - Standby server listening to port
5445
and data directorystandby_data1
, used for streaming replication; - Subscriber server listening to port
5446
and data directorylogical_data
, used for logical replication.
In the postgresql.conf
file, we need to enable the following parameters:
$ vim /var/lib/edb/as16/data/postgresql.conf
wal_level = logical
hot_standby = on
max_wal_senders = 10
max_replication_slots = 10
hot_standby_feedback = on
Add HBA rules to allow connections, by editing file /var/lib/edb/as16/data/pg_hba.conf
.
Restart Postgres:
$ /usr/edb/as16/bin/pg_ctl -D /var/lib/edb/as16/data restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2024-06-10 00:58:29 PDT LOG: redirecting log output to logging collector process
2024-06-10 00:58:29 PDT HINT: Future log output will appear in directory "log".
done
server started
Create 2 users with replication privileges. One is for streaming the changes to the standby, and another to publish changes to the subscribers:
$ /usr/edb/as16/bin/psql -d edb
psql (16.3.0)
Type "help" for help.
edb=#
edb=# create user repuser with replication password 'edb';
CREATE ROLE
edb=# create user pubuser with replication password 'edb';
CREATE ROLE
edb=#
edb=# \du
List of roles
Role name | Attributes
-----------------------+------------------------------------------------------------
aq_administrator_role | No inheritance, Cannot login +
| Profile default
capture_admin | No inheritance, Cannot login +
| Profile default
enterprisedb | Superuser, Create role, Create DB, Replication, Bypass RLS+
| Profile default
pubuser | Replication +
| Profile default
repuser | Replication +
| Profile default
Create the replication slot:
edb=# select pg_create_physical_replication_slot('primary');
pg_create_physical_replication_slot
-------------------------------------
(primary,)
(1 row)
Create sample tables to publish:
edb=# create table t1(id int);
CREATE TABLE
edb=# drop table t1 ;
DROP TABLE
edb=# create table t1(id int primary key, name char(20));
CREATE TABLE
edb=# create table t2(id int primary key, name char(20));
CREATE TABLE
edb=# insert into t1 values (1, 'supriya');
INSERT 0 1
edb=# insert into t2 values (1, 'payal');
INSERT 0 1
edb=# table t1;
id | name
----+----------------------
1 | supriya
(1 row)
edb=# table t2;
id | name
----+----------------------
1 | payal
(1 row)
edb=# GRANT SELECT ON TABLE t1,t2 TO pubuser;
GRANT
Create the publication:
edb=# create publication testpub for all tables;
CREATE PUBLICATION
Check the replication slot and other details:
edb=# select * from pg_publication;
oid | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot
-------+---------+----------+--------------+-----------+-----------+-----------+-------------+------------
16399 | testpub | 10 | t | t | t | t | t | f
(1 row)
edb=# select * from pg_publication
pg_publication pg_publication_namespace pg_publication_rel pg_publication_tables
edb=# select * from pg_publication_tables ;
pubname | schemaname | tablename | attnames | rowfilter
---------+------------+-----------+-----------+-----------
testpub | public | t1 | {id,name} |
testpub | public | t2 | {id,name} |
(2 rows)
edb=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+--------------
public | t1 | table | enterprisedb
public | t2 | table | enterprisedb
(2 rows)
edb=#
edb=# select * from pg_replication_slots ;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size | two_phase
| conflicting
-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+------------+---------------+-----------
+-------------
primary | | physical | | | f | f | | | | | | | | f
|
(1 row)
edb=# \q
Setup streaming replication by taking pg_basebackup
:
$ /usr/edb/as16/bin/pg_basebackup -h localhost -U enterprisedb -p 5444 -D /var/lib/edb/as16/standby_data1 -X stream -RP
59051/59051 kB (100%), 1/1 tablespace
Change port to 5445
and start the server:
$ /usr/edb/as16/bin/pg_ctl -D /var/lib/edb/as16/standby_data1 start
LOG: redirecting log output to logging collector process
HINT: Future log output will appear in directory "log".
done
server started
Check whether it's in recovery:
$ /usr/edb/as16/bin/psql -d edb -p 5445
psql (16.3.0)
Type "help" for help.
edb=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
edb=# \q
Initialize an empty data directory:
$ /usr/edb/as16/bin/initdb -D /var/lib/edb/as16/logical_data
Change port to 5446
and start the server:
$ /usr/edb/as16/bin/pg_ctl -D /var/lib/edb/as16/logical_data start
LOG: redirecting log output to logging collector process
HINT: Future log output will appear in directory "log".
done
server started
We have now the 3 Postgres instances running on this server. Standby is replicating from the Primary, and the Subscriber instance is still empty:
-bash-4.2$ ps -ef | grep post
root 1504 1 0 Jun09 ? 00:00:00 /usr/libexec/postfix/master -w
postfix 1520 1504 0 Jun09 ? 00:00:00 qmgr -l -t unix -u
enterpr+ 11361 1 0 01:22 ? 00:00:00 /usr/edb/as16/bin/edb-postgres -D /var/lib/edb/as16/data
enterpr+ 11362 11361 0 01:22 ? 00:00:00 postgres: logger
enterpr+ 11363 11361 0 01:22 ? 00:00:00 postgres: checkpointer
enterpr+ 11364 11361 0 01:22 ? 00:00:00 postgres: background writer
enterpr+ 11366 11361 0 01:22 ? 00:00:00 postgres: walwriter
enterpr+ 11367 11361 0 01:22 ? 00:00:00 postgres: autovacuum launcher
enterpr+ 11368 11361 0 01:22 ? 00:00:00 postgres: dbms_aq launcher
enterpr+ 11369 11361 0 01:22 ? 00:00:00 postgres: logical replication launcher
enterpr+ 11467 1 0 01:24 ? 00:00:00 /usr/edb/as16/bin/edb-postgres -D /var/lib/edb/as16/standby_data1
enterpr+ 11468 11467 0 01:24 ? 00:00:00 postgres: logger
enterpr+ 11469 11467 0 01:24 ? 00:00:00 postgres: checkpointer
enterpr+ 11470 11467 0 01:24 ? 00:00:00 postgres: background writer
enterpr+ 11471 11467 0 01:24 ? 00:00:00 postgres: startup recovering 000000010000000000000007
enterpr+ 11472 11467 0 01:24 ? 00:00:01 postgres: walreceiver streaming 0/7000148
enterpr+ 11473 11361 0 01:24 ? 00:00:00 postgres: walsender enterprisedb 192.168.84.9(33466) streaming 0/7000148
postfix 12389 1504 0 02:15 ? 00:00:00 pickup -l -t unix -u
enterpr+ 13749 1 0 03:43 ? 00:00:00 /usr/edb/as16/bin/edb-postgres -D /var/lib/edb/as16/logical_data
enterpr+ 13750 13749 0 03:43 ? 00:00:00 postgres: logger
enterpr+ 13751 13749 0 03:43 ? 00:00:00 postgres: checkpointer
enterpr+ 13752 13749 0 03:43 ? 00:00:00 postgres: background writer
enterpr+ 13754 13749 0 03:43 ? 00:00:00 postgres: walwriter
enterpr+ 13755 13749 0 03:43 ? 00:00:00 postgres: autovacuum launcher
enterpr+ 13756 13749 0 03:43 ? 00:00:00 postgres: dbms_aq launcher
enterpr+ 13757 13749 0 03:43 ? 00:00:00 postgres: logical replication launcher
enterpr+ 13763 11582 0 03:44 pts/0 00:00:00 grep --color=auto post
-bash-4.2$
Take the global dump of the primary database and restore it here. Make sure the pg_hba.conf
file is adjusted before this command:
-bash-4.2$ /usr/edb/as16/bin/pg_dumpall -h localhost -p 5444 -U enterprisedb -g | /usr/edb/as16/bin/psql -d edb -h localhost -p 5446 -U enterprisedb
SET
SET
SET
ALTER PROFILE
ERROR: role "enterprisedb" already exists
ALTER ROLE
CREATE ROLE
ALTER ROLE
CREATE ROLE
ALTER ROLE
Now let's create the subscription using the Standby as the source:
$ /usr/edb/as16/bin/psql -d edb -p 5446
psql (16.3.0)
Type "help" for help.
edb=#
edb=# create subscription testsub connection 'dbname=edb host=localhost port=5445 user=pubuser password=edb' publication testpub;
NOTICE: created replication slot "testsub" on publisher
CREATE SUBSCRIPTION
edb=#
edb=# select * from pg_subscription;
-[ RECORD 1 ]-------+-----------------------------------------------------------------
oid | 16399
subdbid | 15239
subskiplsn | 0/0
subname | testsub
subowner | 10
subenabled | t
subbinary | f
substream | f
subtwophasestate | d
subdisableonerr | f
subpasswordrequired | t
subrunasowner | f
subconninfo | dbname=edb host=192.168.84.9 port=5445 user=pubuser password=edb
subslotname | testsub
subsynccommit | off
subpublications | {testpub}
suborigin | any
It may sometimes happen that this process isn't complete immediately because the Standby is still waiting to sync up the data from the Primary server. If it does happen, our recourse is to call the pg_log_standby_snapshot()
function on the Primary, so that the replication state gets refreshed on the Standby, thereby allowing other replication commands to be executed on the Standby thereafter.
On the Primary:
$ /usr/edb/as16/bin/psql -d 5444 -d edb
psql (16.3.0)
Type "help" for help.
edb=#
edb=# select pg_log_standby_snapshot();
pg_log_standby_snapshot
-------------------------
0/70022B8
(1 row)
edb=#
Several powerful capabilities on standby servers, including the ability to:
- Create logical replication slots
- Initiate logical decoding
- Subscribe to changes on a standby
- Persist logical replication slots even after a failover
Utilizing a standby server as a source for logical replication subscribers will be a seamless process, with only a handful of adjustments required:
- Enable
hot_standby_feedback = on
; - Leverage a physical replication slot to replicate data from the upstream to the standby;
- Run
pg_log_standby_snapshot()
on the primary, ensuring subscribers don't stall while creating a subscription to a standby.