Re: Permanent settings - Mailing list pgsql-hackers

On Feb 19, 2008 10:31 PM, Josh Berkus <josh@agliodbs.com> wrote:
> Magnus, All,
>
> This is something I've been thinking about too, just because my efforts to
> write auto-config scripts have gotten bogged down in the need to parse and
> write .conf files in a paltform-agnostic way and preserve comments.  I
> agree with Magnus that it's something we need to address.  Having the
> ability to update .conf through an api other than reading & writing a file
> one line will make developing future autotuning tools significanly easier.
>
> I think that the idea of just appending extra lines to the bottom of the
> file
> in chronoligical (or random) order is so messy and hackish that it's simply
> not worthy of consideration for the PostgreSQL project.

I don't like it either.  I think there is a place of chronological
list of changes
made to the configuration -- it is the log file.  When configuration is changed
remotely it must be logged, and an extra comment message might be nice.
>
> Instead, here's my proposal:
>
> 1) add to the top of postgresql.conf another file switch, like this:
>
>         # auto_config_file = 'ConfigDir/postgresql.auto.conf'
>         # if set, the auto config file will be read by the system and override the
> settings in the rest of this postgresql.conf file, which will be ignored.
>         # to disable automated and SQL command-line-based configuration
>         # comment the above or set it to an empty string
>
> 2) split the "category" column in pg_settings into two columns, and add a
> categories lookup table, so it can be sorted properly
>
> 3) have command line config write to postgresql.auto.conf, dumping the
> whole of pg_settings organized with headings in categories order.
>
> I think an arrangement like that will work well with pg_settings based
> config, autotuning, while still allowing backwards-compatible manual
> control via postgresql.conf.

I kind of like the idea of having two files -- one user-managed and one
database-managed.  But let me first write few issues of general matter.

1) changes that cannot be done to live server:

SET PERMANENT shared_buffers = '1GB';

Now, this is a setting that cannot be changed "live", but it should be
changeable.  And we need a command to query what's permanent
and what's current.

2) '1GB' -- If we are modifying postgres.conf I _think_ the format should
be preserved, so not changed into number of pages but written 'as-is'.

3) If we do have two configuration files (+1), I think PostgreSQL should
issue a BIG FAT WARNING saying that its overriding user-managed
postgres.conf wih postgres.auto, on a per-setting basis.  This way nobody
would be surprised why their setting is not working.

And the rollback of all remote changes would be one unlink away.

4) Saving actual file.  Sometimes it could be nice to be able set work_mem
globally (as if by postgres.conf) but not permanent (so you don't see these
settings on next start), though I am not convinced the feature is worth the
risks of people mixing up things.

5) if we have a file that is 100% PostgreSQL controlled, we could some
day use it
as an alternative to pg_hba.conf and pg_ident.conf.
  Regards,     Dawid