Re: [HACKERS] Hot Standby utility and administrator functions

Please note I have some lags with reading pgsql-hackers mailing list,
you probably have already answered some of my questions.

This is just my opinion. Dave will have better answers for you.

Simon Riggs a écrit :
> 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?
>

I don't think pgAdmin would fail. AFAICS, pgAdmin doesn't write anything
by itself to do its main work. But we will need to discard pgAgent's UI
(to disallow adding jobs and things like that). Not sure about the Slony
stuff, but if someone uses Hot Standby, he won't need slony.

> ------------------------------------------------------------------------
>
> 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)
>>

+1

This will help us to know if we are connected to a master or to a slave,
and will allow us to disable pgAgent's UI.

>> * 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).
>>

Is there a way to get the IP of the salve (if we are connected to the
master? and vice-versa?

>> * pg_last_recovered_xact_timestamp()
>> returns timestamp with timezone
>> (allows easy arithmetic with now() to allow derivation of replication
>> delay etc)
>>

We could had another (read-only) property on a slave server : recovery lag.

We also could use it on the server status window. Would also be of
interest to stuff like check_postgres nagios script and munin plugins.

>> * 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)
>>

I don't quite see a usecase for this function. I see how we can use it,
from a UI pov. Why would someone want to freeze the replication? what
happens to the WAL during the freeze? What issues can come from this
state? (I'm thinking about "out of disk space")

>> * 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)
>>

Same questions here :)

>> * 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)
>>

Interesting for pgAdmin.

>> * 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).
>>

No interest from pgAdmin's pov.

>> * 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?
>

Hope this helps.

Oh, I almost forgot. I see you mailed a few projects (pgAdmin, pgpool,
pgbouncer). Perhaps you should ask phpPgAdmin's guys too?

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com