[BUG] pg_dump/pg_restore does not work

The bug

When run pg_dump and later pg_restore, the restoration process throws an error:

pg_restore: error: COPY failed for table "geodata_places": ERROR:  type "earth" does not exist
LINE 1: ...ians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth
                                                                  ^
QUERY:  SELECT cube(cube(cube(earth()*cos(radians($1))*cos(radians($2))),earth()*cos(radians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth
CONTEXT:  SQL function "ll_to_earth" during inlining
COPY geodata_places, line 1: "3038999	Soldeu	1.66769	42.57688	AD	02		2017-11-06"
pg_restore: warning: errors ignored on restore: 1

I think PR #5301 causes this issue.

The OS that Immich Server is running on

Ubuntu 22.04

Version of Immich Server

v1.89.0

Version of Immich Mobile App

v1.89.0

Platform with the issue

Server
Web
Mobile

Your docker-compose.yml content

N/A.

Your .env content

N/A.

Reproduction steps

1. Immich with some geodata
2. `pg_dump -Fc -U immich -w -d immich -f immich.dump`
3. `pg_restore -U immich -w -d immich --clean immich.dump`
...

Additional information

After some googling, I found this diogob/activerecord-postgres-earthdistance#30 (comment). Basically, we have to replace earth with public.earth.

@zackpollard

Cc @zackpollard

Came to report the same issue, as our Cloudron packaging tests hit this.

To add some more findings, looking at the postgresdump created from the database, it contains the following for earthdistance:

CREATE EXTENSION IF NOT EXISTS earthdistance WITH SCHEMA public;

All other occurances of earthdistance extension functions are correctly prefixed with public it seems.

@vnghia

@vnghia What Postgres version were you using when perform db_dump and pg_restore?

I'm using 16 but I think other versions are affected as well since pg_dump and pg_restore are version-independent, at least for the SQL dump.

References

This also happens on postgres 14

Does the following guide work?

https://immich.app/docs/administration/backup-and-restore

Comparing that with our case, it seems pg_dumpall vs pg_dump is the culprit. In Cloudron only pg_dump is performed. Have to see what potential side-effects are to change that.

Reporting that streaming data out of Cloudnative PG and into a new cluster also runs into this same issue. Seems to fail on the pg_restore function.

My Cloudnative PG cluster config, trying to get data out of a PG14 database into a new cluster that has pgvecto.rs installed by default: https://github.com/ModestTG/jace-cluster/blob/main/kubernetes/apps/database/cloudnative-pg/cluster/cluster2.yaml

To add a bit more findings, so adding SET search_path TO "$user",public; (this may vary depending on your setup, it can be fetched in a working instance with SHOW search_path) in the top of the sqldump makes the restore work. I don't have a solution yet on how to make pg_dump do this automatically so one does not have to edit the file for restore.

This behavior to unset search_path is part of https://www.postgresql.org/docs/release/10.3/

I guess the question is, why this ll_to_earth() uses earth() without prefix, but I am really not a postgres extension expert :/

Just want to add that SET search_path TO "$user",public; is only usable if we dump the database into plain text (sql script). In my case (binary format), it seems there is no way to restore the backup beside converting that binary format to plain text beforehand.

I think it is worth noting that there are two separate things that need to happen:

Schema needs to be re-created and
Data needs to be reloaded

Normally (1) happens via the migrations, which do correctly create the required extensions.

When you run the pg_dump command you have to the option to include only the data, only the schema, or both the schema and the data. The command in the docs exports the schema and the data together. We should maybe update the documentation to instead recommend:

Export data only
Start up new install
Import backup

I was able to get immich migrated from one Cloudnative PG cluster to another Cloudnative PG cluster. Here is the method I used:

Shut down Immich to stop writes to the DB
Run the following commands on the existing DB

ALTER EXTENSION cube SET SCHEMA pg_catalog;
ALTER EXTENSION earthdistance SET SCHEMA pg_catalog;

Create the new cluster using the bootstrap initdb method

spec:
  instances: 3
  imageName: ghcr.io/bo0tzz/cnpgvecto.rs:14-v0.1.10 # CNPG compatible image with pgvecto.rs extension installed
  bootstrap:
    initdb:
      import:
        type: microservice
        databases:
          - immich
        source:
          externalCluster: postgres # name of cluster where existing immich data lives
(...)
  externalClusters:
    - name: postgres
      connectionParameters:
        host: postgres-ro.database.svc.cluster.local
        user: postgres
        dbname: immich
      password:
        name: cloudnative-pg-secret
        key: password

For me, this created a database in the new cluster called app, owned by app. You could either update your immich HR to point at this new db with the new role, or update the db to fit the existing config (this is the choice I took)
To update the new cluster, perform the following psql commands on the new cluster:

ALTER DATABASE app RENAME TO immich; --or whatever your db name is
CREATE ROLE immich; --or whatever your role is
ALTER ROLE immich WITH LOGIN;
\c immich;
CREATE EXTENSION vectors; --add extension for v1.91.0 release
REASSIGN OWNED BY app TO immich;

Update Immich HR secrets/config to point at the new postgres cluster
Bring up Immich, verify all data is present.

For Cloudron we have decided to remove the search_path statement which newer pg_dump versions add to the sqldump. The line which gets added looks like SELECT pg_catalog.set_config('search_path', '', false); and if removed, pg_restore will pick up the default search_path from the main postgres config. This makes the restore work correctly, but given the CVE for this, this is only recommended in a trusted environment.

From our side no immich changes are required then.

So I think we should add a warning in "Backup and restore" section that pg_dump will produce an unusable backup without moving things into pg_catalog or manually editing and then we can close this issue.

I'm testing disaster recovery, and I met same problem when using pg_dumpall,

I'm using the postgres 15 from debian and immich v1.90.2

backup and restore like this

# backup
pg_dumpall -c -U postgres > dump.sql

# restore
psql -f dump.sql postgres

@wlmqpsc Please try to follow the guide here https://immich.app/docs/administration/backup-and-restore/

@alextran1502

OK. I tried this. @alextran1502

For my install i need to use pg_dumpall -c -U postgres | gzip > "./dump.sql.gz" to backup and in new database, use gunzip < "/import/dump.sql.gz" | psql -U postgres -d immich to restore.

and i got same error

ERROR:  type "earth" does not exist
LINE 1: ...ians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth
                                                                  ^
QUERY:  SELECT cube(cube(cube(earth()*cos(radians($1))*cos(radians($2))),earth()*cos(radians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth
CONTEXT:  SQL function "ll_to_earth" during inlining
COPY geodata_places, line 1: "3038999   Soldeu  1.66769 42.57688        AD      02              2017-11-06"

I've also encountered this issue but I've solved it by changing this line in my dump.sql so it uses both search_paths not just the default one.

SELECT pg_catalog.set_config('search_path', '', false)

changed to

SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true)

This sed command sets search path to includes both the public schema and the pg_catalog schema, preventing the issue occurring when importing

sed -i "s/SELECT pg_catalog.set_config('search_path', '', false);/SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true);/g" dump.sql

To verify that the change has been successfully applied, grep can be used:

grep "SELECT pg_catalog.set_config" dump.sql

I also tested removing the line altogether as above mentioned, It worked without issues but I was more conformable just leaving it there and adding those 2 schemas.

@jrasm91

I got similar error while trying to restore the backup made with 1.91.1, these lines

immich/server/src/infra/migrations/1700714033632-AddFaceEmbeddingIndex.ts

Lines 8 to 9 in 1d0dbdf

    
                 CREATE INDEX IF NOT EXISTS face_index ON asset_faces 
        
                 USING vectors (embedding cosine_ops) WITH (options = $$

and

immich/server/src/infra/migrations/1700713994428-AddCLIPEmbeddingIndex.ts

Lines 8 to 9 in 1d0dbdf

    
                 CREATE INDEX IF NOT EXISTS clip_index ON smart_search 
        
                 USING vectors (embedding cosine_ops) WITH (options = $$

should be USING public.vectors instead. Although removing SELECT pg_catalog.set_config('search_path', '', false) makes the issue go away, it will be better if we can fix this somehow.

/cc @jrasm91

Same error. I am on Immich v1.91.4 and I followed the guide https://immich.app/docs/administration/backup-and-restore almost to the letter. (I had to to do docker-compose up --no-start instead of docker compose create, otherwise the network was not created.)

What procedure can be followed when access to the source database is lost?

With the sed command, suggested by #5630 (comment) this works:

Change

gunzip < "/path/to/backup/dump.sql.gz" | \
  docker exec -i immich_postgres psql -U postgres -d immich    # Restore Backup

to:

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 -U postgres -d immich    # Restore Backup

@erikvanoosten

Thank you so much @erikvanoosten, your solution worked for right away.

@nebulade

The default recommended backup/restore path using pg_dumpall actually works fine (@nebulade)

This was not the case for me. I dumped with the suggested dumpall from version 1.93.3 and used the official restore procedure exactly. I have a very pure docker compose setup, all immich containers, nothing custom. I also restored to 1.93.3. However I still got the error during a restore:

ERROR:  type "earth" does not exist              
LINE 1: ...ians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth                           
                                                                  ^                               
QUERY:  SELECT cube(cube(cube(earth()*cos(radians($1))*cos(radians($2))),earth()*cos(radians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth                                                 
CONTEXT:  SQL function "ll_to_earth" during inlining                                              
COPY geodata_places, line 1: "3038999   Soldeu  1.66769 42.57688        AD      02              2017-11-06"

I haven't found something broken in the restored instance though, should there have been some trouble?

Ran into this using Cloudnative-PG doing a v14 to v16 migration. My Postgres is not dedicated to Immich, I was able to list all databases minus Immich and they all cleanly migrated via Cloudnative-PG bootstrap "monolith" method.

Slightly different steps, but same theme overall:

Step 1 - Create Manual Cluster Database Dump of PG14

cd ~/kubernetes/cloudnative-pg/backups
kubectl exec postgres14-1 -c postgres -n cloudnative-pg -- pg_dumpall -c -U postgres > cluster14.pg_dumpall

Step 2 - Use Script to Extract Immich Database from the dumpall file

https://nicolaiarocci.com/how-to-restore-a-single-postgres-database-from-a-pg_dumpall-dump/

Simple script:

cat pg_extract.sh

#!/bin/bash
[ $# -lt 2 ] && { echo "Usage: $0 <postgresql dump> <dbname>"; exit 1; }
sed  "/connect.*$2/,\$!d" $1 | sed "/PostgreSQL database dump complete/,\$d"

Used as:

./pg_extract.sh cluster14.pg_dumpall immich > immich.dump

Step 3 - Fix Search Path:

Could probably add this as sed step in script above
Edit dump file: immich.dump
Locate line starting with SELECT pg_catalog.set_config (About line 8)
Add word “public” as below:

SELECT pg_catalog.set_config('search_path', 'public', false);

Save changes

Step 4 - Manually create Immich database and extensions as others documented.

Step 5 - Import Immich Dump File:

GPASSWORD=<app_user_password> psql -p 5432 -h 192.168.10.247 -d immich -U app < immich.dump

Where GPASSWORD is the password of the app user/role.
Where -h is the host name, in this case IP address of new PG cluster load balancer
Where -d points to immich database
Where -U points to the user/role of app

This imported cleanly with no SQL Errors.

Navigation Menu

Repository navigation

[BUG] `pg_dump/pg_restore` does not work #5630

The bug

The OS that Immich Server is running on

Version of Immich Server

Version of Immich Mobile App

Platform with the issue

Your docker-compose.yml content

Your .env content

Reproduction steps

Additional information

9 remaining items

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

	CREATE INDEX IF NOT EXISTS face_index ON asset_faces
	USING vectors (embedding cosine_ops) WITH (options = $$

	CREATE INDEX IF NOT EXISTS clip_index ON smart_search
	USING vectors (embedding cosine_ops) WITH (options = $$

[BUG] pg_dump/pg_restore does not work #5630

Description

The bug

The OS that Immich Server is running on

Version of Immich Server

Version of Immich Mobile App

Platform with the issue

Your docker-compose.yml content

Your .env content

Reproduction steps

Additional information

Activity

bo0tzz commented on Dec 11, 2023

nebulade commented on Dec 11, 2023

nebulade commented on Dec 11, 2023

alextran1502 commented on Dec 11, 2023

vnghia commented on Dec 11, 2023

alextran1502 commented on Dec 11, 2023

nebulade commented on Dec 11, 2023

alextran1502 commented on Dec 11, 2023

nebulade commented on Dec 11, 2023

ModestTG commented on Dec 11, 2023

nebulade commented on Dec 11, 2023

vnghia commented on Dec 11, 2023

jrasm91 commented on Dec 11, 2023

9 remaining items

ModestTG commented on Dec 13, 2023

nebulade commented on Dec 13, 2023

vnghia commented on Dec 13, 2023

vnwnv commented on Dec 21, 2023

alextran1502 commented on Dec 21, 2023

vnwnv commented on Dec 21, 2023

vktr2b commented on Dec 21, 2023

vnghia commented on Dec 26, 2023

erikvanoosten commented on Dec 29, 2023

erikvanoosten commented on Dec 29, 2023

Zakmaf commented on Jan 16, 2024

bjrne commented on Feb 4, 2024

reefland commented on Feb 9, 2024

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

[BUG] `pg_dump/pg_restore` does not work #5630