Thread: Comparing Parameters PRE- and POST- Upgrade - Any script?

> On Mar 5, 2025, at 2:18 PM, Edwin UY <edwin.uy@gmail.com> wrote:
>
> Thanks, I thought pg_upgrade will somehow insert/update it with new parameters of sorts.

No. So you wind up with new parameters just getting defaults, and not specified or documented in the file.

Couple of ways around this:

- Put your parameters in a separate file that's included into postgresql.conf.
- Generate a new db, diff its config against the old one to see what's new that you might want to change, and use it
withthe inclusion of your settings. 

On 3/5/25 22:54, Edwin UY wrote:
> Hi,
>
> Does anyone know of a script that can be used to compare parameters 
> pre- and post- upgrade?
> Is it something that's worth the effort though? Or is it not something 
> to be worrying about?
> We only need to check pg_settings for this, is that correct?

Basically those the difference can be divided in the following categories :

a) parameters that are not supported any more in the new version

b) parameters that are introduced in the new version

c) parameters that have changed their name across the two versions and 
maybe the unit as well

So you have to isolate all non defaults from the old cluster and then 
read the docs and plan about all actions related to the GUC params in 
a), b)  and c) . Usually grep, ripgrep, awk, sort, uniq, and all the 
unix goodies along pg_settings are your friends to find differences.

Across more "distant" upgrades (I did pgsql10 -> pgsql16 recently) it 
becomes more painful. Across more close upgrades it is easier. If you 
dont know what to do with a new param, or dont have time to research, or 
benchmark, leave it to the default, and note this down to take into 
consideration in the next performance test. Some of them especially the 
new ones (e.g. JIT) sometimes might give tricky results so you may chose 
to be a little conservative and progress as you read and test things.

On Wed, Mar  5, 2025 at 04:24:24PM -0500, Ron Johnson wrote:
> The default postgresql.conf file might change across versions.
> 
> I remove everything from postgresql except for site-specific customizations,
> and copy those and pg_hba.conf over.
> 
> There's certainly no harm in doing "SELECT name, setting, unit FROM pg_settings
> ORDER BY name;" on the old instance, and then on the new instance after it's
> properly configured.

You can query just the changed values with:

    SELECT name, current_setting(name), source
    FROM pg_settings
    WHERE source NOT IN ('default', 'override');

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Do not let urgent matters crowd out time for investment in the future.