暂无图片
pg_rman增量恢复出错了
我来答
分享
暂无图片 匿名用户
pg_rman增量恢复出错了

[postgres@test_dan ~]$ pg_rman show
=====================================================================
StartTime EndTime Mode Size TLI Status
=====================================================================
2022-03-03 09:52:54 2022-03-03 09:53:03 INCR 33MB 2 OK
2022-03-03 09:20:53 2022-03-03 09:24:18 FULL 2585MB 2 OK
[postgres@test_dan ~]$ pg_rman restore --backup-path=/backup/rman_backup --recovery-target-time='2022-03-03 09:53:03'
ERROR: PostgreSQL server is running
HINT: Please stop PostgreSQL server before executing restore.
[postgres@test_dan ~]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
[postgres@test_dan ~]$
[postgres@test_dan ~]$ pg_rman restore --backup-path=/backup/rman_backup --recovery-target-time='2022-03-03 09:53:03'
INFO: the recovery target timeline ID is not given
INFO: use timeline ID of current database cluster as recovery target: 2
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: "2022-03-03 09:20:53"
INFO: copying online WAL files and server log files
INFO: clearing restore destination
INFO: validate: "2022-03-03 09:20:53" backup and archive log files by SIZE
INFO: backup "2022-03-03 09:20:53" is valid
INFO: restoring database files from the full mode backup "2022-03-03 09:20:53"
INFO: searching incremental backup to be restored
INFO: validate: "2022-03-03 09:52:54" backup and archive log files by SIZE
INFO: backup "2022-03-03 09:52:54" is valid
INFO: restoring database files from the incremental mode backup "2022-03-03 09:52:54"
INFO: searching backup which contained archived WAL files to be restored
INFO: backup "2022-03-03 09:52:54" is valid
INFO: restoring WAL files from backup "2022-03-03 09:52:54"
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.


全备恢复没有问题,

增量恢复日志就出现问题了,恢复完后mydb1不能用


如下所示:


(postgres@[local]:5432)-[postgres]-#\l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
alvindb | alvin | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
bldemo | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
mydb | pguser | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =Tc/pguser +
| | | | | pguser=C*T*c*/pguser
mydb1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | admin | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
(8 rows)

(postgres@[local]:5432)-[postgres]-#\c mydb1
FATAL: database "mydb1" does not exist
DETAIL: The database subdirectory "base/32482" is missing.
Previous connection kept


请问如何排查这个问题?

我来答
添加附件
收藏
分享
问题补充
2条回答
默认
最新
杜京

你把备份恢复脚本贴出来吧 大家一起看看

暂无图片 评论
暂无图片 有用 0
打赏 0
nanjing_2013

第一次备份--全备

[postgres@test_dan rman_backup]$ pg_rman backup --backup-mode=full --backup-path=/backup/rman_backup
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
[postgres@test_dan rman_backup]$ pwd

[postgres@test_dan rman_backup]$ ll
总用量 5
drwx------ 3 postgres postgres 1024 3月 3 09:20 20220303 ---第一次全备产生的
drwx------ 4 postgres postgres 1024 3月 3 09:16 backup
-rw-rw-r-- 1 postgres postgres 73 3月 3 09:16 pg_rman.ini
-rw-rw-r-- 1 postgres postgres 40 3月 3 09:16 system_identifier
drwx------ 2 postgres postgres 1024 3月 3 09:24 timeline_history ----第一次全备修改此目录
[postgres@test_dan rman_backup]$

[postgres@test_dan rman_backup]$ pg_rman show --backup-path=/backup/rman_backup ---列出备份集
=====================================================================
StartTime EndTime Mode Size TLI Status
=====================================================================
2022-03-03 09:20:53 2022-03-03 09:24:18 FULL 2585MB 2 OK


第2次增量备份


pg_rman backup --backup-mode=incremental --backup-path=/backup/rman_backup
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.

[postgres@test_dan rman_backup]$ pg_rman validate --backup-path=/backup/rman_backup/
INFO: validate: "2022-03-03 09:52:54" backup and archive log files by CRC
INFO: backup "2022-03-03 09:52:54" is valid


[postgres@test_dan rman_backup]$ pg_rman show --backup-path=/backup/rman_backup/
=====================================================================
StartTime EndTime Mode Size TLI Status
=====================================================================
2022-03-03 09:52:54 2022-03-03 09:53:03 INCR 33MB 2 OK
2022-03-03 09:20:53 2022-03-03 09:24:18 FULL 2585MB 2 OK



[postgres@test_dan rman_backup]$ pg_rman restore --backup-path=/backup/rman_backup --recovery-target-time='2022-03-03 09:24:18'  ---使用全备恢复没问题

再使用增量恢复就报错了

pg_rman restore --backup-path=/backup/rman_backup --recovery-target-time='2022-03-03 09:53:03' --增量备份结束的时间

我是本机备份的,本机恢复的

暂无图片 评论
暂无图片 有用 0
打赏 0
回答交流
Markdown


请输入正文
提交
相关推荐
有什么方法知道每天postgreSQL 数据库里哪些表的数据更新了?
回答 2
每天对pgstatusertables做一下快照拉链表
聊聊pg中的页剪枝问题,oracle中有类似的机制吗?
回答 1
有,而且功能更完善
什么是联合查询?如何执行联合查询?
回答 1
联合查询是指对于多个查询所获得的结果集进行集合操作,包括union、unionall、intersect和minus等。在MySQL中,联合查询的基本语法为:SELECTcolumnname(s)FR
postgresql 用个虚拟ip做一主两备的负载均衡,怎么保障连进去是主库做写操作?
回答 1
请参考文章:<<PostgreSQL运行时角色连接及角色查询问题>>
postgreSQL 哪一个版本可以用procedure?
回答 1
PostgreSQL支持Procedure的版本是从11版本开始的。
PostgreSQL怎么引用一个表的数据类型, 直接创建一个新的数据类型?
回答 2
表本身也是一个数据类型,可以直接使用
postgresql 备机回放wal很慢, 这个和哪个参数可以控制?
回答 1
这个默认都很快不用参数调整控制。看看其他方面。CPU负荷,IO符合等等。
vacuum_cost_limit调大
回答 2
实际对于大表来说,一次vacuum可能需要一天时间太慢了,将autovacuumvacuumcostdelay0加快vacuum清理速度,autovacuumvacuumcostlimit不做调整
数据库技术论坛
回答 6
51CTO
postgresql: 想往json类型的字段里 ,加自增列的id ,怎么操作?
回答 1
这个思路很奇特。需求不太合理。