合 PG备份恢复工具之pgbackrest
Tags： PGPostgreSQL备份恢复pgbackrest
👉 本文共约1899个字，系统预计阅读时间或需8分钟。
目录
简介
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 |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
