postgresql备份恢复管理工具pg_probackup

数据库笔记 2020-12-24

1861

简介

pg_probackup是一个管理PostgreSQL数据库集群备份和恢复的工具。它的设计目的是对PostgreSQL实例执行定期的完整和增量的页面级备份，以便在发生故障时恢复服务器，与oracle rman类似，pg_probackup支持postgresql-9.5及更高版本。

pg_probackup优点：

1.增量备份：支持三种不同模式(PAGE,DELTA,PTRACK)

2.增量合成：将增量备份合并到全量备份形成新的全量而不进行实际的数据恢复

3.数据有效性自动/手动检测(开启data-checksum)

4.备份验证：基于VALIDATE指令，可指定备份集模拟数据恢复

5.历史过期数据处理

6.并行化：支持backup、restore、merge、delete、validate、checkdb

7.备份数据压缩保存

8.Deduplication：重复数据不备份(比如_vm或者_fsm)

9.支持SSH远程备份恢复

10.支持从standby备份

11.数据存储在外部目录：与PGDATA保持独立

pg_probackup 备份模式(backup_mode)：

全量备份：

FULL模式：全量备份会将数据库集下所有的数据文件进行备份

增量备份：

DELTA模式：在该模式下， pg_probackup 会扫描所有的数据目录文件，然后将上一次备份后发生改变的数据页进行拷贝备份。这种模式下增量备份的IO消耗基本等同于全量备份。

PAGE模式：在该模式下， pg_probackup仅会扫描备份上一次备份结束时刻之后的所有WAL归档日志，读取日志中数据页的变更。这种模式下的增量备份必须配置持续归档（wal_level > minimal 、archive_mode = on/always、archive_command 使用 pg_probackup进行archive-push 归档）。

PTRACK模式：在该模式下，pg_probackup 会实时跟踪源备份实例端数据页的变化，对于距上一次备份后发生更新的数据页，将其记录在 bitmap 中，以此来加快增量备份的时间。该模式下不需要关注WAL日志归档的设置，增量备份时间相对于DELTA更快，但是由于需要实时跟踪发生变化的数据页，所以对源端数据库服务器是有一定的资源消耗的。仅支持postgre pro standard和postgre pro Enterprise

增量备份一般选择PAGE模式

安装部署：

本次演示基于centos7.3 postgresql-9.6.20单机环境

参考文档：

https://postgrespro.com/docs/postgrespro/9.6/app-pgprobackup#pbk-restore

https://github.com/postgrespro/pg_probackup

RPM方式安装pg_probackup：

rpm -ivh https://repo.postgrespro.ru/pg_probackup/keys/pg_probackup-repo-centos.noarch.rpm
yum install pg_probackup-9.6
yum install pg_probackup-9.6-debuginfo

配置操作：

初始化备份目录

pg_probackup init -B backup_dir

[postgres@localhost ~]$ pg_probackup-9.6 init -B postgresql/backup/
INFO: Backup catalog '/postgresql/backup' successfully inited

创建备份实例

pg_probackup add-instance -B backup_dir -D data_dir --instance instance_name [remote_options]

[postgres@localhost ~]$ pg_probackup-9.6 add-instance -B postgresql/backup/ -D postgresql/data/ --instance pgbak
INFO: Instance 'pgbak' successfully inited

备份恢复用户配置：

for postgresql-9.6

创建备份恢复用户backup

psql>

BEGIN;
CREATE ROLE backup WITH LOGIN;
GRANT USAGE ON SCHEMA pg_catalog TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.current_setting(text) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_is_in_recovery() TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_start_backup(text, boolean, boolean) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_stop_backup(boolean) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_create_restore_point(text) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_switch_xlog() TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_last_xlog_replay_location() TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.txid_current() TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.txid_current_snapshot() TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.txid_snapshot_xmax(txid_snapshot) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_control_checkpoint() TO backup;
COMMIT;

配置后修改pg_hba.conf

配置持续归档：

vim postgresql/data/postgresql.conf
archive_command = 'pg_probackup archive-push -B postgresql/backup --instance pgbak --wal-file-name=%f'

如果不想使用pg_probackup指令，需要将WAL归档路径指向backup_dir/wal/instance_name目录下

全量备份

su - postgres

pg_probackup backup -B backup_dir --instance instance_name -b backup_mode

[postgres@localhost ~]$ pg_probackup-9.6 backup -B postgresql/backup/ --instance pgbak -b FULL -U backup -W
INFO: Backup start, pg_probackup version: 2.4.4, instance: pgbak, backup ID: QKFRRR, backup mode: FULL, wal mode: ARCHIVE, remote: false, compress-algorithm: none, compress-level: 1
Password for user backup: 
WARNING: This PostgreSQL instance was initialized without data block checksums. pg_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option '--data-checksums'.
INFO: pg_probackup archive-push WAL file: 00000001000000000000001E, threads: 1/1, batch: 1/1, compression: none
INFO: pg_probackup archive-push completed successfully, pushed: 1, skipped: 0, time elapsed: 18ms
INFO: pg_probackup archive-push WAL file: 00000001000000000000001F, threads: 1/1, batch: 1/1, compression: none
INFO: pg_probackup archive-push completed successfully, pushed: 1, skipped: 0, time elapsed: 12ms
INFO: Wait for WAL segment postgresql/backup/wal/pgbak/00000001000000000000001F to be archived
INFO: PGDATA size: 29MB
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 0
LOG:  restore point "pg_probackup, backup_id QKFRRR" created at 0/20000090
STATEMENT:  SELECT pg_catalog.pg_create_restore_point($1)
INFO: pg_probackup archive-push WAL file: 00000001000000000000001F.00000028.backup, threads: 1/1, batch: 1/1, compression: none
INFO: pg_probackup archive-push completed successfully, pushed: 1, skipped: 0, time elapsed: 1ms
INFO: pg_probackup archive-push WAL file: 000000010000000000000020, threads: 1/1, batch: 1/1, compression: none
INFO: pg_probackup archive-push completed successfully, pushed: 1, skipped: 0, time elapsed: 29ms
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executed
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 1s
INFO: Validating backup QKFRRR
INFO: Backup QKFRRR data files are valid
INFO: Backup QKFRRR resident size: 29MB
INFO: Backup QKFRRR completed

若initdb时没有开启data-checksum，则会出现：

WARNING: This PostgreSQL instance was initialized without data block checksums. pg_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option '--data-checksums'.

启用数据校验将会在备份期间，读取每一个page时计算其checksum信息并与page header中的checksum值比对。开启数据校验将保证备份期间没有损坏的页面。

增量备份

pg_probackup backup -B backup_dir --instance instance_name -b backup_mode

[postgres@localhost ~]$ pg_probackup-9.6 backup -B postgresql/backup/ --instance pgbak -b PAGE -U backup -W
INFO: Backup start, pg_probackup version: 2.4.4, instance: pgbak, backup ID: QKFX3V, backup mode: PAGE, wal mode: ARCHIVE, remote: false, compress-algorithm: none, compress-level: 1
Password for user backup: 
WARNING: This PostgreSQL instance was initialized without data block checksums. pg_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option '--data-checksums'.
INFO: pg_probackup archive-push WAL file: 000000010000000000000021, threads: 1/1, batch: 1/1, compression: none
INFO: pg_probackup archive-push completed successfully, pushed: 1, skipped: 0, time elapsed: 34ms
INFO: pg_probackup archive-push WAL file: 000000010000000000000022, threads: 1/1, batch: 1/1, compression: none
INFO: pg_probackup archive-push completed successfully, pushed: 1, skipped: 0, time elapsed: 32ms
INFO: Wait for WAL segment postgresql/backup/wal/pgbak/000000010000000000000022 to be archived
INFO: Parent backup: QKFRRR
INFO: PGDATA size: 29MB
INFO: Extracting pagemap of changed blocks
INFO: Pagemap successfully extracted, time elapsed: 0 sec
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 0
LOG:  restore point "pg_probackup, backup_id QKFX3V" created at 0/23000090
STATEMENT:  SELECT pg_catalog.pg_create_restore_point($1)
INFO: pg_probackup archive-push WAL file: 000000010000000000000022.00000028.backup, threads: 1/1, batch: 1/1, compression: none
INFO: pg_probackup archive-push completed successfully, pushed: 1, skipped: 0, time elapsed: 0ms
INFO: pg_probackup archive-push WAL file: 000000010000000000000023, threads: 1/1, batch: 1/1, compression: none
INFO: pg_probackup archive-push completed successfully, pushed: 1, skipped: 0, time elapsed: 31ms
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executed
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 0
INFO: Validating backup QKFX3V
INFO: Backup QKFX3V data files are valid
INFO: Backup QKFX3V resident size: 148kB
INFO: Backup QKFX3V completed

查看备份

pg_probackup show -B backup_dir[--help] [--instance instance_name [-i backup_id | --archive]] [--format=plain|json]

[postgres@localhost ~]$ pg_probackup-9.6 show -B postgresql/backup/


BACKUP INSTANCE 'pgbak'
===================================================================================================================================
 Instance  Version  ID      Recovery Time           Mode  WAL Mode  TLI  Time   Data   WAL  Zratio  Start LSN   Stop LSN    Status 
===================================================================================================================================
 pgbak     9.6      QKFX3V  2020-11-27 13:40:47+08  PAGE  ARCHIVE   1/1    6s  148kB  16MB    1.00  0/22000028  0/230000B8  OK     
 pgbak     9.6      QKFRRR  2020-11-27 11:45:31+08  FULL  ARCHIVE   1/0    7s   29MB  16MB    1.00  0/1F000028  0/200000B8  OK

查看备份明细

pg_probackup show -B backup_dir --instance instance_name -i backup_id

[postgres@localhost ~]$ pg_probackup-9.6 show -B postgresql/backup/ -i QKFRRR --instance pgbak
#Configuration
backup-mode = FULL
stream = false
compress-alg = none
compress-level = 1
from-replica = false


#Compatibility
block-size = 8192
xlog-block-size = 8192
checksum-version = 0
program-version = 2.4.4
server-version = 9.6


#Result backup info
timelineid = 1
start-lsn = 0/1F000028
stop-lsn = 0/200000B8
start-time = '2020-11-27 11:45:27+08'
end-time = '2020-11-27 11:45:34+08'
recovery-xid = 1786
recovery-time = '2020-11-27 11:45:31+08'
data-bytes = 30378907
wal-bytes = 16777216
uncompressed-bytes = 30354405
pgdata-bytes = 30354182
status = OK
primary_conninfo = 'user=backup port=5432 sslmode=disable sslcompression=1 krbsrvname=postgres'
content-crc = 687105944

查看归档明细

pg_probackup show -B backup_dir [--instance instance_name] --archive --format=json

[postgres@localhost ~]$ pg_probackup-9.6 show -B /postgresql/backup/ --instance pgbak --archive --format=json
[
    {
        "instance": "pgbak",
        "timelines": [
            {
                "tli": 1,
                "parent-tli": 0,
                "switchpoint": "0/0",
                "min-segno": "000000010000000000000001",
                "max-segno": "000000010000000000000023",
                "n-segments": 35,
                "size": 587202560,
                "zratio": 1.00,
                "closest-backup-id": "",
                "status": "OK",
                "lost-segments": [],
                "backups": [
                    {
                        "id": "QKFX3V",
                        "parent-backup-id": "QKFRRR",
                        "backup-mode": "PAGE",
                        "wal": "ARCHIVE",
                        "compress-alg": "none",
                        "compress-level": 1,
                        "from-replica": "false",
                        "block-size": 8192,
                        "xlog-block-size": 8192,
                        "checksum-version": 0,
                        "program-version": "2.4.4",
                        "server-version": "9.6",
                        "current-tli": 1,
                        "parent-tli": 1,
                        "start-lsn": "0/22000028",
                        "stop-lsn": "0/230000B8",
                        "start-time": "2020-11-27 13:40:43+08",
                        "end-time": "2020-11-27 13:40:49+08",
                        "recovery-xid": 1788,
                        "recovery-time": "2020-11-27 13:40:47+08",
                        "data-bytes": 151885,
                        "wal-bytes": 16777216,
                        "uncompressed-bytes": 151775,
                        "pgdata-bytes": 30354182,
                        "primary_conninfo": "user=backup port=5432 sslmode=disable sslcompression=1 krbsrvname=postgres",
                        "status": "OK",
                        "content-crc": 3642602883
                    },
                    {
                        "id": "QKFRRR",
                        "backup-mode": "FULL",
                        "wal": "ARCHIVE",
                        "compress-alg": "none",
                        "compress-level": 1,
                        "from-replica": "false",
                        "block-size": 8192,
                        "xlog-block-size": 8192,
                        "checksum-version": 0,
                        "program-version": "2.4.4",
                        "server-version": "9.6",
                        "current-tli": 1,
                        "parent-tli": 0,
                        "start-lsn": "0/1F000028",
                        "stop-lsn": "0/200000B8",
                        "start-time": "2020-11-27 11:45:27+08",
                        "end-time": "2020-11-27 11:45:34+08",
                        "recovery-xid": 1786,
                        "recovery-time": "2020-11-27 11:45:31+08",
                        "data-bytes": 30378907,
                        "wal-bytes": 16777216,
                        "uncompressed-bytes": 30354405,
                        "pgdata-bytes": 30354182,
                        "primary_conninfo": "user=backup port=5432 sslmode=disable sslcompression=1 krbsrvname=postgres",
                        "status": "OK",
                        "content-crc": 687105944
                    }
                ]
            }
        ]
    }
]

增量合成(merge)

为了防止过多的增量备份集占用过多的备份空间，同时为了方便管理，可以将指定的增量备份和全量备份合并为一个新的全量备份

pg_probackup merge -B backup_dir --instance instance_name -i backup_id

backup_id为需要合成的增量备份id

[postgres@localhost ~]$ pg_probackup-9.6 merge -B /postgresql/backup/ --instance pgbak -i QKFX3V
INFO: Merge started
INFO: Merging backup QKFX3V with parent chain
INFO: Validate parent chain for backup QKFX3V
INFO: Validating backup QKFRRR
INFO: Backup QKFRRR data files are valid
INFO: Validating backup QKFX3V
INFO: Backup QKFX3V data files are valid
INFO: Start merging backup files
INFO: Backup files are successfully merged, time elapsed: 0
INFO: Delete: QKFX3V 2020-11-27 13:40:47+08
INFO: Rename merged full backup QKFRRR to QKFX3V
INFO: Validating backup QKFX3V
INFO: Backup QKFX3V data files are valid
INFO: Merge of backup QKFX3V completed

查看备份列表：

[postgres@localhost ~]$ pg_probackup-9.6 show -B /postgresql/backup/


BACKUP INSTANCE 'pgbak'
==================================================================================================================================
 Instance  Version  ID      Recovery Time           Mode  WAL Mode  TLI  Time  Data   WAL  Zratio  Start LSN   Stop LSN    Status 
==================================================================================================================================
 pgbak     9.6      QKFX3V  2020-11-27 13:40:47+08  FULL  ARCHIVE   1/0     0  29MB  16MB    1.00  0/22000028  0/230000B8  OK

检测备份集可用性

pg_probackup validate -B backup_dir --instance instance_name -i backupid [recovery target option]

例如检查是否能将备份集应用WAL恢复到最新的状态

[postgres@localhost ~]$ pg_probackup-9.6 validate -B /postgresql/backup/ --instance pgbak --recovery-target=latest
INFO: Validate backups of the instance 'pgbak'
INFO: Validating backup QKFX3V
INFO: Backup QKFX3V data files are valid
INFO: Backup QKFX3V WAL segments are valid
INFO: All backups are valid

如示：通过检测备份集QKFX3V可以应用WAL恢复到最新状态

恢复

pg_probackup restore -B backup_dir --instance instance_name[--help] [-D data_dir] [-i backup_id] [-j num_threads] [--progress] [-T OLDDIR=NEWDIR] [--external-mapping=OLDDIR=NEWDIR] [--skip-external-dirs] [-R | --restore-as-replica] [--no-validate] [--skip-block-validation] [--force] [--no-sync] [--restore-command=cmdline] [--primary-conninfo=primary_conninfo] [-S | --primary-slot-name=slot_name] [recovery_target_options] [logging_options] [remote_options] [partial_restore_options] [remote_wal_archive_options]

恢复前保证指定的数据目录为空，否则会报错：

[postgres@localhost ~]$ pg_probackup-9.6 restore -B /postgresql/backup/ --instance pgbak -D /postgresql/data/ recovery-target=latest
ERROR: Restore destination is not empty: "/postgresql/data"

清空数据目录后再次恢复：

pg_probackup-9.6 restore -B /postgresql/backup/ --instance pgbak -D /postgresql/data/ recovery-target=latest
INFO: Validating backup QKFX3V
INFO: Backup QKFX3V data files are valid
INFO: Backup QKFX3V WAL segments are valid
INFO: Backup QKFX3V is valid.
INFO: Restoring the database from backup at 2020-11-27 13:40:43+08
INFO: Start restoring backup files. PGDATA size: 29MB
INFO: Backup files are restored. Transfered bytes: 29MB, time elapsed: 0
INFO: Restore incremental ratio (less is better): 100% (29MB/29MB)
INFO: Syncing restored files to disk
INFO: Restored backup files are synced, time elapsed: 1s
INFO: Restore of backup QKFX3V completed.

恢复后正常起库即可：

pg_ctl -D /postgresql/data start

文章转载自数据库笔记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

postgresql备份恢复管理工具pg_probackup

评论