PostgreSQL is regarded as the most advanced open source database. ApsaraDB RDS for PostgreSQL is compatible with NoSQL databases, supports efficient queries and plug-in management, and provides secure and stable services. This topic describes how to build a primary/secondary PostgreSQL system based on ECS.

Prerequisites

  • You must have an Alibaba Cloud account before you follow the instructions provided in the tutorial. To create an Alibaba Cloud account, click Create an Alibaba Cloud account.
  • You have added an inbound rule to the security group of the ECS instance to support Port 5432. For more information, see Add security group rules.

Background information

The procedure described in this topic is applicable to Alibaba Cloud users that are familiar with Alibaba Cloud ECS instances, the Linux operating system, and PostgreSQL databases.

The following software versions are used in this topic. The versions may be different in your actual running environment.
  • Operating system: CentOS 7.2
  • PostgreSQL: version 9.5.6
You can install PostgreSQL on an ECS instance in either of the following ways:
  • Image deployment: Go to the Alibaba Cloud Marketplace page, and search for the required PostgreSQL image for installation.
  • Manual deployment: Install PostgreSQL by using source code or Yellowdog Update, Modified (YUM).

Procedure

Step 1: Activate two ECS instances

To build the primary/secondary architecture of PostgreSQL, you must activate two ECS instances that run in a Virtual Private Cloud (VPC). One ECS instance works as a primary node and the other ECS instance works as a secondary node. For more information, see Create an instance by using the wizard.
Note We recommend that you do not assign public IP addresses to the ECS instances. Instead, you can attach an Elastic IP Address (EIP) to each ECS instance. This allows you to upgrade the configurations or optimize the architecture in the follow-up management. For more information, see Create an EIP.

Step 2: Configure the primary node of PostgreSQL

To configure the primary node of PostgreSQL, follow these steps:

  1. On the primary ECS instance, run the following commands in sequence to install PostgreSQL.
    1. yum update -y
    2. yum install https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-7-x86_64/pgdg-centos95-9.5-3.noarch.rpm -y
    3. yum install postgresql95-server postgresql95-contrib -y
    4. /usr/pgsql-9.5/bin/postgresql95-setup initdb
    Note The package pgdg-centos95-9.5-3.noarch.rpm is used in this topic. In your actual running environment, use the latest RPM package.
  2. Run the following commands in sequence to start the PostgreSQL service and enable PostgreSQL to run at startup.
    1. systemctl start postgresql-9.5.service    #Starts the PostgreSQL service.
    2. systemctl enable postgresql-9.5.service   #Enables PostgreSQL to run at startup.
  3. Create a database account named replica that is used for replication between the primary and secondary nodes. Afterward, specify the password, logon permission, and backup permission.
    1. Run the following command to log on to PostgreSQL.
      su - postgres
    2. Type psql in the following command to enter the PostgreSQL interactive terminal.
      -bash-4.2$ psql
    3. Enter the following SQL statement to create the database account named replica, and specify the password, logon permission, and backup permission.
      postgres=# CREATE ROLE replica login replication encrypted password 'replica';
    4. Check whether the database account named replica is created.
      postgres=# SELECT usename from pg_user;
      The following response indicates that the account named replica has been created.
      usename  
      ----------
      postgres
      replica
      (2 rows)
    5. Check whether the permissions are created.
      postgres=# SELECT rolname from pg_roles;
      The following response indicates that the permissions have been created.
      rolname  
      ----------
      postgres
      replica
      (2 rows)
    6. Type \q in the command, and press the Enter key to exit the PostgreSQL interactive terminal.
      postgres=# \q
    7. Type exit in the command, and press the Enter key to exit PostgreSQL.
      -bash-4.2$ exit
      logout
  4. Run the following command to open the file pg_hba.conf, and set a whitelist for replica.
    vim /var/lib/pgsql/9.5/data/pg_hba.conf
    Add the following lines to the IPv4 local connections field.
    host all all 192.168.1.0/24 md5              #Enables MD5 password encryption for connections in the CIDR block of the VPC.
    host replication replica 192.168.1.0/24 md5  #Enables data synchronization from the replication database.
  5. Run the following command to open the postgresql.conf file.
    vim /var/lib/pgsql/9.5/data/postgresql.conf
    Set the following parameters:
    wal_level = hot_standby  #Enables the hot standby mode.
    synchronous_commit = on  #Enables synchronization.
    max_wal_senders = 32     #The maximum number of synchronization processes.
    wal_sender_timeout = 60s #The timeout value for the streaming replication instance to synchronize data.
    max_connections = 100    #The maximum number of connections. The value of max_connections for the secondary node must be larger than that for the primary node.
  6. Run the following command to restart the PostgreSQL service.
    systemctl restart postgresql-9.5.service

Step 3: Configure the secondary node of PostgreSQL

To configure the secondary node of PostgreSQL, follow these steps:

  1. Run the following commands in sequence to install PostgreSQL.
    1. yum update -y
    2. yum install https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-7-x86_64/pgdg-centos95-9.5-2.noarch.rpm -y
    3. yum install postgresql95-server postgresql95-contrib -y
  2. Run the following command and use the pg_basebackup utility to create a backup directory.
    # pg_basebackup -D /var/lib/pgsql/9.5/data -h <Primary node IP> -p 5432 -U replica -X stream -P
      Password: 
      30075/30075 kB (100%), 1/1 tablespace
  3. Run the following commands in sequence to create and open the recovery.conf file.
    1. cp /usr/pgsql-9.5/share/recovery.conf.sample /var/lib/pgsql/9.5/data/recovery.conf
    2. vim /var/lib/pgsql/9.5/data/recovery.conf
    Set the following parameters:
    standby_mode = on     #Declares the secondary node.
    primary_conninfo = 'host=<Primary node IP> port=5432 user=replica password=replica' #Connection information of the primary node.
    recovery_target_timeline = 'latest' #Synchronizes the latest data by using streaming replication.
  4. Run the following command to open the postgresql.conf file.
    vim /var/lib/pgsql/9.5/data/postgresql.conf
    Set the following parameters:
    max_connections = 1000             # The maximum number of connections. The value for the secondary node must be larger than that for the primary node.
    hot_standby = on                   # Enables the hot standby mode.
    max_standby_streaming_delay = 30s  # The maximum delay for streaming replication.
    wal_receiver_status_interval = 1s  # The maximum interval for the secondary node to report the running status to the primary node.
    hot_standby_feedback = on          # Enables the secondary node to report errors during replication.
  5. Run the following command to modify the group and owner of the data directory.
    chown -R postgres.postgres /var/lib/pgsql/9.5/data
  6. Run the following commands in sequence to start the PostgreSQL service and enable PostgreSQL to run at startup.
    1. systemctl start postgresql-9.5.service   #Starts the PostgreSQL service.
    2. systemctl enable postgresql-9.5.service  #Enables PostgreSQL to run at startup.

Step 4: Test the primary/secondary architecture of PostgreSQL

To test the primary/secondary architecture of PostgreSQL, follow these steps:

  1. Run the following command to check the sender process on the primary node.
    ps aux |grep sender
    The following response indicates that the sender process is available.
    postgres  2916  0.0  0.3 340388  3220 ?        Ss   15:38   0:00 postgres: wal sender     process replica 192.168.1.222(49640) streaming 0/F01C1A8
  2. Run the following command to check the receiver process on the secondary node.
    ps aux |grep receiver
    The following response indicates that the receiver process is available.
    postgres 23284  0.0  0.3 387100  3444 ?        Ss   16:04   0:00 postgres: wal receiver process   streaming 0/F01C1A8
  3. On the primary node, run the following SQL statement to check the status of the secondary node.
    replication=# select * from pg_stat_replication;
    The following response indicates that the status of the secondary node is available.
    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.1.222 | | 49640 | 2017-05-02 15:38:06.188988+08 | 1836 | streaming | 0/F01C0C8 | 0/F01C0C8 
    | 0/F01C0C8 | 0/F01C0C8 | 0 | async
    (1 rows)