备份说明
Pgbackrest是一个强大的PostgreSQL备份和恢复工具，它使用增量备份策略。
增量备份（incr）备份自上次全量或增量或差异备份之后更改的数据。这意味着每次备份只会备份新增或修改过的数据块，从而减少了备份所需的时间和存储空间。在Pgbackrest中，每个增量备份都建立在上一个完整备份或增量备份的基础上。
差异备份（diff）指每次都备份自上次全量备份之后更改的数据。因此，差异备份需要更多的时间和存储空间，但在恢复时可能比增量备份更快一些。
总结起来，Pgbackrest使用增量备份策略来有效地备份和恢复PostgreSQL数据库。
1-- 创建全量备份。
2pgbackrest --stanza=postgres --log-level-console=info backup
3
4-- 执行增量备份。
5pgbackrest --stanza=postgres --log-level-console=info --type=incr backup
6
7
8-- 执行差异备份。
9pgbackrest --stanza=postgres --log-level-console=info --type=diff backup
备份脚本分享
1-- 备份策略 ： 周六全量，周3做差异备份，其它时间做增量备份，都在凌晨1点进行
2
3
4cat > /home/pg15/bk_pg15.sh <<"EOF"
5#!/bin/bash
6
7
8source ~/.bash_profile
9
10# 获取当前星期几，0=周日，1=周一，2=周二，3=周三，4=周四，5=周五，6=周六
11WEEKDAY=$(date +%u)
12
13# 备份路径和日志级别
14STANZA="pg15"
15LOG_LEVEL="info"
16
17# 选择备份类型
18if [ "$WEEKDAY" -eq 6 ]; then
19 # 周六，全量备份
20 BACKUP_TYPE="full"
21elif [ "$WEEKDAY" -eq 3 ]; then
22 # 周三，差异备份
23 BACKUP_TYPE="diff"
24else
25 # 其它时间，增量备份
26 BACKUP_TYPE="incr"
27fi
28
29# 执行备份
30pgbackrest --stanza=$STANZA --log-level-console=$LOG_LEVEL backup --type=$BACKUP_TYPE
31
32
33sh /home/pg15/pgbackrest_table.sh $STANZA
34
35EOF
36
37
38chmod +x /home/pg15/bk_pg15.sh
39
40
410 1 * * * /home/postgres/bk_pg15.sh > /tmp/bk_pg15.log
备份结果
1[root@lhrpgalloe /]# pgbackrest_table.sh pg16
2|------------------------------------------|--------|---------------------|---------------------|------------|------------|----------------------|----------------------|------------------|------------------|--------------------------|--------------------------|------------------------------------------------------------------------------------------------------|
3| Label | Type | Start Timestamp | Stop Timestamp | DB Size | DB bk Size | Repo bks_all Size | Repo bks Size | Start LSN | Stop LSN | Archive Start | Archive Stop | Reference |
4|------------------------------------------|--------|---------------------|---------------------|------------|------------|----------------------|----------------------|------------------|------------------|--------------------------|--------------------------|------------------------------------------------------------------------------------------------------|
5| 20241030-093035F | full | 2024-10-30 09:30:35 | 2024-10-30 09:30:53 | 588MB | 588MB | 180MB | 180MB | 1/D5000028 | 1/D5000138 | 0000000300000001000000D5 | 0000000300000001000000D5 | |
6| 20241030-093114F | full | 2024-10-30 09:31:14 | 2024-10-30 09:31:32 | 588MB | 588MB | 180MB | 180MB | 1/D7000028 | 1/D7000138 | 0000000300000001000000D7 | 0000000300000001000000D7 | |
7| 20241030-093114F_20241030-093150D | diff | 2024-10-30 09:31:50 | 2024-10-30 09:31:51 | 588MB | 9.5KB | 180MB | 798B | 1/D9000028 | 1/D9000100 | 0000000300000001000000D9 | 0000000300000001000000D9 | 20241030-093114F |
8| 20241030-093114F_20241030-093316I | incr | 2024-10-30 09:33:16 | 2024-10-30 09:33:18 | 588MB | 9.9KB | 180MB | 834B | 1/DB000028 | 1/DB000100 | 0000000300000001000000DB | 0000000300000001000000DB | 20241030-093114F, 20241030-093114F_20241030-093150D |
9| 20241030-093114F_20241030-093321I | incr | 2024-10-30 09:33:21 | 2024-10-30 09:33:23 | 588MB | 11KB | 180MB | 862B | 1/DD000028 | 1/DD000100 | 0000000300000001000000DD | 0000000300000001000000DD | 20241030-093114F, 20241030-093114F_20241030-093150D, 20241030-093114F_20241030-093316I |
10| 20241030-093114F_20241030-093334D | diff | 2024-10-30 09:33:34 | 2024-10-30 09:33:36 | 588MB | 11KB | 180MB | 998B | 1/DE000028 | 1/DF000050 | 0000000300000001000000DE | 0000000300000001000000DF | 20241030-093114F |
11| 20241030-093114F_20241030-101302D | diff | 2024-10-30 10:13:02 | 2024-10-30 10:13:23 | 692MB | 588MB | 213MB | 198MB | 1/E9000028 | 1/E9000138 | 0000000300000001000000E9 | 0000000300000001000000E9 | 20241030-093114F |
12| 20241030-093114F_20241030-101438D | diff | 2024-10-30 10:14:38 | 2024-10-30 10:14:59 | 692MB | 588MB | 213MB | 198MB | 1/EB000028 | 1/EB000138 | 0000000300000001000000EB | 0000000300000001000000EB | 20241030-093114F |
13| 20241030-093114F_20241030-101913I | incr | 2024-10-30 10:19:13 | 2024-10-30 10:19:15 | 692MB | 13KB | 213MB | 1.3KB | 1/ED000028 | 1/ED000138 | 0000000300000001000000ED | 0000000300000001000000ED | 20241030-093114F, 20241030-093114F_20241030-101438D |
14|------------------------------------------|--------|---------------------|---------------------|------------|------------|----------------------|----------------------|------------------|------------------|--------------------------|--------------------------|------------------------------------------------------------------------------------------------------|
15
16[root@lhrpgalloe /]#
