(一)PostgreSQL:备份与PITR恢复

MarsBighead

已于 2025-01-23 17:35:05 修改

阅读量508

点赞数 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

最低0.47元/天解锁文章