PostgreSQL备份与PITR恢复

MarsBighead

已于 2024-07-12 10:45:15 修改

阅读量301

点赞数 8

文章标签： postgresql 数据库 PITR 备份恢复时间点恢复

于 2024-07-12 10:45:02 首次发布

本文链接：https://blog.csdn.net/u012894975/article/details/140372819

版权

本文主要介绍了PostgreSQL物理备份结合日志备份数据，实现PITR恢复的操作与验证方式。

1、 pg_basebackupc创建基础物理备份到目录pgdata/pitr

hbu@Pauls-MacBook-Air ~ %  pg_basebackup -h 127.0.0.1 -R -D   pgdata/pitr  
hbu@Pauls-MacBook-Air ~ % 
hbu@Pauls-MacBook-Air ~ % pg_verifybackup pgdata/pitr                      
备份已成功验证
hbu@Pauls-MacBook-Air ~ % more pgdata/pitr/backup_label 
START WAL LOCATION: 0/22000028 (file 000000010000000000000022)
CHECKPOINT LOCATION: 0/22000060
BACKUP METHOD: streamed
BACKUP FROM: primary
START TIME: 2024-07-11 17:15:41 CST
LABEL: pg_basebackup base backup
START TIMELINE: 1

以压缩的方式备份

hbu@Pauls-MacBook-Air ~ % pg_basebackup -h 127.0.0.1 -R -D   pgdata/backup -Ft --compress=gzip:9

创建恢复文件夹,解压备份文件

hbu@Pauls-MacBook-Air pgdata % mkdir pitr
hbu@Pauls-MacBook-Air pgdata % cd  pitr 
hbu@Pauls-MacBook-Air pitr % tar zxf  ../backup/base.tar.gz 
hbu@Pauls-MacBook-Air pitr % ls
PG_VERSION           pg_dynshmem          pg_notify            pg_stat_tmp          pg_xact
backup_label         pg_hba.conf          pg_replslot          pg_subtrans          postgresql.auto.conf
base                 pg_ident.conf        pg_serial            pg_tblspc            postgresql.conf
global               pg_logical           pg_snapshots         pg_twophase          standby.signal
pg_commit_ts         pg_multixact         pg_stat              pg_wal               tablespace_map
hbu@Pauls-MacBook-Air pgdata % cd pg_wal 
hbu@Pauls-MacBook-Air pitr % tar zxf  ../../backup/pg_wal.tar.gz
hbu@Pauls-MacBook-Air pgdata % cd ../
hbu@Pauls-MacBook-Air pitr % cp  ../backup/backup_manifest ./
 
验证备份文件
hbu@Pauls-MacBook-Air pgdata % pg_verifybackup pitr 
备份已成功验证

2、启动增量备份

postgres=# SELECT pg_backup_start(label => 'pitr', fast => false);
 pg_backup_start 
-----------------
 0/11000028
(1 行记录)

3、修改表中数据

postgres=# select * from emp;
 employee_id | first_name | last_name | hire_date  |  salary  | manager_id | department_id 
-------------+------------+-----------+------------+----------+------------+---------------
         201 | Michael    | Hartstein | 2020-02-02 | 13000.00 |            |            20
         202 | Pat        | Fay       | 2024-07-05 |  6000.00 |        201 |            20
         200 | Jennifer   | Whalen    | 2020-01-01 |  6400.00 |            |            20
         204 | Toy        | Gates     | 2024-07-09 |  7100.00 |            |            10
(4 行记录)
 
postgres=# SELECT pg_current_wal_lsn();
 pg_current_wal_lsn 
--------------------
 0/2F000148
(1 行记录)
postgres=# update emp set hire_date='2022-04-02' where employee_id=204;
UPDATE 1
postgres=# SELECT pg_backup_start(label => 'pitr', fast => false);
 pg_backup_start 
-----------------
 0/30000028
(1 行记录)
 
postgres=# select * from emp;
 employee_id | first_name | last_name | hire_date  |  salary  | manager_id | department_id 
-------------+------------+-----------+------------+----------+------------+---------------
         201 | Michael    | Hartstein | 2020-02-02 | 13000.00 |            |            20
         202 | Pat        | Fay       | 2024-07-05 |  6000.00 |        201 |            20
         200 | Jennifer   | Whalen    | 2020-01-01 |  6400.00 |            |            20
         204 | Toy        | Gates     | 2022-04-02 |  7100.00 |            |            10

4、修改PostgreSQL恢复配置

配置postgresql.conf

# 修改端口
port=5435

postgresql.auto.conf中配置恢复目标点相关信息

restore_command = 'cp /Users/hbu/pgdata/archivedir/%f %p'
#recovery_target_xid = '716'
recovery_target_lsn = '0/2F000148'
recovery_target_time = '2024-07-12 09:12:14.1159+08'

5、创建恢复信号文件recovery.signal

hbu@Pauls-MacBook-Air pitr %touch recovery.signal

6、启动数据库

hbu@Pauls-MacBook-Air pgdata % pg_ctl start -D pitr         
等待服务器进程启动 ....2024-07-12 10:13:14.971 CST [26157] LOG:  starting PostgreSQL 15.7 (Homebrew) on aarch64-apple-darwin23.4.0, compiled by Apple clang version 15.0.0 (clang-1500.3.9.4), 64-bit
2024-07-12 10:13:14.973 CST [26157] LOG:  listening on IPv6 address "::1", port 5435
2024-07-12 10:13:14.973 CST [26157] LOG:  listening on IPv4 address "127.0.0.1", port 5435
2024-07-12 10:13:14.973 CST [26157] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5435"
2024-07-12 10:13:14.977 CST [26160] LOG:  database system was interrupted; last known up at 2024-07-11 20:24:58 CST
cp: /Users/hbu/pgdata/archivedir/00000002.history: No such file or directory
2024-07-12 10:13:15.018 CST [26160] LOG:  starting point-in-time recovery to 2024-07-12 09:12:14.1159+08
2024-07-12 10:13:15.019 CST [26160] LOG:  starting backup recovery with redo LSN 0/2B000028, checkpoint LSN 0/2B000060, on timeline ID 1
2024-07-12 10:13:15.036 CST [26160] LOG:  restored log file "00000001000000000000002B" from archive
2024-07-12 10:13:15.041 CST [26160] LOG:  redo starts at 0/2B000028
2024-07-12 10:13:15.042 CST [26160] LOG:  completed backup recovery with redo LSN 0/2B000028 and end LSN 0/2B000100
2024-07-12 10:13:15.042 CST [26160] LOG:  consistent recovery state reached at 0/2B000100
2024-07-12 10:13:15.042 CST [26157] LOG:  database system is ready to accept read-only connections
2024-07-12 10:13:15.062 CST [26160] LOG:  restored log file "00000001000000000000002C" from archive
2024-07-12 10:13:15.078 CST [26160] LOG:  restored log file "00000001000000000000002D" from archive
2024-07-12 10:13:15.100 CST [26160] LOG:  restored log file "00000001000000000000002E" from archive
2024-07-12 10:13:15.118 CST [26160] LOG:  restored log file "00000001000000000000002F" from archive
2024-07-12 10:13:15.119 CST [26160] LOG:  recovery stopping before commit of transaction 762, time 2024-07-12 09:43:44.6593+08
2024-07-12 10:13:15.119 CST [26160] LOG:  pausing at the end of recovery
2024-07-12 10:13:15.119 CST [26160] HINT:  Execute pg_wal_replay_resume() to promote.
 完成
服务器进程已经启动

7、检查数据

postgres=# select * from emp;
 employee_id | first_name | last_name | hire_date  |  salary  | manager_id | department_id 
-------------+------------+-----------+------------+----------+------------+---------------
         201 | Michael    | Hartstein | 2020-02-02 | 13000.00 |            |            20
         202 | Pat        | Fay       | 2024-07-05 |  6000.00 |        201 |            20
         200 | Jennifer   | Whalen    | 2020-01-01 |  6400.00 |            |            20
         204 | Toy        | Gates     | 2024-07-09 |  7100.00 |            |            10
(4 行记录)

数据已经恢复到指定时间点前的位置

8、启动恢复重制数据库，避免无法执行写操作

postgres=# select pg_wal_replay_resume();
 pg_wal_replay_resume 
----------------------
 
(1 行记录)

postgres=# update emp set salary=salary-400 where employee_id=204;
UPDATE 1
postgres=# select * from emp;
 employee_id | first_name | last_name | hire_date  |  salary  | manager_id | department_id 
-------------+------------+-----------+------------+----------+------------+---------------
         201 | Michael    | Hartstein | 2020-02-02 | 13000.00 |            |            20
         202 | Pat        | Fay       | 2024-07-05 |  6000.00 |        201 |            20
         200 | Jennifer   | Whalen    | 2020-01-01 |  6400.00 |            |            20
         204 | Toy        | Gates     | 2024-07-09 |  6700.00 |            |            10
(4 行记录)