Immich sql database migration - template1 errors
🚧Setup❓Question
I am running immich on docker on truenas and have been trying to migrate my old Immich (version v1.123.0, yes it is very old I know I need to do better) to the docker compose setup. However, at the very end, when the new immich web UI asks you to restore from a backup, the sql databases have a compatibility mismatch. My old sql backups don't have a clean syntax for sql version 14, I think.
When I try to restore from the sql file I get the following screenshot.
So in the truenas shell I am trying to restore the backup manually but I am hung up on this "ERROR: database "template1" does not exist". trying to re syntax the .sql with the sed line doesn't work for me (found this solution on a forum)
truenas_admin@truenas[/mnt/tank/stacks/immich-new]$ sudo gunzip --stdout /mnt/datastore/configs/immich-new/data/backups/immich-db-backup-1771322400014.sql.gz \
| sed -E 's/ WITH .*;//' \
| sudo docker exec -i immich_postgres psql -U postgres -d postgres --set ON_ERROR_STOP=on
SET
SET
SET
DROP DATABASE
NOTICE: database "immich" does not exist, skipping
DROP ROLE
CREATE ROLE
ALTER ROLE
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
UPDATE 0
ERROR: database "template1" does not exist
When I try to restore from the sql file I get the following screenshot.
So in the truenas shell I am trying to restore the backup manually but I am hung up on this "ERROR: database "template1" does not exist". trying to re syntax the .sql with the sed line doesn't work for me (found this solution on a forum)
truenas_admin@truenas[/mnt/tank/stacks/immich-new]$ sudo gunzip --stdout /mnt/datastore/configs/immich-new/data/backups/immich-db-backup-1771322400014.sql.gz \
| sed -E 's/ WITH .*;//' \
| sudo docker exec -i immich_postgres psql -U postgres -d postgres --set ON_ERROR_STOP=on
SET
SET
SET
DROP DATABASE
NOTICE: database "immich" does not exist, skipping
DROP ROLE
CREATE ROLE
ALTER ROLE
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
UPDATE 0
ERROR: database "template1" does not exist
immich.yaml.txt3.09KB
