DocsICP FilingConsole
官方文档
首页

Set up a PostgreSQL primary-secondary architecture

更新时间：
复制 MD 格式
产品详情
我的收藏

Deploy PostgreSQL streaming replication between two ECS instances for read/write splitting, data redundancy, and manual failover.

How it works

image

Replication workflow

  1. WAL generation: The primary node processes write transactions and records changes to the WAL.

  2. WAL transmission: The walsender process on the primary streams WAL records in real time to the walreceiver process on the secondary.

  3. Data synchronization: The walreceiver on the secondary receives the WAL. The recovery process replays WAL records to keep the secondary in sync with the primary.

  4. Read-only service: The secondary serves read-only queries while synchronizing.

Procedure

Step 1: Prepare ECS instances

Create two Elastic Compute Service (ECS) instances as the primary and secondary nodes:

  • 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 VPC, region, and zone to minimize network latency. They also need public network access to download installation packages.

    The secondary node acts as a data replica for failover.

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

Example private IP addresses used in this tutorial:

  • Primary node: 192.168.1.10

  • Secondary node: 192.168.1.20

Step 2: Configure the primary node

Alibaba Cloud Linux 3/CentOS 8

The following steps use PostgreSQL 18.

  1. Log on to an ECS instance.

    1. Go to ECS console - Instances. In the top-left corner, select the region and resource group for the target instance.

    2. Navigate to the details page of the target instance. Click Connect and select Workbench. Follow the on-screen prompts to access the terminal.

  2. Install PostgreSQL 18.

    # 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 default configuration files.

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

  4. Edit /var/lib/pgsql/18/data/postgresql.conf to configure replication 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, create a replication user, and create a replication slot.

    1. Start PostgreSQL and enable it 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. This prevents the primary from removing WAL logs before the secondary receives them, allowing a disconnected secondary to resume without a full re-clone.

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

  6. Add a replication rule to the pg_hba.conf file (/var/lib/pgsql/18/data/pg_hba.conf) to allow the secondary node to connect.

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

    • replica_user: The replication username.

    • 192.168.1.20: Secondary node private IP address.

  7. Restart PostgreSQL to apply the changes.

    sudo systemctl restart postgresql-18.service

Ubuntu/Debian

Applies to Ubuntu 22.04+ and Debian 11+. The steps use PostgreSQL 18.

  1. Log on to an ECS instance.

    1. Go to ECS console - Instances. In the top-left corner, select the region and resource group for the target instance.

    2. Navigate to the details page of the target instance. Click Connect and select Workbench. Follow the on-screen prompts to access the terminal.

  2. Install PostgreSQL 18.

    # 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 /etc/postgresql/18/main/postgresql.conf to configure replication 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, create a replication user, and create a replication slot.

    1. Start PostgreSQL and enable it 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. This prevents the primary from removing WAL logs before the secondary receives them, allowing a disconnected secondary to resume without a full re-clone.

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

  5. Add a replication rule to the pg_hba.conf file (/etc/postgresql/18/main/pg_hba.conf) to allow the secondary node to connect.

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

    • replica_user: The replication username.

    • 192.168.1.20: Secondary node private IP address.

  6. Restart PostgreSQL to apply the changes.

    sudo systemctl restart postgresql.service

Alibaba Cloud Linux 2/CentOS 7

The following steps use PostgreSQL 15.

  1. Log on to an ECS instance.

    1. Go to ECS console - Instances. In the top-left corner, select the region and resource group for the target instance.

    2. Navigate to the details page of the target instance. Click Connect and select Workbench. Follow the on-screen prompts to access the terminal.

  2. Install PostgreSQL 15.

    # 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 default configuration files.

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

  4. Edit /var/lib/pgsql/15/data/postgresql.conf to configure replication 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, create a replication user, and create a replication slot.

    1. Start PostgreSQL and enable it 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. This prevents the primary from removing WAL logs before the secondary receives them, allowing a disconnected secondary to resume without a full re-clone.

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

  6. Add a replication rule to the pg_hba.conf file (/var/lib/pgsql/15/data/pg_hba.conf) to allow the secondary node to connect.

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

    • replica_user: The replication username.

    • 192.168.1.20: Secondary node private IP address.

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

  7. Restart PostgreSQL to apply the changes.

    sudo systemctl restart postgresql-15.service

Step 3: Configure the secondary node

Alibaba Cloud Linux 3/CentOS 8

  1. Install PostgreSQL 18.

    # 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 for the postgres user to store the replication password.

    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. Clone data from the primary with pg_basebackup.

    Important

    This overwrites the data directory /var/lib/pgsql/18/data/. Ensure it 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: Primary node IP address.

    • -D: Secondary node data directory.

    • -U: Replication username.

  4. Create a standby.signal file to enable standby mode.

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

  5. Configure replication in /var/lib/pgsql/18/data/postgresql.conf:

    The password is not required in primary_conninfo. PostgreSQL reads it 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.

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

Ubuntu/Debian

  1. Install PostgreSQL 18.

    # 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 for the postgres user to store the replication password.

    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. Clone data from the primary with pg_basebackup.

    Important

    This overwrites the data directory /var/lib/postgresql/18/main/. Ensure it 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: Primary node IP address.

    • -D: Secondary node data directory.

    • -U: Replication username.

  4. Create a standby.signal file to enable standby mode.

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

  5. Configure replication in /etc/postgresql/18/main/postgresql.conf:

    The password is not required in primary_conninfo. PostgreSQL reads it 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.

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

Alibaba Cloud Linux 2/CentOS 7

  1. Install PostgreSQL 15.

    # 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 for the postgres user to store the replication password.

    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. Clone data from the primary with pg_basebackup.

    Important

    This overwrites the data directory /var/lib/pgsql/15/data/. Ensure it 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: Primary node IP address.

    • -D: Secondary node data directory.

    • -U: Replication username.

  4. Create a standby.signal file to enable standby mode.

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

  5. Configure replication in /var/lib/pgsql/15/data/postgresql.conf:

    The password is not required in primary_conninfo. PostgreSQL reads it 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.

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

Step 4: Verify replication

  1. Check replication processes.

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

    • On the primary, check for the walsender process:

      ps aux | grep "walsender.*streaming"

      Expected output: postgres: walsender replica_user 192.168.1.20(xxxxx) streaming.

    • On the secondary, check for the walreceiver process:

      ps aux | grep "walreceiver.*streaming"

      Expected output: postgres: walreceiver streaming.

  2. Test data synchronization.

    Write data to the primary and verify it appears on the secondary.

    • On the primary, 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, query the table to verify synchronization.

      # 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 works correctly.

Previous：Manage a self-managed database on ECSNext：Set up a VPN