| Role | IP | 系統(tǒng) | 數(shù)據(jù)庫 |
|---|---|---|---|
| 源庫 | 10.10.10.60 | Centos6.5 | postgresql 9.2 |
| 備份庫 | 10.10.10.61 | Centos6.5 | postgresql 9.2 |
需求說明：源庫產(chǎn)生歸檔日志，并傳輸?shù)絺浞輲焐系臍w檔目錄/data/pg_archive；備份庫利用歸檔日志，恢復(fù)至源庫的任意時間點的數(shù)據(jù)。
注意:基礎(chǔ)環(huán)境我們基于postgresql流復(fù)制，但是備份庫作為一個獨立的庫，此時請保證recovery.conf中的standby_mode=off
1.ssh無密碼登錄
由于我們備份和還原過程中所用的archive_command和restore_command命令都以postgres用戶運行，因此我們需要針對postgres用戶實現(xiàn)ssh無密碼登錄。
#源庫 ssh-ketgen -t rsa scp id_rsa.pub postgres@10.10.10.60:/var/lib/pgsql/.ssh/authorized_keys #備份庫 ssh-ketgen -t rsa scp id_rsa.pub postgres@10.10.10.61:/var/lib/pgsql/.ssh/authorized_keys
**注意:**yum安裝postgresql時，默認生成的postgres用戶的家目錄在/var/lib/pgsql
2.配置備份庫的歸檔目錄
#備份庫 mkdir -p /data/pg_archive chmod postgres.postgres /data/pg_archive
說明：源庫產(chǎn)生的歸檔日志，要存到到異地備份庫的/data/pg_archive下。
3.修改源庫的postgresql.conf
在postgresql.conf中添加以下幾行
#開啟歸檔模式 archive_mode = on archive_command = 'ssh 10.10.10.60 test ! -f /data/pg_archive/%f scp %p 10.10.10.60:/data/pg_archive/%f'
其中： %p表示wal日志文件的路徑，%f表示wal日志文件名稱。archive_command表示先驗證備份庫的歸檔目錄下是否存在同名文件，以免發(fā)生覆蓋丟失數(shù)據(jù)，若不存在將源庫上產(chǎn)生的歸檔日志保存到備份庫的/data/pg_archive目錄下。
注意:
(a)archive_timeout強制N秒以后進行一次歸檔,若設(shè)置太小，很快就會超過wal_keep_segments = 16，導(dǎo)致數(shù)據(jù)覆蓋丟失，因此不要盲目設(shè)置。
(b)歸檔模式的開啟，只有在wal_level = hot_standby或archive
4.重載源庫并查看
pg_ctl reload -D /data/pgsql/data postgres=# show archive_mode; archive_mode -------------- on (1 row)
1.查看源庫上的pg_xlog目錄
-bash-4.2$ ll pg_xlog total 16388 -rw-------. 1 postgres postgres 16777216 Apr 21 13:42 000000010000000000000001 drwx------. 2 postgres postgres 4096 Apr 21 13:36 archive_status
此時archive_status目錄為存放歸檔日志的狀態(tài)，若歸檔已經(jīng)產(chǎn)生，但沒有傳輸成功則為xxx.ready，并且一直會保留直至傳輸成功，然后狀態(tài)變?yōu)閤xx.done;此時目錄為空
2.在源庫上添加數(shù)據(jù)
此時由于數(shù)據(jù)庫為空，我們來創(chuàng)建testdb庫，并添加數(shù)據(jù)
postgres=# create database testdb; CREATE DATABASE postgres=# create table t1(id int4,create_time timestamp(0) without time zone); CREATE TABLE postgres=# insert into t1 values(1,now()); INSERT 0 1 postgres=# insert into t1 values(2,now()); INSERT 0 1 postgres=# select * from t1; id | create_time ----+--------------------- 1 | 2016-04-21 13:49:34 2 | 2016-04-21 13:49:48 (2 rows)
3.在源庫上手動切換歸檔
postgres=# select pg_switch_xlog(); pg_switch_xlog ---------------- 0/1821010 (1 row)
正常情況下，wal日志段在達到16M后會自動歸檔，由于測試我們使用手動切換歸檔。
4.查看源庫pg_xlog目錄
-bash-4.2$ ll pg_xlog/ total 16388 -rw-------. 1 postgres postgres 16777216 Apr 21 13:42 000000010000000000000001 drwx------. 2 postgres postgres 4096 Apr 21 13:36 archive_status -bash-4.2$ ls pg_xlog/ 000000010000000000000001 000000010000000000000002 archive_status -bash-4.2$ ls pg_xlog/archive_status/ 000000010000000000000001.ready
此時歸檔日志的狀態(tài)為ready，說明此日志沒有傳輸成功，查看日志
vim /data/pgsql/pg_log/postgresql-Thu.log ssh: connect to host 10.10.10.60 port 22: Connection timed out^M FATAL: archive command failed with exit code 255 DETAIL: The failed archive command was: ssh 10.10.10.68 test ! -f /data/pg_archive/000000010000000000000001 scp pg_xlog/000000010000000000000001 10.10.10.60:/data/pg_archive/000000010000000000000001 LOG: archiver process (PID 22284) exited with exit code 1
原來是由于ip地址錯誤導(dǎo)致無法通過ssh傳輸，更改ip為10.10.10.61后，再次產(chǎn)生歸檔才能再次重新傳輸。
1.手動切換wal日志，select pg_switch_xlog()
2.wal日志寫滿后觸發(fā)歸檔，配置文件默認達到16M后就會觸發(fā)歸檔，wal_keep_segments = 16
3.歸檔超時觸發(fā)歸檔，archive_timeout
在此我們使用的是手擋切換歸檔。
postgres=# insert into t1 values(3,now()); INSERT 0 1 postgres=# insert into t1 values(4,now()); INSERT 0 1 postgres=# select pg_switch_xlog(); pg_switch_xlog ---------------- 0/2000310 (1 row) postgres=# select pg_switch_xlog(); pg_switch_xlog ---------------- 0/3000000 (1 row) postgres=# select pg_switch_xlog(); pg_switch_xlog ---------------- 0/30000D8 (1 row)
再次查看pg_xlog目錄
-bash-4.2$ ll pg_xlog/archive_status/ total 0 -rw-------. 1 postgres postgres 0 Apr 21 13:51 000000010000000000000001.done -rw-------. 1 postgres postgres 0 Apr 21 14:00 000000010000000000000002.done -rw-------. 1 postgres postgres 0 Apr 21 14:04 000000010000000000000003.done
5.查看備份庫上的歸檔目錄
-bash-4.2$ ll /data/pg_archive/ total 49152 -rw-------. 1 postgres postgres 16777216 Apr 21 14:04 000000010000000000000001 -rw-------. 1 postgres postgres 16777216 Apr 21 14:04 000000010000000000000002 -rw-------. 1 postgres postgres 16777216 Apr 21 14:04 000000010000000000000003
至此，歸檔備份已經(jīng)完成，下面我們要介紹利用歸檔進行恢復(fù)。
PITR恢復(fù)是基于文件系統(tǒng)備份和wal文件的備份，因此首先我們需要個基礎(chǔ)備份，然后在此基礎(chǔ)備份上對wal歸檔日志進行回放。具體步驟如下:
1.使用pg_basebackup進行基礎(chǔ)備份
pg_basebackup使用replication復(fù)制協(xié)議，因此需要在源庫上配置pg_hba.conf文件以允許replication，無論是本地還是通過網(wǎng)絡(luò)。
vim pg_hba.conf #添加以下兩行，允許本地和網(wǎng)絡(luò)上的replication用于pg_basebackup host replication rep 127.0.0.1/32 md5 host replication rep 10.10.10.61/8 md5 #重載 pg_ctl reload -D /data/pgsql/data
添加完畢后請重載pgsql
在備份庫上執(zhí)行pg_basebackup進行遠程的基礎(chǔ)備份
-bash-4.2$ pg_basebackup -D /data/pgsql/data -Fp -Xs -v -P -h 10.10.10.61 -p 5432 -U rep Password: transaction log start point: 0/5000020 pg_basebackup: starting background WAL receiver 26664/26664 kB (100%), 1/1 tablespace transaction log end point: 0/50000E0 pg_basebackup: waiting for background process to finish streaming... pg_basebackup: base backup completed
-D 表示接受基礎(chǔ)備份的目錄，我們將基礎(chǔ)備份放到/data/pgsql/data
-X 參數(shù)，在備份完成之后，會到主庫上收集 pg_basebackup 執(zhí)行期間產(chǎn)生的 WAL 日志，在 9.2 版本之后支持 -Xs 即stream 形式，這種模式不需要收集主庫的 WAL 文件，而能以 stream 復(fù)制方式直接追趕主庫。
2.修改備庫上配置文件
由于所有的配置文件是從源庫上的備份過來的，因此我們需要修改：
vim postgresql.conf 屏蔽以下兩行 #archive_mode = on #archive_command = 'ssh 192.168.3.139 test ! -f /data/pg_archive/%f scp %p 192.168.3.139:/data/pg_archive/%f'
3.查看源庫上的時間確認需要的恢復(fù)時間點
postgres=# select * from t1; id | create_time ----+--------------------- 1 | 2016-04-21 13:49:34 2 | 2016-04-21 13:49:48 3 | 2016-04-21 14:00:22 4 | 2016-04-21 14:00:25 5 | 2016-04-21 14:49:11 6 | 2016-04-21 14:49:14 7 | 2016-04-21 14:49:17 (4 rows)
由于此次基礎(chǔ)備份是在“ 4 | 2016-04-21 14:00:25”這條記錄后歸檔，而后面的5，6，7三條記錄是在基礎(chǔ)備份后生成的，因此若恢復(fù)5，6，7中的記錄需要在基礎(chǔ)備份上通過回放5，6，7的歸檔日志達到。
在此我們要將數(shù)據(jù)恢復(fù)到6這條記錄下，需要在recovery.conf中做如下設(shè)置：
cp /usr/share/pgsql/recovery.conf.sample /data/pgsql/data/recovery.conf vim recovery.conf restore_command = 'cp /data/pg_archive/%f %p' recovery_target_time = '2016-04-21 14:49:14'
**注意：**recovery.conf中standby_mode要為off，否則備份庫將會以備庫身份啟動，而不是即時恢復(fù)。
4.啟動備份庫
備份庫啟動過程中，會進行PITR恢復(fù)到指定的時間點
pg_ctl start -D /data/pgsql/data #查看日志 vim /data/pgsql/pg_log/postgresql-Thu.log LOG: database system was interrupted; last known up at 2016-04-21 14:34:29 CST LOG: starting point-in-time recovery to 2016-04-21 14:49:14+08 LOG: restored log file "000000010000000000000005" from archive LOG: redo starts at 0/5000020 LOG: consistent recovery state reached at 0/50000E0 LOG: restored log file "000000010000000000000006" from archive LOG: recovery stopping before commit of transaction 1898, time 2016-04-21 14:49:16.635744+08 LOG: redo done at 0/6000398 LOG: last completed transaction was at log time 2016-04-21 14:49:13.786388+08 cp: cannot stat ‘/data/pg_archive/00000002.history': No such file or directory LOG: selected new timeline ID: 2 cp: cannot stat ‘/data/pg_archive/00000001.history': No such file or directory LOG: archive recovery complete LOG: autovacuum launcher started LOG: database system is ready to accept connections #查看數(shù)據(jù) postgres=# select * from t1; id | create_time ----+--------------------- 1 | 2016-04-21 13:49:34 2 | 2016-04-21 13:49:48 3 | 2016-04-21 14:00:22 4 | 2016-04-21 14:00:25 5 | 2016-04-21 14:49:11 6 | 2016-04-21 14:49:14 (6 rows)
7.查看備份庫pg_xlog
-bash-4.2$ ll pg_xlog total 49160 -rw-------. 1 postgres postgres 16777216 Apr 21 15:00 000000010000000000000005 -rw-------. 1 postgres postgres 16777216 Apr 21 15:00 000000010000000000000006 -rw-------. 1 postgres postgres 16777216 Apr 21 15:00 000000020000000000000006 -rw-------. 1 postgres postgres 64 Apr 21 15:00 00000002.history drwx------. 2 postgres postgres 4096 Apr 21 15:00 archive_status -bash-4.2$ cat pg_xlog/00000002.history 1 000000010000000000000006 before 2016-04-21 14:49:16.635744+08
從pg_xlog我們看到設(shè)置好recovery.conf文件后，啟動數(shù)據(jù)庫，將會產(chǎn)生新的timeline，id=2，而且會生成一個新的history文件00000002.history，里面記錄的是新時間線2從什么時間哪個時間線什么原因分出來的，該文件可能含有多行記錄。
另外，恢復(fù)的默認行為是沿著與當前基本備份相同的時間線恢復(fù)。如果你想恢復(fù)到某些時間線，你需要指定的recovery.conf目標時間線recovery_target_timeline，不能恢復(fù)到早于基本備份分支的時間點。
注意:如果恢復(fù)過一次，并重新設(shè)置recovery_target_time，重新啟動觸發(fā)恢復(fù)，并不會基于時間線1進行恢復(fù)，而是基于時間線2進行恢復(fù)的，但是此時間線上在/data/pg_archive/并沒有時間線為2的歸檔日志，因此會報錯。
補充：postgres修改歸檔模式
步驟一：
修改postgresql的配置文件(postgresql.conf)
wal_level=hot_standby
archive_mode =on
archive_command ='DATE=`date +%Y%m%d`;DIR="/home/postgres/arch/$DATE";(test -d $DIR || mkdir -p $DIR) cp %p $DIR/%f'
ps：%p 是指相對路徑 %f是指文件名
步驟二：創(chuàng)建歸檔路徑
mkdir -p /home/postgres/arch chown -R postgres:postgres /home/postgres/arch
步驟三：重啟數(shù)據(jù)庫
步驟四：驗證歸檔是否正常
postgres=# checkpoint;
CHECKPOINT
postgres=# select pg_switch_xlog();
pg_switch_xlog
----------------
1/760000E8
(1 row)
postgres@ubuntu:~$ cd /home/postgres/data/data_1999/arch/
postgres@ubuntu:~/data/data_1999/arch$ ls
20150603
postgres@ubuntu:~/data/data_1999/arch$ cd 20150603/
postgres@ubuntu:~/data/data_1999/arch/20150603$ ls
000000010000000100000074 000000010000000100000075 000000010000000100000076
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。
