`postgresql.override.conf` should not be located in the postgres database directory

[doraskayo]

I have searched the existing issues, both open and closed, to make sure this is not a duplicate report.

• Yes

The bug

postgresql.override.conf is currently expected to be added to the database directory:
https://github.com/immich-app/base-images/blob/462f4b1022791ceb91d5d7b8c4bc2139120f7a65/postgres/postgresql.ssd.conf#L15

However, this means that it is not possible to create new databases with a pre-existing postgresql.override.conf file. This is because initdb expects the database directory to be empty when creating a new database, and postgresql.override.conf being present in the directory results in an error.

Specifically, when postgresql.override.conf is bound to /var/lib/postgresql/data/postgresql.override.conf` in the database container, the following error is seen for new databases:

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

The only workaround is to create a database without postgresql.override.conf and add it after the database is successly created. However, doing so defeats the purpose of this file.

The OS that Immich Server is running on

Flatcar 4547.1.0

Version of Immich Server

v2.5.6

Version of Immich Mobile App

v2.5.6

Platform with the issue

• Server
• Web
• Mobile

Device make and model

No response

Your docker-compose.yml content

...
  database:
    container_name: immich_postgres
    image: ghcr.io/immich-app/postgres:14-vectorchord0.4.3-pgvectors0.2.0@sha256:bcf63357191b76a916ae5eb93464d65c07511da41e3bf7a8416db519b40b1c23
    environment:
      POSTGRES_PASSWORD: ${DB_PASSWORD}
      POSTGRES_USER: ${DB_USERNAME}
      POSTGRES_DB: ${DB_DATABASE_NAME}
      POSTGRES_INITDB_ARGS: '--data-checksums'
      # Uncomment the DB_STORAGE_TYPE: 'HDD' var if your database isn't stored on SSDs
      # DB_STORAGE_TYPE: 'HDD'
    volumes:
      # Do not edit the next line. If you want to change the database storage location on your system, edit the value of DB_DATA_LOCATION in the .env file
      - ${DB_DATA_LOCATION}:/var/lib/postgresql/data
      - ./postgresql.override.conf:/var/lib/postgresql/data/postgresql.override.conf
    shm_size: 128mb
    restart: always
    healthcheck:
      disable: false
...

Your .env content

# You can find documentation for all the supported env variables at https://docs.immich.app/install/environment-variables

# The location where your uploaded files are stored
UPLOAD_LOCATION=./library

# The location where your database files are stored. Network shares are not supported for the database
DB_DATA_LOCATION=./postgres

# To set a timezone, uncomment the next line and change Etc/UTC to a TZ identifier from this list: https://en.wikipedia.org/wiki/List_of_tz_database_time_zones#List
# TZ=Etc/UTC

# The Immich version to use. You can pin this to a specific version like "v2.1.0"
IMMICH_VERSION=v2

# Connection secret for postgres. You should change it to a random password
# Please use only the characters `A-Za-z0-9`, without special characters or spaces
DB_PASSWORD=postgres

# The values below this line do not need to be changed
###################################################################################
DB_USERNAME=postgres
DB_DATABASE_NAME=immich

Reproduction steps

1. Make sure the host directory bound to `/var/lib/postgresql/data` in the database container is empty. Essentually a new database scenario.
2. Set `postgresql.override.conf` to be bound to `/var/lib/postgresql/data/postgresql.override.conf` in the database container.
3. Try to run the database container. You will get the error mentioned in this issue.

Relevant log output

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

Additional information

No response

[bo0tzz]

What's the reason you're trying to manually create a database with your own override.conf?

[doraskayo]

What's the reason you're trying to manually create a database with your own override.conf?

Postgres currently listens on all IPs/interfaces by default, as you can see here:

$ grep "listen_addresses" /var/lib/postgresql/data/postgresql.conf        
listen_addresses = '*'

I have a setup where I would like it to only listen on localhost for security reasons. I use this postgresql.override.conf to accomplish this:

listen_addresses = '::1,127.0.0.1'

However, this issue is not about my specific use case. It's about the fact that whenever someone wants to add their own postgresql.override.conf to docker-compose.yml, they cannot do so unless they had already created a database. Essentially, when one wants to add their own postgresql.override.conf, they cannot use the same docker-compose.yml file both for the initial execution of the database container and also for its consecutive executions. This seems like a design issue of this feature.

[mmomjian]

I have a setup where I would like it to only listen on localhost for security reasons

This should be handled with Docker port mapping, not with the Postgres config.

Broadly speaking, our Postgres base image is designed to be used with a relatively stock Immich installation. Users planning to do custom deployments would be better served with a standalone database instance. I recommend closing this as it is not a bug.

Moving the override file has potential to cause permissions issues with the underlying image, so it might not be as simple a change as anticipated.

[doraskayo]

@mmomjian, please ignore my personal use case. As I wrote in my comment above, it is irrelevant to this bug report.

I assume postgresql.override.conf was added to allow users to override the default configuration from the Postgres base image. If it was indeed its purpose, I would consider the fact that this feature cannot be used unless a database already exists to be a design issue (i.e., a bug). It is likely that the person who added this feature only tested it with an existing database. Otherwise they would have likely found this bug early on and implemented it in a way that works with new databases as well.

I haven't tested using an alternative location myself, but which permission issues would you expect to see here? Why shouldn't it be possible to expect users to bind this file to /etc/postgresql/postgresql.override.conf, or frankly to any other path in the root filesystem? How would doing so be any different than binding a host directory to /var/lib/postgresql/data in the container like the default docker-compose.yml already does?

The existing file location can still be honoured for backwards compatibility of course, so there shouldn't be a risk that this would break existing setups.

[meesfrensel]

Fixed in immich-app/base-images#315 and updated in #27273

[doraskayo]

Thanks @mertalev!