Primary-Secondary Replication

Disclaimer: This lesson uses the terms primary and secondary instead of the terms master and slave, respectively. Please refer to the PostgreSQL documentation for more details.

Primary-secondary replication is a process in which data from a primary database is replicated to one or more secondary databases. This setup is commonly used for load balancing, failover, and disaster recovery.

Primary-secondary replication provides high availability, redundancy, and scalability by allowing multiple servers to access the same data and ensuring that changes to the data are automatically propagated to all the servers in the configuration. It also helps minimize the risk of data loss due to hardware failures or other issues and allows for easier scaling of database systems as the data size grows.

Prerequisites

Before setting up streaming replication, there are a few prerequisites we should consider:

1. A PostgreSQL database is installed on the primary and secondary (replica) servers.

2. There’s a user account with sufficient privileges to perform database operations on the primary and replica servers.

3. There’s a secure network connection between the primary and replica servers.

Setting up streaming replication in PostgreSQL

Streaming replication in PostgreSQL uses the WAL mechanism to replicate data from a primary database to one or more replica databases.

Note: Streaming replication and primary-secondary replication refer to the same concept in PostgreSQL. Both terms refer to a configuration in which updates made to a primary database server are replicated to one or more secondary database servers. The main difference between the two terms is the terminology used to describe the configuration. The term “streaming replication” is used to emphasize that changes to the primary database are streamed in real time to the secondary servers, while the term “primary-secondary replication” is used to describe the traditional client-server relationship between the primary and secondary servers.

The steps to set up stream replication in PostgreSQL are described below.

Create a replication user

On the primary server, create a new user specifically for replication with the following SQL command:

This command creates a new database user named replicator with the following properties:

REPLICATION LOGIN: This indicates that the user is created for the purpose of replication and will be used by the replica server to connect to the primary server and receive updates.

ENCRYPTED PASSWORD: This specifies that the password for the user is encrypted and password is the password for the user. Replace password with a strong password.

Once the user has been created, we can set up replication by specifying the connection information for the ...