Step-by-step PostgreSQL 12 Streaming Replication on Ubuntu

Step-by-step Installation and Configuration of Streaming Replication in PostgreSQL 12

In this post we have explained how to implement step-by-step PostgreSQL 12 Streaming Replication on Ubuntu 20.04 (Codename: focal). PostgreSQL support several types of replication solutions for performance, scalability and high availability. One of the most commonly used and highly recommended PostgreSQL replication solution is Write-Ahead Log (WAL) Shipping. In this solution, the PostgreSQL standby node is built using file-based log shipping or streaming replication, even combination of both whenever appropriate. In a streaming replication, a standby (PostgreSQL replication slave) is tuned to connect with PostgreSQL Primary / Master Node, to stream WAL records to the standby as and when they are generated, without waiting for the WAL file to be filled. PostgreSQL streaming replication by default is asynchronous in which data is written to the standby server(s) after a transaction has been committed on the primary server. This means there are some associated risks on PostgreSQL data reliability / durability because primary / master node is not bothered about the state of data successfully replicated to standby node, So master server crashes, any uncommitted transactions may not be replicated and this can cause data loss. In this post we have also explained about PostgreSQL 12 “replication slots” for standby node to avoid the master server from recycling old WAL segments before the standby has received them. P.S: Replication slots retain only the number of segments known to be needed.

PostgreSQL Primary / Master Node and Standby Node (replication slave) used in the streaming replication

  • PostgreSQL Primary / Master Node –
  • PostgreSQL Standby Node (replication slave) –

Linux distribution in PostgreSQL Master Instance

Linux distribution in PostgreSQL Standby Instance

PostgreSQL 12 in the Master

PostgreSQL 12 in the Standby

Configure the PostgreSQL Primary / Master Node

On the PostgreSQL Primary / Master Node configure the IP address(es) on which the master server will listen to for connections from clients.

Create a Replication user in PostgreSQL Master

Create a user called replicator to facilitate connections between PostgreSQL primary and standby node using the createuser utility program:

Edit pg_hba.conf file

After creating the replication user let’s configure the user to access our standby node by editing and configuring rules in pg_hba.conf file:

Restart or reload PostgreSQL 12 in the Master / Primary Node for the changes to take effect by running:

Backup standby

Backup PostgreSQL in the standby database node before deleting the data:

Backup from primary to standby

Backup and restore the primary database on to standby node using pg_basebackup utility tool:

-h  – Represents the node that will be backed up. In our case this will be our primary node.

-D – The data directory where the backup will be transferred to.

-U – Connection User

-P – Turns on progress reporting.

-v – Enables verbose mode

-R – Creates a standby.signal file and append connection settings to

-X – includes the required write-ahead log files in the backup

-C – enables the creation of a replication slot named by the -S option before starting the backup.

-S – specifies the replication slot name.

Monitor Replication Slot from Primary / Master Node

Confirm replication slot running from primary / master node, You can check the slot_name node2 in result of the query:

Start PostgreSQL instance in the standby node:

Validate Replication happening between Primary Node / Master and Standby / Slave:

To monitor how much is lag behind the slots:


PostgreSQL powers today several mission critical database infrastructure operations so building highly available, reliable and fault-tolerant PostgreSQL Operations is one the core responsibility of DBAs / Database Reliability Engineers.

Business FunctionContact
☎ CONTACT GLOBAL SALES (24*7)📞 (844) 588-7287 (USA)
📞 (415) 212-6625 (USA)
📞 (778) 770-5251 (Canada)
☎ TOLL FREE PHONE (24*7)📞 (844) 588-7287
🚩 MINERVADB FAX+1 (209) 314-2364
📨 MinervaDB Email - General / Sales /
📨 MinervaDB Email -
📨 MinervaDB Email -Remote
📨 Shiv Iyer Email - Founder and
CA 91723
🏠 CORPORATE ADDRESS: HOUSTONMinervaDB Inc., 1321 Upland Dr. PMB 19322, Houston,
TX, 77043, US
About Shiv Iyer 443 Articles
Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.