recovery.conf location

On Mon, Sep 27, 2010 at 6:56 AM, Thom Brown <thom@linux.com> wrote:

I noticed that there's no way to specify the location of recovery.conf
in postgresql.conf.  The pg_hba and pg_ident files can be altered, so
I'm wondering why this file can't have a specified location.  In
Ubuntu, all configuration files are in a different location to the
cluster by default, so the postgresql.conf file comes with the
hba_file and ident_file parameters set to the configuration directory.
 I'd imagine it would be preferable to keep all conf files in the same
place.

+1

That parameter would be useful when user makes multiple standbys see
the same recovery.conf located in NFS or elsewhere.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

On Sun, Sep 26, 2010 at 6:29 PM, Fujii Masao <masao.fujii@gmail.com> wrote:

On Mon, Sep 27, 2010 at 6:56 AM, Thom Brown <thom@linux.com> wrote:

I noticed that there's no way to specify the location of recovery.conf
in postgresql.conf.  The pg_hba and pg_ident files can be altered, so
I'm wondering why this file can't have a specified location.  In
Ubuntu, all configuration files are in a different location to the
cluster by default, so the postgresql.conf file comes with the
hba_file and ident_file parameters set to the configuration directory.
 I'd imagine it would be preferable to keep all conf files in the same
place.

+1

That parameter would be useful when user makes multiple standbys see
the same recovery.conf located in NFS or elsewhere.

Maybe i'm missing something but this would be a problem if we put a
trigger file and the recovery.conf gets renamed to recovery.done, no?
at least that would be a problem for the standbys that still need to
be standbys

--
Jaime Casanova         www.2ndQuadrant.com
Soporte y capacitación de PostgreSQL

Jaime Casanova <jaime@2ndquadrant.com> writes:

On Sun, Sep 26, 2010 at 6:29 PM, Fujii Masao <masao.fujii@gmail.com> wrote:

On Mon, Sep 27, 2010 at 6:56 AM, Thom Brown <thom@linux.com> wrote:

I noticed that there's no way to specify the location of recovery.conf
in postgresql.conf.

+1

That parameter would be useful when user makes multiple standbys see
the same recovery.conf located in NFS or elsewhere.

Maybe i'm missing something but this would be a problem if we put a
trigger file and the recovery.conf gets renamed to recovery.done, no?

Yeah. The original design for recovery.conf envisioned that it has only
a short lifespan while you're doing an archive recovery. Putting
parameters for slave operation into it has contorted things beyond
recognition. I think we really need to take two steps back and
reconsider the whole "parameters" versus "status" distinction there.

This is pretty tightly tied to the ongoing argument about where to keep
parameters for synchronous replication, too.

regards, tom lane

On Sun, Sep 26, 2010 at 8:56 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Jaime Casanova <jaime@2ndquadrant.com> writes:

On Sun, Sep 26, 2010 at 6:29 PM, Fujii Masao <masao.fujii@gmail.com> wrote:

On Mon, Sep 27, 2010 at 6:56 AM, Thom Brown <thom@linux.com> wrote:

I noticed that there's no way to specify the location of recovery.conf
in postgresql.conf.

+1

That parameter would be useful when user makes multiple standbys see
the same recovery.conf located in NFS or elsewhere.

Maybe i'm missing something but this would be a problem if we put a
trigger file and the recovery.conf gets renamed to recovery.done, no?

Yeah.  The original design for recovery.conf envisioned that it has only
a short lifespan while you're doing an archive recovery.  Putting
parameters for slave operation into it has contorted things beyond
recognition.  I think we really need to take two steps back and
reconsider the whole "parameters" versus "status" distinction there.

Perhaps we should consider folding recovery.conf into postgresql.conf.

This is pretty tightly tied to the ongoing argument about where to keep
parameters for synchronous replication, too.

The thing about the parameters for synchronous replication that is a
little different is that you need a whole set of parameters *for each
standby*. There's not a terribly clean way to handle that in
postgresql.conf as it exists today, but getting any agreement on
non-trivial changes to postgresql.conf has proven to be next to
impossible, despite the fact that AFAICT approximately no one is happy
with the status quo.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

Robert Haas <robertmhaas@gmail.com> writes:

On Sun, Sep 26, 2010 at 8:56 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Yeah. �The original design for recovery.conf envisioned that it has only
a short lifespan while you're doing an archive recovery. �Putting
parameters for slave operation into it has contorted things beyond
recognition. �I think we really need to take two steps back and
reconsider the whole "parameters" versus "status" distinction there.

Perhaps we should consider folding recovery.conf into postgresql.conf.

To the extent that it carries long-lived parameters, that would be
sensible. I think there's also a status component to what it's doing
though. Also, if we're trying to put SR parameters somewhere other than
postgresql.conf, it might be better if the existing parameters migrated
there instead.

regards, tom lane

On Sun, Sep 26, 2010 at 9:12 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

On Sun, Sep 26, 2010 at 8:56 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Yeah.  The original design for recovery.conf envisioned that it has only
a short lifespan while you're doing an archive recovery.  Putting
parameters for slave operation into it has contorted things beyond
recognition.  I think we really need to take two steps back and
reconsider the whole "parameters" versus "status" distinction there.

Perhaps we should consider folding recovery.conf into postgresql.conf.

To the extent that it carries long-lived parameters, that would be
sensible.  I think there's also a status component to what it's doing
though.  Also, if we're trying to put SR parameters somewhere other than
postgresql.conf, it might be better if the existing parameters migrated
there instead.

Again, I think the real question is how to handle values that need to
be maintained PER SLAVE from values of which there is only one copy.
IMHO, whether or not it's related to HS/SR is not particularly
interesting, or particularly well-defined.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

On Mon, Sep 27, 2010 at 9:35 AM, Jaime Casanova <jaime@2ndquadrant.com> wrote:

Maybe i'm missing something but this would be a problem if we put a
trigger file and the recovery.conf gets renamed to recovery.done, no?
at least that would be a problem for the standbys that still need to
be standbys

That's not problem unless more than one standbys become master at the
same time. Since recovery.conf is read by standby only at the start unless
it's triggered, already-started standbys can work even if recovery.conf is
renamed to recovery.done. Though it's somewhat tricky..

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

On Mon, Sep 27, 2010 at 10:55 AM, Robert Haas <robertmhaas@gmail.com> wrote:

Again, I think the real question is how to handle values that need to
be maintained PER SLAVE from values of which there is only one copy.

Yep. One idea is to support something like "pg_ctl standby" and "pg_ctl pitr".
If we do so, we would be able to get rid of standby_mode from recovery.conf,
and move the others to postgresql.conf or elsewhere.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

On 09/27/2010 10:08 AM, Robert Haas wrote:

The thing about the parameters for synchronous replication that is a
little different is that you need a whole set of parameters *for each
standby*. There's not a terribly clean way to handle that in
postgresql.conf as it exists today, but getting any agreement on
non-trivial changes to postgresql.conf has proven to be next to
impossible, despite the fact that AFAICT approximately no one is happy
with the status quo.

Huh? Since when? I thought the whole "standby registration on the
master" discussion was still ongoing. And if we don't have a clean
format to do that in postgresql.conf, we don't have one anywhere else,
either.

As far as each standby is concerned, there's absolutely no reason not to
have all the standby parameters in postgresql.conf. The one and only
thing we'd need to deal with is how the standby writes a bit to indicate
that it has failed over and is no longer a standby ... probably the
simplest idea is to simply leave the trigger file in place.

I am opposed to any solution to configuring sync rep which involves
PostgreSQL having multiple configuration files in multiple different
formats. All of our configuration files should be in the exact same
format, and ideally there should only be one configuration file. There
is no DBA or sysadmin on Earth who would appreciate having to edit one
file in param=val format, and a second file in JSON or XML.

A good compromise with historical formats is to use Apache HTTPD's
approach, which supports both simple param=val declarations, and also
simple blocks. Assuming we need to configure the standbys on the master
at all, which has not yet been decided on this list AFAIK.

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

On Mon, Sep 27, 2010 at 08:52, Fujii Masao <masao.fujii@gmail.com> wrote:

On Mon, Sep 27, 2010 at 10:55 AM, Robert Haas <robertmhaas@gmail.com> wrote:

Again, I think the real question is how to handle values that need to
be maintained PER SLAVE from values of which there is only one copy.

Yep. One idea is to support something like "pg_ctl standby" and "pg_ctl pitr".
If we do so, we would be able to get rid of standby_mode from recovery.conf,
and move the others to postgresql.conf or elsewhere.

That wouldn't really help, would it? You'd just push the problem out
to the initscript that would have to keep track of which mode to go up
in, etc. And then have to put the logic right back in pg_ctl for
Windows where there is no initscript...

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On Mon, Sep 27, 2010 at 08:34, Fujii Masao <masao.fujii@gmail.com> wrote:

On Mon, Sep 27, 2010 at 9:35 AM, Jaime Casanova <jaime@2ndquadrant.com> wrote:

Maybe i'm missing something but this would be a problem if we put a
trigger file and the recovery.conf gets renamed to recovery.done, no?
at least that would be a problem for the standbys that still need to
be standbys

That's not problem unless more than one standbys become master at the
same time. Since recovery.conf is read by standby only at the start unless
it's triggered, already-started standbys can work even if recovery.conf is
renamed to recovery.done. Though it's somewhat tricky..

Uh, what if the slave is restarted for one reason or another? That
seems like it would be really fragile..

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On Mon, Sep 27, 2010 at 5:02 PM, Magnus Hagander <magnus@hagander.net> wrote:

On Mon, Sep 27, 2010 at 08:34, Fujii Masao <masao.fujii@gmail.com> wrote:

On Mon, Sep 27, 2010 at 9:35 AM, Jaime Casanova <jaime@2ndquadrant.com> wrote:

Maybe i'm missing something but this would be a problem if we put a
trigger file and the recovery.conf gets renamed to recovery.done, no?
at least that would be a problem for the standbys that still need to
be standbys

That's not problem unless more than one standbys become master at the
same time. Since recovery.conf is read by standby only at the start unless
it's triggered, already-started standbys can work even if recovery.conf is
renamed to recovery.done. Though it's somewhat tricky..

Uh, what if the slave is restarted for one reason or another? That
seems like it would be really fragile..

Agreed ;)

So, even if we move primary_conninfo and trigger_file to postgresql.conf,
we would need to still leave standby_mode in recovery.conf.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

On Tue, Sep 28, 2010 at 12:46 AM, Fujii Masao <masao.fujii@gmail.com> wrote:

On Mon, Sep 27, 2010 at 5:02 PM, Magnus Hagander <magnus@hagander.net> wrote:

On Mon, Sep 27, 2010 at 08:34, Fujii Masao <masao.fujii@gmail.com> wrote:

On Mon, Sep 27, 2010 at 9:35 AM, Jaime Casanova <jaime@2ndquadrant.com> wrote:

Maybe i'm missing something but this would be a problem if we put a
trigger file and the recovery.conf gets renamed to recovery.done, no?
at least that would be a problem for the standbys that still need to
be standbys

That's not problem unless more than one standbys become master at the
same time. Since recovery.conf is read by standby only at the start unless
it's triggered, already-started standbys can work even if recovery.conf is
renamed to recovery.done. Though it's somewhat tricky..

Uh, what if the slave is restarted for one reason or another? That
seems like it would be really fragile..

Agreed ;)

So, even if we move primary_conninfo and trigger_file to postgresql.conf,
we would need to still leave standby_mode in recovery.conf.

The idea of relying on the existence of recovery.conf to determine
whether we should continue recovery forever or switch to normal
running seems somewhat klunky to me. It mixes up settings with
control information. Maybe the control information should move to
pg_control, and the settings to postgresql.conf. *waves hands*

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

On Tue, Sep 28, 2010 at 10:34 PM, Robert Haas <robertmhaas@gmail.com> wrote:

The idea of relying on the existence of recovery.conf to determine
whether we should continue recovery forever or switch to normal
running seems somewhat klunky to me.  It mixes up settings with
control information.  Maybe the control information should move to
pg_control, and the settings to postgresql.conf.  *waves hands*

You mean to move standby_mode to postgresql.conf, and determine
whether the server should start in standby mode or not by considering
of standby_mode and the status information in pg_control?

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

On Wed, Sep 29, 2010 at 3:14 AM, Fujii Masao <masao.fujii@gmail.com> wrote:

On Tue, Sep 28, 2010 at 10:34 PM, Robert Haas <robertmhaas@gmail.com> wrote:

The idea of relying on the existence of recovery.conf to determine
whether we should continue recovery forever or switch to normal
running seems somewhat klunky to me.  It mixes up settings with
control information.  Maybe the control information should move to
pg_control, and the settings to postgresql.conf.  *waves hands*

You mean to move standby_mode to postgresql.conf, and determine
whether the server should start in standby mode or not by considering
of standby_mode and the status information in pg_control?

I can't parse that. I guess I'm wondering whether standby_mode itself
should move into pg_control. Otherwise, you need a GUC somewhere that
says whether or not you should even try standby_mode plus a trigger
file that can override the GUC. It seems like there's one bit of
information that's being spread out across multiple places.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

Fujii Masao <masao.fujii@gmail.com> writes:

On Tue, Sep 28, 2010 at 10:34 PM, Robert Haas <robertmhaas@gmail.com> wrote:

The idea of relying on the existence of recovery.conf to determine
whether we should continue recovery forever or switch to normal
running seems somewhat klunky to me. �It mixes up settings with
control information. �Maybe the control information should move to
pg_control, and the settings to postgresql.conf. �*waves hands*

You mean to move standby_mode to postgresql.conf, and determine
whether the server should start in standby mode or not by considering
of standby_mode and the status information in pg_control?

I think keeping the status information in a transient text file may
still be a good design choice. If you push it into pg_control it will
be impossible to modify by hand.

regards, tom lane

On Wed, Sep 29, 2010 at 10:02 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Fujii Masao <masao.fujii@gmail.com> writes:

On Tue, Sep 28, 2010 at 10:34 PM, Robert Haas <robertmhaas@gmail.com> wrote:

The idea of relying on the existence of recovery.conf to determine
whether we should continue recovery forever or switch to normal
running seems somewhat klunky to me.  It mixes up settings with
control information.  Maybe the control information should move to
pg_control, and the settings to postgresql.conf.  *waves hands*

You mean to move standby_mode to postgresql.conf, and determine
whether the server should start in standby mode or not by considering
of standby_mode and the status information in pg_control?

I think keeping the status information in a transient text file may
still be a good design choice.  If you push it into pg_control it will
be impossible to modify by hand.

It could be done with a trivial tool, though.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

Robert Haas <robertmhaas@gmail.com> writes:

On Wed, Sep 29, 2010 at 10:02 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

I think keeping the status information in a transient text file may
still be a good design choice.  If you push it into pg_control it will
be impossible to modify by hand.

It could be done with a trivial tool, though.

pg_ctl standby … ?

--
dim

On Wed, Sep 29, 2010 at 3:09 PM, Dimitri Fontaine
<dfontaine@hi-media.com> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

On Wed, Sep 29, 2010 at 10:02 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

I think keeping the status information in a transient text file may
still be a good design choice.  If you push it into pg_control it will
be impossible to modify by hand.

It could be done with a trivial tool, though.

pg_ctl standby … ?

Well, no. I mean, you'd want some kind of pg_ctl utility for starting
in master mode vs. slave mode, and for promoting a running slave to
master.

pg_ctl start -m master
pg_ctl start -m slave
pg_ctl promote

But that's not what Tom is talking about, I don't think: you might
also want a way to explicitly whack the flag in pg_control around.
That would probably be along the lines of pg_resetxlog. I'm not sure
how much use case there is for such a thing, but if it's needed it's
certainly wouldn't be hard to write.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company