Thread: Hot Standby utility and administrator functions

Hot Standby utility and administrator functions

From
Simon Riggs
Date:
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


Re: [HACKERS] Hot Standby utility and administrator functions

From
"Robert Haas"
Date:
> * 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

Re: [HACKERS] Hot Standby utility and administrator functions

From
Simon Riggs
Date:
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


Re: [HACKERS] Hot Standby utility and administrator functions

From
Alvaro Herrera
Date:
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.

Re: [HACKERS] Hot Standby utility and administrator functions

From
Simon Riggs
Date:
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


Re: [HACKERS] Hot Standby utility and administrator functions

From
Tom Lane
Date:
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*.

In any case, do not use the wrong return type for the definition you're
implementing.

            regards, tom lane

Re: [HACKERS] Hot Standby utility and administrator functions

From
Simon Riggs
Date:
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


Re: [HACKERS] Hot Standby utility and administrator functions

From
Zeugswetter Andreas OSB sIT
Date:
> > * 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 ?

Andreas

Re: [HACKERS] Hot Standby utility and administrator functions

From
Tom Lane
Date:
Simon Riggs <simon@2ndQuadrant.com> writes:
> On Mon, 2008-10-20 at 18:45 -0400, Tom Lane wrote:
>> In any case, do not use the wrong return type for the definition you're
>> implementing.

> err...Why would anyone do that?

That's what I wanted to know ;-).  If these functions are really going
to return txid, then they should be named to reflect that.

            regards, tom lane

Re: [HACKERS] Hot Standby utility and administrator functions

From
Simon Riggs
Date:
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


Re: [HACKERS] Hot Standby utility and administrator functions

From
Simon Riggs
Date:
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


Re: [HACKERS] Hot Standby utility and administrator functions

From
Robert Treat
Date:
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


Re: [HACKERS] Hot Standby utility and administrator functions

From
Simon Riggs
Date:
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


Re: [HACKERS] Hot Standby utility and administrator functions

From
Emmanuel Cecchet
Date:
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


Re: [HACKERS] Hot Standby utility and administrator functions

From
Robert Treat
Date:
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

Re: [HACKERS] Hot Standby utility and administrator functions

From
Simon Riggs
Date:
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