Migrate Immich helm chart deployment to CloudNativePG

Deploy a cnpg operator and a one-node postgres cluster

See also immich-app/immich-charts#149 (comment)

# Re-use the password from the source postgres instance.
PASSWORD=$(kubectl -n immich get secrets immich-postgres-user -o yaml | grep ' password:' | awk '{print $NF}' | base64 -d)
echo "immich@postgres password is: $PASSWORD"
# Create an opaque Kubernetes secret with the generated password
kubectl create namespace cnpg
kubectl -n cnpg create secret generic immich-postgres-user --from-literal=password=$PASSWORD

# Install cnpg chart: https://cloudnative-pg.io/documentation/1.24/installation_upgrade/#using-the-helm-chart
helm repo add cnpg https://cloudnative-pg.github.io/charts
helm repo update
helm upgrade --install cnpg \
  --namespace cnpg \
  --create-namespace \
  --set config.clusterWide=false \
  cnpg/cloudnative-pg

# Start an immich postgres instance with a backup to S3.
# The object is based on https://github.com/immich-app/immich-charts/issues/149#issuecomment-2555588331
cat <<'EOF' | kubectl -n cnpg apply -f -
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: immich
  namespace: cnpg
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
  # A bug was reported in v0.3.0, which does not allow a replicated setup:
  # https://github.com/immich-app/immich-charts/issues/149#issuecomment-2559012759
  instances: 1

  postgresql:
    shared_preload_libraries:
      - "vectors.so"

  # The "postgres" superuser is needed only for import and it can be deactivated after migration.
  enableSuperuserAccess: true
  # superuserSecret: immich-superuser

  managed:
    roles:
      - name: immich
        superuser: true
        login: true
        passwordSecret:
          name: immich-postgres-user

  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: 8Gi
    storageClass: rook-ceph-block

  # Optional "live" backup. Remove if not needed.
  backup:
    barmanObjectStore:
      destinationPath: "s3://cnpg-immich-backup-bucket-name"
      endpointURL: "https://minio.example.com"
      s3Credentials:
        accessKeyId:
          name: s3-immich
          key: ACCESS_KEY_ID
        secretAccessKey:
          name: s3-immich
          key: ACCESS_SECRET_KEY
      data:
        compression: bzip2
      wal:
        compression: bzip2
    retentionPolicy: "30d"
EOF

# Create a backup schedule (if needed).
cat <<'EOF' | kubectl -n cnpg apply -f -
apiVersion: postgresql.cnpg.io/v1
kind: ScheduledBackup
metadata:
  name: immich
spec:
  immediate: true
  schedule: "11 12 3 * * *"
  backupOwnerReference: self
  cluster:
    name: immich
EOF

Migrate data from the old postgres

One option would be to use the "externalCluster" import feature of CloudNativePG.

See an example howto recently posted on the github issue.

An alternative approach would be to run pg_dumpall and psql restore commands separately. This way, a "search_path" workaround can be applied, as shown below.

# Test that the passwords and IPs can be extracted from k8s.
kubectl -n immich get secrets immich-postgres-user -o yaml | grep ' password:' | awk '{print $NF}' | base64 -d
kubectl -n cnpg get secrets immich-superuser -o yaml | grep ' password:' | awk '{print $NF}' | base64 -d
kubectl -n immich get svc immich-postgresql -o jsonpath='{.spec.clusterIP}'
kubectl -n cnpg get svc immich-rw -o jsonpath='{.spec.clusterIP}'

# Shutdown the immich-server.
kubectl -n immich scale deployment immich-server immich-machine-learning --replicas=0

# Dump the source database. Here, "podman" can be replaced with "docker".
podman run -ti --rm -v ./backup/:/backup \
  -e PGPASSWORD="$(k -n immich get secrets immich-postgres-user -o yaml | grep ' postgres-password:' | awk '{print $NF}' | base64 -d)" \
  ghcr.io/tensorchord/cloudnative-pgvecto.rs:16.5-v0.3.0 \
  pg_dumpall --database=immich --host="$(kubectl -n immich get svc immich-postgresql -o jsonpath='{.spec.clusterIP}')" \
    --username=immich --superuser=immich --no-role-passwords --clean --if-exists --file=/backup/immich.pg_dumpall.sql

# See https://immich.app/docs/administration/backup-and-restore/#manual-backup-and-restore
sed "s/SELECT pg_catalog.set_config('search_path', '', false);/SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true);/g" < backup/immich.pg_dumpall.sql > backup/immich.pg_dumpall.sql.sed

# Import the dump into the target DB.
# The trick is to connect with "postgres" user, to be able to drop "immich" database during import.
podman run -ti --rm -v ./backup/:/backup \
  -e PGPASSWORD="$(k -n cnpg get secrets immich-superuser -o yaml | grep ' password:' | awk '{print $NF}' | base64 -d)" \
  ghcr.io/tensorchord/cloudnative-pgvecto.rs:16.5-v0.3.0 \
  psql --host="$(kubectl -n cnpg get svc immich-rw -o jsonpath='{.spec.clusterIP}')" --username=postgres --file=/backup/immich.pg_dumpall.sql.sed -v ON_ERROR_STOP=1  |& tee psql.log

grep -i error psql.log && echo "restore operation was not successful"

# If something went wrong, delete all data owned by "immich" and repeat.
podman run -ti --rm \
  -e PGPASSWORD="$(k -n cnpg get secrets immich-superuser -o yaml | grep ' password:' | awk '{print $NF}' | base64 -d)" \
  ghcr.io/tensorchord/cloudnative-pgvecto.rs:16.5-v0.3.0 \
  psql --host="$(kubectl -n cnpg get svc immich-rw -o jsonpath='{.spec.clusterIP}')" --username=postgres \
    -c "REASSIGN OWNED BY immich TO postgres; DROP OWNED BY immich;"

If the logical backup cannot be restored for any reason, then there is an option to import a physical backup. Simply copy the PVC contents from source to target DB. For example, stop both databases, attach the database PVCs to some pods and use "kubectl cp" to download the postgres data folder from source server and upload it to the target cnpg PVC. Make sure to use the correct securityContext.fsGroup value of 26 on the target DB pod.

Finally, update immich/values.yaml, change DB_HOSTNAME env from {{ .Release.Name }}-postgresql to immich-rw.cnpg and redeploy the immich helm chart.