refactor(postgresql.conf): configure postgresql.conf to use 'include_dir' directive

[hunleyd]

What kind of change does this PR introduce?

Moved all config files that are included in the postgresql.conf into the /etc/postgresql-custom dir then updated postgresql.conf to use include_dir instead of include. To ensure the same ordering of the various GUCs (to ensure proper overriding of values), rename files with digits prepended. Finally, split auto_explain and pg_cron out into their own config files instead of them being tacked on the end of the main PG config.

this is take 2, which should fix the supautils issue

[samrose]

@hunleyd this is still in draft mode of course. But requesting that once we are ready for review, we need @soedirgo @steve-chavez @za-arthur and myself to review before we merge and deploy just due to all of the changes.

[hunleyd]

looks like it fails on:

2025-10-24T18:05:08.0704634Z error: Cannot build '/nix/store/6ppacf0lg2xjbgw5f08bv3z0z95y1l0g-postgres-17_11-check-harness.drv'.
2025-10-24T18:05:08.0708489Z        Reason: builder failed with exit code 1.
2025-10-24T18:05:08.0709732Z        Output paths:
2025-10-24T18:05:08.0711914Z          /nix/store/i2cw7k1wnlfffmd68gyn5d26b4fvy1j1-postgres-17_11-check-harness
2025-10-24T18:05:08.0713654Z        Last 25 log lines:
2025-10-24T18:05:08.0714742Z        >
2025-10-24T18:05:08.0715708Z        > pg_regress tests failed
2025-10-24T18:05:08.0719432Z        > diff -U3 /nix/store/a58i3yh31cjad0lgmyx4p7xy9zmg3l13-tests/expected/postgres_fdw.out /nix/store/i2cw7k1wnlfffmd68gyn5d26b4fvy1j1-postgres-17_11-check-harness/regression_output/results/postgres_fdw.out
2025-10-24T18:05:08.0724613Z        > --- /nix/store/a58i3yh31cjad0lgmyx4p7xy9zmg3l13-tests/expected/postgres_fdw.out       1970-01-01 00:00:01.000000000 +0000
2025-10-24T18:05:08.0729147Z        > +++ /nix/store/i2cw7k1wnlfffmd68gyn5d26b4fvy1j1-postgres-17_11-check-harness/regression_output/results/postgres_fdw.out     2025-10-24 18:05:05.290257489 +0000
2025-10-24T18:05:08.0732085Z        > @@ -14,10 +14,9 @@
2025-10-24T18:05:08.0733091Z        >  set role postgres;
2025-10-24T18:05:08.0734286Z        >  -- postgres_fdw should be owned by the superuser
2025-10-24T18:05:08.0736742Z        >  select fdwowner::regrole from pg_foreign_data_wrapper where fdwname = 'postgres_fdw';
2025-10-24T18:05:08.0738481Z        > -    fdwowner
2025-10-24T18:05:08.0739419Z        > -----------------
2025-10-24T18:05:08.0740424Z        > - supabase_admin
2025-10-24T18:05:08.0741376Z        > -(1 row)
2025-10-24T18:05:08.0742248Z        > + fdwowner
2025-10-24T18:05:08.0743121Z        > +----------
2025-10-24T18:05:08.0743821Z        > +(0 rows)
2025-10-24T18:05:08.0744133Z        >
2025-10-24T18:05:08.0744963Z        >  -- Verify that `postgres` can use the FDW despite not owning it
2025-10-24T18:05:08.0745499Z        >  create server s
2025-10-24T18:05:08.0745837Z        > @@ -27,4 +26,5 @@
2025-10-24T18:05:08.0746176Z        >      port '5432',
2025-10-24T18:05:08.0746547Z        >      dbname 'postgres'
2025-10-24T18:05:08.0746898Z        >    );
2025-10-24T18:05:08.0747307Z        > +ERROR:  foreign-data wrapper "postgres_fdw" does not exist
2025-10-24T18:05:08.0747804Z        >  rollback;
2025-10-24T18:05:08.0748127Z        For full logs, run:
2025-10-24T18:05:08.0748724Z          nix log /nix/store/6ppacf0lg2xjbgw5f08bv3z0z95y1l0g-postgres-17_11-check-harness.drv

is that what you meant by 'tests still need some work' @samrose ?

[samrose]

@hunleyd no the tests were expected to pass. But probably whatever is causing the issue is simple. I'll take a look and report back

[jchancojr]

getting supautils related errors

postgres@db-wfcgdcogpuhyxlzxaiur:~$ psql
WARNING:  invalid configuration parameter name "supautils.disable_program", removing it
DETAIL:  "supautils" is now a reserved prefix.

[jchancojr]

/data/pgdata/postgresql.conf still exists. Should we symlink this to postgresql-custom or something? @hunleyd

[hunleyd]

/data/pgdata/postgresql.conf still exists. Should we symlink this to postgresql-custom or something? @hunleyd

imho, we should, yes. or remove them entirely. all the .conf files in this dir (except postgresql.auto.conf obviously).

interested in @samrose 's thoughts though

[samrose]

@hunleyd see if you agree with all of this

Recommending remove the symlink entirely and maintain your current approach of configs in /etc/postgresql/ with lots of testing to make sure nothing breaks:

  Why This is Best:

  1. PostgreSQL Official Documentation endorses separating configs from data
  2. Debian/Ubuntu Standard Practice (which your Ansible builds follow)
  3. Your current postgresql.conf.j2 already implements this correctly:
  data_directory = '/var/lib/postgresql/data'
  hba_file = '/etc/postgresql/pg_hba.conf'
  include_dir = '/etc/postgresql-custom/conf.d'
  4. The symlink serves no purpose - PostgreSQL is already configured to read from /etc/

• PostgreSQL Official Docs: https://www.postgresql.org/docs/current/runtime-config-file-locations.html
• include_dir Best Practices: https://www.postgresql.org/docs/current/config-setting.html

Just please test as much as you can in local infra, and we should run e2e tests against your branch. Please create a playbook to document running e2e tests, but also reach out for help if you get stuck trying to do it. Thanks for the careful work on this.

[blacksmith-sh[bot]]

Found 1 test failure on Blacksmith runners:

Failure

Test	View Logs
z_17_pg_stat_monitor	View Logs