概述

备份重于一切，今天主要介绍PG的五种备份方式，仅供参考。

ps:前四种重点掌握

一、pg_dump备份及pg_restore恢复

1、语法

可以在本地及远程进行备份，只需要表的读权限即可备份。pg_dump创建的备份是一致的，在pg_dump运行时数据库产生快照，不阻塞数据库的DML操作，但是会阻塞需要排他锁的操作，如alter table等。特别注意的是，pg_dump一次只能备份一个单独的数据库，且不能备份角色和表空间信息（因为这些信息是cluster-wide，而不是在某个数据库中（per-database））。

使用pg_dump的自定义转储格式。. 如果PostgreSQL所在的系统上安装了zlib压缩库，自定义转储格式将在写出数据到输出文件时对其压缩。这将产生和使用gzip时差不多大小的转储文件，但是这种方式的一个优势是其中的表可以被有选择地恢复。

下面的命令使用自定义转储格式来转储一个数据库：
pg_dump -Fc dbname > filename
自定义格式的转储不是psql的脚本，只能通过pg_restore恢复，例如：
pg_restore -d dbname filename

pg_dump [OPTION]... [DBNAME]
pg_restore [OPTION]... [FILE]

2、常见用法

--导出指定库（不含create database语句）
pg_dump -h xx.xx..142 -U hwb -p 55432 -d pas_db > /data/pgbackup/pas_db_bak202012.sql
#导出指定库（包含create database语句）
pg_dump -h xx.xx..142 -U hwb -p 55432 -C pas_db > /data/pgbackup/pas_db_bak202012.sql

--导出指定库，结果以自定义压缩格式输出
pg_dump -Fc -h xx.xx..142 -U hwb -p 55432 -d pas_db > /data/pgbackup/pas_db_bak202012.dump
--备份表
pg_dump -h xx.xx..142 -U hwb -p 55432 -d pas_db -t t1 -t t2 > /data/pgbackup/t.sql
--备份某个模式所有表(schema名为hwb)
pg_dump  -h xx.xx.142 -U hwb -p 55432 -d pas_db -t 'hwb.*' > /data/pgbackup/schema_202012.sql
--备份某个模式所有表，排除一张表
pg_dump  -h xx.xx.142 -U hwb -p 55432 -d pas_db -t 'hwb.*' -T hwb.t1  > /data/pgbackup/schema_t1_202012.sql

--还原（导入postgres库，自动创建schema和表）
pg_restore -h xx.xx.142  -U postgres -p 55432 -d postgres  -v  /data/pgbackup/pas_db_bak202012.dump
--single-transaction表示整个恢复过程是一个事务，要么成功要么回滚
--恢复后需运行ANALYZE收集统计信息
psql -h xx.xx.142  -U postgres -p 55432 -d postgres --single-transaction <  /data/pgbackup/pas_db_bak202012.sql

二、pg_dumpall备份与恢复

pg_dump只备份数据库集群中的某个数据库的数据，它不会导出角色和表空间相关的信息。pg_dumpall则可以导出整个数据库集群中所有的数据库中的数据，同时也会导出角色、用户和表空间的定义信息。

执行pg_dumpall需要超级用户权限。

1、语法

pg_dumpall [OPTION]...

2、常用用法

--导出所有database(当应用需要OID字段的话(比如在外键约束中用到)，需添加-o选项)
pg_dumpall  -v > /data/pgbackup/db_all.dmp

--只转储全局对象(角色和表空间)，而不转储数据库
pg_dumpall -g -v > /data/pgbackup/role_tbs.sql

---r（roles-only）只转储角色，不转储数据库或表空间
pg_dumpall -r -v > /data/pgbackup/role.sql

-s(schema-only)只输出对象定义(模式)，不输出数据
pg_dumpall -s -v > /data/pgbackup/schema.sql


--恢复（执行这个命令的时候连接到哪个数据库无关紧要，因为pg_dumpall 创建的脚本会包含创建和连接数据库的命令）
--恢复时需删对应数据库，否则如果数据库存在对应的表会自动插入新的数据
psql postgres -f db_all.dmp 

三、COPY备份与恢复

COPY在PostgreSQL表和文件之间交换数据。 COPY TO把一个表的所有内容都拷贝到一个文件，而COPY FROM从一个文件里拷贝数据到一个表里(把数据附加到表中已经存在的内容里)。 COPY TO还能拷贝SELECT查询的结果。

如果声明了一个字段列表，COPY将只在文件和表之间拷贝已声明字段的数据。 如果表中有任何不在字段列表里的字段，那么COPY FROM将为那些字段插入缺省值。

带文件名的COPY指示PostgreSQL服务器直接从文件中读写数据。 如果声明了文件名，那么服务器必须可以访问该文件，而且文件名必须从服务器的角度声明。 如果使用了PROGRAM选项，则服务器会从指定的这个程序进行输入或是写入该程序作为输出。 如果使用了STDIN 或STDOUT选项，那么数据将通过客户端和服务器之间的连接来传输。

注意：copy命令必须在plsql命令行执行，执行用户必须为superuser，普通用户进行执行，需要在copy前面加入 “\”，即 \copy。

COPY只能用于表，不能用于视图，不过可以用于COPY (SELECT * FROM viewname) TO ...

1、语法

--导出
COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { 'filename' | PROGRAM 'command' | STDOUT }
    [ [ WITH ] ( option [, ...] ) ]

--导入（如果导出的时候，指定了header属性，那么在导入的时候，也需要指定）
COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]

copy to的导出速度非常之快，经测试10W的数据量只需要3秒左右的时间。

COPY FROM能够识别下列特殊反斜杠字符：

2、常见用法

--服务端导出，导出到数据库所在服务器
copy t2 to '/data/pgbackup/t2.csv' with csv;
--导出指定属性
copy t2(name) to '/data/pgbackup/t2_name.csv' with csv;
copy (select * from t2) to '/data/pgbackup/t2_sel.csv' with csv;

--客户端导出，导出到psql命令所在服务器
\copy t2 to '/tmp/t2.dmp'  或者psql -c "copy t2 to stdout" > /tmp/t2.dmp

--如果导出的字段，有integer[]类型，直接导出，再导入的话，会有问题，解决办法是需要在导出的时候，进行处理
\COPY ( select coalesce(integer_array, '{}')::integer[] as integer_array from table ) TO '/tmp/data.csv' with csv header;

--使用escape或unicode模式输入特殊字符(例如TAB做分隔符)
\copy aa from '/tmp/aa.csv' with (delimiter U&'\0009')
\copy aa from '/tmp/aa.csv' with (delimiter E'\t')

四、pg_basebackup备份与恢复

1、基础备份

--postgresql.conf
# - Archiving -
wal_level = replica
archive_mode = on             # enables archiving; off, on, or always
archive_command = 'test ! -f  /data/pgarch/%f && cp %p  /data/pgarch/%f;find /data/pgarch/ -type f -mtime +30 -exec rm -f {} \;'


--创建REPLICATION角色
CREATE ROLE replica login replication encrypted password 'replica@1234';

--配置pg_hba.conf，允许远程流式备份
echo "host  replication replica  0.0.0.0/0   md5" >> pg_hba.conf

--模拟数据
create database pas_db with owner=hwb ENCODING='UTF8' TABLESPACE=pas_data connection limit=-1;
\c pas_db postgres
select pg_switch_wal();
create table t4(id int);
select pg_switch_wal();
insert into t4 values(1),(2),(3),(4);
select pg_switch_wal();
select current_timestamp;
-- 2020-12-14 16:13:23.10133+08

select pg_switch_wal();
insert into t4 values(5),(6),(7),(8);

--远程使用pg_basebackup备份
systemctl stop postgresql
rm -rf /data/pgdata/*
#-Fp表示以plain格式数据，-Xs表示以stream方式包含所需的WAL文件，-P表示显示进度，-R表示为replication写配置信息。
#备份完成，使用-R选项，在data目录下自动生成standby.signal“信号”文件（可手工使用touch命令生成）以及更新了postgresql.auto.conf文件
#postgresql.auto.conf中写入了主库的连接信息（可手工添加primary_conninfo信息）。
pg_basebackup --progress -D /data/pgdata -h xx.142 -p 55432 -U replica --password  -Fp -Xs -P -R

2、恢复

--在data目录下创建一个空文件：
touch recovery.signal

--修改 postgresq.conf 文件把archive相关参数全部注释掉并增加两行：
restore_command = 'cp /data/pgarch/%f  %p'
recovery_target_time = '2020-12-14 16:13:23.10133+08'

a.恢复到最新：
restore_command =  'cp /data/pgarch/%f  %p'
recovery_target_timeline = 'latest'

b.恢复到指定的时间点：
restore_command =  'cp /data/pgarch/%f  %p'
recovery_target_time = '2020-12-14 16:13:23.10133+08'

c.创建还原点：
SELECT pg_create_restore_point('restore_point1');

d.恢复到还原点：
restore_command =  'cp /data/pgarch/%f  %p'
recovery_target_name ='restore_point1'


--启动数据库进行恢复
systemctl restart postgresql

五、PG快照备份与恢复

PostgreSQL有一个导出和导入事务快照的功能，这个功能在9.2版本开始支持，允许事务共享它当时的snapshot给其他的事务使用。
SET TRANSACTION SNAPSHOT命令允许新的事务使用与一个现有事务相同的快照运行。已经存 在的事务必须已经把它的快照用pg_export_snapshot函数导出。该函数会返回一个快照标识符，SET TRANSACTION SNAPSHOT需要被给定一个快照标识符来指定要导入的快照。
需要注意的是：只有事务是SERIALIZABLE以及 repeatable read时，DEFERRABLE 事务属性才会有效。

PostGreSQL采用“快照”方式来实现MVCC。具体地说，这意味着每一个事务中的查询仅能看到：

1)该事务启动之前已经提交的事务所作出的数据更改。

2)当前事务中该查询之前的查询所作出的更改。

下面基于事务隔离级别repeatable read进行测试

1、建表

create table test (id int); 
insert into test values (1),(2);
--开启五个会话进行测试

2、session1:

begin TRANSACTION ISOLATION LEVEL repeatable read;  
SELECT pg_export_snapshot(); --00000004-0000047B-1
insert into test values (3);
SELECT pg_export_snapshot();  --00000004-0000047B-2
select * from txid_current();
select * from txid_current_snapshot();  

3、session2(插入一条新数据并提交)：

insert into test values (4);

4、session3(能查看到会话2插入的数据):

select * from test;

5、session4 (导入s1的第一个snapshot, 因此看不到s2提交的数据) :

begin TRANSACTION ISOLATION LEVEL repeatable read; 
SET TRANSACTION SNAPSHOT '00000004-0000047B-1';
select * from test;  
select * from txid_current(); 
select * from txid_current_snapshot();

6、session5 (导入s1的第二个snapshot, 因此看不到s2提交的数据, 同时验证了看不到s1修改过的数据):

begin TRANSACTION ISOLATION LEVEL REPEATABLE READ;  
SET TRANSACTION SNAPSHOT '00000004-0000047B-2';
select * from test; 
select * from txid_current();  
select * from txid_current_snapshot(); 

7、session1(提交)：

8、session4 (s1提交后, 这个snapshot还存在, 只要还有导入了这个snapshot的事务存在着) :

select * from test; 

9、session5 (s1提交后, 这个snapshot还存在, 只要还有导入了这个snapshot的事务存在着)

篇幅有限，基于时间点恢复的内容后面单独介绍吧，感兴趣的朋友可以关注下！