pgpool+postgresql异步流复制+数据库集群及负载的配置方法

select pid,state client_addr,sync_priority,sync_state from pg_stat_replication;

查看备库落后主库多少字节的wal日志命令：select

pg_xlog_location_diff(pg_current_xlog_location(),replay_location)

from pg_stat_replication;

pgpool的配置 3.1pgpool的安装

tar zxvf pgpool-II-3.4.6.tar.gz cd pgpool-II-3.4.6 mkdir -p /opt/pgpool

./configure --prefix=/us/local/pgpool -with-pgsql=path -with-pgsql=/usr/local/pgsql/ make make install

3.2pgpool相关函数的安装

pgpool 函数不是必需安装，但建议安装 pgpool_regclass, pgpool_recovery 函数。

cd /pgpool-II-3.4.6/src/sql make make install

安装完成后可以在/opt/PostgreSQL/93/share/postgresql/extension/看到pgpool相关文件。

ls -l /usl/local/pgsqlshare/postgresql/extension/ total 36

pgpool_recovery--1.0.sql pgpool_recovery.control pgpool-recovery.sql

pgpool_regclass--1.0.sql pgpool_regclass.control pgpool-regclass.sql plpgsql--1.0.sql plpgsql.control

plpgsql--unpackaged--1.0.sql

登陆需要安装的库中，和安装插件一样执行以下两条命令(一定要执行)。 create extension pgpool_regclass; create extension pgpool_recovery;

psql -U postgres -h 172.16.144.145 -p 5432 -f /usr/local/pgsql/share/extension/pgpool-recovery.sql template1

执行以上命令时，会在主数据库上的template1表空间下面创建几个recovery需要的函数。 3.3配置pcp.conf cd /usr/local/pgpool/etc/

cp pcp.conf.sample pcp.conf

pgpool 提供 pcp 接口〃可以查看、管理 pgpool 的状态〃并且可以远程操作 pgpool 。pcp.conf是用来对 pcp 相关命令认证的文件〃格式为 USERID:MD5PASSWD。

执行pg_md5 -u postgres -p 命令后，让输入密码，然后会出现md5加密后

的字符串

password:123456

XXXXXXXXXXXXXXXXXXXXXXX

然后在pcp.conf中添加postgres:XXXXXXXXXXXXXX 配置pgpool.conf

# ---------------------------- # pgPool-II configuration file # ---------------------------- #

# This file consists of lines of the form: #

# name = value #

# Whitespace may be used. Comments are introduced with \# The complete list of parameter names and allowed values can be found in the # pgPool-II documentation. #

# This file is read on server startup and when the server receives a SIGHUP # signal. If you edit the file on a running system, you have to SIGHUP the # server for the changes to take effect, or use \ Some # parameters, which are marked below, require a server shutdown and restart to # take effect. #

#------------------------------------------------------------------------------ # CONNECTIONS

#------------------------------------------------------------------------------

# - pgpool Connection Settings -

listen_addresses = '*'

# Host name or IP address to listen on: # '*' for all, '' for no TCP/IP connections # (change requires restart) port = 9999

# Port number

# (change requires restart) socket_dir = '/tmp'

# Unix domain socket path # The Debian package defaults to # /var/run/postgresql # (change requires restart)

# - pgpool Communication Manager Connection Settings -

pcp_listen_addresses = '*'

# Host name or IP address for 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

# Set the backlog parameter of listen(2) to

# num_init_children * listen_backlog_multiplier.

# (change requires restart)

# - Backend Connection Settings -

# Host name or IP address to connect to for backend 0 # Port number for backend 0

# Weight for backend 0 (only in load balancing mode) # Data directory for backend 0 # Controls various backend behavior

# ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVER

# - Authentication -

enable_pool_hba = on

# Use pool_hba.conf for client authentication pool_passwd = 'pool_passwd'

# File name of pool_passwd for md5 authentication. # \ # (change requires restart) authentication_timeout = 60

# Delay in seconds to complete client authentication # 0 means no timeout.

# - 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)

#------------------------------------------------------------------------------ # POOLS

#------------------------------------------------------------------------------