pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedComparing Parameters PRE- and POST- Upgrade - Any script?
8+ messages / 5 participants
[nested] [flat]
* Comparing Parameters PRE- and POST- Upgrade - Any script?
@ 2025-03-05 20:54 Edwin UY <edwin.uy@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: Edwin UY @ 2025-03-05 20:54 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Comparing Parameters PRE- and POST- Upgrade - Any script?
@ 2025-03-05 21:03 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Edwin UY <edwin.uy@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Ron Johnson @ 2025-03-05 21:03 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Wed, Mar 5, 2025 at 3:55 PM Edwin UY <edwin.uy@gmail.com> 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?
>
pg_upgrade?
If you copy the old $PGDATA/postgresql.conf to the new $PGDATA directory,
then your config parameters will all be the same.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Comparing Parameters PRE- and POST- Upgrade - Any script?
@ 2025-03-05 21:18 Edwin UY <edwin.uy@gmail.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: Edwin UY @ 2025-03-05 21:18 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Thanks, I thought pg_upgrade will somehow insert/update it with new
parameters of sorts.
On Thu, Mar 6, 2025 at 10:03 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
> On Wed, Mar 5, 2025 at 3:55 PM Edwin UY <edwin.uy@gmail.com> 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?
>>
>
> pg_upgrade?
>
> If you copy the old $PGDATA/postgresql.conf to the new $PGDATA directory,
> then your config parameters will all be the same.
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Comparing Parameters PRE- and POST- Upgrade - Any script?
@ 2025-03-05 21:24 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Edwin UY <edwin.uy@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Scott Ribe @ 2025-03-05 21:24 UTC (permalink / raw)
To: Edwin UY <edwin.uy@gmail.com>; +Cc: Ron Johnson <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
> 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 with the inclusion of your settings.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Comparing Parameters PRE- and POST- Upgrade - Any script?
@ 2025-03-05 21:24 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Edwin UY <edwin.uy@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Ron Johnson @ 2025-03-05 21:24 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
On Wed, Mar 5, 2025 at 4:19 PM Edwin UY <edwin.uy@gmail.com> wrote:
> Thanks, I thought pg_upgrade will somehow insert/update it with new
> parameters of sorts.
>
> On Thu, Mar 6, 2025 at 10:03 AM Ron Johnson <ronljohnsonjr@gmail.com>
> wrote:
>
>> On Wed, Mar 5, 2025 at 3:55 PM Edwin UY <edwin.uy@gmail.com> 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?
>>>
>>
>> pg_upgrade?
>>
>> If you copy the old $PGDATA/postgresql.conf to the new $PGDATA directory,
>> then your config parameters will all be the same.
>>
>> --
>> Death to <Redacted>, and butter sauce.
>> Don't boil me, I'm still alive.
>> <Redacted> lobster!
>>
>
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Comparing Parameters PRE- and POST- Upgrade - Any script?
@ 2025-03-06 08:28 Achilleas Mantzios - cloud <a.mantzios@cloud.gatewaynet.com>
parent: Edwin UY <edwin.uy@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Achilleas Mantzios - cloud @ 2025-03-06 08:28 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Comparing Parameters PRE- and POST- Upgrade - Any script?
@ 2025-03-18 20:46 Bruce Momjian <bruce@momjian.us>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Bruce Momjian @ 2025-03-18 20:46 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Comparing Parameters PRE- and POST- Upgrade - Any script?
@ 2025-03-18 23:01 Edwin UY <edwin.uy@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 8+ messages in thread
From: Edwin UY @ 2025-03-18 23:01 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Ron Johnson <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
FYI, found this one and it helps.
Now I have to figure out how to format it 'nicely' when you have a long
string of sort for current_value so that it displays 'nicely'.
Will ask for help in the UNIX forum
https://www.christophermaier.name/2010/04/27/comparing-settings-from-different-postgresql-databases/
P.S.: darn it, I need a new job remotely to get more skills :( paid/unpaid
just to get ore real more interesting experience
On Wed, Mar 19, 2025 at 9:46 AM Bruce Momjian <bruce@momjian.us> wrote:
> 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.
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2025-03-18 23:01 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-03-05 20:54 Comparing Parameters PRE- and POST- Upgrade - Any script? Edwin UY <edwin.uy@gmail.com>
2025-03-05 21:03 ` Ron Johnson <ronljohnsonjr@gmail.com>
2025-03-05 21:18 ` Edwin UY <edwin.uy@gmail.com>
2025-03-05 21:24 ` Scott Ribe <scott_ribe@elevated-dev.com>
2025-03-05 21:24 ` Ron Johnson <ronljohnsonjr@gmail.com>
2025-03-18 20:46 ` Bruce Momjian <bruce@momjian.us>
2025-03-18 23:01 ` Edwin UY <edwin.uy@gmail.com>
2025-03-06 08:28 ` Achilleas Mantzios - cloud <a.mantzios@cloud.gatewaynet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox