add ability to have custom postgresql.conf. pg_hba.conf about patroni HOT 14 CLOSED

Yes, I realize that Patroni already has a way to take config settings for postgres in the patroni.yml file. However, the formatting and organization of these is rather painful if you have a large number of special config settings.

from patroni.

Actually it's already possible to specify locations for config file and hba file with using standard options:

postgresql:
  parameters:
    hba_file: /path/to/custom/pg_hba.conf
    config_file: /path/to/custom/postgresql.conf

But it wont copy these file to the data directory.

from patroni.

Hmmm. Good point! Lemme test if that actually does what I want it to.

from patroni.

@jberkus Did testing => it did what you wanted it to? Wondering what happens next here cc @feikesteenbergen

from patroni.

I can't get this way to work properly - it adds config_file and hba_file entries to postgresql.conf but they get ignored, as those config entries only work as startup parameters to Postgres. Not sure if that was due to the introduction of the dynamic configuration.

from patroni.

We currently have our postgresql.conf and pg_hba.conf managed outside PG_DATA by Chef and are looking how to make it fit with Patroni.

For pg_hba.conf, it seems making hba_file parameter be passed on the command line (like it previously did?) would do the job.

But for postgresql.conf, it seems this would fit with the dynamic configuration by being able to make Patroni skip handling of postgresql.base.conf and specify a either custom path to a file to include on the real postgresql.conf or to a conf.d path (maybe the discussion for that one would go on #81)

If those two make sense to you we can work on implementing those.

from patroni.

Yeah, I was thinking of just supporting conf.d. Currently you can't do that via Patroni config, because of the special quoting rules for include_directory. However, if we simply always had an include_directory in the container, then the user could drop stuff in it. Agreed about hba_file.

Since I'm big on "convention over configuration", though, maybe we should make a pattern? Assume that there's a bind mount at /etc/patroni/pgconfig/. Any files found in that directory get copied into PGCONFIG at init time.

from patroni.

I can't get this way to work properly - it adds config_file and hba_file entries to postgresql.conf but they get ignored, as those config entries only work as startup parameters to Postgres. Not sure if that was due to the introduction of the dynamic configuration.

Yaeh, before dynamic configuration Patroni was passing all parameters on the command line, what makes it not possible to change values for those parameters without restart. Now only limited amount of parameters is always passed in on the command line:

• cluster_name
• hot_standby
• listen_addresses
• max_connections
• max_locks_per_transaction
• max_prepared_transactions
• max_replication_slots
• max_worker_processes
• port
• track_commit_timestamp
• wal_keep_segments
• wal_level
• wal_log_hints

Basically these parameters either MUST be the same on the master and replicas or responsible for streaming replication or just needed for Patroni to work, everything else is written into postgresql.conf.

What we can do:

1. In case if the config_file is specified and exists (and readable) on the file system we can "include" it instead of postgresql.base.conf
2. If the hba_file is specified and exists (and readable) on the file system we can pass it on the command line instead of writing into postgresql.conf

P.S., include_dir 'conf.d' approach will work for you right now without any changes in Patroni, but unfortunately it doesn't cover pg_hba case.

from patroni.

This whole area could use some better documentation and or cleanup, it's quite confusing as it stands. As of now, PostgreSQL parameters can be set in dynamic configuration in DCS, in Patroni config at postgresql.parameters, bootstrap.dcs.postgresql.parameters, and some, like listen_addresses, also in the environment. postgresql.conf is managed by Patroni, but pg_hba.conf is not, but it is initialized during startup. It's not very clear what gets updated when, and what the precedences are. For example the bootstrap section could use a sign that states in big red letters that it only gets used once and then ignored. postgresql.conf is managed by Patroni, but pg_hba.conf is not, but it is initialized during startup. Some parameters must be changed in Patroni configuration in YAML, for others the recommended way to update a parameter is to post a JSON document to Patroni API using curl. I might be turning into a grumpy old man, but that is not a nice configuration interface.

Having gotten that rant of my chest, I'll come up with some constructive patches soon. Any directions on the path to take would be very welcome.

from patroni.

Hey @CyberDem0n + @alexeyklyukin, can we draft some documentation here to address @ants' points? Happy to help out here.

from patroni.

After some testing, it seems hba_file does indeed work in postgresql.conf but config_file does not.

I've implemented support for adding a custom config file on #281, that will replace the .base.conf. I have not used the config_file Postgres parameter, though, as the behavior is not exactly the same: it includes the real config file and overrides it with any DCS/Patroni-set config instead of just replacing the whole thing.

from patroni.

BTW, my general perspective is that I'd rather see a comprehensive way to update PostgreSQL config using patroni API/DCS than ways to drop in custom files.

from patroni.

I agree, but it's a bit tricky to integrate into environments where there is already config management going on or where you just want to reuse your existing config files.

from patroni.

Solved by #281

from patroni.