How to transfering postgres data from pg14 to pg18?

[modern folio]

I changed from a ubuntu server to trueNAS and setup a new immich app and want to transfer the data to this new immich instance. I moved all the images to the correct library location on the nas and created a backup with:

docker exec -t immich_postgres pg_dumpall -c -U postgres > immich_db_backup.sql

Then i tried to import this backup in the new postgres 18 container with:

cat immich_db_backup.sql | docker exec -i ix-immich-pgvecto-1 psql -U postgres -d immich

Only i got errors like

ERROR:  type "vectors.vector" does not exist
LINE 3:     embedding vectors.vector(512) NOT NULL
                      ^
ERROR:  relation "public.face_search" does not exist

and

ERROR:  relation "public.face_search" does not exist
backslash commands are restricted; only \unrestrict is allowed
ERROR:  syntax error at or near "pg_dump"
LINE 1: pg_dump: error: Dumping the contents of table "face_search" ...
        ^
ERROR:  syntax error at or near "pg_dumpall"
LINE 1: pg_dumpall: error: pg_dump failed on database "immich", exit...

How do i properly move the database?

The previous postgres image was:
ghcr.io/immich-app/postgres:14-vectorchord0.4.3-pgvectors0.2.0@sha256:bcf63357191b76a916ae5eb93464d65c07511da41e3bf7a8416db519b40b1c23

[old nebula]

:wave: Hey @modern folio,

Thanks for reaching out to us. Please carefully read this message and follow the recommended actions. This will help us be more effective in our support effort and leave more time for building Immich .

References

• Container Logs: docker compose logs docs
• Container Status: docker ps -a docs
• Reverse Proxy: https://immich.app/docs/administration/reverse-proxy
• Code Formatting https://support.discord.com/hc/en-us/articles/210298617-Markdown-Text-101-Chat-Formatting-Bold-Italic-Underline#h_01GY0DAKGXDEHE263BCAYEGFJA

Checklist

I have...

1. :ballot_box_with_check: verified I'm on the latest release(note that mobile app releases may take some time).
2. :ballot_box_with_check: read applicable release notes.
3. :ballot_box_with_check: reviewed the FAQs for known issues.
4. :ballot_box_with_check: reviewed Github for known issues.
5. :ballot_box_with_check: tried accessing Immich via local ip (without a custom reverse proxy).
6. :ballot_box_with_check: uploaded the relevant information (see below).
7. :ballot_box_with_check: tried an incognito window, disabled extensions, cleared mobile app cache, logged out and back in, different browsers, etc. as applicable

(an item can be marked as "complete" by reacting with the appropriate number)

Information

In order to be able to effectively help you, we need you to provide clear information to show what the problem is. The exact details needed vary per case, but here is a list of things to consider:

• Your docker-compose.yml and .env files.
• Logs from all the containers and their status (see above).
• All the troubleshooting steps you've tried so far.
• Any recent changes you've made to Immich or your system.
• Details about your system (both software/OS and hardware).
• Details about your storage (filesystems, type of disks, output of commands like fdisk -l and df -h).
• The version of the Immich server, mobile app, and other relevant pieces.
• Any other information that you think might be relevant.

Please paste files and logs with proper code formatting, and especially avoid blurry screenshots.
Without the right information we can't work out what the problem is. Help us help you ;)

If this ticket can be closed you can use the /close command, and re-open it later if needed.

[old nebula]

Successfully submitted, a tag has been added to inform contributors. :white_check_mark:

[brazen pagoda]

Importing the db isn't done like this:
https://docs.immich.app/administration/backup-and-restore/

Should be followed, however there are some things érequired" to be done on TrueNAS side (if you're using the app from TrueNAS catalog, and you already started the app, you'll have to clean the pgData dataset, then add a variable)

I'm also not fully sure if the db shouldn't be upgraded before of if importing straight to pg18 is fine.
@thick pulsar can you help me on this?

[thick pulsar]

You can import an older backup into a newer Postgres. Just not the other way around

In fact that’s typically how an upgrade is done under the hood.

[brazen pagoda]

Wasn't sure of it, thanks
And I didn't know that 😄

[thick pulsar]

With that said there is something very wrong with your backup file. In fact your backup file seems to contain an error output as the first line

“pg_dumpall error”

[modern folio]

Thanks for the reply. I have the old immich server running again. Now I made a new backup with

docker exec -t immich_postgres pg_dumpall --clean --if-exists --username=postgres | gzip > "/mnt/data/immich-backup-postgres/immich-app/immich-app/dump.sql.gz"

It showed no errors and i saw no error in the docker logs.

then i shut that server down and started the app closed all container except postgres because i don't know how to start it separately for a truenas app.

then i ran the command to import the dump and it showed the following given log file

root@truenas[...backup-postgres/immich-app/immich-app]# gunzip --stdout "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" \ | docker exec -i ix-immich-pgvecto-1 psql --dbname=immich --username=immich

It shows errors database cannot be dropped, vector errors, relation "public.face_search" does not exist" and more
I used the given docker-compose and .env file.

Do you know what the issue could be?

[brazen pagoda]

Yeah

As said in my first reply, you

1. Stop APP
2. Recreate the pgData dataset (needs to be "empty"
3. Edit the app and add the variable DB_SKIP_MIGRATIONS with the value true (as explained in the tip section of the link I shared
4. Save config, start the app
5. Run the command you shared

[modern folio]

I have tried to add the DB_SKIP_MIGRATIONS but it still starts the immich server and shows the same error when I am trying to restore the data. I have created a whole new immich instance with all data in the old postgres location deleted. Did i add the variable wrong?

[brazen pagoda]

Can you show the storage config and the dataset you're pointing your new immich instance to?

[modern folio]

I don't know what you would like to see but here are the two data sets

[brazen pagoda]

can you try to do, in the shell
rm -r /mnt/data/immich-postgres/*

then try to restore again?

Because I don't know if it gave you the exact same error (especially in the beginning of the full output) so I don't know where it could be "wrong"

[modern folio]

i ran the command and started the server and the postgres server now shows these errors

this is without me trying to import the old backup and it also shows the same missing face_index relation so that could be one of the problems but i don't know what is causing it

[brazen pagoda]

postgres serrver ?

Wdym by that?
the postgres container or the server one ?

[modern folio]

sorry the pgvecto container

[brazen pagoda]

I think it's ok

[brazen pagoda]

just here, in your restore command, dbname is "postgres" not "immich"

I didn't see that before, so that might be the fix

[modern folio]

okay from what is can see there are two problems one is that something is still connected preventing the drop of the database and vector does not exists.

[brazen pagoda]

Well, stop immich, delete the postgres dataset, recreate it, and start Immich again

barbarian method, but this will work

[modern folio]

i did that and it removed the not being able to drop the database but it still keeps the vectors and missing relations error.

[brazen pagoda]

which immich were you running on your ubuntu server?

[modern folio]

and when i look into the logs of immich trying to start that is creating a lot of errors

the latest immich i pulled was 2.3.0 or 2.3.1

[brazen pagoda]

holy I'm tired, you gave the info I wanted in your first message, sorry

[modern folio]

No problem and i am also tired and it is way to late so i will look at this again tomorrow with a fresh set of eyes. Thanks for all the help.

[frank merlin]

sup!

according to the .env you posted at the beginning, you have
DB_USERNAME=postgres
DB_DATABASE_NAME=immich

and yet this command you ran reversed the two, you put immich as the username and postgres as the dbname

try putting them back in the correct order (remember to delete the old broken stuff first like you've already been doing)

[brazen pagoda]

This doesn't seem to be that, there is a problem with the vector extension, I asked people from the team and support crew, the new image doesn't use it, I'm waiting for a confirmation on what you could do

[brazen pagoda]

The person is restoring to a different install (different dbname and username by default), so I don't think this matters, does it?

[frank merlin]

oh, I was under impression that they re-used the .env

[brazen pagoda]

So, we could try something on the "original db" (as you have one/multiple pgdump, it should be ok, though I would make an extra one)
What I understand from people of the team is that:

Vectors is the type from pgvecto.rs which the new image doesn't have
So you could remove the columns of that, then do a pg-dump and try to import it:
I think you can drop the schema which might be the source of the issue
DROP SCHEMA vectors;

[modern folio]

i wil try that

WOW that worked. Thank you so much for all the help @brazen pagoda .

[brazen pagoda]

Nice! So everything is running on TrueNAS now?

[modern folio]

yes only need to set up home asistant but that will be a fresh start