Setting up PostgreSQL with Streaming Replication for High Availability and Failover involves several key steps. This guide will walk you through the process, step by step.

Prerequisites:

• Two PostgreSQL servers (one as the primary, the other as the standby).
• Similar PostgreSQL versions on both servers.
• Network connectivity between both servers.
• Sufficient disk space on both servers.

Step 1: Configure the Primary Server

1. Edit postgresql.conf:

   • Set listen_addresses to '*' or the specific IP address to allow connections.
   • Set wal_level to replica or logical.
   • Adjust max_wal_senders to a number greater than the number of standby servers.
   • Optionally, adjust wal_keep_size to specify the amount of WAL files to keep.

2. Edit pg_hba.conf:

   • Add a line to allow the standby server to connect.

     host replication <replication_user> <standby_ip>/32 md5
     
     

   • <replication_user> is the user for replication, and <standby_ip> is the IP address of the standby server.

3. Create a Replication Role:

   • Connect to the primary database and execute:

     CREATE ROLE <replication_user> REPLICATION LOGIN ENCRYPTED PASSWORD '<password>';
     
     

4. Restart PostgreSQL.

Step 2: Prepare the Standby Server

1. Stop PostgreSQL on the standby server if it's running.
2. Copy Data from Primary to Standby:

   • Use pg_basebackup to copy data:

     pg_basebackup -h <primary_ip> -D <standby_data_directory> -U <replication_user> -P -v -R
     
     

   • Replace <primary_ip>, <standby_data_directory>, and <replication_user> with your values.

Step 3: Configure the Standby Server

1. Edit postgresql.conf (if not already done by pg_basebackup):

   • Set hot_standby to on.

2. Create standby.signal File:

   • In the data directory of the standby server, create an empty file named standby.signal.

3. Optional Configuration in recovery.conf:

   • If specific recovery parameters are needed, create a recovery.conf in the data directory.

   • Set primary_conninfo with the connection string to the primary server.

     primary_conninfo = 'host=<primary_ip> port=5432 user=<replication_user> password=<password>'
     
     

4. Start PostgreSQL on the standby server.

Step 4: Verify Replication

1. Check the Logs:

   • Ensure that there are no errors in the PostgreSQL logs on both servers.

2. Check Replication Status on Primary:

   • Connect to the primary database and query:

     SELECT * FROM pg_stat_replication;
     
     

3. Check Standby Status:

   • On the standby server, ensure you can connect to the database and that it is in read-only mode.

Step 5: Additional Configurations

1. Setup Automatic Failover (Optional):
   • Use tools like Replication Manager (repmgr), Patroni, or PostgreSQL Automatic Failover (PAF) for automatic failover and more advanced high-availability configurations.
2. Archiving and Backup Strategies:
   • Set up WAL archiving, continuous archiving, and regular backups as per your requirements.

Conclusion

Streaming Replication in PostgreSQL provides a robust solution for high availability and failover. It's important to test the setup thoroughly and simulate failover scenarios to ensure the system behaves as expected under different conditions. Regular monitoring, backups, and updates are essential to maintain the health and performance of the replication setup.