利用pgbackrest实现postgresql备份还原

已于 2023-10-24 15:02:09 修改
阅读量743 收藏 2
点赞数 1
分类专栏： PostgreSQL 文章标签： postgresql
于 2023-10-24 14:23:19 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/dqk1982/article/details/134011522
版权

一、pgbackRest简介

pgBackRest旨在提供一个简单可靠，容易纵向扩展的PostgreSQL备份恢复系统。pgBackRest并不依赖像tar和rsync这样的传统备份工具，而是通过在内部实现所有备份功能，并使用自定义协议来与远程系统进行通信。 消除对tar和rsync的依赖可以更好地解决特定于数据库的备份问题。 自定义远程协议提供了更多的灵活性，并限制执行备份所需的连接类型，从而提高安全性。

相关网站

参考：http://v0.pigsty.cc/zh/blog/2018/02/07/pgbackrest2%E4%B8%AD%E6%96%87%E6%96%87%E6%A1%A3/
pgBackRest主页：http://pgbackrest.org
手册：https://pgbackrest.org/user-guide-rhel.html#introduction
pgBackRest Github主页：https://github.com/pgbackrest/pgbackrest

二、pgbackRest特征

1.并行备份和还原
2.本地或远程操作
3.完整，增量和差异备份
4.备份轮换和存档到期
5.备份完整性
6.页面校验和
7.备份恢复
8.流压缩和校验和
9.增量还原
10.并行，异步WAL Push＆Get
11.表空间和链接支持
12.S3、Azure和GCS兼容对象存储支持
13.加密
13.与PostgreSQL > = 8.3的兼容性

三、pgbackRest安装

基本实验环境

DB: PostgreSQL 14.7
pgbackrest：pgBackRest2.48
OS: CentOS 7.9

安装pgbackRest

root用户：
yum -y install libxml2 libxml2-devel lz4 lz4-devel make gcc openssl openssl-devel libyaml libyaml-devel
source /home/postgres/.bash_profile
unzip pgbackrest-release-2.48.zip
cd pgbackrest-release-2.48/src/
./configure
make
make install

安装完成后，可以查看pgbackrest相关命令

[postgres@du101 ~]$ pgbackrest
pgBackRest 2.48 - General help

Usage:
    pgbackrest [options] [command]

Commands:
    annotate        Add or modify backup annotation.
    archive-get     Get a WAL segment from the archive.
    archive-push    Push a WAL segment to the archive.
    backup          Backup a database cluster.
    check           Check the configuration.
    expire          Expire backups that exceed retention.
    help            Get help.
    info            Retrieve information about backups.
    repo-get        Get a file from a repository.
    repo-ls         List files in a repository.
    restore         Restore a database cluster.
    server          pgBackRest server.
    server-ping     Ping pgBackRest server.
    stanza-create   Create the required stanza data.
    stanza-delete   Delete a stanza.
    stanza-upgrade  Upgrade a stanza.
    start           Allow pgBackRest processes to run.
    stop            Stop pgBackRest processes from running.
    verify          Verify contents of the repository.
    version         Get version.

Use 'pgbackrest help [command]' for more information.
[postgres@du101 ~]$

四、pgbackRest配置

创建所需目录和配置文件

备份目录：
mkdir /data/backup
chown postgres:postgres /data/backup
chmod 750 /data/backup

配置文件目录：
mkdir -p /etc/pgbackrest
mkdir -p /etc/pgbackrest/conf.d
touch /etc/pgbackrest/pgbackrest.conf
chmod 640 /etc/pgbackrest/pgbackrest.conf
chown postgres:postgres /etc/pgbackrest/pgbackrest.conf

pgbackrest日志目录：
mkdir -p -m 770 /var/log/pgbackrest
chown postgres:postgres /var/log/pgbackrest

配值文件内容

[postgres@du101 ~]$ cat /etc/pgbackrest/pgbackrest.conf   
[pgsql]
pg1-path=/data/pgdata
pg1-port=1921
pg1-host-user=postgres
pg1-user=postgres

[global]
process-max=2
start-fast=y
repo1-path=/data/backup
repo1-retention-full=2

[global:archive-push]
compress-level=2
process-max=3

[global:archive-get]
process-max=2
[postgres@du101 ~]$

说明：
start-fast：By default pgBackRest will wait for the next regularly scheduled checkpoint before starting a backup. Depending on the checkpoint_timeout and checkpoint_segments settings in PostgreSQL it may be quite some time before a checkpoint completes and the backup can begin. Generally, it is best to set start-fast=y so that the backup starts immediately. This forces a checkpoint, but since backups are usually run once a day an additional checkpoint should not have a noticeable impact on performance. However, on very busy clusters it may be best to pass --start-fast on the command-line as needed.

process-max：pgBackRest offers parallel processing to improve performance of compression and transfer. The number of processes to be used for this feature is set using the --process-max option.

配置文件其他设置：

以下设置都是在配置文件中的global部分：

集中化备份
-pg-host在备份中有些配置并不是在本地PG数据库上部署的，而是进行集中化备份实现而设置的，所以配置也分管理节点和本地数据库节点。
1 本地数据库节点，不能带有pg-host 这是为远程控制节点而设置的，不是本地的配置
2 本地配置了错误的远程控制的配置后，就会出现上面的错误。
3 本地备份的账号尽力单独设置，在pg_hba.conf 对这个账号访问数据库尽力trust 。

连接数据库选项
–pg-host

设置pgbackrest 命令执行的位置
–pg1-host-cmd

设置配置文件的位置
–pg1-host-config

设置数据库的访问端口
–pg1-port

数据目录位置
–pg1-path

本地数据库socket端口
–pg-socket-path

数据库用户
–pg-user

buffer-size
buffer-size=16MiB
这部分值不能进行随意的设置值在最大不能超过16MB
最小在16kb,默认这部分主要使用在文件的copy 压缩，加密，以及其他的操作中使用的缓冲
Allowed values are 16KiB, 32KiB, 64KiB, 128KiB, 256KiB, 512KiB, 1MiB, 2MiB, 4MiB, 8MiB, and 16MiB

repo1-path
repo1-path=/pgbackrest/backup
这部分是备份文件的存储位置，这里我们统一认为是本地的文件位置

compress-type
compress-type=gz
备份文件的压缩中份不同的等级，其中支持的压缩 bz2 9 个等级 gz 6个等级 LZ4 默认等级 zst 3个等级，这里我们选择常用的gz 并且选择了3这个等级

config-path
config-path=/etc/pgbackrestpgbackrest.conf
配置文件位置

delta
备份和恢复文件采用checksums

**dry-run **
是否打开命令行中运行–dry-run的开关

io-timeout
这里由于pgbackrest里面的备份存储设备可以是S3 或其他设备，则这里需要针对IO读取设置超时的部分范围在 0.1 - 3600 秒

process-max
是否采用更多的线程来对数据库的工作进行操作，这里默认是1 ，不要设置太大，太大会在备份是影响数据库的运行。

repo1-retention-full
这个repo1-retention-full=2是必须要进行设置的，不设置的情况下会软件会给出警告，在设置后相关的repo1-retention-full 中的与之相关的其他的增量和差异备份，也会在过期后被清理出去。

pgbackrest日志设置：
log-path=/pgbackrest
log-level-console=info
log-level-file=debug

检查配置

数据库需要利用pgbackrest配置归档

vi /data/pgdata/postgresql.conf 
增加
archive_command = 'pgbackrest --stanza=pgsql archive-push %p'
archive_mode = on

重启数据库后生效

[postgres@du101 ~]$ pgbackrest --stanza=pgsql --log-level-console=info check
2023-10-24 08:58:33.782 P00   INFO: check command begin 2.48: --exec-id=2836-f2a672fb --log-level-console=info --pg1-path=/data/pgdata --pg1-port=1921 --pg1-user=postgres --repo1-path=/data/backup --stanza=pgsql
2023-10-24 08:58:34.482 P00   INFO: check repo1 configuration (primary)
2023-10-24 08:58:34.897 P00   INFO: check repo1 archive for WAL (primary)
2023-10-24 08:58:35.200 P00   INFO: WAL segment 000000010000000000000001 successfully archived to '/data/backup/archive/pgsql/14-1/0000000100000000/000000010000000000000001-ff4f9f7bcf35eb908754f7e54cbd0d9a072b8a4a.gz' on repo1
2023-10-24 08:58:35.201 P00   INFO: check command end: completed successfully (1419ms)
[postgres@du101 ~]$

创建 Stanza

[postgres@du101 ~]$ pgbackrest --stanza=pgsql --log-level-console=info stanza-create
2023-10-24 08:57:34.334 P00   INFO: stanza-create command begin 2.48: --exec-id=2807-77b81216 --log-level-console=info --pg1-path=/data/pgdata --pg1-port=1921 --pg1-user=postgres --repo1-path=/data/backup --stanza=pgsql
2023-10-24 08:57:35.288 P00   INFO: stanza-create for stanza 'pgsql' on repo1
2023-10-24 08:57:35.345 P00   INFO: stanza-create command end: completed successfully (1011ms)
[postgres@du101 ~]$ tree /data/backup/
/data/backup/
|-- archive
|   `-- pgsql
|       |-- archive.info
|       `-- archive.info.copy
`-- backup
    `-- pgsql
        |-- backup.info
        `-- backup.info.copy

4 directories, 4 files
[postgres@du101 ~]$

五、pgbackRest备份

备份是数据库集群的一致副本，可以对其进行还原，以便从硬件故障中恢复、执行时间点恢复或启动新的备用服务器。
全量备份: pgBackRest将整个数据库集群的内容拷贝到备份中。数据库集群的第一次备份始终是完全备份。pgBackRest始终能够直接恢复全量备份。为了保持一致性，完全备份不依赖于完全备份之外的任何文件。
差异备份: pgBackRest只复制自上次全量备份以来发生变化的数据库集群文件。pgBackRest通过复制所选差异备份中的所有文件和先前完整备份中适当的未更改文件来恢复差异备份。差异备份的优点是它比完全备份需要更少的磁盘空间，但是，要恢复差异备份，差异备份和完全备份必须都是有效的。
增量备份: pgBackRest只复制自上次备份(可以是另一次增量备份、差异备份或完全备份)以来发生变化的数据库集群文件。由于增量备份只包括自上次备份以来更改的文件，因此它们通常比完整备份或差异备份小得多。与差异备份一样，增量备份依赖于其他备份才能有效地恢复增量备份。由于增量备份只包括上次备份以来的那些文件，因此，要执行增量备份的恢复，必须对返回到先前差异备份、先前差异备份和先前完全备份的所有先前增量备份都有效。如果不存在差异备份，则所有先前的增量备份都将返回到先前的完整备份(必须存在)，并且完整备份本身必须有效才能恢复增量备份。

全量备份

[postgres@du101 ~]$ psql
psql (14.7)
Type "help" for help.

postgres=# create table t(id int);
CREATE TABLE
postgres=# insert into t select generate_series(1,100000);   
INSERT 0 100000
postgres=# checkpoint;
CHECKPOINT
postgres=# \q
[postgres@du101 ~]$ 


[postgres@du101 ~]$ pgbackrest --stanza=pgsql --log-level-console=info --type=full backup
2023-10-24 08:59:15.046 P00   INFO: backup command begin 2.48: --exec-id=2841-44c88dc2 --log-level-console=info --pg1-path=/data/pgdata --pg1-port=1921 --pg1-user=postgres --process-max=2 --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql --start-fast --type=full
2023-10-24 08:59:15.969 P00   INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2023-10-24 08:59:16.471 P00   INFO: backup start archive = 000000010000000000000003, lsn = 0/3000028
2023-10-24 08:59:16.471 P00   INFO: check archive for prior segment 000000010000000000000002
WARN: exclude special file '/data/pgdata/.s.PGSQL.1921' from backup
2023-10-24 08:59:22.315 P00   INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2023-10-24 08:59:22.722 P00   INFO: backup stop archive = 000000010000000000000003, lsn = 0/3000138
2023-10-24 08:59:22.731 P00   INFO: check archive for segment(s) 000000010000000000000003:000000010000000000000003
2023-10-24 08:59:22.797 P00   INFO: new backup label = 20231024-085915F
2023-10-24 08:59:22.873 P00   INFO: full backup size = 28.7MB, file total = 957
2023-10-24 08:59:22.873 P00   INFO: backup command end: completed successfully (7828ms)
2023-10-24 08:59:22.873 P00   INFO: expire command begin 2.48: --exec-id=2841-44c88dc2 --log-level-console=info --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql
2023-10-24 08:59:22.883 P00   INFO: expire command end: completed successfully (10ms)
[postgres@du101 ~]$

[postgres@du101 ~]$ ll /data/backup/backup/pgsql/
total 8.0K
drwxr-x--- 3 postgres postgres   72 Oct 24 08:59 20231024-085915F
drwxr-x--- 3 postgres postgres   18 Oct 24 08:59 backup.history
-rw-r----- 1 postgres postgres 1.1K Oct 24 08:59 backup.info
-rw-r----- 1 postgres postgres 1.1K Oct 24 08:59 backup.info.copy
lrwxrwxrwx 1 postgres postgres   16 Oct 24 08:59 latest -> 20231024-085915F
[postgres@du101 ~]$

差异备份

[postgres@du101 ~]$ psql -c "insert into t select generate_series(1,100);checkpoint;"
CHECKPOINT
[postgres@du101 ~]$ psql -c "select count(*) from t;"                                
 count  
--------
 100100
(1 row)

[postgres@du101 ~]$ 
[postgres@du101 ~]$ pgbackrest --stanza=pgsql --log-level-console=info --type=diff backup
2023-10-24 09:01:21.166 P00   INFO: backup command begin 2.48: --exec-id=2876-e5c4d427 --log-level-console=info --pg1-path=/data/pgdata --pg1-port=1921 --pg1-user=postgres --process-max=2 --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql --start-fast --type=diff
2023-10-24 09:01:21.965 P00   INFO: last backup label = 20231024-085915F, version = 2.48
2023-10-24 09:01:21.966 P00   INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2023-10-24 09:01:23.144 P00   INFO: backup start archive = 000000010000000000000005, lsn = 0/5000028
2023-10-24 09:01:23.144 P00   INFO: check archive for prior segment 000000010000000000000004
WARN: exclude special file '/data/pgdata/.s.PGSQL.1921' from backup
2023-10-24 09:01:24.818 P00   INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2023-10-24 09:01:25.020 P00   INFO: backup stop archive = 000000010000000000000005, lsn = 0/5000138
2023-10-24 09:01:25.026 P00   INFO: check archive for segment(s) 000000010000000000000005:000000010000000000000005
2023-10-24 09:01:25.061 P00   INFO: new backup label = 20231024-085915F_20231024-090121D
2023-10-24 09:01:25.136 P00   INFO: diff backup size = 3.8MB, file total = 958
2023-10-24 09:01:25.136 P00   INFO: backup command end: completed successfully (3971ms)
2023-10-24 09:01:25.136 P00   INFO: expire command begin 2.48: --exec-id=2876-e5c4d427 --log-level-console=info --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql
2023-10-24 09:01:25.145 P00   INFO: expire command end: completed successfully (9ms)
[postgres@du101 ~]$ ll /data/backup/backup/pgsql/                                        
total 8.0K
drwxr-x--- 3 postgres postgres   72 Oct 24 08:59 20231024-085915F
drwxr-x--- 3 postgres postgres   72 Oct 24 09:01 20231024-085915F_20231024-090121D
drwxr-x--- 3 postgres postgres   18 Oct 24 08:59 backup.history
-rw-r----- 1 postgres postgres 1.8K Oct 24 09:01 backup.info
-rw-r----- 1 postgres postgres 1.8K Oct 24 09:01 backup.info.copy
lrwxrwxrwx 1 postgres postgres   33 Oct 24 09:01 latest -> 20231024-085915F_20231024-090121D
[postgres@du101 ~]$

增量备份


postgres@du101 ~]$ psql -c "insert into t select generate_series(1,100);checkpoint;"    
CHECKPOINT
[postgres@du101 ~]$ psql -c "select count(*) from t;"                                    
 count  
--------
 100200
(1 row)

[postgres@du101 ~]$ 

--第一次增量备份
[postgres@du101 ~]$ pgbackrest --stanza=pgsql --log-level-console=info --type=incr backup
2023-10-24 09:04:49.582 P00   INFO: backup command begin 2.48: --exec-id=2896-3eb7685d --log-level-console=info --pg1-path=/data/pgdata --pg1-port=1921 --pg1-user=postgres --process-max=2 --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql --start-fast --type=incr
2023-10-24 09:04:50.404 P00   INFO: last backup label = 20231024-085915F_20231024-090121D, version = 2.48
2023-10-24 09:04:50.404 P00   INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2023-10-24 09:04:51.508 P00   INFO: backup start archive = 000000010000000000000007, lsn = 0/7000028
2023-10-24 09:04:51.508 P00   INFO: check archive for prior segment 000000010000000000000006
WARN: exclude special file '/data/pgdata/.s.PGSQL.1921' from backup
2023-10-24 09:04:53.159 P00   INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2023-10-24 09:04:53.374 P00   INFO: backup stop archive = 000000010000000000000007, lsn = 0/7000100
2023-10-24 09:04:53.380 P00   INFO: check archive for segment(s) 000000010000000000000007:000000010000000000000007
2023-10-24 09:04:53.406 P00   INFO: new backup label = 20231024-085915F_20231024-090450I
2023-10-24 09:04:53.466 P00   INFO: incr backup size = 3.5MB, file total = 958
2023-10-24 09:04:53.466 P00   INFO: backup command end: completed successfully (3885ms)
2023-10-24 09:04:53.466 P00   INFO: expire command begin 2.48: --exec-id=2896-3eb7685d --log-level-console=info --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql
2023-10-24 09:04:53.474 P00   INFO: expire command end: completed successfully (8ms)
[postgres@du101 ~]$ ll /data/backup/backup/pgsql/                                        
total 8.0K
drwxr-x--- 3 postgres postgres   72 Oct 24 08:59 20231024-085915F
drwxr-x--- 3 postgres postgres   72 Oct 24 09:01 20231024-085915F_20231024-090121D
drwxr-x--- 3 postgres postgres   72 Oct 24 09:04 20231024-085915F_20231024-090450I
drwxr-x--- 3 postgres postgres   18 Oct 24 08:59 backup.history
-rw-r----- 1 postgres postgres 2.6K Oct 24 09:04 backup.info
-rw-r----- 1 postgres postgres 2.6K Oct 24 09:04 backup.info.copy
lrwxrwxrwx 1 postgres postgres   33 Oct 24 09:04 latest -> 20231024-085915F_20231024-090450I
[postgres@du101 ~]$ 

[postgres@du101 ~]$ psql -c "insert into t select generate_series(1,100);checkpoint;"
CHECKPOINT
[postgres@du101 ~]$ psql -c "select count(*) from t;"                                        
 count  
--------
 100300
(1 ro
[postgres@du101 ~]$ 

--第二次增量备份
[postgres@du101 ~]$ pgbackrest --stanza=pgsql --log-level-console=info --type=incr backup 
2023-10-24 09:05:38.510 P00   INFO: backup command begin 2.48: --exec-id=2909-47344759 --log-level-console=info --pg1-path=/data/pgdata --pg1-port=1921 --pg1-user=postgres --process-max=2 --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql --start-fast --type=incr
2023-10-24 09:05:39.293 P00   INFO: last backup label = 20231024-085915F_20231024-090450I, version = 2.48
2023-10-24 09:05:39.293 P00   INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2023-10-24 09:05:40.019 P00   INFO: backup start archive = 000000010000000000000009, lsn = 0/9000028
2023-10-24 09:05:40.020 P00   INFO: check archive for prior segment 000000010000000000000008
WARN: exclude special file '/data/pgdata/.s.PGSQL.1921' from backup
2023-10-24 09:05:41.963 P00   INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2023-10-24 09:05:42.490 P00   INFO: backup stop archive = 000000010000000000000009, lsn = 0/9000100
2023-10-24 09:05:42.500 P00   INFO: check archive for segment(s) 000000010000000000000009:000000010000000000000009
2023-10-24 09:05:42.534 P00   INFO: new backup label = 20231024-085915F_20231024-090539I
2023-10-24 09:05:42.593 P00   INFO: incr backup size = 3.5MB, file total = 958
2023-10-24 09:05:42.593 P00   INFO: backup command end: completed successfully (4083ms)
2023-10-24 09:05:42.593 P00   INFO: expire command begin 2.48: --exec-id=2909-47344759 --log-level-console=info --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql
2023-10-24 09:05:42.601 P00   INFO: expire command end: completed successfully (8ms)
[postgres@du101 ~]$ ll /data/backup/backup/pgsql/                                            
total 8.0K
drwxr-x--- 3 postgres postgres   72 Oct 24 08:59 20231024-085915F
drwxr-x--- 3 postgres postgres   72 Oct 24 09:01 20231024-085915F_20231024-090121D
drwxr-x--- 3 postgres postgres   72 Oct 24 09:04 20231024-085915F_20231024-090450I
drwxr-x--- 3 postgres postgres   72 Oct 24 09:05 20231024-085915F_20231024-090539I
drwxr-x--- 3 postgres postgres   18 Oct 24 08:59 backup.history
-rw-r----- 1 postgres postgres 3.4K Oct 24 09:05 backup.info
-rw-r----- 1 postgres postgres 3.4K Oct 24 09:05 backup.info.copy
lrwxrwxrwx 1 postgres postgres   33 Oct 24 09:05 latest -> 20231024-085915F_20231024-090539I
[postgres@du101 ~]$

注意 last backup label = 20231024-085915F_20231024-090450I表明第二次增量备份是基于第一个增量的。

差异备份对比

[postgres@du101 ~]$ psql -c "insert into t select generate_series(1,100);checkpoint;"  
CHECKPOINT
[postgres@du101 ~]$ psql -c "select count(*) from t;"                                     
 count  
--------
 100400
(1 row)

[postgres@du101 ~]$ 


[postgres@du101 ~]$ pgbackrest --stanza=pgsql --log-level-console=info --type=diff backup
2023-10-24 09:06:51.910 P00   INFO: backup command begin 2.48: --exec-id=2923-bfbee114 --log-level-console=info --pg1-path=/data/pgdata --pg1-port=1921 --pg1-user=postgres --process-max=2 --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql --start-fast --type=diff
2023-10-24 09:06:52.645 P00   INFO: last backup label = 20231024-085915F, version = 2.48
2023-10-24 09:06:52.645 P00   INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2023-10-24 09:06:53.718 P00   INFO: backup start archive = 00000001000000000000000B, lsn = 0/B000060
2023-10-24 09:06:53.718 P00   INFO: check archive for prior segment 00000001000000000000000A
WARN: exclude special file '/data/pgdata/.s.PGSQL.1921' from backup
2023-10-24 09:06:55.158 P00   INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2023-10-24 09:06:55.361 P00   INFO: backup stop archive = 00000001000000000000000B, lsn = 0/B000138
2023-10-24 09:06:55.370 P00   INFO: check archive for segment(s) 00000001000000000000000B:00000001000000000000000B
2023-10-24 09:06:55.391 P00   INFO: new backup label = 20231024-085915F_20231024-090652D
2023-10-24 09:06:55.450 P00   INFO: diff backup size = 3.8MB, file total = 958
2023-10-24 09:06:55.450 P00   INFO: backup command end: completed successfully (3540ms)
2023-10-24 09:06:55.450 P00   INFO: expire command begin 2.48: --exec-id=2923-bfbee114 --log-level-console=info --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql
2023-10-24 09:06:55.462 P00   INFO: expire command end: completed successfully (12ms)
[postgres@du101 ~]$ ll /data/backup/backup/pgsql/                                        
total 16K
drwxr-x--- 3 postgres postgres   72 Oct 24 08:59 20231024-085915F
drwxr-x--- 3 postgres postgres   72 Oct 24 09:01 20231024-085915F_20231024-090121D
drwxr-x--- 3 postgres postgres   72 Oct 24 09:04 20231024-085915F_20231024-090450I
drwxr-x--- 3 postgres postgres   72 Oct 24 09:05 20231024-085915F_20231024-090539I
drwxr-x--- 3 postgres postgres   72 Oct 24 09:06 20231024-085915F_20231024-090652D
drwxr-x--- 3 postgres postgres   18 Oct 24 08:59 backup.history
-rw-r----- 1 postgres postgres 4.2K Oct 24 09:06 backup.info
-rw-r----- 1 postgres postgres 4.2K Oct 24 09:06 backup.info.copy
lrwxrwxrwx 1 postgres postgres   33 Oct 24 09:06 latest -> 20231024-085915F_20231024-090652D
[postgres@du101 ~]$

注意last backup label = 20231024-085915F表明此次的差异备份是基于全量备份的；

六、查看备份信息

[postgres@du101 ~]$ cat /data/backup/backup/pgsql/backup.info
[backrest]
backrest-format=5
backrest-version="2.48"

[backup:current]
20231024-085915F={"backrest-format":5,"backrest-version":"2.48","backup-archive-start":"000000010000000000000003","backup-archive-stop":"000000010000000000000003","backup-error":false,"backup-info-repo-size":3695955,"backup-info-repo-size-delta":3695955,"backup-info-size":30043421,"backup-info-size-delta":30043421,"backup-lsn-start":"0/3000028","backup-lsn-stop":"0/3000138","backup-timestamp-start":1698109155,"backup-timestamp-stop":1698109162,"backup-type":"full","db-id":1,"option-archive-check":true,"option-archive-copy":false,"option-backup-standby":false,"option-checksum-page":false,"option-compress":true,"option-hardlink":false,"option-online":true}
20231024-085915F_20231024-090121D={"backrest-format":5,"backrest-version":"2.48","backup-archive-start":"000000010000000000000005","backup-archive-stop":"000000010000000000000005","backup-error":false,"backup-info-repo-size":3697409,"backup-info-repo-size-delta":480504,"backup-info-size":30058636,"backup-info-size-delta":3978051,"backup-lsn-start":"0/5000028","backup-lsn-stop":"0/5000138","backup-prior":"20231024-085915F","backup-reference":["20231024-085915F"],"backup-timestamp-start":1698109281,"backup-timestamp-stop":1698109284,"backup-type":"diff","db-id":1,"option-archive-check":true,"option-archive-copy":false,"option-backup-standby":false,"option-checksum-page":false,"option-compress":true,"option-hardlink":false,"option-online":true}
20231024-085915F_20231024-090450I={"backrest-format":5,"backrest-version":"2.48","backup-archive-start":"000000010000000000000007","backup-archive-stop":"000000010000000000000007","backup-error":false,"backup-info-repo-size":3698077,"backup-info-repo-size-delta":416484,"backup-info-size":30070343,"backup-info-size-delta":3694846,"backup-lsn-start":"0/7000028","backup-lsn-stop":"0/7000100","backup-prior":"20231024-085915F_20231024-090121D","backup-reference":["20231024-085915F","20231024-085915F_20231024-090121D"],"backup-timestamp-start":1698109490,"backup-timestamp-stop":1698109493,"backup-type":"incr","db-id":1,"option-archive-check":true,"option-archive-copy":false,"option-backup-standby":false,"option-checksum-page":false,"option-compress":true,"option-hardlink":false,"option-online":true}
20231024-085915F_20231024-090539I={"backrest-format":5,"backrest-version":"2.48","backup-archive-start":"000000010000000000000009","backup-archive-stop":"000000010000000000000009","backup-error":false,"backup-info-repo-size":3698765,"backup-info-repo-size-delta":417087,"backup-info-size":30073862,"backup-info-size-delta":3673789,"backup-lsn-start":"0/9000028","backup-lsn-stop":"0/9000100","backup-prior":"20231024-085915F_20231024-090450I","backup-reference":["20231024-085915F","20231024-085915F_20231024-090121D","20231024-085915F_20231024-090450I"],"backup-timestamp-start":1698109539,"backup-timestamp-stop":1698109542,"backup-type":"incr","db-id":1,"option-archive-check":true,"option-archive-copy":false,"option-backup-standby":false,"option-checksum-page":false,"option-compress":true,"option-hardlink":false,"option-online":true}
20231024-085915F_20231024-090652D={"backrest-format":5,"backrest-version":"2.48","backup-archive-start":"00000001000000000000000B","backup-archive-stop":"00000001000000000000000B","backup-error":false,"backup-info-repo-size":3699509,"backup-info-repo-size-delta":482604,"backup-info-size":30085573,"backup-info-size-delta":4004988,"backup-lsn-start":"0/B000060","backup-lsn-stop":"0/B000138","backup-prior":"20231024-085915F","backup-reference":["20231024-085915F"],"backup-timestamp-start":1698109612,"backup-timestamp-stop":1698109615,"backup-type":"diff","db-id":1,"option-archive-check":true,"option-archive-copy":false,"option-backup-standby":false,"option-checksum-page":false,"option-compress":true,"option-hardlink":false,"option-online":true}

[db]
db-catalog-version=202107181
db-control-version=1300
db-id=1
db-system-id=7209488936723521799
db-version="14"

[db:history]
1={"db-catalog-version":202107181,"db-control-version":1300,"db-system-id":7209488936723521799,"db-version":"14"}

[backrest]
backrest-checksum="8961821af01cc0e1dd5ff47b8a3fb1a1b3fab371"
[postgres@du101 ~]$

---查看所有的备份信息
[postgres@du101 ~]$ pgbackrest info
stanza: pgsql
    status: ok
    cipher: none

    db (current)
        wal archive min/max (14): 000000010000000000000001/00000001000000000000000B

        full backup: 20231024-085915F
            timestamp start/stop: 2023-10-24 08:59:15+08 / 2023-10-24 08:59:22+08
            wal start/stop: 000000010000000000000003 / 000000010000000000000003
            database size: 28.7MB, database backup size: 28.7MB
            repo1: backup set size: 3.5MB, backup size: 3.5MB

        diff backup: 20231024-085915F_20231024-090121D
            timestamp start/stop: 2023-10-24 09:01:21+08 / 2023-10-24 09:01:24+08
            wal start/stop: 000000010000000000000005 / 000000010000000000000005
            database size: 28.7MB, database backup size: 3.8MB
            repo1: backup set size: 3.5MB, backup size: 469.2KB
            backup reference list: 20231024-085915F

        incr backup: 20231024-085915F_20231024-090450I
            timestamp start/stop: 2023-10-24 09:04:50+08 / 2023-10-24 09:04:53+08
            wal start/stop: 000000010000000000000007 / 000000010000000000000007
            database size: 28.7MB, database backup size: 3.5MB
            repo1: backup set size: 3.5MB, backup size: 406.7KB
            backup reference list: 20231024-085915F, 20231024-085915F_20231024-090121D

        incr backup: 20231024-085915F_20231024-090539I
            timestamp start/stop: 2023-10-24 09:05:39+08 / 2023-10-24 09:05:42+08
            wal start/stop: 000000010000000000000009 / 000000010000000000000009
            database size: 28.7MB, database backup size: 3.5MB
            repo1: backup set size: 3.5MB, backup size: 407.3KB
            backup reference list: 20231024-085915F, 20231024-085915F_20231024-090121D, 20231024-085915F_20231024-090450I

        diff backup: 20231024-085915F_20231024-090652D
            timestamp start/stop: 2023-10-24 09:06:52+08 / 2023-10-24 09:06:55+08
            wal start/stop: 00000001000000000000000B / 00000001000000000000000B
            database size: 28.7MB, database backup size: 3.8MB
            repo1: backup set size: 3.5MB, backup size: 471.3KB
            backup reference list: 20231024-085915F
[postgres@du101 ~]$ 

--查看某一个备份集的详细信息
[postgres@du101 ~]$ pgbackrest --stanza=pgsql --set=20231024-085915F_20231024-090652D info
stanza: pgsql
    status: ok
    cipher: none

    db (current)
        wal archive min/max (14): 000000010000000000000001/00000001000000000000000B

        diff backup: 20231024-085915F_20231024-090652D
            timestamp start/stop: 2023-10-24 09:06:52+08 / 2023-10-24 09:06:55+08
            wal start/stop: 00000001000000000000000B / 00000001000000000000000B
            lsn start/stop: 0/B000060 / 0/B000138
            database size: 28.7MB, database backup size: 3.8MB
            repo1: backup set size: 3.5MB, backup size: 471.3KB
            backup reference list: 20231024-085915F
            database list: postgres (13892)
[postgres@du101 ~]$

七、pgbackRest还原

全量还原

在还原的时候不指定备份集，默认寻找最新的备份集进行还原。

[postgres@du101 ~]$ psql -c "select count(*) from t"
 count  
--------
 100400
(1 row)

[postgres@du101 ~]$ 

[postgres@du101 ~]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
[postgres@du101 ~]$ rm -fr /data/pgdata/*

[postgres@du101 ~]$ pgbackrest restore --stanza=pgsql --log-level-console=info
2023-10-24 09:17:29.741 P00   INFO: restore command begin 2.48: --exec-id=3022-1520edf9 --log-level-console=info --pg1-path=/data/pgdata --process-max=2 --repo1-path=/data/backup --stanza=pgsql
2023-10-24 09:17:29.759 P00   INFO: repo1: restore backup set 20231024-085915F_20231024-090652D, recovery will start at 2023-10-24 09:06:52
2023-10-24 09:17:32.706 P00   INFO: write updated /data/pgdata/postgresql.auto.conf
2023-10-24 09:17:32.715 P00   INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2023-10-24 09:17:32.717 P00   INFO: restore size = 28.7MB, file total = 958
2023-10-24 09:17:32.718 P00   INFO: restore command end: completed successfully (2978ms)
[postgres@du101 ~]$ ll /data/pgdata/    
total 64K
-rw------- 1 postgres postgres  258 Oct 24 09:06 backup_label
drwx------ 5 postgres postgres   41 Oct 24 09:17 base
-rw------- 1 postgres postgres   40 Oct 24 08:57 current_logfiles
drwx------ 2 postgres postgres 4.0K Oct 24 09:17 global
drwx------ 2 postgres postgres    6 Oct 24 09:17 pg_commit_ts
drwx------ 2 postgres postgres    6 Oct 24 09:17 pg_dynshmem
-rw------- 1 postgres postgres 4.8K Mar 12  2023 pg_hba.conf
-rw------- 1 postgres postgres 1.6K Mar 12  2023 pg_ident.conf
drwx------ 2 postgres postgres  138 Oct 24 09:17 pg_log
drwx------ 4 postgres postgres   68 Oct 24 09:17 pg_logical
drwx------ 4 postgres postgres   36 Oct 24 09:17 pg_multixact
drwx------ 2 postgres postgres    6 Oct 24 09:17 pg_notify
drwx------ 2 postgres postgres    6 Oct 24 09:17 pg_replslot
drwx------ 2 postgres postgres    6 Oct 24 09:17 pg_serial
drwx------ 2 postgres postgres    6 Oct 24 09:17 pg_snapshots
drwx------ 2 postgres postgres    6 Oct 24 09:17 pg_stat
drwx------ 2 postgres postgres    6 Oct 24 09:17 pg_stat_tmp
drwx------ 2 postgres postgres    6 Oct 24 09:17 pg_subtrans
drwx------ 2 postgres postgres    6 Oct 24 09:17 pg_tblspc
drwx------ 2 postgres postgres    6 Oct 24 09:17 pg_twophase
-rw------- 1 postgres postgres    3 Mar 12  2023 PG_VERSION
drwx------ 3 postgres postgres   28 Oct 24 09:17 pg_wal
drwx------ 2 postgres postgres   18 Oct 24 09:17 pg_xact
-rw------- 1 postgres postgres  230 Oct 24 09:17 postgresql.auto.conf
-rw------- 1 postgres postgres  29K Oct 24 08:57 postgresql.conf
-rw------- 1 postgres postgres    0 Oct 24 09:17 recovery.signal
[postgres@du101 ~]$ cat /data/pgdata/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.

# Recovery settings generated by pgBackRest restore on 2023-10-24 09:17:32
restore_command = 'pgbackrest --stanza=pgsql archive-get %f "%p"'
[postgres@du101 ~]$ pg_ctl start
waiting for server to start....2023-10-24 09:17:55.110 CST [3029] LOG:  00000: redirecting log output to logging collector process
2023-10-24 09:17:55.110 CST [3029] HINT:  Future log output will appear in directory "pg_log".
2023-10-24 09:17:55.110 CST [3029] LOCATION:  SysLogger_Start, syslogger.c:674
 done
server started
[postgres@du101 ~]$ psql -c "select count(*) from t"                          
 count  
--------
 100400
(1 row)

[postgres@du101 ~]$

还原之后会生成postgresql.auto.conf和recovery.signal。postgresql.auto.conf中只有restore_command 会恢复所有的归档日志。

PITR还原

PITR还原可以基于时间点，lsn和xid，命令如下：

pgbackrest restore --stanza=pgsql --type=xid "--target=837"  --log-level-console=info
pgbackrest restore --stanza=pgsql --type=lsn --target="0/7000028" --log-level-console=info
pgbackrest restore --stanza=pgsql --type=time --target="2023-10-24 09:01:21" --log-level-console=info

本次演示基于时间点和lsn的PITR恢复。

基于lsn的恢复

[postgres@du101 ~]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
[postgres@du101 ~]$ rm -fr /data/pgdata/* 
[postgres@du101 ~]$ pgbackrest --stanza=pgsql --set=20231024-085915F_20231024-090450I info
stanza: pgsql
    status: ok
    cipher: none

    db (current)
        wal archive min/max (14): 000000010000000000000001/00000002000000000000000D

        incr backup: 20231024-085915F_20231024-090450I
            timestamp start/stop: 2023-10-24 09:04:50+08 / 2023-10-24 09:04:53+08
            wal start/stop: 000000010000000000000007 / 000000010000000000000007
            lsn start/stop: 0/7000028 / 0/7000100
            database size: 28.7MB, database backup size: 3.5MB
            repo1: backup set size: 3.5MB, backup size: 406.7KB
            backup reference list: 20231024-085915F, 20231024-085915F_20231024-090121D
            database list: postgres (13892)
[postgres@du101 ~]$ pgbackrest restore --stanza=pgsql --type=lsn --target="0/7000028" --log-level-console=info 
2023-10-24 09:21:16.443 P00   INFO: restore command begin 2.48: --exec-id=3055-f059583f --log-level-console=info --pg1-path=/data/pgdata --process-max=2 --repo1-path=/data/backup --stanza=pgsql --target=0/7000028 --type=lsn
2023-10-24 09:21:16.464 P00   INFO: repo1: restore backup set 20231024-085915F_20231024-090121D, recovery will start at 2023-10-24 09:01:21
2023-10-24 09:21:20.073 P00   INFO: write updated /data/pgdata/postgresql.auto.conf
2023-10-24 09:21:20.087 P00   INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2023-10-24 09:21:20.089 P00   INFO: restore size = 28.7MB, file total = 958
2023-10-24 09:21:20.090 P00   INFO: restore command end: completed successfully (3648ms)
[postgres@du101 ~]$ cat /data/pgdata/postgresql.auto.conf  
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.

# Recovery settings generated by pgBackRest restore on 2023-10-24 09:21:20
restore_command = 'pgbackrest --stanza=pgsql archive-get %f "%p"'
recovery_target_lsn = '0/7000028'
[postgres@du101 ~]$ pg_ctl start
waiting for server to start....2023-10-24 09:21:39.020 CST [3061] LOG:  00000: redirecting log output to logging collector process
2023-10-24 09:21:39.020 CST [3061] HINT:  Future log output will appear in directory "pg_log".
2023-10-24 09:21:39.020 CST [3061] LOCATION:  SysLogger_Start, syslogger.c:674
 done
server started
[postgres@du101 ~]$  psql -c "select count(*) from t"
 count  
--------
 100200
(1 row)

[postgres@du101 ~]$

基于时间点的恢复

[postgres@du101 ~]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
[postgres@du101 ~]$ rm -fr /data/pgdata/*
[postgres@du101 ~]$ pgbackrest --stanza=pgsql --set=20231024-085915F_20231024-090121D info                     
stanza: pgsql
    status: ok
    cipher: none

    db (current)
        wal archive min/max (14): 000000010000000000000001/00000002000000000000000D

        diff backup: 20231024-085915F_20231024-090121D
            timestamp start/stop: 2023-10-24 09:01:21+08 / 2023-10-24 09:01:24+08
            wal start/stop: 000000010000000000000005 / 000000010000000000000005
            lsn start/stop: 0/5000028 / 0/5000138
            database size: 28.7MB, database backup size: 3.8MB
            repo1: backup set size: 3.5MB, backup size: 469.2KB
            backup reference list: 20231024-085915F
            database list: postgres (13892)
[postgres@du101 ~]$ pgbackrest restore --stanza=pgsql --type=time --target="2023-10-24 09:01:21" --log-level-console=info 
2023-10-24 09:23:15.035 P00   INFO: restore command begin 2.48: --exec-id=3080-369cb7f1 --log-level-console=info --pg1-path=/data/pgdata --process-max=2 --repo1-path=/data/backup --stanza=pgsql --target="2023-10-24 09:01:21" --type=time
2023-10-24 09:23:15.056 P00   INFO: repo1: restore backup set 20231024-085915F, recovery will start at 2023-10-24 08:59:15
2023-10-24 09:23:18.161 P00   INFO: write updated /data/pgdata/postgresql.auto.conf
2023-10-24 09:23:18.169 P00   INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2023-10-24 09:23:18.171 P00   INFO: restore size = 28.7MB, file total = 957
2023-10-24 09:23:18.172 P00   INFO: restore command end: completed successfully (3138ms)
[postgres@du101 ~]$ cat /data/pgdata/postgresql.auto.conf 
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.

# Recovery settings generated by pgBackRest restore on 2023-10-24 09:23:18
restore_command = 'pgbackrest --stanza=pgsql archive-get %f "%p"'
recovery_target_time = '2023-10-24 09:01:21'
[postgres@du101 ~]$ pg_ctl start
waiting for server to start....2023-10-24 09:23:37.431 CST [3086] LOG:  00000: redirecting log output to logging collector process
2023-10-24 09:23:37.431 CST [3086] HINT:  Future log output will appear in directory "pg_log".
2023-10-24 09:23:37.431 CST [3086] LOCATION:  SysLogger_Start, syslogger.c:674
 done
server started
[postgres@du101 ~]$  psql -c "select count(*) from t"
 count  
--------
 100100
(1 row)

[postgres@du101 ~]$

指定备份集的恢复

[postgres@du101 ~]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
[postgres@du101 ~]$ rm -fr /data/pgdata/*
[postgres@du101 ~]$ pgbackrest restore --stanza=pgsql --set=20231024-085915F_20231024-090539I --log-level-console=info 
2023-10-24 09:37:48.612 P00   INFO: restore command begin 2.48: --exec-id=3319-975accb2 --log-level-console=info --pg1-path=/data/pgdata --process-max=2 --repo1-path=/data/backup --set=20231024-085915F_20231024-090539I --stanza=pgsql
2023-10-24 09:37:48.631 P00   INFO: repo1: restore backup set 20231024-085915F_20231024-090539I, recovery will start at 2023-10-24 09:05:39
2023-10-24 09:37:51.347 P00   INFO: write updated /data/pgdata/postgresql.auto.conf
2023-10-24 09:37:51.355 P00   INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2023-10-24 09:37:51.370 P00   INFO: restore size = 28.7MB, file total = 958
2023-10-24 09:37:51.371 P00   INFO: restore command end: completed successfully (2760ms)
[postgres@du101 ~]$ cat /data/pgdata/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.

# Recovery settings generated by pgBackRest restore on 2023-10-24 09:37:51
restore_command = 'pgbackrest --stanza=pgsql archive-get %f "%p"'
[postgres@du101 ~]$ pg_ctl start
waiting for server to start....2023-10-24 09:38:01.577 CST [3325] LOG:  00000: redirecting log output to logging collector process
2023-10-24 09:38:01.577 CST [3325] HINT:  Future log output will appear in directory "pg_log".
2023-10-24 09:38:01.577 CST [3325] LOCATION:  SysLogger_Start, syslogger.c:674
 done
server started
[postgres@du101 ~]$  psql -c "select count(*) from t"
 count  
--------
 100400
(1 row)

[postgres@du101 ~]$

虽然指定了备份集，但是在postgresql.auto.conf只有restore_command，会恢复所有的归档，所以最后的结果为所有的数据。

在恢复的时候，手工编辑postgresql.auto.conf：

[postgres@du101 ~]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
[postgres@du101 ~]$ rm -fr /data/pgdata/*
[postgres@du101 ~]$ pgbackrest restore --stanza=pgsql --set=20231024-085915F_20231024-090539I --log-level-console=info 
2023-10-24 09:30:59.132 P00   INFO: restore command begin 2.48: --exec-id=3286-f12503ef --log-level-console=info --pg1-path=/data/pgdata --process-max=2 --repo1-path=/data/backup --set=20231024-085915F_20231024-090539I --stanza=pgsql
2023-10-24 09:30:59.149 P00   INFO: repo1: restore backup set 20231024-085915F_20231024-090539I, recovery will start at 2023-10-24 09:05:39
2023-10-24 09:31:02.158 P00   INFO: write updated /data/pgdata/postgresql.auto.conf
2023-10-24 09:31:02.166 P00   INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2023-10-24 09:31:02.168 P00   INFO: restore size = 28.7MB, file total = 958
2023-10-24 09:31:02.169 P00   INFO: restore command end: completed successfully (3038ms)

[postgres@du101 ~]$ ll /data/pgdata/
total 64K
-rw------- 1 postgres postgres  258 Oct 24 09:05 backup_label
drwx------ 5 postgres postgres   41 Oct 24 09:30 base
-rw------- 1 postgres postgres   40 Oct 24 08:57 current_logfiles
drwx------ 2 postgres postgres 4.0K Oct 24 09:31 global
drwx------ 2 postgres postgres    6 Oct 24 09:30 pg_commit_ts
drwx------ 2 postgres postgres    6 Oct 24 09:30 pg_dynshmem
-rw------- 1 postgres postgres 4.8K Mar 12  2023 pg_hba.conf
-rw------- 1 postgres postgres 1.6K Mar 12  2023 pg_ident.conf
drwx------ 2 postgres postgres  138 Oct 24 09:31 pg_log
drwx------ 4 postgres postgres   68 Oct 24 09:31 pg_logical
drwx------ 4 postgres postgres   36 Oct 24 09:30 pg_multixact
drwx------ 2 postgres postgres    6 Oct 24 09:30 pg_notify
drwx------ 2 postgres postgres    6 Oct 24 09:30 pg_replslot
drwx------ 2 postgres postgres    6 Oct 24 09:30 pg_serial
drwx------ 2 postgres postgres    6 Oct 24 09:30 pg_snapshots
drwx------ 2 postgres postgres    6 Oct 24 09:30 pg_stat
drwx------ 2 postgres postgres    6 Oct 24 09:30 pg_stat_tmp
drwx------ 2 postgres postgres    6 Oct 24 09:30 pg_subtrans
drwx------ 2 postgres postgres    6 Oct 24 09:30 pg_tblspc
drwx------ 2 postgres postgres    6 Oct 24 09:30 pg_twophase
-rw------- 1 postgres postgres    3 Mar 12  2023 PG_VERSION
drwx------ 3 postgres postgres   28 Oct 24 09:30 pg_wal
drwx------ 2 postgres postgres   18 Oct 24 09:31 pg_xact
-rw------- 1 postgres postgres  230 Oct 24 09:31 postgresql.auto.conf
-rw------- 1 postgres postgres  29K Oct 24 08:57 postgresql.conf
-rw------- 1 postgres postgres    0 Oct 24 09:31 recovery.signal
[postgres@du101 ~]$ cat /data/pgdata/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.

# Recovery settings generated by pgBackRest restore on 2023-10-24 09:31:02
restore_command = 'pgbackrest --stanza=pgsql archive-get %f "%p"'
[postgres@du101 ~]$ vi /data/pgdata/postgresql.auto.conf
[postgres@du101 ~]$ cat /data/pgdata/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.

# Recovery settings generated by pgBackRest restore on 2023-10-24 09:31:02
restore_command = 'pgbackrest --stanza=pgsql archive-get %f "%p"'
recovery_target = 'immediate'
[postgres@du101 ~]$ pg_ctl start
waiting for server to start....2023-10-24 09:33:39.647 CST [3300] LOG:  00000: redirecting log output to logging collector process
2023-10-24 09:33:39.647 CST [3300] HINT:  Future log output will appear in directory "pg_log".
2023-10-24 09:33:39.647 CST [3300] LOCATION:  SysLogger_Start, syslogger.c:674
 done
server started
[postgres@du101 ~]$  psql -c "select count(*) from t"
 count  
--------
 100300
(1 row)

[postgres@du101 ~]$

手工编辑postgresql.auto.conf，添加recovery_target = 'immediate’表示只要恢复到数据一致的状态后停止。则可以恢复到指定备份集的数据。

recovery-option在恢复的时候指定要写入postgresql.auto.conf的选项

[postgres@du101 ~]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
[postgres@du101 ~]$ rm -fr /data/pgdata/*
[postgres@du101 ~]$ pgbackrest restore --stanza=pgsql --set=20231024-085915F_20231024-090539I --log-level-console=info  --recovery-option="recovery_target=immediate"
2023-10-24 10:04:36.556 P00   INFO: restore command begin 2.48: --exec-id=3472-15075067 --log-level-console=info --pg1-path=/data/pgdata --process-max=2 --recovery-option=recovery_target=immediate --repo1-path=/data/backup --set=20231024-085915F_20231024-090539I --stanza=pgsql
2023-10-24 10:04:36.574 P00   INFO: repo1: restore backup set 20231024-085915F_20231024-090539I, recovery will start at 2023-10-24 09:05:39
2023-10-24 10:04:39.906 P00   INFO: write updated /data/pgdata/postgresql.auto.conf
2023-10-24 10:04:39.914 P00   INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2023-10-24 10:04:39.916 P00   INFO: restore size = 28.7MB, file total = 958
2023-10-24 10:04:39.917 P00   INFO: restore command end: completed successfully (3362ms)
[postgres@du101 ~]$ cat /data/pgdata/postgresql.auto.conf 
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.

# Recovery settings generated by pgBackRest restore on 2023-10-24 10:04:39
recovery_target = 'immediate'
restore_command = 'pgbackrest --stanza=pgsql archive-get %f "%p"'
[postgres@du101 ~]$ pg_ctl start
waiting for server to start....2023-10-24 10:04:52.037 CST [3478] LOG:  00000: redirecting log output to logging collector process
2023-10-24 10:04:52.037 CST [3478] HINT:  Future log output will appear in directory "pg_log".
2023-10-24 10:04:52.037 CST [3478] LOCATION:  SysLogger_Start, syslogger.c:674
 done
server started

[postgres@du101 ~]$ psql
psql (14.7)
Type "help" for help.

postgres=# select count(*) from t;
 count  
--------
 100300
(1 row)

postgres=#

通过–recovery-option="recovery_target=immediate"指定recovery_target=immediate到postgresql.auto.conf 也可以达到恢复到指定备份集的效果。

其他恢复选项

可以通过pgbackrest help restore查看所有的恢复选项：

  --db-exclude                        restore excluding the specified databases
  --db-include                        restore only specified databases
    --delta                             restore or backup using checksums

终止recovery状态

数据库在基于PITR的恢复(或者会到指定备份集数据)之后，处于read-only transaction中，不能写，需要手工执行命令select pg_wal_replay_resume();终止recovery状态。

[postgres@du101 ~]$ psql
psql (14.7)
Type "help" for help.

postgres=# select count(*) from t;
 count  
--------
 100300
(1 row)

postgres=# checkpoint;
CHECKPOINT
postgres=# create database testdb;
ERROR:  cannot execute CREATE DATABASE in a read-only transaction
postgres=# select pg_wal_replay_resume();
 pg_wal_replay_resume 
----------------------
 
(1 row)

postgres=# create database testdb;       
CREATE DATABASE
postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=# create table t(id int);
CREATE TABLE
testdb=# insert into t select generate_series(1,100);   
INSERT 0 100
testdb=# checkpoint;
CHECKPOINT
testdb=# \q
[postgres@du101 ~]$

八、保留策略

全量冗余策略
** repo1-retention-full=2**
差异备份冗余策略
** repo1-retention-diff=1**
归档策略
** repo1-retention-diff=2**
** repo1-retention-full=2**

在配置文件中已经指定repo1-retention-full=2，会保留2个全备的备份集，目前只有一个全备备份集，需要增加全备备份集数量：

[postgres@du101 ~]$ pgbackrest --stanza=pgsql --log-level-console=info --type=full backup
2023-10-24 10:22:47.411 P00   INFO: backup command begin 2.48: --exec-id=3596-dafacc38 --log-level-console=info --pg1-path=/data/pgdata --pg1-port=1921 --pg1-user=postgres --process-max=2 --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql --start-fast --type=full
2023-10-24 10:22:48.376 P00   INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2023-10-24 10:22:49.132 P00   INFO: backup start archive = 000000050000000000000008, lsn = 0/8000028
2023-10-24 10:22:49.132 P00   INFO: check archive for prior segment 000000050000000000000007
WARN: exclude special file '/data/pgdata/.s.PGSQL.1921' from backup
2023-10-24 10:22:53.905 P00   INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2023-10-24 10:22:54.107 P00   INFO: backup stop archive = 000000050000000000000008, lsn = 0/8000138
2023-10-24 10:22:54.115 P00   INFO: check archive for segment(s) 000000050000000000000008:000000050000000000000008
2023-10-24 10:22:54.137 P00   INFO: new backup label = 20231024-102248F
2023-10-24 10:22:54.200 P00   INFO: full backup size = 36.9MB, file total = 1253
2023-10-24 10:22:54.200 P00   INFO: backup command end: completed successfully (6790ms)
2023-10-24 10:22:54.200 P00   INFO: expire command begin 2.48: --exec-id=3596-dafacc38 --log-level-console=info --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql
2023-10-24 10:22:54.214 P00   INFO: repo1: 14-1 remove archive, start = 000000010000000000000001, stop = 000000010000000000000002
2023-10-24 10:22:54.214 P00   INFO: expire command end: completed successfully (14ms)
[postgres@du101 ~]$ pgbackrest info
stanza: pgsql
    status: ok
    cipher: none

    db (current)
        wal archive min/max (14): 000000010000000000000003/000000050000000000000008

        full backup: 20231024-085915F
            timestamp start/stop: 2023-10-24 08:59:15+08 / 2023-10-24 08:59:22+08
            wal start/stop: 000000010000000000000003 / 000000010000000000000003
            database size: 28.7MB, database backup size: 28.7MB
            repo1: backup set size: 3.5MB, backup size: 3.5MB

        diff backup: 20231024-085915F_20231024-090121D
            timestamp start/stop: 2023-10-24 09:01:21+08 / 2023-10-24 09:01:24+08
            wal start/stop: 000000010000000000000005 / 000000010000000000000005
            database size: 28.7MB, database backup size: 3.8MB
            repo1: backup set size: 3.5MB, backup size: 469.2KB
            backup reference list: 20231024-085915F

        incr backup: 20231024-085915F_20231024-090450I
            timestamp start/stop: 2023-10-24 09:04:50+08 / 2023-10-24 09:04:53+08
            wal start/stop: 000000010000000000000007 / 000000010000000000000007
            database size: 28.7MB, database backup size: 3.5MB
            repo1: backup set size: 3.5MB, backup size: 406.7KB
            backup reference list: 20231024-085915F, 20231024-085915F_20231024-090121D

        incr backup: 20231024-085915F_20231024-090539I
            timestamp start/stop: 2023-10-24 09:05:39+08 / 2023-10-24 09:05:42+08
            wal start/stop: 000000010000000000000009 / 000000010000000000000009
            database size: 28.7MB, database backup size: 3.5MB
            repo1: backup set size: 3.5MB, backup size: 407.3KB
            backup reference list: 20231024-085915F, 20231024-085915F_20231024-090121D, 20231024-085915F_20231024-090450I

        diff backup: 20231024-085915F_20231024-090652D
            timestamp start/stop: 2023-10-24 09:06:52+08 / 2023-10-24 09:06:55+08
            wal start/stop: 00000001000000000000000B / 00000001000000000000000B
            database size: 28.7MB, database backup size: 3.8MB
            repo1: backup set size: 3.5MB, backup size: 471.3KB
            backup reference list: 20231024-085915F

        full backup: 20231024-102248F
            timestamp start/stop: 2023-10-24 10:22:48+08 / 2023-10-24 10:22:54+08
            wal start/stop: 000000050000000000000008 / 000000050000000000000008
            database size: 36.9MB, database backup size: 36.9MB
            repo1: backup set size: 4.6MB, backup size: 4.6MB
[postgres@du101 ~]$

可以看出，已经达到2个全备，如果再次进行全量备份，则保留策略会生效：

[postgres@du101 ~]$ psql testdb -c "insert into t select generate_series(1,100);checkpoint;"
CHECKPOINT
[postgres@du101 ~]$ pgbackrest --stanza=pgsql --log-level-console=info --type=full backup   
2023-10-24 10:23:31.237 P00   INFO: backup command begin 2.48: --exec-id=3609-61586be5 --log-level-console=info --pg1-path=/data/pgdata --pg1-port=1921 --pg1-user=postgres --process-max=2 --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql --start-fast --type=full
2023-10-24 10:23:31.957 P00   INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2023-10-24 10:23:32.688 P00   INFO: backup start archive = 00000005000000000000000A, lsn = 0/A000028
2023-10-24 10:23:32.688 P00   INFO: check archive for prior segment 000000050000000000000009
WARN: exclude special file '/data/pgdata/.s.PGSQL.1921' from backup
2023-10-24 10:23:37.067 P00   INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2023-10-24 10:23:37.679 P00   INFO: backup stop archive = 00000005000000000000000A, lsn = 0/A000100
2023-10-24 10:23:37.687 P00   INFO: check archive for segment(s) 00000005000000000000000A:00000005000000000000000A
2023-10-24 10:23:37.707 P00   INFO: new backup label = 20231024-102331F
2023-10-24 10:23:37.770 P00   INFO: full backup size = 36.9MB, file total = 1253
2023-10-24 10:23:37.770 P00   INFO: backup command end: completed successfully (6534ms)
2023-10-24 10:23:37.770 P00   INFO: expire command begin 2.48: --exec-id=3609-61586be5 --log-level-console=info --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql
2023-10-24 10:23:37.771 P00   INFO: repo1: expire full backup set 20231024-085915F, 20231024-085915F_20231024-090121D, 20231024-085915F_20231024-090450I, 20231024-085915F_20231024-090539I, 20231024-085915F_20231024-090652D
2023-10-24 10:23:37.783 P00   INFO: repo1: remove expired backup 20231024-085915F_20231024-090652D
2023-10-24 10:23:37.783 P00   INFO: repo1: remove expired backup 20231024-085915F_20231024-090539I
2023-10-24 10:23:37.784 P00   INFO: repo1: remove expired backup 20231024-085915F_20231024-090450I
2023-10-24 10:23:37.784 P00   INFO: repo1: remove expired backup 20231024-085915F_20231024-090121D
2023-10-24 10:23:37.785 P00   INFO: repo1: remove expired backup 20231024-085915F
2023-10-24 10:23:37.814 P00   INFO: repo1: 14-1 remove archive, start = 0000000400000000, stop = 000000050000000000000007
2023-10-24 10:23:37.814 P00   INFO: repo1: 14-1 remove history file 00000002.history
2023-10-24 10:23:37.814 P00   INFO: repo1: 14-1 remove history file 00000003.history
2023-10-24 10:23:37.814 P00   INFO: repo1: 14-1 remove history file 00000004.history
2023-10-24 10:23:37.814 P00   INFO: expire command end: completed successfully (44ms)
[postgres@du101 ~]$ 
[postgres@du101 ~]$ pgbackrest info
stanza: pgsql
    status: ok
    cipher: none

    db (current)
        wal archive min/max (14): 000000050000000000000008/00000005000000000000000A

        full backup: 20231024-102248F
            timestamp start/stop: 2023-10-24 10:22:48+08 / 2023-10-24 10:22:54+08
            wal start/stop: 000000050000000000000008 / 000000050000000000000008
            database size: 36.9MB, database backup size: 36.9MB
            repo1: backup set size: 4.6MB, backup size: 4.6MB

        full backup: 20231024-102331F
            timestamp start/stop: 2023-10-24 10:23:31+08 / 2023-10-24 10:23:37+08
            wal start/stop: 00000005000000000000000A / 00000005000000000000000A
            database size: 36.9MB, database backup size: 36.9MB
            repo1: backup set size: 4.6MB, backup size: 4.6MB
[postgres@du101 ~]$

可以看出之前的备份集都被删除。检查归档，也被删除：

[postgres@du101 ~]$ ll /data/backup/archive/pgsql/14-1/0000000500000000/
total 228K
-rw-r----- 1 postgres postgres 371 Oct 24 10:22 000000050000000000000008.00000028.backup
-rw-r----- 1 postgres postgres 72K Oct 24 10:22 000000050000000000000008-572204415f43334380af6e1c5b5bd239645e6c95.gz
-rw-r----- 1 postgres postgres 75K Oct 24 10:23 000000050000000000000009-92fd7508875b62821a86c82af171749d90a9cb3b.gz
-rw-r----- 1 postgres postgres 370 Oct 24 10:23 00000005000000000000000A.00000028.backup
-rw-r----- 1 postgres postgres 72K Oct 24 10:23 00000005000000000000000A-5091dff675fb4950c2fbf71e0e31e4c31b356276.gz
[postgres@du101 ~]$

手工指定某个备份集expire之后，对应的备份集也会被删除：

[postgres@du101 ~]$ pgbackrest expire --set=20231024-102248F --stanza=pgsql
[postgres@du101 ~]$ pgbackrest info
stanza: pgsql
    status: ok
    cipher: none

    db (current)
        wal archive min/max (14): 000000050000000000000008/00000005000000000000000A

        full backup: 20231024-102331F
            timestamp start/stop: 2023-10-24 10:23:31+08 / 2023-10-24 10:23:37+08
            wal start/stop: 00000005000000000000000A / 00000005000000000000000A
            database size: 36.9MB, database backup size: 36.9MB
            repo1: backup set size: 4.6MB, backup size: 4.6MB
[postgres@du101 ~]$ ll /data/backup/archive/pgsql/14-1/0000000500000000/
total 228K
-rw-r----- 1 postgres postgres 371 Oct 24 10:22 000000050000000000000008.00000028.backup
-rw-r----- 1 postgres postgres 72K Oct 24 10:22 000000050000000000000008-572204415f43334380af6e1c5b5bd239645e6c95.gz
-rw-r----- 1 postgres postgres 75K Oct 24 10:23 000000050000000000000009-92fd7508875b62821a86c82af171749d90a9cb3b.gz
-rw-r----- 1 postgres postgres 370 Oct 24 10:23 00000005000000000000000A.00000028.backup
-rw-r----- 1 postgres postgres 72K Oct 24 10:23 00000005000000000000000A-5091dff675fb4950c2fbf71e0e31e4c31b356276.gz
[postgres@du101 ~]$ 
[postgres@du101 ~]$ 
[postgres@du101 ~]$ 
[postgres@du101 ~]$ pgbackrest --stanza=pgsql --log-level-console=info --type=full backup
2023-10-24 10:30:55.978 P00   INFO: backup command begin 2.48: --exec-id=3650-1fb9a7d8 --log-level-console=info --pg1-path=/data/pgdata --pg1-port=1921 --pg1-user=postgres --process-max=2 --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql --start-fast --type=full
2023-10-24 10:30:57.363 P00   INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2023-10-24 10:30:58.409 P00   INFO: backup start archive = 00000005000000000000000C, lsn = 0/C000060
2023-10-24 10:30:58.409 P00   INFO: check archive for prior segment 00000005000000000000000B
WARN: exclude special file '/data/pgdata/.s.PGSQL.1921' from backup
2023-10-24 10:31:02.517 P00   INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2023-10-24 10:31:02.717 P00   INFO: backup stop archive = 00000005000000000000000C, lsn = 0/C000138
2023-10-24 10:31:02.723 P00   INFO: check archive for segment(s) 00000005000000000000000C:00000005000000000000000C
2023-10-24 10:31:02.742 P00   INFO: new backup label = 20231024-103057F
2023-10-24 10:31:02.809 P00   INFO: full backup size = 36.9MB, file total = 1253
2023-10-24 10:31:02.809 P00   INFO: backup command end: completed successfully (6832ms)
2023-10-24 10:31:02.809 P00   INFO: expire command begin 2.48: --exec-id=3650-1fb9a7d8 --log-level-console=info --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql
2023-10-24 10:31:02.819 P00   INFO: repo1: 14-1 remove archive, start = 000000050000000000000008, stop = 000000050000000000000009
2023-10-24 10:31:02.819 P00   INFO: expire command end: completed successfully (10ms)
[postgres@du101 ~]$ pgbackrest info
stanza: pgsql
    status: ok
    cipher: none

    db (current)
        wal archive min/max (14): 00000005000000000000000A/00000005000000000000000C

        full backup: 20231024-102331F
            timestamp start/stop: 2023-10-24 10:23:31+08 / 2023-10-24 10:23:37+08
            wal start/stop: 00000005000000000000000A / 00000005000000000000000A
            database size: 36.9MB, database backup size: 36.9MB
            repo1: backup set size: 4.6MB, backup size: 4.6MB

        full backup: 20231024-103057F
            timestamp start/stop: 2023-10-24 10:30:57+08 / 2023-10-24 10:31:02+08
            wal start/stop: 00000005000000000000000C / 00000005000000000000000C
            database size: 36.9MB, database backup size: 36.9MB
            repo1: backup set size: 4.6MB, backup size: 4.6MB
[postgres@du101 ~]$ ll /data/backup/archive/pgsql/14-1/0000000500000000/                 
total 232K
-rw-r----- 1 postgres postgres 370 Oct 24 10:23 00000005000000000000000A.00000028.backup
-rw-r----- 1 postgres postgres 72K Oct 24 10:23 00000005000000000000000A-5091dff675fb4950c2fbf71e0e31e4c31b356276.gz
-rw-r----- 1 postgres postgres 79K Oct 24 10:30 00000005000000000000000B-e2f5215afc918424292aa47f98d41a2d414ae4c8.gz
-rw-r----- 1 postgres postgres 371 Oct 24 10:31 00000005000000000000000C.00000060.backup
-rw-r----- 1 postgres postgres 72K Oct 24 10:31 00000005000000000000000C-ee2a1baa8b64ec95ee3928759a77fc718e9ee94f.gz
[postgres@du101 ~]$

九、监控

The PostgreSQL allows pgBackRest info to be loaded into a table.

[root@du101 ~]# cp pgbackrest-release-2.48/doc/example/* /home/postgres/
[root@du101 ~]# chown postgres:postgres /home/postgres/pgsql-pgbackrest*sql

[postgres@du101 ~]$ psql -f pgsql-pgbackrest-info.sql
CREATE SCHEMA
CREATE FUNCTION
[postgres@du101 ~]$ psql -f pgsql-pgbackrest-query.sql 
  name   | last_successful_backup |    last_archived_wal     
---------+------------------------+--------------------------
 "pgsql" | 2023-10-24 10:31:02+08 | 00000006000000000000000E
(1 row)

[postgres@du101 ~]$ pgbackrest --stanza=pgsql --log-level-console=info --type=full backup
2023-10-24 10:59:01.824 P00   INFO: backup command begin 2.48: --exec-id=3738-4ad48e39 --log-level-console=info --pg1-path=/data/pgdata --pg1-port=1921 --pg1-user=postgres --process-max=2 --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql --start-fast --type=full
2023-10-24 10:59:02.609 P00   INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2023-10-24 10:59:03.186 P00   INFO: backup start archive = 000000060000000000000010, lsn = 0/10000028
2023-10-24 10:59:03.186 P00   INFO: check archive for prior segment 00000006000000000000000F
WARN: exclude special file '/data/pgdata/.s.PGSQL.1921' from backup
2023-10-24 10:59:07.780 P00   INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2023-10-24 10:59:07.981 P00   INFO: backup stop archive = 000000060000000000000010, lsn = 0/10000100
2023-10-24 10:59:07.992 P00   INFO: check archive for segment(s) 000000060000000000000010:000000060000000000000010
2023-10-24 10:59:08.014 P00   INFO: new backup label = 20231024-105902F
2023-10-24 10:59:08.080 P00   INFO: full backup size = 36.9MB, file total = 1253
2023-10-24 10:59:08.080 P00   INFO: backup command end: completed successfully (6257ms)
2023-10-24 10:59:08.080 P00   INFO: expire command begin 2.48: --exec-id=3738-4ad48e39 --log-level-console=info --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql
2023-10-24 10:59:08.081 P00   INFO: repo1: expire full backup 20231024-102331F
2023-10-24 10:59:08.092 P00   INFO: repo1: remove expired backup 20231024-102331F
2023-10-24 10:59:08.135 P00   INFO: repo1: 14-1 remove archive, start = 00000005000000000000000A, stop = 00000005000000000000000B
2023-10-24 10:59:08.135 P00   INFO: expire command end: completed successfully (55ms)
[postgres@du101 ~]$ psql -f pgsql-pgbackrest-query.sql                                   
  name   | last_successful_backup |    last_archived_wal     
---------+------------------------+--------------------------
 "pgsql" | 2023-10-24 10:59:07+08 | 000000060000000000000010
(1 row)

[postgres@du101 ~]$ pgbackrest info
stanza: pgsql
    status: ok
    cipher: none

    db (current)
        wal archive min/max (14): 00000005000000000000000C/000000060000000000000010

        full backup: 20231024-103057F
            timestamp start/stop: 2023-10-24 10:30:57+08 / 2023-10-24 10:31:02+08
            wal start/stop: 00000005000000000000000C / 00000005000000000000000C
            database size: 36.9MB, database backup size: 36.9MB
            repo1: backup set size: 4.6MB, backup size: 4.6MB

        full backup: 20231024-105902F
            timestamp start/stop: 2023-10-24 10:59:02+08 / 2023-10-24 10:59:07+08
            wal start/stop: 000000060000000000000010 / 000000060000000000000010
            database size: 36.9MB, database backup size: 36.9MB
            repo1: backup set size: 4.6MB, backup size: 4.6MB
[postgres@du101 ~]$
duqk1982
关注
  • 1
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
PostgreSQL备份工具 - PgBackRest安装部署使用
qq_37531186的博客
04-17 477
【代码】PgBackRest。
pgbackrest：可靠的PostgreSQL备份和还原
01-31
- **checksum**：pgbackrest利用校验和验证备份文件的完整性，确保备份无损。 - **archiving**：pgbackrest支持归档WAL（Write-Ahead Log）段，这是PostgreSQL事务日志，用于进行连续备份和灾难恢复。 **并行处理...
PostgreSQL pgbackrest 参数与优化 与 “小作文和售货员”
sql server的专栏
12-18 887
开头还是介绍一下群，如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,Redis, Oceanbase, Sql Server，Oracle等有问题，有需求都可以加群群内，可以解决你的问题。加群请微信联系 liuaustin3 ，（共1800人左右 1 + 2 + 3 + 4 +5） 4群（300+）,另欢迎 OpenGauss 的技术人员加入。最近热度最大的新闻...
pgbackrest 备份工具使用 postgresql
最新发布
liyayou的博客
05-10 761
我们的备份策略基本是，1天1次完整备份，1个小时1次差异备份。注意：命令中postgres:postgres，是为了授权给postgres用户权限，而且最好是用postgres用户，不然会遇到很多操作报无权限【因为postgresql在安装初始化的时候就自动创建了一个系统用户postgres，默认使用的也是postgres】#加上 --delta ，pgBackRest 自动确定数据库集群目录中的哪些文件可以保留，哪些文件需要从备份中恢复 — 它还会删除备份清单中不存在的文件，以便处理不同的更改。
postgresql之pgbackrest备份恢复
God_sign的博客
12-08 409
pg库备份
pgBackRest 之三 restore
一名数据库爱好者的专栏
05-04 749
os: centos 7.4 db: postgresql 11.7 版本 # cat /etc/centos-release CentOS Linux release 7.4.1708 (Core) # # # yum list installed |grep -i postgresql postgresql11.x86_64 11.7-1PGDG.rhel7...
pg库恢复
u013758456的专栏
04-13 272
psql.exe -h 127.0.0.1 -p 5432 -U postgres DB123 < F:\DB123.sql
PostgreSQL备份恢复之pgbackRest备份
weixin_41561946的博客
02-22 1398
dd
pgBackRest-可靠的PostgreSQL备份和还原-C/C++开发
05-26
pgBackRest可靠的PostgreSQL备份和还原简介pgBackRest旨在成为一种简单，可靠的备份和还原解决方案，通过利用针对特定数据库需求进行了优化的算法，可以无缝地扩展到最大的数据库和工作负载。 pgBackRest v2.27是...
pghoard：PostgreSQL备份和还原服务
02-05
- **实时备份**：`pghoard` 使用PostgreSQL的`wal2json`扩展进行连续日志流复制，以实现近乎实时的备份。这使得即使在大规模事务处理的环境下，也能保持备份的最新状态。 - **压缩与加密**：备份数据会被压缩，以...
postgresql-backup:自动化PostgreSQL备份和还原
04-07
PostgreSQL备份该gem可自动在您的Rails项目中执行PostgreSQL备份和还原。 通过使用本地系统或AWS S3存储，它将注入两个可用于管理数据的瑞克任务。入门将gem添加到您的Rails项目中： gem 'postgresql-backup' 转到...
Postgresql大象数据库还原文件
03-12
### PostgreSQL 大象数据库还原与配置详解 ...通过以上步骤，您可以成功地在PostgreSQL中创建登录角色、表空间、数据库，还原备份文件，并允许远程访问。这些操作是管理和维护大型PostgreSQL数据库的重要组成部分。
开源数据库MySQL DBA运维实战 第5章 备份
Lancelotest的博客
09-02 388
关于备份 备份的原因 防止因为系统或者人为的错误操作删除或损坏数据 备份的目标 1.数据的一致性 2.数据的可用性 备份技术 1.逻辑备份/热备份 备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL），适用于中小型数据库。 mysqldump,mydumper 效率相对较低 2.物理备份/冷备份 直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的MySQL版本。 tar,cp,scp跨设备复制文件 拷贝数据， 优点快，缺点服务停止。 备份方式 完
pgbackrest
喝醉酒的小白
04-03 1009
pgBackRest是一个可靠的备份和恢复工具，可以为PostgreSQL数据库提供快速和可靠的备份和还原。在进行恢复之前，还需要设置一些恢复参数，例如恢复目录、监听地址和端口等。可以在pgBackRest的配置文件中进行设置，或者在命令行中使用–set参数进行设置。恢复过程可能需要一段时间，具体时间取决于数据库的大小和恢复参数的设置。设置好恢复参数后，使用–delta参数可以进行增量恢复，即只恢复指定时间点之后的更改。其中，STANZA是指备份集的名称，可以在pgBackRest的配置文件中进行设置。
pg数据库之备份还原
weixin_55282974的博客
03-07 482
pg数据库之备份还原
pg备份和还原
menghuannvxia的专栏
03-09 1072
一、备份 1、cmd到PostgreSQL安装目录bin下 2、备份命令 pg_dump -h 192.168.100.23 -U postgres postgres > D:\postgres.bak 3、指令解释 如上命令， pg_dump 是备份数据库指令； 10.194.227.231是数据库的ip地址； postgres 是数据库的用户名； postgres 是数据库名； > 意思是导出到指定目录； 二、还原 1、恢复命令 psql -h localhost -U
【postgres】5、pgbackrest 冷备份介绍、命令、脚本
呆呆的猫的博客
10-27 749
pgbackrest 冷备份介绍、命令、脚本
使用pgbackrest进行远端备份和还原
乡下人
04-27 1390
​作者简介 Granthana Biswas cybertec公司工程师。 译者简介 王志斌，从事数据库产品相关工作，主要致力于postgresql数据库高可用解决方案及云端产品化工作。 校对者简介 李鑫,任职于海能达通信股份有限公司,数据库开发高级工程师,致力于postgresql数据库在专网通信领域、公共安全领域的应用与推广。 在上一篇关于pgBackRest的文章中，我们了解了如何安装和设置pgBackRest，并且为PostgreSQL进行一次备份。这是一个非常基本的单服务器设置，只是为
删库跑路不用怕，来看看pgbackrest最佳实践
青春作伴，爱情最好
02-27 1047
pgbackrest 备份工具 postgres ,备份最佳实践
PostgreSQL备份与恢复策略详解
"刘伟---云和恩墨--PostgreSQL备份与恢复的种种手段.pdf" 在PostgreSQL数据库管理系统中，备份与恢复是确保数据安全和高可用性的重要环节。本资料详细介绍了PostgreSQL的多种备份与恢复策略，包括物理备份与恢复、...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值