Database Replication in PostgreSQL: Setting up Hot Standby

Introduction

In today's world, data is the backbone of any organization. Losing data or experiencing downtime can be catastrophic for businesses. To mitigate such risks, it is essential to implement a robust database replication solution. PostgreSQL, an open-source relational database management system, provides an efficient and reliable replication mechanism called Hot Standby Streaming Replication.

This blog post will guide you through the process of setting up Hot Standby Streaming Replication in PostgreSQL. We will cover the prerequisites, configuration steps, and testing process to ensure a successful replication setup.

Prerequisites

Before diving into the replication setup, make sure you have the following prerequisites:

PostgreSQL installed on both the primary and standby servers. You can download the latest version of PostgreSQL from the official website.
Network connectivity between the primary and standby servers. Ensure that the servers can communicate with each other.
Sufficient disk space on the standby server to accommodate the replicated data.

Configuration Steps

Enable WAL Archiving on the Primary Server: The first step is to enable Write-Ahead Logging (WAL) archiving on the primary server. Open the postgresql.conf file and set the archive_mode parameter to on. Define a directory to store the archived WAL files using the archive_command parameter.
Create a Replication User: To enable replication between the primary and standby servers, create a replication user on the primary server. This user will be used for authentication during the replication process. Run the following SQL command in the PostgreSQL shell:
```
CREATE USER replication_user REPLICATION LOGIN CONNECTION LIMIT 2 ENCRYPTED PASSWORD 'password';
```
Configure Replication Settings: Open the postgresql.conf file on the primary server and configure the following parameters:
- wal_level: Set this parameter to replica to enable replication.
- max_wal_senders: Specify the maximum number of concurrent connections used for sending WAL data to the standby servers.
- wal_keep_segments: Set the number of WAL segments to keep on the primary server for standby servers to catch up in case of network interruptions.
- wal_sender_timeout: Define the maximum time, in milliseconds, that replication will wait while sending data. Adjust this value according to your network conditions.
- synchronous_standby_names: If you want synchronous replication, set this parameter to the name of the standby server.
Configure Standby Server: On the standby server, create a recovery.conf configuration file in the PostgreSQL data directory. Add the following configurations to the recovery.conf file:
```
standby_mode = 'on'
primary_conninfo = 'host=<primary_host> port=<primary_port> user=<replication_user> password=<password>'
trigger_file = '/path/to/trigger/file' 
```
Replace <primary_host>, <primary_port>, <replication_user>, <password>, and /path/to/trigger/file with appropriate values.
Start the Standby Server: Start the PostgreSQL service on the standby server. The server will connect to the primary server and begin replicating the data.

Testing the Replication Setup

To verify that the replication is working correctly, follow these steps:

Check the replication status on the primary server: Run the command SELECT * FROM pg_stat_replication; on the primary server. It should display information about the connected standby server.
Monitor the replication status on the standby server: Connect to the PostgreSQL shell on the standby server and run the command pg_stat_wal_receiver to check the replication status. It should show a valid connection to the primary server.
Perform a failover test: To ensure a seamless failover, simulate a failure on the primary server. Stop the primary PostgreSQL service and monitor the behavior of the standby server. It should automatically take over as the new primary server.

Conclusion

Setting up Hot Standby Streaming Replication in PostgreSQL provides a reliable and efficient way to replicate data between primary and standby servers, ensuring high availability and data durability. By following the configuration steps and testing the replication setup, you can have confidence in your database replication solution.

本文来自极简博客，作者：时光静好，转载请注明原文链接：Database Replication in PostgreSQL: Setting up Hot Standby