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

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