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_w...@mail.gmail.com [2] https://postgr.es/m/cad21aocwnatmzuk_x78v57s+cc8j2ncwstipyqvmbxrftxc...@mail.gmail.com [3] https://postgr.es/m/sacpr18mb28916173ba2c27cb74fa4cff57de...@sacpr18mb289161.namprd18.prod.outlook.com [4] https://postgr.es/m/CALdSSPj2bZzVuduzn8=_7s0ukaxnwvjakkumfl0d_plwcq8...@mail.gmail.com Thanks, Shihao
v1-0002-pg_upgrade-Check-stored-settings-against-the-new-.patch
Description: Binary data
v1-0001-Move-makeAlterConfigCommand-to-fe_utils.patch
Description: Binary data
