On Wed, Dec 5, 2012 at 8:40 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> The real question here probably needs to be "what is the point of
> recoveryPauseAtTarget in the first place?". I find it hard to envision
> what's the point of pausing unless the user has an opportunity to
> make a decision about whether to continue applying WAL.
Right now if I'm doing a PITR and want to look around before blessing
the restore, I have to:
0) restore from the base backup.
1) change pg_hba to lock out everyone else
2) do the restore to my best guess of what was just before the tragic DML.
3) Look around and see if I guessed correctly.
4a) If I replayed to far, blow everything away and start over.
4b) If I replayed not far enough, and opened the database normally
upon completion so I could look around, then blow everything away and
start over.
4c) If I replayed not far enough and opened the database in hot
standby, I don't have to blow everything away, I just have to shut
down the server, change the restore point forward, and restart it.
5) change pg_hba back to normal and restart the server.
It would be nice if I could execute 4c without a shutdown/restart, but
certainly a shutdown/restart is better than blowing everything away
and starting over, like you have to do once the restore picks a new
time line. So while the current pause behavior is incomplete, it is
still useful.
I would also be nice if only the superuser is allowed to connect to
the hot standby when pause_at_recovery_target=true, until after
pg_xlog_replay_resume() is called. That way I could skip steps 1 and
5--steps which are very easy to forget to do.
Cheers,
Jeff