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

《pgpool+postgresql异步流复制+数据库集群与负载的配置方法》由会员分享，可在线阅读，更多相关《pgpool+postgresql异步流复制+数据库集群与负载的配置方法（33页珍藏版）》请在装配图网上搜索。

1、pgpool+postgres 集群、负载、主备的配置文档 系统环境：虚拟机(centos 6.5 )db1:postgres ( master)db2:postgres ( salve)+pgpool(salve)第一章初始化配置首先配置3台主机3台主机新加用户postgres ；在 3 台主机上新建文件 vi /home/postgre/postgresql.log数据库的启动和pgpool的启动全部都是使用postgres用户; 以postgres用户互相SSH信任； 数据库的安装目录为：/usr/local/pgsql/pgpool 的安装目录为：/usr/local/pgpool/

2、(pgpool安装的时候，./configure-prefix=/usr/local/pgpool要用命令指定安装目录，在以后的配置中比较方便，个人建议)。数据库安装之后，要给postgresql和pgpool添加环境变量具体的添加如下：用root账户编辑/etc/profile文件，在文件的最后面添加以下代码export PATH=/usr/local/pgsql/bi n:$PATH:/usr/local/pgpool/binexport PGDATA=/usr/local/pgsql/dataexport PGHOME=/usr/local/pgsqlexport LANG=zh_CN.

3、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/目录下面新建一个文件夹叫

4、data,使用数据库的初始化的命令：initdb -D /usr/local/pgsql/data- locale =zh_CN UTF8数据库的启动命令： pg_ctl -D /usr/local/pgsql -l /home/postgres/postgresql.logstart给数据库的postgres用户添加密码：在终端中输入psql命令后，进入数据库，然后使用以下命令改密码alter user postgres with password 123456：备主机的数据库的目录下面也需要建data文件夹第二章 数据库的流复制配置2.1在主库中创建流复制用户CREATE USER rep

5、user replicati on LOGIN CONNECTION LIMIT 5 ENCRYPTED PASSWORD 123456;2.2 修改主库 pg_hba.conf 文件（目录在 /usr/local/pgsql/data ）在最后添加如下行。host replication repuser 172.16.144.0 /24trusthost all all172.16.144.0 /24trusthost all postgres172.16.144.0 /24trust2.3修改主库postgresql.conf 文件修改如下几个参数liste n_addresses = *

6、wal_level = hot_sta ndbymax_wal_se nders = 2hot_sta ndby = onmax_wal_senders是Slave库的节点数，有多少个slave库就设多少。walevel 是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/

7、 -l node1dbbackup160619在两台备机的/usr/local/pgsql/data/下面同时有了 recovery.conf 文件$ vi recovery . conf - 新增以下三行sta ndby_mode = ontrigger_file = /usr/local/pgsql/data/pg.triggerprimary_c onninfo= host=172.16.144.145 port=5432 user=repuserpassword=123456 keepalives_idle=60recovery_target_timeli ne = latest2.5

8、启动两台备机的postgres数据库测试：在主数据库上通过：psql进入数据库命令使用默认的数据库 CREATE TABLE rep_test (test varchar(40);插入数据：INSERT INTO rep_test VALUES (data on e);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);在

9、备机上通过psql命令进入数据库： 查询rep_test表，看数据是否插入成功； 也可以在主机的数据库中使用命令查看流复制的连接备机情况了： select pid,state clie nt_addr,s yn c_priority,s yn c_state from pg_stat_replicati on;查看备库落后主库多少字节的 wal日志命令：select pg_xlog_locatio n_diff(pg_curre nt_xlog_locatio n(),replay_locatio n) from pg_stat_replicati on;postgres=# select p

10、id. state dieyfic.piorLt“ syne_state -from pg_stat_re plication;卩id I client_addr I sync_priority I sync_5tatE30Z9 I streaming|0 | a&ync3060 | streaming|0 | a&ync(2 row5pastgres=# select pg_xlog_location_diff(pg_current_xlog_location 0# (change requires restart)# - heartbeat mode - wd_heartbeat_port

11、 = 9694# Port number for receiving heartbeat signal# (change requires restart)wd_heartbeat_keepalive = 2# Interval time of sending heartbeat signal (sec)# (change requires restart) wd_heartbeat_deadtime = 30# Deadtime interval for heartbeat signal (sec)# (change requires restart)heartbeat_destinatio

12、nO = host0_ip1# Host name or IP address of destination 0# for sending heartbeat signal.# (change requires restart)heartbeat_destination_port0 = 9694# Port number of destination 0 for sending# heartbeat signal. Usually this is the# same as wd_heartbeat_port.# (change requires restart)heartbeat_device

13、0 =# Name of NIC device (such like eth0)# used for sending/receiving heartbeat# signal to/from destination 0.# This works only when this is not empty# and pgpool has root privilege.# (change requires restart)#heartbeat_destination1 = host0_ip2#heartbeat_destination_port1 = 9694#heartbeat_device1 =#

14、- query mode -wd_life_point = 3# lifecheck retry times# (change requires restart)wdifecheck_query = SELECT 1# lifecheck query to pgpool from watchdog# (change requires restart)wd_lifecheck_dbname = templates# Database name connected for lifecheck# (change requires restart)wd_lifecheck_user = nobody#

15、 watchdog user monitoring pgpools in lifecheck# (change requires restart)wd_lifecheck_password =# Password for watchdog user in lifecheck# (change requires restart)# - Other pgpool Connection Settings -#other_pgpool_hostnameO = hostO# Host name or IP address to connect to for other pgpool 0# (change

16、 requires restart)#other_pgpool_port0 = 5432# Port number for othet pgpool 0# (change requires restart)#other_wd_port0 = 9000# Port number for othet watchdog 0# (change requires restart)#other_pgpool_hostname1 = hostT#other_pgpool_port1 = 5432#other_wd_port1 = 9000# #OTHERS#relcache_expire = 0# Life

17、 time of relation cache in seconds.# 0 means no cache expiration(the default).# The relation cache is used for cache the# query result against PostgreSQL system# catalog to obtain various information# including table structures or if its a# temporary table or not. The cache is# maintained in a pgpool child local memory# and being kept as long as it survives.# If someone modify the table by using# ALTER TABLE or some such, the relcache