All PostgreSQL Replication Explained – How to Do Them Right.

PostgreSQL Streaming Replication is a crucial feature that enhances availability, performance and data integrity of your database. It is a fundamental component in modern database deployment. Replication refers to a technique that copies data and state from one database instance to another. PostgreSQL supports 2 major replication techniques:

• Streaming replication (also known as physical replication)
• Logical replication

In this blog, I will explain the purposes of both replication techniques based on PostgreSQL 16 and how to properly set them up with the right examples. So, let’s begin.

PostgreSQL Streaming Replication

Streaming replication replicates entire database in real-time, which consists of

• One primary node – can read from and write to database
• One or more standby nodes (also called replica nodes. Please do not call them slave nodes) – can only read from database

The replication works by having the primary node send new WAL segments (when a change is made) to all standby nodes. The standby nodes can replicate primary’s database state and data simply by replaying the WAL segments (also called REDO).

Why PostgreSQL Streaming Replication?

• High availability – if the primary node is down, a standby node can promote to be the new primary and continue database operation. Patroni is a popular orchestration tool for this purpose.
• Data redundancy – all nodes store copies of the same data.
• Load balancing – application can distribute read requests among all standby nodes and send write requests only to primary node. Pgpool is a popular orchestration tool for this purpose.
• Scalability – more standby nodes can be added to distribute higher loads of read requests.

PostgreSQL Streaming Replication Setup Example

The example below uses default database postgres and default username $USER to set up the replication. If you would like to use it on another user and database, you will have to update pg_hba.conf as well. Refer to this documentation about pg_hba.conf.

(1) Start by initializing a new database cluster:

initdb -D primary

(2) Examine primary/postgresql.conf and note some of the important parameters related to PostgreSQL streaming replication:

For this example, the primary node will be running at localhost at default port 5432 . wal_level = replica tells PostgreSQL to log enough information to WAL suitable for standby nodes to replicate physical data properly. max_wal_senders sets the maximum number of walsenders process to be sending WAL segments to standby nodes. This determines the maximum number of standby nodes it can connect to. Make sure the standby node’s max_wal_senders value is equal or greater than that of primary node’s. Refer to here for all parameters related to streaming replication.

listen_addresses = 'localhost'
port = 5432
wal_level = replica
max_wal_senders = 10

(3) Start the primary node:

pg_ctl -D primary -l primary.log start

(4) Create standby node by making a basebackup from the primary node: The -R argument tells pg_basebackup to pre-fill the configuration for replication after making a backup.

pg_basebackup -h locahost -p 5432 -D standby -P -Xs -R

(5) Examine standby/postgresql.conf and change listen_address and port as appropriate. For my example, the standby node will also be running on localhost at port 5433. Please also note the hot_standby parameter, which is by default on. This parameter allows standby node to be able to serve incoming read-only client requests. Note that max_wal_senders need to be equal or higher than primary nodes value.

listen_addresses = 'localhost'
port = 5433
hot_standby = on
max_wal_senders = 10

(6) Examine standby/postgresql.auto.conf, which has been pre-filled by pg_basebackup. The primary_conninfo contains the connection parameters to connect to the primary. For this simple replication example, leaving them as defaults should suffice.

primary_conninfo = 'user=caryh passfile=''/home/caryh/.pgpass'' channel_binding=disable host=127.0.0.1 port=5432 sslmode=disable sslcompression=0 sslcertmode=disable sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'

(7) Touch standby.signal file to make standby node operate in standby mode:

touch standby/standby.signal

(8) Finally, we can start the standby node and it should connect to primary and start replicating all the data:

pg_ctl -D standby -l standby.log start

Logical Replication

Logical replication replicates selected data logically in the form of SQL statements, making it possible to do selective data replication (rather than whole database replication) and also cross-platform replication (from PostgreSQL to another database platform). The setup normally consists of

• One publisher node – to send data changes.
• One or more subscriber nodes – to receive data changes and apply them.

The replication works by capturing changes made to the data in the form of SQL statements (also recorded in WAL with wal_level set to logical). This data change is then sent to all subscriber nodes via logical decoding plugins. Finally, the subscribers apply the statements to complete the replication.

Why Logical Replication?

• Selective data replication – can replicate selective tables rather than whole database.
• Cross-version and platform replication – data can be replicated between PostgreSQL nodes of different versions as well as to other databases
• Data aggregation – consolidate data from multiple databases to a central location
• Change tracking – track all changes made to a particular table

Logical Replication Setup Example

There are several ways to set up and customize a logical replication depending on the use cases. I will demonstrate 2 common use cases here:

• PostgreSQL to PostgreSQL logical replication
• PostgreSQL to non-PostgreSQL logical replication (with help pf a custom logical decoding plugin)

PostgreSQL to PostgreSQL Logical Replication

(1) Start by initializing a publisher and a subscriber database cluster:

initdb -D publisher

initdb -D subscriber

(2) Examine publisher/postgresql.conf and subscriber/postgresql.conf and note some of the important parameters related to logical replication. Please note that wal_level must be set to logical to enable logical replication between the 2 nodes.

listen_addresses = 'localhost'
port = 5432
wal_level = logical
max_wal_senders = 10

listen_addresses = 'localhost'
port = 5433
wal_level = logical
max_wal_senders = 10

(3) Start the publisher and subscriber nodes:

pg_ctl -D publisher -l publisher.log start

pg_ctl -D subscriber -l subscriber.log start

(4) Create an example table to be logically replicated on both nodes. Please note that:

• It is required to have the target table pre-created on all nodes before logical replication can be set up.
• It is strongly encouraged that the table contains a PRIMARY KEY column. Logical decoding relies on it to track deleted and updated rows. Without this, subscriber cannot tell what row has been deleted.

CREATE TABLE mytable (a INT PRIMARY KEY, b TEXT);
INSERT INTO mytable VALUES(generate_series(1,5), 'test');
SELECT * FROM mytable;
 a |  b
---+------
 1 | test
 2 | test
 3 | test
 4 | test
 5 | test
(5 rows)

CREATE TABLE mytable (a INT PRIMARY KEY, b TEXT);
SELECT * FROM mytable;
 a | b
---+---
(0 rows)

(5) Create publication and subscription objects to establish logical replication. Once the subscription is set up, subscriber’s mytable will be synchronized with the publisher’s. Furthermore, all subsequent changes on mytable on the publisher side will be logically replicated to the subscriber while other tables not listed are not replicated.

A replication slot will be created on the subscriber node as soon as the subscription object is created. A replication slot tracks the replication status between publisher and subscriber nodes and ensures that the publisher does not remove WAL segments until they have been replicated. A replication slot prevents recovery or replication conflicts if a subscriber goes offline for an extended period of time.

CREATE PUBLICATION mypub FOR TABLE mytable;
SELECT * FROM mytable;
 a |  b
---+------
 1 | test
 2 | test
 3 | test
 4 | test
 5 | test
(5 rows)

CREATE SUBSCRIPTION mysub CONNECTION 'host=127.0.0.1 port=5432 user=caryh dbname=postgres' PUBLICATION mypub;
SELECT * FROM mytable;
 a |  b
---+------
 1 | test
 2 | test
 3 | test
 4 | test
 5 | test
(5 rows)

There you have it. This is how to create a simple logical replication in PostgreSQL. It is possible to further customize a publication to replicate more or less data conditionally. The following is a snapshot taken from the official PostgreSQL 16 CREATE PUBLICATION documentation.

PostgreSQL to non-PostgreSQL Logical Replication

In the case of PostgreSQL to non-PostgreSQL logical replication, we will not be setting up a publication and subscription objects like in the previous example. Instead, we will use a custom logical decoding plugin that is responsible for translating PostgreSQL statements to another. For this example, we will set up PostgreSQL to Mongodb logical replication using wal2mongo logical decoding plugin. Alternatively, you may also use testdecoding logical decoding plugin that comes with PostgreSQL installation. The principle is the same but the output differs.

(1) Download, build and install wal2mongo plugin here or use the built-in test_decoding plugin

(2) Create a logical replication slot using a specific decoding plugin:

SELECT * FROM pg_create_logical_replication_slot('w2m_slot', 'wal2mongo');

SELECT * FROM pg_create_logical_replication_slot('test_slot', 'test_decoding');

(3) Insert some data to mytable which has been created in previous section:

INSERT INTO mytable VALUES(30, 'hello');

(4) Cconsume the table changes using the logical replication slot created:

SELECT * FROM pg_logical_slot_get_changes('w2m_slot', NULL, NULL);
    lsn    | xid |                            data
-----------+-----+------------------------------------------------------------
 0/15CEA58 | 768 | use mycluster_postgres_w2m_slot;
 0/15CEA58 | 768 | db.mytable.insertOne( { a: NumberInt("30"), b:"hello" } );
(2 rows)

SELECT * FROM pg_logical_slot_get_changes('test_slot', NULL, NULL);
    lsn    | xid |                            data
-----------+-----+-------------------------------------------------------------
 0/15CEA58 | 768 | BEGIN 768
 0/15CEA58 | 768 | table public.mytable: INSERT: a[integer]:30 b[text]:'hello'
 0/15CEB10 | 768 | COMMIT 768
(3 rows)

(5) As you can see, the INSERT statement has been decoded into different representations dedicated for different database system. If we were to feed the output of wal2mongo logical decoding plugin directly into a mongodb instance, it would mean:

“Insert a new document with integer value 30 and string value “hello” to a collection called “mytable” under the “mycluster_postgres_w2m_slot” database.”

Logical Replication with pg_recvlogical

Previous section illustrates the principle of logical decoding plugins and how they translate a statement into a custom representation. However, the setup is incomplete because mongodb itself is not in the picture. To complete the picture, we can use pg_recvlogical utility command provided by PostgreSQL to subscribe changes from PostgreSQL and feed the output to mongodb client application.

Print changes on the terminal with pg_recvlogical:

pg_recvlogical -d postgres --slot w2m_slot --start -f -

Feed changes into mongo client application with pg_recvlogical:

pg_recvlogical -d postgres --slot w2m_slot --start -f - | while read -r change; do echo "$change" | mongo ; done

The above turns pg_recvlogical into a middleware between PostgreSQL and Mongodb to complete the logical replication. Indeed it works, but not efficient, because the connection to mongodb is not persistence. Every time a change is received by pg_recvlogical, mongo opens a new connection to mongodb to apply the change. The better way to do it is to modify pg_recvlogical to persist the connection to both PostgreSQL and Mongodb as a new changes arrives.

Summary

PostgreSQL streaming replication and logical replications are 2 major replication techniques available in PostgreSQL 16. They are very important in modern day database deployments. The examples mentioned in this blog aim to show you the principles of both techniques. In real deployments, the replication scenarios are often more complicated. I hope that you will find this blog helpful and please send me a message if you have any questions.

Reference

• PostgreSQL: Documentation: 16: 20.6. Replication
• PostgreSQL: Documentation: 16: CREATE PUBLICATION
• PostgreSQL: Documentation: 16: CREATE SUBSCRIPTION
• PostgreSQL: Documentation: 16: 27.2. Log-Shipping Standby Servers
• PostgreSQL: Documentation: 16: 21.1. The pg_hba.conf File
• PostgreSQL: Documentation: 16: 49.1. Logical Decoding Examples
• Logical Replication Between PostgreSQL and MongoDB

Cary Huang

Hi, this is Cary, your friendly tech enthusiast, educator and author. I enjoy simplifying complex concepts, diving into coding challenges, unraveling the mysteries of software and most importantly, sharing and teaching others about all things tech.