tencent cloud

PrevNext

Feedback

search by keyword

Recent Pages

Documentation
TencentDB for PostgreSQL
    Documentation
    Download PDF

    Implement Read/Write Separation via pgpool

    Last updated: 2024-03-27 17:08:19
    Download PDF

      1. Install pgpool

      Download pgpool and install it, download address.
      $ ./configure
      $ make
      $ make install

      2. Configuration File

      Note:
      Use pgpool to implement the Cloud Load Balancer access. All authentication occurs between the client and pgpool, and the client still needs to continue the PostgreSQL's authentication process.
      Configure the pool_passwd password file
      The pool_passwd password file is required when connecting to the database through pgpool. You can generate the password file using the following command:
      [root@VM-0-15-tencentos ~]# cd /usr/local/bin
      [root@VM-0-15-tencentos bin]# pg_md5 --md5auth --username=dbadmin password
      [root@VM-0-15-tencentos bin]# more /usr/local/etc/pool_passwd 
      dbadmin:md50b0cdb5c1d1f30fe83e5a72061749681
      Configure the pgpool.conf file
      After installing pgpool-II, pgpool.conf.sample is automatically created. We recommend copying or renaming it to pgpool.conf, then you can edit it freely.
      $ cp /usr/local/etc/pgpool.conf.sample /usr/local/etc/pgpool.conf
      pgpool-II only accepts local connections to port 9999 by default. If you want to accept connections from other hosts, please set up.
      listen_addresses = 'localhost'
      port = 9999
      The important pgpool configurations are as follows, please refer to:
      #------------------------------------------------------------------------------
      # BACKEND CLUSTERING MODE
      # Choose one of: 'streaming_replication', 'native_replication',
      # 'logical_replication', 'slony', 'raw' or 'snapshot_isolation'
      # (change requires restart)
      #------------------------------------------------------------------------------
      backend_clustering_mode = 'streaming_replication'
      #------------------------------------------------------------------------------
      # CONNECTIONS
      #------------------------------------------------------------------------------
      # - pgpool Connection Settings -
      listen_addresses = '0.0.0.0'
      # what host name(s) or IP address(es) to listen on;
      # comma-separated list of addresses;
      # defaults to 'localhost'; use '*' for all
      # (change requires restart)
      port = 9989
      # Port number
      # (change requires restart)
      unix_socket_directories = '/tmp'
      # Unix domain socket path(s)
      # The Debian package defaults to
      # /var/run/postgresql
      # (change requires restart)
      #unix_socket_group = ''
      # The Owner group of Unix domain socket(s)
      # (change requires restart)
      reserved_connections = 0
      # Number of reserved connections.
      # Pgpool-II does not accept connections if over
      # num_init_children - reserved_connections.
      # - pgpool Communication Manager Connection Settings -
      pcp_listen_addresses = ''
      # what host name(s) or IP address(es) for pcp process to listen on;
      # comma-separated list of addresses;
      # defaults to 'localhost'; use '*' for all
      # (change requires restart)
      pcp_port = 9898
      # Port number for pcp
      # (change requires restart)
      pcp_socket_dir = '/tmp'
      # Unix domain socket path for pcp
      # The Debian package defaults to
      # /var/run/postgresql
      # (change requires restart)
      listen_backlog_multiplier = 2
      # Set the backlog parameter of listen(2) to
      # num_init_children * listen_backlog_multiplier.
      # (change requires restart)
      serialize_accept = off
      # whether to serialize accept() call to avoid thundering herd problem
      # (change requires restart)
      # - Backend Connection Settings -
      backend_hostname0 = '172.16.0.3'
      # Host name or IP address to connect to for backend 0
      backend_port0 = 5432
      # Port number for backend 0
      backend_weight0 = 1
      # Weight for backend 0 (only in load balancing mode)
      #backend_data_directory0 = '/data'
      # Data directory for backend 0
      backend_flag0 = 'ALWAYS_PRIMARY'
      # Controls various backend behavior
      # ALLOW_TO_FAILOVER, DISALLOW_TO_FAILOVER
      # or ALWAYS_PRIMARY
      backend_application_name0 = 'server0'
      # walsender's application_name, used for "show pool_nodes" command
      backend_hostname1 = '172.16.0.12'
      backend_port1 = 5432
      backend_weight1 = 1
      #backend_data_directory1 = '/data1'
      backend_flag1 = 'DISALLOW_TO_FAILOVER'
      backend_application_name1 = 'server1'
      # - Authentication -
      enable_pool_hba = on
      # Use pool_hba.conf for client authentication
      pool_passwd = 'pool_passwd'
      # File name of pool_passwd for md5 authentication.
      # "" disables pool_passwd.
      # (change requires restart)
      allow_clear_text_frontend_auth = off
      # Allow Pgpool-II to use clear text password authentication
      # with clients, when pool_passwd does not
      # contain the user password
      # - SSL Connections -
      ssl =off
      # Enable SSL support
      # (change requires restart)
      #------------------------------------------------------------------------------
      # POOLS
      #------------------------------------------------------------------------------
      num_init_children = 32
      # Maximum Number of concurrent sessions allowed
      # (change requires restart)
      max_pool = 4
      # Number of connection pool caches per connection
      # (change requires restart)
      # - Life time -
      child_life_time = 5min
      # Pool exits after being idle for this many seconds
      child_max_connections = 0
      # Pool exits after receiving that many connections
      # 0 means no exit
      connection_life_time = 0
      # Connection to backend closes after being idle for this many seconds
      # 0 means no close
      client_idle_limit = 0
      # Client is disconnected after being idle for that many seconds
      # (even inside an explicit transactions!)
      # 0 means no disconnection
      #------------------------------------------------------------------------------
      # FILE LOCATIONS
      #------------------------------------------------------------------------------
      pid_file_name = '/var/run/pgpool/pgpool.pid'
      # PID file name
      # Can be specified as relative to the"
      # location of pgpool.conf file or
      # as an absolute path
      # (change requires restart)
      logdir = '/tmp'
      # Directory of pgPool status file
      # (change requires restart)
      #------------------------------------------------------------------------------
      # CONNECTION POOLING
      #------------------------------------------------------------------------------
      connection_cache = on
      # Activate connection pools
      # (change requires restart)
      # Semicolon separated list of queries
      # to be issued at the end of a session
      # The default is for 8.3 and later
      reset_query_list = 'ABORT; DISCARD ALL'
      # The following one is for 8.2 and before
      #------------------------------------------------------------------------------
      # LOAD BALANCING MODE
      #------------------------------------------------------------------------------
      load_balance_mode = on
      # Activate load balancing mode
      # (change requires restart)
      ignore_leading_white_space = on
      # Ignore leading white spaces of each query
      write_function_list = ''
      # Comma separated list of function names
      # that write to database
      # Regexp are accepted
      # If both read_only_function_list and write_function_list
      # is empty, function's volatile property is checked.
      # If it's volatile, the function is regarded as a
      # writing function.
      allow_sql_comments = off
      # if on, ignore SQL comments when judging if load balance or
      # query cache is possible.
      # If off, SQL comments effectively prevent the judgment
      # (pre 3.4 behavior).
      disable_load_balance_on_write = 'transaction'
      # Load balance behavior when write query is issued
      # in an explicit transaction.
      #
      # Valid values:
      #
      # 'transaction' (default):
      # if a write query is issued, subsequent
      # read queries will not be load balanced
      # until the transaction ends.
      #
      # 'trans_transaction':
      # if a write query is issued, subsequent
      # read queries in an explicit transaction
      # will not be load balanced until the session ends.
      #
      # 'dml_adaptive':
      # Queries on the tables that have already been
      # modified within the current explicit transaction will
      # not be load balanced until the end of the transaction.
      #
      # 'always':
      # if a write query is issued, read queries will
      # not be load balanced until the session ends.
      #
      # Note that any query not in an explicit transaction
      # is not affected by the parameter except 'always'.
      statement_level_load_balance = off
      # Enables statement level load balancing
      #------------------------------------------------------------------------------
      # HEALTH CHECK GLOBAL PARAMETERS
      #------------------------------------------------------------------------------
      health_check_period = 0
      # Health check period
      # Disabled (0) by default
      health_check_timeout = 20
      # Health check timeout
      # 0 means no timeout
      health_check_user = 'nobody'
      # Health check user
      health_check_password = ''
      # Password for health check user
      # Leaving it empty will make Pgpool-II to first look for the
      # Password in pool_passwd file before using the empty password
      health_check_database = ''
      # Database name for health check. If '', tries 'postgres' frist,
      health_check_max_retries = 60
      # Maximum number of times to retry a failed health check before giving up.
      health_check_retry_delay = 1
      # Amount of time to wait (in seconds) between retries.
      connect_timeout = 10000
      # Timeout value in milliseconds before giving up to connect to backend.
      # Default is 10000 ms (10 second). Flaky network user may want to increase
      # the value. 0 means no timeout.
      # Note that this value is not only used for health check,
      # but also for ordinary conection to backend.

      3. Configure the PCP Command

      pgpool-II has an interface for management purposes, used to access database node information, shut down pgpool-II, etc. To use the PCP command, user authentication is required. This kind of authentication is different from PostgreSQL user authentication. It requires defining a username and password in the pcp.conf file. In this file, a username and password appear in pairs on each line, separated by a colon (:). Passwords are in MD5-hashed format.

      4. Configure Database Nodes

      # - Backend Connection Settings -
      backend_hostname0 = '172.16.0.30'
      # Host name or IP address to connect to for backend 0
      backend_port0 = 5432
      # Port number for backend 0
      backend_weight0 = 1
      # Weight for backend 0 (only in load balancing mode)
      #backend_data_directory0 = '/data'
      # Data directory for backend 0
      backend_flag0 = 'ALWAYS_PRIMARY'
      # Controls various backend behavior
      # ALLOW_TO_FAILOVER, DISALLOW_TO_FAILOVER
      # or ALWAYS_PRIMARY
      backend_application_name0 = 'server0'
      # walsender's application_name, used for "show pool_nodes" command
      backend_hostname1 = '172.16.0.16'
      backend_port1 = 5432
      backend_weight1 = 1
      #backend_data_directory1 = '/data1'
      backend_flag1 = 'DISALLOW_TO_FAILOVER'
      backend_application_name1 = 'server1'
      When load_balance_mode is set to true, pgpool-II will distribute SELECT queries among database nodes.
      load_balance_mode = on
      # Activate load balancing mode
      # (change requires restart)
      ignore_leading_white_space = on
      # Ignore leading white spaces of each query
      write_function_list = ''
      # Comma separated list of function names
      # that write to database
      # Regexp are accepted
      # If both read_only_function_list and write_function_list
      # is empty, function's volatile property is checked.
      # If it's volatile, the function is regarded as a
      # writing function.
      allow_sql_comments = off
      # if on, ignore SQL comments when judging if load balance or
      # query cache is possible.
      # If off, SQL comments effectively prevent the judgment
      # (pre 3.4 behavior).
      disable_load_balance_on_write = 'transaction'
      # Load balance behavior when write query is issued
      # in an explicit transaction.
      #
      # Valid values:
      #
      # 'transaction' (default):
      # if a write query is issued, subsequent
      # read queries will not be load balanced
      # until the transaction ends.
      #
      # 'trans_transaction':
      # if a write query is issued, subsequent
      # read queries in an explicit transaction
      # will not be load balanced until the session ends.
      #
      # 'dml_adaptive':
      # Queries on the tables that have already been
      # modified within the current explicit transaction will
      # not be load balanced until the end of the transaction.
      #
      # 'always':
      # if a write query is issued, read queries will
      # not be load balanced until the session ends.
      #
      # Note that any query not in an explicit transaction
      # is not affected by the parameter except 'always'.
      statement_level_load_balance = off
      # Enables statement level load balancing

      5. Start pgpool-II and Verify Read-Write Separation

      $ pgpool -n -d > /tmp/pgpool.log 2>&1 &
      Note:
      Connect and query pg_is_in_recovery(), then disconnect and reconnect to query pg_is_in_recovery() again. If alternating responses of false and true are returned, it indicates that requests are being alternately sent to the master and slave servers, thereby indicating successful read-write separation.
      Use the psql client to connect to pgpool, showing status as normal.
      [root@VM-0-15-tencentos ~]# /usr/local/pgsql/bin/psql -h127.0.0.1 -p9989 -Udbadmin -d postgres
      Password for user dbadmin: 
      psql (15.1)
      Type "help" for help.
      
      postgres=> show pool_nodes;
       node_id |  hostname   | port | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_st
      atus_change  
      ---------+-------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+--------
      -------------
       0       | 172.16.0.30  | 5432 | up     | unknown   | 0.500000  | primary | unknown | 4          | false             | 0                 |                   |                        | 2024-02
      -27 20:04:13
       1       | 172.16.0.16 | 5432 | up     | unknown   | 0.500000  | standby | unknown | 13         | true              | 0                 |                   |                        | 2024-02
      -27 20:04:13
      (2 rows)
      
      postgres=>
      Use read-write SQL on the client side. Due to the prior distinction between read-write and read-only instances, it can be seen that the read-write separation is successful.
      postgres=> insert into pgpool1(id,name)values(3,'b');    
      INSERT 0 1
      postgres=> select * from pgpool1;
       id | name 
      ----+------
        1 | a
        2 | b
        3 | a
        4 | b
        3 | a
      (5 rows)
      
      postgres=>
      Contact Us

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

      Contact Us
      Technical Support

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

      Submit a Ticket
      7x24 Phone Support
      Copyright © 2013-2024 Tencent Cloud. All Rights Reserved.
      Privacy PolicyLegalCookie Policy