Parameter overrides for Postgres does not always apply

[onprem]

What happened?

TOBS is applying some overrides for PostgreSQL config parameters here and the users can apply their own overrides as well. I noticed that these overrides do not always apply to the final configuration. To be more specific, the parameters added by Patroni get overridden by the parameters set in /var/run/postgresql/timescaledb.conf, which is created by tstune.

Did you expect to see something different? I expected tstune to override the base configuration, and Patroni to override both base and timescaledb.conf.

How to reproduce it (as minimally and precisely as possible):

Using these values, do a helm install of tobs

timescaledb-single:
  patroni:
    bootstrap:
      dcs:
        postgresql:
          parameters:
            autovacuum_max_workers: 1
            shared_buffers: 30GB

and after running a SHOW ALL; SQL query, check the parameters, they would be something like:

autovacuum_max_workers: 10
shared_buffers: 512MB

Environment

• tobs version:

  v17.23.1

Anything else we need to know?:

In the container, postgresl runs with --config-file /var/lib/postgresql/postgresql.base.conf, which looks something like

include 'postgresql.base.conf'

# user overrides
autovacuum_max_workers = '1'
shared_buffers = '30GB'

The postgresql.base.conf included here contains the postgres defaults, and an include if exists to timescaledb.conf.

# postgres defaults
shared_buffers = 128MB
...

include_if_exists '/var/run/postgresql/timescaledb.conf'

So, ideally, postgres starts reading postgresql.conf, sees the include postgresql.base.conf and starts reading there, it'll encounter the defaults parameters, then in encounters the include_if_exists to timescaledb.conf, reads the file and overrides the parameters from base config, after that it comes back to postgresql.conf and then the parameters defined there override both postgresql.base.conf and timescaledb.conf.

The order of precedence should be postgresql.conf -> timescaledb.conf -> postgresql.base.conf, but somehow it is timescaledb.conf -> postgresql.conf -> postgresql.base.conf.

[onprem]

Restarting the TimescaleDB pod made this work and I got the overrides to apply correctly 🪄

[mindrunner]

I see very similar behavior, restarting the pod does not help

[Meet-S0ni]

i want to update max_connections in my running tsdb

i have tried to change this in patroni cm tried to change directly in postgresql.conf file from different pod but Chages dose not work tried changing parameter in helm chart and after i updated my chart but still the same old value

[kopecmaciej]

If someone is still facing difficulties updating core values such as max_connections or max_worker_processes, I've found a workaround that has proven effective for me (though I cannot guarantee that it's the correct approach), but it was the only way for me to update those values.

Firstly, ensure that you've updated the values and confirmed that they have been changed in the patroni configmap. Next, you should delete the postmaster.opts file located in /var/lib/postgresql/data (this pertains to the new pg15 version). Additionally, you might consider removing the postmaster.pid file.

The postmaster.opts file contains a command that will initiate (even after you've updated the values and deleted the pod). Removing this file will prompt the script to update this command with the new values. In my situation, the postmaster.opts file appears like this:

/usr/lib/postgresql/15/bin/postgres "-D" "/var/lib/postgresql/data" "--config-file=/var/lib/postgresql/data/postgresql.conf" [...other core values] "--max_connections=150" "--max_worker_processes=29"

So as you can see values in postgresql.conf were overwritten by those added as flags