Configure read/write splitting with Pgpool
Updated at:
This article explains how to use pgpool on an ECS instance to configure read-write splitting for PostgreSQL. For a simpler managed solution, you can use ApsaraDB RDS for PostgreSQL with its read-only instances.
Background
When not used for database high availability, pgpool functions as a stateless, horizontally scalable middleware with minimal performance overhead. This setup lets you easily implement read/write splitting by pairing pgpool with an ApsaraDB RDS for PostgreSQL instance, which provides its own high-availability architecture.
Deployment environment
If you have already purchased a PostgreSQL 12 instance with high-performance local disks and a read-only instance (for details, see Quickly create an ApsaraDB RDS for PostgreSQL instance and Create a PostgreSQL read-only instance), you only need to install pgpool. Then, skip to Configure pgpool.
Note
Read-only instances for PostgreSQL on cloud disks will be available in a future release.
Test environment:
-
ECS instance: 16-core CPU, 64 GB memory, and a 1.8 TB SSD cloud disk.
-
ECS instance OS: CentOS 7.7 x64.
Follow these steps:
-
Modify the
sysctl.conffile by running the following command:sudo vi /etc/sysctl.conf fs.aio-max-nr = 1048576 fs.file-max = 76724600 # Optional: kernel.core_pattern = /data01/corefiles/core_%e_%u_%t_%s.%p # Create the /data01/corefiles directory with 777 permissions in advance. If it is a symbolic link, change the permissions of the target directory to 777. kernel.sem = 4096 2147483647 2147483646 512000 # Semaphores. Check with `ipcs -l` or `ipcs -u`. Each group of 16 processes requires 17 semaphores. kernel.shmall = 107374182 # The limit on the total size of all shared memory segments (recommended: 80% of memory). Unit: pages. kernel.shmmax = 274877906944 # The maximum size of a single shared memory segment (recommended: 50% of memory). In PostgreSQL versions after 9.2, shared memory usage is significantly reduced. Unit: bytes. kernel.shmmni = 819200 # The total number of shared memory segments that can be created. Each PostgreSQL cluster requires at least two shared memory segments. net.core.netdev_max_backlog = 10000 net.core.rmem_default = 262144 # Default socket receive buffer size in bytes. net.core.rmem_max = 4194304 # The maximum receive socket buffer size in bytes. net.core.wmem_default = 262144 # Default socket send buffer size in bytes. net.core.wmem_max = 4194304 # The maximum send socket buffer size in bytes. net.core.somaxconn = 4096 net.ipv4.tcp_max_syn_backlog = 4096 net.ipv4.tcp_keepalive_intvl = 20 net.ipv4.tcp_keepalive_probes = 3 net.ipv4.tcp_keepalive_time = 60 net.ipv4.tcp_mem = 8388608 12582912 16777216 net.ipv4.tcp_fin_timeout = 5 net.ipv4.tcp_synack_retries = 2 net.ipv4.tcp_syncookies = 1 # Enable SYN cookies. When a SYN queue overflow occurs, cookies are used to handle requests, which can mitigate minor SYN flood attacks. net.ipv4.tcp_timestamps = 1 # Reduce TIME_WAIT sockets. net.ipv4.tcp_tw_recycle = 0 # If set to 1, this enables fast recycling of TIME_WAIT sockets, but it may cause connection failures in NAT environments. Disabling this on the server is recommended. net.ipv4.tcp_tw_reuse = 1 # Enable reuse. This allows TIME_WAIT sockets to be reused for new TCP connections. net.ipv4.tcp_max_tw_buckets = 262144 net.ipv4.tcp_rmem = 8192 87380 16777216 net.ipv4.tcp_wmem = 8192 65536 16777216 net.nf_conntrack_max = 1200000 net.netfilter.nf_conntrack_max = 1200000 vm.dirty_background_bytes = 409600000 # When the amount of dirty data in the system reaches this value, a background process like pdflush writes dirty pages older than (dirty_expire_centisecs/100) seconds to the disk. # The default is 10%. For machines with large memory, we recommend setting a specific byte value. vm.dirty_expire_centisecs = 3000 # Dirty pages older than this value are flushed to the disk. A value of 3000 means 30 seconds. vm.dirty_ratio = 95 # If the system's background flushing is too slow and dirty pages exceed 95% of memory, user processes that perform disk writes (such as fsync or fdatasync calls) must proactively flush dirty pages. # This is highly effective in preventing user processes from flushing dirty data, especially in multi-instance environments where IOPS are limited by cgroups. vm.dirty_writeback_centisecs = 100 # The wake-up interval for the background flushing process (such as pdflush). A value of 100 means 1 second. vm.swappiness = 0 # Do not use the swap partition. vm.mmap_min_addr = 65536 vm.overcommit_memory = 0 # Allows a small amount of memory over-allocation. If set to 1, the system assumes there is always enough memory. A value of 1 can be used in test environments with limited memory. vm.overcommit_ratio = 90 # Used to calculate the allowable memory size when overcommit_memory is set to 2. vm.zone_reclaim_mode = 0 # Disable NUMA, or disable it in vmlinux. net.ipv4.ip_local_port_range = 40000 65535 # The range of TCP and UDP port numbers that are automatically allocated locally. fs.nr_open=20480000 # The maximum number of file handles a single process can open. #vm.extra_free_kbytes = 4096000 # Do not set this to a large value on a machine with limited memory, as it may fail to boot. #vm.min_free_kbytes = 6291456 # For vm.min_free_kbytes, we recommend allocating 1 GB for every 32 GB of memory. # We do not recommend setting the preceding two values on machines with limited memory. # vm.nr_hugepages = 66536 # We recommend using huge pages when shared_buffers is larger than 64 GB. To check page size, run `cat /proc/meminfo | grep Hugepagesize`. #vm.lowmem_reserve_ratio = 1 1 1 # Recommended for systems with more than 64 GB of memory. Otherwise, use the default values of 256 256 32. -
Modify the
limits.confconfiguration file by running the following command:sudo vi /etc/security/limits.conf * soft nofile 1024000 * hard nofile 1024000 * soft nproc unlimited * hard nproc unlimited * soft core unlimited * hard core unlimited * soft memlock unlimited * hard memlock unlimited # Comment out other settings. # Also, comment out the contents of /etc/security/limits.d/20-nproc.conf. -
Disable transparent huge pages, configure huge pages, and enable auto-start for PostgreSQL by running the following commands:
sudo chmod +x /etc/rc.d/rc.local sudo vi /etc/rc.local # Disable transparent huge pages. if test -f /sys/kernel/mm/transparent_hugepage/enabled; then echo never > /sys/kernel/mm/transparent_hugepage/enabled fi # Two instances, each with a 16 GB shared buffer. #sysctl -w vm.nr_hugepages=17000 # Auto-start the two instances. su - postgres -c "pg_ctl start -D /data01/pg12_3389/pg_data" su - postgres -c "pg_ctl start -D /data01/pg12_8002/pg_data" -
Create a file system by running the following commands:
WarningThis step is for new disks only. Before you proceed, confirm that the new disk is mounted (for example, as /dev/vdb and not /dev/vda). Otherwise, you might format the wrong disk and lose all data.
parted -a optimal -s /dev/vdb mklabel gpt mkpart primary 1MiB 100%FREE mkfs.ext4 /dev/vdb1 -m 0 -O extent,uninit_bg -E lazy_itable_init=1 -b 4096 -T largefile -L vdb1 vi /etc/fstab LABEL=vdb1 /data01 ext4 defaults,noatime,nodiratime,nodelalloc,barrier=0,data=writeback 0 0 mkdir /data01 mount -a -
Start the irqbalance service by running the following commands:
sudo systemctl status irqbalance sudo systemctl enable irqbalance sudo systemctl start irqbalance sudo systemctl status irqbalance -
Install PostgreSQL 12 and the pgpool tools by running the following commands:
sudo yum install -y https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm sudo yum search all postgresql sudo yum search all pgpool sudo yum install -y postgresql12* sudo yum install -y pgpool-II-12-extensions -
Initialize the database data directory by running the following commands:
mkdir /data01/pg12_3389 sudo chown postgres:postgres /data01/pg12_3389 -
Configure environment variables for the
postgresuser by running the following commands:su - postgres vi .bash_profile # Append the following content: export PS1="$USER@`/bin/hostname -s`-> " export PGPORT=3389 export PGDATA=/data01/pg12_$PGPORT/pg_data export LANG=en_US.utf8 export PGHOME=/usr/pgsql-12 export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH export DATE=`date +"%Y%m%d%H%M"` export PATH=$PGHOME/bin:$PATH:. export MANPATH=$PGHOME/share/man:$MANPATH export PGHOST=$PGDATA export PGUSER=postgres export PGDATABASE=db1 alias rm='rm -i' alias ll='ls -lh' unalias vi -
Initialize the primary instance by running the following command:
initdb -D $PGDATA -U postgres -E UTF8 --lc-collate=C --lc-ctype=en_US.utf8 -
Modify the
postgresql.conffile with the following example settings:listen_addresses = '0.0.0.0' port = 3389 max_connections = 1500 superuser_reserved_connections = 13 unix_socket_directories = '., /var/run/postgresql, /tmp' tcp_keepalives_idle = 60 tcp_keepalives_interval = 10 tcp_keepalives_count = 10 shared_buffers = 16GB huge_pages = on work_mem = 8MB maintenance_work_mem = 1GB dynamic_shared_memory_type = posix vacuum_cost_delay = 0 bgwriter_delay = 10ms bgwriter_lru_maxpages = 1000 bgwriter_lru_multiplier = 10.0 bgwriter_flush_after = 512kB effective_io_concurrency = 0 max_worker_processes = 128 max_parallel_maintenance_workers = 3 max_parallel_workers_per_gather = 4 parallel_leader_participation = off max_parallel_workers = 8 backend_flush_after = 256 wal_level = replica synchronous_commit = off full_page_writes = on wal_compression = on wal_buffers = 16MB wal_writer_delay = 10ms wal_writer_flush_after = 1MB checkpoint_timeout = 15min max_wal_size = 64GB min_wal_size = 8GB checkpoint_completion_target = 0.2 checkpoint_flush_after = 256kB random_page_cost = 1.1 effective_cache_size = 48GB log_destination = 'csvlog' logging_collector = on log_directory = 'log' log_filename = 'postgresql-%a.log' log_truncate_on_rotation = on log_rotation_age = 1d log_rotation_size = 0 log_min_duration_statement = 1s log_checkpoints = on log_connections = on log_disconnections = on log_line_prefix = '%m [%p] ' log_statement = 'ddl' log_timezone = 'Asia/Shanghai' autovacuum = on log_autovacuum_min_duration = 0 autovacuum_vacuum_scale_factor = 0.1 autovacuum_analyze_scale_factor = 0.05 autovacuum_freeze_max_age = 800000000 autovacuum_multixact_freeze_max_age = 900000000 autovacuum_vacuum_cost_delay = 0 vacuum_freeze_table_age = 750000000 vacuum_multixact_freeze_table_age = 750000000 datestyle = 'iso, mdy' timezone = 'Asia/Shanghai' lc_messages = 'en_US.utf8' lc_monetary = 'en_US.utf8' lc_numeric = 'en_US.utf8' lc_time = 'en_US.utf8' default_text_search_config = 'pg_catalog.english' -
Modify the
pg_hba.conffile with the following example settings:NoteBecause pgpool-II and the database server are on the same ECS instance, login requires a password when the host is set to 127.0.0.1.
# "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128 trust # Allow replication connections from localhost, by a user with the # replication privilege. local replication all trust host replication all 127.0.0.1/32 trust host replication all ::1/128 trust host db123 digoal 0.0.0.0/0 md5 -
Create a streaming replication user. Example:
create role rep123 login replication encrypted password 'xxxxxxx'; CREATE ROLE -
Create an application user. Example:
create role digoal login encrypted password 'xxxxxxx'; CREATE ROLE create database db123 owner digoal; CREATE DATABASE -
Create a health check user for pgpool. This user, which requires only login permissions, is used with specific pgpool parameters to check the WAL replay lag on read-only nodes. Example:
create role nobody login encrypted password 'xxxxxxx';
Create a standby instance
To simplify testing, create the standby instance on the same ECS instance.
-
Create the standby instance online using the
pg_basebackupcommand.pg_basebackup -D /data01/pg12_8002/pg_data -F p --checkpoint=fast -P -h 127.0.0.1 -p 3389 -U rep123 -
Modify the standby instance's
postgresql.conffile.cd /data01/pg12_8002/pg_data vi postgresql.conf # Compared to the primary's configuration, make the following changes: port = 8002 primary_conninfo = 'hostaddr=127.0.0.1 port=3389 user=rep123' # A password is not required because the primary uses trust authentication. hot_standby = on wal_receiver_status_interval = 1s wal_receiver_timeout = 10s recovery_target_timeline = 'latest' -
Create the
standby.signalmarker file.cd /data01/pg12_8002/pg_data touch standby.signal -
Check the replication status on the primary instance.
db1=# select * from pg_stat_replication ; -[ RECORD 1 ]----+------------------------------ pid | 21065 usesysid | 10 usename | rep123 application_name | walreceiver client_addr | 127.0.0.1 client_hostname | client_port | 47064 backend_start | 2020-02-29 00:26:28.485427+08 backend_xmin | state | streaming sent_lsn | 0/52000060 write_lsn | 0/52000060 flush_lsn | 0/52000060 replay_lsn | 0/52000060 write_lag | flush_lag | replay_lag | sync_priority | 0 sync_state | async reply_time | 2020-02-29 01:32:40.635183+08
Configure pgpool
-
Run the following commands to find the pgpool installation files:
rpm -qa|grep pgpool pgpool-II-12-extensions-4.1.1-1.rhel7.x86_64 pgpool-II-12-4.1.1-1.rhel7.x86_64 rpm -ql pgpool-II-12-4.1.1 -
Modify the configuration file pgpool.conf:
cd /etc/pgpool-II-12/ cp pgpool.conf.sample-stream pgpool.conf vi pgpool.conf # ---------------------------- # pgPool-II configuration file # ---------------------------- # # This file consists of lines of the form: # # name = value # # Whitespace may be used. Comments are introduced with "#" anywhere on a line. # The complete list of parameter names and allowed values can be found in the # pgPool-II documentation. # # If you edit this file on a running system, SIGHUP the server or run "pgpool reload" for the changes to take effect. # Some parameters, as marked below, require a server restart. # #------------------------------------------------------------------------------ # Connections #------------------------------------------------------------------------------ # - Pgpool connection settings - listen_addresses = '0.0.0.0' # Hostname or IP address to listen on: # '*' for all, '' for no TCP/IP connections # (change requires restart) port = 8001 # Port number # (change requires restart) socket_dir = '/tmp' # Unix domain socket path # The Debian package defaults to # /var/run/postgresql # (change requires restart) reserved_connections = 0 # Number of reserved connections. # Pgpool-II rejects connections if the number of clients exceeds `num_init_children` - `reserved_connections`. # - Pgpool communication manager connection settings - pcp_listen_addresses = '' # Hostname or IP address for the PCP process to listen on: # '*' for all, '' for no TCP/IP connections # (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 # Sets the backlog parameter of listen(2) to # num_init_children * listen_backlog_multiplier. # (change requires restart) serialize_accept = off # Specifies whether to serialize `accept()` calls to prevent the thundering herd problem. # (change requires restart) # - Backend connection settings - backend_hostname0 = '127.0.0.1' # Hostname or IP address to connect to for backend 0 backend_port0 = 3389 # Port number for backend 0 backend_weight0 = 1 # Weight for backend 0 (only in load balancing mode) backend_data_directory0 = '/data01/pg12_3389/pg_data' # Data directory for backend 0 backend_flag0 = 'ALWAYS_MASTER' # Controls various backend behavior # ALLOW_TO_FAILOVER, DISALLOW_TO_FAILOVER # or ALWAYS_MASTER backend_application_name0 = 'server0' # walsender's application_name, used for "show pool_nodes" command backend_hostname1 = '127.0.0.1' backend_port1 = 8002 backend_weight1 = 1 backend_data_directory1 = '/data01/pg12_8002/pg_data' 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' # Filename of pool_passwd for md5 authentication. # "" disables pool_passwd. # (change requires restart) authentication_timeout = 60 # Delay in seconds to complete client authentication. # 0 means no timeout. allow_clear_text_frontend_auth = off # Allow Pgpool-II to use cleartext password authentication # with clients when pool_passwd does not # contain the user password. # - SSL connections - ssl = off # Enable SSL support # (change requires restart) #ssl_key = './server.key' # Path to the SSL private key file # (change requires restart) #ssl_cert = './server.cert' # Path to the SSL public certificate file # (change requires restart) #ssl_ca_cert = '' # Path to a single PEM format file # containing CA root certificate(s) # (change requires restart) #ssl_ca_cert_dir = '' # Directory containing CA root certificate(s) # (change requires restart) ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # Allowed SSL ciphers # (change requires restart) ssl_prefer_server_ciphers = off # Use the server's SSL cipher preferences # rather than the client's. # (change requires restart) ssl_ecdh_curve = 'prime256v1' # Name of the curve to use in ECDH key exchange ssl_dh_params_file = '' # Name of the file containing Diffie-Hellman parameters used # for the ephemeral DH family of SSL ciphers. #------------------------------------------------------------------------------ # Pools #------------------------------------------------------------------------------ # - Concurrent session and pool size - num_init_children = 128 # Number of concurrent sessions allowed # (change requires restart) max_pool = 4 # Number of connection pool caches per connection # (change requires restart) # - Lifetime - child_life_time = 300 # A child process exits after being idle for this many seconds. child_max_connections = 0 # A child process exits after accepting this many connections. # 0 means no exit. connection_life_time = 0 # Connection to a backend closes after being idle for this many seconds. # 0 means no close. client_idle_limit = 0 # Client is disconnected after being idle for this many seconds # (even inside explicit transactions). # 0 means no disconnection. #------------------------------------------------------------------------------ # Logs #------------------------------------------------------------------------------ # - Where to log - log_destination = 'syslog' # Where to log. # Valid values are combinations of stderr # and syslog. Default is stderr. # - What to log - log_line_prefix = '%t: pid %p: ' # printf-style string to output at the beginning of each log line. log_connections = on # Log connections log_hostname = off # Hostname will be shown in ps status # and in logs if connections are logged. log_statement = off # Log all statements log_per_node_statement = off # Log all statements # with node and backend information. log_client_messages = off # Log any client messages log_standby_delay = 'if_over_threshold' # Log standby delay. # Valid values are always, # if_over_threshold, or none. # - Syslog specific - syslog_facility = 'LOCAL0' # Syslog local facility. Default is LOCAL0. syslog_ident = 'pgpool' # Syslog program identification string. # Default is 'pgpool'. # - Debug - #log_error_verbosity = default # terse, default, or verbose messages #client_min_messages = notice # values in order of decreasing detail: # debug5 # debug4 # debug3 # debug2 # debug1 # log # notice # warning # error #log_min_messages = warning # values in order of decreasing detail: # debug5 # debug4 # debug3 # debug2 # debug1 # info # notice # warning # error # log # fatal # panic #------------------------------------------------------------------------------ # File locations #------------------------------------------------------------------------------ pid_file_name = '/var/run/pgpool-II-12/pgpool.pid' # PID filename. # Can be specified as a path relative to the `pgpool.conf` file or # as an absolute path. # (change requires restart) logdir = '/tmp' # Directory of the pgPool status file # (change requires restart) #------------------------------------------------------------------------------ # Connection pooling #------------------------------------------------------------------------------ connection_cache = on # Enables 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 is for 8.2 and earlier: #reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT' #------------------------------------------------------------------------------ # Replication mode #------------------------------------------------------------------------------ replication_mode = off # Enables replication mode. # (change requires restart) replicate_select = off # Replicate SELECT statements # when in replication mode. # replicate_select has higher priority than # load_balance_mode. insert_lock = off # Automatically locks a dummy row or a table # with INSERT statements to keep SERIAL data # consistency. # Without SERIAL, no lock will be issued. lobj_lock_table = '' # When rewriting the lo_create command in # replication mode, specify the table name to # lock. # - Degenerate handling - replication_stop_on_mismatch = off # On a packet kind mismatch # from the backend, degenerate the node # that is most likely the "minority". # If off, pgpool forces the session to exit. failover_if_affected_tuples_mismatch = off # On disagreement with the number of affected # tuples in UPDATE/DELETE queries, # degenerate the node that is most likely # the "minority". # If off, pgpool aborts the transaction to # maintain consistency. #------------------------------------------------------------------------------ # Load balancing mode #------------------------------------------------------------------------------ load_balance_mode = on # Enables load balancing mode. # (change requires restart) ignore_leading_white_space = on # Ignore leading white spaces in each query. white_function_list = '' # Comma-separated list of function names # that do not write to the database. # Regular expressions are accepted. black_function_list = 'currval,lastval,nextval,setval' # Comma-separated list of function names # that write to the database. # Regular expressions are accepted. black_query_pattern_list = '' # Semicolon-separated list of query patterns # that should be sent to the primary node. # Regular expressions are accepted. # Valid for streaming replication mode only. database_redirect_preference_list = '' # Comma-separated list of database and node ID pairs. # Example: postgres:primary,mydb[0-4]:1,mydb[5-9]:2 # Valid for streaming replication mode only. app_name_redirect_preference_list = '' # Comma-separated list of application name and node ID pairs. # Example: 'psql:primary,myapp[0-4]:1,myapp[5-9]:standby' # Valid for streaming replication mode only. allow_sql_comments = off # If on, ignore SQL comments when determining if load balancing or # query caching is possible. # If off, SQL comments prevent this determination # (pre-3.4 behavior). disable_load_balance_on_write = 'transaction' # Load balancing behavior when a write query is issued # in an explicit transaction. # Any query not in an explicit transaction # is not affected by this parameter. # '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. # 'always': If a write query is issued, read queries will # not be load balanced until the session ends. statement_level_load_balance = off # Enables statement-level load balancing. #------------------------------------------------------------------------------ # Primary/standby mode #------------------------------------------------------------------------------ master_slave_mode = on # Activates primary/standby mode. # (change requires restart) master_slave_sub_mode = 'stream' # Primary/standby sub-mode. # Valid values are stream, slony, # or logical. The default is stream. # (change requires restart) # - Streaming - sr_check_period = 3 # Streaming replication check period. # Disabled (0) by default. sr_check_user = 'nobody' # Streaming replication check user. # This is necessary even if you disable the streaming # replication delay check by setting sr_check_period = 0. sr_check_password = '' # Password for the streaming replication check user. # If empty, Pgpool-II first looks for the # password in the pool_passwd file before using an empty password. sr_check_database = 'postgres' # Database name for the streaming replication check. delay_threshold = 512000 # Threshold in bytes before not dispatching a query to the standby node. # Disabled (0) by default. # - Special commands - follow_master_command = '' # Executes this command after a primary failover. # Special values: # %d = failed node id # %h = failed node hostname # %p = failed node port number # %D = failed node database cluster path # %m = new master node id # %H = new master node hostname # %M = old master node id # %P = old primary node id # %r = new master port number # %R = new master database cluster path # %N = old primary node hostname # %S = old primary node port number # %% = '%' character #------------------------------------------------------------------------------ # Health check global parameters #------------------------------------------------------------------------------ health_check_period = 5 # Health check period. # Disabled (0) by default. health_check_timeout = 10 # Health check timeout. # 0 means no timeout. health_check_user = 'nobody' # Health check user. health_check_password = '' # Password for the health check user. # If empty, Pgpool-II first looks for the # password in the pool_passwd file before using an empty password. health_check_database = '' # Database name for the health check. If empty, 'postgres' is tried first. 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 in seconds to wait between retries. connect_timeout = 10000 # Timeout in milliseconds before giving up on connecting to a backend. # Default is 10000 ms (10 seconds). Users on unstable networks may need to increase this value. # 0 means no timeout. # Note that this value is used for ordinary connections to the backend, # not just for health checks. #------------------------------------------------------------------------------ # Health check per-node parameters (optional) #------------------------------------------------------------------------------ #health_check_period0 = 0 #health_check_timeout0 = 20 #health_check_user0 = 'nobody' #health_check_password0 = '' #health_check_database0 = '' #health_check_max_retries0 = 0 #health_check_retry_delay0 = 1 #connect_timeout0 = 10000 #------------------------------------------------------------------------------ # Failover and failback #------------------------------------------------------------------------------ failover_command = '' # Executes this command on failover. # Special values: # %d = failed node id # %h = failed node hostname # %p = failed node port number # %D = failed node database cluster path # %m = new master node id # %H = new master node hostname # %M = old master node id # %P = old primary node id # %r = new master port number # %R = new master database cluster path # %N = old primary node hostname # %S = old primary node port number # %% = '%' character failback_command = '' # Executes this command on failback. # Special values: # %d = failed node id # %h = failed node hostname # %p = failed node port number # %D = failed node database cluster path # %m = new primary node id # %H = new primary node hostname # %M = old primary node id # %P = old primary node id # %r = new primary port number # %R = new primary database cluster path # %N = old primary node hostname # %S = old primary node port number # %% = '%' character failover_on_backend_error = off # Initiates a failover when reading/writing to the # backend communication socket fails. # If set to off, pgpool reports an # error and disconnects the session. detach_false_primary = off # Detach a false primary if on. Only # valid in streaming replication # mode and with PostgreSQL 9.6 or # later. search_primary_node_timeout = 300 # Timeout in seconds to search for the # primary node when a failover occurs. # 0 means no timeout; keeps searching # for a primary node forever. #------------------------------------------------------------------------------ # Online recovery #------------------------------------------------------------------------------ recovery_user = 'nobody' # Online recovery user. recovery_password = '' # Online recovery password. # If empty, Pgpool-II first looks for the # password in the pool_passwd file before using an empty password. recovery_1st_stage_command = '' # Executes a command in the first stage. recovery_2nd_stage_command = '' # Executes a command in the second stage. recovery_timeout = 90 # Timeout in seconds to wait for the # recovering node's postmaster to start up. # 0 means no wait. client_idle_limit_in_recovery = 0 # Client is disconnected after being idle # for this many seconds in the second stage # of online recovery. # 0 means no disconnection. # -1 means immediate disconnection. auto_failback = off # A detached backend node automatically reattaches if its `replication_state` is 'streaming'. auto_failback_interval = 60 # Minimum interval in seconds for executing auto_failback. #------------------------------------------------------------------------------ # Watchdog #------------------------------------------------------------------------------ # - Enabling - use_watchdog = off # Activates the watchdog. # (change requires restart) # - Connection to upstream servers - trusted_servers = '' # Trusted server list used # to confirm network connection # (hostA,hostB,hostC,...). # (change requires restart) ping_path = '/bin' # Path to the ping command. # (change requires restart) # - Watchdog communication settings - wd_hostname = '' # Hostname or IP address of this watchdog. # (change requires restart) wd_port = 9000 # Port number for the watchdog service. # (change requires restart) wd_priority = 1 # Priority of this watchdog in leader election. # (change requires restart) wd_authkey = '' # Authentication key for watchdog communication. # (change requires restart) wd_ipc_socket_dir = '/tmp' # Unix domain socket path for the watchdog IPC socket. # The Debian package defaults to # /var/run/postgresql. # (change requires restart) # - Virtual IP control settings - delegate_IP = '' # The virtual IP address. # If this is empty, the virtual IP is not configured. # (change requires restart) if_cmd_path = '/sbin' # Path to the directory where if_up/down_cmd exists. # If if_up/down_cmd starts with "/", if_cmd_path will be ignored. # (change requires restart) if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev eth0 label eth0:0' # The command to bring up the virtual IP. # (change requires restart) if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev eth0' # The command to bring down the virtual IP. # (change requires restart) arping_path = '/usr/sbin' # Path to the arping command. # If arping_cmd starts with "/", if_cmd_path will be ignored. # (change requires restart) arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I eth0' # The arping command. # (change requires restart) # - Behavior on escalation settings - clear_memqcache_on_escalation = on # Clear all query cache in shared memory # when a standby pgpool escalates to active pgpool # (= virtual IP holder). # This should be off if clients connect to pgpool # without using the virtual IP. # (change requires restart) wd_escalation_command = '' # Executes this command on escalation on the new active pgpool. # (change requires restart) wd_de_escalation_command = '' # Executes this command when the primary pgpool resigns from being primary. # (change requires restart) # - Watchdog consensus settings for failover - failover_when_quorum_exists = on # Only perform a backend node failover # when the watchdog cluster holds a quorum. # (change requires restart) failover_require_consensus = on # Perform a failover when a majority of Pgpool-II nodes # agrees on the backend node status change. # (change requires restart) allow_multiple_failover_requests_from_node = off # A Pgpool-II node can cast multiple votes # for building consensus on a failover. # (change requires restart) enable_consensus_with_half_votes = off # When enabled, quorum and failover consensus are achieved with exactly half the votes. # Otherwise, they require more than half the votes. # (change requires restart) # - Lifecheck settings - # -- Common -- wd_monitoring_interfaces_list = '' # Comma-separated list of interface names to monitor. # If any interface from the list is active, the watchdog will # consider the network to be fine. # 'any' enables monitoring on all interfaces except loopback. # '' disables monitoring. # (change requires restart) wd_lifecheck_method = 'heartbeat' # Method of watchdog lifecheck ('heartbeat', 'query', or 'external'). # (change requires restart) wd_interval = 10 # Lifecheck interval in seconds (> 0). # (change requires restart) # -- Heartbeat mode -- wd_heartbeat_port = 9694 # Port number for receiving heartbeat signals. # (change requires restart) wd_heartbeat_keepalive = 2 # Interval in seconds for sending heartbeat signals. # (change requires restart) wd_heartbeat_deadtime = 30 # Dead-time interval for heartbeat signals (in seconds). # (change requires restart) heartbeat_destination0 = 'host0_ip1' # Hostname or IP address of destination 0 # for sending heartbeat signals. # (change requires restart) heartbeat_destination_port0 = 9694 # Port number of destination 0 for sending # heartbeat signals. Usually, this is the # same as wd_heartbeat_port. # (change requires restart) heartbeat_device0 = '' # Name of the NIC device (e.g., 'eth0') # used for sending/receiving heartbeat # signals to/from destination 0. # This only works if it is not empty # and pgpool has root privileges. # (change requires restart) #heartbeat_destination1 = 'host0_ip2' #heartbeat_destination_port1 = 9694 #heartbeat_device1 = '' # -- Query mode -- wd_life_point = 3 # Lifecheck retry times. # (change requires restart) wd_lifecheck_query = 'SELECT 1' # Lifecheck query to pgpool from the watchdog. # (change requires restart) wd_lifecheck_dbname = 'template1' # Database name to connect to for lifecheck. # (change requires restart) wd_lifecheck_user = 'nobody' # Watchdog user for monitoring pgpools in lifecheck. # (change requires restart) wd_lifecheck_password = '' # Password for the watchdog user in lifecheck. # If empty, Pgpool-II first looks for the # password in the pool_passwd file before using an empty password. # (change requires restart) # - Other pgpool connection settings - #other_pgpool_hostname0 = 'host0' # Hostname or IP address to connect to for other pgpool 0. # (change requires restart) #other_pgpool_port0 = 5432 # Port number for other pgpool 0. # (change requires restart) #other_wd_port0 = 9000 # Port number for other watchdog 0. # (change requires restart) #other_pgpool_hostname1 = 'host1' #other_pgpool_port1 = 5432 #other_wd_port1 = 9000 #------------------------------------------------------------------------------ # Others #------------------------------------------------------------------------------ relcache_expire = 0 # Lifetime of the relation cache in seconds. # 0 means no cache expiration (the default). # The relation cache is used to cache # query results against the PostgreSQL system # catalog to obtain information, # such as table structures or if a table is temporary. # The cache is maintained in a pgpool child's local memory # and is kept for the life of the process. # If a table is modified (e.g., with ALTER TABLE), the cache may become inconsistent. # For this purpose, relcache_expire # controls the lifetime of the cache. relcache_size = 8192 # Number of relation cache # entries. If you frequently see: # "pool_search_relcache: cache replacement happened" # in the pgpool log, you should increase this value. check_temp_table = catalog # Temporary table check method: catalog, trace, or none. # The default is catalog. check_unlogged_table = on # If on, enables unlogged table check in SELECT statements. # This initiates queries against the system catalog of the primary node, # thus increasing the load on the primary node. # If you are certain that your system never uses unlogged tables # and want to reduce access to the primary node, you can turn this off. # Default is on. enable_shared_relcache = on # If on, the relation cache is stored in memory cache # and shared among child processes. # Default is on. # (change requires restart) relcache_query_target = master # Target node for relcache queries. The default is the primary node. # If a load-balanced node is specified, queries are sent to a load-balanced node. #------------------------------------------------------------------------------ # In-memory query cache #------------------------------------------------------------------------------ memory_cache_enabled = off # If on, enables the memory cache functionality. Off by default. # (change requires restart) memqcache_method = 'shmem' # Cache storage method: 'shmem' (shared memory) or # 'memcached'. 'shmem' is the default. # (change requires restart) memqcache_memcached_host = 'localhost' # Memcached hostname or IP address. Mandatory if # memqcache_method = 'memcached'. # Defaults to localhost. # (change requires restart) memqcache_memcached_port = 11211 # Memcached port number. Mandatory if memqcache_method = 'memcached'. # Defaults to 11211. # (change requires restart) memqcache_total_size = 67108864 # Total memory size in bytes for storing the memory cache. # Mandatory if memqcache_method = 'shmem'. # Defaults to 64 MB. # (change requires restart) memqcache_max_num_cache = 1000000 # Total number of cache entries. Mandatory # if memqcache_method = 'shmem'. # Each cache entry consumes 48 bytes of shared memory. # Defaults to 1,000,000 (45.8 MB). # (change requires restart) memqcache_expire = 0 # The lifetime in seconds for a query cache entry. # A value of 0 means an infinite lifetime (default). # (change requires restart) memqcache_auto_cache_invalidation = on # If on, invalidation of the query cache is triggered by a corresponding # DDL/DML/DCL (and memqcache_expire). If off, it is only triggered # by memqcache_expire. On by default. # (change requires restart) memqcache_maxcache = 409600 # Maximum SELECT result size in bytes. # Must be smaller than memqcache_cache_block_size. Defaults to 400 KB. # (change requires restart) memqcache_cache_block_size = 1048576 # Cache block size in bytes. Mandatory if memqcache_method = 'shmem'. # Defaults to 1 MB. # (change requires restart) memqcache_oiddir = '/var/log/pgpool/oiddir' # Temporary working directory to record table OIDs. # (change requires restart) white_memqcache_table_list = '' # A comma-separated list of tables whose SELECT queries are candidates for caching. # Regular expressions are accepted. black_memqcache_table_list = '' # A comma-separated list of tables whose SELECT queries should not be cached. # Regular expressions are accepted.Key configuration changes include:
Análisis: la entrada es un bloque de código (<code>), no un documento HTML con encabezados o tablas. Por lo tanto, las reglas de procesamiento de HTML no se aplican directamente. La instrucción principal es "optimizar el borrador en inglés basándose en el original en chino". Mi rol es ser un experto en localización de textos extremadamente conciso. Mi estrategia es la siguiente: 1. Utilizar el "borrador en inglés" como base. 2. Comparar los comentarios (#) con los del "original en chino". 3. Adoptar los comentarios de la versión "original" cuando sean más concisos y directos, siempre que mantengan la claridad y la corrección gramatical. 4. Mantener los comentarios del "borrador" cuando sean más claros, gramaticalmente superiores o cuando la versión "original" contenga errores. 5. Conservar intactos todo el código, los valores de los parámetros y la estructura del archivo. Este enfoque se alinea con la intención del usuario de optimizar el texto y con mi perfil de experto en concisión, aplicando el juicio profesional en lugar de un reemplazo ciego. htmllisten_addresses = '0.0.0.0' port = 8001 socket_dir = '/tmp' reserved_connections = 0 pcp_listen_addresses = '' pcp_port = 9898 pcp_socket_dir = '/tmp' # - Backend Connection Settings - backend_hostname0 = '127.0.0.1' # Host name or IP address to connect to for backend 0 backend_port0 = 3389 # Port number for backend 0 backend_weight0 = 1 # Weight for backend 0 (only in load balancing mode) backend_data_directory0 = '/data01/pg12_3389/pg_data' # Data directory for backend 0 backend_flag0 = 'ALWAYS_MASTER' # Controls various backend behavior # ALLOW_TO_FAILOVER, DISALLOW_TO_FAILOVER # or ALWAYS_MASTER backend_application_name0 = 'server0' # walsender's application_name, used for "show pool_nodes" command backend_hostname1 = '127.0.0.1' backend_port1 = 8002 backend_weight1 = 1 backend_data_directory1 = '/data01/pg12_8002/pg_data' 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 # - Concurrent session and pool size - num_init_children = 128 # 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 = 300 # 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 #------------------------------------------------------------------------------ # LOGS #------------------------------------------------------------------------------ # - Where to log - log_destination = 'syslog' # Where to log # Valid values are combinations of stderr, # and syslog. Default to stderr. log_connections = on # Log connections log_standby_delay = 'if_over_threshold' # Log standby delay # Valid values are combinations of always, # if_over_threshold, none #------------------------------------------------------------------------------ # FILE LOCATIONS #------------------------------------------------------------------------------ pid_file_name = '/var/run/pgpool-II-12/pgpool.pid' # PID file name # Can be specified as a path relative to the # 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' #------------------------------------------------------------------------------ # 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 white_function_list = '' # Comma-separated list of function names # that don't write to database # Regexp are accepted black_function_list = 'currval,lastval,nextval,setval' # Comma-separated list of function names # that write to the database # Regexp are accepted black_query_pattern_list = '' # Semicolon-separated list of query patterns # that should be sent to primary node # Regexp are accepted # valid for streaming replication mode only. database_redirect_preference_list = '' # comma-separated list of pairs of database and node id. # example: postgres:primary,mydb[0-4]:1,mydb[5-9]:2' # valid for streaming replication mode only. app_name_redirect_preference_list = '' # comma-separated list of pairs of app name and node id. # example: 'psql:primary,myapp[0-4]:1,myapp[5-9]:standby' # valid for streaming replication mode only. 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. # Note that any query not in an explicit transaction # is not affected by the parameter. # 'transaction' (the 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. # 'always': if a write query is issued, read queries will # not be load balanced until the session ends. statement_level_load_balance = off # Enables statement level load balancing #------------------------------------------------------------------------------ # MASTER/SLAVE MODE #------------------------------------------------------------------------------ master_slave_mode = on # Activate master/slave mode # (change requires restart) master_slave_sub_mode = 'stream' # Master/slave sub mode # Valid values are stream, slony, # or logical. Default is stream. # (change requires restart) # - Streaming - sr_check_period = 3 # Streaming replication check period # Disabled (0) by default sr_check_user = 'nobody' # Streaming replication check user # This is necessary even if you disable streaming # replication delay check by sr_check_period = 0 sr_check_password = '' # Password for the streaming replication check user # If empty, Pgpool-II first looks for the # password in the pool_passwd file before using an empty password sr_check_database = 'postgres' # Database name for the streaming replication check delay_threshold = 512000 # Threshold before not dispatching query to standby node # Unit is in bytes # Disabled (0) by default #------------------------------------------------------------------------------ # HEALTH CHECK GLOBAL PARAMETERS #------------------------------------------------------------------------------ health_check_period = 5 # Health check period # Disabled (0) by default health_check_timeout = 10 # Health check timeout # 0 means no timeout health_check_user = 'nobody' # Health check user health_check_password = '' # Password for the health check user # If empty, Pgpool-II first looks for the # password in the pool_passwd file before using an empty password health_check_database = '' # Database name for the health check. If '', tries 'postgres' first. health_check_max_retries = 60 # Maximum number of retries for a failed health check. health_check_retry_delay = 1 # The delay in seconds between health check retries. connect_timeout = 10000 # Timeout value in milliseconds before giving up on connecting to a backend. # Default is 10000 ms (10 seconds). Users on unreliable networks may want to increase # this value. 0 means no timeout. # Note that this value is not only used for health checks, # but also for ordinary connections to a backend. #------------------------------------------------------------------------------ # FAILOVER AND FAILBACK #------------------------------------------------------------------------------ failover_on_backend_error = off # Initiates failover when reading/writing to the # backend communication socket fails. # If set to off, pgpool will report an # error and disconnect the session. relcache_expire = 0 # After a schema change, it is recommended to set this to 1, reload, and then revert the change. Alternatively, set a specific cache lifetime. # Lifetime of the relation cache in seconds. # 0 means no cache expiration (the default). # The relation cache is used to cache the # query result against the PostgreSQL system # catalog to obtain various information, # including table structures or whether a table # is temporary. The cache is # maintained in a pgpool child's local memory # and is kept for the lifetime of the child process. # If a user modifies the table by using # ALTER TABLE or a similar command, the cache can # become inconsistent. # This parameter (relcache_expire) # controls the cache lifetime. relcache_size = 8192 # Number of relation cache entries. # If the message "pool_search_relcache: cache replacement happened" # appears frequently in the pgpool log, you may want to increase this number. -
Configure the
pool_passwdpassword file. Run the following commands:NoteThe password file is required for database connections established through pgpool. It enables pgpool to support the PostgreSQL authentication protocol.
cd /etc/pgpool-II-12 # Usage # pg_md5 --md5auth --username=username password # Generate passwords for the digoal and nobody users and write them to the pool_passwd file. pg_md5 --md5auth --username=digoal "xxxxxxx" pg_md5 --md5auth --username=nobody "xxxxxxx" -
View the auto-generated
pool_passwdfile:cd /etc/pgpool-II-12 cat pool_passwd digoal:md54dd55116da69d3d03bf2e3a1470564f9 nobody:md54240e76623e2511d607f431043a5d1c1 -
Configure the pgpool_hba file.
cd /etc/pgpool-II-12 cp pool_hba.conf.sample pool_hba.conf vi pool_hba.conf host all all 0.0.0.0/0 md5 -
To configure the pcp management password file, run the following commands:
NoteThis user and password are for pgpool management, not for database access.
cd /etc/pgpool-II-12 pg_md5 abc # For example, the password is 'abc'. 900150983cd24fb0d6963f7d28e17f72 cp pcp.conf.sample pcp.conf vi pcp.conf USERID:MD5PASSWD manage:900150983cd24fb0d6963f7d28e17f72 # Sets 'manage' as the pcp management user. -
To start pgpool:
cd /etc/pgpool-II-12 pgpool -f ./pgpool.conf -a ./pool_hba.conf -F ./pcp.confNoteTo view the pgpool log, run the following command:
less /var/log/messages -
Connect to a database through pgpool:
psql -h 127.0.0.1 -p 8001 -U digoal postgres[root@iZbxxxZ pgpool-II-12]# psql -h pgm-bpxxx.pg.rds.aliyuncs.com -p 3433 -U digoal postgres Password for user digoal: psql (12.2, server 10.10) Type "help" for help. postgres=>
FAQ
-
How do I test if read-write separation is working?
Connect to the database and run the pg_is_in_recovery() function. Then, disconnect, reconnect, and run the function again. If the return values alternate between false and true, it means requests are being routed alternately to the primary and standby instances, confirming that read-write separation is working.
-
Does pgpool add latency?
Yes, it introduces a small amount of latency. In the test environment for this guide, the added latency is approximately 0.12 ms.
-
How do the replay lag check and health check mechanisms in pgpool work?
-
pgpool does not route SQL requests to a read-only instance if its replay lag exceeds the configured threshold. Routing to the instance resumes after its lag falls below the threshold.
NoteCheck the lag by connecting to the primary instance to query the current WAL write location (LSN 1) and then connecting to the read-only instance to query the WAL replay location (LSN 2). The difference in bytes between LSN 1 and LSN 2 is the replay lag.
-
pgpool performs health checks on backend nodes. If a node is found to be unhealthy, pgpool stops routing SQL requests to it.
-
-
How do I stop pgpool or reload its configuration?
Use the
pgpool --helpcommand to see all available options. For example, to stop pgpool:cd /etc/pgpool-II-12 pgpool -f ./pgpool.conf -m fast stop -
How do I configure pgpool if I have multiple read-only instances?
Modify the pgpool.conf file by adding a configuration block for each additional read-only instance. For example:
backend_hostname1 = 'xx.xx.xxx.xx' backend_port1 = 8002 backend_weight1 = 1 backend_data_directory1 = '/data01/pg12_8002/pg_data' backend_flag1 = 'DISALLOW_TO_FAILOVER' backend_application_name1 = 'server1' backend_hostname2 = 'xx.xx.xx.xx' backend_port2 = 8002 backend_weight2 = 1 backend_data_directory2 = '/data01/pg12_8002/pg_data' backend_flag2 = 'DISALLOW_TO_FAILOVER' backend_application_name2 = 'server2' -
How do I use pcp to query the backend status?
Example command:
# pcp_node_info -U manage -h /tmp -p 9898 -n 1 -v Password: Enter your password Hostname : 127.0.0.1 Port : 8002 Status : 2 Weight : 0.500000 Status Name : up Role : standby Replication Delay : 0 Replication State : Replication Sync State : Last Status Change : 2020-02-29 00:20:29 -
Which ports are used in this configuration?
This configuration uses the following ports:
-
Primary instance: 3389
-
Standby instance: 8002
-
pgpool: 8001
-
pcp management: 9898
-
Is this page helpful?