Re: PITR potentially broken in 9.2

On Wed, Dec 5, 2012 at 8:40 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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