PostgresqlHA高可用安裝配置

1、PostgreSQL HA方案目錄PostgreSQL HA方案11.方案架構(gòu)圖32.環(huán)境信息43.資源調(diào)整(master/standby)53.1.主備節(jié)點時鐘同步53.2.配置sysctl53.3.limit資源分配53.4.關(guān)閉SElinux53.5.創(chuàng)建postgres用戶并設(shè)置密碼63.6.配置SSH免登陸64.postgreSQL流復(fù)制環(huán)境安裝配置64.1.修改權(quán)限64.2.安裝依賴包64.3.解壓安裝74.4.設(shè)置環(huán)境變量74.5.初始化和啟動服務(wù)(master)74.6.配置流復(fù)制用戶?74.7.PostgreSQL主配置文件調(diào)整(master)84.8.監(jiān)控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相關(guān)函數(shù)(master)115.3.配置環(huán)境變量(master/standby)125.4.配置 pcp.conf(master/standby)125.5.配置 ifconfig, arping 執(zhí)行權(quán)限(master/standby)125

3、.6.配置 pgpool.conf(master)125.7.配置pgpool.conf(standby)165.8.failover_stream.sh文件內(nèi)容(master/standby)195.9.創(chuàng)建運行時目錄(master/standby)195.10.配置pool_hba(master)195.11.配置pool_hba(standby)205.12.配置密碼文件(master/standby)?205.13.啟動pgpool205.14.連接測試211. 架構(gòu)圖1.1. 方案架構(gòu)圖1.2. postgres故障切換圖 1.3. pgpool故障切換圖1.4. pgpool+po

4、stgres故障切換圖2. 安裝資源信息操作系統(tǒng)的安裝就不在這里介紹，主機名和IP地址如下所示操作系統(tǒng)：RHEL 6.5數(shù)據(jù)庫：PostgreSQL 9.4.4虛擬機兩臺：主節(jié)點node1(IP 31)備節(jié)點node2(IP 32)pgpool：pgpool-II-3.4.3VIP：33 (虛擬IP)3. 資源調(diào)整(master/standby)3.1. 前期準備創(chuàng)建安裝文件夾#mkdir /opt/soft/用于放置源碼安裝文件#mkdir /opt/pgdata/postgres數(shù)據(jù)庫目錄#mkdir /opt/pgsql/

5、postgres安裝目錄#mkdir /opt/pgpool/pgpool安裝目錄修改文件夾權(quán)限#chown postgres:postgres /opt/soft#chown postgres:postgres /opt/pgsql#chown postgres:postgres /opt/pgdata#chown postgres:postgres /opt/pgpool復(fù)制以下安裝文件到/opt/soft，并且配置權(quán)限pgpool-II-3.4.3.tar.gzpostgresql-9.4.4.tar.gz配置安裝文件所屬人和所屬組為postgres#chown R postgres:p

6、otgres /opt/soft把此目錄及子目錄的所屬人和所屬組全換成postgres3.2. 關(guān)閉防火墻#chkconfig iptables off#service iptables stop3.1. 關(guān)閉SElinux修改SELinux配置文件#vim /etc/sysconfig/selinux把SELINUX=enforcing修改為SELINUX=disabled3.2. 配置hosts文件在hosts文件中增加以下內(nèi)容#vim /etc/hosts31 pgpool-node0132 pgpool-node023.1. 創(chuàng)建postgr

7、es用戶并設(shè)置密碼安裝之前要先檢查系統(tǒng)中是否有postgres系統(tǒng)賬號#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. 主備節(jié)點時鐘同步#/usr/sbin/ntpdate && /sbin/hwclock systohcasia.pool.ntp.o

8、rg為網(wǎng)絡(luò)時間通過ntpdate命令獲取網(wǎng)絡(luò)時間并且將其作為硬件時間3.3. 配置sysctlsysctl.conf(配置系統(tǒng)信息，系統(tǒng)變量，主要用來優(yōu)化系統(tǒng))/etc/sysctl.conf這個目錄主要是配置一些系統(tǒng)信息,而且它的內(nèi)容全部是對應(yīng)于/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流復(fù)制環(huán)境安裝配置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. 設(shè)置環(huán)境變量l 編輯環(huán)境變量配置文件，增加以下內(nèi)容$vim /.bash_profileexport PGPORT=5432export PGDATA=/opt/pgdataexport PGHOME=/opt/pgsqlexport PATH=.:$PGHOME/bin:$PATHl 使環(huán)境變量配置立即生效$source /.bash_profile4.5. 初始化數(shù)據(jù)庫(master)初始化數(shù)據(jù)庫$initdb -D $PGDATA4.6. 修改配置文件(master)主配置文件postgresql.conf參數(shù)調(diào)整(master)找到以下參數(shù)內(nèi)容，調(diào)

15、整參數(shù)值$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)增加以下訪問控制內(nèi)容$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在文件中輸入以下內(nèi)容$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. 啟動主庫服務(wù)(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. 配置流復(fù)制用戶(master)l 創(chuàng)建流復(fù)制用戶$psql -U postgrespostgres# CREATE ROLE replica login replication encrypted password 'replica'4.9. 備庫實例生成(standby)從主庫復(fù)制實例到備庫#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. 啟動備庫服務(wù)(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)  新增的數(shù)據(jù)已經(jīng)傳輸過去，并且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安裝相關(guān)函數(shù)(master)l 安裝pgpool-regclass,pgpool-recovery函數(shù)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/目錄下應(yīng)該有如下

24、文件pgpool_recovery-1.1.sqlpgpool_recovery.controlpgpool-recovery.sqlpgpool_regclass-1.0.sqlpgpool_regclass.controlpgpool-regclass.sql備注：備節(jié)點不需要創(chuàng)建pgpool_regclass,pgpool_recovery)l 查看新增加的函數(shù)postgresnode1 $psql -U postgres template1template1=# df5.2. 配置環(huán)境變量(master/standby)$vim /etc/profileexport PGPOOL=/o

25、pt/pgpoolexport PATH=.:$PGPOOL/bin:$PATH使環(huán)境變量立即生效$source /etc/profile5.3. 配置 ifconfig, arping 執(zhí)行權(quán)限(master/standby)l 配置sudo權(quán)限#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權(quán)限#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服務(wù)#/etc/init.d/rsyslog restartl pgpool.conf配置以下行#vim /opt/pgpool/etc/pgpool.conflog_destination=syslog5.1. 主庫修改配置

27、文件(master)主配置文件pgpool.conf參數(shù)調(diào)整postgresnode1 $cd /opt/pgpool/etcpostgresnode1 etc$cp pgpool.conf.sample pgpool.confpostgresnode1 etc$vim pgpool.conf主節(jié)點的 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' #配置數(shù)據(jù)節(jié)點 node1backend_data_directory0 = '/opt/pgdata'bac

29、kend_hostname1 = 'node2' #配置數(shù)據(jù)節(jié)點 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 # 設(shè)置流復(fù)制模式master_slave_sub_mode = 'stre

32、am' # 設(shè)置流復(fù)制模式# - 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 文件，寫入以下內(nèi)容# 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文件內(nèi)容$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;創(chuàng)建運行時目錄(跟據(jù)主配置文件配置內(nèi)容創(chuàng)建)#mkdir /var/log/pgpool/oiddir#chown -R postgres:postgres /var/log/pgpool#su - postgres5.2.

40、 備庫修改配置文件(standby)主配置文件pgpool.conf參數(shù)調(diào)整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，必須同網(wǎng)段的空閑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 文件，寫入以下內(nèi)容# 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文件內(nèi)容（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;創(chuàng)建運行時目錄(根據(jù)主配置文件配置內(nèi)容創(chuàng)建)#mkdir /var/log/pgpool/oiddir#chown -R postgres:postgres /var/log/pgpool5.3. 啟動pgpool(master/standby)建議使用root賬號啟動pgpool，用普通用戶啟用虛擬IP無法漂移。rootnode1 #pgpool n &關(guān)閉pgpool：rootnode1 #pgpool m fast stop5.4. 連接測

50、試檢查pgpool啟動進程On master:On slave:通過pgpool訪問數(shù)據(jù)庫postgresnode1 $ psql -h localhost -p 9999 -U postgres -d postgres查看pgpool集群中nodes的狀態(tài)postgres=# show pool_nodes;關(guān)于 status 狀態(tài)，如下§ 0 - 該狀態(tài)僅僅用于初始化，PCP從不顯示它。§ 1 - 節(jié)點已啟動，還沒有連接。§ 2 - 節(jié)點已啟動，連接被緩沖。§ 3 - 節(jié)點已關(guān)閉。查看虛擬IPOn Master:6. Failover測試6.1. 關(guān)

51、閉主庫postgres進程測試使用pg_ctl -D $PGDATA -m fast stop停止主庫服務(wù)6.2. 關(guān)閉主庫pgpool進程Node1 關(guān)閉數(shù)據(jù)庫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 節(jié)點已完成從 standby