Setting Up a PostgreSQL Primary/Standby Architecture

Overview

PostgreSQL is an open-source object-relational database management system with an emphasis on extensibility and standards compliance. It applies to business-oriented online transaction processing (OLTP) scenarios and supports NoSQL (JSON, XML, or hstore) and geographic information system (GIS) data types. It has won a good reputation in reliability and data integrity, and applies widely to Internet websites, location-based applications, and complex data object processing.

This section describes how to set up a PostgreSQL primary/standby architecture on ECSs.

Prerequisites

• You have created two ECSs, one as the primary node and the other as the standby node. For details, see Purchasing an ECS in Custom Config Mode.

  In this example, the Ubuntu 20.04 64-bit and Debian 12.0.0 64-bit are used.

  Example IP address of the primary node: 192.168.1.10

  Example IP address of the standby node: 192.168.1.11

• The rules listed in the following table have been added to the security groups that the target ECS belongs to. For details, see Adding a Security Group Rule.

  Table 1 Security group rules

  Direction	Priority	Action	Type	Protocol & Port	Source
  Inbound	1	Allow	IPv4	TCP: 22	0.0.0.0/0
  Inbound	1	Allow	IPv4	TCP: 5432	0.0.0.0/0

Step 1: Configure the PostgreSQL Primary Node

Perform the following operations based on the OS.

Ubuntu

1. Run the following command to add the official PostgreSQL source with Huawei Cloud acceleration:

   echo "deb [signed-by=/etc/apt/trusted.gpg.d/postgresql.gpg] https://mirrors.huaweicloud.com/postgresql/repos/apt focal-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list

2. Run the following command to import the official PostgreSQL signature key:

   wget -qO - https://mirrors.huaweicloud.com/postgresql/repos/apt/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg

3. Run the following commands to update the source and install PostgreSQL 14:

   sudo apt update
   sudo apt install -y postgresql-14 postgresql-client-14 postgresql-contrib-14

4. Run the following command to start PostgreSQL and enable it to automatically start upon ECS startup:

   sudo systemctl enable --now postgresql@14-main

5. Run the following command to check the version of the installed PostgreSQL:

   psql --version

   If information similar to the following is displayed, the installation is successful.

   

6. Run the following commands in sequence to edit the /etc/postgresql/14/main/postgresql.conf file:

   # Allow access from all addresses.
   sudo sed -i "s/^#listen_addresses =.*/listen_addresses = '*'/" /etc/postgresql/14/main/postgresql.conf
   # Enable the WAL replication mode.
   sudo sed -i "s/^#wal_level =.*/wal_level = replica/" /etc/postgresql/14/main/postgresql.conf
   # Set the maximum number of WAL sender processes.
   sudo sed -i "s/^#max_wal_senders =.*/max_wal_senders = 5/" /etc/postgresql/14/main/postgresql.conf
   # Enable replication slots.
   sudo sed -i "s/^#max_replication_slots =.*/max_replication_slots = 5/" /etc/postgresql/14/main/postgresql.conf
   # Set the WAL log retention size.
   sudo sed -i "s/^#wal_keep_size =.*/wal_keep_size = 128MB/" /etc/postgresql/14/main/postgresql.conf
   # Enable WAL archiving.
   sudo sed -i "s/^#archive_mode =.*/archive_mode = on/" /etc/postgresql/14/main/postgresql.conf
   # Configure the archive path.
   sudo sed -i "s|^#archive_command = ''|archive_command = 'cp %p /var/lib/postgresql/14/main/wal_archive/%f'|" /etc/postgresql/14/main/postgresql.conf
   
   # Create WAL archive directories and set permissions.
   sudo mkdir -p /var/lib/postgresql/14/main/wal_archive
   sudo chown postgres:postgres /var/lib/postgresql/14/main/wal_archive
   sudo chmod 700 /var/lib/postgresql/14/main/wal_archive

7. Run the following command to modify the /var/lib/pgsql/11/data/pg_hba.conf configuration file:

   # At the end, add replication permissions for the standby node IP, using scram-sha-256 encryption (the default authentication method in Ubuntu 20.04).
   echo "host    replication     replica_user    192.168.1.11/32    scram-sha-256" | sudo tee -a /etc/postgresql/14/main/pg_hba.conf

8. Run the following command to create a user and grant permissions:

   # Create a replication user.
   sudo -u postgres psql -c "CREATE ROLE replica_user WITH REPLICATION LOGIN PASSWORD 'password';"
   # Create a physical replication slot (to prevent WAL logs from being cleared and allow automatic catch-up after disconnection).
   sudo -u postgres psql -c "SELECT pg_create_physical_replication_slot('pg_slot01');"

9. Run the following commands to restart the service:

   sudo systemctl restart postgresql@14-main
   sudo systemctl status postgresql@14-main

Debian

1. Run the following commands to update the source and install PostgreSQL 15:

   apt update
   apt install -y postgresql postgresql-contrib

2. Run the following commands to start PostgreSQL and enable it to automatically start upon ECS startup:

   systemctl enable postgresql
   systemctl start postgresql

3. Run the following command to check the version of the installed PostgreSQL:

   psql --version

   If information similar to the following is displayed, the installation is successful.

   

4. Run the following commands in sequence to edit the /etc/postgresql/15/main/postgresql.conf file:

   # Configure the primary node.
   sed -i "s/^#listen_addresses =.*/listen_addresses = '*'/" /etc/postgresql/15/main/postgresql.conf
   sed -i "s/^wal_level =.*/wal_level = replica/" /etc/postgresql/15/main/postgresql.conf
   sed -i "s/^max_wal_senders =.*/max_wal_senders = 10/" /etc/postgresql/15/main/postgresql.conf
   sed -i "s/^max_replication_slots =.*/max_replication_slots = 10/" /etc/postgresql/15/main/postgresql.conf

5. Run the following command to modify the /etc/postgresql/15/main/pg_hba.conf configuration file:

   # Allow replication from the standby database.
   echo "host    replication     replica_user    0.0.0.0/0    trust" >> /etc/postgresql/15/main/pg_hba.conf

6. Run the following command to create a user and grant permissions:

   sudo -u postgres psql -c "CREATE ROLE replica_user WITH REPLICATION LOGIN;"

7. Run the following commands to restart the service:

   systemctl restart postgresql

Step 2: Configure the PostgreSQL Standby Node

Perform the following operations based on the OS.

Ubuntu

1. Run the following command to add the official PostgreSQL source with Huawei Cloud acceleration:

   echo "deb [signed-by=/etc/apt/trusted.gpg.d/postgresql.gpg] https://mirrors.huaweicloud.com/postgresql/repos/apt focal-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list

2. Run the following command to import the official PostgreSQL signature key:

   wget -qO - https://mirrors.huaweicloud.com/postgresql/repos/apt/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg

3. Run the following commands to update the source and install PostgreSQL 14:

   sudo apt update
   sudo apt install -y postgresql-14 postgresql-client-14 postgresql-contrib-14

4. Run the following command to start PostgreSQL and enable it to automatically start upon ECS startup:

   sudo systemctl enable --now postgresql@14-main

5. Run the following command to check the version of the installed PostgreSQL:

   psql --version

   If information similar to the following is displayed, the installation is successful.

   

6. Run the following commands to stop the standby database service and clear the data directory:

   sudo systemctl stop postgresql@14-main
   sudo rm -rf /var/lib/postgresql/14/main/*

7. Run the following commands to configure passwordless authentication using .pgpass:

   echo "192.168.1.10:5432:replication:replica_user:password" | sudo tee /var/lib/postgresql/.pgpass
   sudo chown postgres:postgres /var/lib/postgresql/.pgpass
   sudo chmod 600 /var/lib/postgresql/.pgpass

8. Run the following commands to fully clone the data of the primary database:

   sudo -u postgres pg_basebackup -h 192.168.1.10 -D /var/lib/postgresql/14/main/ \
   -U replica_user -P -v --wal-method=stream

9. Run the following commands to configure the parameters and the signal file of the standby database:

   # Enable hot standby read-only.
   sudo sed -i "s/^#hot_standby =.*/hot_standby = on/" /etc/postgresql/14/main/postgresql.conf
   # Configure the primary database connection information and bind the replication slot.
   echo "primary_conninfo = 'host=192.168.1.10 port=5432 user=replica_user password=Pg@Huawei@123'" | sudo tee -a /etc/postgresql/14/main/postgresql.conf
   echo "primary_slot_name = 'pg_slot01'" | sudo tee -a /etc/postgresql/14/main/postgresql.conf
   
   # Create the standby signal file standby.signal (for PostgreSQL 12 and later versions, replacing the old recovery.conf).
   sudo touch /var/lib/postgresql/14/main/standby.signal
   sudo chown postgres:postgres /var/lib/postgresql/14/main/standby.signal

10. Run the following commands to start the standby database service:

    sudo systemctl enable --now postgresql@14-main
    sudo systemctl status postgresql@14-main

Debian

1. Run the following commands to update the source and install PostgreSQL 15:

   apt update
   apt install -y postgresql postgresql-contrib

2. Run the following commands to start PostgreSQL and enable it to automatically start upon ECS startup:

   systemctl enable postgresql
   systemctl start postgresql

3. Run the following command to check the version of the installed PostgreSQL:

   psql --version

   If information similar to the following is displayed, the installation is successful.

   

4. Run the following commands to stop the standby database service and clear the data directory:

   systemctl stop postgresql
   rm -rf /var/lib/postgresql/15/main/*

5. Run the following commands to fully clone the data of the primary database:

   mkdir -p /var/lib/postgresql/15/main
   chown postgres:postgres /var/lib/postgresql/15/main
   chmod 700 /var/lib/postgresql/15/main
   cd /tmp && sudo -u postgres pg_basebackup -h 192.168.1.10 -D /var/lib/postgresql/15/main -U replica_user -P -X stream

6. Run the following commands to configure the parameters and the signal file of the standby database:

   # Standby database configuration
   echo "primary_conninfo = 'host=192.168.1.10 port=5432 user=replica_user'" >> /etc/postgresql/15/main/postgresql.conf
   echo "hot_standby = on" >> /etc/postgresql/15/main/postgresql.conf
   
   # Standby database signal
   touch /var/lib/postgresql/15/main/standby.signal
   chown postgres:postgres /var/lib/postgresql/15/main/standby.signal

7. Run the following commands to start the standby database service:

   systemctl start postgresql

Step 3: Verify the Deployment

Perform the following operations based on the OS.

Ubuntu

1. Run the following command to check the replication status on the primary node:

   sudo -u postgres psql -c "SELECT pid,usename,application_name,state,sync_state FROM pg_stat_replication;"

2. Run the following commands to check the WAL receiving status on the standby node:

   sudo -u postgres psql -c "SELECT * FROM pg_stat_wal_receiver;"
   sudo -u postgres psql -c "SELECT pg_is_in_recovery();"

3. Test data synchronization.
   1. Run the following commands to create a test table on the primary node and insert data into the table:

      sudo -u postgres psql -c "CREATE TABLE test_huawei(id INT PRIMARY KEY, info TEXT);"
      sudo -u postgres psql -c "INSERT INTO test_huawei VALUES(1,'Huawei Cloud Ubuntu 20.04 PostgreSQL primary/standby');"

   2. Run the following command to query the table on the standby node to check whether the data has been synchronized.

      sudo -u postgres psql -c "SELECT * FROM test_huawei;"

   If the inserted data can be queried, the primary/standby replication has been established.

Debian

1. Run the following command to check the replication status on the primary node:

   sudo -u postgres psql -c "SELECT pid, usename, state, sync_state FROM pg_stat_replication;"

2. Run the following commands to check the WAL receiving status on the standby node:

   sudo -u postgres psql -c "SELECT * FROM pg_stat_wal_receiver;"
   sudo -u postgres psql -c "SELECT pg_is_in_recovery();"

3. Test data synchronization.
   1. Run the following commands to create a test table on the primary node and insert data into the table:

      sudo -u postgres psql -c "CREATE TABLE test_sync(id int);" 
      sudo -u postgres psql -c "INSERT INTO test_sync VALUES(1);"

   2. Run the following command to query the table on the standby node to check whether the data has been synchronized.

      sudo -u postgres psql -c "SELECT * FROM test_sync;"

   If the inserted data can be queried, the primary/standby replication has been established.