tencent cloud

Feedback

Building the Primary/Secondary PostgreSQL Architecture

Last updated: 2023-02-16 11:38:02

    Overview

    PostgreSQL is an open source relational database management system emphasizing scalability and standard compliance. PostgreSQL is ideal for enterprise-level complex online transaction processing (OLTP) systems. It supports NoSQL (JSON/XML/hstore) and Geographic Information System (GIS) data types. Featuring strong reliability and data integrity, PostgreSQL is a suitable for websites, location application systems, complex data object processing and other use cases.

    This document describes how to build a PostgreSQL system on a CVM instance running CentOS 7.

    Software

    This document uses the following software as an example to build PostgreSQL.

    • Linux: Linux operating system. This document uses CentOS 7.6 as an example.
    • PostgreSQL: Relational database management system. This document uses PostgreSQL 12 as an example.

    Prerequisites

    Directions

    Configuring primary node

    1. Log in to the primary CVM instance.

    2. Run the following command to upgrade all packages, system versions and kernels.

      yum update -y
      
    3. Run the following commands in sequence to install PostgreSQL.
      This document uses PostgreSQL 12 as an example. You can choose other versions as needed.

      wget --no-check-certificate https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
      
    rpm -ivh pgdg-redhat-repo-latest.noarch.rpm
    
    yum install postgresql12-server postgresql12-contrib -y
    
    /usr/pgsql-12/bin/postgresql12-setup initdb
    
    1. Run the following command to start the service.

      systemctl start postgresql-12.service
      
    2. Run the following command to enable the service at startup.

      systemctl enable postgresql-12.service 
      
    3. Run the following command to switch to the postgres user.

      su - postgres
      
    4. Run the following command to go to the PostgreSQL interactive terminal.

      psql
      
    5. Run the following command to set the password for the postgres user, and enhance its security.

      ALTER USER postgres WITH PASSWORD 'Custom password';
      
    6. Run the following command to create a database account and set the password, login permission and backup permission.

      create role account name login replication encrypted password 'Custom password';
      

    This document uses creating the database account replica and the password 123456 as an example. Run the following command:

    create role replica login replication encrypted password '123456';
    
    1. Run the following command to check whether the account has been created.
      SELECT usename from pg_user;
      

    If the following result is returned, it indicates that the account has been successfully created.

    usename  
    ----------
    postgres
    replica
    (2 rows)
    
    1. Run the following command to check whether the permission has been created.
      SELECT rolname from pg_roles;
      

    If the following result is returned, it indicates that the account has been successfully created.

    rolname      
    -------------------
    pg_signal_backend
    postgres
    replica
    (3 rows)
    
    1. Enter \q and press Enter to exit the PostgreSQL interactive terminal.

    2. Enter exit and press Enter to exit PostgreSQL.

    3. Run the following command to open the pg_hba.conf configuration file and add the replica user to the allowlist.

      vim /var/lib/pgsql/12/data/pg_hba.conf
      
    4. Press i to switch to the edit mode, and add the following two lines to the IPv4 local connections section:

      host    all             all             <IPv4 IP range of the secondary node's VPC>          md5     #Enable the MD5 password encryption for connections in the IP ranges of the VPC
      host    replication     replica         <IPv4 IP range of the secondary node's VPC>        md5     #Allow data synchronization from the `replication` database.
      

    For example, if the database account is replica and the IPv4 IP range of the secondary node's VPC is xx.xx.xx.xx/16, add the following content to IPv4 local connections:

    host    all             all             xx.xx.xx.xx/16         md5
    host    replication     replica         xx.xx.xx.xx/16         md5
    
    1. Press Esc and enter :wq to save and close the file.

    2. Run the following command to open the postgresql.conf file.

      vim /var/lib/pgsql/12/data/postgresql.conf
      
    3. Press i to enter the edit mode, locate and replace the following parameters:

      listen_addresses = '*'   #The private IP listened on
      max_connections = 100    #The maximum connections. The value of `max_connections` for the secondary node must be greater than that for the primary node
      wal_level = hot_standby  #Enable hot standby mode.
      synchronous_commit = on  #Enable synchronous replication
      max_wal_senders = 32     #The maximum number of synchronization processes
      wal_sender_timeout = 60s ##The timeout value for the streaming replication instance to send data.
      
    4. Press Esc and enter :wq to save the file.

    5. Run the following command to restart the service.

      systemctl restart postgresql-12.service
      

    Configuring secondary node

    1. Log in to the secondary CVM instance.

    2. Run the following command to upgrade all packages, system versions and kernels.

      yum update -y
      
    3. Run the following commands in sequence to install PostgreSQL.

      wget --no-check-certificate https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
      
    rpm -ivh pgdg-redhat-repo-latest.noarch.rpm
    
    yum install postgresql12-server postgresql12-contrib -y
    
    1. Run the following command and use the pg_basebackup utility to create a backup directory:
      pg_basebackup -D /var/lib/pgsql/12/data -h Public IP of the primary node> -p 5432 -U replica -X stream -P
      

    Enter the password as prompted, and press Enter. If the following is returned, it indicates that the backup directory has been successfully created.

    Password: 
    24526/24526 kB (100%), 1/1 tablespace
    
    1. Run the following command to copy the configuration files of the primary node.

      cp /usr/pgsql-12/share/recovery.conf.sample /var/lib/pgsql/121/data/recovery.conf
      
    2. Run the following command to open the recovery.conf file.

      vim /var/lib/pgsql/12/data/recovery.conf
      
    3. Press i to switch to the edit mode, locate and replace the following parameters:

      standby_mode = on     #Declare the secondary node
      primary_conninfo = 'host=<Public IP of the primary node> port=5432 user=Database account password=Database password' #Connection information of the primary node
      recovery_target_timeline = 'latest' #Sync the latest data by using streaming replication
      
    4. Press Esc and enter :wq to save and close the file.

    5. Run the following command to open the postgresql.conf file.

      vim /var/lib/pgsql/12/data/postgresql.conf
      
    6. Press i to switch to the edit mode, locate and replace the following parameters:

      max_connections = 1000             #The maximum connections. The value of `max_connections` for the secondary node must be greater than that for the primary node
      hot_standby = on                   #Enable 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 its status to the primary node
      hot_standby_feedback = on          #Enable the secondary node to report errors during replication.
      
    7. Press Esc and enter :wq to save and close the file.

    8. Run the following command to modify the group and owner of the data directory:

      chown -R postgres.postgres /var/lib/pgsql/12/data
      
    9. Run the following command to start the service.

      systemctl start postgresql-12.service
      
    10. Run the following command to enable the service at startup.

      systemctl enable postgresql-12.service
      

    Verifying deployment

    Perform the following to verify the deployment.

    1. Run the following command to back up the directory from the node.
      pg_basebackup -D /var/lib/pgsql/12/data -h <Public IP of the primary node> -p 5432 -U replica -X stream -P
      

    Enter the database password and press Enter. If the following is returned, it indicates that the backup directory has been successfully created.

    Password: 
    24526/24526 kB (100%), 1/1 tablespace
    
    1. Run the following command to check the sender process on the primary node:
      ps aux |grep sender
      


    3. Run the following command to check the receiver process on the secondary node:

    ps aux |grep receiver
    

    If the following is returned, it indicates that the receiver process is available.

    4. On the primary node, run the following commands sequentially to check the status of the secondary node in the PostgreSQL interactive terminal.

    su - postgres
    
    psql
    
    select * from pg_stat_replication;
    

    If the following is returned, it indicates that the secondary node status is available.

    Contact Us

    Contact our sales team or business advisors to help your business.

    Technical Support

    Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

    7x24 Phone Support