PostgreSQL的Backup和Recovery操作相對比較簡單,透過簡單的幾個命令和配置即可實現備份和恢復.
下面透過例子簡單說明PG備份和恢復的基本操作.
**場景**
1.執行備份
2.建立資料表並執行插入
3.刪除資料
4.使用備份恢復到刪除資料前的狀態

**引數配置**
*修改配置檔案postgresql.conf*
```
archive_mode = on 
archive_command = '/home/xdb/archive.sh'
wal_level = replica
max_wal_size = 4GB
min_wal_size = 1024MB
```
其中archive.sh指令碼如下(該指令碼參照自德哥部落格)
```
[xdb@localhost ~]$ cat archive.sh 
#!/bin/bash

export LANG=en_US.utf8
export PGHOME=/appdb/xdb/pg11.2
export DATE=`date +"%Y%m%d"`
export PATH=$PGHOME/bin:$PATH:.

BASEDIR="/data/archivelog"

if [ ! -d $BASEDIR/$DATE ]; then
  mkdir -p $BASEDIR/$DATE
  if [ ! -d $BASEDIR/$DATE ]; then
echo "error mkdir -p $BASEDIR/$DATE!"
exit 1
  fi
fi

cp $1 $BASEDIR/$DATE/$2
if [ $? -eq 0 ]; then
  exit 0
else
  echo -e "cp $1 $BASEDIR/$DATE/$2 error!"
  exit 1
fi

echo -e "backup failed!"
exit 1
```
該指令碼把WAL log複製到$BASEDIR/$DATE目錄下

同時修改日誌輸出
```
log_destination = 'csvlog'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d.log'
```

**驗證配置**
*啟動資料庫*
```
[xdb@localhost testdb]$ pg_ctl start
waiting for server to start....2019-03-11 14:21:08.591 CST [21847] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2019-03-11 14:21:08.591 CST [21847] LOG:  listening on IPv6 address "::", port 5432
2019-03-11 14:21:08.609 CST [21847] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-03-11 14:21:08.635 CST [21847] LOG:  redirecting log output to logging collector process
2019-03-11 14:21:08.635 CST [21847] HINT:  Future log output will appear in directory "pg_log".
 done
server started
[xdb@localhost testdb]$ psql -d testdb
psql (11.2)
Type "help" for help.

testdb=# show wal_level;
 wal_level 
-----------
 replica
(1 row)

testdb=# show archive_command;
      archive_command       
----------------------------
 /home/xdb/archive.sh %p %f
(1 row)
```
*切換日誌*
切換前的目錄資訊
```
[xdb@localhost ~]$ ll $PGDATA/pg_wal
total 49152
-rw-------. 1 xdb xdb 16777216 Mar 11 14:21 00000001000000000000000D
-rw-------. 1 xdb xdb 16777216 Mar 11 14:20 00000001000000000000000E
-rw-------. 1 xdb xdb 16777216 Mar 11 14:20 00000001000000000000000F
drwx------. 2 xdb xdb        6 Mar 11 14:21 archive_status
[xdb@localhost ~]$ ll $PGDATA/pg_wal/archive_status 
total 0
[xdb@localhost ~]$ ll /data/archivelog/20190311/
total 0
```
執行切換
```
testdb=# select pg_switch_wal();
 pg_switch_wal 
---------------
 0/D0000E8
(1 row)

testdb=# select pg_switch_wal();
 pg_switch_wal 
---------------
 0/E000120
(1 row)

```
切換後的目錄資訊
```
[xdb@localhost ~]$  ll $PGDATA/pg_wal
total 49152
-rw-------. 1 xdb xdb 16777216 Mar 11 14:26 00000001000000000000000E
-rw-------. 1 xdb xdb 16777216 Mar 11 14:26 00000001000000000000000F
-rw-------. 1 xdb xdb 16777216 Mar 11 14:26 000000010000000000000010
drwx------. 2 xdb xdb       43 Mar 11 14:26 archive_status
[xdb@localhost ~]$  ll $PGDATA/pg_wal/archive_status 
total 0
-rw-------. 1 xdb xdb 0 Mar 11 14:26 00000001000000000000000E.done
[xdb@localhost ~]$ 
[xdb@localhost ~]$ ll /data/archivelog/20190311/
total 32768
-rw-------. 1 xdb xdb 16777216 Mar 11 14:26 00000001000000000000000D
-rw-------. 1 xdb xdb 16777216 Mar 11 14:26 00000001000000000000000E
```

**執行備份**
使用PG提供的pg_basebackup命令即可實現.
```
[xdb@localhost ~]$ pg_basebackup -D /data/backup/20190311-1/ -l 20190311-1 -v
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/10000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_21910"
pg_basebackup: write-ahead log end point: 0/10000130
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed
[xdb@localhost ~]$ 
```
-D指定備份儲存的目錄
-l指定LABEL
-v顯示診斷資訊
執行完畢,備份資訊可透過檢視/data/backup/20190311-1/backup_label檔案獲得
```
[xdb@localhost ~]$ cat /data/backup/20190311-1/backup_label
START WAL LOCATION: 0/10000028 (file 000000010000000000000010)
CHECKPOINT LOCATION: 0/10000060
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2019-03-11 14:31:42 CST
LABEL: 20190311-1
START TIMELINE: 1
```

**模擬PITR**
*插入資料*
```
testdb=# create table tbl(id int);
CREATE TABLE
testdb=# insert into tbl select generate_series(1,1000000);
INSERT 0 1000000
testdb=# create table tbl2(id int);
CREATE TABLE
testdb=# insert into tbl2 select generate_series(1,1000000);
INSERT 0 1000000
testdb=# 
```
檢視歸檔日誌
```
[xdb@localhost ~]$ ll $PGDATA/pg_wal
total 163848
-rw-------. 1 xdb xdb 16777216 Mar 11 14:31 00000001000000000000000F
-rw-------. 1 xdb xdb 16777216 Mar 11 14:31 000000010000000000000010
-rw-------. 1 xdb xdb      325 Mar 11 14:31 000000010000000000000010.00000028.backup
-rw-------. 1 xdb xdb 16777216 Mar 11 14:34 000000010000000000000011
-rw-------. 1 xdb xdb 16777216 Mar 11 14:34 000000010000000000000012
-rw-------. 1 xdb xdb 16777216 Mar 11 14:34 000000010000000000000013
-rw-------. 1 xdb xdb 16777216 Mar 11 14:34 000000010000000000000014
-rw-------. 1 xdb xdb 16777216 Mar 11 14:34 000000010000000000000015
-rw-------. 1 xdb xdb 16777216 Mar 11 14:34 000000010000000000000016
-rw-------. 1 xdb xdb 16777216 Mar 11 14:34 000000010000000000000017
-rw-------. 1 xdb xdb 16777216 Mar 11 14:35 000000010000000000000018
drwx------. 2 xdb xdb     4096 Mar 11 14:34 archive_status
[xdb@localhost ~]$ ll $PGDATA/pg_wal/archive_status 
total 0
-rw-------. 1 xdb xdb 0 Mar 11 14:31 00000001000000000000000F.done
-rw-------. 1 xdb xdb 0 Mar 11 14:31 000000010000000000000010.00000028.backup.done
-rw-------. 1 xdb xdb 0 Mar 11 14:31 000000010000000000000010.done
-rw-------. 1 xdb xdb 0 Mar 11 14:34 000000010000000000000011.done
-rw-------. 1 xdb xdb 0 Mar 11 14:34 000000010000000000000012.done
-rw-------. 1 xdb xdb 0 Mar 11 14:34 000000010000000000000013.done
-rw-------. 1 xdb xdb 0 Mar 11 14:34 000000010000000000000014.done
-rw-------. 1 xdb xdb 0 Mar 11 14:34 000000010000000000000015.done
-rw-------. 1 xdb xdb 0 Mar 11 14:34 000000010000000000000016.done
-rw-------. 1 xdb xdb 0 Mar 11 14:34 000000010000000000000017.done
[xdb@localhost ~]$ 
[xdb@localhost ~]$ ll /data/archivelog/20190311/
total 180228
-rw-------. 1 xdb xdb 16777216 Mar 11 14:26 00000001000000000000000D
-rw-------. 1 xdb xdb 16777216 Mar 11 14:26 00000001000000000000000E
-rw-------. 1 xdb xdb 16777216 Mar 11 14:31 00000001000000000000000F
-rw-------. 1 xdb xdb 16777216 Mar 11 14:31 000000010000000000000010
-rw-------. 1 xdb xdb      325 Mar 11 14:31 000000010000000000000010.00000028.backup
-rw-------. 1 xdb xdb 16777216 Mar 11 14:34 000000010000000000000011
-rw-------. 1 xdb xdb 16777216 Mar 11 14:34 000000010000000000000012
-rw-------. 1 xdb xdb 16777216 Mar 11 14:34 000000010000000000000013
-rw-------. 1 xdb xdb 16777216 Mar 11 14:34 000000010000000000000014
-rw-------. 1 xdb xdb 16777216 Mar 11 14:34 000000010000000000000015
-rw-------. 1 xdb xdb 16777216 Mar 11 14:34 000000010000000000000016
-rw-------. 1 xdb xdb 16777216 Mar 11 14:34 000000010000000000000017
[xdb@localhost ~]$ 
```
記錄當前時間
```
testdb=# select now();
              now              
-------------------------------
 2019-03-11 14:39:37.403147+08
(1 row)

```
*刪除資料*
```
testdb=# select now();
              now              
-------------------------------
 2019-03-11 14:40:07.353201+08
(1 row)

testdb=# truncate table tbl;
TRUNCATE TABLE
testdb=# truncate table tbl2;
TRUNCATE TABLE
testdb=# 
```

*恢復*
關閉資料庫,從base backup中恢復資料
```
[xdb@localhost testdb]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
[xdb@localhost testdb]$ ls
backup_label.old  log           pg_ident.conf  pg_notify     pg_stat      pg_twophase  postgresql.auto.conf
base              pg_commit_ts  pg_log         pg_replslot   pg_stat_tmp  PG_VERSION   postgresql.conf
current_logfiles  pg_dynshmem   pg_logical     pg_serial     pg_subtrans  pg_wal       postmaster.opts
global            pg_hba.conf   pg_multixact   pg_snapshots  pg_tblspc    pg_xact
[xdb@localhost testdb]$ 
[xdb@localhost testdb]$ rm -rf *
[xdb@localhost testdb]$ cp -R /data/backup/20190311-1/* ./
[xdb@localhost testdb]$ 
```
建立recovery.conf檔案,指定恢復時間點
```
[xdb@localhost testdb]$ vim recovery.conf
[xdb@localhost testdb]$ cat recovery.conf 
restore_command = 'cp /data/archivelog/20190311/%f "%p"'
recovery_target_time='03-11-2019 14:40:00'
```
執行恢復並驗證
```
[xdb@localhost testdb]$ pg_ctl start
waiting for server to start....2019-03-11 14:43:35.034 CST [21986] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2019-03-11 14:43:35.034 CST [21986] LOG:  listening on IPv6 address "::", port 5432
2019-03-11 14:43:35.037 CST [21986] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-03-11 14:43:35.116 CST [21986] LOG:  redirecting log output to logging collector process
2019-03-11 14:43:35.116 CST [21986] HINT:  Future log output will appear in directory "pg_log".
 done
server started
```
檢視日誌輸出
```
2019-03-11 14:43:35.116 CST,,,21986,,5c860397.55e2,1,,2019-03-11 14:43:35 CST,,0,LOG,00000,"ending log output to stderr",,"Future log output will go to log destination ""csvlog"".",,,,,,,""
2019-03-11 14:43:35.120 CST,,,21988,,5c860397.55e4,1,,2019-03-11 14:43:35 CST,,0,LOG,00000,"database system was interrupted; last known up at 2019-03-11 14:31:42 CST",,,,,,,,,""
2019-03-11 14:43:35.130 CST,,,21988,,5c860397.55e4,2,,2019-03-11 14:43:35 CST,,0,LOG,00000,"starting point-in-time recovery to 2019-03-11 14:40:00+08",,,,,,,,,""
2019-03-11 14:43:35.225 CST,,,21988,,5c860397.55e4,3,,2019-03-11 14:43:35 CST,,0,LOG,00000,"restored log file ""000000010000000000000010"" from archive",,,,,,,,,""
2019-03-11 14:43:35.305 CST,,,21988,,5c860397.55e4,4,,2019-03-11 14:43:35 CST,1/0,0,LOG,00000,"redo starts at 0/10000028",,,,,,,,,""
2019-03-11 14:43:35.306 CST,,,21988,,5c860397.55e4,5,,2019-03-11 14:43:35 CST,1/0,0,LOG,00000,"consistent recovery state reached at 0/10000130",,,,,,,,,""
2019-03-11 14:43:35.307 CST,,,21986,,5c860397.55e2,2,,2019-03-11 14:43:35 CST,,0,LOG,00000,"database system is ready to accept read only connections",,,,,,,,,""
2019-03-11 14:43:35.363 CST,,,21988,,5c860397.55e4,6,,2019-03-11 14:43:35 CST,1/0,0,LOG,00000,"restored log file ""000000010000000000000011"" from archive",,,,,,,,,""
2019-03-11 14:43:35.972 CST,,,21988,,5c860397.55e4,7,,2019-03-11 14:43:35 CST,1/0,0,LOG,00000,"restored log file ""000000010000000000000012"" from archive",,,,,,,,,""
2019-03-11 14:43:36.566 CST,,,21988,,5c860397.55e4,8,,2019-03-11 14:43:35 CST,1/0,0,LOG,00000,"restored log file ""000000010000000000000013"" from archive",,,,,,,,,""
2019-03-11 14:43:37.281 CST,,,21988,,5c860397.55e4,9,,2019-03-11 14:43:35 CST,1/0,0,LOG,00000,"restored log file ""000000010000000000000014"" from archive",,,,,,,,,""
2019-03-11 14:43:37.854 CST,,,21988,,5c860397.55e4,10,,2019-03-11 14:43:35 CST,1/0,0,LOG,00000,"restored log file ""000000010000000000000015"" from archive",,,,,,,,,""
2019-03-11 14:43:38.432 CST,,,21988,,5c860397.55e4,11,,2019-03-11 14:43:35 CST,1/0,0,LOG,00000,"restored log file ""000000010000000000000016"" from archive",,,,,,,,,""
2019-03-11 14:43:39.167 CST,,,21988,,5c860397.55e4,12,,2019-03-11 14:43:35 CST,1/0,0,LOG,00000,"restored log file ""000000010000000000000017"" from archive",,,,,,,,,""
2019-03-11 14:43:39.942 CST,,,21988,,5c860397.55e4,13,,2019-03-11 14:43:35 CST,1/0,0,LOG,00000,"restored log file ""000000010000000000000018"" from archive",,,,,,,,,""
2019-03-11 14:43:40.315 CST,,,21988,,5c860397.55e4,14,,2019-03-11 14:43:35 CST,1/0,0,LOG,00000,"recovery stopping before commit of transaction 577, time 2019-03-11 14:40:13.662008+08",,,,,,,,,""
2019-03-11 14:43:40.315 CST,,,21988,,5c860397.55e4,15,,2019-03-11 14:43:35 CST,1/0,0,LOG,00000,"recovery has paused",,"Execute pg_wal_replay_resume() to continue.",,,,,,,""
```
日誌提示recovery has paused,連線資料庫,執行pg_wal_replay_resume()
```
testdb=# select pg_wal_replay_resume();
 pg_wal_replay_resume 
----------------------
 
(1 row)
```
日誌輸出如下
```
2019-03-11 14:47:44.741 CST,,,21988,,5c860397.55e4,16,,2019-03-11 14:43:35 CST,1/0,0,LOG,00000,"redo done at 0/18A8D8A0",,,,,,,,,""
2019-03-11 14:47:44.741 CST,,,21988,,5c860397.55e4,17,,2019-03-11 14:43:35 CST,1/0,0,LOG,00000,"last completed transaction was at log time 2019-03-11 14:35:10.895964+08",,,,,,,,,""
2019-03-11 14:47:44.744 CST,,,21988,,5c860397.55e4,18,,2019-03-11 14:43:35 CST,1/0,0,LOG,00000,"selected new timeline ID: 2",,,,,,,,,""
2019-03-11 14:47:44.993 CST,,,21988,,5c860397.55e4,19,,2019-03-11 14:43:35 CST,1/0,0,LOG,00000,"archive recovery complete",,,,,,,,,""
2019-03-11 14:47:46.109 CST,,,21986,,5c860397.55e2,3,,2019-03-11 14:43:35 CST,,0,LOG,00000,"database system is ready to accept connections",,,,,,,,,""

```
recovery.conf檔案改名為recovery.done
backup_label檔案改名為backup_label.old
```
[xdb@localhost ~]$ ls $PGDATA/recovery*
/data/pgsql/testdb/recovery.done
[xdb@localhost ~]$ ls $PGDATA/backup_label*
/data/pgsql/testdb/backup_label.old
```
*驗證資料*
```
testdb=# select count(*) from tbl;
  count  
---------
 1000000
(1 row)

testdb=# select count(*) from tbl2;
  count  
---------
 1000000
(1 row)
```

**參考資料**
[Base Backup & Point-in-Time Recovery](http://www.interdb.jp/pg/pgsql10.html)

PostgreSQL DBA(28) - Backup&Recovery#1(基本操作)

相關文章