Deprecate postgres subchart

[bo0tzz]

As of chart version 0.9.0, the postgres subchart will be deprecated. In 0.10.0 it will be removed entirely. See #129 for the reasoning and discussion.

If you're reading this issue, you probably came here because trying to install the chart gave you an error message with a link. This is your signal to look into a different way of running Postgres for Immich, that doesn't depend on this chart directly. We recommend using an operator with an image like https://github.com/tensorchord/cloudnative-pgvecto.rs or https://github.com/chkpwd/cdpgvecto.rs. More details on possible configurations will be added to the README file later.

If you want to bypass the deprecation warning and install with the deprecated postgres chart anyways, you can do that by setting useDeprecatedPostgresChart: true in the values. Be very careful if you do that, because in chart version 0.10.0 that postgres instance will be deleted entirely without any warning.

TBD before full removal

Preview Give feedback

1. Add a list of postgres deployment options to README.md
2. Add a check to checks.yaml to ensure the chart is configured with a postgres instance

[prometheanfire]

I think specifying that we need 0.2.1 for the pgvecto.rs extension would be helpful (iirc that's still the version required...).

It also looks like we'll (end users) will have to build containers for if we want up to date versions of psql-server, but that's no change (previously was just using the old published image iirc).

[andredasilvapinto]

Is there a documented migration path for people currently using the postgres subchart so we don't lose the current data?

[bo0tzz]

I think specifying that we need 0.2.1 for the pgvecto.rs extension would be helpful

Yes, I intend to add some more detail to the README about the options for deploying postgres.

we'll (end users) will have to build containers for if we want up to date versions of psql-server

I'm not quite sure what you mean by this?

Is there a documented migration path

Not specifically. It'll depend on what method you choose for running postgres going forward. For example, operators such as CNPG have an option to import from an existing database: https://cloudnative-pg.io/documentation/1.24/database_import/, or if you run a plain postgres you can do a backup/restore just like with the usual docker compose setup: https://immich.app/docs/administration/backup-and-restore.

[prometheanfire]

I was just looking at the published containers for pgvecto.rs and noticed that any that specified 0.2.1 are 9 months old. https://hub.docker.com/r/tensorchord/pgvecto-rs/tags?name=0.2.1

[nadiamoe]

In case someone wants to go the cloudnative-pg route I actually did that ~a month ago, so I think it might be useful to share what I ended up with after couple hours troubleshooting:

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: immich-postgres
spec:
  # At the time of writing, immich is only compatible with pgvecto.rs <0.4. Latest postgres image with that version is 16.5.
  imageName: ghcr.io/tensorchord/cloudnative-pgvecto.rs:16.5-v0.3.0@sha256:be3f025d79aa1b747817f478e07e71be43236e14d00d8a9eb3914146245035ba
  instances: 1

  postgresql:
    shared_preload_libraries:
      - "vectors.so"

  managed:
    roles:
      - name: immich
        superuser: true
        login: true

  bootstrap:
    initdb:
      database: immich
      owner: immich
      secret:
        name: immich-postgres-user
      postInitSQL:
        - CREATE EXTENSION IF NOT EXISTS "vectors";
        - CREATE EXTENSION IF NOT EXISTS "cube" CASCADE;
        - CREATE EXTENSION IF NOT EXISTS "earthdistance" CASCADE;

  storage:
    size: 4Gi
    storageClass: immich-postgres

That, and migrate the data by running pgdump and then piping the output to psql using the immich user referenced above.

The critical part being making immich a superuser. Otherwise, it's lots of trouble down the line trying to import a pgdump which wants to create extensions, or immich to do the same. Theoretically you should be able to create the extensions in postInitSQL, but that did not work for me (they were invisible to the unprivileged user).

[R-Nabil]

Hey,

I understand the move, completely. But it might leave some users (like me) in a hard spot. I fully switched to Immich (ie, I've removed my photos from elsewhere but don't worry I do keep backups). but Immich is my main way of viewing pictures now.

Would it be possible to walk us through a step by step of how to do the switch ?
thks

[bo0tzz]

I don't have a step-by-step because the details will depend quite a bit on your setup and how you choose to run postgres, and because if you opt to run a more advanced setup like Kubernetes you're somewhat expected to be able to handle this kind of thing yourself. In general though, you want to spin up a new postgres instance in whatever way you prefer and get the Immich data into it, either through the import that some operators support or with a backup-restore type procedure, and then configure Immich to use that new database.

[R-Nabil]

What I meant is a default step by step as a drop in replacement to minimise disruption.
Understand the assumption, but its not always true. I'm not well-versed in DB setup at all, or in what are the choices available or why choose one over the others. But I'm reasonably comfortable with K3S. It does not necessarily seem that uncommon but I might be wrong.

Just wanted to highlight that it might put some users in a not so easy spot, to have to solve something unexpected. If it is what it is, then so be it ofc.

[camrossi]

If it can help anyone here are the steps I followed to do the migration just now and it didn't explode ;)
Thanks to @nadiamoe as I copy pasted most of her config !

https://github.com/camrossi/home-cluster/blob/main/apps/immich/DB_Migration.md

[prometheanfire]

If it can help anyone here are the steps I followed to do the migration just now and it didn't explode ;) Thanks to @nadiamoe as I copy pasted most of her config !

camrossi/home-cluster@main/apps/immich/DB_Migration.md

This is awesome, I'm reading the CNPG docs right now to deploy it.

[prometheanfire]

It looks like a cnpg cluster won't work due to a bug only fixed in 0.4.0, so setting cluster size to 1 is required

tensorchord/pgvecto.rs#570

Also, for some reason envFrom for the password at least was not working well, I had to set env/envFrom to this to get the password populating in the env vars... maybe because I have lower case keys in the secret...

envFrom:
  - secretRef:
      name: immich-postgres-user
env:
  DB_PASSWORD:
    valueFrom:
      secretKeyRef:
        name: immich-postgres-user
        key: password
  DB_HOSTNAME: immich-postgres-rw.immich.svc

[kabakaev]

Yet another migration howto, largely based on the comments of this issue:
https://gist.github.com/kabakaev/1d8fa31d4e7fa8134c968101fa88d200

[nadiamoe]

Sharing some hiccups I found after my initial approach in #149 (comment).

Yesterday I noticed that immich search was not working, and erroring with:

[Nest] 12  - 12/26/2024, 1:11:39 PM   ERROR [Api:ErrorInterceptor~4s4f08ki] Database error: QueryFailedError: operator does not exist: vectors.vector <=> unknown

I solved this by manually running:

alter role immich set search_path TO "$user", public, vectors;

Where immich is the name of the immich user in PG and the "$user" string is literal.

I think this could be added in the CNPG manifest, perhaps in postInitSQL, but I have not tried it (it would require me to recreate the db, which I'm a bit lazy to do).

[bo0tzz]

this could be added in the CNPG manifest, perhaps in postInitSQL

This is what the README for the image suggests.

I intend to eventually provide a base manifest for CNPG that should work out of the box, but haven't had time to do comprehensive testing yet.

[prometheanfire]

One question I have is if we should remove the externalClusters section once we are "up and running".

[isZumpo]

I'm running into this error when trying to migrate with the initdb import approach:

"pg_restore: error: could not execute query: ERROR: type "earth" does not exist"

Any advice on how to easily solve it?

[R-Nabil]

I don't have a step-by-step because the details will depend quite a bit on your setup and how you choose to run postgres, and because if you opt to run a more advanced setup like Kubernetes you're somewhat expected to be able to handle this kind of thing yourself. In general though, you want to spin up a new postgres instance in whatever way you prefer and get the Immich data into it, either through the import that some operators support or with a backup-restore type procedure, and then configure Immich to use that new database.

Just to add a little bit more on that : Immich traditional docker install provides the instructions for DB deployment as well. So I can understand that this is a lot of work for one person to maintain an extra set of instructions for a helm chart. But would you be open to the suggestion of others trying to come up with a set of instructions ? Your help would be needed eventually to review confirm or highlight potential issues. Reason is, it's already obvious few people have tried and failed on this discussions. Meaning it might not be as simple as it looks.

It's a positive thing for all these users and for Immich if we could come up with something that makes it safer to migrate. The opposite would bring an undeserved negative experience towards using Immich in k8s

Btw its implicit but I can try to come up with those instructions.

[camrossi]

I hit another small issue, I was upgrading my K8s cluster and the cordon of the node was failing as the postgres pod wasn't being removed as there is a Pod Distribution Budget set. I have for now configured the

spec:
  enablePDB: false

To disable it and now I can cordon my nodes and immich went down for like a minute when the DB was finally moved to a different node. I am fine with this, just wondering however if you folks are also seeing this and if you run a single instance as well.

[rwlove]

I had a catastrophic cluster failure before I was able to migrate my database to CNPG. I am trying to import my immich database backup into CNPG, but the last command hangs. This is what I did:

1. gunzip immich-db-backup-1735542000019.sql.gz
2. sed "s/SELECT pg_catalog.set_config('search_path', '', false);/SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true);/g" immich-db-backup-1735542000019.sql > immich-db-backup.sql
3. kubectl -n databases exec -it postgres-16-1 -- pg_restore -U immich -Ce -f ./immich-db-backup.sql

The last command hangs with no command output.

[bo0tzz]

One question I have is if we should remove the externalClusters section once we are "up and running".

You can to tidy things up, but it's not required.

ERROR: type "earth" does not exist"

I believe (but am not entirely certain) this should be solved by the sed command in https://immich.app/docs/administration/backup-and-restore

immich went down for like a minute when the DB was finally moved to a different node.

Is this with CNPG? Because if you run multiple replicas the switchover should be pretty seamless (maybe a few seconds of downtime). If you're running a single pod, downtime when it reschedules is inevitable.

kubectl -n databases exec -it postgres-16-1 -- pg_restore -U immich -Ce -f ./immich-db-backup.sql

The last command hangs with no command output.

This command tries to access the ./immich-db-backup.sql file inside the context of the postgres pod, where it doesn't exist.

[rwlove]

kubectl -n databases exec -it postgres-16-1 -- pg_restore -U immich -Ce -f ./immich-db-backup.sql

The last command hangs with no command output.

This command tries to access the ./immich-db-backup.sql file inside the context of the postgres pod, where it doesn't exist.

Ah, yes. I kubectl cp'd the file into the postgres container, but still no luck. I created a support thread in discord.

[Kras4ooo]

Based on this PR: immich-app/immich#6785, does this mean we will be able to use pgvector instead of pgvecto.rs? If so, can we utilize the already installed extension mentioned here: https://docs.crunchybridge.com/extensions-and-languages (I’m sharing this link because I’m using Crunchy Postgres)? In other words, can we migrate without installing pgvecto.rs and instead use pgvector? Am I understanding this correctly?

Found it after my first part of the comment: It seems that the migration process is not possible: immich-app/immich#7296 (reply in thread) But for my use case, I will be able to create the Immich from scratch and migrate the date I after that :)

[krohrsb]

If it can help anyone here are the steps I followed to do the migration just now and it didn't explode ;) Thanks to @nadiamoe as I copy pasted most of her config !

https://github.com/camrossi/home-cluster/blob/main/apps/immich/DB_Migration.md

Thanks for this. It worked generally speaking. I had to dig up how to tweak initdb for disabling huge pages for the cloudnative-pg config (for my cluster/node setup).

I ended up with (mixed in with the rest)

  postgresql:
    parameters:
      huge_pages: "off"
  bootstrap:
    initdb:
      options: ['--set', 'huge_pages=off']

[etokheim]

Yet another migration howto, largely based on the comments of this issue: https://gist.github.com/kabakaev/1d8fa31d4e7fa8134c968101fa88d200

Thanks, @kabakaev - your detailed Gist really helped me out! I'm ashamed to say I still used three days fixing this... Would greatly appreciate more official documentation.

[c0depool]

Yet another migration howto, largely based on the comments of this issue: https://gist.github.com/kabakaev/1d8fa31d4e7fa8134c968101fa88d200

Thanks @kabakaev - I like the approach of running the database import separately using pg_dumpall and psql so that the "search_path" workaround is also applied. Created a k8s job manifest based of your approach for easy migration - immich-db-restoration-job.yaml.

[justutkarsh]

I ran the detailed gist and everything is up but now. But I get this if anybody faced this while using explore, rest everything works !

[Nest] 17 - 01/09/2025, 5:45:29 PM ERROR [Api:ErrorInterceptor~cmd8e7bu] Database error: QueryFailedError: column "tags" does not exist
[Nest] 17 - 01/09/2025, 5:45:29 PM ERROR [Api:ErrorInterceptor~uipoge6f] Database error: QueryFailedError: column "tags" does not exist
[Nest] 17 - 01/09/2025, 5:45:35 PM ERROR [Api:ErrorInterceptor~3un9as79] Database error: QueryFailedError: column AssetEntity__AssetEntity_smartInfo.assetId does not exist

Edit:
was able to fix this error using in case somebody benefits

BEGIN;

-- First create the smart_info table
CREATE TABLE IF NOT EXISTS smart_info (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    "assetId" UUID NOT NULL,
    tags TEXT[] DEFAULT '{}',
    objects TEXT[] DEFAULT '{}',
    "createdAt" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_smart_info_asset FOREIGN KEY ("assetId") REFERENCES assets(id) ON DELETE CASCADE
);

-- Now create the index
CREATE INDEX IF NOT EXISTS idx_smart_info_asset_id ON smart_info("assetId");

-- Create the view that the application is expecting
CREATE OR REPLACE VIEW asset_entity__asset_entity_smart_info AS
SELECT 
    a.id as asset_id,
    si.id as smart_info_id,
    si."assetId"
FROM assets a
LEFT JOIN smart_info si ON a.id = si."assetId";

COMMIT;

[esomore]

If you are like me and ended up manually migrating the data and now you need to configure the helm chart to inject the DB vars from the secret created by cnpg you can do this as follows in the values.yaml:

env:
  DB_HOSTNAME:
    secretKeyRef:
      name: immich-app
      key: host
  DB_USERNAME:
    secretKeyRef:
      name: immich-app
      key: user
  DB_DATABASE_NAME:
    secretKeyRef:
      name: immich-app
      key: dbname
  DB_PASSWORD:
    secretKeyRef:
      name: immich-app
      key: password

[tonydlo]

Yet another migration howto, largely based on the comments of this issue: https://gist.github.com/kabakaev/1d8fa31d4e7fa8134c968101fa88d200

Thanks for this.

For the restore part, I had no problems following Immich's own instructions on: https://immich.app/docs/administration/backup-and-restore/

• NOTE: You may want to force Immich to trigger a backup before you proceed, in case anything has changed since the nightly backup. Instructions also on that Immich page.
• Copy the latest backup file from the Immich server PVC (/usr/src/app/upload/backups) to the new postgres PVC (/var/lib/postgresql/data)
  • There are various ways to do that, but probably the easiest is a kubectl cp... if you don't have direct access to the PVC file system
• Shut down all the deployments/statefulsets (except the new Immich postgres server)
• Execute the following in the postgres server pod where you copied the backup to (just a tweak from their restore command):

gunzip < "immich-db-backup-1737252000004.sql.gz" \
| sed "s/SELECT pg_catalog.set_config('search_path', '', false);/SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true);/g" \
| psql --username=postgres 

• The username would be whatever you are using for the superuser, and obviously your backup filename will be different
• Import was completed successfully (it starts by dropping the database to make sure the restore is clean)
• Don't forget to delete the backup file as it's no longer of use

After altering the DB_HOST, DB_USERNAME and DB_PASSWORD in my values.yaml and commenting out the entire postgres section, Immich came right back up with no issues.

BTW, you can create the postgres cluster anywhere, so instead of placing it with the cnpg operator namesspace, I think it makes more sense to actually put it in the immich namespace, that way it's simple for Immich to access and for you to create any extra NetworkPolicy on top.

[weiyentan]

@nadiamoe you don't know helpful this is. Was trying to use cnpg and hitting against a brick wall. this helped a lot

i owe you a beer!

In case someone wants to go the cloudnative-pg route I actually did that ~a month ago, so I think it might be useful to share what I ended up with after couple hours troubleshooting:

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: immich-postgres
spec:

At the time of writing, immich is only compatible with pgvecto.rs <0.4. Latest postgres image with that version is 16.5.

imageName: ghcr.io/tensorchord/cloudnative-pgvecto.rs:16.5-v0.3.0@sha256:be3f025d79aa1b747817f478e07e71be43236e14d00d8a9eb3914146245035ba
instances: 1

postgresql:
shared_preload_libraries:
- "vectors.so"

managed:
roles:
- name: immich
superuser: true
login: true

bootstrap:
initdb:
database: immich
owner: immich
secret:
name: immich-postgres-user
postInitSQL:
- CREATE EXTENSION IF NOT EXISTS "vectors";
- CREATE EXTENSION IF NOT EXISTS "cube" CASCADE;
- CREATE EXTENSION IF NOT EXISTS "earthdistance" CASCADE;

storage:
size: 4Gi
storageClass: immich-postgres
That, and migrate the data by running pgdump and then piping the output to psql using the immich user referenced above.

The critical part being making immich a superuser. Otherwise, it's lots of trouble down the line trying to import a pgdump which wants to create extensions, or immich to do the same. Theoretically you should be able to create the extensions in postInitSQL, but that did not work for me (they were invisible to the unprivileged user).

[sdwilsh]

For better or worse, I use zalando/postgres-operator in my cluster, and the process was similar, but different compared to cnpg. In case someone else uses it as well, I wanted to document how I got it working.

Creating the database is pretty straightforward, with only minor complications:

• spilo doesn't include vecto.rs as an extension, but luckily @geraldwuhoo maintains a fork with minimal changes that we can reference by setting the dockerImage in the cluster manifest to registry.wuhoo.xyz/jerry/spilo-pgvecto.rs:spilo-3.3-p2-pgvectors-0.3.0.
• I found that the reverse goecoder at startup will OOM the database with the default memory limits, so I bumped it up to 4Gi in the cluster manifest. It's probably too much, but I didn't want to figure out a better value.
• Immich uses a non-standard sslmode, so the DB_URL must end in sslmode=require&sslmode=no-verify. This is documented, but it's non-obvious because require is supposed to not verify anyway.

Migrating data largely followed the documentation to get the data, but it was more complicated to write it back.

1. Get the current data: kubectl -n immich exec -it pod/immich-postgresql-0 -- pg_dumpall --clean --if-exists --username=immich | gzip > dump.sql.gz
2. Copy data into the new database pod: kubectl cp dump.sql.gz immich/immich-14-pg-0:dump.sql.gz
3. We need to load the extension with psql -U postgres -c 'ALTER SYSTEM SET shared_preload_libraries = "vectors.so"'
4. Restart the database so the shared library is loaded: patronictl restart immich-14-pg
5. Create the extensions in the database:
   • psql -U postgres -c 'CREATE EXTENSION IF NOT EXISTS "vectors"'
   • psql -U postgres -c 'CREATE EXTENSION IF NOT EXISTS "cube" CASCADE'
   • psql -U postgres -c 'CREATE EXTENSION IF NOT EXISTS "earthdistance" CASCADE'
6. Inject the data: gunzip < dump.sql.gz | sed "s/SELECT pg_catalog.set_config('search_path', '', false);/SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true);/g" | psql -U postgres -v ON_ERROR_STOP=1
   • This will fail attempting to drop postgres because Patroni has a heartbeat that I don't know if it's even possible to stop.
7. Now to fix the things that failed in (7): psql -U postgres, and run these lines:

ALTER DATABASE postgres OWNER TO immich;

\connect postgres

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off

The full set of changes I made can be seen in sdwilsh/ansible-playbooks@8858c83.

[bpetrikovics]

I think the correct order would be

• First updating the chart with proper documentation on how to properly provide an external postgres
• Document a migration procedure
• Only then deprecating it.