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

pgpool+postgres 集群、负载、主备的配置文档系统环境：虚拟机（centos 6.5）db1:postgres（master）+pgpool(master)node1:ip:172.16.144.145db2:postgres（salve）+pgpool(salve)node2:ip:172.16.144.146db3:postgres(salve)node3:ip:172.16.144.147第一章 初始化配置首先配置3台主机3台主机新加用户postgres；在3台主机上新建文件 vi /home/postgre/postgresql.log数据库的启动和pgpool的启动全部都是使用postgres用户；以postgres用户互相SSH信任；数据库的安装目录为：/usr/local/pgsql/pgpool的安装目录为：/usr/local/pgpool/ (pgpool安装的时候，./configure prefix=/usr/local/pgpool要用命令指定安装目录，在以后的配置中比较方便，个人建议)。数据库安装之后，要给postgresql和pgpool添加环境变量具体的添加如下：用root账户编辑/etc/profile文件，在文件的最后面添加以下代码export PATH=/usr/local/pgsql/bin:$PATH:/usr/local/pgpool/binexport PGDATA=/usr/local/pgsql/dataexport PGHOME=/usr/local/pgsqlexport LANG=zh_CN.UTF-8export PGPORT=5432保存文件后，需要使用postgres用户 使用source /etc/profile命令使环境变量生效。使用root账户，对/usr/local/pgsql的那个目录使用chown -R postgres:postgres pgsql以node1，配置ntpd服务，确保node1,node2,node3的时间保持一致，在node2和node3中要加上定时任务去同步node1的ntp服务。主数据库的数据库需要初始化，备数据库不用数据库初始化。主数据库的初始化的方法：在node1的/usr/local/pgsql/目录下面新建一个文件夹叫data,使用数据库的初始化的命令：initdb -D /usr/local/pgsql/data -locale=zh_CN.UTF8数据库的启动命令：pg_ctl -D /usr/local/pgsql -l /home/postgres/postgresql.log start给数据库的postgres用户添加密码：在终端中输入psql命令后，进入数据库，然后使用以下命令改密码alter user postgres with password '123456'备主机的数据库的目录下面也需要建data文件夹第二章 数据库的流复制配置2.1在主库中创建流复制用户CREATE USER repuser replication LOGIN CONNECTION LIMIT 5 ENCRYPTED PASSWORD '123456'2.2修改主库pg_hba.conf文件（目录在/usr/local/pgsql/data）在最后添加如下行。host replication repuser 172.16.144.0/24 trusthost all all 172.16.144.0/24 trusthost all postgres 172.16.144.0/24 trust2.3修改主库postgresql.conf文件修改如下几个参数listen_addresses = '*'wal_level = hot_standbymax_wal_senders = 2hot_standby = onmax_wal_senders是Slave库的节点数，有多少个slave库就设多少。wal_level是write ahead log参数值,设置流复制务必将此值更新成hot_standby。使用postgres用户启动主数据库，命令见上一章。2.4在salve 主机上使用命令：在备机上使用命令来跟主库进行同步：pg_basebackup -h 172.16.144.145 -U repuser -F p -P -x -R -D /usr/local/pgsql/data/ -l node1dbbackup160619在两台备主机上的数据库安装目录下面的data文件夹中都有了数据。在两台备机的/usr/local/pgsql/data/下面同时有了recovery.conf文件$ vi recovery.conf  -新增以下三行 standby_mode = 'on' trigger_file = '/usr/local/pgsql/data/pg.trigger' primary_conninfo = 'host=172.16.144.145 port=5432 user=repuser password=123456 keepalives_idle=60'recovery_target_timeline = 'latest'2.5启动两台备机的postgres数据库测试：在主数据库上通过：psql进入数据库命令使用默认的数据库CREATE TABLE rep_test (test varchar(40);插入数据：INSERT INTO rep_test VALUES ('data one');INSERT INTO rep_test VALUES ('some more words');INSERT INTO rep_test VALUES ('lalala');INSERT INTO rep_test VALUES ('hello there');INSERT INTO rep_test VALUES ('blahblah');在备机上通过psql命令进入数据库：查询rep_test表，看数据是否插入成功；也可以在主机的数据库中使用命令查看流复制的连接备机情况了：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.gzcd pgpool-II-3.4.6mkdir -p /opt/pgpool./configure -prefix=/us/local/pgpool -with-pgsql=path -with-pgsql=/usr/local/pgsql/makemake install3.2pgpool相关函数的安装 pgpool 函数不是必需安装，但建议安装 pgpool_regclass, pgpool_recovery 函数。cd /pgpool-II-3.4.6/src/sqlmakemake install安装完成后可以在/opt/PostgreSQL/93/share/postgresql/extension/看到pgpool相关文件。 ls -l /usl/local/pgsqlshare/postgresql/extension/total 36pgpool_recovery-1.0.sqlpgpool_recovery.controlpgpool-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.confcd /usr/local/pgpool/etc/cp pcp.conf.sample pcp.confpgpool 提供 pcp 接口，可以查看、管理 pgpool 的状态，并且可以远程操作 pgpool 。pcp.conf是用来对 pcp 相关命令认证的文件，格式为  USERID:MD5PASSWD。执行pg_md5 -u postgres -p 命令后，让输入密码，然后会出现md5加密后的字符串password:123456XXXXXXXXXXXXXXXXXXXXXXX然后在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 "#" anywhere on a line.# 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 "pgpool reload". 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 authenticationpool_passwd = 'pool_passwd' # File name 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.# - 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#-# - Pool size -num_init_children = 32 # Number of pools # (change requires restart)max_pool = 4 # Number of connections per pool # (change requires restart)# - Life time -child_life_time = 300 # Pool exits after being idle for this many secondschild_max_connections = 0 # Pool exits after receiving that many connections # 0 means no exitconnection_life_time = 0 # Connection to backend closes after being idle for this many seconds # 0 means no closeclient_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 = 'stderr' # Where to log # Valid values are combinations of stderr, # and syslog. Default to stderr.# - What to log -log_line_prefix = '%t: pid %p: ' # printf-style string to output at beginning of each log line.log_connections = off # Log connectionslog_hostname = off # Hostname will be shown in ps status # and in logs if connections are loggedlog_statement = off # Log all statementslog_per_node_statement = off # Log all statements # with node and backend informationslog_standby_delay = 'if_over_threshold' # Log standby delay # Valid values are combinations of always, # if_over_threshold, none# - Syslog specific -syslog_facility = 'LOCAL0' # Syslog local facility. Default to LOCAL0syslog_ident = 'pgpool' # Syslog program identification string # Default to 'pgpool'# - Debug -debug_level = 0 # Debug message verbosity level # 0 means no message, 1 or more mean verbose#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 = '/usr/local/pgpool/pgpool.pid' # PID file name # (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 laterreset_query_list = 'ABORT; DISCARD ALL' # The following one is for 8.2 and before#reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'#-# REPLICATION MODE#-replication_mode = off # Activate replication mode # (change requires restart)replicate_select = off # Replicate SELECT statements # when in replication mode # replicate_select is higher priority than # load_balance_mode.insert_lock = off