Unable to change Postgresql data directory with systemd service overrides on Ubuntu 22

Question

I am trying to change the data directory that Postgres uses to look for its configuration files.

I have a fresh update of Ubuntu 22 and newly installed Postgresql@15.

In order to maintain version control over my Postgres configuration files, I've copied over the default configs to a new data directory in my server-configs repo, /usr/local/apps/server-configs/configs/postgres/postgres_data/.

Postgresql installs its service files to these paths: (/lib, not /etc):

/lib/systemd/system/postgresql.service
/lib/systemd/system/[email protected]

Via sudo systemctl edit postgresql and sudo systemctl edit [email protected], I have added the following service environment variable assignment to both locations:

[Service]
Environment=
Environment=PGDATA=/usr/local/apps/server-configs/configs/postgres/postgres_data/

(The double Environment declarations may be incorrect. In troubleshooting this, I read somewhere that they must be set to clear whatever was there before.)

In turn this created 2 override files:

postgresql.service.d/override.conf
[email protected]/override.conf

I then did both a daemon-reload and restarted Postgres, just for kicks.

sudo systemctl daemon-reload
sudo systemctl restart postgresql

Finally to check if the new data directory was picked up by Postgres, I connected to the database and checked via this method:

sudo -iu postgres
psql
postgres@logs:~$ psql

psql (15.2 (Ubuntu 15.2-1.pgdg22.04+1))
Type "help" for help.

postgres=# SHOW config_file;
               config_file               
-----------------------------------------
 /etc/postgresql/15/main/postgresql.conf
(1 row)

I've been reading lots of walk throughs that describe this as more or less the correct way to solve this. But no matter what I've been trying, the config file still points at the installed one and not the version in my server-configs repo.

What am I missing? Thanks!

Answer 1

I experienced a similar issue and it turns out I had a shadow postgres installed alongside my manually compiled version. The shadow postgres was installed with apt using postgres-contrib-15. My systemd script below was actually starting the shadow postgres using a global postgresql.conf

[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)
Wants=network-online.target
After=network-online.target

[Service]
Type=notify
User=postgres
Environment=PGDATA=/var/lib/postgresql/data
ExecStart=/usr/lib/postgresql/bin/postgres -D /var/lib/postgresql/data
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=infinity

[Install]
WantedBy=multi-user.target

edit to fix the issue I simply removed postgres-contrib-15 with sudo apt remove postgres-contrib-15