[Guide] Database backup in Truenas

[aviv926]

I recently started using Truenas to deploy Immich, I ran into a lot of missing information about how it's deployed.
As part of this their layout includes a different name for the database (immich&immich) rather than immich and postgres which has led to a lot of confusion.
In addition the database password is generated randomly without the user knowing what it is (It does not appear in the Truenas documentation).

How is it tested?
The backup was created on Immich with an environment of 260 images (including machine learning running)
and successful restoration of the backup.

Problems:
During the restoration process I encountered a problem that the names of the places were not restored at all, this is a known problem #7004.

Guide

Truenas Dragonfish version

Backup

[!NOTE]
By default when using the Immich distribution of TrueNAS the database password is randomly generated, to back up Immich the database > password is needed.

[!INFO]
pgAdmin is required to be installed

immich old version < v1.105.1

1. [HeavyScript](https://github.com/Heavybullets8/heavy_script) and pgAdmin must be installed. 2. Go to System Settings -> Shell and enter: `sudo heavyscript dns immich` Copy the entry of immich-postgres It should look something like this: `immich-postgres.ix-immich.svc.cluster.local` 3. Now enter: `heavyscript pod -s immich` 4. Select immich-postgres. 5. Now write `env` and copy the POSTGRES_PASSWORD value. 6. Identify your database password, save it!

Now go to pgAdmin

1. Connect to the browser interface.

2. Select Add New Server.

3. Enter the following values:

   Name	Value
   Host name/address	The value from section 2
   Port	5432
   Maintenance database	immich
   Username	immich
   Password	The value from section 6

4. Under Databases select Immich -> right click -> backup.

5. Select the backup location and click Backup.

In order to connect to the database and perform a backup, we will need login details, the password will appear on the right side together with all the details required to connect to the database

Now go to pgAdmin

1. Connect to the browser interface.

2. Select Add New Server.

3. Enter the following values:

   Name	Value
   Host name/address	The value that appears in Database Details
   Port	5432
   Maintenance database	immich
   Username	immich
   Password	The value that appears in Database Details

4. Under Databases select Immich -> right click -> backup.

5. Select the backup location and click Backup.

You have successfully backed up your database!

Recovery

1. Reinstall Immich (and keep it running!).
2. Go through the steps from the previous section until connecting to the database.
3. In pgadmin under databases select Immich -> right click -> restore
4. Load the backup file and under Query Options check the Clean before restore option.
5. Wait for the restore to finish and open Immich.

If you have suggestions or other ways in which you take a backup, you are welcome to write to me, I will edit accordingly.

[Dievale]

I love immich but all the breaking changes on major released stopped me from using it. I was waiting for exactly the same. How to restore from truenas apps instead of container. Thank you for the info. Ill try

[wscott]

This does allow me to connect to the database with pgadmin, but it doesn't seem to work to restore the pgbackup files that are automatically saved by the current immich image. Those are .SQL files that want to be restored with psql.

[Dievale]

Same happened to me . Yes you can I tested and it works. The instructions are a little confusing You have to select save as sql . Click on the ? Mark to see the details in pgadmin. This is a game changer at least for me. Now I can backup the db in truenas scale. I was able to backup and restore from scratch Thank you, Diego

…

________________________________ From: Wayne Scott ***@***.***> Sent: Monday, May 6, 2024 9:08:05 PM To: immich-app/immich ***@***.***> Cc: Dievale ***@***.***>; Comment ***@***.***> Subject: Re: [immich-app/immich] [Guide] Database backup in Truenas (Discussion #8809) This does allow me to connect to the database with pgadmin, but it doesn't seem to work to restore the pgbackup files that are automatically saved by the current immich image. Those are .SQL files that want to be restored with psql. — Reply to this email directly, view it on GitHub<#8809 (reply in thread)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AI4F45AQO2CE5WE7COLILJTZBASPLAVCNFSM6AAAAABGHK6IW6VHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4TGMZVGUZTS>. You are receiving this because you commented.Message ID: ***@***.***>

[aviv926]

I would love to improve the guide, could you elaborate more on what you mean? Maybe include a screenshot?

[z1haze]

Well the the database credentials show up right in the sidebar of the pgadmin app so all of those steps in order to get the credentials seem to be not needed now.

[z1haze]

Do you think its possible to migrate from a standalone immich environment running on docker to the trunas application? I have typical docker-compose immich server running on a separate machine and I want to migrate over to my new trunas machine and I have been struggling to figure out how. Some of the folder names are different, like upload vs uploads in trunas. If I have the sql dump from my old server, you think it will import fine into the trunas app?

Following your instructions and so far they've been flawless -- however when it comes to restoring a backup .. how do i get my backup from my other server into the scope of pgadmin in the immich-postgres container?

[z1haze]

Sorta. You have to do a custom installation and specify the location of everything during the setup and then manually setup a cronjob task to do the dB backups. I created a new dataset call immich and set all of the paths during the immich install to point to that dataset. At least then you can have direct access to the files.

[aviv926]

exactly like that!
You must change the folder name in truenas as it appeared when you took a backup on your previous machine, otherwise nothing will work because the paths look for the path with the folder name as it appears in the database

Therefore you must generate under Type of Storage the Host Path and define the system installations for Immich with the correct names.

For example:

[avilabss]

My restore seems to keep failing. When I see the logs on pgAdmin, it says something about 'postgres' role not being found. That's one last hurdle I seem to be encountering now

[avilabss]

My restore seems to keep failing. When I see the logs on pgAdmin, it says something about 'postgres' role not being found. That's one last hurdle I seem to be encountering now

bump!

[z1haze]

Of you moving from thr standalone docker app to the trunas app it is significantly more involved due to the folder structure being different, the pg user and roles being different, and the file system user and group. If you look through the support tickets in the immich discord I have a thread in there which by the end of it I detailed everything I did to make it work. But just s heads up, it is very convoluted. Looks like you at least didn't have to worry about a different pg version though

[Nordlicht-13]

How to start pgAdmin in TrueNAS to backup immich database?
I just had a corrupted Immich App container. Reinstalled immich
and want to backup my albums now, because they are all gone.

[aviv926]

I hope it's not too late...
You can use the pgAdmin application as you would install any other application...

[Nordlicht-13]

Tried to, but pgAdmin is deploying forever, guess I have to wait for TrueNAS Electric Eel.

[Reikagilu]

Tried to, but pgAdmin is deploying forever, guess I have to wait for TrueNAS Electric Eel.

i had this trouble too, check the logs of the container, it probably have a error there

[LEOSCHUMY]

I hope it's not too late... You can use the pgAdmin application as you would install any other application...

How connect from pgAdmin container to Immich dB container? Which parameter I need to use as hostname, port, user and password?

[Reikagilu]

i tryed the commands above from my immich with 60gb of disk usage with images and videos
but the postgres file is 300 mb
This is wright?

[z1haze]

Yeah. It's only the database. You still have to copy over your media

[Reikagilu]

Yeah. It's only the database. You still have to copy over your media

Can you help me with how to do this?

[z1haze]

You can use something like rsync

[Reikagilu]

i just want to access my files, and download them, but i dont know where i can find the files

[Enzo-Kerkhof]

Hey thanks for the information. I'm moving from the TrueCharts deployment to the TrueNAS one, since TrueCharts will be deprecated and also was not updating for me anymore.

I've come very far with all the available information. The only issue I have left is that the current PostgreSQL version of the TrueNAS app is 15 and the ProstgreSQL version of the TrueCharts deployment was/is 16 with their Cloudnative-PG.
Therefore I cannot restore my database to my new TrueNAS deployment. pg_restore: error: unsupported version (1.15) in file header
Without knowing excatly what to do with all the pg_dump commands and not just using the pgAdmin GUI, can someone give me some advice?

[Tr0gdorTheBurninator]

Now that HeavyScript is no longer supporting TrueNAS, is there another way to do backups?

[nicholasdaviees]

@Tr0gdorTheBurninator I just installed HeavyScripts yesterday and it's still working fine. Idk if support for it will be completely dropping soon though. Follow the installation instructions on this page: https://github.com/Heavybullets8/heavy_script?tab=readme-ov-file

[Nordlicht-13]

Maybe you can try this, if you are running Immich on TrueNAS:
#10906
Hopefully we get a option to backup the database in the GUI in the near future.

[Enzo-Kerkhof]

So this is what I've been trying to do, either through pgAdmin or this way doesn't really matter.
The main issue is the PostgreSQL version of the TrueNAS pod is 15 and the ProstgreSQL version of the TrueCharts pod is 16.
Anyone a good idea to get the psql 15 pg_dumpall bin inside the TrueCharts pod?

[pulsar895]

I've done without HeavyScripts or pgadmin. I've detailed the steps here .

[pulsar895]

Below, my updated documentation.
However, I think the official immich documentation works.

---

Backup

I make my backups with this script (run as root):

#!/bin/bash

immich_pod="ix-immich";
backup_path="/mnt/ronflex/immich/pgbackup";
backup_file="immich_$(date --utc +%Y-%m-%d_%H-%M-%S).sql";

# Make the dump
docker exec ix-immich-pgvecto-1 pg_dump --username="immich" immich | sudo tee "${backup_path}/${backup_file}" > /dev/null

# Remove old backups
find "${backup_path}" -mtime +7 -path "*.sql" -delete

Restore

Stop all immich containers except pgvecto:

$ ssh <truenas>
$ for container in $(docker ps -a | grep immich | grep -v pgvecto | awk '{print $1}'); do sudo docker stop $container; done

Push the dump file on postgres immich container:

$ find . -mtime 1 -name "*.sql" -exec sudo docker cp {} ix-immich-pgvecto-1:/tmp/last.sql \;

Connect to the container:

$ sudo docker exec -it ix-immich-pgvecto-1 bash

Connect to the database:

postgres@0b438c858f77:/$ psql --username=immich postgres
psql (15.5 (Debian 15.5-1.pgdg120+1))
Type "help" for help.

postgres=#

Delete and recreate immich database:

postgres=# DROP DATABASE immich WITH (FORCE);
DROP DATABASE
postgres=# CREATE DATABASE immich;
CREATE DATABASE
postgres=# exit

Import the dump (plain text file):

$ cat /tmp/last.sql | sed "s/SELECT pg_catalog.set_config('search_path', '', false);/SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true);/g" | psql -v ON_ERROR_STOP=1 -h 127.0.0.1 -U immich immich; exit

Restart containers previously stopped:

$ for container in $(docker ps -a | grep immich | grep -v pgvecto | awk '{print $1}'); do sudo docker start $container; done

Enjoy!

[omar-salama]

Thanks a ton! Migrating Immich to trueNAS Scale gave me a headache till i stumbled upon your comment.

However the dump import gives an error Cannot drop the currently open database
Easily fixable by connected to the postgres database instead of immich. Modified command:

cat /tmp/last.sql | sed "s/SELECT pg_catalog.set_config('search_path', '', false);/SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true);/g" | psql -v ON_ERROR_STOP=1 -h 127.0.0.1 -U immich postgres; exit

[pulsar895]

Hello,

I think you forgot to delete the current base with the following commands:

postgres=# DROP DATABASE immich WITH (FORCE);
DROP DATABASE
postgres=# CREATE DATABASE immich;
CREATE DATABASE
postgres=# exit

[omar-salama]

The issue is that the backup script has DROP command inside it, and you can’t drop a database while you’re connected to it (while running the migration script).
Does it work for you with -U immich immich? Because it didn’t for me, and I did follow your guide step by step.

[LEOSCHUMY]

Same omar-salama problem.
Solved without connecting to the database and without deleting and recreating the Immich database,
but directly from root@truenas

 cat "<my_folder>/immich-db-backup-1738571040010.sql" \
| 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=postgres --username=immich  

[Barbiiii]

Seems like the database connection details are no longer visible in the Apps Details. Where do I find the hostname nowadays?

[kigrup]

Have you found out how to get the DB details? When installing the application you can provide "database" password, but I couldn't manage to connect to it.

[ajkelsey]

This thread has been very helpful to me. Based on previous comments, I have a working procedure to restore a backup.

TrueNAS ElectricEel-24.10.2.1
immitch 1.7.42

Immich automatically generates the database backup. I use a replication task to copy all directories excluding pgData/ to a backup location.

• freshly install immich instance
• do not log into it

from [immich root]/backups:

• unzip database backup

for container in $(docker ps -a | grep immich | grep -v pgvecto | awk '{print $1}'); do sudo docker stop $container; done
sudo docker cp [backup sql filename] ix-immich-pgvecto-1:/tmp/last.sql
sudo docker exec -it ix-immich-pgvecto-1 bash

from docker prompt:

psql --username=immich postgres

from sql prompt:

DROP DATABASE immich WITH (FORCE);
CREATE DATABASE immich;
exit
cat "[sql filename]" | 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=postgres --username=immich  
  for container in $(docker ps -a | grep immich | grep -v pgvecto | awk '{print $1}'); do sudo docker start $container; done

from truenas backup directory prompt:

  cp -r library/ [immich root]
  cp -r profile/ [immich root]
  cp -r thumbs/ [immich root]
  cp -r upload/ [immich root]
  cp -r video/ [immich root]

[dmolnar110]

Nice!