Separating config using PGDATA does not seem to behave as described in the docs

[tucked]

https://www.postgresql.org/docs/15/runtime-config-file-locations.html

If you wish to keep the configuration files elsewhere than the data directory, the postgres -D command-line option or PGDATA environment variable must point to the directory containing the configuration files, and the data_directory parameter must be set in postgresql.conf (or on the command line) to show where the data directory is actually located. Notice that data_directory overrides -D and PGDATA for the location of the data directory, but not for the location of the configuration files.

$ sudo grep data_directory /example/postgresql.conf
data_directory = '/var/lib/postgresql/data'             # use data in another directory
$ sudo docker run -e PGDATA=/etc/postgresql -e POSTGRES_PASSWORD=foo -v /example/postgresql.conf:/etc/postgresql/postgresql.conf -v /example/postgresql-data:/var/lib/postgresql/data postgres:15.2
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

initdb: error: directory "/etc/postgresql" exists but is not empty
initdb: hint: If you want to create a new database system, either remove or empty the directory "/etc/postgresql" or run initdb with an argument other than "/etc/postgresql".
The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

Am I missing something? I'm trying to have postgres to look at my config file, see data_directory, and initialize a new DB in data_directory if it is empty, otherwise use it as-is.

[LaurentGoderre]

Not sure what you are trying to do do but simply doing the following works

docker run -e PGDATA=/some/other/path -e POSTGRES_PASSWORD=foo postgres:15.2

[tucked]

@LaurentGoderre you aren't providing a config file, so /some/other/path is presumably empty, which is what initdb is complaining about when I provide a config file:

initdb: error: directory "/etc/postgresql" exists but is not empty

I'm trying to keep my config file separate from the data directory.

[LaurentGoderre]

@tucked no, it created the db

root@a9b2462dc6f0:/# ls /some/other/path/
base    pg_commit_ts  pg_hba.conf    pg_logical    pg_notify    pg_serial     pg_stat      pg_subtrans  pg_twophase  pg_wal   postgresql.auto.conf  postmaster.opts
global  pg_dynshmem   pg_ident.conf  pg_multixact  pg_replslot  pg_snapshots  pg_stat_tmp  pg_tblspc    PG_VERSION   pg_xact  postgresql.conf       postmaster.pid
root@a9b2462dc6f0:/# 

[LaurentGoderre]

Maybe there is a conflict between the env var and your config file?

[tucked]

Please try my example.

The docs say I should be able to set PGDATA to the location of my config file, which then set the data_directory and that doesn't seem to work (because initdb complains that PGDATA isn't empty -- because it contains my config file).

Alternatively, the effect I want can be achieved like this:

sudo docker run -e POSTGRES_PASSWORD=foo -v /example/postgresql.conf:/etc/postgresql/postgresql.conf -v /example/postgresql-data:/var/lib/postgresql/data postgres:15.2 -c config_file=/etc/postgresql/postgresql.conf

[LaurentGoderre]

@tucked I think your data needs to either be in subfolder or a completely different directory than your config file

[tucked]

It is:

$ sudo grep data_directory /example/postgresql.conf
data_directory = '/var/lib/postgresql/data'             # use data in another directory

[LaurentGoderre]

It worked for me. Mahybe the issue is permission of the mounted file and folders? I noticed you used /example

[tucked]

It worked for me.

What command did you run?

Mahybe the issue is permission of the mounted file and folders?

I don't think that explains the error from initdb

initdb: error: directory "/etc/postgresql" exists but is not empty
initdb: hint: If you want to create a new database system, either remove or empty the directory "/etc/postgresql" or run initdb with an argument other than "/etc/postgresql".

[yosifkit]

It seems like initdb doesn't really support data directory in this way since it wants to do the full "init" (copying postgresql.conf.sample and creating pg_hba.conf). It doesn't even read data_directory from the -c config_file. It happens to work because it is the same as the image default PGDATA but breaks if it is somewhere else.

Broken version (works without the 22 on the directory):

$ ls -lan data/
total 8
drwx------ 2 1000 1000 4096 May 13 15:28 .
drwxrwsr-x 6 1000 1000 4096 May 13 15:15 ..
$ ls -ln conf/postgresql.conf
-rw-r--r-- 1 1000 1000 68 May 13 15:52 conf/postgresql.conf
$ cat conf/postgresql.conf
data_directory = '/var/lib/postgresql/data22'
shared_buffers=2048MB
$ docker run -it --rm -e POSTGRES_PASSWORD=foo \
  -v "$PWD/data:/var/lib/postgresql/data22" \
  -v "$PWD/conf/:/etc/postgresql/" \
  postgres:15 -c config_file=/etc/postgresql/postgresql.conf
...
fixing permissions on existing directory /var/lib/postgresql/data ... ok
...
2024-05-13 22:53:33.200 GMT [43] LOG:  skipping missing configuration file "/var/lib/postgresql/data22/postgresql.auto.conf"
waiting for server to start....2024-05-13 22:53:33.211 GMT [48] LOG:  skipping missing configuration file "/var/lib/postgresql/data22/postgresql.auto.conf"
2024-05-13 22:53:33.211 GMT [48] FATAL:  data directory "/var/lib/postgresql/data22" has wrong ownership
2024-05-13 22:53:33.211 GMT [48] HINT:  The server must be started by the user that owns the data directory.
 stopped waiting
pg_ctl: could not start server

$ ls -lan data/
total 8
drwx------ 2 1000 1000 4096 May 13 15:28 .
drwxrwsr-x 6 1000 1000 4096 May 13 15:15 ..

---

Semi-related issue: #1220

[tucked]

It doesn't even read data_directory from the -c config_file

I'm not sure that's true (altho, you're right in that it still doesn't work 😩)

$ ll
total 32K
drwxr-x--- 2    999 root   6 May 13 23:48 data
-rw-r--r-- 1 tucked root 29K May 13 23:45 postgresql.conf
$ sudo ls -l data
total 0
$ grep data_directory postgresql.conf 
data_directory = '/data'                # use data in another directory
$ sudo docker run --name postgres -e POSTGRES_PASSWORD=foo -v $PWD/postgresql.conf:/etc/postgresql/postgresql.conf -v $PWD/data:/data postgres:15.2 -c config_file=/etc/postgresql/postgresql.conf
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok


Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgresql/data -l logfile start

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
2024-05-13 23:50:06.653 GMT [42] LOG:  skipping missing configuration file "/data/postgresql.auto.conf"
waiting for server to start....2024-05-13 23:50:06.701 GMT [47] LOG:  skipping missing configuration file "/data/postgresql.auto.conf"
2024-05-13 23:50:06.702 UTC [47] FATAL:  "/data" is not a valid data directory
2024-05-13 23:50:06.702 UTC [47] DETAIL:  File "/data/PG_VERSION" is missing.
 stopped waiting
pg_ctl: could not start server
Examine the log output.

Is this a product bug (it's supposed to work) or a doc bug (we should stop saying this is supposed to work)?

[yosifkit]

It looks like this is a limitation of initdb. It is not designed to handle config coming from a different directory since it is trying to create the initial config; it was really made for regular hosts and VMs where creating a new database directory is usually only done once on package installation. The data_directory setting will work fine if you have an already initialized database directory (as pg_*cluster scripts from the postgresql-common package does).