Postgresql--HA高可用安装配置

1、PostgreSQLHA&案目录PostgreSQLHA 方案 11.方案架构图 32.环境信息 63.资源调整(master/standby)73.1.主备节点时钟同步 73.2.配置 sysctl93.3.limit 资源分配 103.4.关闭 SElinux83.5.创建 postgres 用户并设置密码 83.6.配置 SSH 免登陆 104.postgreSQL 流复制环境安装配置 104.1.修改权限 104.2.安装依赖包 104.3.解压安装 114.4.设置环境变量 114.5.初始化和启动服务(master)124.6.配置流复制用户?124.7.PostgreSQ

2、L 主配置文件调整(master)错误!未定义书签。#标志文件的配置 13#生成备库实例(standby)13#修改备库标识文件 13#修改备库主配置文件错误!未定义书签。#启动备库 14#检查运彳 f 情况 14#测试 142.%2.pgpool-II 安装配置 15#安装 pgpool(master/standby)15#安装 pgpool 相关函数(master)15#配置环境变量(master/standby)16#配置 pcp.conf(master/standby)16#配置 ifconfig,arping 执行权限(master/standby)20#配置 pgpool.conf

3、(master)16#配置 pgpool.conf(standby)20#failover_stream.sh 文件内容(master/standby)20#创建运行时目录(master/standby)24#配置 pool_hba(master)错误!未定义书签。#配置 pool_hba(standby)错误!未定义书签。#配置密码文件(master/standby)?错误!未定义书签。.架构图方案架构图pgpool-nodeOlStreamreplication1RW、122.1684.233ipostgresMaster192.16&L231postgresStandby192,

4、168.1,232postgres 故障切换图postgresMaster31postgresStandby192.1684.22、-1艇.16g.L玲3一postgresMaster192.1&8.1.231postgresStandby32pgpool-nodeQlClient“x-所fUalfpj-、里3一pgpoahnodeOZL1一RWpostgresM-aster31postgresStandby192,165.1.2322.安装资源信息操作系统的安装就不在这里介绍，主机名和 IP 地址如下所示操作系统：RH

5、EL6.5数据库：PostgreSQL9.4.4虚拟机两台:主节点 node1(IP31)备节点 node2(IP32)pgpool:pgpool-II-3.4.3VIP:33(虚拟 IP)3.资源调整(master/standby)3.1.前期准备创建安装文件夹#mkdir/opt/pgpool/pgpool 安装目录修改文件夹权限#chownpostgres:postgres/opt/soft#chownpostgres:postgres/opt/pgsql#chownpostgres:postgres/opt/pgdata

6、#chownpostgres:postgres/opt/pgpool复制以下安装文件到/opt/soft,并且配置权限pgpool-II-3.4.3.tar.gzpostgresql-9.4.4.tar.gz配置安装文件所属人和所属组为 postgres#chown-Rpostgres:potgres/opt/soft把此目录及子目录的所属人和所属组全换成 postgresroogpool-nodefllsoft#11total226681postgrespostgres94322Jan1312:26pgpool-11*3,4*3*tar.gz-rwr-r-,1postgrespostgres

7、23113477Jan1312;25postgresql-94%rootpgpool-nodeOlsoft#|3.2.关闭防火墙#chkconfigiptablesoff#serviceiptablesstop#mkdir/opt/soft 用于放置源码安装文件#mkdir/opt/pgdata/postgres 数据库目录#mkdir/opt/pgsql/postgres 安装目录关闭 SElinux修改 SELinux 配置文件#vim/etc/sysconfig/selinux把 SELINUX=enforcing 修改为 SELINUX=disabled津Thi5filecontrol

8、sthestateofSELirauxonthesystem.SELIMJX=cantakeoneofthesethreevalues：enforcing-SELmuxsecuritypolicyisenforci.permissive-SELinuxprintswarningsinstead。千enforcing,disabled-NoSELinuxpolicyisloaded,gELINUX-disabledSELINUXTYPE-cantakeoneofthesstwovalues：targeted-TargetedprocessarepotectQd.本mis-*MultiLevelS

9、ecurityprotection.SELTNUmPE=targsted配置 hosts 文件在 hosts 文件中增加以下内容#vim/etc/hosts31pgpool-node0132pgpool-node02127.0.&-1localfiostlocalhost.localdomainl(jcalhQ5t4Iocalhost4.localdofnai.n4：Ilocaltiastlocalhost.localdomainlocalhost5localhastb-locaLdofTiainG192+163+1.231pgpool-no

10、deGl092+163+1.232pqpool川odM2创建 postgres 用户并设置密码安装之前要先检查系统中是否有 postgres 系统账号cat/etc/passwd|greppostgres如果没有 postgres 账号，需要新增 postgres 账号groupadd-g26postgresuseradd-d/var/lib/pgsql-gpostgres-u26postgres给 postgres 配置密码passwdpostgresrootgpool-nodeOlpmssudpostgresCtiangngpassword+oruserpostgras.Newpa?rd:

11、BADPASSWORD；itisb爵汨onadictionarywordRetypenewpassword:passwd:allauthenticationtokensupdatedsuccessfully.raatpgpfiol-nade61T*|主备节点时钟同步#/usr/sbin/&/sbin/ 为网络时间通过 ntpdate 命令获取网络时间并且将其作为硬件时间配置 sysctlsysctl.conf（配置系统信息，系统变量，主要用来优化系统）/etc/sysctl.

12、conf 这个目录主要是配置一些系统信息，而且它的内容全部是对应于/proc/sys/这个目录的子目录及文件#vi/etc/sysctl.confkernel.shmmni=4096kernel.sem=5010064128000501001280fs.=7672460net.ipv4.ip_local_port_range=900065000net.core.rmem_default=1048576net.core.rmem_max=4194304net.core.wmem_default=262144net.core.wmem_max=1048576net.ipv4.tcp_tw_recy

13、cle=1net.ipv4.tcp_max_syn_backlog=dev_max_backlog=10000vm.overcommit_memory=0net.ipv4.ip_conntrack_max=655360fs.aio-max-nr=1048576net.ipv4.tcp_timestamps=0使文件修改生效#sysctl-plimit 资源分配vi/etc/security/limits.confsoftnohardnosoftnproc131072hardnproc131072softcoreunlimitedhardcoreunlimitedsoftmemlock50000

14、000hardmemlock50000000postgreSQL流复制环境安装配置配置 SSHfe 登陆Onmaster:#su-postgres$ssh-keygen-trsa-P$ssh-copy-id-i/.ssh/id_rsa.pub$sshpostgresnode2Onstandby:#su-postgres$ssh-keygen-trsa-P$ssh-copy-id-i/.ssh/idrsa.pub$sshpostgresnode1安装依赖包使用 yum 自动安装以下软件#yum-yinstallreadline-develzlibzlib-developensslopenssl-

15、develpam-devellibxml2-devellibxslt-develpython-develtcl-develgccperl-ExtUtils-Embed或者是手动安装以下软件gcclibxml2-devellibxslt-developenssl-develpam-develpython-develreadline-develtcl-develzlib-develcloog-pplcppkeyutils-libs-develkrb5-devellibcom_err-devellibgcrypt-devellibgpg-error-devellibselinux-devellibs

16、epol-develmpfrncurses-develppltcl解压和安装#su-postgres$cd/opt/soft/$tar-zxvfpostgresql-9.3.5.tar.gz$cdpostgresql-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$gmakeinstall设置环境变量编辑环境变

17、量配置文件，增加以下内容$vim/.bash_profileexportPGPORT=5432exportPGDATA=/opt/pgdataexportPGHOME=/opt/pgsqlexportPATH=.:$PGHOME/bin:$PATH使环境变量配置立即生效$source/.bashprofile初始化数据库(master)初始化数据库$initdb-D$PGDATA修改配置文件(master)找到以下参数内容，调整参数值$cd$PGDATA$vimpostgresql.conflisten_addresses=*port=5432max_connections=500wal_le

18、vel=hot_standbyarchive_mode=onarchive_command=/bin/datemax_wal_senders=6hot_standby=on访问控制文件 pg_hba.conf 配置(master)增加以下访问控制内容$cd$PGDATA$vimpg_hba.confhostallall31/32trusthostallall32/32trusthostreplicationreplica32/32trusthostreplicationreplica31/32trustlo

19、calallalltrusthostallalV/32trusthostallallG.0.0.0/0trusth&streplrcatLonreplica/0trustthostallall：V120trustt1标志文件recovery.done酉己置(master)新增一个 recovery.done 文件$touch/opt/pgdata/recovery.done$chmod777recovery.done在文件中输入以下内容$vim/opt/pgdata/recovery.done主酉己置文件 postgresql.conf参数调整(mast

20、er)standby_mode=onrecovery_target_timeline=latestprimary_conninfo=host=32port=5432user=replicapassword=replicatrigger_file=/opt/pgdata/trigger_file启动主库服务(master)$pg_ctl-D$PGDATA-l$PGDATA/log或$pg_ctl-D$PGDTAestartPgctl-D$PGDATAstart/opt/pgdata/logfile21$&配置流复制用户(master)创建流复制用户$psql-Upo

21、stgrespostgres#CREATEROLEreplicaloginreplicationencryptedpasswordreplica;备库实例生成(standby)从主库复制实例到备库#su-postgres$pg_basebackup-D$PGDATA-Fp-Xs-v-P-hnode1-Ureplica修改备库标识文件$mv/opt/pgdata/recovery.done/opt/pgdata/recovery.conf$vim/opt/pgdata/recovery.confstandby_mode=onrecovery_target_timeline=latestprima

22、ry_conninfo=host=192,168.1.231port=5432user=replicatrigger_file=/opt/pgdata/trigger_file启动备库服务(standby)$chown-Rpostgres:postgres/opt/pgdata$chmod0700/opt/pgdata$pgctlD$PGDATAl$PGDATA/logHMTil1postgres(anode2pg_ctl-DSPGDATAstartserverstartingpostgres(anode2FATAL；datadirectoryVopt/pgdata*hasgrouporwor

23、ldaccessDETAIL：Remissionsshouldbeu=rwx(。7。钻，检查运行情况查看备库进程ps-efgreppostgrespqcf)grmposrgfffiroot2WJ71S78915=42W:W；4W、u-pvstqp-tgFJ0O482064?0)5;42pts/S；M；00-ba5hplGtj的2G2111916:37pts/30Q：OG：CG，卯t/pgsql/biiiJpDGtg4D/pt/pgdjtdpoitgrMiXQU20211Q&：3?00:00:00附stgr”.“tart叩prmsrscov#n119OCOOOOO100000000000

24、6000：postQrn?O213血n016=37TW:W：eo普4tq8：diecK口口interpm匚蟠5pisstgrPE202142211Blfi：37?第00)况 巴二writerproEesposr-gr302152O21L0lfls37?鹏上陶，强postgres；丸?rstqllecTH/roty弓_202IO2M118lfis37TOG:O0:OG一同刷二prcK务btrvingd73OO?3rv查看主库进程ps-ef|greppostgres1Jiii,ef|qppostgresU1G51M620*1.9pt导修0i%;00IU-paitgrespmmtq明111的1116

25、501M19pts/9QG1QQ1QQ-hash1126310比二馥翻;州:的，叩上邛nqL1rbi口乳冀，-DgpL/paddl.apntgrHmug16:17?皿；8；00puTtgrM；cherkPBiFir&rprocpiipgstgr112661126301632?p*05tfr？s:writerpnocsspDtgre*5iL1267112&33?BB：由：SKIualwriterproemsMKQ艇11206112&816=J3TMlGOsW保！iUtflViCliLisi1junchrp0M均*L1Z3U28电Hill7i.：7：=-：=arciwer二

26、二二”last布.口:m.二口工匚，.，E.ZKJ 句ta:-：jpIMStgresL127Q112630?&B:3G：0G(MJStflrM：:号tat53111奥工。匚见“65月pgtgu112疆1DG3016:37?QB上SB：081JBdErOFOE*npli192.16B.1.13Q13EIJL1JrwinqDTTEd测试OnMaster:$psql-Upostgrespostgres=#createtabletest(idint,namevarchar(10);postgres=#selecttable_namefrominformation_schema.tableswh

27、eretableschema=public;tablenametest(1row)postgres=#selecttablenamefroirinformationschema,tableswheretable_schena=public1；tablenametesttesta(2raws)OnStandby:postgres=#selecttablenamefrominformationschema.tableswheretableschema=public;tablenametest(1row)新增的数据已经传输过去，并且 standby 端的会话是只读的。pgpool-II安装配置安装解

28、压和安装(master/standby)#su-postgres$mkdir/opt/pgpool$tarzxvfpgpool-II-3.4.3.tar.gz$cdpgpool-II-3.4.3$./configure-prefix=/opt/pgpool-with-pgsql=/opt/pgsql-with-openssl$make$makeinstall安装相关函数(master)安装 pgpool-regclass,pgpool-recovery 函数postgresnode1$cd/opt/soft/pgpool-II-3.4.3/src/sql/pgpool-regclasspost

29、gresnode1pgpool-regclass$makepostgresnode1pgpool-regclass$makeinstallpostgresnode1pgpool-regclass$psql-fpgpool-regclass.sqltemplate1postgresnode1pgpool-regclass$cd./pgpool-recoverypostgresnode1pgpool-recovery$makepostgresnode1pgpool-recovery$makeinstallpostgresnode1pgpool-recovery$psql-fpgpool-recov

30、ery.sqltemplatel安装完以后/opt/pgsql/share/extension/目录下应该有如下文件pgpoolrecovery-1.1.sqlpgpool_recovery.controlpgpool-recovery.sqlpgpool_regclass-1.0.sqlpgpoolregclass.controlpgpool-regclass.sql备注：备节点不需要创建 pgpool_regclass,pgpool_recovery)查看新增加的函数postgresnode1$psql-Upostgrestemplate1template1=#df配置环境变量(maste

31、r/standby)$vim/etc/profileexportPGPOOL=/opt/pgpoolexportPATH=.:$PGPOOL/bin:$PATH使环境变量立即生效$source/etc/profile配置 ifconfig,arping 执行权限(master/standby)配置 sudo 权限#vim/etc/sudoerspostgresALL=(ALL)NOPASSWD:/sbin/ifconfigpostgresALL=(ALL)NOPASSWD:/sbin/ifuppostgresALL=(ALL)NOPASSWD:/sbin/ifdownpostgresALL=(

32、ALL)NOPASSWD:/sbin/arpingteirplatel=#dfSchema|+MameListoffunctionsIResultdatatype|Arginantdatatypespublicpublicpublicpublic(4rows)pgpool_pgctlpgpoolrecoverypgpo01_remote_startpgpool_switch_xlogbooleanbooleanbaoleantext+Itext,Itext,Itext,texttexttext.text,texttextIType+InormalInormalInormalnormal渗加文杵

33、的与叔眼*乜就是输入命令山idQ+研Et75口二叫rr5l2776pKtgres2777w京gr酷27JBpaMgm,?77spostgrs278Sroot2K2PS钝破2S3potgres2B6potgrE2912中Stqrfi,泮0pKtqras29Q0podulgirb上翼工postgr5i2K2postgre?2J23pQVtgtQE3也4po4tgresP誓5poHstgres2K&2打9黑2927psstqrfiE独RpKtgrcs2929uotarea29第2?3.,：2773上：ZE27731430娜 ?27J32833?,-：29122g122912MU油？2312金

34、写U戏172912现?-#T口gr#ppDiigrniri4pts/cro-Q6；relhl4?总2腼:初lrU11-14Tffi-QGzK11:14fIlcHTW=iWIlsl4?；ee：9b1X21ptl/fi的二时!帕11;21pU/Q11:25?EE:BC;0OUL?pt”白幅:BB:的11；?8pt“g情工解:的ii：ji晔用OQzQOiOOU：2fiRl”白口；葩ptt/u8二地的11;2pts汨11：挹外“自曜腼：的11;2ptS/G哑:晚OzZS厘内的工画；SBUi.aptS/D而1强;你1J：pt“白崎第100I1L2Spts/1笃：的H：2fipt/QJ5in-|M5tg5

35、-D-opt/pqdata口花博由招r曾就加1衽的proce&Epoit|FMswriterpractHVpMtgres；ualwriterpr&tessp-Mtgesi昌七口的CUMlauncherpr$ccs&pwitjinM：arhjvtrpectsqlantHFI巾QfblKlECKMMKKrflPaBZCQBpWSPickuppotgrBststatscellBCtorprocessmu-pDBt-gregfrashpMtgesiwalsenesrpro二行三replica192.1EB,1.232(55495kstreaTiirg9/2DO0C15E-nD0

36、crnli：-Hirhd叫p学igLhaartbaatreceiver,IL:hmrtbedthendleppdOl;lifKhKkpgpaclEwaitforcflnrgctionrequestp事|。巾1WilIt f*r(.nHMtCLCiHIoqu9tp曹mhwaiff*rcioiwctiMreuwlpg(IOl：MBitforcanrectnonrefluestPfpiMflswaitf-irconrecuonriwestpqpdaUMHltforccprflrtionoquslp-gpaoliwaittorconnirctianrequtugnan-Uudillofcunrecti

37、onrequestOnslave:l口说打科修od鼠mgit27pastures2773口白ng-0&之822postgres，?snP03tgir9?a?4pa=;tqres2&2Spdstares23poitgrM282728Upgstgres2345pattgres2S4TpMtMes7a48口MtgwzawpaEtqes 2a50postgres2BS1patgr!p”tgm靖工pgstgr3854postgres2SS5P05taresM5optjtices27E312B2? 曜2Q222B222日 送21鸡2B4428442044?02H44/M42IM2B442B

38、442044n：或11:22二二11525n：?s11:25.l:/o11:2S11:28Ils2811:281112911；9lliTH.1:Z8L1:2Blls2BII;2311:28Ig“ppitE/pts/plS/0HtgEipts/6pts/6pts/6ptOptClpt5-0pts/oWOpt5/6pts/6pt9/09Q:6fi50!QQ：ee0Q：9D50:90鼠的upattqrvi-bash/Dpt/pgiqI/bin/pofitgres-D/cpL/pgddldp/tgr蜴i仪tipprecess0m地ri/加9翎90S蜀e9班)魄MJ0G2DpTg：rnprirpflii

39、nter口E:国pestgreszwriterprocesEpcstgires.：statcolltarprovespoitgirwJwilreceiverproctsiitnuninig0/2UW01&6pgpoQl-npgiwk响txh加gpgpQobh打t网七rweiverpgpunlheditbaibenderPlwl；lifedwkpapaal“litforcannrtiararequestpgpDQl：而ittorconnectiararequiestpgpgL;后NF*rconniR.lignTH窜电IpgpgLMilforconnKtiorirquvstp那叭;田tfo

40、rconnectian日叩芮七pgpglugitfurconnectiorarequestQpgLlAdilGrcDimecti俏ifOuraT通过 pgpooi 访问数据库postgresnode1$psql-hlocalhost-p9999-Upostgres-dpostgrespostgresianodelT$psql-hlocalhwt-p9999-Upostgres-dpostgrespsql(9.2.5)Typehelp*forhelp.postgresMr|查看 pgpool 集群中 nodes 的状态postgres=#showpoolnodes;postgres=#showp

41、ool_nades；node_id|十hostnameport|status|lb_weight|role01(2rows)pqpoal-nodeOlpgpoalnode0254325432十一0.5000000.500000十IprimaryIstandby关于 status 状态，如下0 0- -该状态仅仅用于初始化，PCPPCP 从不显示它- -节点已启动，还没有连接。- -节点已启动，连接被缓冲。- -节点已关闭。查看虚拟 IPOnMaster:Lpostgres(anodel-$ipa1：lo：LOOPBACK,UP,LOWERJJFmtn16436qdiscnoqueuestate

42、UNKNOWN1lAk/locpbackQG：GO：0Q：OG：06：00brdOG：0&：0G：OS：00：Q&met127.G.0.1/8StopehostIflinetfi：L/Liascopehostvalidftforeverpreferred_tftfcjrever2：eth:mtu15GOqdiscmqstateUPqla1060link/ether00=5&：56：fiSsfe：Slbnfl：ff：ffmet192,168.1,231724tjrd55scopeglobalethomet192.l&S,1.233/24brd

43、192.16B+1+255scopeglobalsecondaryethO:Oinetfi：2SG：56ff：fe95：fe5V64scopelinkvalidlftforeverpreferred!ftforever6.Failover测试关闭主库 postgres 进程测试使用 Pg_ctl-D$PGDATA-mfaststop 停止主库服务pHTgrtia4eL叫$pg_cil-D阳Will“fMTit*rtaitirqf*r：酒帕u1progross.1H1L；：痴 姐1=11：lpragrws-ans-niii2fin-n;r4rtnchutdev.2036-61-3111:3G-：

44、3QipidEfnER：failedtouthsnticateILg！前一pid3J；型：篦 xpH11:3426:pidll-SG：J：pid扬：范1.pid工勺】二S?12：2912=BElMLi!ihglHJiilh6H工Miohfrwsjgo*mpaRuPL=ticlingPlmdrotaivcd-kKIfjilodtggwtfaEsrverQHEWWR：口讥Mlitzkg曲E时DETAIL=CDrrKti&nt白iiD&tirKidh15432Failed(JOG!fulsdlaBHFWC星ticsrvaronETOR：tn.ledtnek?pEEistentdbc&

45、amp;rmectiarDEIMLi!i-onMTlonitaW虱i.时fillw-rwdghM-JSZ1.gntEOckapit0datctaddfltKEGririrnrrerCardtinn?wnewiri2&16&1-11PFDflflMlS-2016e1-IL2d加81-11MM-m-n2tfmi2MoMLatM-n-u-T.J汨董I-JlIKU；有却?912：IIIKEipp291iUKshvtltlvChrtinimtryL0&fsil&Stnrc-medttCser?-anenunrf1MdteU&oE-Opmtitnxyw inpidd

46、pl。pidpidpidpid29029122912-河厂鸿心aw2W-EHROFfilledt&nak?wrmerrf曲3F也”小HE16JL-tOrtnettl&ftTa*1好七产领制1；义tSllMLdOiiwailth/-hi,“收中(Mint2LOQ；failedIDconnectttPi5tgre5QLservergoEffiDH-filledtB曲*口BfitwvtdbcwwiKrifiWMVsorvactiontohn?.t?xj(fel；M32_failMLQQ;及-ImMckj寸rM，C融帆3LDCi；failedtnSMecttePgtqr61gL口”rw

47、ifeliSIir.detectederrortrrorOpeatitninin关闭主库 pgpool 进程Nodel 关闭数据库pg93db2etc$pg_ctlstop-mfastwaitingforservertoshutdowndoneserverstoppednode2 上测试pg93db1etc$pg_controldata|grepclusterDatabaseclusterstate:inproductionpg93db1etc$pg93db1etc$ll/database/pg93/pg_root/recovery.done-rw-r-r-1pg93pg934.8KOct20

48、9:05/database/pg93/pg_root/recovery.done备注：可以看到 db1 节点已完成从 standby 角色切换到 primary,并且$PGDATA/recovery.conf 文件变成 recovery.done。查看 pgpool 状态francsfrancs:$psql-h00-p9999postgrespostgresPasswordforuserpostgres:psql(9.3.5,server9.3.3)Typehelpforhelp.postgres=#showpool_nodes;node_id|hostname|port

49、|status|lb_weight|role+0|db1|1921 |2|0.500000|primary1|db2|1921 |3|0.500000|standby(2rows)备注：db1已转换成primary 角色。db2 状态为 3,表小 down 状态接下来以 standby 身份启动 db2pg93db2pg_root$mvrecovery.donerecovery.confpg93db2pg_root$pg_ctlstartdb1 上操作，添加 db2 中点信息pg93db1etc$pcp_attach_node-d5db29898pgpoolpgpool1DEBUG:send:

50、tos=R,len=44DEBUG:recv:tos=r,len=21,data=AuthenticationOKDEBUG:send:tos=D,len=6DEBUG:recv:tos=c,len=20,data=CommandCompleteDEBUG:send:tos=X,len=4db2 状态恢复pg93db1etc$psql-p9999-UpostgresPasswordforuserpostgres:psql(9.3.3)Typehelpforhelp.postgres=#showpool_nodes;node_id|hostname|port|status|lb_weight|role+0|db1|1921|2|0.500000|primary1|db2|1921|2|0.500000|standby(2rows)使用 pgpool-mfaststop 停止 pgpool 节点 1 的进程检查虚拟 IP 已经漂移到节点 2 服务器partgres9iodelpgtbtajfip