PG的备份还原
简介
Postgresql的备份分为三种：
SQL转储
文件系统级别备份（冷备份）
在线热备份（归档）
以下通过实例来讲解PostgreSQL的三种备份。
参考：https://blog.csdn.net/licheng6302/article/details/7567523
SQL转储即逻辑备份
pg_dump命令参考
只能备份单个数据库，不会导出角色和表空间相关的信息
-F c 备份为二进制格式,压缩存储.并且可被pg_restore用于精细还原
-F p 备份为文本,大库不推荐
pg_dump恢复命令参考
psql dbname -U username < bakfile
或
pg_restore
– pg_restore [option] ... [filename]
– pg_restore -d dbname bakfile
二进制格式的备份只能使用pg_restore来还原,可以指定还原的表,编辑TOC文件,定制还原的顺序,表, 索引等。
文本格式的备份还原, 直接使用用户连接到对应的数据库执行备份文本即可,例如psql dbname -f bak.sql
pg_dump实验
1，创建备份测试数据库
createdb pgback_test
2，连入数据库pg
psql pgback_test
3，创建测试表，插入数据
create table pg_test(a int);
insert into pg_test(a) values(1);
insert into pg_test(a) values(2);
4，查看数据
select * from pg_test;
5，创建备份目录
mkdir /postgres/backup
6，备份
pg_dump pgback_test > /postgres/backup/pgback_test.dmp
7，查看备份数据库备份文件
vim /postgres/backup/pgback_test.dmp
从上述信息我们可以发现该备份文件是以SQL为主的文本文件，里面存储了创建该数据库的对象语句即插入数据的语句，所以pg_dump是一种逻辑备份。
8，删除数据库pg
dropdb pgback_test
9，查看数据库是否已经删除
从上述的查询结果可以看出pgbackup备库已经被删除了。
10，创建新数据库（创建一个与之前名称不同的数据库，测试备份导入是否正常）
createdb pg
11，恢复数据
psql pb < /postgres/backup/pgback_test.dmp
12，查看数据是否回复
select * from pg_test;
至此，数据已成功恢复！
注：pg_dump可以对针对单表或者多表进行备份
如：pg_dump databasename –t tablename1 –t tablename2 >filename
pg_dumpall实验
pg_dump只能备份单个数据库，而且恢复的时候需要创建空数据库。pg_dumpall可以备份所有数据库，并且备份角色、表空间。
1，创建数据库
createdb pg1
createdb pg2
2，pg1中创建表并插入数据
psql pg1
create table tb1(a int);
insert into tb1 (a) values (1);
3，pg2中创建表并插入数据
psql pg2
create table tb2 (a int);
insert into tb2 (a) values(2);
4，备份数据库
pg_dumpall > /postgres/backup/pg_all.dmp
5，查看备份文件
6，删除数据库
dropdb pg1
dropdb pg2
6，恢复数据库
psql –f /postgres/pg_all.dmp postgres
可以指定任何数据库名，如果恢复到一个空的集群中，通常使用postgres数据库
7，查看数据库pg1是否恢复
psql pg1
select * from tb1;
8，查看数据库pg2是否恢复
psql pg2
select * from tb2;
至此，数据已成功恢复！
备份压缩与分割
压缩与分割的原理都是利用Linux的管线（PIPE）命令，不再进行试验。
压缩
备份：
pg_dump dbname | gzip > filename.gz
恢复：
gunzip -c filename.gz | psql dbname
分割
备份：
pg_dump dbname | split -b1m- filename
恢复：
cat filename* | psql dbname
文件系统级别备份（冷备份）
文件系统级别的备份是冷备份，需要停止数据库。
1，停止数据库
pg_ctl -D /postgres/data stop
2，备份数据库
tar -jcv -f /postgres/backup/filesystem.tar.bz2 /postgres/data/
3，删除/usr/local/pgsql/data/目录
rm -r /postgres/data/
4，解压备份文件到原目录
tar -jxv -f /postgres/backup/filesystem.tar.bz2 -C /
5，启动数据库
pg_ctl -D /postgres/data start
6，查看数据库pg1是否恢复
至此，数据已成功恢复！
在线热备份（归档）重点！！！
注：由于一般工作时备份都是在线备份，所以在线热备份是工作中最常用到的
##（一）备份
1，配置归档模式
配置归档需要编辑postgresql.conf文件，默认为与/postgres/data/目录下
vim /postgres/data/postgesql.conf
wal_level = hot_standby
archive_mode = on
archive_command = 'cp %p /postgres/arch/archived_log%f'
注：%p要被归档的日志文件的路径，%f是要被归档的日志文件的文件名
2，启动数据库
pg_ctl -D /postgres/data start
3，创建数据库arch_backup，备份测试数据库
createdb arch_backup
4，创建表并插入记录
psql arch_backup
create table tb (a int);
insert into tb (a) values(1);
5，创建备份
select pg_start_backup('baseline');
6，备份整个data目录
tar -jcv -f /postgres/backup/baseline.tar.bz2 /postgres/data/
7，停止备份
psql arch_backup
select pg_stop_backup();
8，插入新记录，然后切换日志，重复3次
insert into tb (a) values(2);
select pg_switch_wal();
insert into tb (a) values (3);
select pg_switch_wal();
insert into tb (a) values (4);
select pg_switch_wal();
9.6版本与10版本改变参考如下：
| 9.6 Function Name | 10 Function Name |
|---|---|
| pg_current_xlog_flush_location | pg_current_wal_flush_lsn |
| pg_current_xlog_insert_location | pg_current_wal_insert_lsn |
| pg_current_xlog_location | pg_current_wal_lsn |
| pg_is_xlog_replay_paused | pg_is_wal_replay_paused |
| pg_last_xlog_receive_location | pg_last_wal_receive_lsn |
| pg_last_xlog_replay_location | pg_last_wal_replay_lsn |
| pg_switch_xlog | pg_switch_wal |
| pg_xlog_location_diff | pg_wal_lsn_diff |
| pg_xlog_replay_pause | pg_wal_replay_pause |
| pg_xlog_replay_resume | pg_wal_replay_resume |
| pg_xlogfile_name | pg_walfile_name |
| pg_xlogfile_name_offset | pg_walfile_name_offset |
9，把/data/pg_wal/下的WAL日志文件复制到预设的归档目录下，保证产生的WAL日志都已归档。
cp /postgres/data/pg_wal /postgres/backup/ -Rp
注：9.6的版本为data/pg_xlog/下的xlog日志文件
（二）恢复
1，停止数据库
pg_ctl -D /postgres/data/ stop
2，删除/data/
rm -r /postgres/data/*
3，恢复备份
tar -jxv -f /postgres/backup/baseline.tar.bz2 -C /
4，清空/data/pg_xlog/目录下所有文件
rm -r /postgres/data/pg_wal/
5，恢复之前复制的pg_wal目录，若是postgres10版本以前为pg_xlog
mv /postgres/backup/pg_wal/ /postgres/data/
6，在/data/目录下创建postgresql.conf
vim /postgres/data/postgresql.conf
restore_command = 'cp /postgres/arch/archived_log%f "%p" '
7，启动数据库
pg_ctl -D /postgres/data/ start
一切正常的话数据库就会自动应用WAL日志进行恢复
8，查看数据库arch是否恢复
psql arch_backup
select * from tb;
至此，数据已经成功恢复！
以下为归档热备脚本参考
# postgres数据文件目录
export POSTDATA=/postgres/data
# postgres备份文件目录
export POSTBACKUPDATA=/postgres/backup
# postgres的免密文件
export PGPASSFILE=/home/postgres/.pgpass
# 备份文件见名称
backupname=`date +"%Y%m%d%H%M%S"`
# postgres的host地址
pghostname = 192.168.30.11
# postgres备份的用户名
username=postgres
echo "$backupname"
cd $POSTDATA
echo "$PWD"
psql -h pghostname -d postgres -U username -c "select pg_start_backup('hot_backup$backupname');"
tar -cvjpf $POSTBACKUPDATA/hot$backupname.tar.gz data
psql -h pghostname -d postgres -U username -c "select pg_stop_backup();select pg_switch_wal();"