Postgresql基于时间点恢复PITR案例(二)-白红宇

Postgresql基于时间点恢复PITR案例(二)

阅读量：6833 次

发布时间：2019-06-26

本文共 11779 字，大约阅读时间需要 39 分钟。

一个朋友说参考了网址：http://www.mkyong.com/database/postgresql-point-in-time-recovery-incremental-backup/后做了一个基于时间点的数据库恢复操作，但是失败了。其过程大致如下：

测试环境：vmware 8

os ：centos 5.7 (final)

PG: version 9.1.2

####以下是他的测试过程记录

postgres=# create table testPITR1 as select * from pg_class, pg_description; SELECT 936936postgres=# select * from current_timestamp(0);timestamptz ------------------------2012-07-02 01:53:16-07(1 row)postgres=# select pg_start_backup('full_backup-testing_20120702');pg_start_backup -----------------0/60000020(1 row)postgres=# select pg_current_xlog_location();pg_current_xlog_location --------------------------0/600000B0(1 row)

--打包数据文件 tar pgdata.tar ./pgdata

postgres=# select pg_stop_backup();NOTICE: pg_stop_backup complete, all required WAL segments have been archivedpg_stop_backup ----------------0/60000168(1 row)postgres=# create table testPITR2 as select * from pg_class, pg_description;SELECT 946764postgres=# select * from current_timestamp(0);timestamptz ------------------------2012-07-02 02:05:20-07(1 row)postgres=# create table testPITR3 as select * from pg_class, pg_description; SELECT 956592postgres=# select * from current_timestamp(0);timestamptz ------------------------2012-07-02 02:14:33-07(1 row)postgres=# create table testPITR4 as select * from pg_class, pg_description; SELECT 966420postgres=# select * from current_timestamp(0);timestamptz ------------------------2012-07-02 02:35:31-07(1 row)postgres=# \dList of relationsSchema | Name | Type | Owner --------+-------------+-------+----------public | tesk | table | postgrespublic | test | table | postgrespublic | testpitr1 | table | postgrespublic | testpitr2 | table | postgrespublic | testpitr3 | table | postgrespublic | testpitr4 | table | postgres(7 rows)[postgres archive]$ pwd/home/postgres/archive[postgres archive]$ ls -lshtotal 1.1G64M -rw-------. 1 postgres postgres 64M Jul 2 01:41 00000002000000000000001364M -rw-------. 1 postgres postgres 64M Jul 2 01:52 00000002000000000000001464M -rw-------. 1 postgres postgres 64M Jul 2 01:52 00000002000000000000001564M -rw-------. 1 postgres postgres 64M Jul 2 01:52 00000002000000000000001664M -rw-------. 1 postgres postgres 64M Jul 2 01:56 00000002000000000000001764M -rw-------. 1 postgres postgres 64M Jul 2 02:04 0000000200000000000000184.0K -rw-------. 1 postgres postgres 295 Jul 2 02:04 000000020000000000000018.00000020.backup64M -rw-------. 1 postgres postgres 64M Jul 2 02:04 00000002000000000000001964M -rw-------. 1 postgres postgres 64M Jul 2 02:05 00000002000000000000001A64M -rw-------. 1 postgres postgres 64M Jul 2 02:05 00000002000000000000001B64M -rw-------. 1 postgres postgres 64M Jul 2 02:07 00000002000000000000001C64M -rw-------. 1 postgres postgres 64M Jul 2 02:07 00000002000000000000001D64M -rw-------. 1 postgres postgres 64M Jul 2 02:07 00000002000000000000001E64M -rw-------. 1 postgres postgres 64M Jul 2 02:07 00000002000000000000001F64M -rw-------. 1 postgres postgres 64M Jul 2 02:35 00000002000000000000002064M -rw-------. 1 postgres postgres 64M Jul 2 02:35 00000002000000000000002164M -rw-------. 1 postgres postgres 64M Jul 2 02:35 00000002000000000000002264M -rw-------. 1 postgres postgres 64M Jul 2 02:35 000000020000000000000023将原来的PGDATA通过move名字改为pgdata_bad[postgres pg_xlog]$ pwd/database/pgdata_bad/pg_xlog[postgres pg_xlog]$ ls -lshtotal 1.9G64M -rw-------. 1 postgres postgres 64M Jun 14 22:33 00000001000000000000000C64M -rw-------. 1 postgres postgres 64M Jun 14 22:33 00000001000000000000000D64M -rw-------. 1 postgres postgres 64M Jun 14 21:05 00000001000000000000000E64M -rw-------. 1 postgres postgres 64M Jun 14 22:05 00000001000000000000000F64M -rw-------. 1 postgres postgres 64M Jun 15 03:40 00000002000000000000000D64M -rw-------. 1 postgres postgres 64M Jun 15 03:46 00000002000000000000000E64M -rw-------. 1 postgres postgres 64M Jun 15 03:51 00000002000000000000000F64M -rw-------. 1 postgres postgres 64M Jun 15 04:23 00000002000000000000001064M -rw-------. 1 postgres postgres 64M Jun 25 02:41 00000002000000000000001164M -rw-------. 1 postgres postgres 64M Jun 30 01:24 00000002000000000000001265M -rw-------. 1 postgres postgres 64M Jul 2 01:41 00000002000000000000001364M -rw-------. 1 postgres postgres 64M Jul 2 01:52 00000002000000000000001464M -rw-------. 1 postgres postgres 64M Jul 2 01:52 00000002000000000000001564M -rw-------. 1 postgres postgres 64M Jul 2 01:52 00000002000000000000001664M -rw-------. 1 postgres postgres 64M Jul 2 01:56 00000002000000000000001764M -rw-------. 1 postgres postgres 64M Jul 2 02:04 0000000200000000000000184.0K -rw-------. 1 postgres postgres 295 Jul 2 02:04 000000020000000000000018.00000020.backup64M -rw-------. 1 postgres postgres 64M Jul 2 02:04 00000002000000000000001965M -rw-------. 1 postgres postgres 64M Jul 2 02:05 00000002000000000000001A64M -rw-------. 1 postgres postgres 64M Jul 2 02:05 00000002000000000000001B64M -rw-------. 1 postgres postgres 64M Jul 2 02:07 00000002000000000000001C64M -rw-------. 1 postgres postgres 64M Jul 2 02:07 00000002000000000000001D64M -rw-------. 1 postgres postgres 64M Jul 2 02:07 00000002000000000000001E64M -rw-------. 1 postgres postgres 64M Jul 2 02:07 00000002000000000000001F64M -rw-------. 1 postgres postgres 64M Jul 2 02:35 00000002000000000000002064M -rw-------. 1 postgres postgres 64M Jul 2 02:35 00000002000000000000002165M -rw-------. 1 postgres postgres 64M Jul 2 02:35 00000002000000000000002264M -rw-------. 1 postgres postgres 64M Jul 2 02:35 00000002000000000000002364M -rw-------. 1 postgres postgres 64M Jul 2 02:37 0000000200000000000000244.0K -rw-------. 1 postgres postgres 56 Jun 14 22:34 00000002.history4.0K drwx------. 2 postgres postgres 4.0K Jul 2 02:35 archive_status36M -rw-------. 1 postgres postgres 36M Jun 25 02:41 xlogtemp.204624M -rw-------. 1 postgres postgres 24M Jun 30 01:24 xlogtemp.2077

将之前打包备份的文件释放到pgdata位置，并重建pg_xlog文件，然后启动

# rm -rf pg_xlog

# mkdir -p pg_xlog/archive_status

这个时候启动是正常的，PSQL可以登录进去

postgres=# \dList of relationsSchema | Name | Type | Owner --------+-------------+-------+----------public | tesk | table | postgrespublic | test | table | postgrespublic | testpitr1 | table | postgres(4 rows)

因为没有做恢复，所以是正常的。

然后关闭数据库，设置recovery.conf文件

restore_command = 'cp /home/postgres/archive/%f %p'

recovery_target_time = '2012-07-02 02:10:31'

设置完了再启动就报错了，日志如下：

[root pg_log]# more postgresql-2012-07-03_014309.csv2012-07-03 01:43:09.701 PDT,,,7621,,4ff2b09d.1dc5,1,,2012-07-03 01:43:09 PDT,,0,LOG,00000,"database system was shut down at 2012-07-03 00:03:21 PDT",,,,,,,,,""2012-07-03 01:43:09.764 PDT,,,7621,,4ff2b09d.1dc5,2,,2012-07-03 01:43:09 PDT,,0,LOG,00000,"starting point-in-time recovery to 2012-07-02 02:10:31-07",,,,,,,,,""2012-07-03 01:43:14.177 PDT,,,7621,,4ff2b09d.1dc5,3,,2012-07-03 01:43:09 PDT,,0,LOG,00000,"restored log file ""000000020000000000000019"" from archive",,,,,,,,,""2012-07-03 01:43:14.177 PDT,,,7621,,4ff2b09d.1dc5,4,,2012-07-03 01:43:09 PDT,,0,LOG,00000,"invalid resource manager ID in primary checkpoint record",,,,,,,,,""2012-07-03 01:43:14.342 PDT,,,7621,,4ff2b09d.1dc5,5,,2012-07-03 01:43:09 PDT,,0,LOG,00000,"restored log file ""000000020000000000000018"" from archive",,,,,,,,,""2012-07-03 01:43:14.342 PDT,,,7621,,4ff2b09d.1dc5,6,,2012-07-03 01:43:09 PDT,,0,LOG,00000,"invalid xl_info in secondary checkpoint record",,,,,,,,,""2012-07-03 01:43:14.342 PDT,,,7621,,4ff2b09d.1dc5,7,,2012-07-03 01:43:09 PDT,,0,PANIC,XX000,"could not locate a valid checkpoint record",,,,,,,,,""2012-07-03 01:43:18.500 PDT,,,7619,,4ff2b09c.1dc3,1,,2012-07-03 01:43:08 PDT,,0,LOG,00000,"startup process (PID 7621) was terminated by signal 6: Aborted",,,,,,,,,""2012-07-03 01:43:18.500 PDT,,,7619,,4ff2b09c.1dc3,2,,2012-07-03 01:43:08 PDT,,0,LOG,00000,"aborting startup due to startup process failure",,,,,,,,,""[root pg_log]# more postgresql-2012-07-03_014309.logcp: cannot stat `/home/postgres/archive/00000002.history': No such file or directory[root archive]# more 00000002.history 1 00000001000000000000000D no recovery target specified

看了一下，/home/postgres/archive/00000002.history这个文件确实是没有，就从老的备份文件里面拷贝了一份过去，再启动，.log文件没有信息了，但是.csv文件报错如下：

[root pg_log]# more postgresql-2012-07-03_014413.csv2012-07-03 01:44:13.159 PDT,,,7647,,4ff2b0dd.1ddf,1,,2012-07-03 01:44:13 PDT,,0,LOG,00000,"database system was shut down at 2012-07-03 00:03:21 PDT",,,,,,,,,""2012-07-03 01:44:13.168 PDT,,,7647,,4ff2b0dd.1ddf,2,,2012-07-03 01:44:13 PDT,,0,LOG,00000,"restored log file ""00000002.history"" from archive",,,,,,,,,""2012-07-03 01:44:13.168 PDT,,,7647,,4ff2b0dd.1ddf,3,,2012-07-03 01:44:13 PDT,,0,LOG,00000,"starting point-in-time recovery to 2012-07-02 02:10:31-07",,,,,,,,,""2012-07-03 01:44:13.300 PDT,,,7647,,4ff2b0dd.1ddf,4,,2012-07-03 01:44:13 PDT,,0,LOG,00000,"restored log file ""000000020000000000000019"" from archive",,,,,,,,,""2012-07-03 01:44:13.300 PDT,,,7647,,4ff2b0dd.1ddf,5,,2012-07-03 01:44:13 PDT,,0,LOG,00000,"invalid resource manager ID in primary checkpoint record",,,,,,,,,""2012-07-03 01:44:13.407 PDT,,,7647,,4ff2b0dd.1ddf,6,,2012-07-03 01:44:13 PDT,,0,LOG,00000,"restored log file ""000000020000000000000018"" from archive",,,,,,,,,""2012-07-03 01:44:13.407 PDT,,,7647,,4ff2b0dd.1ddf,7,,2012-07-03 01:44:13 PDT,,0,LOG,00000,"invalid xl_info in secondary checkpoint record",,,,,,,,,""2012-07-03 01:44:13.407 PDT,,,7647,,4ff2b0dd.1ddf,8,,2012-07-03 01:44:13 PDT,,0,PANIC,XX000,"could not locate a valid checkpoint record",,,,,,,,,""2012-07-03 01:44:13.811 PDT,,,7645,,4ff2b0dc.1ddd,1,,2012-07-03 01:44:12 PDT,,0,LOG,00000,"startup process (PID 7647) was terminated by signal 6: Aborted",,,,,,,,,""2012-07-03 01:44:13.811 PDT,,,7645,,4ff2b0dc.1ddd,2,,2012-07-03 01:44:12 PDT,,0,LOG,00000,"aborting startup due to startup process failure",,,,,,,,,""

最终的PG_CONTROLDATA信息如下：

[postgres pgdata]$ pg_controldatapg_control version number: 903Catalog version number: 201105231Database system identifier: 5735970894348214195Database cluster state: shut downpg_control last modified: Tue 03 Jul 2012 12:03:21 AM PDTLatest checkpoint location: 0/64000020Prior checkpoint location: 0/60000140Latest checkpoint's REDO location: 0/64000020Latest checkpoint's TimeLineID: 2Latest checkpoint's NextXID: 0/1859Latest checkpoint's NextOID: 40985Latest checkpoint's NextMultiXactId: 1Latest checkpoint's NextMultiOffset: 0Latest checkpoint's oldestXID: 1792Latest checkpoint's oldestXID's DB: 1Latest checkpoint's oldestActiveXID: 0Time of latest checkpoint: Tue 03 Jul 2012 12:03:17 AM PDTMinimum recovery ending location: 0/0Backup start location: 0/0Current wal_level setting: hot_standbyCurrent max_connections setting: 100Current max_prepared_xacts setting: 0Current max_locks_per_xact setting: 64Maximum data alignment: 8Database block size: 8192Blocks per segment of large relation: 1048576WAL block size: 65536Bytes per WAL segment: 67108864Maximum length of identifiers: 64Maximum columns in an index: 32Maximum size of a TOAST chunk: 1996Date/time type storage: 64-bit integersFloat4 argument passing: by valueFloat8 argument passing: by value

########说明##########

在上述恢复的过程中，该DB被启动了两次，在第一次启动的时候是没有指定recovery.conf文件的，这里有一个前后的顺序问题，应该先配置recovery.conf，配置其恢复的时间点，然后启动DB。启动时已经有了一个check点了，这个时候再恢复到过去是不可能的，当然了，如果把recovery.conf中的recovery_target_time设置成比第一次启动晚的时间点也是可以的。我本机的检测过程如下：

[postgres pgdata]$ psqlpsql (9.1.2)Type "help" for help.postgres=# select pg_current_xlog_location(); pg_current_xlog_location -------------------------- 0/94000078(1 row)postgres=# \q[postgres pgdata]$ pg_stopwaiting for server to shut down................. doneserver stopped[postgres pgdata]$ pg_startserver starting[postgres pgdata]$ psqlpsql (9.1.2)Type "help" for help.postgres=# select pg_current_xlog_location(); pg_current_xlog_location -------------------------- 0/98000078(1 row)

转载于:https://my.oschina.net/Kenyon/blog/64830

你可能感兴趣的文章