📣📣📣
哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10余年DBA及大数据工作经验
一位上进心十足的【大数据领域博主】！😜😜😜
中国DBA联盟(ACDU)成员，目前服务于工业互联网
擅长主流Oracle、MySQL、PG、高斯及Greenplum运维开发，
备份恢复，安装迁移，性能优化、故障应急处理等。
📣📣📣

PG的备份大致可以分为两类，逻辑备份和物理备份，各有优略，本文做了详细的说明

📣 1.备份恢复概述

备份大致可以分为两类：逻辑备份和物理备份。
虽然两者都有优点和缺点，但逻辑备份的一个缺点是它们可能非常耗时。
特别是，备份大型数据库可能需要很长时间，从备份数据恢复数据库可能需要更长的时间。
另一方面，可以更快地进行和恢复物理备份，使其成为实际系统中非常重要和有用的功能。

📣 2.逻辑方式

✨ 2.1 pg_dump

pg_dump 将表结构及数据以SQL语句的形式导出到文件中，
恢复数据时，将导出的文件作为输入，
执行其中的SQL语句，即可恢复数据。

pg_dump 能够对正在使用的PostgreSQL数据库进行备份，
并且不影响正常业务的读写。

pg_dump备份示例：
1 导出单表数据
pg_dump -h 127.0.0.1 -U admin -p 5432 -W db -t t1 –inserts > bak.sql
2 导出多个表数据
pg_dump -h 127.0.0.1 -U admin -p 5432 -W db -t t1 -t t2 –inserts > bak.sql
3 导出整个数据库
pg_dump -h 127.0.0.1 -U admin -p 5432 -W db –inserts > bak.sql
4 只导出表结构，不导出数据
pg_dump -h 127.0.0.1 -U admin -p 5432 -W db -s > bak.sql
5 只导出数据，不导出表结构
pg_dump -h 127.0.0.1 -U admin -p 5432 -W db –inserts -a > bak.sql
6.压缩导出
pg_dump --dbname=jmedb --format=custom --file=/pgbak/jmedb.dmp

pg_dump后如何恢复？
1.文本文件
drop database jmedb;
create database jmedb;
psql --file=jmedb.sql
psql --dbname=db2 --file=jmedb.sql

2.二进制文件
pg_restore --username=postgres
–host=192.168.1.54
–port=35432
–dbname=jmedb
/pgbak/jmedb.dmp

✨ 2.2 pg_dumpall

相对于pg_dump只能备份单个库，
pg_dumpall可以备份整个postgresql实例中所有的数据，
包括角色和表空间定义。

示例如下：
pg_dumpall -h 127.0.0.1
-U admin -p 5432
-W –inserts > bak.sql

1.pg_dump支持指定所要备份的对象：
可以单独备份表、schema或者database；
2.pg_dumpall仅支持导出全库数据。
3.pg_dump可以将数据备份为SQL文本文件格式，
也支持备份为用户自定义的压缩格式或者TAR包格式。

✨ 2.3 COPY 与 \copy

copy：适合单表或带条件sql结果导出
（可导出为csv或txt格式）

COPY 与 \copy
1.导出
COPY { table_name [ ( column_name [, …] ) ] | ( query ) }
TO { ‘filename’ | PROGRAM ‘command’ | STDOUT }
[ [ WITH ] ( option [, …] ) ]
2.导入
COPY table_name [ ( column_name [, …] ) ]
FROM { ‘filename’ | PROGRAM ‘command’ | STDIN }
[ [ WITH ] ( option [, …] ) ]
说明：
WITH csv：导入csv格式数据
HEADER：不导入第一行
encoding：指定编码

txt格式导出：
psql -ddb_name -Uuser_name -W
执行
COPY 表名 copy TO ‘/home/postgres/test.txt’
或者
\copy test_copy to ‘/home/postgres/test.txt’
导入txt文件
psql -ddb_name -Uuser_name -W
COPY 表名 copy FROM ‘/home/postgres/test.txt’
#或
\copy test_copy from ‘/home/postgres/test.txt’
备注：COPY是sql命令，
需要superuser权限，性能更好；
\copy是元命令，不需要superuser权限

导出csv文件
psql -ddb_name -Uuser_name -W
COPY products TO ‘/path/to/output.csv’ WITH csv;
导入csv文件
psql -ddb_name -Uuser_name -W
COPY products FROM ‘/path/to/input.csv’ WITH csv;
不导入第一行，指定编码为UTF-8
\copy tmp_3 FROM ‘/data/tmp_3.csv’ WITH csv HEADER encoding ‘UTF-8’;

可以导出指定的列
psql -ddb_name -Uuser_name -W
COPY products (name, price) TO ‘/path/to/output.csv’ WITH csv;
也可以配合查询语句
psql -dplatform -Ubom_rw -W
COPY (select * from temp_0524 where id=‘xxx’) TO ‘home/postgres/temp_0524.csv’ with csv header

📣 3.物理方式

✨ 3.1 冷备份

• 直接拷贝PostgreSQL 中用来存储数据的文件。
• 你可以用任何方式来进行通常的系统文件备份，比如：
– tar -cf backup.tar /usr/local/pgsql/data
• 为了得到一个可用的备份，数据库服务器必须关闭。
• 文件系统级别的备份只为完全备份，并且恢复整个数据集合实例。

✨ 3.2 pg_basebackup

pg_basebackup 则是一个用于制作数据库集群物理备份的工具，
它会生成一个完整的数据库集群副本，
包括所有数据文件、WAL 文件和其他必要文件。
这种备份方式更利于快速恢复，并支持流复制初始化从库。

基于事务的恢复案例：
插入3000条记录，但是，只找回其中的2000条
--全备任务
pg_basebackup -Fp -P -v -D pgdata_bak
--发起插入的数据
postgres=$ create table test(id int,info text);
postgres=$ begin;
postgres=$ select txid_current();
 txid_current 
--------------
          839
postgres=$ insert into test select n,'test' from generate_series(1,2000) as n;
postgres=$ commit;
--再次插入数据
postgres=$ begin;
postgres=$ select txid_current();
 txid_current 
--------------
         840
postgres=$ insert into test select n,'test' from generate_series(1,1000) as n;
postgres=# commit;

--删除数据切日志
postgres=# delete from test;
postgres=# select pg_switch_wal();
 pg_switch_wal 
---------------
0/1007E250

--编辑恢复的文件
[postgres@centos79 ~]$ vi pgdata_bak/postgresql.conf

restore_command = 'cp /home/postgres/archive_dir/%f %p'
recovery_target_xid = '839'
port=5558

--生成文件
touch pgdata_bak/recovery.signal

--启动备份实例
[postgres@centos79 ~]$ pg_ctl -D pgdata_bak start

基于时间点的恢复案例
# Place archive logs under /mnt/server/archivedir directory.
restore_command = 'cp /mnt/server/archivedir/%f %p'
recovery_target_time = "2024-1-1 12:05 GMT"

$ touch /usr/local/pgsql/data/recovery.signal

全备脚本：pg_basebackup.sh
#!/bin/bash
#auth:cuckoo

DATE=$(date '+%Y%m%d')
sevendays_time=$(date -d -7days '+%Y%m%d')
pgpath=/opt/pgsql/bin/
port=15432
pguser=postgres
bkpath=/u01/pg_backup/basebackup
bktmp=$bkpath/backups-tmp

#START BACKUP
echo "START BACKUP..............."
rm -rf $bkpath/base_$sevendays_time.tar.gz                  
$pgpath/pg_basebackup -Ft -Pv -Xf -z -Z5 -p $port -U $pguser -D $bktmp
mv $bktmp/base.tar.gz $bkpath/base_$DATE.tar.gz
$pgpath/psql -p $port -U $pguser -c "select pg_switch_wal()"
echo "BACKUP  END"

✨ 3.3 pg_rman

pg_rman是一个开源的PG备份软件，第三方，
本质是一个文件的拷贝，必须和Server安装在一起
支持基于时间的恢复，支持在线的全备和增备
同时还可以支持WAL的备份过期删除

如何备份？
–初始化，创建一个目录，用于存放备份
pg_rman init -B /rmanbk
全备脚本
pg_rman backup --backup-mode=full -B /rmanbk
pg_rman validate -B /rmanbk
增备脚本
jem_db=# select * from test1;
pg_rman backup --backup-mode=incremental -B /rmanbk

–如何恢复呢？
pg_ctl stop
pg_rman restore -B /rmanbk
pg_ctl start

📣 4.总结

通过备份和恢复来保护数据，避免数据丢失，在发生灾难或人为误操作的情况下，能够进行恢复是DBA的日常最重要的工作。不仅要保证能够成功备份，还要保证备份数据能够恢复