Generate ALTER SYSTEM commands

[edvinv]

Hi,
thx for great tool.

After postgresql 9.4 you can use ALTER SYSTEM SET to change individual options. IMO it will be good that those statements are also generated. This way you can very easy change configuration.

So that you get something like this:

ALTER SYSTEM SET max_connections = '16';
ALTER SYSTEM SET shared_buffers = '4GB';
ALTER SYSTEM SET effective_cache_size = '12GB';
ALTER SYSTEM SET work_mem = '256MB';
ALTER SYSTEM SET maintenance_work_mem = '1GB';
ALTER SYSTEM SET min_wal_size = '1GB';
ALTER SYSTEM SET max_wal_size = '2GB';
ALTER SYSTEM SET checkpoint_completion_target = '0.7';
ALTER SYSTEM SET wal_buffers = '16MB';
ALTER SYSTEM SET default_statistics_target = '100';

br

[le0pard]

@edvinv not sure, what the benefit from this - user can "convert" any pgtune output to this command and pgtune output works for postgresql < 9.4. Because many of this configuration apply after the next server restart:

$ vim postgresql.cof
$ services restart postgresql

is much shorter, than

$ psql
psql# ALTER SYSTEM SET ...;
psql# ALTER SYSTEM SET ...;
psql# \q
$ services restart postgresql

But maybe I don't see bigger picture.

[edvinv]

@le0pard hi I completely agree with you that you can easy convert pgtune output to ALTER SYSTEM commands with any editor or simple script. I just think that for version >=9.4 ALTER SYSTEM is the way to go and it will be convenient to have both outputs. For me (again personal choice) ALTER SYSTEM is much better because I keep postgresql.conf untouched and I can easy see all changes in postgresql.auto.conf. Of course, current pgtune output should remain as it is.

[le0pard]

Released new version with "ALTER SYSTEM" commands

[josuamarcelc]

Hi, thx for great tool.

After postgresql 9.4 you can use ALTER SYSTEM SET to change individual options. IMO it will be good that those statements are also generated. This way you can very easy change configuration.

So that you get something like this:

ALTER SYSTEM SET max_connections = '16';
ALTER SYSTEM SET shared_buffers = '4GB';
ALTER SYSTEM SET effective_cache_size = '12GB';
ALTER SYSTEM SET work_mem = '256MB';
ALTER SYSTEM SET maintenance_work_mem = '1GB';
ALTER SYSTEM SET min_wal_size = '1GB';
ALTER SYSTEM SET max_wal_size = '2GB';
ALTER SYSTEM SET checkpoint_completion_target = '0.7';
ALTER SYSTEM SET wal_buffers = '16MB';
ALTER SYSTEM SET default_statistics_target = '100';

br

you don't need to restart the POSTGRESQL, you only need to run this query after ALTER SYSTEM,

select pg_reload_conf()

[le0pard]

@josuamarcelc let's read for example shared_buffers setting from official doc

https://www.postgresql.org/docs/14/runtime-config-resource.html

This parameter can only be set at server start.

Same for max_worker_processes

This parameter can only be set at server start

And many other params.

So your proposal will not work.