Hello,

I recently updated the Immich_PostgreSQL database container from Unraid Community Applications

Directly after that, I needed to restore the database [and reinstall immich] due to swapping a hardware component and breaking some networking.

Postgres did a major version update recently, [to 18] and my last database dump was from a previous version. Following the restore documentation on immich gets me the following errors.

Immich_postgres logs report that I need both! versions of postgres installed to upgrade the database.

What is the simplest way to determine the correct postgres version to use to restore the database, or, failing that, to upgrade the database dump?

From what I understand about the error, I should have updated both immich and the postgres container before swapping hardware, and triggered a manual database dump in case restoring an install became necessary.

I understand this is a basic question, but I can't seem to wrap my head around docker and postgres in this instance.

Attempted to install a previous version of the immich postgres repository

This failed to work, sadly.

Is the correct way to solve this installing a previous version of Immich_Postgresql, restoring the database, and then reinstalling immich as normal? Or am I screwed in this instance?

Thanks for your time and expertise,

Iakovos_

Due to postgress and immich coniter to sett eh postgress tag version. you must specfi the coret docker major location.

FYI Postgres v18 and up issues.

so postgress docker v18 needs different docker varables to run

PGDATA

Quote

Important Change: the PGDATA environment variable of the image was changed to be version specific in PostgreSQL 18 and above⁠. For 18 it is /var/lib/postgresql/18/docker. Later versions will replace 18 with their respective major version (e.g., /var/lib/postgresql/19/docker for PostgreSQL 19.x). The defined VOLUME was changed in 18 and above to /var/lib/postgresql. Mounts and volumes should be targeted at the updated location. This will allow users upgrading between PostgreSQL major releases to use the faster --link when running pg_upgrade and mounting /var/lib/postgresql.

Users who wish to opt-in to this change on older releases can do so by setting PGDATA explicitly (--env PGDATA=/var/lib/postgresql/17/docker --volume some-postgres:/var/lib/postgresql). To migrate pre-existing data, adjust the volume's folder structure appropriately first (moving all database files into a PG_MAJOR/docker subdirectory).

Quote

Important Note: (for PostgreSQL 17 and below) Mount the data volume at /var/lib/postgresql/data and not at /var/lib/postgresql because mounts at the latter path WILL NOT PERSIST database data when the container is re-created. The Dockerfile that builds the image declares a volume at /var/lib/postgresql/data and if no data volume is mounted at that path then the container runtime will automatically create an anonymous volume⁠ that is not reused across container re-creations. Data will be written to the anonymous volume rather than your intended data volume and won't persist when the container is deleted and re-created.

This (PGDATA) is an environment variable that is not Docker specific. Because the variable is used by the postgres server binary (see the PostgreSQL docs⁠), the entrypoint script takes it into account.

https://github.com/immich-app/immich/issues/23663

I have updated the CA for v18 moving forwards to fix issues. and set the app data path back to one so docker tag changes may show a 18 folder or 19 depending on tag...

you will need to edit the docker appdata container location for tag version 18

/var/lib/postgresql/18/docker

unraiod also don'et support nor have gunzip installed its a 3rd party application that can be retrived via un-get in nerdtools...
https://docs.immich.app/administration/backup-and-restore/

unget plugin for slack 3rd party packages...
https://forums.unraid.net/topic/129200-plug-in-nerdtools/page/25/#findComment-1513524

un-get install gzip

https://slackware.pkgs.org/15.0/slackware-patches-x86_64/gzip-1.12-x86_64-1_slack15.0.txz.html
Binary for extra folder https://slackware.uk/slackware/slackware64-15.0/patches/packages/gzip-1.12-x86_64-1_slack15.0.txz

Edited December 2, 2025Dec 2 by bmartino1

Thanks for your reply, I understand a bit more now.

I modified the appdata location and moved the database file appropriately.

Is it possible to upgrade a dump made by postgres 16 to version 18?

It seems to restore the database properly and have dump compatible with pg 18, I would need to restore the DB in postgres 16, then upgrade to postgres 18 and trigger a manual dump.

Initializing the postgresql database now returns the following error, as my last dump was made in pg 16.

To downgrade to pg 16, is it correct I need to edit the repository for the immich_postgresql docker container, likely to [16-vectorchord0.4.3-pgvectors0.3.0]

then temporarily readjust the docker appdata mount point to its previous location?

are there any other steps I need to take to restore this database?

Thanks again for your help,

Iakovos_

Edited December 3, 2025Dec 3 by Iakov_

Is it possible to upgrade a dump made by postgres 16 to version 18?

yes I have done this recenlty my self to trest teh image genis unraid CA form postgress v16 to v18.
When you restore form a backup your porvious immich postgress v16 data sucah as user name and passowrd are over writeen so you will need to maintin v16 credentals if you restore.

https://medium.com/@damngoodtech/how-to-easily-restore-a-postgresql-database-in-a-docker-container-23ccfaa3d60b

I would use a immich last backup sql file made. or use pgadmin a third party docker to connect ot hte database and dump its contents.

the goal is to unziip/extract the files inteh zip/tar etc to get to you sql file

One can then follow guide:
https://davejansen.com/how-to-dump-and-restore-a-postgresql-database-from-a-docker-container/

I then to use a docker varable and samba to use 7zip to extract the archived backup to get my sql file then add a tempatle edit and docker varbale to pass the file into the continer...

then i would use unriad docker webui to console conect to get a termainl windows of the docker container and run the comands pointing at the file I passwed in the folder...

There is no one size fits all and many way to acomplish this.

as the goal is to excute the sql file in the postgress database tag version you want.

so I would 7zip extract my lattest backup



so I have a file

I would then clear and resetup a new postgress databse

double check the docker tag

and contner due to pgdata changees with postgress v18


and add the docker pat varble to add my folder for immich...


this way when I console in the docker I see imchi backup form the postgress continer
I would leave this default as this is my login credentals for the restore command.



once postgress v16 sql is restored the password and user will revert to my v16 data.

IT IS BEST TO KEEP AND MAINTIN THE SAME DB NAME/ USER / PASSOWoED!!!!

I then apply the v18 immich_postgress form the CA --which is setup for v18 atm.

then wait until postgress makes its database...

now i'm setup with a blank postgress ready to restore my sql file...

the v18 tag require the continer var to be
/var/lib/postgresql/18/docker/

Per the log error:
since this is v18 i need to add 18/docker

AS I don't have a good way to share the CA template due to the postgress change of a default pgdata location...

once postgress is up we can console into it and run the pg restore command...
my sql file is now in /backup pwer my earlier add container

I see that my temaplte on the CA failed I will be updateing it shortly to fix this by forcing v18 and v19 newer tags to use pgdata to odler /var/postgres /data folder

Edited December 3, 2025Dec 3 by bmartino1

appologies I thought I made the template edit to fix this i see i copied the worng one to my github repo

the CA will be updated later the correct temapte is in that fixes and sets the /data folder for all Postgres moving off tag 16 to higher tags due to changes in Postgres.

you will still need to add a folder location to access the backup dump of your v16
you may need to delete the appdat folder for a fresh download of the template.

Thank you for bringing this to my attention.

After making the changes to the postgres container, it seems like its ready to go.

Wasn't the default database called "postgres" in earlier verisons? should be ok, just checking.

Next step is running the pg_restore command, within the postgres container command line, correct? I used the below syntax, but nothing happened. No output, no errors from the postgres container terminal

pg_restore --filename=immich-db-backup-20251201T020000-v2.3.1-pg16.10.sql

I also tried it with the file still in archive format, nothing. Seems a lot easier to restore from the same verision of postgres, rather than across major verisions.

I'm learning a decent bit, but still, I plan on making sure to only restore after a new db dump for that version is completed.

I unzipped the file and added it to backup path specifically for restoring the file that you mentioned. Still nothing.

If I understand correctly, the high-level overview is as follows: delete appdata folder, reinstall the postgres container, ensure volumes are mapped correctly, then run the pg_restore command to rebuild the database, then reinstall the immich container, restart both containers, then immich should be working?

Ive done up to mapping volumes, but fail at the restore command.

Is there any other information I can provide to help with troubleshooting this?

Edited December 4, 2025Dec 4 by Iakov_

the db name was always customizable. No idea what the default was when I made the updated Postgres CA templates It was a near exact duplicate of space invaders so what ever he had as defaults was the shipped name.

Usually Postgres defaults with Postgres as a db name. Most use the application there using as some multi task 1 database with mutiple database systems. so 1 Postgres image for immich and on this Postgres instance they will have another database called net probe for the netprobe stack and xyz for that stack... while others will run mutule of the same with only 1 database...

in your case, its whatever the sql file has. And what the original docker at creation made.

you can edit the sql file in notepad and see the commnads and look for the database name. to get teh database name... IT more important at a resotre to infomr imich to conect ot the corect databse after the restore...

Hi, thank you very much for the assistance so far.

I ended up running psql as suggested by one of your links. The database restored!!!! Thank you!

In case it helps anyone, I successfully ran it as root in the terminal with this specific syntax: [docker exec -i Immich_PostgreSQL /bin/bash -c "PGPASSWORD=changeme psql --username postgres immich" < /mnt/user/appdata/immich/backups/immich-db-backup-20251201T020000-v2.3.1-pg16.10.sql]

Reminder that if it works in the container terminal its best to run it there.

It seems to have worked. Immich is back up, and user accounts, album and other metadata have been restored.

Now just to test uploading photos from the phone.

Thank you very much

Iakovos_