| From: | shihao zhong <zhong950419(at)gmail(dot)com> |
|---|---|
| To: | pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> |
| Cc: | Kirill Reshke <reshkekirill(at)gmail(dot)com>, adeshdb(at)amazon(dot)com |
| Subject: | pg_upgrade --check: catch stored settings the new cluster rejects |
| Date: | 2026-11-08 01:10:24 |
| Message-ID: | CAGRkXqRzM-68S9OhtLsTscZ_hhpHhy=ArWCdmp5GxRxo+UBmig@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi hackers,
pg_dump turns the settings stored on roles, db and functions
into ALTER ROLE ... SET and similar commands. It turns privileges on
parameters into GRANT SET. pg_upgrade --check does not look at any of
these. If the new server rejects one, --check passes and the upgrade
then fails during the restore. For example, force_parallel_mode was
renamed in 16 with no alias. A role from 15 with it set cannot be
restored. Neither can a role with default_with_oids = on.
This came up for 19 in [1], where 45762084545 made
standard_conforming_strings = off an error. Masahiko pointed out in [2]
that the problem is older, and that a fix should cover all such
settings on master. Adesh found the same failure with GRANT SET [3].
The patch asks the new server instead of keeping a list of removed
names. For each stored setting, it runs the command the restore would
run on the new cluster. Each runs under a savepoint, and all of it is
rolled back. Failures go to stored_settings.txt:
role "fpmuser": force_parallel_mode=on: unrecognized configuration
parameter "force_parallel_mode"
Kirill suggested checking each setting with set_config() in [4],
thanks. I used the real command instead. A setting like
default_tablespace can point at an object that is not restored yet. The
restore only warns about that, but set_config() fails. Since 69d5ca484b6
the check hooks decide which checks need the database state.
The downside is cost. It needs a transaction in the new cluster, and a
query per database in the old one for function settings. A list of
removed names is cheaper, but it has to be updated for every release.
0001 moves makeAlterConfigCommand() to fe_utils, so pg_upgrade builds
the same command as pg_dump. No functional change. 0002 adds the check
and a TAP test.
I tried upgrades from 14 and 18 to master. Each bad case was caught.
Without the patch the upgrade failed with the same error. Settings that
point at a tablespace or a text search configuration still pass.
[1]
https://postgr.es/m/CAGRkXqT+5+mQhoKZFTiM4yOZtVV=NW506vDwFtVeG+VmL_W5wQ@mail.gmail.com
[2]
https://postgr.es/m/CAD21AoCwNAtMZuK_x78V57S+Cc8j2nCWStipYQVmBXrFtXCsCw@mail.gmail.com
[3]
https://postgr.es/m/SACPR18MB28916173BA2C27CB74FA4CFF57DE6D2@SACPR18MB289161.namprd18.prod.outlook.com
[4]
https://postgr.es/m/CALdSSPj2bZzVuduzn8=_7s0ukaxnWVjAkKuMfL0d_PLwCQ8ixQ@mail.gmail.com
Thanks,
Shihao
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0002-pg_upgrade-Check-stored-settings-against-the-new-.patch | application/octet-stream | 19.8 KB |
| v1-0001-Move-makeAlterConfigCommand-to-fe_utils.patch | application/octet-stream | 11.2 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | shihao zhong | 2026-11-08 01:19:34 | Re: [PG19] pg_upgrade --check passes, then the upgrade fails |
| Previous Message | David G. Johnston | 2026-11-08 01:04:48 | Re: First draft of PG 19 release notes |