Creating High Availability with PostgreSQL 14 Stream Replication: A Step-by-Step Guide

Setting up PostgreSQL 14 Stream Replication for High Availability involves creating a primary database server and one or more standby servers that replicate data from the primary using PostgreSQL Stream Replication. This step-by-step guide will walk you through the installation and configuration process, including best practices and common pitfalls to avoid during deployment. The goal is to ensure your database environment is robust and can handle unexpected failures.

Step 1: Install PostgreSQL 14 on Primary Server

First, ensure that you have PostgreSQL 14 installed on your primary server. You can do this using a package manager or by compiling from source. For example, on Ubuntu, you can use: 

PostgreSQL is a powerful, open-source relational database system that has gained popularity for its reliability and feature set. Stream replication is one of its standout features, allowing for the continuous streaming of data from the primary server to its replicas. This setup not only enhances data availability but also aids in load balancing and disaster recovery. In this guide, we will explore each aspect of setting up PostgreSQL Stream Replication, ensuring that you have a comprehensive understanding.

sudo apt-get update
sudo apt-get install postgresql-14

Step 2: Configure Primary Server

  1. Edit the PostgreSQL configuration file to allow replication. Locate the postgresql.conf file, typically found in /etc/postgresql/14/main/ on Linux systems.
  2. Add or modify the following lines:
listen_addresses = '*' # Listen on all network interfaces
wal_level = replica # Set the Write-Ahead Log level to replica
max_wal_senders = 3 # Number of replication connections allowed
wal_keep_segments = 64 # Number of WAL files to keep for replication

3. Create a replication user and grant the necessary permissions:

Creating a replication user is crucial for secure access. The user you’ve created for replication must have appropriate permissions to ensure that the standby servers can connect and replicate data seamlessly. Security is paramount, so using strong passwords and limiting connection permissions can greatly enhance the integrity of your system.

CREATE USER replicator REPLICATION LOGIN CONNECTION LIMIT 3 ENCRYPTED PASSWORD 'your_password';

4. Update the pg_hba.conf file to allow replication connections. Add the following line to allow replication from the standby server(s):

The pg_hba.conf file is essential for defining who can connect to your PostgreSQL server and how they can authenticate. It is critical to ensure that this file is correctly configured to enable replication while maintaining security. Always validate the entries in this file after making changes to ensure that no unintended access is allowed.

host replication replicator standby_ip/32 md5

5. Restart PostgreSQL to apply the changes:

sudo systemctl restart postgresql

Step 3: Create a Base Backup on Primary

Creating a base backup is a fundamental part of replication setup. It’s the first step to ensuring that your standby server has the same data as the primary server at the point of the backup. Utilizing the pg_basebackup tool not only streamlines this process but also minimizes the potential for human error. This method ensures that your replicated data starts on a solid foundation, reducing issues in future replication.

  1. On the primary server, create a base backup using the pg_basebackup utility. Replace standby_username and standby_password with the credentials for the replication user you created earlier:
pg_basebackup -h primary_ip -U replicator -D /var/lib/postgresql/14/main -P --wal-method=stream

Step 4: Install PostgreSQL 14 on Standby Server

Installing PostgreSQL on the standby server should mirror the primary server’s setup closely to ensure compatibility. It’s advisable to check the versions and any required extensions to maintain consistency. Having similar configurations on both servers can significantly reduce the complexity during replication.

Install PostgreSQL 14 on the standby server using the same method as in Step 1.

Step 5: Configure Standby Server

    1. On the standby server, create a recovery configuration file named recovery.conf in the PostgreSQL data directory (e.g., /var/lib/postgresql/14/main). Add the following lines:

The recovery.conf file is critical as it tells the PostgreSQL instance how to connect to the primary server for replication. This file must be carefully crafted to include the correct parameters, as any misconfigurations can lead to replication failures. Always double-check the connection settings to ensure they match your network environment.

    standby_mode = 'on'
    primary_conninfo = 'host=primary_ip port=5432 user=replicator password=standby_password'
    trigger_file = '/path/to/trigger/file'

    Replace primary_ip, replicator, standby_password, and /path/to/trigger/file with appropriate values.

    1. Ensure that the recovery.conf file is owned by the PostgreSQL user and has the correct permissions.
    2. Start the PostgreSQL service on the standby server:

    sudo systemctl start postgresql

    Step 6: Monitor Replication

    Monitoring replication is essential for maintaining high availability. Regular checks on the replication status can help you identify potential issues before they escalate. Utilizing PostgreSQL’s built-in statistics views can provide valuable insights and ensure that your replication setup is functioning as intended. Consider incorporating alerts for critical thresholds to stay proactive in your replication management.

    You can monitor the replication status using the following SQL command on the standby server:

    SELECT * FROM pg_stat_replication;

    Step 7: Failover (Optional)

    Failover procedures are vital for ensuring continuity in case of a primary server failure. It’s recommended to have a documented failover process and to test it periodically. Training your team on these procedures and understanding the implications of promoting a standby server is crucial, as this can impact application connections and data consistency. Planning for failover scenarios can help reduce downtime and maintain service availability.

    In the event of a primary server failure, you can promote a standby server to become the new primary. This requires manual intervention and may involve updating DNS records or connection settings in your applications.

    In conclusion, implementing PostgreSQL Stream Replication for high availability requires careful planning and execution. By following the steps outlined in this guide, you will create a resilient database infrastructure capable of handling failures while ensuring data integrity. As you continue to enhance your PostgreSQL environment, consider exploring additional features such as logical replication, which offers more flexibility for certain use cases. Embrace the richness of PostgreSQL to optimize your database performance and reliability.

    Further Reading