pg 备份恢复（二）—— 利用pg_rman进行完整恢复及基于时间点的恢复

最新推荐文章于 2024-05-16 05:46:54 发布
最新推荐文章于 2024-05-16 05:46:54 发布
阅读量2.9k 收藏 3
点赞数 1
分类专栏： 备份还原 PostgreSQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Hehuyi_In/article/details/102644517
版权

利用上一篇提到的pg_rman来做备份恢复测试，相对而言它的命令更简单，在紧急恢复时更方便。

一、 完整恢复测试

1. 在users库创建一个测试表

create table test1017(a int);
insert into test1017 values(1);  
insert into test1017 values(2);
insert into test1017 values(3);
select * from test1017;
 a 
---
 1
 2
 3
(3 rows)

2. 全备users库

pg_rman backup  -b full -s -C -Z --keep-data-days=10 --keep-arclog-files=15 --keep-arclog-days=10 --keep-srvlog-files=10 --keep-srvlog-days=15 -h 127.0.0.1 -p 5432 -U postgres -d users

输出：
INFO: copying database files
INFO: copying archived WAL files
INFO: copying server log files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
INFO: start deleting old archived WAL files from ARCLOG_PATH (keep files = 15, keep days = 10)
INFO: the threshold timestamp calculated by keep days is "2019-10-07 00:00:00"
INFO: start deleting old server files from SRVLOG_PATH (keep files = 10, keep days = 15)
INFO: the threshold timestamp calculated by keep days is "2019-10-02 00:00:00"
INFO: start deleting old backup (keep after = 2019-10-07 00:00:00)
INFO: does not include the backup just taken
INFO: backup "2019-10-17 15:00:01" should be kept
DETAIL: This is taken after "2019-10-07 00:00:00".
INFO: backup "2019-10-17 14:59:44" should be kept
DETAIL: This is taken after "2019-10-07 00:00:00".

3. 备份验证

pg_rman validate;

输出：
INFO: validate: "2019-10-16 06:44:17" backup, archive log files and server log files by CRC
INFO: backup "2019-10-16 06:44:17" is valid

4. 再插入两条数据

insert into test1017 values(444);
insert into test1017 values(555);

users=# select * from test1017;
  a  
-----
   1
   2
   3
 444
 555
(5 rows)

5. 增备users库

pg_rman backup -b incremental -s -C -Z --keep-data-days=10 --keep-arclog-files=15 --keep-arclog-days=10 --keep-srvlog-files=10 --keep-srvlog-days=15 -h 127.0.0.1 -p 5432 -U postgres -d users 

输出：
INFO: copying database files
INFO: copying archived WAL files
INFO: copying server log files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
INFO: start deleting old archived WAL files from ARCLOG_PATH (keep files = 15, keep days = 10)
INFO: the threshold timestamp calculated by keep days is "2019-10-07 00:00:00"
INFO: start deleting old server files from SRVLOG_PATH (keep files = 10, keep days = 15)
INFO: the threshold timestamp calculated by keep days is "2019-10-02 00:00:00"
INFO: start deleting old backup (keep after = 2019-10-07 00:00:00)
INFO: does not include the backup just taken
INFO: backup "2019-10-17 15:15:25" should be kept
DETAIL: This is taken after "2019-10-07 00:00:00".
INFO: backup "2019-10-17 15:00:01" should be kept
DETAIL: This is taken after "2019-10-07 00:00:00".
INFO: backup "2019-10-17 14:59:44" should be kept
DETAIL: This is taken after "2019-10-07 00:00:00".

6. 备份验证

pg_rman validate;
INFO: validate: "2019-10-17 15:20:50" backup, archive log files and server log files by CRC
INFO: backup "2019-10-17 15:20:50" is valid

7. 检查备份信息

务必确认备份状态是ok的，万一是corrupt的后面恢复不了就悲剧了

-bash-4.2$ pg_rman show
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status 
=====================================================================
2019-10-17 15:20:50  2019-10-17 15:20:52  INCR    66kB     1  OK  <-- 测试的增备
2019-10-17 15:15:25  2019-10-17 15:15:28  FULL  4570kB     1  OK  <-- 测试的全备
2019-10-17 15:00:01  2019-10-17 15:00:02  ARCH    31kB     1  OK
2019-10-17 14:59:44  2019-10-17 14:59:47  FULL  6210kB     1  OK

查看备份文件内容

-bash-4.2$ cd /data/PRD/pgbackup/
-bash-4.2$ ls
20191017  backup  pg_rman.ini  pg_rman_log  system_identifier  test.file  timeline_history
-bash-4.2$ cd 20191017
-bash-4.2$ ls
145944  150001  151525  152050
-bash-4.2$ cd 151525   <--- 对应备份开始时间2019-10-17 15:15:25 
-bash-4.2$ ls
arclog  backup.ini  database  file_arclog.txt  file_database.txt  file_srvlog.txt  mkdirs.sh  srvlog
-bash-4.2$ cd database/
-bash-4.2$ ls
backup_label      pg_dynshmem    pg_multixact  pg_stat      PG_VERSION            postmaster.opts
base              pg_hba.conf    pg_notify     pg_stat_tmp  pg_wal                postmaster.pid
current_logfiles  pg_ident.conf  pg_replslot   pg_subtrans  pg_xact               repmgr
global            pg_log         pg_serial     pg_tblspc    postgresql.auto.conf
pg_commit_ts      pg_logical     pg_snapshots  pg_twophase  postgresql.conf

从输出信息及备份文件可以看出，这种备份方式也是copy文件

8. 完整恢复测试

模拟误删除os文件导致整个数据目录丢失，此时希望恢复到最近时间点（前提是增备之后的都归档没丢），故障前test1017有5条数据，恢复后也应该5条

关闭pg服务

pg_ctl stop -m fast

挪走$PGDATA目录模拟删除（不要真的删，万一真恢复不回来就要重装了...）

-bash-4.2$ pwd
/data/PRD/postgres/10.7/pg5432
-bash-4.2$ mv data data.bak
-bash-4.2$ ls
data.bak  pg_archlog  pgbackup

再起pg，肯定挂了

-bash-4.2$ postgres -D $PGDATA &
[1] 5259
-bash-4.2$ postgres: could not access directory "/data/PRD/postgres/10.7/pg5432/data": No such file or directory
Run initdb or pg_basebackup to initialize a PostgreSQL data directory.

[1]+  Exit 2                  postgres -D $PGDATA

开始恢复数据

mkdir data                 # postgres用户创建数据目录并授权
chmod 0700 data
cp -R pg_archlog pg_archlog_bak #备份下归档目录

使用pg_rman restore还原

注意pg_rman会自动生成recovery.conf文件，不需手动创建

-bash-4.2$ pg_rman restore        

输出：
WARNING: pg_controldata file "/data/PRD/postgres/10.7/pg5432/data/global/pg_control" does not exist
INFO: the recovery target timeline ID is not given
INFO: use timeline ID of latest full backup as recovery target: 1
INFO: calculating timeline branches to be used to recovery target point
INFO: searching latest full backup which can be used as restore start point
INFO: found the full backup can be used as base in recovery: "2019-10-17 15:15:25"
INFO: copying online WAL files and server log files
INFO: clearing restore destination
INFO: validate: "2019-10-17 15:15:25" backup, archive log files and server log files by SIZE
INFO: backup "2019-10-17 15:15:25" is valid
INFO: restoring database files from the full mode backup "2019-10-17 15:15:25"
INFO: searching incremental backup to be restored
INFO: validate: "2019-10-17 15:20:50" backup, archive log files and server log files by SIZE
INFO: backup "2019-10-17 15:20:50" is valid
INFO: restoring database files from the incremental mode backup "2019-10-17 15:20:50"
INFO: searching backup which contained archived WAL files to be restored
INFO: backup "2019-10-17 15:20:50" is valid
INFO: restoring WAL files from backup "2019-10-17 15:20:50"
INFO: restoring online WAL files and server log files
INFO: generating recovery.conf
INFO: restore complete
HINT: Recovery will start automatically when the PostgreSQL server is started.

看到是还原到了2019-10-17 15:20:50，当pg服务启动时会自动进行Recovery

看一下pg_rman自动生成的recovery.conf文件

-bash-4.2$ cat recovery.done
# recovery.conf generated by pg_rman 1.3.8
restore_command = 'cp /data/PRD/postgres/10.7/pg5432/pg_archlog/%f %p'
recovery_target_timeline = '1'

可以看到数据文件已经回来了

-bash-4.2$ cd data
-bash-4.2$ ls
backup_label      pg_dynshmem    pg_multixact  pg_stat      PG_VERSION            postmaster.opts
base              pg_hba.conf    pg_notify     pg_stat_tmp  pg_wal                recovery.conf
current_logfiles  pg_ident.conf  pg_replslot   pg_subtrans  pg_xact               repmgr
global            pg_log         pg_serial     pg_tblspc    postgresql.auto.conf
pg_commit_ts      pg_logical     pg_snapshots  pg_twophase  postgresql.conf

启动pg服务

-bash-4.2$ postgres -D $PGDATA &
[1] 5702
-bash-4.2$ 2019-10-17 15:32:23 CST::@:[5702]: LOG:  listening on IPv4 address "0.0.0.0", port 5432
2019-10-17 15:32:23 CST::@:[5702]: LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-10-17 15:32:23 CST::@:[5702]: LOG:  redirecting log output to logging collector process
2019-10-17 15:32:23 CST::@:[5702]: HINT:  Future log output will appear in directory "/data/PRD/postgres/10.7/pg5432/data/pg_log".

查看日志

-bash-4.2$ tail -100f postgresql-17.log 
2019-10-17 15:32:23 CST::@:[5704]: LOG:  database system was interrupted; last known up at 2019-10-17 15:20:50 CST
2019-10-17 15:32:23 CST::@:[5704]: LOG:  starting archive recovery  <-- 开始归档恢复
2019-10-17 15:32:23 CST::@:[5704]: LOG:  restored log file "000000010000000000000007" from archive
2019-10-17 15:32:24 CST::@:[5704]: LOG:  redo starts at 0/7000028
2019-10-17 15:32:24 CST::@:[5704]: LOG:  consistent recovery state reached at 0/70000F8
2019-10-17 15:32:24 CST::@:[5702]: LOG:  database system is ready to accept read only connections
2019-10-17 15:32:24 CST::@:[5704]: LOG:  restored log file "000000010000000000000008" from archive
cp: cannot stat ‘/data/PRD/postgres/10.7/pg5432/pg_archlog/000000010000000000000009’: No such file or directory
2019-10-17 15:32:24 CST::@:[5704]: LOG:  redo done at 0/8000088
2019-10-17 15:32:24 CST::@:[5704]: LOG:  last completed transaction was at log time 2019-10-17 15:20:52.167637+08
2019-10-17 15:32:24 CST::@:[5704]: LOG:  restored log file "000000010000000000000008" from archive
cp: cannot stat ‘/data/PRD/postgres/10.7/pg5432/pg_archlog/00000002.history’: No such file or directory
2019-10-17 15:32:25 CST::@:[5704]: LOG:  selected new timeline ID: 2  <-- 产生新的时间线
2019-10-17 15:32:25 CST::@:[5704]: LOG:  archive recovery complete  <-- 归档恢复完成
cp: cannot stat ‘/data/PRD/postgres/10.7/pg5432/pg_archlog/00000001.history’: No such file or directory
2019-10-17 15:32:25 CST::@:[5708]: LOG:  checkpoint starting: end-of-recovery immediate wait
2019-10-17 15:32:25 CST::@:[5708]: LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.000 s, sync=0.000 s, total=0.023 s; sync files=0, longest=0.000 s, average=0.000 s; distance=32768 kB, estimate=32768 kB
2019-10-17 15:32:25 CST::@:[5702]: LOG:  database system is ready to accept connections

检查测试表，5条数据，ok

users=# select * from test1017;
  a  
-----
   1
   2
   3
 444
 555
(5 rows)

二、 基于时间点的恢复——PITR

这是工作中更常用的一种恢复，一般用于误删除/更改表后恢复到误操作之前，找回数据。若要恢复到最后一次备份之后的时间点，也需要保证归档没丢。

1. 再做一次全备

./pg_rman_backup_db.sh

这里有一个巨坑

  • 如果之前有全备和归档，又没做过恢复，这个备份可以不做；
  • 如果之前做过恢复，那么此时DB与之前的备份已经不在同一时间线上。恢复默认只沿着基础备份建立时时间线恢复而不会切换到新的时间线，如果不做处理，恢复结果将和前面的完整恢复一模一样，恢复不出新插入的数据。

此时有两种解决方法：

  • 一是再做一个全备（就是我们选用的方法）
  • 二是recovery.conf文件设置recovery_target_timeline='latest'，这样便可恢复到该离当前最近的时间线
-bash-4.2$ pg_rman show
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status 
=====================================================================
2019-10-17 16:11:36  2019-10-17 16:11:39  FULL  4616kB     5  OK   <-----  注意此时时间线是5，因为之前还测过几次别的恢复
2019-10-17 16:04:24  2019-10-17 16:04:26  ARCH   207kB     4  OK
2019-10-17 15:20:50  2019-10-17 15:20:52  INCR    66kB     1  OK
2019-10-17 15:15:25  2019-10-17 15:15:28  FULL  4570kB     1  OK
2019-10-17 15:00:01  2019-10-17 15:00:02  ARCH    31kB     1  OK
2019-10-17 14:59:44  2019-10-17 14:59:47  FULL  6210kB     1  OK

2. 再插入一条数据

users=# insert into test1017 values(1017);
INSERT 0 1
users=# select now();
              now              
-------------------------------
 2019-10-17 16:12:57.859887+08
(1 row)

users=# select * from test1017;
  a   
------
    1
    2
    3
  444
  555
 1017
(6 rows)

保险起见做个Checkpoint再切下归档

users=# checkpoint;
CHECKPOINT
users=# 
users=# select pg_switch_wal();           
 pg_switch_wal 
---------------
 0/B000308
(1 row)

3. 误操作truncate了test1017表

users=# truncate table test1017;
TRUNCATE TABLE
users=# 
users=# select now();
              now              
-------------------------------
 2019-10-17 16:17:24.705675+08
(1 row)
users=# select * from test1017;
 a 
---
(0 rows)

根据最新数据插入时间（2019-10-17 16:12:57）及truncate时间（2019-10-17 16:17:24），恢复到2019-10-17 16:15:00应该能找回被truncate前数据。注意这个时间不是很准，最好再往前推几分钟，避免恢复后还是找不到被误操作的数据。

4. 关闭pg服务

pg_ctl stop -m fast

挪走$PGDATA目录

cd /data/PRD/postgres/10.7/pg5432
mv data data.bak

5. 还原到指定时间点

-bash-4.2$ pg_rman restore --recovery-target-time "2019-10-17 16:15:00"

输出：
WARNING: pg_controldata file "/data/PRD/postgres/10.7/pg5432/data/global/pg_control" does not exist
INFO: the recovery target timeline ID is not given
INFO: use timeline ID of latest full backup as recovery target: 5
INFO: calculating timeline branches to be used to recovery target point
INFO: searching latest full backup which can be used as restore start point
INFO: found the full backup can be used as base in recovery: "2019-10-17 16:11:36"
INFO: copying online WAL files and server log files
INFO: clearing restore destination
INFO: validate: "2019-10-17 16:11:36" backup, archive log files and server log files by SIZE
INFO: backup "2019-10-17 16:11:36" is valid
INFO: restoring database files from the full mode backup "2019-10-17 16:11:36"
INFO: searching incremental backup to be restored
INFO: searching backup which contained archived WAL files to be restored
INFO: backup "2019-10-17 16:11:36" is valid
INFO: restoring WAL files from backup "2019-10-17 16:11:36"
INFO: restoring online WAL files and server log files
INFO: generating recovery.conf
INFO: restore complete
HINT: Recovery will start automatically when the PostgreSQL server is started.

6. 启动pg服务

-bash-4.2$ postgres -D $PGDATA &
[1] 11868
-bash-4.2$ 2019-10-17 16:19:53 CST::@:[11868]: LOG:  listening on IPv4 address "0.0.0.0", port 5432
2019-10-17 16:19:53 CST::@:[11868]: LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-10-17 16:19:53 CST::@:[11868]: LOG:  redirecting log output to logging collector process
2019-10-17 16:19:53 CST::@:[11868]: HINT:  Future log output will appear in directory "/data/PRD/postgres/10.7/pg5432/data/pg_log".

查看日志

-bash-4.2$ tail -100f postgresql-17.log
2019-10-17 16:19:53 CST::@:[11870]: LOG:  database system was interrupted; last known up at 2019-10-17 16:11:37 CST
2019-10-17 16:19:54 CST::@:[11870]: LOG:  restored log file "00000005.history" from archive
2019-10-17 16:19:54 CST::@:[11870]: LOG:  starting point-in-time recovery to 2019-10-17 16:15:00+08
2019-10-17 16:19:54 CST::@:[11870]: LOG:  restored log file "00000005.history" from archive
2019-10-17 16:19:54 CST::@:[11870]: LOG:  restored log file "00000005000000000000000A" from archive
2019-10-17 16:19:54 CST::@:[11870]: LOG:  redo starts at 0/A000028
2019-10-17 16:19:54 CST::@:[11870]: LOG:  consistent recovery state reached at 0/A000130
2019-10-17 16:19:54 CST::@:[11868]: LOG:  database system is ready to accept read only connections
2019-10-17 16:19:54 CST::@:[11870]: LOG:  restored log file "00000005000000000000000B" from archive
2019-10-17 16:19:54 CST::@:[11870]: LOG:  restored log file "00000005000000000000000C" from archive
2019-10-17 16:19:54 CST::@:[11870]: LOG:  recovery stopping before commit of transaction 578, time 2019-10-17 16:17:18.160569+08
2019-10-17 16:19:54 CST::@:[11870]: LOG:  recovery has paused  <--  注意
2019-10-17 16:19:54 CST::@:[11870]: HINT:  Execute pg_wal_replay_resume() to continue.  <--  注意
2019-10-17 16:20:25 CST:[local]:[unknown]@[unknown]:[11911]: LOG:  connection received: host=[local]
2019-10-17 16:20:25 CST:[local]:postgres@users:[11911]: LOG:  connection authorized: user=postgres database=users

注意日志里以下信息
2019-10-17 16:19:54 CST::@:[11870]: LOG:  recovery has paused
2019-10-17 16:19:54 CST::@:[11870]: HINT:  Execute pg_wal_replay_resume() to continue.

这与recovery.conf文件的recovery_target_action参数设置有关（默认为pause），后面文章会再提到这个参数。

需要先执行pg_wal_replay_resume()，否则在数据库查询语句会被卡住

users=# select * from test1017;
^CCancel request sent
ERROR:  canceling statement due to user request

日志里会有阻塞信息

2019-10-17 16:20:25 CST:[local]:[unknown]@[unknown]:[11911]: LOG:  connection received: host=[local]
2019-10-17 16:20:25 CST:[local]:postgres@users:[11911]: LOG:  connection authorized: user=postgres database=users
2019-10-17 16:21:12 CST:[local]:postgres@users:[11911]: LOG:  process 11911 still waiting for AccessShareLock on relation 16416 of database 16385 after 1000.120 ms at character 15
2019-10-17 16:21:12 CST:[local]:postgres@users:[11911]: DETAIL:  Process holding the lock: 11870. Wait queue: 11911.
2019-10-17 16:21:12 CST:[local]:postgres@users:[11911]: STATEMENT:  select * from test1017;
2019-10-17 16:21:26 CST:[local]:[unknown]@[unknown]:[11977]: LOG:  connection received: host=[local]

7. 执行pg_wal_replay_resume()，再查询测试表

users=# select pg_wal_replay_resume();
 pg_wal_replay_resume 
----------------------
(1 row)

users=# select * from test1017;       
  a   
------
    1
    2
    3
  444
  555
 1017
(6 rows)

成功啦~

Hehuyi_In
关注
  • 1
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
pg_rman备份恢复
God_sign的博客
02-07 445
pg_rman备份恢复
PG备份恢复之pg_rman
Auspicious航的博客
08-27 623
数据库维护中备份和恢复重要性毋庸置疑，实现的方式也多种多样，各有千秋；参照ORACLE备份恢复工具， Postgresql也具备了类似的备份恢复能力。 现对以下几种备份工具的简单对比： 鉴于长期使用rman的原因，本文主要介绍pg_rman的部署及使用； 1. 安装部署 1）下载对应数据库版本及操作系统的安装包 https://github.com/ossc-db/pg_rman/releases 当前1.3版本是比较稳定的版本，从1.3.9开始支持PG12以上版本，目前最新支持PG1..
Mysql必知必会：物理备份与恢复测试
2301_76379671的博客
05-16 896
不论是通过停机冷备份，还是通过 NDB Cluster 自行提供的在线联机备份工具，或者是第三方备份软件来进行备份，都需要备份以上三种物理文件才能构成一个完整有效的备份集。当然，相关的配置文件，尤其是管理节点上面的配置信息，同样也需要备份。
linux postgresql 恢复数据库,postgresql 使用pg_rman恢复还原数据库
weixin_30388691的博客
05-08 341
1、备份准备工作--注意pg_rman需要一个备份目录[postgres@webtest bin]$ export BACKUP_PATH=/u01/backup注意，这个可以直接编辑在.bash_profile文件里面--创建并初始化目录[postgres@webtest bin]$ mkdir -p /u01/backup[postgres@webtest bin]$ ./pg_rman in...
pg_rman备份恢复测试
weixin_33890526的博客
03-06 208
环境描述1.OSCentOS Linux release 7.2.1511 (Core) X642.PostgreSQLPostgreSQL 9.6.13.pg_rmanpg_rman-1.3.3-pg96.tar.gz v1.3.3注意：请下载版本对应的源码包。https://github.com/ossc-db/pg_rman/releases/download/v1.3...
postgresql 使用pg_rman恢复还原数据库
coujiongyong0208的博客
08-24 457
1、备份准备工作 --注意pg_rman需要一个备份目录 [postgres@webtest bin]$ export BACKUP_PATH=/u01/backup 注意，这个可以直接编辑在.bash_p...
Postgresql使用pg_rman恢复以及备份集的删除
魂醉的一亩二分地
04-12 904
从之前的介绍我们了解到，pg_rman是物理备份，所以恢复也是物理恢复，需要从物理的数据库文件恢复，提供recovery.conf配置，可以指定恢复的时间点，或者是xlog文件的位置。 恢复实例： 异机恢复 A节点： 10.1.1.8 备份源节点 B节点： 10.1.1.9 需要恢复的新机器 A机器操作： pg_rman show detail -B /pgbackup/ 可以看到备份相关信息 这里复制备份目录到新节点 scp -r /pgbackup/ root@10.1.1.9:/ B节
一步一步学RMAN完整版Oracle备份恢复
06-15
一步一步学RMAN完整版Oracle备份恢复
顶级DBA漫谈Oracle Rman备份与恢复
09-01
以下是Oracle RMAN备份与恢复的相关知识点： 备份恢复概述 备份恢复是DBA管理员的基本技能之一，旨在保护数据库中的数据免受意外损坏或丢失。 Oracle RMAN提供了强大的备份和恢复功能，帮助DBA管理员快速恢复...
Oracle 11g R2 Rman备份与恢复_刘耀龙的博客-CSDN博客_rman备份.pdf
09-13
完全恢复恢复到故障前状态，而不完全恢复则可以恢复到某一特定时间点。 RMAN 支持三种存储类型：备份集（默认）、压缩备份集和镜像 copy。备份集由多个备份片组成，可以存储在控制文件或恢复目录中。RMAN 的优点...
Oracle19c rac备份数据通过rman恢复到单实例
最新发布
07-02
Oracle19c rac备份数据通过rman恢复到单实例
PostgreSQL 最佳实践 - pg_rman 数据库恢复示例 与 软件限制解说
weixin_34032779的博客
08-29 533
背景 pg_rman备份已经讲完了，接下来讲一下数据恢复。 由于pg_rman使用了物理备份，所以恢复时，与普通物理备份的恢复原理是一样的。 需要将数据文件恢复，同时需要提供recovery.conf，在recovery.conf中指定需要恢复到哪个位置，以及如何获取XLOG归档文件等配置。 数据库恢复 pg_rman数据恢复时的两个必要要素 1. 新...
postgresql 物理备份 pg_rman 之一 setup
一名数据库爱好者的专栏
06-12 964
os: centos 7.4 postgresql: 9.6.9 pg_rman: REL9_6_STABLE pg_rman 是一款优秀的postgresql 在线备份和恢复的工具，在github上可以找到该软件。 下面是pg_rman主页面的描述: pg_rman is an online backup and restore tool for PostgreSQL. The go...
pg_rman备份恢复data下面的数据全丢了
ct028111的专栏
09-19 186
-bash-4.1$ pg_rman backup --backup-mode=full INFO: database backup start NOTICE: pg_stop_backup complete, all r...
PostgreSQL启动恢复读取checkpoint时open wal日志
帅的数说
08-05 554
在上一篇博客https://blog.csdn.net/yanzongshuai/article/details/81415211中写到StartupXLOG最终会调用XLogFileReadAnyTLI打开归档日志或者WAL日志。 static int XLogFileReadAnyTLI(XLogSegNo segno, int emode, int source) { if (exp...
PostgreSQL检查点(checkpoint)详解
热门推荐
Focus on PostgreSQL
11-11 1万+
checkpoint简单点说就是一个数据库事件，用来保证数据一致性和完整性。 当我们在数据库中执行checkpoint时，就会将其之前的脏数据刷到磁盘，从而实现数据缩短数据库崩溃恢复时间的目的。 例如当我们更新任何数据的时候，对应于包含该数据的块将在shared buffers中更新。所做更改的记录保存在wal缓冲区中。提交后，我们所做的更改将被写入磁盘上的wal文件，从而使它们永久生效。如果实例立即崩溃，则将清除数据库共享缓冲区，但是在重新启动数据库时，PostgreSQL将把WAL文件中记录的更改应用于
说说pg中的检查点(checkpoint)之一
weixin_34110749的博客
05-02 933
最近一直在使用pgbench对pg进行压测,在压测的过程中,发现checkpoint的发生会对数据库的性能产生极大的影响. 想看到最近有没有发生checkpoint,有两种比较简单的方式: 一个是不停的刷新pg_stat_bgwriter这个视图,这个视图中两个字段checkpoints_timed和checkpoints_req直接反映了PG已经发生...
postgresql源码学习（29）—— 检查点① - 基础知识与结构体
Hehuyi_In的博客
07-10 2246
因此，我们需要这样一个时间点这个时间点，就是检查点。就是前面提到的那几点，后面我们会再具体看是怎么实现的检查点XLog记录的结构体，我们暂时只会用到第一个变量，因此这里只记第一个。 这个redo变量就是redopoint，所谓的故障恢复起点。 redopoint的特性是在它之前所有XLOG对应的数据都已经落盘。那么与其去找redopoint不如构建redopoint。怎么构建呢？ 当前日志写入的位置为Insert->CurrBytePos，那么只要在这个时候将数据页

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Hehuyi_In

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值