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=60recovery_target_timeline = latest2.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 # automatically locks a dummy row or a table # with insert statements to keep serial data # consistency # without serial, no lock will be issuedlobj_lock_table = # when rewriting lo_creat command in # replication mode, specify table name to # lock# - degenerate handling -replication_stop_on_mismatch = off # on disagreement with the packet kind # sent from backend, degenerate the node # which is most likely minority # if off, just force to exit this sessionfailover_if_affected_tuples_mismatch = off # on disagreement with the number of affected # tuples in update/delete queries, then # degenerate the node which is most likely # minority. # if off, just abort the transaction to # keep the consistency#-# 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 querywhite_function_list = # comma separated list of function names # that dont write to database # regexp are acceptedblack_function_list = currval,lastval,nextval,setval # comma separated list of function names # that write to database # regexp are accepteddatabase_redirect_preference_list = # comma separated list of pairs of database and node id. # example: postgres:primary,mydb0-4:1,mydb5-9:2 # valid for streaming replicaton mode only.app_name_redirect_preference_list = # comma separated list of pairs of app name and node id. # example: psql:primary,myapp0-4:1,myapp5-9:standby # valid fo