Creating a high-availability setup with PostgreSQL 15.4 replication on Ubuntu involves several steps. This guide will take you through setting up a primary and a standby server with streaming replication. It's assumed that PostgreSQL 15.4 is installed on both servers.

Primary Server Setup

1. Configure PostgreSQL: Edit the PostgreSQL configuration file, typically located at /etc/postgresql/15.4/main/postgresql.conf.

   # Enable WAL Archiving
   wal_level = replica
   
   # Set the maximum number of WAL senders
   max_wal_senders = 3
   
   # Set the maximum number of replication slots
   max_replication_slots = 2
   
   # Listen for connections from all addresses
   listen_addresses = '*'
   
   

2. Configure Client Authentication: Edit the pg_hba.conf file, typically located at /etc/postgresql/15.4/main/pg_hba.conf.

   # Allow replication connections from standby's IP address
   host replication replication_user standby_ip/32 md5
   
   

3. Create a Replication User: Connect to the PostgreSQL server and run:

   CREATE ROLE replication_user WITH REPLICATION LOGIN ENCRYPTED PASSWORD 'your_password';
   
   

4. Restart PostgreSQL: Restart the PostgreSQL service to apply the changes.

   sudo systemctl restart postgresql
   
   

Standby Server Setup

1. Stop PostgreSQL Service: On the standby server, stop the PostgreSQL service.

   sudo systemctl stop postgresql
   
   

2. Copy Data from Primary: Use pg_basebackup to copy the data directory from the primary to the standby.

   pg_basebackup -h primary_ip -D /var/lib/postgresql/15.4/main -U replication_user -P -v --wal-method=stream
   
   

3. Create Recovery Configuration: Create a standby.signal file in the data directory to enable standby mode.

   touch /var/lib/postgresql/15.4/main/standby.signal
   
   

   Create or edit the postgresql.conf file in the data directory:

   primary_conninfo = 'host=primary_ip user=replication_user password=your_password'
   
   

4. Start PostgreSQL: Start the PostgreSQL service on the standby server.

   sudo systemctl start postgresql
   
   

Post-Setup

• Verify Replication: Check the replication status on the primary using psql:

  SELECT * FROM pg_stat_replication;
  
  

• Regular Backup: Regularly backup your databases to ensure data safety.

• Monitoring: Set up monitoring for both servers to quickly detect issues.

• Failover Planning: Establish a failover procedure for switching to the standby in case the primary fails.

This setup provides a basic high-availability solution using streaming replication. For more advanced setups, consider using tools like Patroni for automatic failover management. Always test your replication and failover procedures in a non-production environment before implementing them in production.