Re: recovery.conf location

Hi,

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.

Thanks

Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

On Mon, Sep 27, 2010 at 6:56 AM, Thom Brown <thom(at)linux(dot)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(dot)fujii(at)gmail(dot)com> wrote:
> On Mon, Sep 27, 2010 at 6:56 AM, Thom Brown <thom(at)linux(dot)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(at)2ndquadrant(dot)com> writes:
> On Sun, Sep 26, 2010 at 6:29 PM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>> On Mon, Sep 27, 2010 at 6:56 AM, Thom Brown <thom(at)linux(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Jaime Casanova <jaime(at)2ndquadrant(dot)com> writes:
>> On Sun, Sep 26, 2010 at 6:29 PM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>>> On Mon, Sep 27, 2010 at 6:56 AM, Thom Brown <thom(at)linux(dot)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(at)gmail(dot)com> writes:
> On Sun, Sep 26, 2010 at 8:56 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Sun, Sep 26, 2010 at 8:56 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)2ndquadrant(dot)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(at)gmail(dot)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(dot)fujii(at)gmail(dot)com> wrote:
> On Mon, Sep 27, 2010 at 10:55 AM, Robert Haas <robertmhaas(at)gmail(dot)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(dot)fujii(at)gmail(dot)com> wrote:
> On Mon, Sep 27, 2010 at 9:35 AM, Jaime Casanova <jaime(at)2ndquadrant(dot)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(at)hagander(dot)net> wrote:
> On Mon, Sep 27, 2010 at 08:34, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>> On Mon, Sep 27, 2010 at 9:35 AM, Jaime Casanova <jaime(at)2ndquadrant(dot)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(dot)fujii(at)gmail(dot)com> wrote:
> On Mon, Sep 27, 2010 at 5:02 PM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
>> On Mon, Sep 27, 2010 at 08:34, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>>> On Mon, Sep 27, 2010 at 9:35 AM, Jaime Casanova <jaime(at)2ndquadrant(dot)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(at)gmail(dot)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(dot)fujii(at)gmail(dot)com> wrote:
> On Tue, Sep 28, 2010 at 10:34 PM, Robert Haas <robertmhaas(at)gmail(dot)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(dot)fujii(at)gmail(dot)com> writes:
> On Tue, Sep 28, 2010 at 10:34 PM, Robert Haas <robertmhaas(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Fujii Masao <masao(dot)fujii(at)gmail(dot)com> writes:
>> On Tue, Sep 28, 2010 at 10:34 PM, Robert Haas <robertmhaas(at)gmail(dot)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(at)gmail(dot)com> writes:
> On Wed, Sep 29, 2010 at 10:02 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)hi-media(dot)com> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Wed, Sep 29, 2010 at 10:02 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> 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.

Right, but instead of having to provide such a tool, we could just
store the status as a text file. There is a pretty time-honored
tradition for that, ya know.

regards, tom lane

On 9/29/10 7:54 PM, Tom Lane wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> 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.
>
> Right, but instead of having to provide such a tool, we could just
> store the status as a text file. There is a pretty time-honored
> tradition for that, ya know.

And then move all the other config parameters to postgresql.conf? And
have PG poll that text file periodically so that you could update it and
it would fail over?

+1.

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

On Fri, Oct 1, 2010 at 9:21 AM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> On 9/29/10 7:54 PM, Tom Lane wrote:
>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>> 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.
>>
>> Right, but instead of having to provide such a tool, we could just
>> store the status as a text file.  There is a pretty time-honored
>> tradition for that, ya know.
>
> And then move all the other config parameters to postgresql.conf?

The consensus seems to be to move only parameters for the standby server
(except standby_mode) to postgresql.conf. That is, primary_conninfo and
trigger_file.

> And
> have PG poll that text file periodically so that you could update it and
> it would fail over?

Hmm.. instead of that text file (i.e., recovery.conf), trigger file is
periodically polled by the standby server.

Regards,

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

On Oct 1, 2010, at 5:47 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> On Fri, Oct 1, 2010 at 9:21 AM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> On 9/29/10 7:54 PM, Tom Lane wrote:
>>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>>> 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.
>>>
>>> Right, but instead of having to provide such a tool, we could just
>>> store the status as a text file. There is a pretty time-honored
>>> tradition for that, ya know.
>>
>> And then move all the other config parameters to postgresql.conf?
>
> The consensus seems to be to move only parameters for the standby server
> (except standby_mode) to postgresql.conf. That is, primary_conninfo and
> trigger_file.
>
>> And
>> have PG poll that text file periodically so that you could update it and
>> it would fail over?
>
> Hmm.. instead of that text file (i.e., recovery.conf), trigger file is
> periodically polled by the standby server.

I'm not sure I understand the point of moving all the parameters except one.

...Robert

On Fri, 2010-10-01 at 18:47 +0900, Fujii Masao wrote:
> On Fri, Oct 1, 2010 at 9:21 AM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> > On 9/29/10 7:54 PM, Tom Lane wrote:
> >> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> >>> 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.
> >>
> >> Right, but instead of having to provide such a tool, we could just
> >> store the status as a text file. There is a pretty time-honored
> >> tradition for that, ya know.
> >
> > And then move all the other config parameters to postgresql.conf?
>
> The consensus seems to be to move only parameters for the standby server
> (except standby_mode) to postgresql.conf. That is, primary_conninfo and
> trigger_file.

I think we should allow them to be set in both places. I see no point at
all in invalidating everybody's configuration settings; we have many
external products that use this, various open source projects rely on it
plus everybody's roll-your-own scripts.

All new settings would be added to postgresql.conf

We can keep recovery.conf but recommend it is now left empty. So the
status is the existence of that file, just as it is now.

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

On Fri, Oct 1, 2010 at 8:40 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On Fri, 2010-10-01 at 18:47 +0900, Fujii Masao wrote:
>> On Fri, Oct 1, 2010 at 9:21 AM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> > On 9/29/10 7:54 PM, Tom Lane wrote:
>> >> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> >>> 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.
>> >>
>> >> Right, but instead of having to provide such a tool, we could just
>> >> store the status as a text file.  There is a pretty time-honored
>> >> tradition for that, ya know.
>> >
>> > And then move all the other config parameters to postgresql.conf?
>>
>> The consensus seems to be to move only parameters for the standby server
>> (except standby_mode) to postgresql.conf. That is, primary_conninfo and
>> trigger_file.
>
> I think we should allow them to be set in both places. I see no point at
> all in invalidating everybody's configuration settings; we have many
> external products that use this, various open source projects rely on it
> plus everybody's roll-your-own scripts.
>
> All new settings would be added to postgresql.conf
>
> We can keep recovery.conf but recommend it is now left empty. So the
> status is the existence of that file, just as it is now.

+1. Getting recovery.conf to be parsed using the same code we use for
parsing postgresql.conf would be nice from a code cleanup point of
view, too.

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

On 1 October 2010 15:41, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Fri, Oct 1, 2010 at 8:40 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> On Fri, 2010-10-01 at 18:47 +0900, Fujii Masao wrote:
>>> On Fri, Oct 1, 2010 at 9:21 AM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>>> > On 9/29/10 7:54 PM, Tom Lane wrote:
>>> >> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>> >>> 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.
>>> >>
>>> >> Right, but instead of having to provide such a tool, we could just
>>> >> store the status as a text file.  There is a pretty time-honored
>>> >> tradition for that, ya know.
>>> >
>>> > And then move all the other config parameters to postgresql.conf?
>>>
>>> The consensus seems to be to move only parameters for the standby server
>>> (except standby_mode) to postgresql.conf. That is, primary_conninfo and
>>> trigger_file.
>>
>> I think we should allow them to be set in both places. I see no point at
>> all in invalidating everybody's configuration settings; we have many
>> external products that use this, various open source projects rely on it
>> plus everybody's roll-your-own scripts.
>>
>> All new settings would be added to postgresql.conf
>>
>> We can keep recovery.conf but recommend it is now left empty. So the
>> status is the existence of that file, just as it is now.
>
> +1.  Getting recovery.conf to be parsed using the same code we use for
> parsing postgresql.conf would be nice from a code cleanup point of
> view, too.

If you're going to do that, just make it clear which conf file's
settings take precedence if someone accidental puts a setting in both.
Presumably the recovery.conf file's settings would take precedence.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

On 10/1/10 4:05 AM, Robert Haas wrote:
>>> And
>>> >> have PG poll that text file periodically so that you could update it and
>>> >> it would fail over?
>> >
>> > Hmm.. instead of that text file (i.e., recovery.conf), trigger file is
>> > periodically polled by the standby server.
>
> I'm not sure I understand the point of moving all the parameters except one.

Instead of having a setting which indicates a trigger file, and also
having a recovery.conf file (which is awkward at best) we would:

recovery.conf:
server_status = standby | active

Then instead of having a trigger file, the admin could just update the
status file in recovery.conf and save it (or overwrite the file). This
would also give admins an easy place to check what the current server
status is.

Our current arrangement of having a postgresql.conf file, a
recovery.conf file, and potentially a trigger file (during final
recovery) seems horribly hackish and impossible to manage neatly.

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

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> Then instead of having a trigger file, the admin could just update the
> status file in recovery.conf and save it (or overwrite the file).

This doesn't seem like a terribly bright idea. We've expended megabytes
of list traffic on arguing about automatic updates of config files, and
still don't have a generally acceptable solution. Now you want to make
standby-mode transitions contingent on solving that problem?

Keep the state separate from the config, please.

regards, tom lane

On Fri, Oct 1, 2010 at 4:00 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>
> Our current arrangement of having a postgresql.conf file, a
> recovery.conf file, and potentially a trigger file (during final
> recovery) seems horribly hackish and impossible to manage neatly.
>

all the contrary, IMHO what we have now seems *almost* fine... what we
need to do is to have another standby side config file called maybe:
standby.conf, otherwise we will end up with another huge configuration
file ala postgresql.conf

so we remove from recovery.conf what doesn't belong there... maybe is
a good idea to put recovery_command on postgresql.conf (or in both
files, recovery.conf and standby.conf, because they are no supposed to
co-exist)

if we have an standby server we use standby.conf, if we are
recoverying a backup we use recovery.conf; and we can specify
parameters in each situation (probably standby_mode should go away too
because the only existence of a standby.conf file imply we are a
standby but that is debatable)

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

On 02.10.2010 00:20, Tom Lane wrote:
> Josh Berkus<josh(at)agliodbs(dot)com> writes:
>> Then instead of having a trigger file, the admin could just update the
>> status file in recovery.conf and save it (or overwrite the file).
>
> This doesn't seem like a terribly bright idea. We've expended megabytes
> of list traffic on arguing about automatic updates of config files, and
> still don't have a generally acceptable solution. Now you want to make
> standby-mode transitions contingent on solving that problem?
>
> Keep the state separate from the config, please.

There is also a permissions problem if whatever triggers the failover
needs to modify a config file. A separate trigger file can be in
/var/run/foo or something, but recovery.conf is in the data directory.
You don't want to give monitoring tools that decide on failover write
access to the data directory.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com