[BUG] pg_dump/pg_restore does not work

[vnghia]

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.

[bo0tzz]

Cc @zackpollard

[nebulade]

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

[nebulade]

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.

[alextran1502]

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

[vnghia]

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.

[alextran1502]

References

• ERROR: type "earth" does not exist at character 136 diogob/activerecord-postgres-earthdistance#30
• https://postgrespro.com/list/thread-id/2400453
• https://stackoverflow.com/questions/63586022/pg-dump-pg-restore-error-with-extension-cube

[nebulade]

This also happens on postgres 14

[alextran1502]

Does the following guide work?

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

[nebulade]

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.

[ModestTG]

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

[nebulade]

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 :/

[vnghia]

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.

[jrasm91]

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

1. Schema needs to be re-created and
2. 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:

1. Export data only
2. Start up new install
3. Import backup

[nebulade]

The default recommended backup/restore path using pg_dumpall actually works fine, so I don't think it needs a fix for such deployments. However we only hit issues if not the default deployment and database setup is used. For those cases relying on a special process like "install fresh (to create the schema) -> import data" adds more requirements to the backkup/restore strategy, which I think should be rather avoided.

[vnghia]

Based on this Stackoverflow's answer and this thread, this is a earthdistance's bug which uses non prefixed "earth" and there's nothing we can do about it.

The workaround is to move cube and earthdistance to pg_catalog schema which will be included even if search_path is unset by:

For a newly-created database:

CREATE EXTENSION cube WITH SCHEMA pg_catalog;
CREATE EXTENSION earthdistance WITH SCHEMA pg_catalog;

For a migrated database:

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

This only need to be done once, no need to modify the sqldump, works well with binary dump and the easiest way to do this is adding a migration:

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

but moving something to pg_catalog is considered bad practice :/

I can not restore even with pg_dumpall so if we decide not to move the extensions, we should at least add a warning to the backup-and-restore doc.

Happy to send a PR if needed 😄

Edit: On a second thought, moving extensions will effect only the current immich database, which is fine I guess :/

[ModestTG]

So I migrated the data and I'm trying to get immich stood up in the new cluster, and I keep getting <Table> already exists and errors saying that the server can't connect to the database. Perhaps my new psql cluster doesn't have the right permissions? When the migration happened the database name did not migrate over, I had to rename the database

EDIT: I just noticed that all of the tables also have the wrong owner. I'll fix that. I would think that the bootstrap initdb from Cloudnative PG would keep all of that info but I guess it doesn't. Maybe I'm not understanding it correctly.

[nebulade]

For anyone wanting to understand the root cause a bit better, pg_dump does set the search_path to "" which was done due to https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058%3A_Protect_Your_Search_Path
This causes the restore to fail as it can't lookup some symbols like earth() unless they are fully prefixed.

After all this I don't think its a good idea to register the extension into pg_catalog as a solution to the problem.

For a trusted environment, adding public to the search_path during restore is not a security issue as far as I understand the CVE and thus I think its the preferred solution, it will not require any immich change and also is in-line with the preferred backup/restore for immich which uses pg_dumpall anyways.

[ModestTG]

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

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

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

3. 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

4. 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)
5. 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;

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

[razin99]

+1 on this
I skipped shutting down the db (just wondering if it can, but we probably should not)
You can skip step 5 by configuring bootstrap.initdb.[database,owner] and referenced the secret to reuse the password of the old database.

  bootstrap:
    initdb:
      database: immich
      owner: immich
      secret:
        name: immich-postgresql-password
      import:
        type: microservice
        databases:
          - immich
        source:
          externalCluster: old-immich

[nebulade]

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.

[vnghia]

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.

[vnwnv]

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 

[alextran1502]

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

[vnwnv]

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"

[vktr2b]

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.

[vnghia]

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

[erikvanoosten]

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?

[erikvanoosten]

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

[sayidabyan]

I tried to modify the command to my non-docker setup

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" | \
  sudo -u immich psql -U immich -d immich    # Restore Backup

immich is the name of the db and db user in my case. In the middle of the restorations it gave me this error

ERROR:  must be owner of extension vectors
ERROR:  must be owner of extension uuid-ossp
ERROR:  must be owner of extension unaccent
ERROR:  must be owner of extension pg_trgm
ERROR:  must be owner of extension earthdistance
ERROR:  must be owner of extension cube
ERROR:  cannot drop schema vectors because other objects depend on it
DETAIL:  extension vectors depends on schema vectors
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
ALTER SCHEMA
ERROR:  schema "vectors" already exists
ALTER SCHEMA
CREATE EXTENSION
ERROR:  must be owner of extension cube
CREATE EXTENSION
ERROR:  must be owner of extension earthdistance
CREATE EXTENSION
ERROR:  must be owner of extension pg_trgm
CREATE EXTENSION
ERROR:  must be owner of extension unaccent
CREATE EXTENSION
ERROR:  must be owner of extension uuid-ossp
CREATE EXTENSION
ERROR:  must be owner of extension vectors

any idea how I can solve this? thank you so much.

[Zakmaf]

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

[bjrne]

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?

[reefland]

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.