Re: [HACKERS] Hot Standby utility and administrator functions

On Fri, 2008-10-24 at 09:54 +0200, Guillaume Lelarge wrote:
> 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.

Thanks for your comments.

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

You should probably be running
SHOW default_transaction_read_only;

I set that, but its possible to set it in postgresql.conf as well (nothing at all to do with Hot Standby)

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

No. They are separately configurable and don't pass that info.

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

Yes the intention was to allow that to be calculated. But there are
aspects to that calculation outside of the server's control, so I just
want to expose the single time and let you do the calculation.

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

Yes

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

OK. There are use cases, but those will become clearer when docs are
finished.

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

Yes, effectively allows failover to be initiated by a client.

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

I would ask: why not? Why is PITR not part of pgAdmin's capability?

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

Yes, thanks.

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

Not on that list, if you are could you pass it on.

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