How to implement master-slave replication and high availability in PostgreSQL?

Implementing master-slave replication and high availability in PostgreSQL involves several steps and configurations. Here’s a detailed explanation along with an example:

Master-Slave Replication

Explanation:
Master-slave replication is a method where data from one database server (the master) is copied to one or more other database servers (the slaves). This setup is useful for scaling read operations and providing redundancy.

Steps:

1. Configure the Master:

   • Edit the postgresql.conf file on the master to enable replication.

     wal_level = replica
     max_wal_senders = 10
     wal_keep_segments = 64
     

   • Create a replication user on the master.

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

   • Edit the pg_hba.conf file to allow connections from the slave.

     host replication replicator master_ip/32 md5
     

2. Configure the Slave:

   • Stop PostgreSQL on the slave.
   • Copy the data directory from the master to the slave using pg_basebackup.

     pg_basebackup -h master_ip -U replicator -D /path/to/slave/data -P --wal-method=stream
     

   • Edit the postgresql.conf file on the slave to set it to slave mode.

     hot_standby = on
     

   • Start PostgreSQL on the slave.

High Availability

Explanation:
High availability (HA) ensures that your database is always accessible even in the event of failures. This is often achieved using a combination of replication and failover mechanisms.

Example with Streaming Replication and Failover:

1. Set Up Streaming Replication:

   • Follow the steps above to set up master-slave replication.

2. Implement Failover:

   • Use a tool like pgpool-II or repmgr to manage failover automatically.
   • Using pgpool-II:
     • Install and configure pgpool-II on both the master and slave.
     • Configure pgpool.conf to set up replication and failover parameters.

       backend_hostname0 = 'master_ip'
       backend_port0 = 5432
       backend_weight0 = 1
       backend_data_directory0 = '/path/to/master/data'
       backend_flag0 = 'ALLOW_TO_FAILOVER'
       
       backend_hostname1 = 'slave_ip'
       backend_port1 = 5432
       backend_weight1 = 1
       backend_data_directory1 = '/path/to/slave/data'
       backend_flag1 = 'ALLOW_TO_FAILOVER'
       
       failover_command = '/path/to/failover_script %d %H %P %h %p'
       

     • Create a failover script that handles the promotion of the slave to master and updates pgpool.conf.

Recommendation for Cloud Services

For a more managed and scalable solution, consider using cloud services that offer managed PostgreSQL databases with built-in replication and high availability features. For example, Tencent Cloud provides the TencentDB for PostgreSQL service, which offers automated replication, failover, and scaling capabilities, reducing the operational overhead.

By following these steps and leveraging cloud services, you can achieve robust master-slave replication and high availability in PostgreSQL.