用pgBackRest 在同机器创建备库

首先检查备份：

[postgres@gzdba-postgres ivandb]$ pgbackrest  --stanza=pgdb1 info
stanza: pgdb1
    status: ok
    cipher: aes-256-cbc

    db (current)
        wal archive min/max (14): 0000000300000002000000C1/0000000500000002000000CC

        full backup: 20241227-225253F
            timestamp start/stop: 2024-12-27 22:52:53+08 / 2024-12-27 22:54:15+08
            wal start/stop: 0000000300000002000000C2 / 0000000300000002000000C2
            database size: 3GB, database backup size: 3GB
            repo1: backup set size: 384.3MB, backup size: 384.3MB

        incr backup: 20241227-225253F_20241227-225525I
            timestamp start/stop: 2024-12-27 22:55:25+08 / 2024-12-27 22:55:27+08
            wal start/stop: 0000000300000002000000C4 / 0000000300000002000000C4
            database size: 3GB, database backup size: 45.7KB
            repo1: backup set size: 384.3MB, backup size: 4.7KB
            backup reference list: 20241227-225253F

        diff backup: 20241227-225253F_20241228-161806D
            timestamp start/stop: 2024-12-28 16:18:06+08 / 2024-12-28 16:18:14+08
            wal start/stop: 0000000400000002000000CF / 0000000400000002000000CF
            database size: 3GB, database backup size: 16.7MB
            repo1: backup set size: 388.4MB, backup size: 4.7MB
            backup reference list: 20241227-225253F
复制

创建备库目录：

mkdir -p /data/ivandb/pgdbstandby
复制

复制pgbackrest 配置文件：/etc/pgbackrest/pgbackrest.conf

cp /etc/pgbackrest/pgbackrest.conf  /etc/pgbackrest/pgbackrest.conf_forstandby 
复制

编辑  /etc/pgbackrest/pgbackrest.conf_forstandby 文件，修改pg1-path 使其指向到 /data/ivandb/pgdbstandby

pg1-path=/data/ivandb/pgdbstandby

进行恢复：

pgbackrest --config=/etc/pgbackrest/pgbackrest.conf_forstandby --type=standby  --log-level-console=detail  --stanza=pgdb1  restore 
复制

内容输出很多，截取一点：

2024-12-28 16:32:21.942 P00 DETAIL: sync path '/data/ivandb/pgdbstandby/pg_multixact/offsets'
2024-12-28 16:32:21.942 P00 DETAIL: sync path '/data/ivandb/pgdbstandby/pg_notify'
2024-12-28 16:32:21.942 P00 DETAIL: sync path '/data/ivandb/pgdbstandby/pg_replslot'
2024-12-28 16:32:21.942 P00 DETAIL: sync path '/data/ivandb/pgdbstandby/pg_serial'
2024-12-28 16:32:21.942 P00 DETAIL: sync path '/data/ivandb/pgdbstandby/pg_snapshots'
2024-12-28 16:32:21.942 P00 DETAIL: sync path '/data/ivandb/pgdbstandby/pg_stat'
2024-12-28 16:32:21.942 P00 DETAIL: sync path '/data/ivandb/pgdbstandby/pg_stat_tmp'
2024-12-28 16:32:21.942 P00 DETAIL: sync path '/data/ivandb/pgdbstandby/pg_subtrans'
2024-12-28 16:32:21.942 P00 DETAIL: sync path '/data/ivandb/pgdbstandby/pg_tblspc'
2024-12-28 16:32:21.942 P00 DETAIL: sync path '/data/ivandb/pgdbstandby/pg_twophase'
2024-12-28 16:32:21.942 P00 DETAIL: sync path '/data/ivandb/pgdbstandby/pg_wal'
2024-12-28 16:32:21.942 P00 DETAIL: sync path '/data/ivandb/pgdbstandby/pg_wal/archive_status'
2024-12-28 16:32:21.942 P00 DETAIL: sync path '/data/ivandb/pgdbstandby/pg_xact'
2024-12-28 16:32:21.946 P00   INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2024-12-28 16:32:21.946 P00 DETAIL: sync path '/data/ivandb/pgdbstandby/global'
2024-12-28 16:32:21.947 P00   INFO: restore size = 3GB, file total = 1461
2024-12-28 16:32:21.947 P00   INFO: restore command end: completed successfully (24180ms)
复制

配置备库, 运行在端口6433：

export PGDATA=/data/ivandb/pgdbstandby
export PGPORT=6433
cd $PGDATA
复制

因为是在本机注意修改以下选项，同时省去了pg_hba.conf 以及配置专门的同步用户：

添加到 postgresql.auto.conf

archive_command= 'test ! -f /data/ivandb/archive/pgdbstandby/%f && cp %p /data/ivandb/archive/pgdbstandby/%f;pgbackrest --stanza=pgdb1 archive-push %p'
primary_conninfo = 'host=localhost port=6432 user=postgres'
hot_standby = on 
复制

启动：

pg_ctl start -D /data/ivandb/pgdbstandby
复制

备库检查，返回为t：

select pg_is_in_recovery();
复制

主库检查：

\x
select * from pg_stat_replication;

-[ RECORD 1 ]----+------------------------------
pid              | 4370
usesysid         | 10
usename          | postgres
application_name | Ivanstandby
client_addr      | ::1
client_hostname  | 
client_port      | 34101
backend_start    | 2024-12-28 16:54:58.457621+08
backend_xmin     | 
state            | streaming
sent_lsn         | 2/D2031E88
write_lsn        | 2/D2031E88
flush_lsn        | 2/D2031E88
replay_lsn       | 2/D2031E88
write_lag        | 00:00:00.000087
flush_lag        | 00:00:00.000699
replay_lag       | 00:00:00.000733
sync_priority    | 0
sync_state       | async
reply_time       | 2024-12-28 17:03:30.805672+08
复制

所以，pg1-path 配置决定了该备份应该恢复到哪个目录。异机原理差不多，写好配置文件，指定恢复pg-path 便可恢复。加密串是必须的。