All Products
Search

This Product

    Elastic Compute Service:Set up a PostgreSQL primary-secondary architecture

    Document Center

    Elastic Compute Service:Set up a PostgreSQL primary-secondary architecture

    Last Updated:Dec 30, 2025

    Setting up a PostgreSQL primary-secondary architecture lets you implement read/write splitting to offload read queries to secondary nodes and improve performance. Secondary nodes also act as data replicas of the primary node. If the primary node fails, you can manually fail over to a secondary node, enhancing your system's high availability.

    Solution architecture

    image

    How it works

    1. Data writes and Write-Ahead Log (WAL) generation: The primary node processes write transactions and records data changes to the WAL.

    2. WAL transmission: The walsender process on the primary node uses streaming replication to transfer the WAL in real time to the walreceiver process on the secondary node.

    3. Data synchronization: The walreceiver process on the secondary node receives the WAL. The recovery process reads the WAL and applies the data changes to the secondary database, synchronizing it with the primary node.

    4. Read-only service: While continuously synchronizing, the secondary node serves read-only queries.

    Procedure

    Step 1: Prepare ECS instances and the network environment

    Create two Elastic Compute Service (ECS) instances to serve as the primary and secondary nodes. Instance configuration:

    • Specifications: ecs.c7.large (2 vCPU, 4 GiB) or higher.

    • Image: Alibaba Cloud Linux 3.2104 LTS 64-bit, CentOS 8.x 64-bit, or Ubuntu 22.04 64-bit.

    • Network: Both instances must be in the same Virtual Private Cloud (VPC), Region, and Availability Zone to minimize network latency and ensure data synchronization performance. They also need public network access to download installation packages.

      High availability is achieved through the primary-secondary architecture. The secondary node acts as a data replica for failover.

    • Security group: Assign both instances to the same security group.

    Example private IP addresses:

    • Primary node example private IP address: 192.168.1.10

    • Secondary node example private IP address: 192.168.1.20

    Step 2: Configure the PostgreSQL primary node

    Alibaba Cloud Linux 3/CentOS 8

    The following steps use PostgreSQL 18 as an example.

    1. Log on to the ECS instance.

      1. Go to ECS console - Instances. In the top navigation bar, select the target region and resource group.

      2. Go to the details page of the target instance. Click Connect and select Workbench. Follow the prompts on the page to log on to the terminal.

    2. Install PostgreSQL 18. Configure the YUM repository and install the server package.

      # Check the OS type. If it is CentOS 8, disable the default PostgreSQL module.
if [ -f /etc/os-release ]; then
    . /etc/os-release
    if [ "$ID" = "centos" ] && [ "${VERSION_ID%%.*}" = "8" ]; then
        sudo dnf --assumeyes module disable postgresql
    fi
fi
# Add the official PostgreSQL YUM repository.
sudo rpm -Uvh http://mirrors.cloud.aliyuncs.com/postgresql/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# Replace the repository URL with an Alibaba Cloud mirror to speed up downloads.
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
# Install the PostgreSQL 18 server.
sudo dnf install -y postgresql18-server

    3. Initialize the database. This creates the data directory /var/lib/pgsql/18/data/ and generates default configuration files.

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

    4. Edit the /var/lib/pgsql/18/data/postgresql.conf file to configure the listening address, WAL level, and other parameters.

      # Listen for connections from all network interfaces.
listen_addresses = '*'
# Set the WAL level to replica.
wal_level = replica
# Set the maximum number of streaming replication connections. This should be greater than or equal to the number of secondary nodes.
max_wal_senders = 3
# Enable WAL archiving. If a secondary node is offline for a long time and the primary node cleans up old WALs, the secondary node can automatically recover from the archive.
archive_mode = on
# Configure the archiving scheme. This example archives to /mnt/server/archivedir/.
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'

    5. Start the service and create a PostgreSQL user and a replication slot.

      1. Start the PostgreSQL service and enable it to start on boot.

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

      2. Create a PostgreSQL user with REPLICATION permissions.

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

      3. Create a physical replication slot. A replication slot prevents the primary node from removing WAL logs before the secondary has received them. This ensures a disconnected secondary can resume synchronization after reconnecting without needing a full re-clone.

        sudo -u postgres psql -c "SELECT pg_create_physical_replication_slot('secondary_slot');"

    6. Configure the pg_hba.conf file to allow connections from the secondary node. Add an authentication rule at the end of the /var/lib/pgsql/18/data/pg_hba.conf file to allow the secondary node to connect as the PostgreSQL user.

      # TYPE  DATABASE        USER            ADDRESS                 METHOD
host    replication     replica_user    192.168.1.20/32         scram-sha-256

      • replica_user: The PostgreSQL username.

      • 192.168.1.20: The private IP address of the secondary node.

    7. Restart the service to apply the changes.

      sudo systemctl restart postgresql-18.service

    Ubuntu/Debian

    This guide applies to Ubuntu 22.04 and later, and Debian 11 and later. The steps use PostgreSQL 18 as an example.

    1. Log on to the ECS instance.

      1. Go to ECS console - Instances. In the top navigation bar, select the target region and resource group.

      2. Go to the details page of the target instance. Click Connect and select Workbench. Follow the prompts on the page to log on to the terminal.

    2. Install PostgreSQL 18. Configure the PostgreSQL APT repository and install the package.

      # Add the official PostgreSQL APT repository and use an Alibaba Cloud mirror.
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'

# Import the repository signature key.
wget -qO - http://mirrors.cloud.aliyuncs.com/postgresql/repos/apt/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg

# Update the package list and install.
sudo apt-get update
sudo apt-get install -y postgresql-18

    3. Edit the /etc/postgresql/18/main/postgresql.conf file to configure the listening address, WAL level, and other parameters.

      # Listen for connections from all network interfaces.
listen_addresses = '*'
# Set the WAL level to replica.
wal_level = replica
# Set the maximum number of streaming replication connections. This should be greater than or equal to the number of secondary nodes.
max_wal_senders = 3
# Enable WAL archiving. If a secondary node is offline for a long time and the primary node cleans up old WALs, the secondary node can automatically recover from the archive.
archive_mode = on
# Configure the archiving scheme. This example archives to /mnt/server/archivedir/.
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'

    4. Start the service and create a PostgreSQL user and a replication slot.

      1. Start the PostgreSQL service and enable it to start on boot.

        sudo systemctl enable postgresql.service
sudo systemctl start postgresql.service

      2. Create a PostgreSQL user with REPLICATION permissions.

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

      3. Create a physical replication slot. A replication slot prevents the primary node from removing WAL logs before the secondary has received them. This ensures a disconnected secondary can resume synchronization after reconnecting without needing a full re-clone.

        sudo -u postgres psql -c "SELECT pg_create_physical_replication_slot('secondary_slot');"

    5. Configure the pg_hba.conf file to allow connections from the secondary node. Add an authentication rule at the end of the /etc/postgresql/18/main/pg_hba.conf file to allow the secondary node to connect as the PostgreSQL user.

      # TYPE  DATABASE        USER            ADDRESS                 METHOD
host    replication     replica_user    192.168.1.20/32         scram-sha-256

      • replica_user: The PostgreSQL username.

      • 192.168.1.20: The private IP address of the secondary node.

    6. Restart the service to apply the changes.

      sudo systemctl restart postgresql.service

    Alibaba Cloud Linux 2/CentOS 7

    The following steps use PostgreSQL 15 as an example.

    1. Log on to the ECS instance.

      1. Go to ECS console - Instances. In the top navigation bar, select the target region and resource group.

      2. Go to the details page of the target instance. Click Connect and select Workbench. Follow the prompts on the page to log on to the terminal.

    2. Install PostgreSQL 15. Configure the YUM repository and install the server package.

      # Add the official PostgreSQL YUM repository.
sudo rpm -Uvh http://mirrors.cloud.aliyuncs.com/postgresql/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# Replace the repository URL with an Alibaba Cloud mirror to speed up downloads.
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 the PostgreSQL 15 server.
sudo yum install -y postgresql15-server

    3. Initialize the database. This creates the data directory /var/lib/pgsql/15/data/ and generates default configuration files.

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

    4. Edit the /var/lib/pgsql/15/data/postgresql.conf file to configure the listening address, WAL level, and other parameters.

      # Listen for connections from all network interfaces.
listen_addresses = '*'
# Set the WAL level to replica.
wal_level = replica
# Set the maximum number of streaming replication connections. This should be greater than or equal to the number of secondary nodes.
max_wal_senders = 3
# Enable WAL archiving. If a secondary node is offline for a long time and the primary node cleans up old WALs, the secondary node can automatically recover from the archive.
archive_mode = on
# Configure the archiving scheme. This example archives to /mnt/server/archivedir/.
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'

    5. Start the service and create a PostgreSQL user and a replication slot.

      1. Start the PostgreSQL service and enable it to start on boot.

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

      2. Create a PostgreSQL user with REPLICATION permissions.

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

      3. Create a physical replication slot. A replication slot prevents the primary node from removing WAL logs before the secondary has received them. This ensures a disconnected secondary can resume synchronization after reconnecting without needing a full re-clone.

        sudo -u postgres psql -c "SELECT pg_create_physical_replication_slot('secondary_slot');"

    6. Configure the pg_hba.conf file to allow connections from the secondary node. Add an authentication rule at the end of the /var/lib/pgsql/15/data/pg_hba.conf file to allow the secondary node to connect as the PostgreSQL user.

      # TYPE  DATABASE        USER            ADDRESS                 METHOD
host    replication     replica_user    192.168.1.20/32         scram-sha-256

      • replica_user: The PostgreSQL username.

      • 192.168.1.20: The private IP address of the secondary node.

      • scram-sha-256: A more secure password authentication method than md5.

    7. Restart the service to apply the changes.

      sudo systemctl restart postgresql-15.service

    Step 3: Configure the PostgreSQL secondary node

    Alibaba Cloud Linux 3/CentOS 8

    1. Install PostgreSQL 18. Configure the YUM repository and install the server package.

      # Check the OS type. If it is CentOS 8, disable the default PostgreSQL module.
if [ -f /etc/os-release ]; then
    . /etc/os-release
    if [ "$ID" = "centos" ] && [ "${VERSION_ID%%.*}" = "8" ]; then
        sudo dnf --assumeyes module disable postgresql
    fi
fi
# Add the official PostgreSQL YUM repository.
sudo rpm -Uvh http://mirrors.cloud.aliyuncs.com/postgresql/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# Replace the repository URL with an Alibaba Cloud mirror to speed up downloads.
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
# Install the PostgreSQL 18 server.
sudo dnf install -y postgresql18-server

    2. Create a .pgpass file in the postgres user's home directory to store the password for automatic authentication.

      1. Switch to the postgres user.

        sudo su - postgres

      2. Create and configure the .pgpass file.

        echo "192.168.1.10:5432:replication:replica_user:YOUR_SECURE_PASSWORD" > ~/.pgpass
chmod 600 ~/.pgpass

      3. Exit the postgres user session.

        exit

    3. Use pg_basebackup to clone data from the primary node.

      Important

      This operation clones all data from the primary node, overwriting the target data directory /var/lib/pgsql/18/data/. Ensure that this directory is empty.

      # Perform the base backup as the postgres user.
sudo -u postgres pg_basebackup -h 192.168.1.10 -D /var/lib/pgsql/18/data/ -U replica_user -P -v --wal-method=stream

      • -h: The IP address of the primary node.

      • -D: The data directory of the secondary node.

      • -U: The PostgreSQL username.

    4. Create a standby.signal file to enable standby mode. This empty file tells PostgreSQL to start in standby mode instead of as a primary.

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

    5. Configure the secondary to connect to the primary. Edit the /var/lib/pgsql/18/data/postgresql.conf file and add the following settings:

      You do not need to include the password in primary_conninfo. PostgreSQL automatically uses the password from the .pgpass file.
      # Primary node connection information for streaming replication.
# The application_name must match the replication slot name created on the primary node.
primary_conninfo = 'host=192.168.1.10 port=5432 user=replica_user application_name=secondary_node1'
# Specify the replication slot name. It must match the name of the slot created on the primary node.
primary_slot_name = 'secondary_slot'
# Enable hot standby mode to allow read-only queries during synchronization.
hot_standby = on

    6. Start the secondary node service.

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

    Ubuntu/Debian

    1. Install PostgreSQL 18. Configure the PostgreSQL APT repository and install the package.

      # Add the official PostgreSQL APT repository and use an Alibaba Cloud mirror.
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'

# Import the repository signature key.
wget -qO - http://mirrors.cloud.aliyuncs.com/postgresql/repos/apt/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg

# Update the package list and install.
sudo apt-get update
sudo apt-get install -y postgresql-18

    2. Create a .pgpass file in the postgres user's home directory to store the password for automatic authentication.

      1. Switch to the postgres user.

        sudo su - postgres

      2. Create and configure the .pgpass file.

        echo "192.168.1.10:5432:replication:replica_user:YOUR_SECURE_PASSWORD" > ~/.pgpass
chmod 600 ~/.pgpass

      3. Exit the postgres user session.

        exit

    3. Use pg_basebackup to clone data from the primary node.

      Important

      This operation clones all data from the primary node, overwriting the target data directory /var/lib/postgresql/18/main/. Ensure that this directory is empty.

      # Perform the base backup as the postgres user.
sudo -u postgres pg_basebackup -h 192.168.1.10 -D /var/lib/postgresql/18/main/ -U replica_user -P -v --wal-method=stream

      • -h: The IP address of the primary node.

      • -D: The data directory of the secondary node.

      • -U: The PostgreSQL username.

    4. Create a standby.signal file to enable standby mode. This empty file tells PostgreSQL to start in standby mode instead of as a primary.

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

    5. Configure the secondary to connect to the primary. Edit the /etc/postgresql/18/main/postgresql.conf file and add the following settings:

      You do not need to include the password in primary_conninfo. PostgreSQL automatically uses the password from the .pgpass file.
      # Primary node connection information for streaming replication.
# The application_name must match the replication slot name created on the primary node.
primary_conninfo = 'host=192.168.1.10 port=5432 user=replica_user application_name=secondary_node1'
# Specify the replication slot name. It must match the name of the slot created on the primary node.
primary_slot_name = 'secondary_slot'
# Enable hot standby mode to allow read-only queries during synchronization.
hot_standby = on

    6. Start the secondary node service.

      sudo systemctl enable postgresql.service
sudo systemctl start postgresql.service

    Alibaba Cloud Linux 2/CentOS 7

    1. Install PostgreSQL 15. Configure the YUM repository and install the server package.

      # Add the official PostgreSQL YUM repository.
sudo rpm -Uvh http://mirrors.cloud.aliyuncs.com/postgresql/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# Replace the repository URL with an Alibaba Cloud mirror to speed up downloads.
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 the PostgreSQL 15 server.
sudo yum install -y postgresql15-server

    2. Create a .pgpass file in the postgres user's home directory to store the password for automatic authentication.

      1. Switch to the postgres user.

        sudo su - postgres

      2. Create and configure the .pgpass file.

        echo "192.168.1.10:5432:replication:replica_user:YOUR_SECURE_PASSWORD" > ~/.pgpass
chmod 600 ~/.pgpass

      3. Exit the postgres user session.

        exit

    3. Use pg_basebackup to clone data from the primary node.

      Important

      This operation clones all data from the primary node, overwriting the target data directory /var/lib/pgsql/15/data/. Ensure that this directory is empty.

      # Perform the base backup as the postgres user.
sudo -u postgres pg_basebackup -h 192.168.1.10 -D /var/lib/pgsql/15/data/ -U replica_user -P -v --wal-method=stream

      • -h: The IP address of the primary node.

      • -D: The data directory of the secondary node.

      • -U: The PostgreSQL username.

    4. Create a standby.signal file to enable standby mode. This empty file tells PostgreSQL to start in standby mode instead of as a primary.

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

    5. Configure the secondary to connect to the primary. Edit the /var/lib/pgsql/15/data/postgresql.conf file and add the following settings:

      You do not need to include the password in primary_conninfo. PostgreSQL automatically uses the password from the .pgpass file.
      # Primary node connection information for streaming replication.
# The application_name must match the replication slot name created on the primary node.
primary_conninfo = 'host=192.168.1.10 port=5432 user=replica_user application_name=secondary_node1'
# Specify the replication slot name. It must match the name of the slot created on the primary node.
primary_slot_name = 'secondary_slot'
# Enable hot standby mode to allow read-only queries during synchronization.
hot_standby = on

    6. Start the secondary node service.

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

    Step 4: Verify the replication status

    1. Check the replication-related processes.

      The walsender (on the primary node) and walreceiver (on the secondary node) processes handle data synchronization. Running processes indicate an active replication connection.

      • On the primary node, check for the walsender process:

        ps aux | grep "walsender.*streaming"

        The expected output is similar to: postgres: walsender replica_user 192.168.1.20(xxxxx) streaming.

      • On the secondary node, check for the walreceiver process:

        ps aux | grep "walreceiver.*streaming"

        The expected output is similar to: postgres: walreceiver streaming.

    2. Perform a data synchronization test.

      Test the replication by writing data to the primary node and verifying its presence on the secondary node.

      • On the primary node, create a test table and insert data.

        sudo -u postgres psql -c "CREATE TABLE replication_test (id serial primary key, test_data text, created_at timestamptz default now());"
sudo -u postgres psql -c "INSERT INTO replication_test (test_data) VALUES ('hello replication');"

      • On the secondary node, query the table to verify that the data has been synchronized.

        # Primary-secondary synchronization usually has some delay. Wait a few seconds before you run the query.
sudo -u postgres psql -c "SELECT * FROM replication_test;"

        If the query returns the inserted data, replication is configured correctly.