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

标题: 使用pgbackrest2.57 不完整恢复pitr PG18.1 [打印本页]

作者: 郑全 时间: 2026-1-2 19:19
标题: 使用pgbackrest2.57 不完整恢复pitr PG18.1
本帖最后由郑全于 2026-1-3 12:17 编辑

环境：
oracle linux 10.1 + pg 18.1

备份：
pgbackrest 5.27 进行了全备

要求：
把数据库恢复到某个操作之前。

下面进行具体的操作步骤：

1.准备数据
  \c cost

  begin;
create table important_table (message text);
insert into important_table values ('Important Data');
  commit;

  select * from important_table;

cost=#  select * from important_table;
message
----------------
Important Data
(1 row)

select current_timestamp;

cost=# select current_timestamp;
   current_timestamp
-------------------------------
2026-01-02 18:29:59.769442+08
(1 row)

2.删除  important_table 表
cost=# begin;
BEGIN
cost=*# drop table  important_table;
DROP TABLE
cost=*# commit;
COMMIT
cost=# select * from  important_table;
ERROR:  relation "important_table" does not exist
LINE 1: select * from  important_table;
                     ^
cost=#

3.做一个备份
postgres@pg181:/home$ pgbackrest --stanza=regcost --type=incr backup
2026-01-02 18:38:12.000 P00 WARN: a timeline switch has occurred since the 20260102-172025F backup, enabling delta checksum
                                 HINT: this is normal after restoring from backup or promoting a standby.

4.查看当前备份
  postgres@pg181:/home$ pgbackrest --stanza=regcost info
stanza: regcost
status: ok
cipher: none

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

      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

      incr backup: 20260102-172025F_20260102-183810I
         timestamp start/stop: 2026-01-02 18:38:10+08 / 2026-01-02 18:38:12+08
         wal start/stop: 00000002000000000000000E / 00000002000000000000000E
         database size: 31MB, database backup size: 7.7MB
         repo1: backup size: 1.3MB
         backup reference total: 1 full

5.停止数据库
  pg_ctl stop

6.使用增量备份做恢复
  # 指定破坏发生后的备份来恢复，将失败，数据库只能向前，不能退后

pgbackrest --stanza=regcost --delta \
   --set=20260102-172025F_20260102-183810I --target-timeline=current \
   --type=time "--target=2026-01-02 18:29:59.769442+08" --target-action=promote restore

postgres@pg181:/home$ pg_ctl start
waiting for server to start....2026-01-02 18:47:43.439 CST [13770] LOG:  redirecting log output to logging collector process
2026-01-02 18:47:43.439 CST [13770] HINT:  Future log output will appear in directory "pg_log".
stopped waiting
pg_ctl: could not start server
Examine the log output.

查看日志
postgres@pg181:/home$ tail -20 /pg18/data/pg_log/postgresql.log
2026-01-02 18:47:43.439 CST [13770] LOG:  starting PostgreSQL 18.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 14.3.1 20250617 (Red Hat 14.3.1-2), 64-bit
2026-01-02 18:47:43.440 CST [13770] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2026-01-02 18:47:43.440 CST [13770] LOG:  listening on IPv6 address "::", port 5432
2026-01-02 18:47:43.442 CST [13770] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2026-01-02 18:47:43.446 CST [13777] LOG:  database system was interrupted; last known up at 2026-01-02 18:38:10 CST
2026-01-02 18:47:43.459 CST [13777] LOG:  starting backup recovery with redo LSN 0/E000028, checkpoint LSN 0/E000080, on timeline ID 2
2026-01-02 18:47:43.472 CST [13777] LOG:  restored log file "00000002.history" from archive
2026-01-02 18:47:43.499 CST [13777] LOG:  restored log file "00000002000000000000000E" from archive
2026-01-02 18:47:43.514 CST [13777] LOG:  starting point-in-time recovery to 2026-01-02 18:29:59.769442+08
2026-01-02 18:47:43.515 CST [13777] LOG:  redo starts at 0/E000028
2026-01-02 18:47:43.541 CST [13777] LOG:  restored log file "00000002000000000000000F" from archive
2026-01-02 18:47:43.569 CST [13777] LOG:  completed backup recovery with redo LSN 0/E000028 and end LSN 0/E000158
2026-01-02 18:47:43.569 CST [13777] LOG:  consistent recovery state reached at 0/E000158
2026-01-02 18:47:43.569 CST [13770] LOG:  database system is ready to accept read-only connections
2026-01-02 18:47:43.582 CST [13777] LOG:  redo done at 0/F000060 system usage: CPU: user: 0.00 s, system: 0.01 s, elapsed: 0.06 s
2026-01-02 18:47:43.582 CST [13777] FATAL:  recovery ended before configured recovery target was reached
2026-01-02 18:47:43.583 CST [13770] LOG:  startup process (PID 13777) exited with exit code 1
2026-01-02 18:47:43.583 CST [13770] LOG:  terminating any other active server processes
2026-01-02 18:47:43.584 CST [13770] LOG:  shutting down due to startup process failure
2026-01-02 18:47:43.586 CST [13770] LOG:  database system is shut down

7.自动选择
让系统自动去找合适的备份
pgbackrest --stanza=regcost --delta \
   --type=time "--target=2026-01-02 18:29:59.769442+08" \
   --target-action=promote restore

查看日志

postgres@pg181:/home$ more /var/log/pgbackrest/regcost-restore.log
-------------------PROCESS START-------------------
2026-01-02 18:12:36.609 P00 INFO: restore command begin 2.57.0: --exec-id=12509-65e02530 --log-path=/var/log/pgbackrest --pg1-path=/pg18/data --process-ma
x=4 --repo1-path=/pg18/pgbackrest --stanza=regcost
2026-01-02 18:12:36.617 P00 INFO: repo1: restore backup set 20260102-172025F, recovery will start at 2026-01-02 17:20:25
2026-01-02 18:12:37.377 P00 INFO: write updated /pg18/data/postgresql.auto.conf
2026-01-02 18:12:37.381 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2026-01-02 18:12:37.382 P00 INFO: restore size = 23.3MB, file total = 970
2026-01-02 18:12:37.382 P00 INFO: restore command end: completed successfully (778ms)

-------------------PROCESS START-------------------
2026-01-02 18:45:19.072 P00 INFO: restore command begin 2.57.0: --delta --exec-id=13553-98677d1a --log-path=/var/log/pgbackrest --pg1-path=/pg18/data --pr
ocess-max=4 --repo1-path=/pg18/pgbackrest --set=20260102-172025F_20260102-183810I --stanza=regcost --target="2026-01-02 18:29:59.769442+08" --target-action=
promote --target-timeline=current --type=time
2026-01-02 18:45:19.083 P00 INFO: repo1: restore backup set 20260102-172025F_20260102-183810I, recovery will start at 2026-01-02 18:38:10
2026-01-02 18:45:19.085 P00 INFO: remove invalid files/links/paths from '/pg18/data'
2026-01-02 18:45:21.354 P00 INFO: write updated /pg18/data/postgresql.auto.conf
2026-01-02 18:45:21.358 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2026-01-02 18:45:21.358 P00 INFO: restore size = 31MB, file total = 1271
2026-01-02 18:45:21.359 P00 INFO: restore command end: completed successfully (2291ms)

-------------------PROCESS START-------------------
2026-01-02 18:52:35.328 P00 INFO: restore command begin 2.57.0: --delta --exec-id=13794-992db754 --log-path=/var/log/pgbackrest --pg1-path=/pg18/data --pr
ocess-max=4 --repo1-path=/pg18/pgbackrest --stanza=regcost --target="2026-01-02 18:29:59.769442+08" --target-action=promote --type=time
2026-01-02 18:52:35.336 P00 INFO: repo1: restore backup set 20260102-172025F, recovery will start at 2026-01-02 17:20:25
2026-01-02 18:52:35.338 P00 INFO: remove invalid files/links/paths from '/pg18/data'
2026-01-02 18:52:37.215 P00 INFO: write updated /pg18/data/postgresql.auto.conf
2026-01-02 18:52:37.218 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2026-01-02 18:52:37.219 P00 INFO: restore size = 23.3MB, file total = 970
2026-01-02 18:52:37.219 P00 INFO: restore command end: completed successfully (1896ms)

检查postgresql.auto.conf,已经被自动修改了

postgres@pg181:/home$ cd /pg18/data
postgres@pg181:/pg18/data$ ls -ltr
total 64
-rw------- 1 postgres postgres    3 Jan  1 18:29 PG_VERSION
-rw------- 1 postgres postgres  2681 Jan  1 18:29 pg_ident.conf
-rw------- 1 postgres postgres  5813 Jan  1 18:47 pg_hba.conf
-rw------- 1 postgres postgres 32596 Jan  2 16:59 postgresql.conf
-rw------- 1 postgres postgres 29 Jan  2 17:00 current_logfiles
-rw------- 1 postgres postgres 258 Jan  2 17:20 backup_label
drwx------ 2 postgres postgres    6 Jan  2 18:12 pg_twophase
drwx------ 2 postgres postgres    6 Jan  2 18:12 pg_tblspc
drwx------ 2 postgres postgres    6 Jan  2 18:12 pg_stat_tmp
drwx------ 2 postgres postgres    6 Jan  2 18:12 pg_snapshots
drwx------ 2 postgres postgres    6 Jan  2 18:12 pg_serial
drwx------ 2 postgres postgres    6 Jan  2 18:12 pg_replslot
drwx------ 2 postgres postgres    6 Jan  2 18:12 pg_notify
drwx------ 4 postgres postgres 36 Jan  2 18:12 pg_multixact
drwx------ 2 postgres postgres    6 Jan  2 18:12 pg_dynshmem
drwx------ 2 postgres postgres    6 Jan  2 18:12 pg_commit_ts
drwx------ 2 postgres postgres 18 Jan  2 18:12 pg_xact
drwx------ 2 postgres postgres 28 Jan  2 18:12 pg_log
drwx------ 4 postgres postgres 68 Jan  2 18:42 pg_logical
drwx------ 2 postgres postgres    6 Jan  2 18:45 pg_stat
drwx------ 2 postgres postgres    6 Jan  2 18:45 pg_subtrans
drwx------ 5 postgres postgres 33 Jan  2 18:52 base
drwx------ 4 postgres postgres 45 Jan  2 18:52 pg_wal
-rw------- 1 postgres postgres 322 Jan  2 18:52 postgresql.auto.conf
-rw------- 1 postgres postgres    0 Jan  2 18:52 recovery.signal
drwx------ 2 postgres postgres  4096 Jan  2 18:52 global

postgres@pg181:/pg18/data$ cat postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.

# Recovery settings generated by pgBackRest restore on 2026-01-02 18:52:37
restore_command = 'pgbackrest --stanza=regcost archive-get %f "%p"'
recovery_target_time = '2026-01-02 18:29:59.769442+08'
recovery_target_action = 'promote'

8.启动数据库
pg_ctl start

postgres@pg181:/pg18/data$ pg_ctl start
waiting for server to start....2026-01-02 18:59:44.985 CST [14799] LOG:  redirecting log output to logging collector process
2026-01-02 18:59:44.985 CST [14799] HINT:  Future log output will appear in directory "pg_log".
done
server started

查看日志

postgres@pg181:/pg18/data$ tail -20 /pg18/data/pg_log/postgresql.log
2026-01-02 18:59:45.070 CST [14806] LOG:  restored log file "00000001000000000000000B" from archive
2026-01-02 18:59:45.084 CST [14806] LOG:  starting point-in-time recovery to 2026-01-02 18:29:59.769442+08
2026-01-02 18:59:45.085 CST [14806] LOG:  redo starts at 0/B000028
2026-01-02 18:59:45.113 CST [14806] LOG:  restored log file "00000001000000000000000C" from archive
2026-01-02 18:59:45.128 CST [14806] LOG:  completed backup recovery with redo LSN 0/B000028 and end LSN 0/B000158
2026-01-02 18:59:45.128 CST [14806] LOG:  consistent recovery state reached at 0/B000158
2026-01-02 18:59:45.128 CST [14799] LOG:  database system is ready to accept read-only connections
2026-01-02 18:59:45.177 CST [14806] LOG:  restored log file "00000002000000000000000D" from archive
2026-01-02 18:59:45.221 CST [14806] LOG:  restored log file "00000002000000000000000E" from archive
2026-01-02 18:59:45.280 CST [14806] LOG:  restored log file "00000002000000000000000F" from archive
2026-01-02 18:59:45.309 CST [14806] LOG:  recovery stopping before commit of transaction 768, time 2026-01-02 18:32:02.347646+08
2026-01-02 18:59:45.309 CST [14806] LOG:  redo done at 0/D021600 system usage: CPU: user: 0.00 s, system: 0.07 s, elapsed: 0.22 s
2026-01-02 18:59:45.309 CST [14806] LOG:  last completed transaction was at log time 2026-01-02 18:29:06.511318+08
2026-01-02 18:59:45.378 CST [14806] LOG:  restored log file "00000002000000000000000D" from archive
2026-01-02 18:59:45.409 CST [14806] LOG:  selected new timeline ID: 3
2026-01-02 18:59:45.452 CST [14806] LOG:  restored log file "00000002.history" from archive
2026-01-02 18:59:45.453 CST [14806] LOG:  archive recovery complete
2026-01-02 18:59:45.454 CST [14804] LOG:  checkpoint starting: end-of-recovery immediate wait
2026-01-02 18:59:45.503 CST [14804] LOG:  checkpoint complete: wrote 985 buffers (6.0%), wrote 3 SLRU buffers; 0 WAL file(s) added, 0 removed, 2 recycled; write=0.044 s, sync=0.003 s, total=0.049 s; sync files=307, longest=0.001 s, average=0.001 s; distance=32901 kB, estimate=32901 kB; lsn=0/D021600, redo lsn=0/D021600
2026-01-02 18:59:45.505 CST [14799] LOG:  database system is ready to accept connections

查看数据

psql

postgres@pg181:/pg18/data$ psql
psql (18.1)
Type "help" for help.

postgres=# \c cost
You are now connected to database "cost" as user "postgres".
cost=# select * from important_table ;
message
----------------
Important Data
(1 row)

cost=#

数据已经恢复到删除之前了。

目的已经达到。

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