一个朋友说参考了网址：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 936936
postgres=# 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 archived
pg_stop_backup
----------------
0/60000168
(1 row)
postgres=# create table testPITR2 as select * from pg_class, pg_description;
SELECT 946764
postgres=# 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 956592
postgres=# 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 966420
postgres=# select * from current_timestamp(0);
timestamptz
------------------------
2012-07-02 02:35:31-07
(1 row)
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------+-------+----------
public | tesk | table | postgres
public | test | table | postgres
public | testpitr1 | table | postgres
public | testpitr2 | table | postgres
public | testpitr3 | table | postgres
public | testpitr4 | table | postgres
(7 rows)
[postgres@localhost archive]$ pwd
/home/postgres/archive
[postgres@localhost archive]$ ls -lsh
total 1.1G
64M -rw-------. 1 postgres postgres 64M Jul 2 01:41 000000020000000000000013
64M -rw-------. 1 postgres postgres 64M Jul 2 01:52 000000020000000000000014
64M -rw-------. 1 postgres postgres 64M Jul 2 01:52 000000020000000000000015
64M -rw-------. 1 postgres postgres 64M Jul 2 01:52 000000020000000000000016
64M -rw-------. 1 postgres postgres 64M Jul 2 01:56 000000020000000000000017
64M -rw-------. 1 postgres postgres 64M Jul 2 02:04 000000020000000000000018
4.0K -rw-------. 1 postgres postgres 295 Jul 2 02:04 000000020000000000000018.00000020.backup
64M -rw-------. 1 postgres postgres 64M Jul 2 02:04 000000020000000000000019
64M -rw-------. 1 postgres postgres 64M Jul 2 02:05 00000002000000000000001A
64M -rw-------. 1 postgres postgres 64M Jul 2 02:05 00000002000000000000001B
64M -rw-------. 1 postgres postgres 64M Jul 2 02:07 00000002000000000000001C
64M -rw-------. 1 postgres postgres 64M Jul 2 02:07 00000002000000000000001D
64M -rw-------. 1 postgres postgres 64M Jul 2 02:07 00000002000000000000001E
64M -rw-------. 1 postgres postgres 64M Jul 2 02:07 00000002000000000000001F
64M -rw-------. 1 postgres postgres 64M Jul 2 02:35 000000020000000000000020
64M -rw-------. 1 postgres postgres 64M Jul 2 02:35 000000020000000000000021
64M -rw-------. 1 postgres postgres 64M Jul 2 02:35 000000020000000000000022
64M -rw-------. 1 postgres postgres 64M Jul 2 02:35 000000020000000000000023
将原来的PGDATA通过move名字改为pgdata_bad
[postgres@localhost pg_xlog]$ pwd
/database/pgdata_bad/pg_xlog
[postgres@localhost pg_xlog]$ ls -lsh
total 1.9G
64M -rw-------. 1 postgres postgres 64M Jun 14 22:33 00000001000000000000000C
64M -rw-------. 1 postgres postgres 64M Jun 14 22:33 00000001000000000000000D
64M -rw-------. 1 postgres postgres 64M Jun 14 21:05 00000001000000000000000E
64M -rw-------. 1 postgres postgres 64M Jun 14 22:05 00000001000000000000000F
64M -rw-------. 1 postgres postgres 64M Jun 15 03:40 00000002000000000000000D
64M -rw-------. 1 postgres postgres 64M Jun 15 03:46 00000002000000000000000E
64M -rw-------. 1 postgres postgres 64M Jun 15 03:51 00000002000000000000000F
64M -rw-------. 1 postgres postgres 64M Jun 15 04:23 000000020000000000000010
64M -rw-------. 1 postgres postgres 64M Jun 25 02:41 000000020000000000000011
64M -rw-------. 1 postgres postgres 64M Jun 30 01:24 000000020000000000000012
65M -rw-------. 1 postgres postgres 64M Jul 2 01:41 000000020000000000000013
64M -rw-------. 1 postgres postgres 64M Jul 2 01:52 000000020000000000000014
64M -rw-------. 1 postgres postgres 64M Jul 2 01:52 000000020000000000000015
64M -rw-------. 1 postgres postgres 64M Jul 2 01:52 000000020000000000000016
64M -rw-------. 1 postgres postgres 64M Jul 2 01:56 000000020000000000000017
64M -rw-------. 1 postgres postgres 64M Jul 2 02:04 000000020000000000000018
4.0K -rw-------. 1 postgres postgres 295 Jul 2 02:04 000000020000000000000018.00000020.backup
64M -rw-------. 1 postgres postgres 64M Jul 2 02:04 000000020000000000000019
65M -rw-------. 1 postgres postgres 64M Jul 2 02:05 00000002000000000000001A
64M -rw-------. 1 postgres postgres 64M Jul 2 02:05 00000002000000000000001B
64M -rw-------. 1 postgres postgres 64M Jul 2 02:07 00000002000000000000001C
64M -rw-------. 1 postgres postgres 64M Jul 2 02:07 00000002000000000000001D
64M -rw-------. 1 postgres postgres 64M Jul 2 02:07 00000002000000000000001E
64M -rw-------. 1 postgres postgres 64M Jul 2 02:07 00000002000000000000001F
64M -rw-------. 1 postgres postgres 64M Jul 2 02:35 000000020000000000000020
64M -rw-------. 1 postgres postgres 64M Jul 2 02:35 000000020000000000000021
65M -rw-------. 1 postgres postgres 64M Jul 2 02:35 000000020000000000000022
64M -rw-------. 1 postgres postgres 64M Jul 2 02:35 000000020000000000000023
64M -rw-------. 1 postgres postgres 64M Jul 2 02:37 000000020000000000000024
4.0K -rw-------. 1 postgres postgres 56 Jun 14 22:34 00000002.history
4.0K drwx------. 2 postgres postgres 4.0K Jul 2 02:35 archive_status
36M -rw-------. 1 postgres postgres 36M Jun 25 02:41 xlogtemp.2046
24M -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=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------+-------+----------
public | tesk | table | postgres
public | test | table | postgres
public | 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@localhost pg_log]# more postgresql-2012-07-03_014309.csv
2012-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@localhost pg_log]# more postgresql-2012-07-03_014309.log
cp: cannot stat `/home/postgres/archive/00000002.history': No such file or directory
[root@localhost archive]# more 00000002.history
1 00000001000000000000000D no recovery target specified
~~~
看了一下，/home/postgres/archive/00000002.history这个文件确实是没有，就从老的备份文件里面拷贝了一份过去，再启动，.log文件没有信息了，但是.csv文件报错如下：
~~~
[root@localhost pg_log]# more postgresql-2012-07-03_014413.csv
2012-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@localhost pgdata]$ pg_controldata
pg_control version number: 903
Catalog version number: 201105231
Database system identifier: 5735970894348214195
Database cluster state: shut down
pg_control last modified: Tue 03 Jul 2012 12:03:21 AM PDT
Latest checkpoint location: 0/64000020
Prior checkpoint location: 0/60000140
Latest checkpoint's REDO location: 0/64000020
Latest checkpoint's TimeLineID: 2
Latest checkpoint's NextXID: 0/1859
Latest checkpoint's NextOID: 40985
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 1792
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 0
Time of latest checkpoint: Tue 03 Jul 2012 12:03:17 AM PDT
Minimum recovery ending location: 0/0
Backup start location: 0/0
Current wal_level setting: hot_standby
Current max_connections setting: 100
Current max_prepared_xacts setting: 0
Current max_locks_per_xact setting: 64
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 1048576
WAL block size: 65536
Bytes per WAL segment: 67108864
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by value
~~~
########说明##########
在上述恢复的过程中，该DB被启动了两次，在第一次启动的时候是没有指定recovery.conf文件的，这里有一个前后的顺序问题，应该先配置recovery.conf，配置其恢复的时间点，然后启动DB。启动时已经有了一个check点了，这个时候再恢复到过去是不可能的，当然了，如果把recovery.conf中的recovery_target_time设置成比第一次启动晚的时间点也是可以的。 我本机的检测过程如下：
~~~
[postgres@localhost pgdata]$ psql
psql (9.1.2)
Type "help" for help.
postgres=# select pg_current_xlog_location();
pg_current_xlog_location
--------------------------
0/94000078
(1 row)
postgres=# \q
[postgres@localhost pgdata]$ pg_stop
waiting for server to shut down................. done
server stopped
[postgres@localhost pgdata]$ pg_start
server starting
[postgres@localhost pgdata]$ psql
psql (9.1.2)
Type "help" for help.
postgres=# select pg_current_xlog_location();
pg_current_xlog_location
--------------------------
0/98000078
(1 row)
~~~
- 数据表
- 模式Schema
- 表的继承和分区
- 常用数据类型
- 函数和操作符-一
- 函数和操作符-二
- 函数和操作符-三
- 索引
- 事物隔离
- 性能提升技巧
- 服务器配置
- 角色和权限
- 数据库管理
- 数据库维护
- 系统表
- 系统视图
- SQL语言函数
- PL-pgSQL过程语言
- PostgreSQL 序列（SEQUENCE）
- PostgreSQL的时间-日期函数使用
- PostgreSQL 查看数据库，索引，表，表空间大小
- 用以查询某表的详细 包含表字段的注释信息
- PostgreSQL 系统表查看系统信息
- postgre存储过程简单实用方法
- PostgreSQL实用日常维护SQL
- PostgreSQL的时间函数使用整理
- 命令
- pg_ctl控制服务器
- initdb 初始化数据库簇
- createdb创建数据库
- dropdb 删除数据库
- createuser创建用户
- dropuser 删除用户
- psql交互式工具
- psql命令手册
- pg_dump 数据库转储
- pg_restore恢复数据库
- vacuumdb 清理优化数据库
- reindexdb 数据库重创索引
- createlang 安装过程语言
- droplang 删除过程语言
- pg_upgrade 升级数据库簇
- 调试存储过程
- 客户端命令-一
- 客户端命令-二
- 使用技巧
- PostgreSQL删除重复数据
- postgresql 小技巧
- PostgreSQL的10进制与16进制互转
- PostgreSQL的汉字转拼音
- Postgres重复数据的更新一例
- PostgreSQL使用with一例
- PostgreSQL在函数内返回returning
- PostgreSQL中的group_concat使用
- PostgreSQL数据库切割和组合字段函数
- postgresql重复数据的删除
- PostgreSQL的递归查询(with recursive)
- PostgreSQL函数如何返回数据集
- PostgreSQL分区表（Table Partitioning）应用 - David_Tang - 博客园
- PostgreSQL: function 返回结果集多列和单列的例子
- 利用pgAgent创建定时任务
- 浅谈 PostgreSQL 类型转换类似Oracle
- postgresql在windows(包括win7)下的安装配置
- PostgreSQL简介、安装、用户管理、启动关闭、创建删除数据库 (2010-11-08 12-52-51)转载▼标签： 杂谈分类： PostgreSQL
- PostgreSQL的generate_series函数应用
- PostgreSQL 8.3.1 全文检索(Full Text Search)
- postgresql record 使用
- 备份恢复
- PostgreSQL基于时间点恢复(PITR)
- Postgresql基于时间点恢复PITR案例(二)
- Postgres逻辑备份脚本
- Postgres invalid command \N数据恢复处理