[RFE] Reloadable PostgreSQL

[danbst]

Meta-issue for making PG truly reloadable. The reason for this is because not everybody implements correct handling of PG reboots

General thoughts

• reloadIfChanged NixOS systemd option doesn't work well here. Yes, it sends SIGHUP, but no, nothing actually changes on filesystem (due to immutability)
• if I set reloadable = true; for PG, I still want to be notified about when restart is required. Maybe warn during activation, maybe something automatible (allow send webhooks, notification, etc)
• to make things easier, original postgres service shouldn't change during reloads (change in Nix sense). Only when PG itself has version bump (channel advanced). The "reloader" should be a separate service, which actually can be restartable, and which manages the state for postgres service.

Configuration

Some configuration option require restart, but most often they don't. The only reliable way to distiguish them is to parse sample PG config and extract params with (change requires restart). We can't just SIGHUP PG and parse it's reload output - it does reload but still barfs about "restart required".

Then split extraConfig into "reloadable" and "restart-required" configs, and disallow "restart-only" options in "reloadable" subconfig. Kinda complicated...

And yes, "reloadable" config should be implemented using environment.etc or any other kind of managed state. Alternatively, drop immutability for PG config and move it back to PG datadir.

Same for pg_hba.conf. It's change doesn't require restart.

Plugins

In #61480 @jamessewell notes, that some plugins can be added without restart. This requires some managed state for plugins, which can be implemented using environment.etc.

Plugins which must set shared_preload_libraries should trigger restart as per configuration change.

SQL scripts

Currently adding SQL scripts in postStart makes PG restarts. This includes ensureDatabases option. It really shouldn't.

[domenkozar]

Another mid-term remedy would be to use socket activation, so if postgresql does restart, at least there's only delay instead of connection issues.

[flokli]

Related: #49528 (comment)

[stale]

I marked this as stale due to inactivity. → More info