合 PG备份恢复工具之pgbackrest
Tags： PGPostgreSQL备份恢复S3sshfspgbackrest
👉 本文共约3527个字，系统预计阅读时间或需14分钟。
目录
- 简介
- 主要的功能
- 优势
- 安装
- 报错configure: error: library 'pq' is required
- 参数
- 实验
- 数据库服务器上的配置
- 备份服务器上的配置
- 创建存储空间
- 报错
- 全量备份
- 差异备份
- 增量备份
- 查看备份信息
- 备份监控
- 还原
- 基于时间点的恢复（Point-in-Time Recovery）
- 增量 vs 差异备份
- 备份到S3存储
- 格式化pgbackrest info的备份结果为表格形式
- 备份的shell脚本
- 报错
- ERROR: [056]: unable to find primary cluster - cannot proceed
- connection to server on socket "/var/run/postgresql/.s.PGSQL.5441" failed: No such file or directory
- WAL segment 00000001000000000000000B was not archived before the 60000ms timeout
- terminated unexpectedly [255]: Permission denied (publickey,password)
- FATAL: recovery ended before configured recovery target was reached
- 总结
- 参考
简介
pgBackRest旨在成为一个可靠、易于使用的备份和恢复解决方案，通过利用针对特定数据库要求优化的算法，可以无缝扩展到最大的数据库和工作负载。
GitHub：https://github.com/pgbackrest/pgbackrest
文档：https://pgbackrest.org/user-guide-rhel.html
主要的功能
- 并行备份和恢复
- 本地或远程备份
- 完整、增量和差异备份
- 备份轮换和存档过期策略
- 支持压缩和完整性校验
优势
1、支持对象存储
2、支持从standby实例备份，可以减小主库的IO压力
3、支持异地备份
安装
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 | wget https://github.com/pgbackrest/pgbackrest/archive/refs/tags/release/2.52.1.tar.gz yum -y install libyaml-devel libxml2 libxml2-devel bzip2 bzip2-devel export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/pg16/pg16/lib export PATH=$PATH:/pg16/pg16/bin tar -zxvf 2.52.1.tar.gz cd pgbackrest-release-2.52.1/src ./configure make -j8 make install ln -s /usr/local/bin/pgbackrest /usr/bin/pgbackrest chmod 755 /usr/local/bin/pgbackrest mkdir -p -m 770 /var/log/pgbackrest chown postgres:postgres /var/log/pgbackrest 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 mkdir -p /var/lib/pgbackrest chmod 750 /var/lib/pgbackrest chown postgres:postgres /var/lib/pgbackrest [root@lhrpgall ~]# pgbackrest pgBackRest 2.52.1 - 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. [pg16@lhrpgall2 ~]$ pgbackrest help backup pgBackRest 2.52.1 - 'backup' command help Backup a database cluster. When multiple repositories are configured, pgBackRest will backup to the highest priority repository (e.g. repo1) unless the --repo option is specified. pgBackRest does not have a built-in scheduler so it's best to run it from cron or some other scheduling mechanism. See Perform a Backup for more details and examples. Command Options: --annotation annotate backup with user-defined key/value pairs --archive-check check that WAL segments are in the archive before backup completes [default=y] --archive-copy copy WAL segments needed for consistency to the backup [default=n] --archive-mode-check check the PostgreSQL archive_mode setting [default=y] --archive-timeout archive timeout [default=60] --backup-standby backup from the standby cluster [default=n] --checksum-page validate data page checksums --exclude exclude paths/files from the backup --expire-auto automatically run the expire command after a successful backup [default=y] --force force an offline backup [default=n] --manifest-save-threshold manifest save threshold during backup [default=1GiB] --online perform an online backup [default=y] --resume allow resume of failed backup [default=y] --start-fast force a checkpoint to start backup quickly [default=n] --stop-auto stop prior failed backup on new backup [default=n] --type backup type [default=incr] General Options: --buffer-size buffer size for I/O operations [default=1MiB] --cmd pgBackRest command [default=pgbackrest] --cmd-ssh SSH client command [default=ssh] --compress use file compression [default=y] --compress-level file compression level --compress-level-network network compression level [default=3] --compress-type file compression type [default=gz] --config pgBackRest configuration file [default=/etc/pgbackrest/pgbackrest.conf] --config-include-path path to additional pgBackRest configuration files [default=/etc/pgbackrest/conf.d] --config-path base path of pgBackRest configuration files [default=/etc/pgbackrest] --db-timeout database query timeout [default=1800] --delta restore or backup using checksums [default=n] --io-timeout I/O timeout [default=60] --lock-path path where lock files are stored [default=/tmp/pgbackrest] --neutral-umask use a neutral umask [default=y] --process-max max processes to use for compress/transfer [default=1] --protocol-timeout protocol timeout [default=1830] --sck-keep-alive keep-alive enable [default=y] --stanza defines the stanza --tcp-keep-alive-count keep-alive count --tcp-keep-alive-idle keep-alive idle time --tcp-keep-alive-interval keep-alive interval time Log Options: --log-level-console level for console logging [default=warn] --log-level-file level for file logging [default=info] --log-level-stderr level for stderr logging [default=warn] --log-path path where log files are stored [current=/var/log/pgbackrest, default=/var/log/pgbackrest] --log-subprocess enable logging in subprocesses [default=n] --log-timestamp enable timestamp in logging [default=y] Maintainer Options: --page-header-check check PostgreSQL page headers [default=y] --pg-version-force force PostgreSQL version Repository Options: --repo set repository --repo-azure-account azure repository account --repo-azure-container azure repository container --repo-azure-endpoint azure repository endpoint [default=blob.core.windows.net] --repo-azure-key azure repository key --repo-azure-key-type azure repository key type [default=shared] --repo-azure-uri-style azure URI Style [default=host] --repo-block enable block incremental backup [default=n] --repo-bundle bundle files in repository [default=n] --repo-bundle-limit limit for file bundles [default=2MiB] --repo-bundle-size target size for file bundles [default=20MiB] --repo-cipher-pass repository cipher passphrase --repo-cipher-type cipher used to encrypt the repository [default=none] --repo-gcs-bucket GCS repository bucket --repo-gcs-endpoint GCS repository endpoint [default=storage.googleapis.com] --repo-gcs-key GCS repository key --repo-gcs-key-type GCS repository key type [default=service] --repo-hardlink hardlink files between backups in the repository [default=n] --repo-path path where backups and archive are stored [current=/bk/repos, default=/var/lib/pgbackrest] --repo-retention-archive number of backups worth of continuous WAL to retain --repo-retention-archive-type backup type for WAL retention [default=full] --repo-retention-diff number of differential backups to retain --repo-retention-full full backup retention count/time [current=2] --repo-retention-full-type retention type for full backups [default=count] --repo-retention-history days of backup history manifests to retain --repo-s3-bucket S3 repository bucket --repo-s3-endpoint S3 repository endpoint --repo-s3-key S3 repository access key --repo-s3-key-secret S3 repository secret access key --repo-s3-key-type S3 repository key type [default=shared] --repo-s3-kms-key-id S3 repository KMS key --repo-s3-region S3 repository region --repo-s3-role S3 repository role --repo-s3-sse-customer-key S3 Repository SSE Customer Key --repo-s3-token S3 repository security token --repo-s3-uri-style S3 URI Style [default=host] --repo-sftp-host SFTP repository host --repo-sftp-host-fingerprint SFTP repository host fingerprint --repo-sftp-host-key-check-type SFTP host key check type [default=strict] --repo-sftp-host-key-hash-type SFTP repository host key hash type --repo-sftp-host-port SFTP repository host port [default=22] --repo-sftp-host-user SFTP repository host user --repo-sftp-known-host SFTP known hosts file --repo-sftp-private-key-file SFTP private key file --repo-sftp-private-key-passphrase SFTP private key passphrase --repo-sftp-public-key-file SFTP public key file --repo-storage-ca-file repository storage CA file --repo-storage-ca-path repository storage CA path --repo-storage-host repository storage host --repo-storage-port repository storage port [default=443] --repo-storage-tag repository storage tag(s) --repo-storage-upload-chunk-size repository storage upload chunk size --repo-storage-verify-tls repository storage certificate verify [default=y] --repo-type type of storage used for the repository [default=posix] Stanza Options: --pg-database postgreSQL database [default=postgres] --pg-host postgreSQL host for operating remotely --pg-host-ca-file postgreSQL host certificate authority file --pg-host-ca-path postgreSQL host certificate authority path --pg-host-cert-file postgreSQL host certificate file --pg-host-cmd postgreSQL host pgBackRest command [default=pgbackrest] --pg-host-config pgBackRest database host configuration file [default=/etc/pgbackrest/pgbackrest.conf] --pg-host-config-include-path pgBackRest database host configuration include path [default=/etc/pgbackrest/conf.d] --pg-host-config-path pgBackRest database host configuration path [default=/etc/pgbackrest] --pg-host-key-file postgreSQL host key file --pg-host-port postgreSQL host port when pg-host is set --pg-host-type postgreSQL host protocol type [default=ssh] --pg-host-user postgreSQL host logon user when pg-host is set [default=postgres] --pg-path postgreSQL data directory --pg-port postgreSQL port [default=5432] --pg-socket-path postgreSQL unix socket path --pg-user postgreSQL database user Use 'pgbackrest help backup [option]' for more information. [pg16@lhrpgall2 ~]$ |
报错configure: error: library 'pq' is required
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | [root@lhrpgall src]# ./configure checking for gcc... gcc checking whether the C compiler works... yes checking for C compiler default output file name... a.out checking for suffix of executables... checking whether we are cross compiling... no checking for suffix of object files... o checking whether the compiler supports GNU C... yes checking whether gcc accepts -g... yes checking for gcc option to enable C11 features... -std=gnu11 checking build system type... x86_64-pc-linux-gnu checking host system type... x86_64-pc-linux-gnu checking for pg_config... no checking for PQconnectdb in -lpq... no configure: error: library 'pq' is required |
解决：需要配置PATH变量 export PATH=$PATH:/pg15/pg15/bin
参数
--start-fast ：触发快速检查点，而不是等到下一个定时检查点。
--compress ：启用压缩备份。默认情况下压缩是打开的。
--process-max ：可以启动备份以加快备份速度的并发进程数。
实验
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 | docker pull registry.cn-hangzhou.aliyuncs.com/lhrbest/lhrpgall:4.0 docker rm -f lhrpg01 docker run -itd --name lhrpg01 -h lhrpg01 \ -p 25432-25445:5432-5445 -p 122:22 -p 189:3389 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --restart=always \ --privileged=true lhrbest/lhrpgall:4.0 \ /usr/sbin/init docker exec -it lhrpg01 bash systemctl status pg94 pg96 pg10 pg11 pg12 pg13 pg14 pg15 pg16 systemctl status postgresql-15.service ps -ef|grep postgres | grep bin docker rm -f lhrpg02 docker run -itd --name lhrpg02 -h lhrpg02 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --restart=always \ --privileged=true lhrbest/lhrpgall:4.0 \ /usr/sbin/init docker exec -it lhrpg02 bash systemctl stop pg94 pg96 pg10 pg11 pg12 pg13 pg14 pg15 systemctl stop postgresql-15.service wget https://github.com/pgbackrest/pgbackrest/archive/refs/tags/release/2.52.1.tar.gz yum -y install libyaml-devel libxml2 libxml2-devel bzip2 bzip2-devel export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/pg16/pg16/lib export PATH=$PATH:/pg16/pg16/bin tar -zxvf 2.52.1.tar.gz cd pgbackrest-release-2.52.1/src ./configure make -j8 make install ln -s /usr/local/bin/pgbackrest /usr/bin/pgbackrest chmod 755 /usr/local/bin/pgbackrest mkdir -p -m 770 /var/log/pgbackrest chown pg16:pg16 /var/log/pgbackrest mkdir -p /etc/pgbackrest mkdir -p /etc/pgbackrest/conf.d touch /etc/pgbackrest/pgbackrest.conf chmod 640 /etc/pgbackrest/pgbackrest.conf chown pg16:pg16 /etc/pgbackrest/pgbackrest.conf mkdir -p /var/lib/pgbackrest chmod 750 /var/lib/pgbackrest chown pg16:pg16 /var/lib/pgbackrest mkdir -p /bk/repos chown pg16.pg16 /etc/pgbackrest/ -R chown pg16.pg16 /bk -R chown pg16.pg16 /var/log/pgbackrest/ -R ln -s /pg16/pgdata/.s.PGSQL.5441 /var/run/postgresql/.s.PGSQL.5441 -- 互信 ./sshUserSetup.sh -user pg16 -hosts "192.92.0.23 192.92.0.42" -advanced -noPromptPassphrase ./sshUserSetup.sh -user pg16 -hosts "lhrpg01 lhrpg02" -advanced -noPromptPassphrase chmod 600 /home/pg16/.ssh/config |
数据库服务器上的配置
lhrpg01(192.92.0.23)为数据库服务器，lhrpg02(192.92.0.42)为备份服务器。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | cat > /etc/pgbackrest/pgbackrest.conf <<"EOF" [global] log-level-file=detail repo1-host=192.92.0.42 repo1-host-user=pg16 repo1-path=/bk/repos log-path=/var/log/pgbackrest start-fast=y process-max=4 [pg01] pg1-path=/pg16/pgdata pg1-port=5441 pg1-host-user=pg16 EOF # 设置数据库参数 alter system set archive_command = 'pgbackrest --stanza=pg01 archive-push %p'; alter system set archive_mode = on; alter system set listen_addresses = '*'; -- alter system set log_line_prefix = ''; alter system set max_wal_senders = 10; alter system set wal_level = replica; # 重启数据库 pg_ctl restart |
备份服务器上的配置
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | cat > /etc/pgbackrest/pgbackrest.conf <<"EOF" [global] repo1-path=/bk/repos log-level-file=detail repo1-retention-full=2 log-path=/var/log/pgbackrest start-fast=y process-max=4 repo1-host-user=pg16 [global:archive_push] compress-level=3 [pg02] pg1-path=/pg16/pgdata pg1-port=5441 [pg01] pg1-path=/pg16/pgdata pg1-host-config-path=/etc/pgbackrest pg1-host-port=22 pg1-host-user=pg16 pg1-host=192.92.0.23 pg1-port=5441 pg1-user=postgres EOF # 设置数据库参数 alter system set archive_command = 'pgbackrest --stanza=pg02 archive-push %p'; alter system set archive_mode = on; alter system set listen_addresses = '*'; -- alter system set log_line_prefix = ''; alter system set max_wal_senders = 10; alter system set wal_level = replica; # 重启数据库 pg_ctl restart #备注： pg1-path 数据库的文件路径 pg1-port 数据库端口号 repo1-path 指定 备份和归档仓库路径 compress-level 指定压缩级别 bz2 - 9 ; gz - 6 ; lz4 - 1 ; zst - 3 |
创建存储空间
1 2 3 4 5 6 7 8 | #本地节点 pgbackrest --stanza=pg02 --log-level-console=info stanza-create pgbackrest --stanza=pg02 --log-level-console=info check #远程节点 pgbackrest --stanza=pg01 --log-level-console=info stanza-create pgbackrest --stanza=pg01 --log-level-console=info check |
日志：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | -- 本地 [pg16@lhrpgall2 ~]$ pgbackrest --stanza=pg02 --log-level-console=info stanza-create 2024-07-02 14:50:14.511 P00 INFO: stanza-create command begin 2.52.1: --exec-id=5188-b392e5f0 --log-level-console=info --log-path=/var/log/pgbackrest --pg1-path=/pg16/pgdata --pg1-port=5441 --repo1-path=/bk/repos --stanza=pg02 2024-07-02 14:50:15.114 P00 INFO: stanza-create for stanza 'pg02' on repo1 2024-07-02 14:50:15.124 P00 INFO: stanza-create command end: completed successfully (614ms) [pg16@lhrpgall2 ~]$ [pg16@lhrpgall2 ~]$ pgbackrest --stanza=pg02 --log-level-console=info check 2024-07-02 14:50:20.925 P00 INFO: check command begin 2.52.1: --exec-id=5199-75bca446 --log-level-console=info --log-path=/var/log/pgbackrest --pg1-path=/pg16/pgdata --pg1-port=5441 --repo1-path=/bk/repos --stanza=pg02 2024-07-02 14:50:21.528 P00 INFO: check repo1 configuration (primary) 2024-07-02 14:50:21.730 P00 INFO: check repo1 archive for WAL (primary) 2024-07-02 14:50:22.331 P00 INFO: WAL segment 000000010000000000000003 successfully archived to '/bk/repos/archive/pg02/16-1/0000000100000000/000000010000000000000003-586f4518e35c9e2f2c1a9cb0920ce161ead58281.gz' on repo1 2024-07-02 14:50:22.331 P00 INFO: check command end: completed successfully (1407ms) [pg16@lhrpgall2 ~]$ -- 远程 [pg16@lhrpgall2 ~]$ pgbackrest --stanza=pg01 --log-level-console=info stanza-create 2024-07-02 15:03:47.230 P00 INFO: stanza-create command begin 2.52.1: --exec-id=6260-cb162782 --log-level-console=info --log-path=/var/log/pgbackrest --pg1-host=192.92.0.23 --pg1-host-config-path=/etc/pgbackrest --pg1-host-port=22 --pg1-host-user=pg16 --pg1-path=/pg16/pgdata --pg1-port=5441 --pg1-user=postgres --repo1-path=/bk/repos --stanza=pg01 2024-07-02 15:03:47.972 P00 INFO: stanza-create for stanza 'pg01' on repo1 2024-07-02 15:03:48.083 P00 INFO: stanza-create command end: completed successfully (854ms) [pg16@lhrpgall2 ~]$ [pg16@lhrpgall2 repos]$ pgbackrest --stanza=pg01 --log-level-console=detail check 2024-07-02 15:32:15.797 P00 INFO: check command begin 2.52.1: --exec-id=8328-880c6fa7 --log-level-console=detail --log-path=/var/log/pgbackrest --pg1-host=192.92.0.23 --pg1-host-config-path=/etc/pgbackrest --pg1-host-port=22 --pg1-host-user=pg16 --pg1-path=/pg16/pgdata --pg1-port=5441 --pg1-user=postgres --repo1-path=/bk/repos --stanza=pg01 2024-07-02 15:32:16.534 P00 INFO: check repo1 configuration (primary) 2024-07-02 15:32:16.736 P00 INFO: check repo1 archive for WAL (primary) 2024-07-02 15:32:17.037 P00 INFO: WAL segment 00000001000000000000000C successfully archived to '/bk/repos/archive/pg01/16-1/0000000100000000/00000001000000000000000C-d3f9958fdbd8a99856af8edb9cecd2ea91da51a8.gz' on repo1 2024-07-02 15:32:17.138 P00 INFO: check command end: completed successfully (1342ms) [pg16@lhrpgall2 repos]$ |
报错
报错1：
1 2 3 4 5 6 7 8 | [pg16@lhrpgall2 ~]$ pgbackrest --stanza=pg01 --log-level-console=info stanza-create 2024-07-02 15:00:13.647 P00 INFO: stanza-create command begin 2.52.1: --exec-id=6027-c93bd88b --log-level-console=info --log-path=/var/log/pgbackrest --pg1-host=192.92.0.23 --pg1-host-config-path=/etc/pgbackrest --pg1-host-port=22 --pg1-host-user=pg16 --pg1-path=/pg16/pgdata --pg1-port=5441 --pg1-user=postgres --repo1-path=/bk/repos --stanza=pg01 WARN: unable to check pg1: [DbConnectError] raised from remote-0 ssh protocol on '192.92.0.23': unable to connect to 'dbname='postgres' port=5441 user='postgres'': connection to server on socket "/var/run/postgresql/.s.PGSQL.5441" failed: No such file or directory Is the server running locally and accepting connections on that socket? ERROR: [056]: unable to find primary cluster - cannot proceed HINT: are all available clusters in recovery? 2024-07-02 15:00:13.784 P00 INFO: stanza-create command end: aborted with exception [056] [pg16@lhrpgall2 ~]$ |
解决：
1 | ln -s /pg16/pgdata/.s.PGSQL.5441 /var/run/postgresql/.s.PGSQL.5441 |
报错2：
1 2 3 4 5 6 7 8 9 | [pg16@lhrpgall2 repos]$ pgbackrest --stanza=pg01 --log-level-console=detail check 2024-07-02 15:28:02.009 P00 INFO: check command begin 2.52.1: --exec-id=7843-c7c80d5f --log-level-console=detail --log-path=/var/log/pgbackrest --pg1-host=192.92.0.23 --pg1-host-config-path=/etc/pgbackrest --pg1-host-port=22 --pg1-host-user=pg16 --pg1-path=/pg16/pgdata --pg1-port=5441 --pg1-user=postgres --repo1-path=/bk/repos --stanza=pg01 2024-07-02 15:28:02.751 P00 INFO: check repo1 configuration (primary) 2024-07-02 15:28:02.954 P00 INFO: check repo1 archive for WAL (primary) ERROR: [082]: WAL segment 00000001000000000000000B was not archived before the 60000ms timeout HINT: check the archive_command to ensure that all options are correct (especially --stanza). HINT: check the PostgreSQL server log for errors. HINT: run the 'start' command if the stanza was previously stopped. 2024-07-02 15:29:02.954 P00 INFO: check command end: aborted with exception [082] |
解决：
远程数据库告警日志：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | 2024-07-02 15:28:02.753 CST [506012] LOG: restore point "pgBackRest Archive Check" created at 0/B0001B0 2024-07-02 15:28:02.753 CST [506012] STATEMENT: select pg_catalog.pg_create_restore_point('pgBackRest Archive Check')::text ERROR: [103]: unable to find a valid repository: repo1: [UnknownError] remote-0 process on '192.92.0.42' terminated unexpectedly [255]: Permission denied (publickey,password). 2024-07-02 15:28:02.953 CST [504952] LOG: archive command failed with exit code 103 2024-07-02 15:28:02.953 CST [504952] DETAIL: The failed archive command was: pgbackrest --stanza=pg01 archive-push pg_wal/000000010000000000000001 ERROR: [103]: unable to find a valid repository: repo1: [UnknownError] remote-0 process on '192.92.0.42' terminated unexpectedly [255]: Permission denied (publickey,password). 2024-07-02 15:28:04.019 CST [504952] LOG: archive command failed with exit code 103 2024-07-02 15:28:04.019 CST [504952] DETAIL: The failed archive command was: pgbackrest --stanza=pg01 archive-push pg_wal/000000010000000000000001 ERROR: [103]: unable to find a valid repository: repo1: [UnknownError] remote-0 process on '192.92.0.42' terminated unexpectedly [255]: Permission denied (publickey,password). 2024-07-02 15:28:05.092 CST [504952] LOG: archive command failed with exit code 103 2024-07-02 15:28:05.092 CST [504952] DETAIL: The failed archive command was: pgbackrest --stanza=pg01 archive-push pg_wal/000000010000000000000001 2024-07-02 15:28:05.092 CST [504952] WARNING: archiving write-ahead log file "000000010000000000000001" failed too many times, will try again later ERROR: [103]: unable to find a valid repository: repo1: [UnknownError] remote-0 process on '192.92.0.42' terminated unexpectedly [255]: Permission denied (publickey,password). 2024-07-02 15:29:05.211 CST [504952] LOG: archive command failed with exit code 103 2024-07-02 15:29:05.211 CST [504952] DETAIL: The failed archive command was: pgbackrest --stanza=pg01 archive-push pg_wal/000000010000000000000001 ERROR: [103]: unable to find a valid repository: repo1: [UnknownError] remote-0 process on '192.92.0.42' terminated unexpectedly [255]: Permission denied (publickey,password). 2024-07-02 15:29:06.277 CST [504952] LOG: archive command failed with exit code 103 2024-07-02 15:29:06.277 CST [504952] DETAIL: The failed archive command was: pgbackrest --stanza=pg01 archive-push pg_wal/000000010000000000000001 ERROR: [103]: unable to find a valid repository: repo1: [UnknownError] remote-0 process on '192.92.0.42' terminated unexpectedly [255]: Permission denied (publickey,password). 2024-07-02 15:29:07.344 CST [504952] LOG: archive command failed with exit code 103 2024-07-02 15:29:07.344 CST [504952] DETAIL: The failed archive command was: pgbackrest --stanza=pg01 archive-push pg_wal/000000010000000000000001 2024-07-02 15:29:07.344 CST [504952] WARNING: archiving write-ahead log file "000000010000000000000001" failed too many times, will try again later |
原因：互信配置之后，配置的是pg16用户，所以应该添加：repo1-host-user=pg16
全量备份
1 2 3 4 5 | -- 远程备份 pgbackrest --stanza=pg01 --log-level-console=info backup --type=full -- 本地备份 pgbackrest --stanza=pg02 --log-level-console=info backup --type=full |
日志：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | [pg16@lhrpgall2 ~]$ pgbackrest --stanza=pg01 --log-level-console=info backup 2024-07-02 15:35:53.070 P00 INFO: backup command begin 2.52.1: --exec-id=8566-e8dc7705 --log-level-console=info --log-path=/var/log/pgbackrest --pg1-host=192.92.0.23 --pg1-host-config-path=/etc/pgbackrest --pg1-host-port=22 --pg1-host-user=pg16 --pg1-path=/pg16/pgdata --pg1-port=5441 --pg1-user=postgres --repo1-path=/bk/repos --repo1-retention-full=2 --stanza=pg01 WARN: no prior backup exists, incr backup has been changed to full 2024-07-02 15:35:53.914 P00 INFO: execute non-exclusive backup start: backup begins after the next regular checkpoint completes 2024-07-02 15:35:54.617 P00 INFO: backup start archive = 00000001000000000000000E, lsn = 0/E000028 2024-07-02 15:35:54.617 P00 INFO: check archive for prior segment 00000001000000000000000D WARN: exclude special file '/pg16/pgdata/.s.PGSQL.5441' from backup 2024-07-02 15:35:58.300 P00 INFO: execute non-exclusive backup stop and wait for all WAL segments to archive 2024-07-02 15:35:58.501 P00 INFO: backup stop archive = 00000001000000000000000E, lsn = 0/E000138 2024-07-02 15:35:58.504 P00 INFO: check archive for segment(s) 00000001000000000000000E:00000001000000000000000E 2024-07-02 15:35:58.714 P00 INFO: new backup label = 20240702-153553F 2024-07-02 15:35:58.743 P00 INFO: full backup size = 22.0MB, file total = 979 2024-07-02 15:35:58.743 P00 INFO: backup command end: completed successfully (5673ms) 2024-07-02 15:35:58.743 P00 INFO: expire command begin 2.52.1: --exec-id=8566-e8dc7705 --log-level-console=info --log-path=/var/log/pgbackrest --repo1-path=/bk/repos --repo1-retention-full=2 --stanza=pg01 2024-07-02 15:35:58.746 P00 INFO: expire command end: completed successfully (3ms) [pg16@lhrpgall2 ~]$ pgbackrest --stanza=pg02 --log-level-console=info backup 2024-07-02 15:36:13.599 P00 INFO: backup command begin 2.52.1: --exec-id=8644-807913a0 --log-level-console=info --log-path=/var/log/pgbackrest --pg1-path=/pg16/pgdata --pg1-port=5441 --repo1-path=/bk/repos --repo1-retention-full=2 --stanza=pg02 WARN: no prior backup exists, incr backup has been changed to full 2024-07-02 15:36:14.304 P00 INFO: execute non-exclusive backup start: backup begins after the next regular checkpoint completes 2024-07-02 15:36:14.805 P00 INFO: backup start archive = 000000010000000000000005, lsn = 0/5000028 2024-07-02 15:36:14.805 P00 INFO: check archive for prior segment 000000010000000000000004 WARN: exclude special file '/pg16/pgdata/.s.PGSQL.5441' from backup 2024-07-02 15:36:17.447 P00 INFO: execute non-exclusive backup stop and wait for all WAL segments to archive 2024-07-02 15:36:17.647 P00 INFO: backup stop archive = 000000010000000000000005, lsn = 0/5000138 2024-07-02 15:36:17.649 P00 INFO: check archive for segment(s) 000000010000000000000005:000000010000000000000005 2024-07-02 15:36:17.658 P00 INFO: new backup label = 20240702-153614F 2024-07-02 15:36:17.688 P00 INFO: full backup size = 21.9MB, file total = 971 2024-07-02 15:36:17.688 P00 INFO: backup command end: completed successfully (4090ms) 2024-07-02 15:36:17.688 P00 INFO: expire command begin 2.52.1: --exec-id=8644-807913a0 --log-level-console=info --log-path=/var/log/pgbackrest --repo1-path=/bk/repos --repo1-retention-full=2 --stanza=pg02 2024-07-02 15:36:17.692 P00 INFO: expire command end: completed successfully (4ms) |
差异备份
1 2 | pgbackrest --stanza=pg01 --log-level-console=info backup --type=diff pgbackrest --stanza=pg02 --log-level-console=info backup --type=diff |
日志：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | [pg16@lhrpgall2 ~]$ pgbackrest --stanza=pg01 --type=diff --log-level-console=info backup 2024-07-02 15:40:49.516 P00 INFO: backup command begin 2.52.1: --exec-id=8921-46766b42 --log-level-console=info --log-path=/var/log/pgbackrest --pg1-host=192.92.0.23 --pg1-host-config-path=/etc/pgbackrest --pg1-host-port=22 --pg1-host-user=pg16 --pg1-path=/pg16/pgdata --pg1-port=5441 --pg1-user=postgres --repo1-path=/bk/repos --repo1-retention-full=2 --stanza=pg01 --type=diff 2024-07-02 15:40:50.364 P00 INFO: last backup label = 20240702-153553F, version = 2.52.1 2024-07-02 15:40:50.364 P00 INFO: execute non-exclusive backup start: backup begins after the next regular checkpoint completes 2024-07-02 15:40:50.867 P00 INFO: backup start archive = 000000010000000000000010, lsn = 0/10000028 2024-07-02 15:40:50.867 P00 INFO: check archive for prior segment 00000001000000000000000F WARN: exclude special file '/pg16/pgdata/.s.PGSQL.5441' from backup 2024-07-02 15:40:52.763 P00 INFO: execute non-exclusive backup stop and wait for all WAL segments to archive 2024-07-02 15:40:52.965 P00 INFO: backup stop archive = 000000010000000000000010, lsn = 0/10000100 2024-07-02 15:40:52.969 P00 INFO: check archive for segment(s) 000000010000000000000010:000000010000000000000010 2024-07-02 15:40:53.379 P00 INFO: new backup label = 20240702-153553F_20240702-154050D 2024-07-02 15:40:53.414 P00 INFO: diff backup size = 94.8KB, file total = 979 2024-07-02 15:40:53.414 P00 INFO: backup command end: completed successfully (3899ms) 2024-07-02 15:40:53.414 P00 INFO: expire command begin 2.52.1: --exec-id=8921-46766b42 --log-level-console=info --log-path=/var/log/pgbackrest --repo1-path=/bk/repos --repo1-retention-full=2 --stanza=pg01 2024-07-02 15:40:53.418 P00 INFO: expire command end: completed successfully (4ms) [pg16@lhrpgall2 ~]$ [pg16@lhrpgall2 ~]$ pgbackrest --stanza=pg02 --type=diff --log-level-console=info backup 2024-07-02 15:40:58.407 P00 INFO: backup command begin 2.52.1: --exec-id=8991-28da1a9f --log-level-console=info --log-path=/var/log/pgbackrest --pg1-path=/pg16/pgdata --pg1-port=5441 --repo1-path=/bk/repos --repo1-retention-full=2 --stanza=pg02 --type=diff 2024-07-02 15:40:59.121 P00 INFO: last backup label = 20240702-153614F, version = 2.52.1 2024-07-02 15:40:59.121 P00 INFO: execute non-exclusive backup start: backup begins after the next regular checkpoint completes 2024-07-02 15:40:59.622 P00 INFO: backup start archive = 000000010000000000000007, lsn = 0/7000028 2024-07-02 15:40:59.623 P00 INFO: check archive for prior segment 000000010000000000000006 WARN: exclude special file '/pg16/pgdata/.s.PGSQL.5441' from backup 2024-07-02 15:41:00.519 P00 INFO: execute non-exclusive backup stop and wait for all WAL segments to archive 2024-07-02 15:41:00.719 P00 INFO: backup stop archive = 000000010000000000000007, lsn = 0/7000100 2024-07-02 15:41:00.725 P00 INFO: check archive for segment(s) 000000010000000000000007:000000010000000000000007 2024-07-02 15:41:00.735 P00 INFO: new backup label = 20240702-153614F_20240702-154059D 2024-07-02 15:41:00.784 P00 INFO: diff backup size = 25.8KB, file total = 971 2024-07-02 15:41:00.785 P00 INFO: backup command end: completed successfully (2379ms) 2024-07-02 15:41:00.785 P00 INFO: expire command begin 2.52.1: --exec-id=8991-28da1a9f --log-level-console=info --log-path=/var/log/pgbackrest --repo1-path=/bk/repos --repo1-retention-full=2 --stanza=pg02 2024-07-02 15:41:00.792 P00 INFO: expire command end: completed successfully (7ms) [pg16@lhrpgall2 ~]$ |
增量备份
1 2 | pgbackrest --stanza=pg01 --log-level-console=info backup --type=incr pgbackrest --stanza=pg02 --log-level-console=info backup --type=incr |
日志：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | [pg16@lhrpgall2 ~]$ pgbackrest --stanza=pg01 --type=incr --log-level-console=info backup 2024-07-02 15:41:16.241 P00 INFO: backup command begin 2.52.1: --exec-id=9011-a4ef03ea --log-level-console=info --log-path=/var/log/pgbackrest --pg1-host=192.92.0.23 --pg1-host-config-path=/etc/pgbackrest --pg1-host-port=22 --pg1-host-user=pg16 --pg1-path=/pg16/pgdata --pg1-port=5441 --pg1-user=postgres --repo1-path=/bk/repos --repo1-retention-full=2 --stanza=pg01 --type=incr 2024-07-02 15:41:17.101 P00 INFO: last backup label = 20240702-153553F_20240702-154050D, version = 2.52.1 2024-07-02 15:41:17.101 P00 INFO: execute non-exclusive backup start: backup begins after the next regular checkpoint completes 2024-07-02 15:41:17.604 P00 INFO: backup start archive = 000000010000000000000012, lsn = 0/12000028 2024-07-02 15:41:17.604 P00 INFO: check archive for prior segment 000000010000000000000011 WARN: exclude special file '/pg16/pgdata/.s.PGSQL.5441' from backup 2024-07-02 15:41:18.766 P00 INFO: execute non-exclusive backup stop and wait for all WAL segments to archive 2024-07-02 15:41:18.967 P00 INFO: backup stop archive = 000000010000000000000012, lsn = 0/12000100 2024-07-02 15:41:18.972 P00 INFO: check archive for segment(s) 000000010000000000000012:000000010000000000000012 2024-07-02 15:41:19.382 P00 INFO: new backup label = 20240702-153553F_20240702-154116I 2024-07-02 15:41:19.414 P00 INFO: incr backup size = 95.2KB, file total = 979 2024-07-02 15:41:19.415 P00 INFO: backup command end: completed successfully (3175ms) 2024-07-02 15:41:19.415 P00 INFO: expire command begin 2.52.1: --exec-id=9011-a4ef03ea --log-level-console=info --log-path=/var/log/pgbackrest --repo1-path=/bk/repos --repo1-retention-full=2 --stanza=pg01 2024-07-02 15:41:19.419 P00 INFO: expire command end: completed successfully (4ms) [pg16@lhrpgall2 ~]$ [pg16@lhrpgall2 ~]$ pgbackrest --stanza=pg02 --type=incr --log-level-console=info backup 2024-07-02 15:41:23.523 P00 INFO: backup command begin 2.52.1: --exec-id=9082-61d5b820 --log-level-console=info --log-path=/var/log/pgbackrest --pg1-path=/pg16/pgdata --pg1-port=5441 --repo1-path=/bk/repos --repo1-retention-full=2 --stanza=pg02 --type=incr 2024-07-02 15:41:24.236 P00 INFO: last backup label = 20240702-153614F_20240702-154059D, version = 2.52.1 2024-07-02 15:41:24.236 P00 INFO: execute non-exclusive backup start: backup begins after the next regular checkpoint completes 2024-07-02 15:41:24.737 P00 INFO: backup start archive = 000000010000000000000009, lsn = 0/9000028 2024-07-02 15:41:24.737 P00 INFO: check archive for prior segment 000000010000000000000008 WARN: exclude special file '/pg16/pgdata/.s.PGSQL.5441' from backup 2024-07-02 15:41:25.518 P00 INFO: execute non-exclusive backup stop and wait for all WAL segments to archive 2024-07-02 15:41:25.718 P00 INFO: backup stop archive = 000000010000000000000009, lsn = 0/9000100 2024-07-02 15:41:25.721 P00 INFO: check archive for segment(s) 000000010000000000000009:000000010000000000000009 2024-07-02 15:41:25.729 P00 INFO: new backup label = 20240702-153614F_20240702-154124I 2024-07-02 15:41:25.761 P00 INFO: incr backup size = 26.1KB, file total = 971 2024-07-02 15:41:25.762 P00 INFO: backup command end: completed successfully (2240ms) 2024-07-02 15:41:25.762 P00 INFO: expire command begin 2.52.1: --exec-id=9082-61d5b820 --log-level-console=info --log-path=/var/log/pgbackrest --repo1-path=/bk/repos --repo1-retention-full=2 --stanza=pg02 2024-07-02 15:41:25.766 P00 INFO: expire command end: completed successfully (4ms) [pg16@lhrpgall2 ~]$ |
查看备份信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | [pg16@lhrpgall2 ~]$ pgbackrest info stanza: pg01 status: ok cipher: none db (current) wal archive min/max (16): 000000010000000000000001/000000010000000000000012 full backup: 20240702-153553F timestamp start/stop: 2024-07-02 15:35:53+08 / 2024-07-02 15:35:58+08 wal start/stop: 00000001000000000000000E / 00000001000000000000000E database size: 22.0MB, database backup size: 22.0MB repo1: backup set size: 2.9MB, backup size: 2.9MB diff backup: 20240702-153553F_20240702-154050D timestamp start/stop: 2024-07-02 15:40:50+08 / 2024-07-02 15:40:52+08 wal start/stop: 000000010000000000000010 / 000000010000000000000010 database size: 22.0MB, database backup size: 94.8KB repo1: backup set size: 2.9MB, backup size: 5.3KB backup reference list: 20240702-153553F incr backup: 20240702-153553F_20240702-154116I timestamp start/stop: 2024-07-02 15:41:16+08 / 2024-07-02 15:41:18+08 wal start/stop: 000000010000000000000012 / 000000010000000000000012 database size: 22.0MB, database backup size: 95.2KB repo1: backup set size: 2.9MB, backup size: 5.4KB backup reference list: 20240702-153553F, 20240702-153553F_20240702-154050D stanza: pg02 status: ok cipher: none db (current) wal archive min/max (16): 000000010000000000000001/000000010000000000000009 full backup: 20240702-153614F timestamp start/stop: 2024-07-02 15:36:14+08 / 2024-07-02 15:36:17+08 wal start/stop: 000000010000000000000005 / 000000010000000000000005 database size: 21.9MB, database backup size: 21.9MB repo1: backup set size: 2.9MB, backup size: 2.9MB diff backup: 20240702-153614F_20240702-154059D timestamp start/stop: 2024-07-02 15:40:59+08 / 2024-07-02 15:41:00+08 wal start/stop: 000000010000000000000007 / 000000010000000000000007 database size: 21.9MB, database backup size: 25.8KB repo1: backup set size: 2.9MB, backup size: 2.3KB backup reference list: 20240702-153614F incr backup: 20240702-153614F_20240702-154124I timestamp start/stop: 2024-07-02 15:41:24+08 / 2024-07-02 15:41:25+08 wal start/stop: 000000010000000000000009 / 000000010000000000000009 database size: 21.9MB, database backup size: 26.1KB repo1: backup set size: 2.9MB, backup size: 2.3KB backup reference list: 20240702-153614F, 20240702-153614F_20240702-154059D [pg16@lhrpgall2 ~]$ |
备份监控
PostgreSQL 将通过COPY命令允许 pgBackRest信息加载到表中。以下示例将该逻辑包装在可用于执行实时查询的函数中。
1 2 3 4 5 6 7 8 9 10 | [pg16@lhrpgall2 ~]$ psql -f /soft/pgbackrest-release-2.52.1/doc/example/pgsql-pgbackrest-info.sql CREATE SCHEMA CREATE FUNCTION [pg16@lhrpgall2 ~]$ psql -f /soft/pgbackrest-release-2.52.1/doc/example/pgsql-pgbackrest-query.sql name | last_successful_backup | last_archived_wal --------+------------------------+-------------------------- "pg01" | 2024-07-02 15:41:18+08 | 000000010000000000000012 "pg02" | 2024-07-02 15:41:25+08 | 000000010000000000000009 (2 rows) |
还原
默认还原到最新的时间点：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 | [pg16@lhrpgall pgdata]$ pgbackrest --stanza=pg01 restore --force --log-level-console=info 2024-07-02 16:02:32.078 P00 INFO: restore command begin 2.52.1: --exec-id=507954-8fe7c33a --force --log-level-console=info --log-level-file=detail --log-path=/var/log/pgbackrest --pg1-path=/pg16/pgdata --repo1-host=192.92.0.42 --repo1-host-user=pg16 --repo1-path=/bk/repos --stanza=pg01 WARN: --delta or --force specified but unable to find 'PG_VERSION' or 'backup.manifest' in '/pg16/pgdata' to confirm that this is a valid $PGDATA directory. --delta and --force have been disabled and if any files exist in the destination directories the restore will be aborted. 2024-07-02 16:02:32.323 P00 INFO: repo1: restore backup set 20240702-153553F_20240702-154116I, recovery will start at 2024-07-02 15:41:16 WARN: unknown group 'postgres' in backup manifest mapped to current group ERROR: [040]: unable to restore to path '/pg16/pgdata' because it contains files HINT: try using --delta if this is what you intended. 2024-07-02 16:02:32.323 P00 INFO: restore command end: aborted with exception [040] [pg16@lhrpgall pgdata]$ cd .. [pg16@lhrpgall pg16]$ ps -ef|grep pg16 root 504681 497498 0 15:08 pts/1 00:00:00 su - pg16 pg16 504682 504681 0 15:08 pts/1 00:00:00 -bash pg16 507969 504682 0 16:02 pts/1 00:00:00 ps -ef pg16 507970 504682 0 16:02 pts/1 00:00:00 grep --color=auto pg16 [pg16@lhrpgall pg16]$ cd pgdata/ [pg16@lhrpgall pgdata]$ ll total 0 [pg16@lhrpgall pgdata]$ [pg16@lhrpgall pgdata]$ ll -a total 8 drwx------ 1 pg16 postgres 4096 Jul 2 15:59 . drwxrwxr-x 1 pg16 postgres 20 Oct 20 2023 .. -rw------- 1 pg16 pg16 48 Oct 20 2023 .s.PGSQL.5440.lock [pg16@lhrpgall pgdata]$ rm -rf .s.PGSQL.5440.lock [pg16@lhrpgall pgdata]$ ll total 0 [pg16@lhrpgall pgdata]$ pgbackrest --stanza=pg01 restore --force --log-level-console=info 2024-07-02 16:11:44.459 P00 INFO: restore command begin 2.52.1: --exec-id=508418-14f3cd55 --force --log-level-console=info --log-level-file=detail --log-path=/var/log/pgbackrest --pg1-path=/pg16/pgdata --repo1-host=192.92.0.42 --repo1-host-user=pg16 --repo1-path=/bk/repos --stanza=pg01 WARN: --delta or --force specified but unable to find 'PG_VERSION' or 'backup.manifest' in '/pg16/pgdata' to confirm that this is a valid $PGDATA directory. --delta and --force have been disabled and if any files exist in the destination directories the restore will be aborted. 2024-07-02 16:11:44.706 P00 INFO: repo1: restore backup set 20240702-153553F_20240702-154116I, recovery will start at 2024-07-02 15:41:16 WARN: unknown group 'postgres' in backup manifest mapped to current group 2024-07-02 16:11:47.666 P00 INFO: write updated /pg16/pgdata/postgresql.auto.conf 2024-07-02 16:11:47.672 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started) 2024-07-02 16:11:47.672 P00 INFO: restore size = 22.0MB, file total = 979 2024-07-02 16:11:47.673 P00 INFO: restore command end: completed successfully (3215ms) [pg16@lhrpgall pgdata]$ ll total 64 -rw------- 1 pg16 pg16 260 Jul 2 15:41 backup_label drwx------ 5 pg16 pg16 33 Jul 2 16:11 base -rw------- 1 pg16 pg16 33 Jul 2 15:11 current_logfiles drwx------ 2 pg16 pg16 4096 Jul 2 16:11 global drwx------ 2 pg16 pg16 6 Jul 2 16:11 pg_commit_ts drwx------ 2 pg16 pg16 6 Jul 2 16:11 pg_dynshmem -rw------- 1 pg16 pg16 5954 Oct 20 2023 pg_hba.conf -rw------- 1 pg16 pg16 2640 Oct 20 2023 pg_ident.conf drwx------ 2 pg16 pg16 188 Jul 2 16:11 pg_log drwx------ 4 pg16 pg16 68 Jul 2 16:11 pg_logical drwx------ 4 pg16 pg16 36 Jul 2 16:11 pg_multixact drwx------ 2 pg16 pg16 6 Jul 2 16:11 pg_notify drwx------ 2 pg16 pg16 6 Jul 2 16:11 pg_replslot drwx------ 2 pg16 pg16 6 Jul 2 16:11 pg_serial drwx------ 2 pg16 pg16 6 Jul 2 16:11 pg_snapshots drwx------ 2 pg16 pg16 6 Jul 2 16:11 pg_stat drwx------ 2 pg16 pg16 6 Jul 2 16:11 pg_stat_tmp drwx------ 2 pg16 pg16 6 Jul 2 16:11 pg_subtrans drwx------ 2 pg16 pg16 6 Jul 2 16:11 pg_tblspc drwx------ 2 pg16 pg16 6 Jul 2 16:11 pg_twophase -rw------- 1 pg16 pg16 3 Oct 20 2023 PG_VERSION drwx------ 3 pg16 pg16 28 Jul 2 16:11 pg_wal drwx------ 2 pg16 pg16 18 Jul 2 16:11 pg_xact -rw------- 1 pg16 pg16 378 Jul 2 16:11 postgresql.auto.conf -rw------- 1 pg16 pg16 29868 Oct 20 2023 postgresql.conf -rw------- 1 pg16 pg16 0 Jul 2 16:11 recovery.signal [pg16@lhrpgall pgdata]$ pg_ctl start waiting for server to start....2024-07-02 16:15:54.898 CST [508687] LOG: redirecting log output to logging collector process 2024-07-02 16:15:54.898 CST [508687] HINT: Future log output will appear in directory "pg_log". done server started [pg16@lhrpgall pgdata]$ |
基于时间点的恢复（Point-in-Time Recovery）
参考：https://pgbackrest.org/user-guide-rhel.html
可以基于时间time或lsn进行增量恢复：
1 2 3 4 5 6 7 8 9 10 11 | pgbackrest info pgbackrest --stanza=pg01 --set=20240703-141740F info pgbackrest --stanza=pg01 --log-level-console=info --delta \ --set=20240703-141740F --target-timeline=current \ --type=lsn "--target=0/5000100" --target-action=promote restore pgbackrest --stanza=pg01 --log-level-console=info --delta \ --set=20240702-163310F --target-timeline=current \ --type=time "--target=2024-07-02 16:33:16+08" --target-action=promote restore |
日志：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | [pg16@lhrpgall pgdata]$ pgbackrest --stanza=pg01 --log-level-console=info --delta \ > --set=20240702-163310F --target-timeline=current \ > --type=time "--target=2024-07-02 16:33:16+08" --target-action=promote restore 2024-07-02 16:44:07.320 P00 INFO: restore command begin 2.52.1: --delta --exec-id=510444-ce5526e6 --log-level-console=info --log-level-file=detail --log-path=/var/log/pgbackrest --pg1-path=/pg16/pgdata --repo1-host=192.92.0.42 --repo1-host-user=pg16 --repo1-path=/bk/repos --set=20240702-163310F --stanza=pg01 --target="2024-07-02 16:33:16+08" --target-action=promote --target-timeline=current --type=time WARN: --delta or --force specified but unable to find 'PG_VERSION' or 'backup.manifest' in '/pg16/pgdata' to confirm that this is a valid $PGDATA directory. --delta and --force have been disabled and if any files exist in the destination directories the restore will be aborted. 2024-07-02 16:44:07.568 P00 INFO: repo1: restore backup set 20240702-163310F, recovery will start at 2024-07-02 16:33:10 2024-07-02 16:44:10.525 P00 INFO: write updated /pg16/pgdata/postgresql.auto.conf 2024-07-02 16:44:10.532 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started) 2024-07-02 16:44:10.532 P00 INFO: restore size = 36.2MB, file total = 1575 2024-07-02 16:44:10.532 P00 INFO: restore command end: completed successfully (3213ms) [pg16@lhrpgall pgdata]$ psql psql: error: connection to server on socket "/pg16/pgdata/.s.PGSQL.5441" failed: No such file or directory Is the server running locally and accepting connections on that socket? [pg16@lhrpgall pgdata]$ pg_ctl start waiting for server to start....2024-07-02 16:44:20.986 CST [510462] LOG: redirecting log output to logging collector process 2024-07-02 16:44:20.986 CST [510462] HINT: Future log output will appear in directory "pg_log". done server started |
增量 vs 差异备份
Pgbackrest是一个强大的PostgreSQL备份和恢复工具，它使用增量备份策略。
增量备份（incr）备份自上次全量或增量或差异备份之后更改的数据。这意味着每次备份只会备份新增或修改过的数据块，从而减少了备份所需的时间和存储空间。在Pgbackrest中，每个增量备份都建立在上一个完整备份或增量备份的基础上。
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
