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)