Should pg_dumpall dump ALTER SYSTEM settings?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Should pg_dumpall dump ALTER SYSTEM settings?
Date: 2022-04-06 18:26:28
Message-ID: 3122015.1649269588@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Joe Conway 2022-04-06 18:26:36 Re: How about a psql backslash command to show GUCs?
Previous Message Andres Freund 2022-04-06 18:05:40 Re: SQL/JSON: JSON_TABLE