重庆思庄Oracle、Redhat认证学习论坛

 找回密码
 注册

QQ登录

只需一步,快速开始

搜索
查看: 983|回复: 0
打印 上一主题 下一主题

PostgreSQL备份恢复工具——WAL-G

[复制链接]
跳转到指定楼层
楼主
发表于 2024-3-17 14:56:46 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
目前开源的备份恢复工具有很多,比如pgbackrest、pg_probackup、barman等,这里我们讲一下另一个备份工具wal-g的使用。

安装部署
wal-g是采用Go语言实现的,所以,需要先安装Go环境,在进行编译安装。

安装go环境,参考Golang官网文档Download and install进行安装即可。
可编译安装部署,亦可直接下载安装包。这里我们采用编译安装的方式,可参考文档Installing
安装完成后,可–help检查一下是否安装成功。

postgres@slpc:~$ wal-g --help
PostgreSQL backup tool

Usage:
wal-g [command]

Available Commands:
backup-fetch  Fetches a backup from storage
backup-list   Prints full list of backups from which recovery is available
backup-mark   Marks a backup permanent or impermanent
backup-push   Makes backup and uploads it to storage
catchup-fetch Fetches an incremental backup from storage
catchup-list  Prints available incremental backups
catchup-push  Creates incremental backup from lsn
completion    Output shell completion code for the specified shell
copy          copy specific or all backups
daemon        Runs WAL-G in daemon mode which executes commands sent from the lightweight walg-daemon-client.
delete        Clears old backups and WALs
flags         Display the list of available global flags for all wal-g commands
help          Help about any command
pgbackrest    Interact with pgbackrest backups (beta)
st            (DANGEROUS) Storage tools
wal-fetch     Fetches a WAL file from storage
wal-push      Uploads a WAL file to storage
wal-receive   Receive WAL stream with postgres Streaming Replication Protocol and push to storage
wal-restore   Restores WAL segments from storage.
wal-show      Show storage WAL segments info grouped by timelines.
wal-verify    Verify WAL storage folder. Available checks: integrity, timeline.

Flags:
      --config string   config file (default is $HOME/.walg.json)
  -h, --help            help for wal-g
      --turbo           Ignore all kinds of throttling defined in config
  -v, --version         version for wal-g

To get the complete list of all global flags, run: 'wal-g flags'

Use "wal-g [command] --help" for more information about a command.
wal-g配置
wal-g有非常众多的配置,可通过环境变量进行配置或者使用配置文件,配置文件支持json、yaml以及viper package支持的配置文件格式。

我们以最常用的json以及yaml为例进行说明:

环境变量
通过环境变量进行配置的使用示例:

export WALG_COMPRESSION_METHOD=zstd   # 配置压缩算法
# 其他配置略...
json格式
{"WALG_COMPRESSION_METHOD": "lzma"}
使用方式,wal-g --config /configpath/*.json。测试示例如下:

postgres@slpc:~$ wal-g wal-push /home/postgres/pg15.5/pgdata/pg_wal/00000001000000000000000F --config /home/postgres/pgdata/wal-g.json
INFO: 2024/02/29 10:53:36.578807 Files will be uploaded to storage: default
INFO: 2024/02/29 10:53:37.385081 FILE PATH: 00000001000000000000000F.lzma
yaml格式
例如:

# cat /etc/wal-g/wal-g.yaml
PGDATABASE: "postgres"
WALE_S3_PREFIX: "s3://some/s3/prefix/"
WALG_NETWORK_RATE_LIMIT: 8388608
PGPASSFILE: "/home/gpadmin/.pgpass"
WALG_GP_LOGS_DIR: "/var/log/greenplum"
AWS_ACCESS_KEY_ID: "aws_access_key_id"
WALG_UPLOAD_CONCURRENCY: 5
WALG_PGP_KEY_PATH: "/path/to/PGP_KEY"
WALG_DOWNLOAD_CONCURRENCY: 5
WALG_DOWNLOAD_FILE_RETRIES: 15
WALE_GPG_KEY_ID: "gpg_key_id"
WALG_DISK_RATE_LIMIT: 167772160
PGUSER: "gpadmin"
GOMAXPROCS: 6
PGHOST: "localhost"
AWS_ENDPOINT: "https://s3-endpoint.host.name"
AWS_SECRET_ACCESS_KEY: "aws_secret_access_key"
WALG_COMPRESSION_METHOD: "brotli"
使用方式,wal-g --config /configpath/*.yaml。测试示例如下:

postgres@slpc:~$ wal-g wal-push /home/postgres/pg15.5/pgdata/pg_wal/00000001000000000000000F --config /home/postgres/pgdata/wal-g.yaml
INFO: 2024/02/29 11:08:24.272284 Files will be uploaded to storage: default
INFO: 2024/02/29 11:08:24.343777 FILE PATH: 00000001000000000000000F.br
备份
备份到本地盘
通过wal-g backup-push $PGDATA的方式进行备份,设置环境变量,设置备份目录WALG_FILE_PREFIX,设置数据库实例位置PGDATA。

export WALG_FILE_PREFIX=/home/postgres/pgdata/backup
export WALG_COMPRESSION_METHOD=zstd
export PGDATA=/home/postgres/pg15.5/pgdata
示例如下:

postgres@slpc:~/pgdata$ PGHOST=192.168.109.133 PGPASSWORD=postgres wal-g backup-push $PGDATA
INFO: 2024/03/04 14:00:44.531702 Backup will be pushed to storage: default
ERROR: 2024/03/04 14:00:44.564208 unexpected message type
ERROR: 2024/03/04 14:00:44.564269 Failed to connect using provided PGHOST and PGPORT, trying localhost:5432
ERROR: 2024/03/04 14:00:44.572534 Connect: postgres connection failed: FATAL: unrecognized configuration parameter "gp_role" (SQLSTATE 42704)
postgres@slpc:~/pgdata$ PGHOST=192.168.109.133 wal-g backup-push $PGDATA
INFO: 2024/03/04 14:01:36.195250 Backup will be pushed to storage: default
INFO: 2024/03/04 14:01:36.277066 Calling pg_start_backup()
INFO: 2024/03/04 14:01:36.336652 Initializing the PG alive checker (interval=1m0s)...
INFO: 2024/03/04 14:01:36.336740 Starting a new tar bundle
INFO: 2024/03/04 14:01:36.336767 Walking ...
INFO: 2024/03/04 14:01:36.337112 Starting part 1 ...
INFO: 2024/03/04 14:01:36.566550 Packing ...
INFO: 2024/03/04 14:01:36.568290 Finished writing part 1.
INFO: 2024/03/04 14:01:36.568363 Starting part 2 ...
INFO: 2024/03/04 14:01:36.568987 /global/pg_control
INFO: 2024/03/04 14:01:36.578094 Finished writing part 2.
INFO: 2024/03/04 14:01:36.578148 Calling pg_stop_backup()
INFO: 2024/03/04 14:01:36.626079 Starting part 3 ...
INFO: 2024/03/04 14:01:36.627065 backup_label
INFO: 2024/03/04 14:01:36.627130 tablespace_map
INFO: 2024/03/04 14:01:36.629653 Finished writing part 3.
INFO: 2024/03/04 14:01:36.630428 Querying pg_database
INFO: 2024/03/04 14:01:36.730815 Wrote backup with name base_00000001000000000000000A to storage default

我们看一下备份目录的文件:

postgres@slpc:~/pgdata/backup$ tree .
.
├── basebackups_005
│   ├── base_00000001000000000000000A
│   │   ├── files_metadata.json
│   │   ├── metadata.json
│   │   └── tar_partitions
│   │       ├── part_001.tar.zst
│   │       ├── part_003.tar.zst
│   │       └── pg_control.tar.zst
│   └── base_00000001000000000000000A_backup_stop_sentinel.json
├── wal_005
│   ├── 000000010000000000000001.zst
│   ├── 000000010000000000000002.00000028.backup.zst
│   ├── 000000010000000000000002.zst
│   ├── 000000010000000000000003.zst

恢复
数据库备份后可通过wal-g backup-fetch进行恢复。

用法示例:

wal-g backup-fetch bakmn LATEST
恢复日志如下:

postgres@slpc:~/pgdata$ wal-g backup-fetch bakmn LATEST
INFO: 2024/03/05 09:29:05.629353 Selecting the latest backup...
INFO: 2024/03/05 09:29:05.629786 Backup to fetch will be searched in storages: [default]
INFO: 2024/03/05 09:29:05.630010 LATEST backup is: 'base_00000001000000000000000A'
INFO: 2024/03/05 09:29:05.647958 Finished extraction of part_003.tar.zst
INFO: 2024/03/05 09:29:07.259976 Finished extraction of part_001.tar.zst
INFO: 2024/03/05 09:29:07.262729 Finished extraction of pg_control.tar.zst
INFO: 2024/03/05 09:29:07.262782
Backup extraction complete.
在启动前创建recovery.signal文件

postgres@slpc:~/pgdata/bakmn$ touch recovery.signal
然后启动数据库

postgres@slpc:~/pgdata$ pg_ctl start -D bakmn
server started
恢复成功。我们验证一下:

postgres@slpc:~/pgdata/backup$ psql     --连接数据库
psql (15.5)
Type "help" for help.

postgres=# select * from t1;
a
---
1
(1 row)

postgres=# insert into t1 values(2);   -- 插入数据
INSERT 0 1
postgres=# select pg_switch_wal();    -- 触发日志归档
pg_switch_wal
---------------
0/D0001E8
(1 row)

postgres=# \q
postgres@slpc:~/pgdata/backup$ ls
basebackups_005  wal_005  wal-g.log
postgres@slpc:~/pgdata/backup$ tree wal_005/   -- 查看归档日志目录
wal_005/
├── 000000010000000000000001.zst
├── 000000010000000000000002.00000028.backup.zst
├── 000000010000000000000002.zst
├── 000000010000000000000003.zst
├── 000000010000000000000004.00000028.backup.zst
├── 000000010000000000000004.zst
├── 000000010000000000000005.zst
├── 000000010000000000000006.zst
├── 000000010000000000000007.zst
├── 000000010000000000000008.zst
├── 000000010000000000000009.zst
├── 00000001000000000000000A.00000028.backup.zst
├── 00000001000000000000000A.zst
├── 00000001000000000000000B.zst
├── 00000001000000000000000C.zst
├── 00000002000000000000000D.zst             -- 新的归档日志,时间线已切为2
└── 00000002.history.zst                     -- 时间线历史文件
每次恢复数据库,其时间线都会加1。 时间线用于区分原始数据库和恢复生成的数据库集簇,是PITR的核心概念。

增量备份
设置环境变量

export WALG_DELTA_ORIGIN=LATEST_FULL
执行增量备份

postgres@slpc:~$ wal-g backup-push $PGDATA --delta-from-name base_000000020000000000000024
INFO: 2024/03/05 16:33:02.689154 Backup will be pushed to storage: default
INFO: 2024/03/05 16:33:02.698428 Selecting the backup with name base_000000020000000000000024 as the base for the current delta backup...
INFO: 2024/03/05 16:33:02.956011 Delta will be made from full backup.
INFO: 2024/03/05 16:33:02.968992 Delta backup from base_000000020000000000000024 with LSN 0/24000028.
INFO: 2024/03/05 16:33:03.075391 Calling pg_start_backup()
INFO: 2024/03/05 16:33:03.093341 Initializing the PG alive checker (interval=1m0s)...
INFO: 2024/03/05 16:33:03.093443 Delta backup enabled
INFO: 2024/03/05 16:33:03.093481 Starting a new tar bundle
INFO: 2024/03/05 16:33:03.093510 Walking ...
INFO: 2024/03/05 16:33:03.094918 Starting part 1 ...
INFO: 2024/03/05 16:33:03.128718 Packing ...
INFO: 2024/03/05 16:33:03.129758 Finished writing part 1.
INFO: 2024/03/05 16:33:03.163413 Starting part 2 ...
INFO: 2024/03/05 16:33:03.164104 /global/pg_control
INFO: 2024/03/05 16:33:03.176944 Finished writing part 2.
INFO: 2024/03/05 16:33:03.176973 Calling pg_stop_backup()
INFO: 2024/03/05 16:33:03.226442 Starting part 3 ...
INFO: 2024/03/05 16:33:03.227771 backup_label
INFO: 2024/03/05 16:33:03.227807 tablespace_map
INFO: 2024/03/05 16:33:03.233816 Finished writing part 3.
INFO: 2024/03/05 16:33:03.250012 Querying pg_database
INFO: 2024/03/05 16:33:03.553529 Wrote backup with name base_000000020000000000000029_D_000000020000000000000024 to storage default
查看备份文件

postgres@slpc:~$ wal-g st ls basebackups_005
type size last modified                     name
dir  0    0001-01-01 00:00:00 +0000 UTC     base_000000020000000000000024/
dir  0    0001-01-01 00:00:00 +0000 UTC     base_000000020000000000000029_D_000000020000000000000024/
obj  398  2024-03-05 06:55:53.786 +0000 UTC base_000000020000000000000024_backup_stop_sentinel.json
obj  522  2024-03-05 08:33:03.501 +0000 UTC base_000000020000000000000029_D_000000020000000000000024_backup_stop_sentinel.json
再次执行备份

postgres@slpc:~$ wal-g backup-push $PGDATA --delta-from-name base_000000020000000000000029_D_000000020000000000000024
INFO: 2024/03/05 16:49:52.073832 Backup will be pushed to storage: default
INFO: 2024/03/05 16:49:52.073934 Selecting the backup with name base_000000020000000000000029_D_000000020000000000000024 as the base for the current delta backup...
INFO: 2024/03/05 16:49:52.349094 Delta will be made from full backup.
INFO: 2024/03/05 16:49:52.362781 Delta backup from base_000000020000000000000024 with LSN 0/24000028.
INFO: 2024/03/05 16:49:52.443188 Calling pg_start_backup()
INFO: 2024/03/05 16:49:52.547250 Initializing the PG alive checker (interval=1m0s)...
INFO: 2024/03/05 16:49:52.547356 Delta backup enabled
INFO: 2024/03/05 16:49:52.547394 Starting a new tar bundle
INFO: 2024/03/05 16:49:52.547448 Walking ...
INFO: 2024/03/05 16:49:52.547831 Starting part 1 ...
INFO: 2024/03/05 16:49:52.585035 Packing ...
INFO: 2024/03/05 16:49:52.588192 Finished writing part 1.
INFO: 2024/03/05 16:49:52.631401 Starting part 2 ...
INFO: 2024/03/05 16:49:52.641163 /global/pg_control
INFO: 2024/03/05 16:49:52.650529 Finished writing part 2.
INFO: 2024/03/05 16:49:52.650607 Calling pg_stop_backup()
INFO: 2024/03/05 16:49:52.694601 Starting part 3 ...
INFO: 2024/03/05 16:49:52.697699 backup_label
INFO: 2024/03/05 16:49:52.697793 tablespace_map
INFO: 2024/03/05 16:49:53.183120 Finished writing part 3.
INFO: 2024/03/05 16:49:53.341786 Querying pg_database
INFO: 2024/03/05 16:49:53.730008 Wrote backup with name base_00000002000000000000002C_D_000000020000000000000024 to storage default
postgres@slpc:~$ wal-g st ls basebackups_005
type size last modified                     name
dir  0    0001-01-01 00:00:00 +0000 UTC     base_000000020000000000000024/
dir  0    0001-01-01 00:00:00 +0000 UTC     base_000000020000000000000029_D_000000020000000000000024/
dir  0    0001-01-01 00:00:00 +0000 UTC     base_00000002000000000000002C_D_000000020000000000000024/
obj  398  2024-03-05 06:55:53.786 +0000 UTC base_000000020000000000000024_backup_stop_sentinel.json
obj  522  2024-03-05 08:33:03.501 +0000 UTC base_000000020000000000000029_D_000000020000000000000024_backup_stop_sentinel.json
obj  522  2024-03-05 08:49:53.712 +0000 UTC base_00000002000000000000002C_D_000000020000000000000024_backup_stop_sentinel.json
postgres@slpc:~$ wal-g backup-list
INFO: 2024/03/05 16:59:06.308660 List backups from storages: [default]
backup_name                                              modified             wal_file_name            storage_name
base_000000020000000000000024                            2024-03-05T06:55:53Z 000000020000000000000024 default
base_000000020000000000000029_D_000000020000000000000024 2024-03-05T08:33:03Z 000000020000000000000029 default
base_00000002000000000000002C_D_000000020000000000000024 2024-03-05T08:49:53Z 00000002000000000000002C default
存储
wal-g可备份到S3、Google Cloud Storage、Azure、Swift、远端主机、本地盘

本地盘
可通过WALG_FILE_PREFIX进行配置。例如归档日志到某个目录:

archive_command = 'export WALG_FILE_PREFIX=/home/postgres/pgdata/archive; wal-g wal-push %p'
一般生产情况下,不会采用这种方式。建议归档到S3。

远端主机
可通过WALG_SSH_PREFIX配置归档或者备份到其他远端主机。

WALG_SSH_PREFIX (e.g. ssh://localhost/walg-folder)
SSH_PORT ssh connection port
SSH_USERNAME connect with username
SSH_PASSWORD connect with password
SSH_PRIVATE_KEY_PATH or connect with a SSH KEY by specifying its full path
示例环境变量配置如下:

export WALG_SSH_PREFIX=ssh://192.168.109.136/home/postgres/pgdata/backup   # 备份到192.168.109.136机器的home/postgres/pgdata/backup 目录下
export SSH_USERNAME=postgres
export SSH_PASSWORD=postgres
export SSH_PORT=22
export WALG_COMPRESSION_METHOD=zstd
进行备份:

postgres@slpc:~$ wal-g backup-push $PGDATA --full
INFO: 2024/03/05 14:30:06.664883 Backup will be pushed to storage: default
INFO: 2024/03/05 14:30:06.701011 Doing full backup.
INFO: 2024/03/05 14:30:06.733931 Calling pg_start_backup()
INFO: 2024/03/05 14:30:06.810888 Initializing the PG alive checker (interval=1m0s)...
INFO: 2024/03/05 14:30:06.811014 Starting a new tar bundle
INFO: 2024/03/05 14:30:06.811053 Walking ...
INFO: 2024/03/05 14:30:06.811514 Starting part 1 ...
INFO: 2024/03/05 14:30:08.067431 Packing ...
INFO: 2024/03/05 14:30:08.069307 Finished writing part 1.
INFO: 2024/03/05 14:30:08.071450 Starting part 2 ...
INFO: 2024/03/05 14:30:08.072180 /global/pg_control
INFO: 2024/03/05 14:30:08.075420 Finished writing part 2.
INFO: 2024/03/05 14:30:08.075463 Calling pg_stop_backup()
INFO: 2024/03/05 14:30:08.265699 Starting part 3 ...
INFO: 2024/03/05 14:30:08.266629 backup_label
INFO: 2024/03/05 14:30:08.266706 tablespace_map
INFO: 2024/03/05 14:30:08.289423 Finished writing part 3.
INFO: 2024/03/05 14:30:08.296357 Querying pg_database
INFO: 2024/03/05 14:30:08.495505 Wrote backup with name base_00000002000000000000001D to storage default
postgres@slpc:~$ wal-g backup-list
INFO: 2024/03/05 14:30:27.814430 List backups from storages: [default]
backup_name                   modified                  wal_file_name            storage_name
base_00000002000000000000001D 2024-03-05T14:30:08+08:00 00000002000000000000001D default
登录192.168。109.136机器,查看备份文件

postgres@slpc:~/pgdata/backup$ tree .
.
├── basebackups_005
│   ├── base_00000002000000000000001D
│   │   ├── files_metadata.json
│   │   ├── metadata.json
│   │   └── tar_partitions
│   │       ├── part_001.tar.zst
│   │       ├── part_003.tar.zst
│   │       └── pg_control.tar.zst
│   └── base_00000002000000000000001D_backup_stop_sentinel.json
└── wal_005
    ├── 00000002000000000000001B.zst
    ├── 00000002000000000000001C.zst
    ├── 00000002000000000000001D.00000028.backup.zst
    └── 00000002000000000000001D.zst

4 directories, 10 files
S3
建议将备份以及日志归档到S3中,成本以及可靠性等均有优势。
需要配置WALG_S3_PREFIX为S3。

用法示例:

export WALG_S3_PREFIX=s3://pgbackup
export AWS_ENDPOINT=eos-shanghai-4.cmecloud.cn
export AWS_SECRET_ACCESS_KEY="ccNPsAeO0FtBIl90RqZgAG8O098GpNnel1BAWa4G"
export AWS_ACCESS_KEY_ID="IP51OA2Y0629WJH9PEB9"
postgres@slpc:~$ wal-g backup-push $PGDATA
INFO: 2024/03/05 14:55:51.787825 Backup will be pushed to storage: default
INFO: 2024/03/05 14:55:51.859070 Calling pg_start_backup()
INFO: 2024/03/05 14:55:51.925236 Initializing the PG alive checker (interval=1m0s)...
INFO: 2024/03/05 14:55:51.925473 Starting a new tar bundle
INFO: 2024/03/05 14:55:51.926138 Walking ...
INFO: 2024/03/05 14:55:51.926573 Starting part 1 ...
INFO: 2024/03/05 14:55:52.371235 Packing ...
INFO: 2024/03/05 14:55:52.372979 Finished writing part 1.
INFO: 2024/03/05 14:55:53.012767 Starting part 2 ...
INFO: 2024/03/05 14:55:53.020235 /global/pg_control
INFO: 2024/03/05 14:55:53.025917 Finished writing part 2.
INFO: 2024/03/05 14:55:53.025981 Calling pg_stop_backup()
INFO: 2024/03/05 14:55:53.073788 Starting part 3 ...
INFO: 2024/03/05 14:55:53.075182 backup_label
INFO: 2024/03/05 14:55:53.075239 tablespace_map
INFO: 2024/03/05 14:55:53.114629 Finished writing part 3.
INFO: 2024/03/05 14:55:53.175671 Querying pg_database
INFO: 2024/03/05 14:55:53.795401 Wrote backup with name base_000000020000000000000024 to storage default
postgres@slpc:~$ wal-g backup-list
INFO: 2024/03/05 14:57:04.216972 List backups from storages: [default]
backup_name                   modified             wal_file_name            storage_name
base_000000020000000000000024 2024-03-05T06:55:53Z 000000020000000000000024 default
然后就可以登录S3,可以是云厂商的对象存储,也可以自己用minio搭建对象存储。查看其中的备份以及归档文件。
image.png

日志归档
使用wal-g进行日志归档,并进行恢复。wal-g支持归档到S3,需要进行配置。

归档
以pg15版本为例,配置如下:

archive_mode = on               # enables archiving; off, on, or always
archive_command = 'export WALG_FILE_PREFIX=/home/postgres/pgdata/archive; wal-g wal-push %p'
归档当指定目录,可以看到默认情况下是进行压缩了的,原有的16M文件被压缩为了2M。对归档日志进行压缩不但能够节约存储的成本,也能在涉及到I/O操作,网络传输等场景降低代价。

postgres@slpc:~/pgdata/archive$ ls
wal_005
postgres@slpc:~/pgdata/archive$ cd wal_005/
postgres@slpc:~/pgdata/archive/wal_005$ ls
000000010000000000000001.lz4
postgres@slpc:~/pgdata/archive/wal_005$ du -h
2.0M        .
恢复
通过wal-g wal-fetch进行恢复,日志恢复有个功能,就是预取功能,可以在请求指定WAL日志前先预先fetch日志到./.wal-g/prefetch文件下,当缓存的日志文件比当前需要的WAL日志老时,就可以被删除了,以防止缓存日志膨胀。如果缓存的日志文件被wal-fetch请求,那么请求后日志从缓存中删除然后触发预取新的缓存日志。

示例:

restore_command = 'wal-g wal-fetch %f %p >> /home/postgres/pgdata/backup/wal-g.log 2>&1'
备机执行归档恢复日志如下:

2024-03-04 11:20:17.306 CST [11149] DEBUG:  executing restore command "wal-g wal-fetch 000000010000000000000004 pg_wal/RECOVERYXLOG >> /home/postgres/pgdata/backup/wal-g.log 2>&1"
2024-03-04 11:20:17.475 CST [11149] LOG:  restored log file "000000010000000000000004" from archive
2024-03-04 11:20:17.514 CST [11149] DEBUG:  got WAL segment from archive
2024-03-04 11:20:17.514 CST [11149] DEBUG:  checkpoint record is at 0/4000060
压缩
压缩通过环境变量WALG_COMPRESSION_METHOD进行配置。支持如下压缩算法:

lz4 默认压缩算法是lz4,压缩速度最快,但是压缩比较低
lzma 压缩速度慢,但是压缩比是lz4的6倍
zstd 压缩速度和压缩比较为均衡,速度比lz4略慢,但是压缩比比lz4高
brotli 压缩速度和压缩比较为均衡
配置示例,以zstd压缩算法为例,配置环境变量WALG_COMPRESSION_METHOD

export WALG_COMPRESSION_METHOD=zstd
我们看一下对WAL日志归档文件的压缩,我们向表中插入一行数据,然后select pg_switch_wal();进行日志切换进行归档,16M数据被压缩为8k。

postgres@slpc:~/pgdata/archive$ ls
wal_005
postgres@slpc:~/pgdata/archive$ cd wal_005/
postgres@slpc:~/pgdata/archive/wal_005$ ls
000000010000000000000003.zst
postgres@slpc:~/pgdata/archive/wal_005$ du -h
8.0K        .
加密
略,详见 https://wal-g.readthedocs.io/

监控
略,详见https://wal-g.readthedocs.io/

其他命令
wal-show
可使用wal-g wal-show查看备份情况

postgres@slpc:~/pgdata/backup$ wal-g wal-show
+-----+------------+-----------------+--------------------------+--------------------------+---------------+----------------+--------+---------------+
| TLI | PARENT TLI | SWITCHPOINT LSN | START SEGMENT            | END SEGMENT              | SEGMENT RANGE | SEGMENTS COUNT | STATUS | BACKUPS COUNT |
+-----+------------+-----------------+--------------------------+--------------------------+---------------+----------------+--------+---------------+
|   1 |          0 |             0/0 | 000000010000000000000001 | 00000001000000000000000C |            12 |             12 | OK     |             1 |
|   2 |          1 |       0/D000000 | 00000002000000000000000D | 000000020000000000000011 |             5 |              5 | OK     |             1 |
+-----+------------+-----------------+--------------------------+--------------------------+---------------+----------------+--------+---------------+
wal-verify
wal-g wal-verify integrity用于验证WAL日志段文件是否完整,可执行PITR。

postgres@slpc:~/pgdata/backup$ wal-g wal-verify integrity
INFO: 2024/03/05 09:45:07.637578 Current WAL segment: 00000002000000000000000D
INFO: 2024/03/05 09:45:07.640460 Building check runner: integrity
INFO: 2024/03/05 09:45:07.641946 Detected earliest available backup: base_00000001000000000000000A
INFO: 2024/03/05 09:45:07.642015 Running the check: integrity
[wal-verify] integrity check status: OK
[wal-verify] integrity check details:
+-----+--------------------------+--------------------------+----------------+--------+
| TLI | START                    | END                      | SEGMENTS COUNT | STATUS |
+-----+--------------------------+--------------------------+----------------+--------+
|   1 | 00000001000000000000000A | 00000001000000000000000C |              3 |  FOUND |
+-----+--------------------------+--------------------------+----------------+--------+
wal-g wal-verify timeline用于检查timeline信息

postgres@slpc:~/pgdata/backup$ wal-g wal-verify timeline
INFO: 2024/03/05 10:01:01.236100 Current WAL segment: 00000002000000000000000E
INFO: 2024/03/05 10:01:01.239507 Building check runner: timeline
INFO: 2024/03/05 10:01:01.239586 Running the check: timeline
WARNING: 2024/03/05 10:01:01.239683 Could not parse the timeline Id from 000000010000000000000002.00000028.backup.zst. Skipping...
WARNING: 2024/03/05 10:01:01.239732 Could not parse the timeline Id from 000000010000000000000004.00000028.backup.zst. Skipping...
WARNING: 2024/03/05 10:01:01.239758 Could not parse the timeline Id from 00000001000000000000000A.00000028.backup.zst. Skipping...
[wal-verify] timeline check status: OK
[wal-verify] timeline check details:
Highest timeline found in storage: 2
Current cluster timeline: 2
backup-list
列出当前可用的备份

postgres@slpc:~/pgdata$ wal-g backup-push $PGDATA    # 新增备份
INFO: 2024/03/05 10:42:36.461264 Backup will be pushed to storage: default
INFO: 2024/03/05 10:42:36.526088 Calling pg_start_backup()
INFO: 2024/03/05 10:42:36.579452 Initializing the PG alive checker (interval=1m0s)...
INFO: 2024/03/05 10:42:36.579508 Starting a new tar bundle
INFO: 2024/03/05 10:42:36.579570 Walking ...
INFO: 2024/03/05 10:42:36.579922 Starting part 1 ...
INFO: 2024/03/05 10:42:36.804335 Packing ...
INFO: 2024/03/05 10:42:36.805626 Finished writing part 1.
INFO: 2024/03/05 10:42:36.805702 Starting part 2 ...
INFO: 2024/03/05 10:42:36.806202 /global/pg_control
INFO: 2024/03/05 10:42:36.818013 Finished writing part 2.
INFO: 2024/03/05 10:42:36.818063 Calling pg_stop_backup()
INFO: 2024/03/05 10:42:36.864683 Starting part 3 ...
INFO: 2024/03/05 10:42:36.865665 backup_label
INFO: 2024/03/05 10:42:36.865721 tablespace_map
INFO: 2024/03/05 10:42:36.868750 Finished writing part 3.
INFO: 2024/03/05 10:42:36.869898 Querying pg_database
INFO: 2024/03/05 10:42:36.963805 Wrote backup with name base_000000020000000000000011 to storage default
postgres@slpc:~/pgdata$ wal-g backup-list   # 查看当前可用的备份
INFO: 2024/03/05 10:42:49.146926 List backups from storages: [default]
backup_name                   modified                  wal_file_name            storage_name
base_00000001000000000000000A 2024-03-04T14:01:36+08:00 00000001000000000000000A default
base_000000020000000000000011 2024-03-05T10:42:36+08:00 000000020000000000000011 default    # 新增的备份
postgres@slpc:~/pgdata$ wal-g backup-list --detail
INFO: 2024/03/05 10:45:20.010794 List backups from storages: [default]
backup_name                   modified                  wal_file_name            storage_name start_time           finish_time          hostname data_dir                    pg_version start_lsn  finish_lsn is_permanent
base_00000001000000000000000A 2024-03-04T14:01:36+08:00 00000001000000000000000A default      2024-03-04T06:01:36Z 2024-03-04T06:01:36Z slpc     /home/postgres/pgdata/mn    150005     0/A000028  0/A000100  false
base_000000020000000000000011 2024-03-05T10:42:36+08:00 000000020000000000000011 default      2024-03-05T02:42:36Z 2024-03-05T02:42:36Z slpc     /home/postgres/pgdata/bakmn 150005     0/11000028 0/11000138 false
delete
用于删除归档日志,删除备份文件

比如我们仅保留一份备份,删除其他的备份,可执行wal-g delete retain full 1,结果如下

postgres@slpc:~/pgdata/backup$ wal-g delete retain full 1
INFO: 2024/03/05 10:51:42.833707 Backup to delete will be searched in storages: [default]
INFO: 2024/03/05 10:51:42.833892 retrieving permanent objects
INFO: 2024/03/05 10:51:42.834811 Start delete
INFO: 2024/03/05 10:51:42.835484 Objects in folder:
INFO: 2024/03/05 10:51:42.835596         will be deleted: basebackups_005/base_00000001000000000000000A_backup_stop_sentinel.json, from storage: default
INFO: 2024/03/05 10:51:42.835608         will be deleted: wal_005/000000010000000000000001.zst, from storage: default
INFO: 2024/03/05 10:51:42.835614         will be deleted: wal_005/000000010000000000000002.00000028.backup.zst, from storage: default
INFO: 2024/03/05 10:51:42.835622         will be deleted: wal_005/000000010000000000000002.zst, from storage: default
INFO: 2024/03/05 10:51:42.835633         will be deleted: wal_005/000000010000000000000003.zst, from storage: default
INFO: 2024/03/05 10:51:42.835642         will be deleted: wal_005/000000010000000000000004.00000028.backup.zst, from storage: default
INFO: 2024/03/05 10:51:42.835647         will be deleted: wal_005/000000010000000000000004.zst, from storage: default
INFO: 2024/03/05 10:51:42.835651         will be deleted: wal_005/000000010000000000000005.zst, from storage: default
INFO: 2024/03/05 10:51:42.835656         will be deleted: wal_005/000000010000000000000006.zst, from storage: default
INFO: 2024/03/05 10:51:42.835661         will be deleted: wal_005/000000010000000000000007.zst, from storage: default
INFO: 2024/03/05 10:51:42.835667         will be deleted: wal_005/000000010000000000000008.zst, from storage: default
INFO: 2024/03/05 10:51:42.835671         will be deleted: wal_005/000000010000000000000009.zst, from storage: default
INFO: 2024/03/05 10:51:42.835676         will be deleted: wal_005/00000001000000000000000A.00000028.backup.zst, from storage: default
INFO: 2024/03/05 10:51:42.835681         will be deleted: wal_005/00000001000000000000000A.zst, from storage: default
INFO: 2024/03/05 10:51:42.835702         will be deleted: wal_005/00000001000000000000000B.zst, from storage: default
INFO: 2024/03/05 10:51:42.835709         will be deleted: wal_005/00000001000000000000000C.zst, from storage: default
INFO: 2024/03/05 10:51:42.835715         will be deleted: wal_005/00000002000000000000000D.zst, from storage: default
INFO: 2024/03/05 10:51:42.835727         will be deleted: wal_005/00000002000000000000000E.zst, from storage: default
INFO: 2024/03/05 10:51:42.835736         will be deleted: wal_005/00000002000000000000000F.zst, from storage: default
INFO: 2024/03/05 10:51:42.835741         will be deleted: wal_005/000000020000000000000010.zst, from storage: default
INFO: 2024/03/05 10:51:42.835751         will be deleted: basebackups_005/base_00000001000000000000000A/files_metadata.json, from storage: default
INFO: 2024/03/05 10:51:42.835798         will be deleted: basebackups_005/base_00000001000000000000000A/metadata.json, from storage: default
INFO: 2024/03/05 10:51:42.835826         will be deleted: basebackups_005/base_00000001000000000000000A/tar_partitions/part_001.tar.zst, from storage: default
INFO: 2024/03/05 10:51:42.835860         will be deleted: basebackups_005/base_00000001000000000000000A/tar_partitions/part_003.tar.zst, from storage: default
INFO: 2024/03/05 10:51:42.835877         will be deleted: basebackups_005/base_00000001000000000000000A/tar_partitions/pg_control.tar.zst, from storage: default
INFO: 2024/03/05 10:51:42.835907 Dry run, nothing were deleted
可以看到,并没有实际删除备份,这是因为删除备份,日志归档等文件是十分谨慎的,为了防止误操作以及用户确认删除的文件信息。

postgres@slpc:~/pgdata/backup$ wal-g backup-list
INFO: 2024/03/05 10:52:11.011245 List backups from storages: [default]
backup_name                   modified                  wal_file_name            storage_name
base_00000001000000000000000A 2024-03-04T14:01:36+08:00 00000001000000000000000A default
base_000000020000000000000011 2024-03-05T10:42:36+08:00 000000020000000000000011 default
我们需要执行wal-g delete retain full 1 --confirm才实际执行删除动作。

postgres@slpc:~/pgdata/backup$ wal-g delete retain full 1 --confirm
INFO: 2024/03/05 10:57:15.929301 Backup to delete will be searched in storages: [default]
INFO: 2024/03/05 10:57:15.929458 retrieving permanent objects
INFO: 2024/03/05 10:57:15.930468 Start delete
INFO: 2024/03/05 10:57:15.931124 Objects in folder:
INFO: 2024/03/05 10:57:15.931287         will be deleted: basebackups_005/base_00000001000000000000000A_backup_stop_sentinel.json, from storage: default
INFO: 2024/03/05 10:57:15.931315         will be deleted: wal_005/000000010000000000000001.zst, from storage: default
INFO: 2024/03/05 10:57:15.931336         will be deleted: wal_005/000000010000000000000002.00000028.backup.zst, from storage: default
INFO: 2024/03/05 10:57:15.931354         will be deleted: wal_005/000000010000000000000002.zst, from storage: default
INFO: 2024/03/05 10:57:15.931394         will be deleted: wal_005/000000010000000000000003.zst, from storage: default
INFO: 2024/03/05 10:57:15.931438         will be deleted: wal_005/000000010000000000000004.00000028.backup.zst, from storage: default
INFO: 2024/03/05 10:57:15.931452         will be deleted: wal_005/000000010000000000000004.zst, from storage: default
INFO: 2024/03/05 10:57:15.931467         will be deleted: wal_005/000000010000000000000005.zst, from storage: default
INFO: 2024/03/05 10:57:15.931476         will be deleted: wal_005/000000010000000000000006.zst, from storage: default
INFO: 2024/03/05 10:57:15.931490         will be deleted: wal_005/000000010000000000000007.zst, from storage: default
INFO: 2024/03/05 10:57:15.931527         will be deleted: wal_005/000000010000000000000008.zst, from storage: default
INFO: 2024/03/05 10:57:15.931539         will be deleted: wal_005/000000010000000000000009.zst, from storage: default
INFO: 2024/03/05 10:57:15.931550         will be deleted: wal_005/00000001000000000000000A.00000028.backup.zst, from storage: default
INFO: 2024/03/05 10:57:15.931559         will be deleted: wal_005/00000001000000000000000A.zst, from storage: default
INFO: 2024/03/05 10:57:15.931568         will be deleted: wal_005/00000001000000000000000B.zst, from storage: default
INFO: 2024/03/05 10:57:15.931578         will be deleted: wal_005/00000001000000000000000C.zst, from storage: default
INFO: 2024/03/05 10:57:15.931590         will be deleted: wal_005/00000002000000000000000D.zst, from storage: default
INFO: 2024/03/05 10:57:15.931602         will be deleted: wal_005/00000002000000000000000E.zst, from storage: default
INFO: 2024/03/05 10:57:15.931611         will be deleted: wal_005/00000002000000000000000F.zst, from storage: default
INFO: 2024/03/05 10:57:15.931620         will be deleted: wal_005/000000020000000000000010.zst, from storage: default
INFO: 2024/03/05 10:57:15.931642         will be deleted: basebackups_005/base_00000001000000000000000A/files_metadata.json, from storage: default
INFO: 2024/03/05 10:57:15.931653         will be deleted: basebackups_005/base_00000001000000000000000A/metadata.json, from storage: default
INFO: 2024/03/05 10:57:15.931677         will be deleted: basebackups_005/base_00000001000000000000000A/tar_partitions/part_001.tar.zst, from storage: default
INFO: 2024/03/05 10:57:15.931690         will be deleted: basebackups_005/base_00000001000000000000000A/tar_partitions/part_003.tar.zst, from storage: default
INFO: 2024/03/05 10:57:15.931703         will be deleted: basebackups_005/base_00000001000000000000000A/tar_partitions/pg_control.tar.zst, from storage: default
postgres@slpc:~/pgdata/backup$ wal-g backup-list
INFO: 2024/03/05 10:57:19.467593 List backups from storages: [default]
backup_name                   modified                  wal_file_name            storage_name
base_000000020000000000000011 2024-03-05T10:42:36+08:00 000000020000000000000011 default
登录后复制
可以看到备份文件被删除掉。

分享到:  QQ好友和群QQ好友和群 QQ空间QQ空间 腾讯微博腾讯微博 腾讯朋友腾讯朋友
收藏收藏 支持支持 反对反对
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

QQ|手机版|小黑屋|重庆思庄Oracle、Redhat认证学习论坛 ( 渝ICP备12004239号-4 )

GMT+8, 2024-12-27 04:50 , Processed in 0.086574 second(s), 19 queries .

重庆思庄学习中心论坛-重庆思庄科技有限公司论坛

© 2001-2020

快速回复 返回顶部 返回列表