在http://www.postgresql.org/docs/9.1/static/continuous-archiving.html之后，我将 WAL 档案复制到另一台机器上并定期应用它们。最近由于我的主服务器上的 pg 服务器崩溃，我尝试通过推送触发器（一个空文件，期待智能故障转移）来恢复数据库，日志如下所示。

prodrestore_error.log

WAL file not present yet. Checking for trigger file...
trigger file found: smart failover
Trigger file:         /app/recovery-prod/trigger/pgsql.trigger.5432
Waiting for WAL file: 0000000100000001000000CC
WAL file path:        /backup/prod/db_backup/archive/0000000100000001000000CC
Restoring to:         pg_xlog/RECOVERYXLOG
Sleep interval:       60 seconds
Max wait interval:    0 forever
Command for restore:  cp "/backup/prod/db_backup/archive/0000000100000001000000CC" "pg_xlog/RECOVERYXLOG"
Keep archive history: 000000000000000000000000 and later
trigger file found: smart failover
running restore:      OK

Trigger file:         /app/recovery-prod/trigger/pgsql.trigger.5432
Waiting for WAL file: 00000002.history
WAL file path:        /backup/prod/db_backup/archive/00000002.history
Restoring to:         pg_xlog/RECOVERYHISTORY
Sleep interval:       60 seconds
Max wait interval:    0 forever
Command for restore:  cp "/backup/prod/db_backup/archive/00000002.history" "pg_xlog/RECOVERYHISTORY"
Keep archive history: 000000000000000000000000 and later
running restore:      cp: cannot stat `/backup/prod/db_backup/archive/00000002.history': No such file or directory
cp: cannot stat `/backup/prod/db_backup/archive/00000002.history': No such file or directory
cp: cannot stat `/backup/prod/db_backup/archive/00000002.history': No such file or directory
cp: cannot stat `/backup/prod/db_backup/archive/00000002.history': No such file or directory
not restored
history file not found
Trigger file:         /app/recovery-prod/trigger/pgsql.trigger.5432
Waiting for WAL file: 00000001.history
WAL file path:        /backup/prod/db_backup/archive/00000001.history
Restoring to:         pg_xlog/RECOVERYHISTORY
Sleep interval:       60 seconds
Max wait interval:    0 forever
Command for restore:  cp "/backup/prod/db_backup/archive/00000001.history" "pg_xlog/RECOVERYHISTORY"
Keep archive history: 000000000000000000000000 and later
running restore:      cp: cannot stat `/backup/prod/db_backup/archive/00000001.history': No such file or directory
cp: cannot stat `/backup/prod/db_backup/archive/00000001.history': No such file or directory
cp: cannot stat `/backup/prod/db_backup/archive/00000001.history': No such file or directory
cp: cannot stat `/backup/prod/db_backup/archive/00000001.history': No such file or directory
not restored
history file not found

postgresql-9.1-main.log

2015-08-03 08:08:44 IST::@:[5542]:LOG:  restored log file "0000000100000001000000CC" from archive
2015-08-03 08:11:44 IST::@:[5542]:LOG:  could not open file "pg_xlog/0000000100000001000000CD" (log file 1, segment 205): No such file or directory
2015-08-03 08:11:44 IST::@:[5542]:LOG:  redo done at 1/CC001E10
2015-08-03 08:11:44 IST::@:[5542]:LOG:  last completed transaction was at log time 2015-08-03 07:59:58.121908+05:30
2015-08-03 08:11:44 IST::@:[5542]:LOG:  restored log file "0000000100000001000000CC" from archive
2015-08-03 08:17:44 IST::@:[5542]:LOG:  selected new timeline ID: 2
2015-08-03 08:23:45 IST::@:[5542]:LOG:  archive recovery complete
2015-08-03 08:23:51 IST::@:[6088]:LOG:  autovacuum launcher started
2015-08-03 08:23:51 IST::@:[5541]:LOG:  database system is ready to accept connections

整个查找两个历史文件，4次，最后启动数据库的过程00000002.history大约00000001.history用了12分钟。然后，通过恢复新的基本备份进行的每次重复迭代都需要更多时间，因为它开始寻找更多 .history 文件。

如何使查找历史文件的速度尽可能快，如果不存在，则快速继续以读/写模式启动数据库？否则，根本不查找历史文件。

我在 Ubuntu LTS 10.04.02 服务器版本上使用 PostgreSQL 8.4。我使用 pg_standby 来恢复数据并为数据库恢复创建了一个 recovery.conf 并将其放在 /etc/postgresql/8.4/main 上，其中存在其余的 conf 文件并尝试使用启动数据库

sudo service postgresql start

期望服务能够识别我正在使用 recovery.conf 进行恢复。我发现这个文件被忽略了。然后，我必须将所有 *.conf 文件复制到我的数据目录（在我的情况下为 /dbdata/data），然后，我不得不从“postgres”用户启动数据库，而不是使用 init.d 中的脚本

/usr/lib/postgresql/8.4/bin/pg_ctl -D /dbdata/proddata  -l /var/log/postgresql/postgresql-8.4-main.log start

如何使我的恢复过程服从 /etc/init.d/postgresql start|stop|restart 或让 pg_ctl 命令查找 conf 文件 /etc/postgresql/8.4/main ？

注意：我是 PostgreSQL 和一般数据库管理的新手

我正在尝试使用 recovery.conf 文件来恢复数据库以进行时间点复制。

在 recovery.conf 文件中，我添加了一个恢复目标时间，如下所示。

recovery_target_time = '2011-06-16 04:10:00 IST'

但是在日志中，我可以看到它是否将时间提前了 3.5 小时到 07:40，并且尝试在这段时间内进行实际恢复，而不是我在 recovery_target_time 中给出的时间。

2011-06-16 12:53:45 IST LOG:  starting archive recovery
2011-06-16 12:53:45 IST LOG:  restore_command = '/app/postgresinstall/bin/pg_standby -l /dbdata/archive %f %p %r'
2011-06-16 12:53:45 IST LOG:  recovery_target_time = '2011-06-16 07:40:00+05:30'
cp: cannot stat `/dbdata/archive/00000001.history': No such file or directory

为什么会有 3.5 小时的差异，我需要帮助来弄清楚在决定恢复目标时间时发生了什么？

更新：这是来自数据库的时间详细信息

postgres=# SELECT EXTRACT(timezone_hour FROM now()),EXTRACT(timezone_minute FROM now());
 date_part | date_part 
-----------+-----------
         5 |        30
(1 row)

postgres=# select now();
               now                
----------------------------------
 2011-08-12 13:08:19.333068+05:30
(1 row)

OS Date/Time:
postgres@xxxxx:~$ date
Fri Aug 12 13:10:19 IST 2011

当我尝试从同一服务器和我的备份服务器上的 WAL 档案恢复时，我遇到了同样的问题。此外，我可以在两台服务器上始终如一地重现此问题。

因此，如果我需要创建接近当前时间的备份，我会使用比当前时间晚 3.5 小时的 recovery_target_time 并使用 recovery.conf 启动数据库