immich support

[th-2021]

When trying to use this for immich (https://github.com/immich-app) I found 2 issues:

1. immich requires that the db user is the owner of the database.
   This corresponds to the latest gitlab database documentation.
   Is this standard for other apps as well? Then this should get chnged. (This would render the grant statements obsolete)

2. immich uses extensions that have dependencies.
   As a workaround the dependencies can be added to the list.
   immich documentation uses: create extension CASCADE;
   which will load the dependencies automatically.

[kkimurak]

1. set DB_USER to owner : not sure but may be common. How about adding environment variable like DB_USER_IS_DB_OWNER (true/false) ?

2. Current code does not handle separator correctly so the syntax like --env DB_EXTENSIONS="vchord CASCADE,bree_gist,pg_trgm" should not work (vchord and CASCADE will be handled separately) because shell (bash) internally treat whitespace as separator by default.
   This ca be fixed by setting separator to IFS instead of awk -F. I have pushed kkimurak/docker-postgresql@aa9438d:

   docker-postgresql/runtime/functions

   Lines 321 to 324 in f5b7752

   	for extension in $(awk -F',' '{for (i = 1 ; i <= NF ; i++) print $i}' <<< "${DB_EXTENSION}"); do
   	echo "‣ Loading ${extension} extension..."
   	psql -U ${PG_USER} -d ${database} -c "CREATE EXTENSION IF NOT EXISTS ${extension};" >/dev/null 2>&1
   	done

   -  for extension in $(awk -F',' '{for (i = 1 ; i <= NF ; i++) print $i}' <<< "${DB_EXTENSION}"); do
   +  IFS_ORG=$IFS
   +  IFS=,
   +  for extension in ${DB_EXTENSION}; do
        echo "‣ Loading ${extension} extension..."
        psql -U ${PG_USER} -d ${database} -c "CREATE EXTENSION IF NOT EXISTS ${extension};" >/dev/null 2>&1
      done
   +  IFS=$IFS_ORG
    }

   By applying this patch we can include any parameters we want to use for CREATE_EXTENSION (such as CASCADE, WITH, SCHEMA ... or VERSION ...) in DB_EXTENSIONS like above.

   But, for vchord, we have to add installation process to Dockerfile as well. I have pushed kkimurak/docker-postgresql@345c742 (testing branch). I was able to confirm that it could be built, started, and the following log was displayed, which suggests that the vchord extension was loaded. I'm not sure how to use it so I hope it will help for testing.

   By the way, Would there be a need for a way to install such extensions at runtime? It may be difficult to cover all of extensions so I think it would be useful for user to write install script and let this image to load / run it on startup.

[th-2021]

Ad 1) ok with me Ad 2) couldn't we leave the env var as is and just change the create extension in the script and add cascade? For vchord I have a Dockerfile, which extends the postgresql one. This is imho the easiest way for adding code. It can be a deb package or a github address... Am 11. Juli 2025 12:22:28 MESZ schrieb KIMURA Kazunori ***@***.***>:

…

kkimurak left a comment (sameersbn/docker-postgresql#173) 1. set DB_USER to owner : not sure but may be common. How about adding environment variable like `DB_USER_IS_DB_OWNER` (true/false) ? 2. Current code does not handle separator correctly so the syntax like `--env DB_EXTENSIONS="vchord CASCADE,bree_gist,pg_trgm"` should not work (`vchord` and `CASCADE` will be handled separately) because shell (`bash`) internally treat whitespace as separator by default. This ca be fixed by setting separator to `IFS` instead of `awk -F`. I have pushed ***@***.***: https://github.com/sameersbn/docker-postgresql/blob/f5b7752b9336b89a651541cf9872e6fe5cff4e4e/runtime/functions#L321-L324 ```diff - for extension in $(awk -F',' '{for (i = 1 ; i <= NF ; i++) print $i}' <<< "${DB_EXTENSION}"); do + IFS_ORG=$IFS + IFS=, + for extension in ${DB_EXTENSION}; do echo "‣ Loading ${extension} extension..." psql -U ${PG_USER} -d ${database} -c "CREATE EXTENSION IF NOT EXISTS ${extension};" >/dev/null 2>&1 done + IFS=$IFS_ORG } ``` By applying this patch we can include any parameters we want to use for `CREATE_EXTENSION` (such as `CASCADE`, `WITH`, `SCHEMA ...` or `VERSION ...`) in `DB_EXTENSIONS` like above. But, for `vchord`, we have to add installation process to Dockerfile as well. I have pushed ***@***.*** (`testing` branch). I was able to confirm that it could be built, started, and the following log was displayed, which suggests that the vchord extension was loaded. I'm not sure how to use it so I hope it will help for testing. By the way, Would there be a need for a way to install such extensions at runtime? It may be difficult to cover all of extensions so I think it would be useful for user to write install script and let this image to load / run it on startup. -- Reply to this email directly or view it on GitHub: #173 (comment) You are receiving this because you authored the thread. Message ID: ***@***.***>

[th-2021]

Here is my Dockerfile for immich:

ARG VERSION=16

FROM  kkimurak/sameersbn-postgresql:$VERSION

RUN apt-get update && apt-get install -y git make gcc postgresql-server-dev-16 wget

# Install pgvector extension
RUN git clone https://github.com/pgvector/pgvector.git /tmp/pgvector && \
    cd /tmp/pgvector && \
    make && \
    make install && \
    rm -rf /tmp/pgvector \
    apt-get purge  build-essential postgresql-server-dev-16 && \
    apt-get autoremove && \
    apt-get clean

RUN wget https://github.com/tensorchord/VectorChord/releases/download/0.3.0/postgresql-16-vchord_0.3.0-1_amd64.deb

RUN apt install ./postgresql-16-vchord_0.3.0-1_amd64.deb
    # Add the extension to the PostgreSQL configuration
RUN echo "shared_preload_libraries = 'vchord.so'" >> /usr/share/postgresql/16/postgresql.conf.sample

[th-2021]

immich document:

'CREATE EXTENSION IF NOT EXISTS ${extension} CASCADE'
                                                                              -----------

[kkimurak]

Ad 2) couldn't we leave the env var as is and just change the create extension in the script and add cascade?

For vchord I have a Dockerfile, which extends the postgresql one. This is imho the easiest way for adding code. It can be a deb package or a github address...

Then it can be done by just applying kkimurak/docker-postgresql@aa9438d (with some update to documents). DB_EXTENSIONS will be truly treated as a comma-separated string, i.e. you can write vchord CASCADE.

I will submit a PR to this repository and mine (kkimurak/docker-postgresql`) for clarify, and going to include in next regular release. If necessary, irregular releases are also possible.

[th-2021]

No need for special release. Thanks.

[kkimurak]

@th-2021 I have submitted pull request #175 (and optional #174 and #176). I have already applied these changes to my fork image kkimurak/sameersbn-postgresql (release 20250717) so you can test it.

I have confirmed that the image built with Dockerfile you posted (#173 (comment)) successfully launch by following command:

$ docker run --rm --env 'DB_EXTENSION=btree_gist,pg_trgm,vchord CASCADE' --env 'DB_NAME=test' --env 'DB_USER=test_user' --env 'DB_PASS=password' kkimurak/sameersbn-postgresql:16-vchord

[th-2021]

@kkimurak I verified in my environment and it's all working. Thank you