[Pgpool-general] [Fwd: [Fwd: Re: [HACKERS] Hot Standby utility and administrator functions]]

[Simon Riggs]

Can I ask for input from pgpool people for Hot Standby?

What operations will work/not work if we have a forced read-only session?

Will we need to control pgpool differently? Do we need anything
in-core to make that happen, or work better?

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

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