PostgreSQLDBA(28)-Backup&Recovery#1(基本操作)
PostgreSQL的Backup和Recovery操作相对比较简单,通过简单的几个命令和配置即可实现备份和恢复.
下面通过例子简单说明PG备份和恢复的基本操作.
场景
1.执行备份
2.创建数据表并执行插入
3.删除数据
4.使用备份恢复到删除数据前的状态
参数配置
修改配置文件postgresql.conf
10年积累的网站建设、成都网站制作经验，可以快速应对客户对网站的新想法和需求。提供各种问题对应的解决方案。让选择我们的客户得到更好、更有力的网络服务。我虽然不认识你，你也不认识我。但先做网站设计后付款的网站建设流程，更有霍山免费网站建设让你可以放心的选择与我们合作。
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
网页名称：PostgreSQLDBA(28)-Backup&Recovery#1(基本操作)
转载来于：http://ybzwz.com/article/jhigpo.html
