Re: [HACKERS] Hot Standby utility and administrator functions

Could I get some input on the control functions that might be needed for
Hot Standby please? Think adminpack-for-HotStandby. Thanks.

What operations in pgAdmin would fail if we connected using a (forced)
read only transaction? Will they be disabled, or will they just throw
errors?

------------------------------------------------------------------------

On Mon, 2008-10-20 at 10:25 +0100, Simon Riggs wrote:
> I'm looking to implement the following functions for Hot Standby, to
> allow those with administrative tools or management applications to have
> more control during recovery. Please let me know if other functions are
> required.
>
> What else do we need?
>
> * pg_is_in_recovery()
> returns bool (true if in recovery, false if not)
>
> * pg_last_recovered_xact_xid()
> Will throw an ERROR if *not* executed in recovery mode.
> returns bigint
>
> * pg_last_completed_xact_xid()
> Will throw an ERROR *if* executed in recovery mode.
> returns bigint
>
> (together allows easy arithmetic on xid difference between master and
> slave).
>
> * pg_last_recovered_xact_timestamp()
> returns timestamp with timezone
> (allows easy arithmetic with now() to allow derivation of replication
> delay etc)
>
> * pg_freeze_recovery() - freezes recovery after the current record has
> been applied. The server is still up and queries can happen, but no WAL
> replay will occur. This is a temporary state change and we keep no
> record of this, other than making a server log entry. If the server is
> shutdown or crashes, it will unfreeze itself automatically. Has no
> effect on master.
> Will throw an ERROR if not executed in recovery mode.
> Superusers only.
> returns text (XLogRecPtr of freeze point)
>
> * pg_unfreeze_recovery() - unfreezes recovery. Recovery will begin again
> at exactly the point recovery was frozen at.
> Will throw an ERROR is not executed in recovery mode.
> Superusers only.
> returns bool (true if unfroze, false if was not frozen when called)
>
> * pg_end_recovery() -
> Will force recovery to end at current location. Recovery mode cannot be
> easily re-entered, so there is no "restart" function.
> Will throw an ERROR is not executed in recovery mode.
> Superusers only.
> returns text (XLogRecPtr of freeze point)
>
> * pg_start_backup()/pg_stop_backup() could work during recovery, but the
> backup history file would need to be manually inserted into the archive
> once complete. Is that acceptable? (Note that we don't know where the
> archive is or how to access that; the information is all in
> recovery_command. We cannot assume that archive_command points to same
> archive. So making it happen automatically is too much work for this
> release, if ever.) If that seems useful, we could do this by avoiding
> any operation that changes WAL stream during recovery: no checkpoints,
> log switches etc..
> pg_start_backup() would return XLogRecPtr of last restartpoint.
> pg_stop_backup() would return last known xlrec recovered (we won't keep
> track of this record by record).
>
> * pg_reload_conf() will not force re-read of recovery.conf since that
> may require extra work and doesn't seem that important, if we have the
> manual override mentioned above.
>
> All desirable? All possible? Any others?

--
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support