产品动态
[root@VM-10-6-tencentos ~]# tar -zxvf pgpool-II-4.4.5.tar.gz[root@VM-10-6-tencentos ~]# cd pgpool-II-4.4.5[root@VM-10-6-tencentos pgpool-II-4.4.5]# ./configure[root@VM-10-6-tencentos pgpool-II-4.4.5]# make[root@VM-10-6-tencentos pgpool-II-4.4.5]# make install
[root@VM-10-6-tencentos pgpool-II-4.4.5]# pgpool --versionpgpool-II version 4.4.5 (nurikoboshi)
[root@VM-0-15-tencentos pgpool-II-4.4.5]#cp /usr/local/etc/pgpool.conf.sample /usr/local/etc/pgpool.conf[root@VM-10-6-tencentos pgpool-II-4.4.5]# vi /usr/local/etc/pgpool.conf
#listen_addresses = '0.0.0.0'#backend_hostname0 = '云数据库实例 IP 地址'#backend_port0 = 5432#enable_pool_hba = on
#------------------------------------------------------------------------------# 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 = 'localhost'# 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 = '主节点数据库 ip 地址'# 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 = '备节点数据库 ip 地址'#backend_port1 = 5433#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#------------------------------------------------------------------------------# - Concurrent session and pool size -#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.#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 = 0# 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.
[root@VM-10-6-tencentos pgpool-II-4.4.5]# cd /usr/local/bin[root@VM-10-6-tencentos bin]# pg_md5 --md5auth --username=dbadmin password[root@VM-10-6-tencentos bin]# more /usr/local/etc/pool_passwddbadmin:md50b0cdb5c1d1f30fe83e5a*******
[root@VM-10-6-tencentos bin]# cd /usr/local/etc[root@VM-10-6-tencentos etc]# cp /usr/local/etc/pcp.conf.sample /usr/local/etc/pcp.conf
[root@VM-10-6-tencentos etc]# pg_md5 --md5auth --username=pcpuser password2[root@VM-10-6-tencentos etc]# more /usr/local/etc/pool_passwddbadmin:md50b0cdb5c1d1f30fe83e5a*******pcpuser:md5907cf835939adc1c736e2*******
[root@VM-10-6-tencentos etc]# vi /usr/local/etc/pcp.conf
节点 | 作用 |
主节点 | 负责写操作和读操作（如果开启读写分离） |
备节点 | 只处理读操作 |
[root@VM-10-6-tencentos etc]# vi /usr/local/etc/pgpool.confg
# - Backend Connection Settings -#backend_hostname0 = '主节点 ip 地址'# 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 = '备节点 ip 地址'#backend_port1 = 5433#backend_weight1 = 1#backend_data_directory1 = '/data1'#backend_flag1 = 'DISALLOW_TO_FAILOVER'#backend_application_name1 = 'server1'
[root@VM-10-6-tencentos etc]# sudo useradd -r -s /sbin/nologin pgpool[root@VM-10-6-tencentos etc]# vi /etc/systemd/system/pgpool.service
[Unit]Description=Pgpool-II connection pool serverAfter=network.target[Service]Type=simpleUser=pgpoolGroup=pgpoolExecStart=/usr/local/bin/pgpool -n -f /usr/local/etc/pgpool.conf #此处需填写pgpool.conf文件路径，可根据实际情况调整Restart=on-failure[Install]WantedBy=multi-user.target
[root@VM-10-6-tencentos etc]# sudo mkdir -p /var/run/pgpool[root@VM-10-6-tencentos etc]# sudo systemctl daemon-reload[root@VM-10-6-tencentos etc]# sudo systemctl enable pgpool[root@VM-10-6-tencentos etc]# sudo systemctl start pgpool
[root@VM-0-15-tencentos ~]# /usr/local/pgsql/bin/psql -h127.0.0.1 -p9989 -U dbadmin -d postgresPassword 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_status_change---------+-------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------0 | *.*.*.* | 5432 | up | unknown | 0.500000 | primary | unknown | 4 | false | 0 | | | 2024-02-27 20:04:131 | *.*.*.* | 5432 | up | unknown | 0.500000 | standby | unknown | 13 | true | 0 | | | 2024-02-27 20:04:13(2 rows)postgres=>
文档反馈