Обсуждение: Should pg_dumpall dump ALTER SYSTEM settings?

Commit a0ffa885e included some code that makes "pg_dumpall -g"
dump GRANT commands for any GUCs that have had nondefault
privileges granted on them.  I pushed that without complaint,
but it feels a little weird to me that we are worrying about
preserving grants for GUCs when we don't worry about preserving
their actual values.

Historically, we've been afraid to have pg_upgrade copy the
old installation's postgresql.conf into the new one, because
of the likelihood that the new version accepts a different
set of GUCs, which could possibly cause the new server to
fail to start; not to mention that there might be entries
such as data_directory that we had better not copy.  I think
that reasoning is still sound, but it wasn't revisited when
we added ALTER SYSTEM.

What I want to propose today is that "pg_dumpall -g" should
dump ALTER SYSTEM commands to replicate the contents of
the source system's postgresql.auto.conf (which it could
read out using the pg_file_settings view if it's running
as superuser, or less reliably from pg_settings if it isn't).
As far as I can see offhand, this'd be a great deal safer
than messing directly with postgresql.conf:

* We reject ALTER SYSTEM for the most dangerous settings
like data_directory, so they won't show up in the source file.
(Perhaps pg_dumpall should blacklist settings related to
filesystem layout, too.)

* The recipient server will validate the arguments of
ALTER SYSTEM and reject anything that it doesn't like,
so the risk of injecting bad values due to cross-version
differences seems low.

* We're already buying into the risk of cross-version GUC
incompatibility by dumping settings from pg_db_role_setting,
and that hasn't caused a lot of problems as far as I've heard.

Thoughts?

            regards, tom lane

On Wed, Apr 6, 2022 at 2:26 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Thoughts?

I'm a little bit skeptical about this proposal, mostly because it
seems like it has the end result that values that are configured in
postgresql.conf and postgresql.auto.conf end up being handled
differently: one file has to be copied by hand, while the other file's
contents are propagated forward to the new version by pg_dump. I don't
think that's what people are going to be expecting...

-- 
Robert Haas
EDB: http://www.enterprisedb.com

On Wed, 2022-04-06 at 21:39 -0400, Robert Haas wrote:
> On Wed, Apr 6, 2022 at 2:26 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Thoughts?
> 
> I'm a little bit skeptical about this proposal, mostly because it
> seems like it has the end result that values that are configured in
> postgresql.conf and postgresql.auto.conf end up being handled
> differently: one file has to be copied by hand, while the other file's
> contents are propagated forward to the new version by pg_dump. I don't
> think that's what people are going to be expecting...

"postgresql.auto.conf" is an implementation detail, and I would expect
most users to distinguish between "parameters set in postgresql.conf"
and "parameters set via the SQL statement ALTER SYSTEM".
If that is the way you look at things, then it seems natural for the
latter to be included in a dump, but not the former.

As another case in point, the Ubuntu/Debian packages split up the data
directory so that the config files are under /etc, while the rest of
the data directory is under /var/lib.  "postgresql.auto.conf" is *not*
in /etc, but in /var/lib there.  So a user of these distributions would
naturally think that the config files in /etc need to be handled manually,
but "postgresql.auto.conf" need not.

I am +1 on Tom's idea.

Yours,
Laurenz Albe

At Thu, 07 Apr 2022 12:38:43 +0200, Laurenz Albe <laurenz.albe@cybertec.at> wrote in 
> On Wed, 2022-04-06 at 21:39 -0400, Robert Haas wrote:
> > On Wed, Apr 6, 2022 at 2:26 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > > Thoughts?
> > 
> > I'm a little bit skeptical about this proposal, mostly because it
> > seems like it has the end result that values that are configured in
> > postgresql.conf and postgresql.auto.conf end up being handled
> > differently: one file has to be copied by hand, while the other file's
> > contents are propagated forward to the new version by pg_dump. I don't
> > think that's what people are going to be expecting...
> 
> "postgresql.auto.conf" is an implementation detail, and I would expect
> most users to distinguish between "parameters set in postgresql.conf"
> and "parameters set via the SQL statement ALTER SYSTEM".
> If that is the way you look at things, then it seems natural for the
> latter to be included in a dump, but not the former.
> 
> As another case in point, the Ubuntu/Debian packages split up the data
> directory so that the config files are under /etc, while the rest of
> the data directory is under /var/lib.  "postgresql.auto.conf" is *not*
> in /etc, but in /var/lib there.  So a user of these distributions would
> naturally think that the config files in /etc need to be handled manually,
> but "postgresql.auto.conf" need not.
> 
> I am +1 on Tom's idea.

I'm -0.2 if it is the default/implicit behavior. postgresql.conf and
ALTER SYSTEM SET works on the same set of settings.  If we include
.auto's settings in a dump data, it overrides the intentional changes
in postgresql.conf.  I see it a bit surprising.

I'm +-0 if it is a optional behavior.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center