Postgresql pg_rman安装使用

简单介绍一下：
pg_rman可以在线备份整个数据库，支持增量备份和备份压缩，支持存储快照。
pg_rman使用的是pg_start_backup(), copy, pg_stop_backup()的备份模式。
pg_rman跑的不是流复制协议，而是文件拷贝，所以pg_rman必须和数据库节点跑在一起。
如果在standby节点跑pg_rman，pg_rman则需要通过网络连接到主节点执行pg_start_backup和pg_stop_backup。

下载安装：

wget https://github.com/ossc-db/pg_rman/releases/download/V1.3.11/pg_rman-1.3.11-pg12.tar.gz
tar -zxvf pg_rman-1.3.11-pg12.tar.gz 
cd soft/pg_rman-1.3.11-pg12
make && make install

使用pg_rman的先决条件：

postgres=# show log_destination ;  
 log_destination   
-----------------  
 csvlog  
(1 row)  
  
postgres=# SHOW log_directory ;  
 log_directory  
---------------  
log  
(1 row)  
  
postgres=# SHOW archive_command ;  
              archive_command  
--------------------------------------------  
 cp %p /pgarchive/%f  
(1 row)  

初始化备份目录：

#初始化需要两个参数，分别为备份目标目录，以及数据库的$PGDATA
pg_rman init -B /pgbackup -D /opt/pgdata/pg_root
INFO: ARCLOG_PATH is set to '/pgarchive'
INFO: SRVLOG_PATH is set to '/opt/pgdata/pg_root/log'

查看配置文件：

cat pg_rman.ini 
ARCLOG_PATH='/pgarchive'
SRVLOG_PATH='/opt/pgdata/pg_root/log'

cat system_identifier 
SYSTEM_IDENTIFIER='6940149485915408008'
#可见和控制文件的系统标识号一致
pg_controldata | grep system
Database system identifier:           6940149485915408008

备份：

#全量备份
pg_rman backup -B /pgbackup -D /opt/pgdata/pg_root -b full -s -Z -C --keep-data-days=10 --keep-arclog-files=15 --keep-arclog-days=10 --keep-srvlog-files=10 --keep-srvlog-days=15 -h 127.0.0.1 -p 1921 -U postgres -d postgres 
INFO: copying database files
INFO: copying archived WAL files
INFO: copying server log files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
INFO: start deleting old archived WAL files from ARCLOG_PATH (keep files = 15, keep days = 10)
INFO: the threshold timestamp calculated by keep days is "2021-03-08 00:00:00"
INFO: start deleting old server files from SRVLOG_PATH (keep files = 10, keep days = 15)
INFO: the threshold timestamp calculated by keep days is "2021-03-03 00:00:00"
INFO: start deleting old backup (keep after = 2021-03-08 00:00:00)
INFO: does not include the backup just taken

#验证
pg_rman validate -B /pgbackup/

#增量备份
pg_rman backup -B /pgbackup -D /opt/pgdata/pg_root -b incremental -s -Z -C --keep-data-days=10 --keep-arclog-files=15 --keep-arclog-days=10 --keep-srvlog-files=10 --keep-srvlog-days=15 -h 127.0.0.1 -p 1921 -U postgres -d postgres          
INFO: copying database files
INFO: copying archived WAL files
INFO: copying server log files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
INFO: start deleting old archived WAL files from ARCLOG_PATH (keep files = 15, keep days = 10)
INFO: the threshold timestamp calculated by keep days is "2021-03-08 00:00:00"
INFO: start deleting old server files from SRVLOG_PATH (keep files = 10, keep days = 15)
INFO: the threshold timestamp calculated by keep days is "2021-03-03 00:00:00"
INFO: start deleting old backup (keep after = 2021-03-08 00:00:00)
INFO: does not include the backup just taken
INFO: backup "2021-03-18 19:37:45" should be kept
DETAIL: This is taken after "2021-03-08 00:00:00".

#再次验证
pg_rman validate -B /pgbackup/

#查看备份信息

INFO: validate: "2021-03-18 19:59:19" backup, archive log files and server log files by CRC
INFO: backup "2021-03-18 19:59:19" is valid
postgres@nano-dev-nano-python-test-database-02-> pg_rman show detail -B /pgbackup/
======================================================================================================================
 StartTime           EndTime              Mode    Data  ArcLog  SrvLog   Total  Compressed  CurTLI  ParentTLI  Status 
======================================================================================================================
2021-03-18 19:59:19  2021-03-18 19:59:21  INCR    17kB    33MB      0B    33kB        true       1          0  OK
2021-03-18 19:37:45  2021-03-18 19:37:48  FULL    50MB    33MB    71kB  6689kB        true       1          0  OK

相关option:

init   #初始化备份目录
backup   #在线备份
restore   #恢复选项
show  #显示备份集历史，detail选项会显示每个备份集更多的信息
validate  #验证备份文件，如果不验证，备份无法用于恢复和增量备份
delete  #删除备份文件
purge  #从备份目录删除备份

为了减轻主节点的压力，当然我们更加喜欢在备节点做备份，以下附上参数解释

pg_rman backup \    
-b full \                          # 全量备份   
-B /pgbackup \       # 备份目录  
-D /opt/pgdata/pg_root \   # 备库的$PGDATA  
-s \                               # 备份pg_log  
-Z \                               # 压缩  
--keep-data-generations=3 \        # 保留3个全量备份，删除不需要的全量备份  
--keep-data-days=10 \              # 保证能恢复到10天内的任意时间点，删除不需要的  
--keep-arclog-files=30 \           # 保留最近30个归档文件  
--keep-arclog-days=20 \            # 保留20天内的归档文件  
--keep-srvlog-files=10 \           # 保留最近10个日志文件(pg_log)  
--keep-srvlog-days=20 \            # 保留20天内的日志文件(pg_log)  
--standby-host=127.0.0.1 \         # 如何连接standby  
--standby-port=1921 \              # 如何连接standby  
-h 10.4.9.166 \                     # 如何连接primary  
-p 1921 \                          # 如何连接primary  
-U postgres \                      # 如何连接primary, standby(超级用户)  
-d postgres                        # 如何连接primary, standby(database name) 

备节点备份：

#备节点做一次全备
pg_rman backup -b full  -B /pgbackup  -D /opt/pgdata/pg_root -s -Z  --keep-data-generations=3  --keep-data-days=10 --keep-arclog-files=30  --keep-arclog-days=20  --keep-srvlog-files=10 --keep-srvlog-days=20 --standby-host=127.0.0.1  --standby-port=1921  -h 10.4.9.166 -p 1921  -U postgres  -d postgres    
INFO: copying database files
INFO: copying archived WAL files
INFO: copying server log files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
INFO: start deleting old archived WAL files from ARCLOG_PATH (keep files = 30, keep days = 20)
INFO: the threshold timestamp calculated by keep days is "2021-02-26 00:00:00"
INFO: start deleting old server files from SRVLOG_PATH (keep files = 10, keep days = 20)
INFO: the threshold timestamp calculated by keep days is "2021-02-26 00:00:00"
INFO: start deleting old backup (keep generations = 3 AND keep after = 2021-03-08 00:00:00)
INFO: does not include the backup just taken
WARNING: backup "2021-03-18 20:25:44" is not taken into account
DETAIL: This is not a valid backup.
WARNING: backup "2021-03-18 20:24:48" is not taken into account
DETAIL: This is not a valid backup.
WARNING: backup "2021-03-18 20:23:52" is not taken into account
DETAIL: This is not a valid backup.
WARNING: backup "2021-03-18 20:22:47" is not taken into account
DETAIL: This is not a valid backup.
pg_rman validate -B /pgbackup/
INFO: validate: "2021-03-18 20:27:12" backup, archive log files and server log files by CRC
INFO: backup "2021-03-18 20:27:12" is valid

#验证备份
pg_rman validate -B /pgbackup/
INFO: validate: "2021-03-18 20:27:12" backup, archive log files and server log files by CRC
INFO: backup "2021-03-18 20:27:12" is valid

#查看备份
pg_rman show detail -B /pgbackup/
======================================================================================================================
 StartTime           EndTime              Mode    Data  ArcLog  SrvLog   Total  Compressed  CurTLI  ParentTLI  Status 
======================================================================================================================
2021-03-18 20:27:12  2021-03-18 20:27:17  FULL    49MB      0B    18kB  5749kB        true       1          0  OK

参考：
https://github.com/ossc-db/pg_rman/releases
http://ossc-db.github.io/pg_rman/index.html
https://github.com/digoal/blog/blob/master/201608/20160826_01.md
https://github.com/digoal/blog/blob/master/201608/20160829_02.md