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

标题: 使用pgbackrest 2.57 备份PG 18.1 配置实例 [打印本页]
作者: 郑全    时间: 2026-1-2 18:03
标题: 使用pgbackrest 2.57 备份PG 18.1 配置实例
环境：
   已经安装好PGBACKREST软件，以及存在一个数据库PG18.1
   data: /pg18/data


要求：
    完成基本备份设置



1.创建pgbackrest 配置文件
  mkdir -p /etc/pgbackrest/
  touch /etc/pgbackrest/pgbackrest.conf
  chown postgres:postgres  /etc/pgbackrest/pgbackrest.conf

  echo >> /etc/pgbackrest/pgbackrest.conf << EOF
  [regcost]
  pg1-path=/pg18/data

  EOF  
2.创建备份目录和日志目录
  Create the pgBackRest repository

  mkdir -p /pg18/pgbackrest
  chmod 750 /pg18/pgbackrest
  chown postgres:postgres /pg18/pgbackrest


  mkdir -p /var/log/pgbackrest
  chmod 770 /var/log/pgbackrest
  chown -R postgres:postgres /var/log/pgbackrest



3.让pgbackrest知道备份和日志位置

  echo >>/etc/pgbackrest/pgbackrest.conf<<EOF

  [global]
  repo1-path=/pg18/pgbackrest
  log-path=/var/log/pgbackrest
  EOF

4.Configure Archiving
  # 调整数据库参数
  echo >> /pg18/data/postgresql.conf << EOF
  archive_command = 'pgbackrest --stanza=regcost archive-push %p'
  archive_mode = on
  logging_collector = on
  log_filename = 'postgresql.log'
  log_directory = 'pg_log'
  max_wal_senders = 3
  wal_level = replica

  EOF



  # 增加 归档设置

  echo >> etc/pgbackrest/pgbackrest.conf << EOF
  [global:archive-push]
  compress-level=3

  EOF

5.设置保留策略
  Configure Retention

  vim /etc/pgbackrest/pgbackrest.conf

  [regcost]
  pg1-path=/pg18/data


  [global]
  repo1-path=/pg18/pgbackrest
  repo1-retention-full=2
  log-path=/var/log/pgbackrest

  [global:archive-push]
  compress-level=3

6.重启数据库
  pg_ctl restart

7.初始化Stanza
  pgbackrest --stanza=regcost --log-level-console=info stanza-create


8.Check the Configuration
  pgbackrest --stanza=regcost --log-level-console=info check

9.Performance Tuning
  vim /etc/pgbackrest/pgbackrest.conf

  [regcost]
  pg1-path=/pg18/data
  pg1-socket-path=/tmp

  [global]
  repo1-path=/pg18/pgbackrest
  log-path=/var/log/pgbackrest
  repo1-bundle=y
  repo1-block=y
  start-fast=y
  process-max=4
  compress-type=lz4  
  compress-level=5
  repo1-retention-full=2
  repo1-retention-archive=2
  repo1-retention-archive-type=full

  [global:archive-push]
  compress-level=3

10.Perform a Backup
  pgbackrest --stanza=regcost --log-level-console=info backup

  pgbackrest --stanza=regcost --type=diff --log-level-console=info backup

  pgbackrest --stanza=regcost --type=incr --log-level-console=info backup

  pgbackrest --stanza=regcost --type=full --log-level-console=info backup



postgres@pg181:/home$ pgbackrest --stanza=regcost --log-level-console=info backup
2026-01-02 17:15:29.187 P00   INFO: backup command begin 2.57.0: --compress-level=5 --compress-type=lz4 --exec-id=10331-03831dca --log-level-console=info --log-path=/var/log/pgbackrest --pg1-path=/pg18/data --pg1-socket-path=/tmp --process-max=4 --repo1-block --repo1-bundle --repo1-path=/pg18/pgbackrest --repo1-retention-archive=2 --repo1-retention-archive-type=full --repo1-retention-full=2 --stanza=regcost --start-fast
2026-01-02 17:15:29.201 P00   WARN: no prior backup exists, incr backup has been changed to full
2026-01-02 17:15:29.201 P00   INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2026-01-02 17:15:29.283 P00   INFO: backup start archive = 000000010000000000000003, lsn = 0/3000028
2026-01-02 17:15:29.283 P00   INFO: check archive for prior segment 000000010000000000000002
2026-01-02 17:15:31.766 P00   INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2026-01-02 17:15:31.780 P00   INFO: backup stop archive = 000000010000000000000003, lsn = 0/3000158
2026-01-02 17:15:31.783 P00   INFO: check archive for segment(s) 000000010000000000000003:000000010000000000000003
2026-01-02 17:15:31.891 P00   INFO: new backup label = 20260102-171529F
2026-01-02 17:15:31.914 P00   INFO: full backup size = 23.3MB, file total = 970
2026-01-02 17:15:31.914 P00   INFO: backup command end: completed successfully (2731ms)
2026-01-02 17:15:31.914 P00   INFO: expire command begin 2.57.0: --exec-id=10331-03831dca --log-level-console=info --log-path=/var/log/pgbackrest --repo1-path=/pg18/pgbackrest --repo1-retention-archive=2 --repo1-retention-archive-type=full --repo1-retention-full=2 --stanza=regcost
2026-01-02 17:15:31.915 P00   INFO: expire command end: completed successfully (1ms)
postgres@pg181:/home$ pgbackrest --stanza=regcost --type=diff --log-level-console=info backup
2026-01-02 17:16:35.957 P00   INFO: backup command begin 2.57.0: --compress-level=5 --compress-type=lz4 --exec-id=10374-ed339bb3 --log-level-console=info --log-path=/var/log/pgbackrest --pg1-path=/pg18/data --pg1-socket-path=/tmp --process-max=4 --repo1-block --repo1-bundle --repo1-path=/pg18/pgbackrest --repo1-retention-archive=2 --repo1-retention-archive-type=full --repo1-retention-full=2 --stanza=regcost --start-fast --type=diff
2026-01-02 17:16:35.973 P00   INFO: last backup label = 20260102-171529F, version = 2.57.0
2026-01-02 17:16:35.973 P00   INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2026-01-02 17:16:36.005 P00   INFO: backup start archive = 000000010000000000000005, lsn = 0/5000028
2026-01-02 17:16:36.005 P00   INFO: check archive for prior segment 000000010000000000000004
2026-01-02 17:16:37.446 P00   INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2026-01-02 17:16:37.457 P00   INFO: backup stop archive = 000000010000000000000005, lsn = 0/5000120
2026-01-02 17:16:37.459 P00   INFO: check archive for segment(s) 000000010000000000000005:000000010000000000000005
2026-01-02 17:16:37.569 P00   INFO: new backup label = 20260102-171529F_20260102-171635D
2026-01-02 17:16:37.590 P00   INFO: diff backup size = 10.2KB, file total = 970
2026-01-02 17:16:37.591 P00   INFO: backup command end: completed successfully (1639ms)
2026-01-02 17:16:37.591 P00   INFO: expire command begin 2.57.0: --exec-id=10374-ed339bb3 --log-level-console=info --log-path=/var/log/pgbackrest --repo1-path=/pg18/pgbackrest --repo1-retention-archive=2 --repo1-retention-archive-type=full --repo1-retention-full=2 --stanza=regcost
2026-01-02 17:16:37.591 P00   INFO: expire command end: completed successfully (0ms)

postgres@pg181:/home$   pgbackrest --stanza=regcost --type=incr --log-level-console=info backup
2026-01-02 17:17:35.301 P00   INFO: backup command begin 2.57.0: --compress-level=5 --compress-type=lz4 --exec-id=10418-69a497d5 --log-level-console=info --log-path=/var/log/pgbackrest --pg1-path=/pg18/data --pg1-socket-path=/tmp --process-max=4 --repo1-block --repo1-bundle --repo1-path=/pg18/pgbackrest --repo1-retention-archive=2 --repo1-retention-archive-type=full --repo1-retention-full=2 --stanza=regcost --start-fast --type=incr
2026-01-02 17:17:35.319 P00   INFO: last backup label = 20260102-171529F_20260102-171635D, version = 2.57.0
2026-01-02 17:17:35.320 P00   INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2026-01-02 17:17:35.401 P00   INFO: backup start archive = 000000010000000000000007, lsn = 0/7000028
2026-01-02 17:17:35.401 P00   INFO: check archive for prior segment 000000010000000000000006
2026-01-02 17:17:37.453 P00   INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2026-01-02 17:17:37.467 P00   INFO: backup stop archive = 000000010000000000000007, lsn = 0/7000120
2026-01-02 17:17:37.470 P00   INFO: check archive for segment(s) 000000010000000000000007:000000010000000000000007
2026-01-02 17:17:37.579 P00   INFO: new backup label = 20260102-171529F_20260102-171735I
2026-01-02 17:17:37.600 P00   INFO: incr backup size = 10.6KB, file total = 970
2026-01-02 17:17:37.600 P00   INFO: backup command end: completed successfully (2303ms)
2026-01-02 17:17:37.600 P00   INFO: expire command begin 2.57.0: --exec-id=10418-69a497d5 --log-level-console=info --log-path=/var/log/pgbackrest --repo1-path=/pg18/pgbackrest --repo1-retention-archive=2 --repo1-retention-archive-type=full --repo1-retention-full=2 --stanza=regcost
2026-01-02 17:17:37.601 P00   INFO: expire command end: completed successfully (1ms)
postgres@pg181:/home$ pgbackrest --stanza=regcost --type=full --log-level-console=info backup
2026-01-02 17:18:13.429 P00   INFO: backup command begin 2.57.0: --compress-level=5 --compress-type=lz4 --exec-id=10460-fc8b8e19 --log-level-console=info --log-path=/var/log/pgbackrest --pg1-path=/pg18/data --pg1-socket-path=/tmp --process-max=4 --repo1-block --repo1-bundle --repo1-path=/pg18/pgbackrest --repo1-retention-archive=2 --repo1-retention-archive-type=full --repo1-retention-full=2 --stanza=regcost --start-fast --type=full
2026-01-02 17:18:13.439 P00   INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2026-01-02 17:18:13.459 P00   INFO: backup start archive = 000000010000000000000009, lsn = 0/9000028
2026-01-02 17:18:13.459 P00   INFO: check archive for prior segment 000000010000000000000008
2026-01-02 17:18:15.544 P00   INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2026-01-02 17:18:15.556 P00   INFO: backup stop archive = 000000010000000000000009, lsn = 0/9000120
2026-01-02 17:18:15.559 P00   INFO: check archive for segment(s) 000000010000000000000009:000000010000000000000009
2026-01-02 17:18:15.667 P00   INFO: new backup label = 20260102-171813F
2026-01-02 17:18:15.695 P00   INFO: full backup size = 23.3MB, file total = 970
2026-01-02 17:18:15.695 P00   INFO: backup command end: completed successfully (2270ms)
2026-01-02 17:18:15.695 P00   INFO: expire command begin 2.57.0: --exec-id=10460-fc8b8e19 --log-level-console=info --log-path=/var/log/pgbackrest --repo1-path=/pg18/pgbackrest --repo1-retention-archive=2 --repo1-retention-archive-type=full --repo1-retention-full=2 --stanza=regcost
2026-01-02 17:18:15.696 P00   INFO: repo1: 18-1 remove archive, start = 000000010000000000000001, stop = 000000010000000000000002
2026-01-02 17:18:15.696 P00   INFO: expire command end: completed successfully (1ms)


11. Backup Information
  pgbackrest info
postgres@pg181:/home$ pgbackrest info
stanza: regcost
    status: ok
    cipher: none

    db (current)
        wal archive min/max (18): 000000010000000000000003/000000010000000000000009

        full backup: 20260102-171529F
            timestamp start/stop: 2026-01-02 17:15:29+08 / 2026-01-02 17:15:31+08
            wal start/stop: 000000010000000000000003 / 000000010000000000000003
            database size: 23.3MB, database backup size: 23.3MB
            repo1: backup size: 3.8MB

        diff backup: 20260102-171529F_20260102-171635D
            timestamp start/stop: 2026-01-02 17:16:35+08 / 2026-01-02 17:16:37+08
            wal start/stop: 000000010000000000000005 / 000000010000000000000005
            database size: 23.3MB, database backup size: 10.2KB
            repo1: backup size: 1.3KB
            backup reference total: 1 full

        incr backup: 20260102-171529F_20260102-171735I
            timestamp start/stop: 2026-01-02 17:17:35+08 / 2026-01-02 17:17:37+08
            wal start/stop: 000000010000000000000007 / 000000010000000000000007
            database size: 23.3MB, database backup size: 10.6KB
            repo1: backup size: 1.4KB
            backup reference total: 1 full, 1 diff

        full backup: 20260102-171813F
            timestamp start/stop: 2026-01-02 17:18:13+08 / 2026-01-02 17:18:15+08
            wal start/stop: 000000010000000000000009 / 000000010000000000000009
            database size: 23.3MB, database backup size: 23.3MB
            repo1: backup size: 3.8MB
postgres@pg181:/home$ pgbackrest --stanza=regcost --type=full --log-level-console=info backup
2026-01-02 17:20:25.582 P00   INFO: backup command begin 2.57.0: --compress-level=5 --compress-type=lz4 --exec-id=10507-a6e560da --log-level-console=info --log-path=/var/log/pgbackrest --pg1-path=/pg18/data --pg1-socket-path=/tmp --process-max=4 --repo1-block --repo1-bundle --repo1-path=/pg18/pgbackrest --repo1-retention-archive=2 --repo1-retention-archive-type=full --repo1-retention-full=2 --stanza=regcost --start-fast --type=full
2026-01-02 17:20:25.592 P00   INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2026-01-02 17:20:25.609 P00   INFO: backup start archive = 00000001000000000000000B, lsn = 0/B000028
2026-01-02 17:20:25.609 P00   INFO: check archive for prior segment 00000001000000000000000A
2026-01-02 17:20:27.531 P00   INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2026-01-02 17:20:27.542 P00   INFO: backup stop archive = 00000001000000000000000B, lsn = 0/B000158
2026-01-02 17:20:27.544 P00   INFO: check archive for segment(s) 00000001000000000000000B:00000001000000000000000B
2026-01-02 17:20:27.652 P00   INFO: new backup label = 20260102-172025F
2026-01-02 17:20:27.675 P00   INFO: full backup size = 23.3MB, file total = 970
2026-01-02 17:20:27.675 P00   INFO: backup command end: completed successfully (2097ms)
2026-01-02 17:20:27.676 P00   INFO: expire command begin 2.57.0: --exec-id=10507-a6e560da --log-level-console=info --log-path=/var/log/pgbackrest --repo1-path=/pg18/pgbackrest --repo1-retention-archive=2 --repo1-retention-archive-type=full --repo1-retention-full=2 --stanza=regcost
2026-01-02 17:20:27.677 P00   INFO: repo1: expire full backup set 20260102-171529F, 20260102-171529F_20260102-171635D, 20260102-171529F_20260102-171735I
2026-01-02 17:20:27.680 P00   INFO: repo1: remove expired backup 20260102-171529F_20260102-171735I
2026-01-02 17:20:27.680 P00   INFO: repo1: remove expired backup 20260102-171529F_20260102-171635D
2026-01-02 17:20:27.680 P00   INFO: repo1: remove expired backup 20260102-171529F
2026-01-02 17:20:27.681 P00   INFO: repo1: 18-1 remove archive, start = 000000010000000000000003, stop = 000000010000000000000008
2026-01-02 17:20:27.681 P00   INFO: expire command end: completed successfully (6ms)
postgres@pg181:/home$



postgres@pg181:/home$ pgbackrest info
stanza: regcost
    status: ok
    cipher: none

    db (current)
        wal archive min/max (18): 000000010000000000000009/00000001000000000000000B

        full backup: 20260102-171813F
            timestamp start/stop: 2026-01-02 17:18:13+08 / 2026-01-02 17:18:15+08
            wal start/stop: 000000010000000000000009 / 000000010000000000000009
            database size: 23.3MB, database backup size: 23.3MB
            repo1: backup size: 3.8MB

        full backup: 20260102-172025F
            timestamp start/stop: 2026-01-02 17:20:25+08 / 2026-01-02 17:20:27+08
            wal start/stop: 00000001000000000000000B / 00000001000000000000000B
            database size: 23.3MB, database backup size: 23.3MB
            repo1: backup size: 3.8MB
postgres@pg181:/home$








欢迎光临 重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛 (http://bbs.cqsztech.com/) Powered by Discuz! X3.2