[PostgreSQL-Hackers] recovery.conf location

+1

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

Regards,

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

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

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.

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

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.

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,

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

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.

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,

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,

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,

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.

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

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.

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

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.

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.
Hmm.. instead of that text file (i.e., recovery.conf), trigger file is
periodically polled by the standby server.

Regards,

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

...Robert

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.

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

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.

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)

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.