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

2025-04-13 技术教程

PostgreSQL的Backup和Recovery操作相对比较简单,通过简单的几个命令和配置即可实现备份和恢复.
下面通过例子简单说明PG备份和恢复的基本操作.
场景
1.执行备份
2.创建数据表并执行插入
3.删除数据
4.使用备份恢复到删除数据前的状态

参数配置
修改配置文件postgresql.conf

archive_mode = on archive_command = '/home/xdb/archive.sh'wal_level = replicamax_wal_size = 4GBmin_wal_size = 1024MB

其中archive.sh脚本如下(该脚本参照自德哥博客)

[xdb@localhost ~]$ cat archive.sh #!/bin/bashexport LANG=en_US.utf8export PGHOME=/appdb/xdb/pg11.2export 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 ]; thenecho "error mkdir -p $BASEDIR/$DATE!"exit 1 fificp $1 $BASEDIR/$DATE/$2if [ $? -eq 0 ]; then exit 0else echo -e "cp $1 $BASEDIR/$DATE/$2 error!" exit 1fiecho -e "backup failed!"exit 1

该脚本把WAL log拷贝到$BASEDIR/$DATE目录下

同时修改日志输出

log_destination = 'csvlog'logging_collector = onlog_directory = 'pg_log'log_filename = 'postgresql-%Y-%m-%d.log'

验证配置
启动数据库

[xdb@localhost testdb]$ pg_ctl startwaiting for server to start....2019-03-11 14:21:08.591 CST [21847] LOG: listening on IPv4 address "0.0.0.0", port 54322019-03-11 14:21:08.591 CST [21847] LOG: listening on IPv6 address "::", port 54322019-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 process2019-03-11 14:21:08.635 CST [21847] HINT: Future log output will appear in directory "pg_log". doneserver started[xdb@localhost testdb]$ psql -d testdbpsql (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_waltotal 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 00000001000000000000000Fdrwx------. 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_waltotal 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 000000010000000000000010drwx------. 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 -vpg_basebackup: initiating base backup, waiting for checkpoint to completepg_basebackup: checkpoint completedpg_basebackup: write-ahead log start point: 0/10000028 on timeline 1pg_basebackup: starting background WAL receiverpg_basebackup: created temporary replication slot "pg_basebackup_21910"pg_basebackup: write-ahead log end point: 0/10000130pg_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_labelSTART WAL LOCATION: 0/10000028 (file 000000010000000000000010)CHECKPOINT LOCATION: 0/10000060BACKUP METHOD: streamedBACKUP FROM: masterSTART TIME: 2019-03-11 14:31:42 CSTLABEL: 20190311-1START TIMELINE: 1

模拟PITR
插入数据

testdb=# create table tbl(id int);CREATE TABLEtestdb=# insert into tbl select generate_series(1,1000000);INSERT 0 1000000testdb=# create table tbl2(id int);CREATE TABLEtestdb=# insert into tbl2 select generate_series(1,1000000);INSERT 0 1000000testdb=#

查看归档日志

[xdb@localhost ~]$ ll $PGDATA/pg_waltotal 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 000000010000000000000018drwx------. 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 TABLEtestdb=# truncate table tbl2;TRUNCATE TABLEtestdb=#

恢复
关闭数据库,从base backup中恢复数据

[xdb@localhost testdb]$ pg_ctl stopwaiting for server to shut down.... doneserver stopped[xdb@localhost testdb]$ lsbackup_label.old log pg_ident.conf pg_notify pg_stat pg_twophase postgresql.auto.confbase pg_commit_ts pg_log pg_replslot pg_stat_tmp PG_VERSION postgresql.confcurrent_logfiles pg_dynshmem pg_logical pg_serial pg_subtrans pg_wal postmaster.optsglobal 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 startwaiting for server to start....2019-03-11 14:43:35.034 CST [21986] LOG: listening on IPv4 address "0.0.0.0", port 54322019-03-11 14:43:35.034 CST [21986] LOG: listening on IPv6 address "::", port 54322019-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 process2019-03-11 14:43:35.116 CST [21986] HINT: Future log output will appear in directory "pg_log". doneserver 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