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 – 167.172.114.63
  • PostgreSQL Standby Node (replication slave) – 167.172.123.158

Linux distribution in PostgreSQL Master Instance

Linux distribution in PostgreSQL Standby Instance

Install PostgreSQL 12

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 postgresql.auto.conf

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

Conclusion 

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)
📞 (415) 650-4682 (USA)
📞 (778) 644-0767 (Canada)
📞 (778) 770-5251 (Canada)
📞 +44 7 480549332 (UK)
☎ TOLL FREE PHONE (24*7)📞 (844) 588-7287
🚩 MINERVADB FAX+1 (209) 314-2364
📨 MinervaDB Email - General / Sales / Consultingcontact@minervadb.com
📨 MinervaDB Email - Supportsupport@minervadb.com
📨 MinervaDB Email -Remote DBAremotedba@minervadb.com
📨 Shiv Iyer Email - Founder and Principalshiv@minervadb.com
🏠 CORPORATE ADDRESS: CALIFORNIAMinervaDB Inc.,
340 S LEMON AVE #9718
WALNUT 91789 CA, US
🏠 CORPORATE ADDRESS: DELAWAREMinervaDB Inc.,
PO Box 2093 PHILADELPHIA PIKE #3339
CLAYMONT, DE 19703
🏠 CORPORATE ADDRESS: HOUSTONMinervaDB Inc., 1321 Upland Dr. PMB 19322, Houston,
TX, 77043, US
About Shiv Iyer 18 Articles
Open Source Database Systems Geek in MySQL, MariaDB, PostgreSQL and ClickHouse with core expertise in performance, scalability, high availability and database reliability engineering, Shiv currently is the founder and principal of MinervaDB, An boutique private-label consulting, support, remote DBA and education services provider for MySQL, MariaDB, Percona Server, MyRocks, PostgreSQL and ClickHouse with over 300 customers worldwide. Shiv in the past worked for companies like MySQL AB, SUN Microsystems, AOL, eBay, PayPal, PalominoDB and Percona. Shiv also is a frequent speaker in open source conferences worldwide.