pgBackRest 多次PITR的坑

##

pgBackRest 多次PITR的坑

1、场景复现

前提：已经做了PITR，然后删库，再做PITR

[postgres@pgdb01:/home/postgres]$pgbackrest info
stanza: pgdb01
    status: ok
    cipher: none

    db (current)
        wal archive min/max (15): 00000004000000000000001E/000000060000000000000024
    
        full backup: 20240227-104337F
            timestamp start/stop: 2024-02-27 10:43:37+08 / 2024-02-27 10:43:41+08
            wal start/stop: 00000004000000000000001E / 00000004000000000000001E
            database size: 37.2MB, database backup size: 37.2MB
            repo1: backup set size: 5.7MB, backup size: 5.7MB
    
        full backup: 20240227-110502F
            timestamp start/stop: 2024-02-27 11:05:02+08 / 2024-02-27 11:05:06+08
            wal start/stop: 000000050000000000000020 / 000000050000000000000020
            database size: 37.2MB, database backup size: 37.2MB
            repo1: backup set size: 5.7MB, backup size: 5.7MB
    
        incr backup: 20240227-110502F_20240227-110709I
            timestamp start/stop: 2024-02-27 11:07:09+08 / 2024-02-27 11:07:12+08
            wal start/stop: 000000050000000000000022 / 000000050000000000000023
            database size: 37.2MB, database backup size: 1.5MB
            repo1: backup set size: 5.7MB, backup size: 183.4KB
            backup reference list: 20240227-110502F
    
        incr backup: 20240227-110502F_20240227-110728I
            timestamp start/stop: 2024-02-27 11:07:28+08 / 2024-02-27 11:07:31+08
            wal start/stop: 000000050000000000000025 / 000000050000000000000026
            database size: 37.3MB, database backup size: 1.5MB
            repo1: backup set size: 5.7MB, backup size: 183.5KB
            backup reference list: 20240227-110502F, 20240227-110502F_20240227-110709I

[postgres@pgdb01:/home/postgres]$pg_ctl -m fast stop
rm -rf /postgresql/data/*
rm -rf /postgresql/wal/*
rm -rf /postgresql/archive/*
waiting for server to shut down.... done
server stopped

[postgres@pgdb01:/home/postgres]$pgbackrest restore --stanza=pgdb01 --delta --type=time "--target=2024-02-27 11:07:09+08" --log-level-console=info --target-action=promote --link-all
2024-02-27 11:44:26.357 P00   INFO: restore command begin 2.50: --delta --exec-id=8908-86bee3ba --link-all --log-level-console=info --pg1-path=/postgresql/data --repo1-path=/postgresql/backup/pgbackrest --stanza=pgdb01 --target="2024-02-27 11:07:09+08" --target-action=promote --type=time
WARN: --delta or --force specified but unable to find 'PG_VERSION' or 'backup.manifest' in '/postgresql/data' 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-02-27 11:44:26.371 P00   INFO: repo1: restore backup set 20240227-110502F, recovery will start at 2024-02-27 11:05:02
2024-02-27 11:44:28.016 P00   INFO: write updated /postgresql/data/postgresql.auto.conf
2024-02-27 11:44:28.022 P00   INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2024-02-27 11:44:28.024 P00   INFO: restore size = 37.2MB, file total = 1345
2024-02-27 11:44:28.025 P00   INFO: restore command end: completed successfully (1671ms)
[postgres@pgdb01:/home/postgres]$pg_ctl start
waiting for server to start....2024-02-27 11:44:31.726 CST [8912] LOG:  redirecting log output to logging collector process
2024-02-27 11:44:31.726 CST [8912] HINT:  Future log output will appear in directory "/postgresql/log".
 done
server started
[postgres@pgdb01:/home/postgres]$psql
psql: error: connection to server on socket "/postgresql/data/.s.PGSQL.5432" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?
[postgres@pgdb01:/home/postgres]$

从操作上来讲并没有什么错误。检查日志

2024-02-27 11:44:32.385 CST [8916] FATAL:  recovery ended before configured recovery target was reached
2024-02-27 11:44:32.389 CST [8912] LOG:  startup process (PID 8916) exited with exit code 1
2024-02-27 11:44:32.389 CST [8912] LOG:  terminating any other active server processes
2024-02-27 11:44:32.390 CST [8912] LOG:  shutting down due to startup process failure
2024-02-27 11:44:32.416 CST [8912] LOG:  database system is shut down

2、如何解决

[postgres@pgdb01:/home/postgres]$cd /postgresql/backup/pgbackrest/archive/pgdb01/15-1/
[postgres@pgdb01:/postgresql/backup/pgbackrest/archive/pgdb01/15-1]$ll
total 16
drwxr-x--- 2 postgres postgres 4096 Feb 27 11:05 0000000400000000
drwxr-x--- 2 postgres postgres 4096 Feb 27 11:10 0000000500000000
-rw-r----- 1 postgres postgres   35 Feb 27 10:53 00000005.history
drwxr-x--- 2 postgres postgres  158 Feb 27 11:44 0000000600000000
-rw-r----- 1 postgres postgres   87 Feb 27 11:37 00000006.history
[postgres@pgdb01:/postgresql/backup/pgbackrest/archive/pgdb01/15-1]$rm -fr 00000006*
[postgres@pgdb01:/postgresql/backup/pgbackrest/archive/pgdb01/15-1]$ll
total 12
drwxr-x--- 2 postgres postgres 4096 Feb 27 11:05 0000000400000000
drwxr-x--- 2 postgres postgres 4096 Feb 27 11:10 0000000500000000
-rw-r----- 1 postgres postgres   35 Feb 27 10:53 00000005.history
[postgres@pgdb01:/postgresql/backup/pgbackrest/archive/pgdb01/15-1]$
[postgres@pgdb01:/postgresql/backup/pgbackrest/archive/pgdb01/15-1]$cd
[postgres@pgdb01:/home/postgres]$
[postgres@pgdb01:/home/postgres]$
[postgres@pgdb01:/home/postgres]$

[postgres@pgdb01:/postgresql/backup/pgbackrest/archive/pgdb01/15-1]$pg_ctl -m fast stop
rm -rf /postgresql/data/*
rm -rf /postgresql/wal/*
rm -rf /postgresql/archive/*
pg_ctl: PID file "/postgresql/data/postmaster.pid" does not exist
Is server running?

[postgres@pgdb01:/postgresql/backup/pgbackrest/archive/pgdb01/15-1]$rm -fr /postgresql/data/.*

target-action=promote带来了新的时间线，所以删除就可以了

3、再次恢复

[postgres@pgdb01:/postgresql/backup/pgbackrest/archive/pgdb01/15-1]$pgbackrest restore --stanza=pgdb01 --delta --type=time "--target=2024-02-27 11:07:09+08" --log-level-console=info --target-action=promote --link-all
2024-02-27 11:50:56.234 P00   INFO: restore command begin 2.50: --delta --exec-id=9060-5c6c7dda --link-all --log-level-console=info --pg1-path=/postgresql/data --repo1-path=/postgresql/backup/pgbackrest --stanza=pgdb01 --target="2024-02-27 11:07:09+08" --target-action=promote --type=time
WARN: --delta or --force specified but unable to find 'PG_VERSION' or 'backup.manifest' in '/postgresql/data' 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-02-27 11:50:56.248 P00   INFO: repo1: restore backup set 20240227-110502F, recovery will start at 2024-02-27 11:05:02
2024-02-27 11:50:57.871 P00   INFO: write updated /postgresql/data/postgresql.auto.conf
2024-02-27 11:50:57.876 P00   INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2024-02-27 11:50:57.877 P00   INFO: restore size = 37.2MB, file total = 1345
2024-02-27 11:50:57.878 P00   INFO: restore command end: completed successfully (1647ms)

4、验证

[postgres@pgdb01:/postgresql/backup/pgbackrest/archive/pgdb01/15-1]$pg_ctl start
waiting for server to start....2024-02-27 11:51:01.292 CST [9064] LOG:  redirecting log output to logging collector process
2024-02-27 11:51:01.292 CST [9064] HINT:  Future log output will appear in directory "/postgresql/log".
 done
server started
[postgres@pgdb01:/postgresql/backup/pgbackrest/archive/pgdb01/15-1]$psql
psql (15.6)
Type "help" for help.

postgres=#

5、其他

尝试失败多次之后的时间线

[postgres@pgdb01:/postgresql/backup/pgbackrest/archive/pgdb01/15-1]$ll
total 28
drwxr-x--- 2 postgres postgres 4096 Feb 27 11:05 0000000400000000
drwxr-x--- 2 postgres postgres 4096 Feb 27 11:10 0000000500000000
-rw-r----- 1 postgres postgres   35 Feb 27 10:53 00000005.history
drwxr-x--- 2 postgres postgres   82 Feb 27 11:12 0000000600000000
-rw-r----- 1 postgres postgres   78 Feb 27 11:10 00000006.history
drwxr-x--- 2 postgres postgres   82 Feb 27 11:16 0000000700000000
-rw-r----- 1 postgres postgres  121 Feb 27 11:15 00000007.history
drwxr-x--- 2 postgres postgres   82 Feb 27 11:19 0000000800000000
-rw-r----- 1 postgres postgres  164 Feb 27 11:17 00000008.history
drwxr-x--- 2 postgres postgres   82 Feb 27 11:20 0000000900000000
-rw-r----- 1 postgres postgres  216 Feb 27 11:19 00000009.history