脚本内容
1cat > pgbackrest_table.sh <<"EOF"
2#!/bin/bash
3
4# 获取 pgBackRest 信息并解析 JSON
5pgbackrest info --stanza=$1 --output=json | jq -r '
6[
7 "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"
8],
9(
10 .[0].backup[] | [
11 .label,
12 .type,
13 .timestamp.start,
14 .timestamp.stop,
15 .info.size,
16 .info.delta,
17 .info.repository.size,
18 .info.repository.delta,
19 .lsn.start,
20 .lsn.stop,
21 .archive.start,
22 .archive.stop,
23 (if .reference then (.reference | join(", ")) else "" end) # 处理可能为空的 reference 数组
24 ]
25) | @tsv' | while IFS=$'\t' read -r label type start_ts stop_ts size delta repo_size repo_delta start_lsn stop_lsn archive_start archive_stop reference; do
26 # 检查是否是标题行
27 if [[ "$label" == "Label" ]]; then
28 printf "|------------------------------------------|--------|---------------------|---------------------|------------|------------|----------------------|----------------------|------------------|------------------|--------------------------|--------------------------|------------------------------------------------------------------------------------------------------|\n"
29 printf "| %-40s | %-6s | %-19s | %-19s | %-10s | %-10s | %-20s | %-20s | %-16s | %-16s | %-24s | %-24s | %-100s |\n" \
30 "$label" "$type" "$start_ts" "$stop_ts" "$size" "$delta" "$repo_size" "$repo_delta" "$start_lsn" "$stop_lsn" "$archive_start" "$archive_stop" "$reference"
31 # 输出分隔行
32 printf "|------------------------------------------|--------|---------------------|---------------------|------------|------------|----------------------|----------------------|------------------|------------------|--------------------------|--------------------------|------------------------------------------------------------------------------------------------------|\n"
33 continue
34 fi
35
36 # 使用 numfmt 格式化大小到 MB 或 GB
37 size=$(numfmt --to=iec --suffix=B "$size")
38 delta=$(numfmt --to=iec --suffix=B "$delta")
39 repo_size=$(numfmt --to=iec --suffix=B "$repo_size")
40 repo_delta=$(numfmt --to=iec --suffix=B "$repo_delta")
41
42 # 东八区
43 start_ts=$(TZ='Asia/Shanghai' date -d @"$start_ts" +"%Y-%m-%d %H:%M:%S")
44 stop_ts=$(TZ='Asia/Shanghai' date -d @"$stop_ts" +"%Y-%m-%d %H:%M:%S")
45
46 # 打印格式化的内容，并在每列前后加竖线
47 printf "| %-40s | %-6s | %-19s | %-19s | %-10s | %-10s | %-20s | %-20s | %-16s | %-16s | %-15s | %-15s | %-100s |\n" \
48 "$label" "$type" "$start_ts" "$stop_ts" "$size" "$delta" "$repo_size" "$repo_delta" "$start_lsn" "$stop_lsn" "$archive_start" "$archive_stop" "$reference"
49done
50printf "|------------------------------------------|--------|---------------------|---------------------|------------|------------|----------------------|----------------------|------------------|------------------|--------------------------|--------------------------|------------------------------------------------------------------------------------------------------|\n"
51echo " "
52
53EOF
54
55
56cp pgbackrest_table.sh /usr/local/bin/
57chmod +x pgbackrest_table.sh
示例
1[pg15@pg15 ~]$ sh 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|------------------------------------------|--------|---------------------|---------------------|------------|------------|----------------------|----------------------|------------------|------------------|--------------------------|--------------------------|------------------------------------------------------------------------------------------------------|
说明：
“database size”：是数据库的完整未压缩大小
“database backup size”是要实际备份的数据库中的数据量（对于完全备份，这两个值将相同）。
“backup set size”包括此备份中的所有文件以及存储库中所需的任何引用备份，以从此备份恢复数据库
“backup size”仅包括此备份中的文件（对于完全备份，这两个值也将相同）。如果在 pgBackRest 中启用了压缩，则存储库大小反映压缩后的文件大小。