1、Postgresql （主從）hot_standby安裝說明一、搭建PT備份數(shù)據(jù)庫1 配置環(huán)境變量vi /etc/sysctl.confkernel.shmmni = 4096kernel.sem = 50100 64128000 50100 1280fs.file-max = 7672460net.ipv4.ip_local_port_range = 9000 65000net.core.rmem_default = 1048576net.core.rmem_max = 4194304net.core.wmem_default = 262144net.core.wmem_max = 1048

2、576net.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 = 0使文件修改生效sysctl -pvi /etc/security/limits.conf* soft nofile 131072* hard nofile 131072* soft nproc 131072*

3、 hard nproc 131072* soft core unlimited* hard core unlimited* soft memlock 50000000* hard memlock 500000002 建立數(shù)據(jù)庫用戶給兩個(gè)節(jié)點(diǎn)分別創(chuàng)建postgres用戶并設(shè)置密碼mkdir /home/postgreschown -R /home/postgresgroupadd postgresuseradd -u 501 -g postgres -d /home/postgres -s /bin/bash postgrespasswd postgres3 ssh免密鑰通信配置兩臺(tái)機(jī)器的ssh

4、免密鑰登錄ssh-keygen -t rsa主機(jī)：cat /.ssh/id_rsa.pub >> /.ssh/authorized_keyschmod go-rwx /.ssh/*cd /.sshscp id_rsa.pub postgres57:/home/postgres/.ssh/id_rsa.pub1備機(jī)：cd /.sshcat id_rsa.pub1>>authorized_keyschmod go-rwx /.ssh/*scp id_rsa.pub postgres56:/home/postgres/.ssh/

5、id_rsa.pub2主機(jī)：cat id_rsa.pub2>>authorized_keys備機(jī)：cat id_rsa.pub1>>authorized_keys4 安裝PT數(shù)據(jù)庫軟件安裝PG的依賴軟件包：zypper install lrzsz sysstat e4fsprogs ntp readline-devel zlib zlib-devel openssl openssl-devel pam-devel libxml2-devel libxslt-devel python-devel tcl-devel gcc make flex bison創(chuàng)建源碼包存儲(chǔ)目錄

6、mkdir /app/databasemkdir /app/pgsqlchown -R postgres:users /app/databasechown -R postgres:users /app/pgsqlsu - postgrespostgresql必須用postgres 用戶安裝，安裝之前先改變postgresql的將要安裝的目錄的權(quán)限安裝PostgreSQLwget /pub/source/v9.5.3/postgresql-9.5.3.tar.gzcd /app/database tar zxvf postgresql-9.5.3

7、.tar.gzcd postgresql-9.5.3./configure -prefix=/app/pgsql -with-pgport=5432 -with-perl -with-python -with-openssl -with-pam -without-ldap -with-libxml -with-libxslt -with-blocksize=8 gmake worldgmake install-world主備節(jié)點(diǎn)設(shè)置相關(guān)環(huán)境變量(主備節(jié)點(diǎn)應(yīng)該一致)vi /home/postgres/.bash_profileexport PGHOME=/app/pgsqlexport LD_L

8、IBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATHexport DATE=date +"%Y%m%d%H%M"export PATH=$PGHOME/bin:$PATH:.export MANPATH=$PGHOME/share/man:$MANPATH備機(jī)安裝pg_standby進(jìn)入編譯后的/postgresql-9.5.3/contrib./pg_standby目錄，執(zhí)行以下命令make install這時(shí)便安裝好

9、pg_standby。5 主機(jī)應(yīng)用配置主機(jī)初始化數(shù)據(jù)庫mkdir -pv /app/DBchown -R postgres:users /app/DBcd /app/pgsql/bin./initdb -D /app/DB 備庫(postgres用戶安裝 備節(jié)點(diǎn)只需要安裝軟件，不需要init數(shù)據(jù)庫)配置監(jiān)聽地址和端口：vi postgresql.conflisten_addresses = '*'port = 5432wal_level = hot_standbymax_wal_senders = 1允許遠(yuǎn)程連接：vi pg_hba.conf添加host all all 192

10、.168.11.0/24 trusthost replication postgres /0 trust主機(jī)設(shè)定WAL歸檔vi postgres.conf設(shè)定如下參數(shù)：archive_mode = onarchive_command = 'scp %p 57:/app/pgsql/backup/standby_log/%f </dev/null'archive_timeout = 1200hot_standby = onarchive_mode參數(shù)，開啟WAL歸檔archive_command參數(shù)，設(shè)定歸檔時(shí)執(zhí)行的命令，我們這里通

11、過scp把主機(jī)的WAL歸檔日志傳送到備機(jī)的/app/pgsql/backup/standby_log/目錄下archive_timeout參數(shù)，規(guī)定了最多多久執(zhí)行一次歸檔，也是發(fā)生故障時(shí)數(shù)據(jù)庫丟失數(shù)據(jù)的最長時(shí)間。設(shè)定很小的話保證了安全性，增加了網(wǎng)絡(luò)傳輸量。 postgresql數(shù)據(jù)庫隨系統(tǒng)啟動(dòng)而啟動(dòng)將啟動(dòng)腳本拷貝到/etc/init.d/目錄下，具體執(zhí)行如下命令：cd /etc/init.dcp (第一步解壓的安裝文件目錄) /contrib/start-scripts/linux postgresqlchmod +x postgresqlvi postgresqlprefix=/a

12、pp/pgsqlPGDATA="/app/DB"PGUSER=postgresPGLOG="/app/DB/log/pgsql.log"chkconfig -add postgresql啟動(dòng)數(shù)據(jù)庫：mkdir /app/DB/logservice postgresql start6 備機(jī)應(yīng)用配置建立備機(jī)數(shù)據(jù)庫主機(jī)開始備份psql postgres（實(shí)例名）select pg_start_backup('hotbak');主機(jī)壓縮備份整個(gè)data目錄mkdir /app/pgsql/backuptar -jcv -f /app/pgsql/

13、backup/baseline.tar.bz2 /app/DB/主機(jī)通過scp復(fù)制到備機(jī)scp -v /app/pgsql/backup/baseline.tar.bz2 57:/app/pgsql/backup/baseline.tar.bz2備機(jī)解壓縮備份文件sudo tar -jxv -f baseline.tar.bz2 -C /主機(jī)結(jié)束備份psql postgresselect pg_stop_backup();備機(jī)進(jìn)行設(shè)置，啟動(dòng)到恢復(fù)模式在/app/DB/目錄內(nèi)建立recovery.conf文件cp /app/pgsql/share/recovery.con

14、f.sample /app/DB/recovery.confvi recovery.confstandby_mode = 'on'primary_conninfo = 'host=56 port=5432 user=postgres'trigger_file = '/app/DB/pgsql.trigger.5432'vi postgres.confhot_standby = on#刪除pid文件rm postmaster.pid更改postgres.conf文件中備份設(shè)置把a(bǔ)rchive_mode、archive_com

15、mand、archive_timeout三個(gè)參數(shù)注釋掉，這些參數(shù)是主機(jī)設(shè)置的，由于我們備機(jī)暫時(shí)不需歸檔，所以注釋掉他。 啟動(dòng)備機(jī)postgresql數(shù)據(jù)庫建立postgresql服務(wù)啟動(dòng)數(shù)據(jù)庫pg_ctl D /app/DB start備機(jī)會(huì)一直讀取主機(jī)傳送的WAL歸檔日志進(jìn)行恢復(fù)- -另一歸檔方法（未驗(yàn)證） -cp /app/pgsql/share/postgresql/recovery.conf.sample /app/DB/recovery.confvi recovery.confrestore_command = 'pg_standby -d -s 2 -t /tmp

16、/pgsql.trigger.5432 /usr/local/pgsql/backup/standby_log %f %p %r 2>>standby.log'recovery_end_command = 'rm -f /tmp/pgsql.trigger.5432'/tmp目錄下創(chuàng)建pgsql.trigger.5432文件，停止備份后會(huì)刪除該文件防止錯(cuò)誤操作7 測試1) 主機(jī)登錄standby數(shù)據(jù)庫，創(chuàng)建表并插入相應(yīng)數(shù)據(jù)psql postgrescreate table tb(a int);insert into tb(a) values(1);inser

17、t into tb(a) values(2);insert into tb(a) values(3);2) 備機(jī)查看日志，確定主機(jī)剛發(fā)送過來的WAL歸檔日志已經(jīng)被應(yīng)用到備機(jī)上。 3) 登錄備機(jī)standby數(shù)據(jù)庫，查看數(shù)據(jù)是否恢復(fù)psql postgresselect * from tb;a-1238 實(shí)現(xiàn)主備切換（熱備）關(guān)閉主庫(on Primary)postgrespg1 pg_root$ pg_ctl stop -m fast -D $PGDATAwaiting for server to shut down. doneserver stopped激活備庫到主庫狀態(tài) ( on

18、slave )激活備庫只要?jiǎng)?chuàng)建一個(gè)文件即可，根據(jù)備庫 recovery.conf 配置文件的參數(shù) trigger_file 值，創(chuàng)建這個(gè) trigger 文件即可。 例如 "touch /opt/pgdata/pg_root/postgresql.trigger.1921 "postgrespgb pg_root$ touch /opt/pgdata/pg_root/postgresql.trigger.1921過一會(huì)兒發(fā)現(xiàn) recovery.conf 文件變成 recovery.done ，說明備庫已經(jīng)激活。創(chuàng)建監(jiān)測自動(dòng)激活腳本vi standbydog.sh#!/bin

19、/shcheck_process() RET=ps ax | grep $1 | grep -v "grep" | wc -l if $RET -eq 0 ; then return 1; else return 0; ficheck_process walif $? -ne 0 ;then touch /app/DB/pgsql.trigger.5432else exitfiexit 0激活原來的主庫，讓其轉(zhuǎn)變成從庫(在原來的主庫上執(zhí)行) -創(chuàng)建 $PGDATA/recovery.conf 文件，配置以下參數(shù)recovery_target_timeline = '

20、latest'standby_mode = 'on'  -標(biāo)記PG為STANDBY SERVERprimary_conninfo = 'host=57 port=5432 user=postgres '   -標(biāo)識(shí)主庫信息trigger_file = '/app/DB/pgsql.trigger.5432'     -標(biāo)識(shí)觸發(fā)器文件-將原來的主庫（現(xiàn)在的從庫）啟動(dòng)postgrespg1 pg_root$ pg_ctl start -D $PGDA

21、TAserver starting-查看從庫日志， 發(fā)現(xiàn)大量 FATAL 錯(cuò)誤信息2011-08-24 21:31:59.178 CST,17889,4e54fd4f.45e1,1,2011-08-24 21:31:59 CST,0,FATAL,XX000,"timeline 6 of the primary does not match recovery target timeline 5",""2011-08-24 21:32:04.208 CST,17891,4e54fd54.45e3,1,2011-08-24 21:32:04 CST,0,FAT

22、AL,XX000,"timeline 6 of the primary does not match recovery target timeline 5",""2011-08-24 21:32:09.135 CST,17892,4e54fd59.45e4,1,2011-08-24 21:32:09 CST,0,FATAL,XX000,"timeline 6 of the primary does not match recovery target timeline 5",""2011-08-24 21:32:14

23、.136 CST,17895,4e54fd5e.45e7,1,2011-08-24 21:32:14 CST,0,FATAL,XX000,"timeline 6 of the primary does not match recovery target timeline 5",""   備注：出現(xiàn)了大量 FATAL,XX000,"timeline 6 of the primary does not match recovery target timeline 5”     &

24、#160;       估計(jì)是時(shí)間線有問題，網(wǎng)上查了下資料也沒啥結(jié)果，后來咨詢了下德哥，只要將從庫 $PGDATA/pg_xlog               一個(gè)文件考過來就行。         -將主庫文件 00000006.history 復(fù)制到從庫postgrespgb pg_xlog$ scp 00000006.his

25、tory postgres5:/opt/pgdata/pg_root/pg_xlogpostgres5's password: 00000006.history                 -再次查看從庫日志2011-08-24 21:36:04.819 CST,17948,4e54fe44.461c,1,2011-08-24 21:36:04 CST,0,FATAL,XX000,

26、"timeline 6 of the primary does not match recovery target timeline 5",""2011-08-24 21:36:09.742 CST,17885,4e54fd44.45dd,5,2011-08-24 21:31:48 CST,1/0,0,LOG,00000,"new target timeline is 6",""2011-08-24 21:36:09.824 CST,17977,4e54fe49.4639,1,2011-08-24 21:36:09

27、 CST,0,LOG,00000,"streaming replication successfully connected to primary",""  備注：根據(jù)日志信息，說明從庫已經(jīng)恢復(fù)正常；9 共享存儲(chǔ)使用應(yīng)用環(huán)境：兩臺(tái)主機(jī)共享存儲(chǔ)，非集群負(fù)載1、 創(chuàng)建角色postgres=# create role "TestRole2"postgres=# select rolname from pg_roles;2、 創(chuàng)建用戶postgres=# create user "TestUser2" with

28、password 'TestUser2' login in role "TestRole2"3、 創(chuàng)建表空間postgres=# create tablespace "TestDbs2" location 'E:PostgreSQLdataTestDbs2'CREATE DATABASE dbname OWNER kanon TEMPLATE template1 TABLESPACE tablespacename;4、 創(chuàng)建數(shù)據(jù)庫postgre

29、s=# create database "TestDb2"建議使用pgadmin3工具創(chuàng)建10 清理pg_xlog日志postgrestest01-> pg_controldatapg_control version number:            922Catalog version number:               201204301Database system identifier:           5849803358968196539Database cluster state: