Installing extensions on postgreSQL docker container

[Flatbed0563]

Hi all,

 

I currently have an Immich container, which uses a separate PostgreSQL database in the backend.

Due to a recent update, the server does not boot up because it tries to use the vectors extension in PostgreSQL, which is not installed.

 

When looking up how to install extensions, the results mostly say I should console into the docker, clone the git repo and install it, but I have some questions whether this is the clean way of doing it.

 

I always thought that everything done in the docker console command line is a sandboxed environment installed from the docker image, so if I update my docker container in the future, will it also remove the extension I installed with the command line?

 

I guess the extensions also need to be updated, so if I install it via the container console, I would probably have to manually update it as well?

Is there a way to embed this in the container config so that it will install and update the extension at startup of the container?

 

This is the step plan I found:

console in the PostgreSQL container and install tools needed to install the extension:

apk add --no-cache git build-base clang llvm15-dev llvm15

 

clone the git repo of the extension in a temp folder

cd /tmp
git clone --branch v0.5.1 https://github.com/pgvector/pgvector.git
cd pgvector

 

compile and install the extension

make
make install

 

after this, the extension should be installed and ready to use in Immich, as that container installs the extension on the database itself on startup

 

is this correct?

[Tunatron]

I'm looking to solve the same problem.  You had any luck with this?

[captainfeeny]

I ended up just changing the repository on the postgresql14 docker to: tensorchord/pgvecto-rs:pg14-v0.1.11. Then I restarted immich and all is good. See attached. 

The repository info was from here: https://github.com/imagegenius/docker-immich

[tmoran000]

I did this an now immach says I have 49k unmatched images and nothing shows

[CapriciousCrawdad]

Info about this change can be found at this issue on the imagegenius github page.

The suggested fix worked for my instance. I'm up and running again without any apparent issues.

Edited December 17, 2023 by CapriciousCrawdad

[Signet]

@captainfeeny youre awesome! Just what i was looking for too. The docker image of immich mentions postgres 15 too... 

[MKI]

15 hours ago, captainfeeny said:

I ended up just changing the repository on the postgresql14 docker to: tensorchord/pgvecto-rs:pg14-v0.1.11. Then I restarted immich and all is good. See attached. 

The repository info was from here: https://github.com/imagegenius/docker-immich

this worked, thank you

[talmania]

Thank you for the link and this topic!  I updated and noticed that Immich was broken and after trying to figure out how to add vectors to the postgresql15 DB (and failing) I found this thread.  @captainfeeny link worked perfectly using the postgresql15 repo.

[linuxdevil]

Thanks, worked for me as well.

[Clockwork Elf]

@talmania did you use tensorchord/pgvecto-rs:pg14-v0.1.11 as repository for postgres15 ? The container doesnt start for me after i change it

[Watever]

Thank you ! This fixed my issue !

[DrMac]

changing to tensorchord/pgvecto-rs:pg15-v0.1.11 fixed my first time install issues also.

After I uploaded some pictures it stops and now my postgres log shows this:

 

2023-12-25 03:46:59.273 CST [925] FATAL:  could not open file "global/pg_filenode.map": No such file or directory
2023-12-25 03:47:03.827 CST [66] LOG:  checkpoint starting: time
2023-12-25 03:47:03.944 CST [66] PANIC:  could not open file "pg_commit_ts": No such file or directory
2023-12-25 03:47:03.950 CST [1] LOG:  checkpointer process (PID 66) was terminated by signal 6: Aborted
2023-12-25 03:47:03.950 CST [1] LOG:  terminating any other active server processes
2023-12-25 03:47:03.956 CST [1] LOG:  all server processes terminated; reinitializing
2023-12-25 03:47:03.978 CST [1] PANIC:  could not open file "global/pg_control": No such file or directory

** Press ANY KEY to close this window ** 

[Carbur8tr]

Just more data, I've tested on 15 with a few of the suggested solutions and I am still finding that 14 is the only solution to which is stable.

 

I'm either missing pictures or indexing fails in some form or fashion on 15.

[Flatbed0563]

On 12/28/2023 at 3:41 PM, Carbur8tr said:

Just more data, I've tested on 15 with a few of the suggested solutions and I am still finding that 14 is the only solution to which is stable.

 

I'm either missing pictures or indexing fails in some form or fashion on 15.

 

hmmm, started from 15, and switching to the  tensorchord/pgvecto-rs:pg15-v0.1.11 seems to have worked seamlessly for me actually, at least no errors in indexing, and the image counts in the stats seem to be the same as before the switch...

[DrMac]

I switched to 14 and it worked great for 2 days.

Maybe i'll look into this in a few years when its stable.

[cmahesh7]

On 12/21/2023 at 6:37 AM, Clockwork Elf said:

@talmania did you use tensorchord/pgvecto-rs:pg14-v0.1.11 as repository for postgres15 ? The container doesnt start for me after i change it

for postgres15 change repo to tensorchord/pgvecto-rs:pg15-v0.1.11. this worked for me.

[Cirion]

On 12/25/2023 at 8:01 PM, DrMac said:

changing to tensorchord/pgvecto-rs:pg15-v0.1.11 fixed my first time install issues also.

After I uploaded some pictures it stops and now my postgres log shows this:

 

2023-12-25 03:46:59.273 CST [925] FATAL:  could not open file "global/pg_filenode.map": No such file or directory
2023-12-25 03:47:03.944 CST [66] PANIC:  could not open file "pg_commit_ts": No such file or directory

I'm getting the same error from postgres14 trying 15 now.

I manage to import my complete library 1.3TB with transcoding to 1080P HEVC with Nvidia on videos. And it works afterwards, but fist nightrun and postgres panics and shuts down and I do not get it to start up again. I get the same messages.

[Cirion]

Think I might have figured this one out with a little help om the Immich discussions.

The postgres template uses a path that is:
/mnt/cache/appdata/postgresql14

 

Whenever the mover runs, it moves the files to:
/mnt/user/appdata/postgresql14

 

After the mover does it's thing, the file is missing 🤪

 

So the path on my docker should not be on the cache drive...

I switched and testing, but pretty confident this is the solution 😁

 

Here is the discussion:

https://github.com/immich-app/immich/discussions/6680

Edited January 29 by Cirion
Added link

[Cirion]

Yes, that fixed it for me 😁

[plv8]

Actually I found updated docker image PLV8 and it works with chips M1, M2. Officially this file published on the dockerhub - https://hub.docker.com/r/sibedge/postgres-plv8/tags

but they also have their own site https://plv8.com/

Edited March 25 by plv8