pg_upgrade --check: catch stored settings the new cluster rejects

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

Browse pgsql-hackers by date

  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