tencent cloud

PrevNext

文档反馈

请输入关键字

Recent Pages

文档
云数据库 PostgreSQL
    文档
    Download PDF

    通过 pgpool 实现读写分离

    最后更新时间：2024-03-20 15:49:10
    下载PDF

      1、安装 pgpool

      下载 pgpool 并进行安装，下载 地址。
      $ ./configure
      $ make
      $ make install

      2、配置文件

      说明：
      使用 pgpool 实现负载均衡访问，所有认证发生在客户端和 pgpool 之间，同时客户端仍然需要继续通过 PostgreSQL 的认证过程。
      配置 pool_passwd 密码文件
      pool_passwd 密码文件是通过 pgpool 连接数据库时需要使用密码文件。可以使用如下命令生成密码文件：
      [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
      配置 pgpool.conf 文件
      当你安装 pgpool-II 后， pgpool.conf.sample 被自动建立。我们建议拷贝或者重命名它为 pgpool.conf ，然后你可以随意编辑它。
      $ cp /usr/local/etc/pgpool.conf.sample /usr/local/etc/pgpool.conf
      pgpool-II 默认只接受到 9999 端口的本地连接。如果你希望从其他主机接受连接，请设置
      listen_addresses = 'localhost'
      port = 9999
      重要的 pgpool 配置如下，请参考：
      #------------------------------------------------------------------------------
      # 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_chidlren - 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、配置 PCP 命令

      pgpool-II 有一个用于管理功能的接口，用于通过网络获取数据库节点信息、关闭 pgpool-II 等。要使用 PCP 命令，必须进行用户认证。这种认证和 PostgreSQL 的用户认证不同。这需要在 pcp.conf 文件中定义一个用户和密码。在这个文件中，一个用户名和密码成对地出现在每一行中，它们用冒号（:）隔开。密码为用 md5 哈希加密的格式。

      4、配置数据库节点

      # - 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'
      当 load_balance_mode 被设置为 true，pgpool-II 将在数据库节点之间分发 SELECT 查询。
      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、启动 pgpool-II 并验证读写分离

      $ pgpool -n -d > /tmp/pgpool.log 2>&1 &
      说明：
      连接并查询 pg_is_in_recovery()，然后断开重连再查询 pg_is_in_recovery()，如果交替返回 false 和 true，说明是交替将请求发送给了主库和从库，即读写分离成功。
      使用客户端 psql 连接 pgpool，展示 status 为正常。
      [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=>
      在客户端使用读写 SQL，由于提前区分了读写实例和只读实例，发现读写分离成功。
      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=>

      

      
      联系我们

      联系我们，为您的业务提供专属服务。

      联系我们
      技术支持

      如果你想寻求进一步的帮助，通过工单与我们进行联络。我们提供7x24的工单服务。

      提交工单
      7x24 电话支持
      Copyright © 2013-2024 Tencent Cloud. All Rights Reserved.
      隐私条款服务条款缓存条款