All Products
Search

This Product

    Elastic Compute Service:Build a primary/secondary PostgreSQL architecture

    Document Center

    Elastic Compute Service:Build a primary/secondary PostgreSQL architecture

    Last Updated:Feb 14, 2025

    PostgreSQL is an open source database system that follows SQL standards and supports recursive queries, full-text searches, and geographic information system (GIS)-based geospatial queries for NoSQL data types, such as JSON, XML, and hstore. PostgreSQL can satisfy complex data analysis requirements. PostgreSQL is widely used in Internet websites, enterprise-level application backends, data warehousing and analysis platforms, location-based applications, and complex data object processing. This topic describes how to build a primary/secondary PostgreSQL architecture on Linux Elastic Compute Service (ECS) instances.

    Prerequisites

    • Two ECS instances are created. One ECS instance is used as the primary PostgreSQL node, and the other ECS instance is used as the secondary PostgreSQL node. For information about how to create an ECS instance, see Create an instance on the Custom Launch tab.

    • An inbound rule is added to a security group of each ECS instance to open port 22. For information about how to add a security group rule, see Add a security group rule.

    Step 1: Deploy the primary PostgreSQL node

    In this topic, PostgreSQL 15 is used. You can install an appropriate PostgreSQL version based on your business requirements.

    Alibaba Cloud Linux 3 or CentOS 8

    1. Add a PostgreSQL image repository and configure files. 

      # Add a PostgreSQL image repository.
sudo rpm -Uvh http://mirrors.cloud.aliyuncs.com/postgresql/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# Use the sed utility to edit the /etc/yum.repos.d/pgdg-redhat-all.repo file.
sudo sed -i "s@https://download.postgresql.org/pub@http://mirrors.cloud.aliyuncs.com/postgresql@g" /etc/yum.repos.d/pgdg-redhat-all.repo
sudo sed -i "s@\$releasever@8@g" /etc/yum.repos.d/pgdg-redhat-all.repo
# Create the /var/lib/pgsql/15/data/ directory.
sudo mkdir -p /var/lib/pgsql/15/data/

    2. (Optional) Run the following command to disable the default PostgreSQL module if your ECS instance runs CentOS 8: 

      sudo dnf --assumeyes module disable postgresql

    3. Install PostgreSQL 15. 

      sudo dnf install -y postgresql15-server

    4. Initialize the PostgreSQL database. 

      sudo /usr/pgsql-15/bin/postgresql-15-setup initdb

      The following command output indicates that the PostgreSQL database is initialized.

      image

    5. Configure the primary node.

      Modify the configurations of the primary node as follows: 

      # wal_level: the level of information to be written into the write-ahead log (WAL). Set wal_level to replica to write sufficient data to support WAL archiving and replication. 
sudo sed -i "s@#wal_level = replica@wal_level = replica@g" /var/lib/pgsql/15/data/postgresql.conf
# max_wal_senders: the maximum number of WAL sender processes.
sudo sed -i "s@#max_wal_senders = 10@max_wal_senders = 10@g" /var/lib/pgsql/15/data/postgresql.conf
# wal_keep_size: the minimum size of past WAL files kept on the primary node.
sudo sed -i "s@#wal_keep_size = 0@wal_keep_size = 128MB@g" /var/lib/pgsql/15/data/postgresql.conf
# # archive_mode: whether to enable WAL archiving. Set the value to on.
sudo sed -i "s@#archive_mode = off@archive_mode = on@g" /var/lib/pgsql/15/data/postgresql.conf
# listen_addresses: the IP addresses on which the server listens for connections from client applications.
sudo sed -i "s@#listen_addresses = 'localhost'@listen_addresses = '*'@g" /var/lib/pgsql/15/data/postgresql.conf

    6. Modify the pg_hba.conf file to configure the permissions for the secondary node to connect to the primary node.

      • Replace <YOUR_USER> with a username of the secondary node.

      • Replace <Private IP address or CIDR block of the secondary node> with the private IP address of the ECS instance used as the secondary node or the CIDR block to which the private IP address belongs. 

      echo "host replication <YOUR_USER> <Private IP address or CIDR block of the secondary node> md5" | sudo tee -a /var/lib/pgsql/15/data/pg_hba.conf

    7. Start PostgreSQL and enable PostgreSQL to run on system startup. 

      sudo systemctl enable postgresql-15.service
sudo systemctl start postgresql-15.service

    8. Create a replication user.

      • Replace <YOUR_USER> with a username of the secondary node.

      • Replace <YOUR_PASSWORD> with the password of the secondary node. 

      cd /
sudo -u postgres psql -c "CREATE ROLE <YOUR_USER> REPLICATION LOGIN PASSWORD '<YOUR_PASSWORD>';"

    9. Restart PostgreSQL. 

      sudo systemctl restart postgresql-15.service

    Alibaba Cloud Linux 2 or CentOS 7

    1. Install PostgreSQL 15. 

      # Add a PostgreSQL image repository.
sudo rpm -Uvh http://mirrors.cloud.aliyuncs.com/postgresql/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# Use the sed utility to edit the /etc/yum.repos.d/pgdg-redhat-all.repo file.
sudo sed -i "s@https://download.postgresql.org/pub@http://mirrors.cloud.aliyuncs.com/postgresql@g" /etc/yum.repos.d/pgdg-redhat-all.repo
sudo sed -i "s@\$releasever@7@g" /etc/yum.repos.d/pgdg-redhat-all.repo
# Create the /var/lib/pgsql/15/data/ directory.
sudo mkdir -p /var/lib/pgsql/15/data/
# Install PostgreSQL 15.
sudo yum install -y postgresql15-server

    2. Initialize the PostgreSQL database. 

      sudo /usr/pgsql-15/bin/postgresql-15-setup initdb

      The following command output indicates that the PostgreSQL database is initialized.

      image

    3. Configure the primary node.

      Modify the configurations of the primary node as follows: 

      # wal_level: the level of information to be written into the write-ahead log (WAL). Set wal_level to replica to write sufficient data to support WAL archiving and replication. 
sudo sed -i "s@#wal_level = replica@wal_level = replica@g" /var/lib/pgsql/15/data/postgresql.conf
# max_wal_senders: the maximum number of WAL sender processes.
sudo sed -i "s@#max_wal_senders = 10@max_wal_senders = 10@g" /var/lib/pgsql/15/data/postgresql.conf
# wal_keep_size: the minimum size of past WAL files kept on the primary node.
sudo sed -i "s@#wal_keep_size = 0@wal_keep_size = 128MB@g" /var/lib/pgsql/15/data/postgresql.conf
# # archive_mode: whether to enable WAL archiving. Set the value to on.
sudo sed -i "s@#archive_mode = off@archive_mode = on@g" /var/lib/pgsql/15/data/postgresql.conf
# listen_addresses: the IP addresses on which the server listens for connections from client applications.
sudo sed -i "s@#listen_addresses = 'localhost'@listen_addresses = '*'@g" /var/lib/pgsql/15/data/postgresql.conf

    4. Modify the pg_hba.conf file to configure the permissions for the secondary node to connect to the primary node.

      • Replace <YOUR_USER> with a username of the secondary node.

      • Replace <Private IP address or CIDR block of the secondary node> with the private IP address of the ECS instance used as the secondary node or the CIDR block to which the private IP address belongs. 

      echo "host replication <YOUR_USER> <Private IP address or CIDR block of the secondary node> md5" | sudo tee -a /var/lib/pgsql/15/data/pg_hba.conf

    5. Start PostgreSQL and enable PostgreSQL to run on system startup. 

      sudo systemctl enable postgresql-15.service
sudo systemctl start postgresql-15.service

    6. Create a replication user.

      • Replace <YOUR_USER> with a username of the secondary node.

      • Replace <YOUR_PASSWORD> with the password of the secondary node. 

      cd /
sudo -u postgres psql -c "CREATE ROLE <YOUR_USER> REPLICATION LOGIN PASSWORD '<YOUR_PASSWORD>';"

    7. Restart PostgreSQL. 

      sudo systemctl restart postgresql-15.service

    Ubuntu or Debian

    1. Install PostgreSQL 15. 

      # Add a PostgreSQL image repository. 
sudo sh -c 'echo "deb [signed-by=/etc/apt/trusted.gpg.d/postgresql.gpg] http://mirrors.cloud.aliyuncs.com/postgresql/repos/apt/ $(lsb_release -sc)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# Download and export a GNU Privacy Guard (GPG) key and convert the key into a format suitable for Advanced Package Tool (APT). 
wget -qO - http://mirrors.cloud.aliyuncs.com/postgresql/repos/apt/ACCC4CF8.asc | sudo apt-key add -
sudo apt-key export ACCC4CF8 | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg
# Update the software package list. 
sudo apt-get update
# Install PostgreSQL 15.
sudo apt-get install -y postgresql-15

    2. Configure the primary node.

      Modify the configurations of the primary node as follows: 

      sudo sed -i "s@#listen_addresses = 'localhost'@listen_addresses = '*'@g" /etc/postgresql/15/main/postgresql.conf
#wal_level = replica
sudo sed -i "s@#wal_level = replica@wal_level = replica@g" /etc/postgresql/15/main/postgresql.conf
# max_wal_senders: the maximum number of WAL sender processes.
sudo sed -i "s@#max_wal_senders = 10@max_wal_senders = 10@g" /etc/postgresql/15/main/postgresql.conf
# wal_keep_size: the minimum size of past WAL files kept on the primary node.
sudo sed -i "s@#wal_keep_size = 0@wal_keep_size = 128MB@g" /etc/postgresql/15/main/postgresql.conf
# # archive_mode: whether to enable WAL archiving. Set the value to on.
sudo sed -i "s@#archive_mode = off@archive_mode = on@g" /etc/postgresql/15/main/postgresql.conf

    3. Modify the pg_hba.conf file to configure the permissions for the secondary node to connect to the primary node.

      • Replace <YOUR_USER> with a username of the secondary node.

      • Replace <Private IP address or CIDR block of the secondary node> with the private IP address of the ECS instance used as the secondary node or the CIDR block to which the private IP address belongs. 

      echo "host replication <YOUR_USER> <Private IP address or CIDR block of the secondary node> md5" | sudo tee -a /etc/postgresql/15/main/pg_hba.conf

    4. Create a replication user.

      • Replace <YOUR_USER> with a username of the secondary node.

      • Replace <YOUR_PASSWORD> with the password of the secondary node. 

      cd /
sudo -u postgres psql -c "CREATE ROLE <YOUR_USER> REPLICATION LOGIN PASSWORD '<YOUR_PASSWORD>';"

    5. Restart PostgreSQL and enable PostgreSQL to start on system startup. 

      sudo systemctl restart postgresql.service
sudo systemctl enable postgresql.service

    Step 2: Deploy the PostgreSQL secondary node

    In this topic, PostgreSQL 15 is used. You can install an appropriate PostgreSQL version based on your business requirements.

    Alibaba Cloud Linux 3 or CentOS 8

    1. Add a PostgreSQL image repository and configure files. 

      # Add a PostgreSQL image repository.
sudo rpm -Uvh http://mirrors.cloud.aliyuncs.com/postgresql/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# Use the sed utility to edit the /etc/yum.repos.d/pgdg-redhat-all.repo file.
sudo sed -i "s@https://download.postgresql.org/pub@http://mirrors.cloud.aliyuncs.com/postgresql@g" /etc/yum.repos.d/pgdg-redhat-all.repo
sudo sed -i "s@\$releasever@8@g" /etc/yum.repos.d/pgdg-redhat-all.repo

    2. (Optional) Run the following command to disable the default PostgreSQL module if your ECS instance runs CentOS 8: 

      sudo dnf --assumeyes module disable postgresql

    3. Install PostgreSQL 15. 

      sudo dnf install -y postgresql15-server

    4. Use the pg_basebackup utility to create a base backup of the primary node on the secondary node.

      • Replace <YOUR_USER> with a username of the primary node.

      • Replace <YOUR_PASSWORD> with the password of the primary node.

      • Replace <Private IP address of the primary node> with the private IP address of the ECS instance that is used as the primary node. 

      cd /
export PGPASSWORD=<YOUR_PASSWORD>
sudo -E -u postgres pg_basebackup -h <Private IP address of the primary node> -D /var/lib/pgsql/15/data/ -U <YOUR_USER> -P -w -v --wal-method=stream

    5. Modify the postgresql.conf file of the secondary node. 

      # hot_standby: whether to enable read-only queries on the secondary node. Set the value to on.
sudo sed -i "s@#hot_standby = off@hot_standby = on@g" /var/lib/pgsql/15/data/postgresql.conf
# hot_standby_feedback: whether to allow the secondary node to send feedback about replication status and progress to the primary node. Set the value to on.
sudo sed -i "s@#hot_standby_feedback = off@hot_standby_feedback = on@g" /var/lib/pgsql/15/data/postgresql.conf

    6. Configure the connection information of the primary node.

      • Replace <Private IP address of the primary node> with the private IP address of the ECS instance that is used as the primary node.

      • Replace <YOUR_USER> with a username of the primary node.

      • Replace <YOUR_PASSWORD> with the password of the primary node. 

      sudo sed -i "s@#primary_conninfo = ''@primary_conninfo = 'host=<Private IP address of the primary node> port=5432 user=<YOUR_USER> password=<YOUR_PASSWORD>'@g" /var/lib/pgsql/15/data/postgresql.conf

    7. Configure the secondary node to be able to take over from the primary node. 

      sudo -u postgres touch /var/lib/pgsql/15/data/standby.signal

    8. Start PostgreSQL and enable PostgreSQL to automatically run on system startup. 

      sudo systemctl enable postgresql-15.service
sudo systemctl start postgresql-15.service

    Alibaba Cloud Linux 2 or CentOS 7

    1. Install PostgreSQL 15. 

      # Add a PostgreSQL image repository.
sudo rpm -Uvh http://mirrors.cloud.aliyuncs.com/postgresql/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# Use the sed utility to edit the /etc/yum.repos.d/pgdg-redhat-all.repo file.
sudo sed -i "s@https://download.postgresql.org/pub@http://mirrors.cloud.aliyuncs.com/postgresql@g" /etc/yum.repos.d/pgdg-redhat-all.repo
sudo sed -i "s@\$releasever@7@g" /etc/yum.repos.d/pgdg-redhat-all.repo
# Install PostgreSQL 15.
sudo yum install -y postgresql15-server

    2. Use the pg_basebackup utility to create a base backup of the primary node on the secondary node.

      • Replace <YOUR_USER> with a username of the primary node.

      • Replace <YOUR_PASSWORD> with the password of the primary node.

      • Replace <Private IP address of the primary node> with the private IP address of the ECS instance that is used as the primary node. 

      cd /
export PGPASSWORD=<YOUR_PASSWORD>
sudo -E -u postgres pg_basebackup -h <Private IP address of the primary node> -D /var/lib/pgsql/15/data/ -U <YOUR_USER> -P -w -v --wal-method=stream

    3. Modify the postgresql.conf file of the secondary node. 

      # hot_standby: whether to enable read-only queries on the secondary node. Set the value to on.
sudo sed -i "s@#hot_standby = off@hot_standby = on@g" /var/lib/pgsql/15/data/postgresql.conf
# hot_standby_feedback: whether to allow the secondary node to send feedback about replication status and progress to the primary node. Set the value to on.
sudo sed -i "s@#hot_standby_feedback = off@hot_standby_feedback = on@g" /var/lib/pgsql/15/data/postgresql.conf

    4. Configure the connection information of the primary node.

      • Replace <Private IP address of the primary node> with the private IP address of the ECS instance that is used as the primary node.

      • Replace <YOUR_USER> with a username of the primary node.

      • Replace <YOUR_PASSWORD> with the password of the primary node. 

      sudo sed -i "s@#primary_conninfo = ''@primary_conninfo = 'host=<Private IP address of the primary node> port=5432 user=<YOUR_USER> password=<YOUR_PASSWORD>'@g" /var/lib/pgsql/15/data/postgresql.conf

    5. Configure the secondary node to be able to take over from the primary node. 

      sudo -u postgres touch /var/lib/pgsql/15/data/standby.signal

    6. Start PostgreSQL and enable PostgreSQL to automatically run on system startup. 

      sudo systemctl enable postgresql-15.service
sudo systemctl start postgresql-15.service

    Ubuntu or Debian

    1. Install PostgreSQL 15. 

      # Add a PostgreSQL image repository. 
sudo sh -c 'echo "deb [signed-by=/etc/apt/trusted.gpg.d/postgresql.gpg] http://mirrors.cloud.aliyuncs.com/postgresql/repos/apt/ $(lsb_release -sc)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# Download and export a GNU Privacy Guard (GPG) key and convert the key into a format suitable for Advanced Package Tool (APT). 
wget -qO - http://mirrors.cloud.aliyuncs.com/postgresql/repos/apt/ACCC4CF8.asc | sudo apt-key add -
sudo apt-key export ACCC4CF8 | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg
# Update the software package list. 
sudo apt-get update
# Install PostgreSQL 15.
sudo apt-get install -y postgresql-15

    2. Stop PostgreSQL. 

      sudo systemctl stop postgresql.service

    3. Delete the initialization data of the secondary node. 

      sudo rm -rf /var/lib/postgresql/15/main/

    4. Use the pg_basebackup utility to create a base backup of the primary node on the secondary node.

      • Replace <YOUR_USER> with a username of the primary node.

      • Replace <YOUR_PASSWORD> with the password of the primary node.

      • Replace <Private IP address of the primary node> with the private IP address of the ECS instance that is used as the primary node. 

      cd /
export PGPASSWORD=<YOUR_PASSWORD>
sudo -E -u postgres pg_basebackup -h <Private IP address of the primary node> -D /var/lib/postgresql/15/main/ -U <YOUR_USER> -P -w -v --wal-method=stream

    5. Modify the postgresql.conf file of the secondary node. 

      # hot_standby: whether to enable read-only queries on the secondary node. Set the value to on.
sudo sed -i "s@#hot_standby = off@hot_standby = on@g" /etc/postgresql/15/main/postgresql.conf
# hot_standby_feedback: whether to allow the secondary node to send feedback about replication status and progress to the primary node. Set the value to on.
sudo sed -i "s@#hot_standby_feedback = off@hot_standby_feedback = on@g" /etc/postgresql/15/main/postgresql.conf

    6. Configure the connection information of the primary node.

      • Replace <Private IP address of the primary node> with the private IP address of the ECS instance that is used as the primary node.

      • Replace <YOUR_USER> with a username of the primary node.

      • Replace <YOUR_PASSWORD> with the password of the primary node. 

      sudo sed -i "s@#primary_conninfo = ''@primary_conninfo = 'host=<Private IP address of the primary node> port=5432 user=<YOUR_USER> password=<YOUR_PASSWORD>'@g" /etc/postgresql/15/main/postgresql.conf

    7. Configure the secondary node to be able to take over from the primary node. 

      sudo -u postgres touch /var/lib/postgresql/15/main/standby.signal

    8. Restart PostgreSQL and enable PostgreSQL to start on system startup. 

      sudo systemctl restart postgresql.service
sudo systemctl enable postgresql.service

    Test the primary/secondary PostgreSQL architecture

    Test the primary/secondary PostgreSQL architecture when data is exchanged between the primary and secondary nodes. For example, if you test the architecture when you run the following command to create a backup directory of the primary node on the secondary node, you can obtain the expected test result: 

    sudo pg_basebackup -D /var/lib/pgsql/15/data -h <IP address of the primary node> -p 5432 -U replica -X stream -P

    1. Run the following command on the primary node to check whether the sender process is available: 

      ps aux |grep sender

      The following command output indicates that the sender process is available: 

      postgres  2916  0.0  0.3 340388  3220 ?        Ss   15:38   0:00 postgres: walsender  replica 192.168.**.**(49640) streaming 0/F01C1A8

    2. Run the following command on the secondary node to check whether the receiver process is available: 

      ps aux |grep receiver

      The following command output indicates that the receiver process is available: 

      postgres 23284  0.0  0.3 387100  3444 ?        Ss   16:04   0:00 postgres: walreceiver   streaming 0/F01C1A8

    3. On the primary node, access the PostgreSQL interactive terminal and execute an SQL statement to check the status of the secondary node.

      1. Run the following command to log on to PostgreSQL by using the postgres account: 

        sudo su - postgres

      2. Run the following command to access the PostgreSQL interactive terminal: 

        psql

      3. Execute the following statement to check the status of the secondary node: 

        select * from pg_stat_replication;

        The following result indicates the status of the secondary node: 

        pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_location | write_locati
on | flush_location | replay_location | sync_priority | sync_state 
------+----------+---------+------------------+---------------+-----------------+------------- +-------------------------------+--------------+-----------+---------------+-------------
---+----------------+-----------------+---------------+------------
2916 | 16393 | replica | walreceiver | 192.168.**.** | | 49640 | 2017-05-02 15:38:06.188988+08 | 1836 | streaming | 0/F01C0C8 | 0/F01C0C8 
| 0/F01C0C8 | 0/F01C0C8 | 0 | async
(1 rows)

      4. Run the following command and press the Enter key to exit the PostgreSQL interactive terminal: 

        \q

      5. Run the following command and press the Enter key to exit PostgreSQL: 

        exit