Skip site navigation (1) Skip section navigation (2)

Hot Standby utility and administrator functions

From: Simon Riggs <simon(at)2ndQuadrant(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, pgadmin-hackers(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org
Subject: Hot Standby utility and administrator functions
Date: 2008-10-20 09:25:29
Message-ID: 1224494729.3808.594.camel@ebony.2ndQuadrant (view raw, whole thread or download thread mbox)
Lists: pgadmin-hackerspgsql-generalpgsql-hackers
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

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

* 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 
 PostgreSQL Training, Services and Support


pgsql-hackers by date

Next:From: Heikki LinnakangasDate: 2008-10-20 09:28:45
Subject: Re: collation discussion notes
Previous:From: Markus WannerDate: 2008-10-20 09:23:07
Subject: Re: Block-level CRC checks

pgadmin-hackers by date

Next:From: Gevik BabakhaniDate: 2008-10-20 09:51:29
Subject: Re: extending functionality strategy
Previous:From: Dave PageDate: 2008-10-20 08:47:11
Subject: Re: extending functionality strategy

pgsql-general by date

Next:From: salman SheikhDate: 2008-10-20 09:34:28
Subject: Error in Adding All Table
Previous:From: Alvaro HerreraDate: 2008-10-19 19:17:38
Subject: Re: Problem removing Sequence in Postgresql 8.0.3

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group