Thread: Hot Standby utility and administrator functions
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
> * 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 Should these return xid? ...Robert
On Mon, 2008-10-20 at 16:22 -0400, Robert Haas wrote: > > * 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 > > Should these return xid? Perhaps, but they match txid_current() which returns bigint. http://developer.postgresql.org/pgdocs/postgres/functions-info.html Thanks for checking. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support
Simon Riggs escribió: > > On Mon, 2008-10-20 at 16:22 -0400, Robert Haas wrote: > > > * 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 > > > > Should these return xid? > > Perhaps, but they match txid_current() which returns bigint. > http://developer.postgresql.org/pgdocs/postgres/functions-info.html That's been "extended with an epoch counter" per the docs; I don't think that's appropriate for the new functions, is it? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On Mon, 2008-10-20 at 17:44 -0300, Alvaro Herrera wrote: > Simon Riggs escribió: > > > > On Mon, 2008-10-20 at 16:22 -0400, Robert Haas wrote: > > > > * 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 > > > > > > Should these return xid? > > > > Perhaps, but they match txid_current() which returns bigint. > > http://developer.postgresql.org/pgdocs/postgres/functions-info.html > > That's been "extended with an epoch counter" per the docs; I don't think > that's appropriate for the new functions, is it? I assumed it was, so you can subtract them easily. It can be done either way, I guess. Happy to provide what people need. I just dreamed up a few that sounded useful. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support
On Mon, 2008-10-20 at 18:45 -0400, Tom Lane wrote: > Simon Riggs <simon@2ndQuadrant.com> writes: > > On Mon, 2008-10-20 at 17:44 -0300, Alvaro Herrera wrote: > >> That's been "extended with an epoch counter" per the docs; I don't think > >> that's appropriate for the new functions, is it? > > > I assumed it was, so you can subtract them easily. > > > It can be done either way, I guess. Happy to provide what people need. I > > just dreamed up a few that sounded useful. > > I don't think you should be inventing new functions without clear > use-cases in mind. Depending on what the use is, I could see either the > xid or the txid definition as being *required*. The use case for the two functions was clearly stated as "together allows easy arithmetic on xid difference between master and slave". In that context, xid plus epoch is appropriate. There are other use cases. We can have both, neither or just one, depending upon what people think. What would you want "xid only" for? Do you think that should replace the txid one? This is everybody's opportunity to say what we need. > In any case, do not use the wrong return type for the definition you're > implementing. err...Why would anyone do that? -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support
On Tue, 2008-10-21 at 09:44 +0200, Zeugswetter Andreas OSB sIT wrote: > > > * 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 > > > > Should these return xid? > > And shouldn't these two be folded together ? > It seems most usages of this xid(/lsn?) will be agnostic to the > recovery mode. Or if not, it seems more convenient to have a function > that returns both recovery mode and xid, no ? You are right that it would be better to have a single function. Functions that return multiple values are a pain to use and develop, plus we can always run the other function if we are in doubt. txid_last_completed() returns bigint (txid) seems better. I am more than happy to add an id version as well, if anybody sees the need for that. Just say. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support
On Mon, 2008-10-20 at 10:25 +0100, Simon Riggs wrote: > What else do we need? > * pg_freeze_recovery() > * pg_unfreeze_recovery() Two more functions pg_freeze_recovery_cleanup() pg_unfreeze_recovery_cleanup() These would allow recovery to continue normally, except for row removal operations which would halt the progress of recovery. It would eventually be possible to have a function that halts recovery whenever row removal takes place for a list of tables. Not planning on implementing that initially though. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support
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
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
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
Simon Riggs a écrit : > On Fri, 2008-10-24 at 09:54 +0200, Guillaume Lelarge wrote: >>[...] >> 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. > I definitely need to read your mails on pgsql-hackers. > [...] >>>> * 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? > Strictly speaking, pgAdmin is already able to configure PITR. Go on the configure tool, put your archive command, reload (pre8.3) or restart (8.3) the server. That's all it needs. But LogShipping is a different matter. For LogShipping to be part of pgAdmin's capabilities, we would need to be able to copy all PGDATA files from the master server to a slave server within pgAdmin. I'm not sure how we can handle this. Moreover, we would also need to launch the slave server. I mean, I would love to build a "PITR/LogShipping wizard": ask the slave IP, configure postgresql.conf on the master, recovery.conf on the slave, reload conf on the master, start the slave... but how do we copy files from one server to the other? how do we start the slave server? > [...] >> 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. > Done. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
On Monday 20 October 2008 05:25:29 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? > Is it possible to give the master/slave knowledge about each other? -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.net
On Mon, 2008-10-27 at 11:42 -0400, Robert Treat wrote: > On Monday 20 October 2008 05:25:29 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? > > > > Is it possible to give the master/slave knowledge about each other? Yes, but for what reason? The project I'm working on is Hot Standby, not streaming replication. That will link things together better than they are now, so I'd probably rather not prejudge/duplicate that. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support
Simon Riggs wrote: > On Mon, 2008-10-27 at 11:42 -0400, Robert Treat wrote: > > >> On Monday 20 October 2008 05:25:29 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? >>> >>> >> Is it possible to give the master/slave knowledge about each other? >> > > Yes, but for what reason? > > The project I'm working on is Hot Standby, not streaming replication. > That will link things together better than they are now, so I'd probably > rather not prejudge/duplicate that. > I think this could make sense in the case of a network partition (split brain). If we need to perform a reconciliation after a partition we will need to know when a slave has switched to master and what transactions have been processed on each side. Another use case is probably to implement failback once the former master has been restarted to just send the diff since failover happened. Actually to complement pg_last_recovered_xact_xid(), pg_last_completed_xact_xid() or pg_last_recovered_xact_timestamp(), I would like to have something like pg_xact_xid_status(txid) that would return something of a xid_status type that can be completed, recovered or not_found with a timestamp that would only be meaningful if the status is recovered or completed. Regards, Emmanuel -- Emmanuel Cecchet FTO @ Frog Thinker Open Source Development & Consulting -- Web: http://www.frogthinker.org email: manu@frogthinker.org Skype: emmanuel_cecchet
On Monday 27 October 2008 12:12:18 Simon Riggs wrote:
> On Mon, 2008-10-27 at 11:42 -0400, Robert Treat wrote:
> > On Monday 20 October 2008 05:25:29 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?
> >
> > Is it possible to give the master/slave knowledge about each other?
>
> Yes, but for what reason?
>
Was thinking that admin tools that show hot standby information might also
want to show the corresponding slave information (from the point of view of
the master). It might also allow tools to not have to be configured for all
servers... ie connect to one and lookup the other.
> The project I'm working on is Hot Standby, not streaming replication.
> That will link things together better than they are now, so I'd probably
> rather not prejudge/duplicate that.
It's possible this type of information isn't appropriate for our Hot Standby
implementation, but it is somewhat common in asynchronous and/or master/slave
systems.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
On Mon, 2008-10-27 at 13:08 -0400, Robert Treat wrote: > Was thinking that admin tools that show hot standby information might > also want to show the corresponding slave information (from the point > of view of the master). Well, the standby might be persuaded to know something about the master, but not the other way around. The master:standby relationship is 1:Many, and not restricted in the way things are daisy-chained. So it's more than just a pair of servers. All the slaves will say they are the same if you ask them, so you can't rely on that to identify them. So you need to specify which two servers you're interested in comparing and how to identify them externally. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support