PostgresqlHA高可用安装配置

1、PostgreSQL HA方案目录PostgreSQL HA方案11.方案架构图32.环境信息43.资源调整(master/standby)53.1.主备节点时钟同步53.2.配置sysctl53.3.limit资源分配53.4.关闭SElinux53.5.创建postgres用户并设置密码63.6.配置SSH免登陆64.postgreSQL流复制环境安装配置64.1.修改权限64.2.安装依赖包64.3.解压安装74.4.设置环境变量74.5.初始化和启动服务(master)74.6.配置流复制用户?74.7.PostgreSQL主配置文件调整(master)84.8.监控SQL功能?84.

2、9.访问控制文件pg_hba.conf的配置84.10.标志文件的配置94.11.生成备库实例(standby)94.12.修改备库标识文件94.13.修改备库主配置文件94.14.启动备库94.15.检查运行情况104.16.测试105.pgpool-II安装配置115.1.安装pgpool(master/standby)115.2.安装 pgpool相关函数(master)115.3.配置环境变量(master/standby)125.4.配置 pcp.conf(master/standby)125.5.配置 ifconfig, arping 执行权限(master/standby)125

3、.6.配置 pgpool.conf(master)125.7.配置pgpool.conf(standby)165.8.failover_stream.sh文件内容(master/standby)195.9.创建运行时目录(master/standby)195.10.配置pool_hba(master)195.11.配置pool_hba(standby)205.12.配置密码文件(master/standby)?205.13.启动pgpool205.14.连接测试211. 架构图1.1. 方案架构图1.2. postgres故障切换图 1.3. pgpool故障切换图1.4. pgpool+po

4、stgres故障切换图2. 安装资源信息操作系统的安装就不在这里介绍，主机名和IP地址如下所示操作系统：RHEL 6.5数据库：PostgreSQL 9.4.4虚拟机两台：主节点node1(IP 31)备节点node2(IP 32)pgpool：pgpool-II-3.4.3VIP：33 (虚拟IP)3. 资源调整(master/standby)3.1. 前期准备创建安装文件夹#mkdir /opt/soft/用于放置源码安装文件#mkdir /opt/pgdata/postgres数据库目录#mkdir /opt/pgsql/

5、postgres安装目录#mkdir /opt/pgpool/pgpool安装目录修改文件夹权限#chown postgres:postgres /opt/soft#chown postgres:postgres /opt/pgsql#chown postgres:postgres /opt/pgdata#chown postgres:postgres /opt/pgpool复制以下安装文件到/opt/soft，并且配置权限pgpool-II-3.4.3.tar.gzpostgresql-9.4.4.tar.gz配置安装文件所属人和所属组为postgres#chown R postgres:p

6、otgres /opt/soft把此目录及子目录的所属人和所属组全换成postgres3.2. 关闭防火墙#chkconfig iptables off#service iptables stop3.1. 关闭SElinux修改SELinux配置文件#vim /etc/sysconfig/selinux把SELINUX=enforcing修改为SELINUX=disabled3.2. 配置hosts文件在hosts文件中增加以下内容#vim /etc/hosts31 pgpool-node0132 pgpool-node023.1. 创建postgr

7、es用户并设置密码安装之前要先检查系统中是否有postgres系统账号#cat /etc/passwd | grep postgres如果没有postgres账号，需要新增postgres账号#groupadd -g 26 postgres# useradd -d /var/lib/pgsql -g postgres -u 26 postgres给postgres配置密码#passwd postgres3.2. 主备节点时钟同步#/usr/sbin/ntpdate && /sbin/hwclock systohcasia.pool.ntp.o

8、rg为网络时间通过ntpdate命令获取网络时间并且将其作为硬件时间3.3. 配置sysctlsysctl.conf(配置系统信息，系统变量，主要用来优化系统)/etc/sysctl.conf这个目录主要是配置一些系统信息,而且它的内容全部是对应于/proc/sys/这个目录的子目录及文件#vi /etc/sysctl.confkernel.shmmni = 4096kernel.sem = 50100 64128000 50100 1280fs.file-max = 7672460net.ipv4.ip_local_port_range = 9000 65000net.core.rmem_d

9、efault = 1048576net.core.rmem_max = 4194304net.core.wmem_default = 262144net.core.wmem_max = 1048576net.ipv4.tcp_tw_recycle = 1net.ipv4.tcp_max_syn_backlog = 4096dev_max_backlog = 10000vm.overcommit_memory = 0net.ipv4.ip_conntrack_max = 655360fs.aio-max-nr = 1048576net.ipv4.tcp_timestamps = 0l 使文件修改

10、生效#sysctl p3.4. limit资源分配#vi /etc/security/limits.conf* soft nofile 131072* hard nofile 131072* soft nproc 131072* hard nproc 131072* soft core unlimited* hard core unlimited* soft memlock 50000000* hard memlock 500000004. postgreSQL流复制环境安装配置4.1. 配置SSH免登陆On master:#su postgres$ssh-keygen -t rsa P &#

11、39; '$ssh-copy-id -i /.ssh/id_rsa.pub postgres32$ssh postgresnode2On standby:#su postgres$ssh-keygen -t rsa -P ' '$ssh-copy-id -i /.ssh/id_rsa.pub postgres31$ssh postgresnode14.2. 安装依赖包使用yum自动安装以下软件#yum -y install readline-devel zlib zlib-devel openssl openssl-devel

12、 pam-devel libxml2-devel libxslt-devel python-devel tcl-devel gcc perl-ExtUtils-Embed或者是手动安装以下软件gcclibxml2-devellibxslt-developenssl-develpam-develpython-develreadline-develtcl-develzlib-develcloog-pplcppkeyutils-libs-develkrb5-devellibcom_err-devellibgcrypt-devellibgpg-error-devellibselinux-develli

13、bsepol-develmpfrncurses-develppltcl4.3. 解压和安装#su - postgres$cd /opt/soft/$tar -zxvf postgresql-9.3.5.tar.gz$cd postgresql-9.3.5$./configure -prefix=/opt/pgsql/ -with-pgport=5432 -with-perl -with-tcl -with-python -with-openssl -with-pam -without-ldap -with-libxml -with-libxslt -with-blocksize=8$gmake

14、$gmake install4.4. 设置环境变量l 编辑环境变量配置文件，增加以下内容$vim /.bash_profileexport PGPORT=5432export PGDATA=/opt/pgdataexport PGHOME=/opt/pgsqlexport PATH=.:$PGHOME/bin:$PATHl 使环境变量配置立即生效$source /.bash_profile4.5. 初始化数据库(master)初始化数据库$initdb -D $PGDATA4.6. 修改配置文件(master)主配置文件postgresql.conf参数调整(master)找到以下参数内容，调

15、整参数值$cd $PGDATA$vim postgresql.conflisten_addresses = '*'port = 5432max_connections = 500wal_level = hot_standbyarchive_mode = onarchive_command = '/bin/date'max_wal_senders = 6hot_standby = on访问控制文件pg_hba.conf配置(master)增加以下访问控制内容$cd $PGDATA$vim pg_hba.confhostallall31/32t

16、rusthostallall32/32trusthostreplicationreplica32/32trusthostreplicationreplica31/32trust标志文件recovery.done配置(master)新增一个recovery.done文件$touch / opt/pgdata/recovery.done$chmod 777 recovery.done在文件中输入以下内容$vim /opt/pgdata/recovery.donestandby_mode = onrecovery_target_tim

17、eline = 'latest'primary_conninfo = 'host=32 port=5432 user=replica password=replica'trigger_file = '/opt/pgdata/trigger_file'4.7. 启动主库服务(master)$pg_ctl -D $PGDATA -l $PGDATA/logfile restart或$pg_ctl D $PGDTA restartPg_ctl D $PGDATA start >> /opt/pgdata/logfile

18、 2>1$ &4.8. 配置流复制用户(master)l 创建流复制用户$psql -U postgrespostgres# CREATE ROLE replica login replication encrypted password 'replica'4.9. 备库实例生成(standby)从主库复制实例到备库#su postgres$ pg_basebackup -D $PGDATA -Fp -Xs -v -P -h node1 -U replica修改备库标识文件$mv /opt/pgdata/recovery.done /opt/pgdata/reco

19、very.conf$vim /opt/pgdata/recovery.confstandby_mode = onrecovery_target_timeline =latestprimary_conninfo = host=31 port=5432 user=replicatrigger_file = /opt/pgdata/trigger_file4.10. 启动备库服务(standby)$chown -R postgres:postgres  /opt/pgdata$chmod 0700 /opt/pgdata$pg_ctl D $PGDATA -l $PG

20、DATA/logfile start4.11. 检查运行情况l 查看备库进程ps ef | grep postgresl 查看主库进程ps ef | grep postgres4.12. 测试On Master: $ psql -U postgrespostgres =# create table test( id int, name varchar(10);postgres=# select table_name from information_schema.tables where table_schema = 'public'table_name - test

21、(1 row)  On Standby： postgres=# select table_name from information_schema.tables where table_schema = 'public' table_name - test(1 row)  新增的数据已经传输过去，并且standby端的会话是只读的。5. pgpool-II安装配置5.1. 安装解压和安装(master/standby)#su - postgres$mkdir /opt/pgpool$tar zxvf pgpool-II-3.4.3.tar.gz$c

22、d pgpool-II-3.4.3$./configure -prefix=/opt/pgpool -with-pgsql=/opt/pgsql -with-openssl$make$make install安装相关函数(master)l 安装pgpool-regclass,pgpool-recovery函数postgresnode1 $cd /opt/soft/pgpool-II-3.4.3/src/sql/pgpool-regclasspostgresnode1 pgpool-regclass$makepostgresnode1 pgpool-regclass$make installpo

23、stgresnode1 pgpool-regclass$psql -f pgpool-regclass.sql template1postgresnode1 pgpool-regclass$cd ./pgpool-recoverypostgresnode1 pgpool-recovery$makepostgresnode1 pgpool-recovery$make installpostgresnode1 pgpool-recovery$psql -f pgpool-recovery.sql template1l 安装完以后/opt/pgsql/share/extension/目录下应该有如下

24、文件pgpool_recovery-1.1.sqlpgpool_recovery.controlpgpool-recovery.sqlpgpool_regclass-1.0.sqlpgpool_regclass.controlpgpool-regclass.sql备注：备节点不需要创建pgpool_regclass,pgpool_recovery)l 查看新增加的函数postgresnode1 $psql -U postgres template1template1=# df5.2. 配置环境变量(master/standby)$vim /etc/profileexport PGPOOL=/o

25、pt/pgpoolexport PATH=.:$PGPOOL/bin:$PATH使环境变量立即生效$source /etc/profile5.3. 配置 ifconfig, arping 执行权限(master/standby)l 配置sudo权限#vim /etc/sudoerspostgres ALL=(ALL) NOPASSWD: /sbin/ifconfigpostgres ALL=(ALL) NOPASSWD: /sbin/ifuppostgres ALL=(ALL) NOPASSWD: /sbin/ifdownpostgres ALL=(ALL) NOPASSWD: /sbin/a

26、rping l 配置s权限#chmod u+s /sbin/ifconfig#chmod u+s /usr/sbin/arping5.4. #开启日志(master/standby)#l 在日志/etc/rsyslog.conf加入以下行#vim /etc/rsyslog.conflocal0.* /var/log/pgpool/pgpool.logl 重启rsyslog服务#/etc/init.d/rsyslog restartl pgpool.conf配置以下行#vim /opt/pgpool/etc/pgpool.conflog_destination=syslog5.1. 主库修改配置

27、文件(master)主配置文件pgpool.conf参数调整postgresnode1 $cd /opt/pgpool/etcpostgresnode1 etc$cp pgpool.conf.sample pgpool.confpostgresnode1 etc$vim pgpool.conf主节点的 pgpool.conf(配置文件中的用户名和配置能用postgres用户就用postgres用户，尽量减少用户)# CONNECTIONS# - pgpool Connection Settings -listen_addresses = '*'port = 9999socket

28、_dir = '/opt/pgpool'# - pgpool Communication Manager Connection Settings pcp_listen_addresses = '*'pcp_port = 9898pcp_socket_dir = '/opt/pgpool'# - Backend Connection Settings -backend_hostname0 = 'node1' #配置数据节点 node1backend_data_directory0 = '/opt/pgdata'bac

29、kend_hostname1 = 'node2' #配置数据节点 node2backend_port1 = 5432backend_weight1 = 1backend_data_directory1 = '/opt/pgdata'backend_flag1 = 'ALLOW_TO_FAILOVER'# - Authentication -enable_pool_hba = onpool_passwd = 'pool_passwd'authentication_timeout = 60# - Where to log -log_d

30、estination = 'syslog'# - What to log -log_connections = onlog_hostname = onlog_statement = onlog_per_node_statement = offlog_standby_delay = 'none'# FILE LOCATIONSpid_file_name = '/opt/pgpool/pgpool.pid'logdir = '/var/log/pgpool'# CONNECTION POOLINGconnection_cache =

31、onreset_query_list = 'ABORT; DISCARD ALL'# LOAD BALANCING MODEload_balance_mode = onignore_leading_white_space = onwhite_function_list = ''black_function_list = 'nextval,setval,nextval,setval'# MASTER/SLAVE MODEmaster_slave_mode = on # 设置流复制模式master_slave_sub_mode = 'stre

32、am' # 设置流复制模式# - Streaming -sr_check_period = 5sr_check_user = 'replica'sr_check_password = 'replica'delay_threshold = 16000# - Special commands -follow_master_command = ''parallel_mode = offpgpool2_hostname = ''system_db_hostname = 'localhost'system_db_po

33、rt = 5432system_db_dbname = 'pgpool'system_db_schema = 'pgpool_catalog'system_db_user = 'pgpool'system_db_password = ''# HEALTH CHECKhealth_check_period = 5health_check_timeout = 20health_check_user = 'replica'health_check_password = 'replica'health_ch

34、eck_max_retries = 3health_check_retry_delay = 1# FAILOVER AND FAILBACKfailover_command = '/opt/pgpool/failover_stream.sh %d %H /opt/pgdata/trigger_file' # WATCHDOGuse_watchdog = on# - Watchdog communication Settings -wd_hostname = 'node1'# - Virtual IP control Setting delegate_IP = &

35、#39;33' 代理的IP地址# - Lifecheck Setting -wd_interval = 6# - heartbeat mode heartbeat_destination0 = 'node2' # 配置对端的 hostnameheartbeat_device0 = 'eth0' # - Other pgpool Connection Settings -other_pgpool_hostname0 = 'node2' # 配置对端的地址other_pgpool_port0 = 9999other_wd

36、_port0 = 9000配置 pcp.confl 生成md5$pg_md5 -u postgres -p password: postgrese8a48653851e28c69d0506508fb27fc5l 配置pcp.conf$cd /opt/pgpool/etc$cp pcp.conf.sample pcp.conf$vim pcp.conf编写 pcp.conf 文件，写入以下内容# USERID:MD5PASSWDpostgres: e8a48653851e28c69d0506508fb27fc5配置pool_hbapostgresnode1 $cd /opt/pgpool/etc

37、postgresnode1 etc$cp pool_hba.conf.sample pool_hba.confpostgresnode1 etc$vim pool_hba.confhost all all 32/24 trusthost all all 31/24 trust配置failover文件内容$touch /opt/pgpool/failover_stream.sh$chmod 775 /opt/pgpool/failover_stream.sh$vim /opt/pgpool/failover_stream.sh#! /bin/sh# F

38、ailover command for streaming replication.# This script assumes that DB node 0 is primary, and 1 is standby.# If standby goes down, do nothing. If primary goes down, create a# trigger file so that standby takes over primary node.# Arguments: $1: failed node id. $2: new master hostname. $3: path to#

39、trigger file. failed_node=$1new_master=$2trigger_file=$3# Do nothing if standby goes down.#if $failed_node = 1 ; then# exit 0;#fi/usr/bin/ssh -T $new_master /bin/touch $trigger_fileexit 0;创建运行时目录(跟据主配置文件配置内容创建)#mkdir /var/log/pgpool/oiddir#chown -R postgres:postgres /var/log/pgpool#su - postgres5.2.

40、 备库修改配置文件(standby)主配置文件pgpool.conf参数调整postgresnode2 $cd /opt/pgpool/etcpostgresnode2 etc$cp pgpool.conf.sample pgpool.confpostgresnode2 etc$vim pgpool.conf# CONNECTIONSlisten_addresses = '*'socket_dir = '/opt/pgpool'# - pgpool Communication Manager Connection Settings pcp_socket_dir

41、= '/opt/pgpool'# - Backend Connection Settings -backend_hostname0 = 'node1'backend_hostname1 = 'node2'backend_port1 = 5432backend_weight1 = 1backend_flag1 = 'ALLOW_TO_FAILOVER'# - Authentication -enable_pool_hba = on# LOGS# - What to log -log_destination = 'syslog

42、'print_timestamp = onlog_connections = onlog_hostname = onlog_statement = onlog_per_node_statement = offlog_standby_delay = 'none'# FILE LOCATIONSpid_file_name = '/opt/pgpool/pgpool.pid'logdir = '/var/log/pgpool'# LOAD BALANCING MODEload_balance_mode = on# MASTER/SLAVE MO

43、DEmaster_slave_mode = onmaster_slave_sub_mode = 'stream'# - Streaming -sr_check_period = 0sr_check_user = 'replica'sr_check_password = 'replica'delay_threshold = 16000# - Special commands -follow_master_command = ''# FAILOVER AND FAILBACKfailover_command = '/opt/p

44、gpool/failover_stream.sh %d %H /opt/pgdata/trigger_file'# - Enabling -use_watchdog = on'# - Watchdog communication Settings -wd_hostname = 'node2 'wd_port = 9000wd_authkey = ''# - Virtual IP control Setting -delegate_IP = '33 ' （虚拟IP，必须同网段的空闲IP，用来故障切换）# - h

45、eartbeat mode -heartbeat_destination0 = 'node1'heartbeat_device0 = 'eth0'# - Other pgpool Connection Settings -other_pgpool_hostname0 = 'node1'other_pgpool_port0 = 9999other_wd_port0 = 9000配置 pcp.confl 生成md5$pg_md5 -u postgres -p password: postgrese8a48653851e28c69d0506508fb2

46、7fc5l 配置pcp.conf$cd /opt/pgpool/etc$cp pcp.conf.sample pcp.conf$vim pcp.conf编写 pcp.conf 文件，写入以下内容# USERID:MD5PASSWDpostgres: e8a48653851e28c69d0506508fb27fc5配置pool_hba(standby)postgresnode1 $cd /opt/pgpool/etcpostgresnode1 etc$cp pool_hba.conf.sample pool_hba.confpostgresnode1 etc$vim pool_hba.confh

47、ost all all 32/32 trusthost all all 31/32 trust配置failover文件内容（standby）$touch /opt/pgpool/failover_stream.sh$chmod 775 /opt/pgpool/failover_stream.sh#! /bin/sh# Failover command for streaming replication.# This script assumes that DB node 0 is primary, and 1 is standby.# If stan

48、dby goes down, do nothing. If primary goes down, create a# trigger file so that standby takes over primary node.# Arguments: $1: failed node id. $2: new master hostname. $3: path to# trigger file. failed_node=$1new_master=$2trigger_file=$3# Do nothing if standby goes down.#if $failed_node = 1 ; then

49、# exit 0;#fi/usr/bin/ssh -T $new_master /bin/touch $trigger_fileexit 0;创建运行时目录(根据主配置文件配置内容创建)#mkdir /var/log/pgpool/oiddir#chown -R postgres:postgres /var/log/pgpool5.3. 启动pgpool(master/standby)建议使用root账号启动pgpool，用普通用户启用虚拟IP无法漂移。rootnode1 #pgpool n &关闭pgpool：rootnode1 #pgpool m fast stop5.4. 连接测

50、试检查pgpool启动进程On master:On slave:通过pgpool访问数据库postgresnode1 $ psql -h localhost -p 9999 -U postgres -d postgres查看pgpool集群中nodes的状态postgres=# show pool_nodes;关于 status 状态，如下§ 0 - 该状态仅仅用于初始化，PCP从不显示它。§ 1 - 节点已启动，还没有连接。§ 2 - 节点已启动，连接被缓冲。§ 3 - 节点已关闭。查看虚拟IPOn Master:6. Failover测试6.1. 关

51、闭主库postgres进程测试使用pg_ctl -D $PGDATA -m fast stop停止主库服务6.2. 关闭主库pgpool进程Node1 关闭数据库pg93db2 etc$ pg_ctl stop -m fastwaiting for server to shut down. doneserver stoppednode2 上测试pg93db1 etc$ pg_controldata | grep clusterDatabase cluster state:               in productionpg93db1 etc$pg93db1 etc$ ll /database/pg93/pg_root/recovery.done -rw-r-r- 1 pg93 pg93 4.8K Oct  2 09:05 /database/pg93/pg_root/recovery.done备注：可以看到  db1 节点已完成从 standby