PostgreSQL Replication and Automatic Failover Tutorial

PostgreSQL replication refers to the process of data export from a PostgreSQL database server (master server) to another server (replica server). It is useful in OLTP Performance, Fault Tolerance, Data Migration, and Testing Systems in Parallel.

Failure in replication can occur if the primary server for the database fails. This is where Automatic Failover comes in. It can help to detect failures and automatically switch to standby. This will minimize database downtime. In this PostgreSQL replication and automatic failover tutorial, we will briefly discuss the most important things you need to know.

Types of PostgreSQL Database Replication

There are two major types of PostgreSQL database replications, namely:

• Physical Replication of PostgreSQL Database
• Logical Replication of PostgreSQL Database.

Physical replication is specifically applicable to the transfer of files and directories. This type of replication does not bother with the knowledge of the content of these files and directories. It is executed at the file system level, or what is technically referred to as disk level.

Logical replication of databases goes deeper to deal with databases, tables, and DML operations. This type of replication can be used for specific tables, instead of an entire file or directory. It is done at a level that is technically referred to as a database cluster level.

Logging and Importance in PostgreSQL Replication

To prevent data loss due to PostgreSQL crash, operating system crash, hardware failure, and other unforeseen circumstances, logging is a standard mechanism in PostgreSQL Replication and Failover. This is a process of logging all changes made by a transaction before sending it to the initiating client. Write Ahead Logging (WAL) is the standard mechanism for this process, and the log file is called Write Ahead Log. 

PostgreSQL Replication Failover Setup

Setting up PostgreSQL Replication failover properly is critical to successful database replication. Ideally, the setup consists of two CentOS 7 machines connected through LAN. To configure with WAL streaming, follow the steps below:

• Disable and stop firewalls on the two machines
• Allow replication connections and connections from the network on the primary server
• Edit Postgresql.conf on the primary server
• Start the primary server
• Take base backup to bootstrap the standby server
• Check the base backup file
• In the base backup, add the line primary_slot_name = ‘node_a_slot’ in the recovery.conf:
• Check the /tmp/sb_data/recover.conf file
• Connect to the primary server and issue the appropriate command
• Transfer the base backup to the standby server
• Start the standby server
• Connect to the primary server and issue some simple commands
• Check the data on the replica.

How to Automate Failover and Replication in PostgreSQL

There is an easy way to quickly set up automatic failover using EDB Postgres Failover Manager (EFM). You will have to start by downloading EFM on the master and standby nodes you want to use for the replication. When that is done, create an EFM Cluster, which should be made up of a master node, standby node(s), and an optional witness node.