[SOLVED] pg_restore immich backup into new CNPG instance
1. gunzip immich-db-backup-1735542000019.sql.gz
2. sed "s/SELECT pg_catalog.set_config('search_path', '', false);/SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true);/g" immich-db-backup-1735542000019.sql > immich-db-backup.sql
3. kubectl -n databases cp ./immich-db-backup.sql postgres-16-1:/var/lib/postgresql/data/
4. kubectl -n databases exec -it postgres-16-1 -- bash
5. pg_restore -S postgres -U immich -Cev -f /var/lib/postgresql/data/immich-db-backup.sql
The last command just hangs with no output. I've tried with a variety of pg_restore options. Any suggestions?
17 Replies
:wave: Hey @Bubba,
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 :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.
Successfully submitted, a tag has been added to inform contributors. :white_check_mark:Try with the psql command from the docs
I assume you mean this command
gunzip < "/path/to/backup/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 immich_postgres psql --username=postgres but I don't have docker and I'm trying to restore to a new CNPG database, so I tried to adjust this command to my enviornment with the steps stated in the OP.What I meant specifically is you're using
pg_restore where our docs use psql
idk if the behaviour is maybe differentack
progress, but failed due to missing immich database. Trying to figure out how to create it.
Are you connected to a read replica?
I think I am, I just created it via 'kubectl cnpg..."
ok, I guess I need to get into the primary cnpg instance. restoring is failing due to read-only as well.
looking good so far
creating, altering an copying tables
Nice
For the future I'll suggest using kubectl cnpg port-forward instead of execing into the pod
Defaults to the right pod plus you don't have to mess with copying files etc
good call
I'm writing up my procedure and I'll share once I have verified it works
Or cnpg psql actually, forgot about that one for a sec lol
the kubectl plugin?
if so I'm not sure how that works with the local backup file. That's what I initially tried but I barely know what I'm doing.
I believe it runs psql locally
But I'm not actually sure
one of the 'CREATE INDEX' has been sitting there for a while. I have a pretty big immich instance, so hopefully this is still working
Does it say which index? The ML ones can take a fair bit of compute to put together
it does not
I have other problems to fix since my cluster rebuild in the meantime š
done
ok, now I need to pivot my cnpg service over to this new instance and then pivot my immich instance over to the cnpg database (instead of the bitnami one)
then I'll know if it's really working
š¤
bleh
You are using image
ghcr.io/tensorchord/cloudnative-pgvecto.rs:14.11-v0.2.1 and I am using image ghcr.io/tensorchord/cloudnative-pgvecto.rs:16.6-v0.4.0
looks like I gotta do this: https://github.com/immich-app/immich/releases/tag/v1.95.0
well, that's not working at all
created a cnpg instance with the same image you use and I'm importing my backup. I think I figured out the syntax with the kubectl plugin
I don't have the "--username=immich" flag though
looks like my image db was restored. I haven't tested anything, but the instance comes online and I can see the photos and metadataThis thread has been closed. To re-open, use the button below.