Subject: Re: Errors when restoring backup created by pg_dumpall

From: PopeRigby <[email-masked]>
Date: Sat, 30 Nov 2024 17:17:31 -0800

On 11/29/24 17:47, Adrian Klaver wrote:
> On 11/29/24 17:34, PopeRigby wrote:
>> My HDD recently failed so I'm trying to restore my backup, but I'm 
>> running into some errors.
>>
>> I've been using a systemd service that periodically backs up my 
>> cluster with pg_dumpall, and I'm using this command to restore:
>>
>> sudo psql -f backup.sql postgres
>>
>> I'm getting this output: 
>> https://gist.github.com/poperigby/d5a0103b9de1eba95aba783007e557ea
>
>
> psql:all.sql:4104: 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
>
> CREATE TABLE public.geodata_places (
>     id integer NOT NULL,
>     name character varying(200) NOT NULL,
>     longitude double precision NOT NULL,
>     latitude double precision NOT NULL,
>     "countryCode" character(2) NOT NULL,
>     "admin1Code" character varying(20),
>     "admin2Code" character varying(80),
>     "modificationDate" date NOT NULL,
>     "earthCoord" public.earth GENERATED ALWAYS AS 
> (public.ll_to_earth(latitude, longitude)) STORED,
>     "admin1Name" character varying,
>     "admin2Name" character varying,
>     "alternateNames" character varying
> );
>
>
> Looks like an extension or extensions where not installed before the 
> restore was done.
>
>
>>
>> This is my (redacted) database dump: 
>> https://gist.github.com/poperigby/fcb59eb6c22c6051800e06a0ec482b49
>>
>>
>
Weird, I have all the same software that was installed before I 
restored, as I'm using NixOS. I'm guessing the earth type is provided by 
earthdistance, and in the SQL script it's able to successfully install 
cube, vector, and earthdistance. I think earthdistance and cube are 
actually built-in modules, right?

I ran the following commands, and earth is even one of the listed types:

postgres=# CREATE EXTENSION earthdistance;
CREATE EXTENSION
postgres=# SELECT t.typname
FROM pg_catalog.pg_type t
JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
JOIN pg_catalog.pg_extension e ON e.extnamespace = n.oid
WHERE e.extname = 'earthdistance';
  typname
---------
  _cube
  _earth
  cube
  earth
(4 rows)

The earthdistance module is even getting added between the table with 
the earth type is added, so shouldn't there be no problem?

---

From: "David G. Johnston" <[email-masked]>
Date: Sat, 30 Nov 2024 18:27:26 -0700

On Saturday, November 30, 2024, PopeRigby <[email-masked]> wrote:

>> QUERY:  SELECT cube(cube(cube(earth()*cos(radians($1))*cos(radians($2))),
>> earth()*cos(radians($1))*sin(radians($2))),earth()*sin(radia
>> ns($1)))::earth
The fact that “earth” is not schema qualified leads me to suspect you are
getting bit by safe search_path environment rules.

David J.

---

From: PopeRigby <[email-masked]>
Date: Sat, 30 Nov 2024 18:11:18 -0800

On 11/30/24 17:27, David G. Johnston wrote:
>         LINE 1:
>         ...ians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth
>
>         QUERY:  SELECT
>          The earthdistance module is even getting added between the
>         table with the earth type is added, so shouldn't there be no
>         problem?
>
>
> The fact that “earth” is not schema qualified leads me to suspect you 
> are getting bit by safe search_path environment rules.
>
> David J.

Ah. How can I fix that?

---

From: "David G. Johnston" <[email-masked]>
Date: Sat, 30 Nov 2024 19:41:27 -0700

On Saturday, November 30, 2024, PopeRigby <[email-masked]> wrote:

>>> QUERY:  SELECT cube(cube(cube(earth()*cos(rad
>>> ians($1))*cos(radians($2))),earth()*cos(radians($1))*sin(rad
>>> ians($2))),earth()*sin(radians($1)))::earth
Since you are past the point of fixing the source to produce valid
dumps…that leaves finding the places in the text the lack the schema
qualification and manually adding them in.

David J.

---

From: PopeRigby <[email-masked]>
Date: Sat, 30 Nov 2024 19:26:51 -0800

On 11/30/24 18:41, David G. Johnston wrote:
>>              The earthdistance module is even getting added between
>>             the table with the earth type is added, so shouldn't
>>             there be no problem?
>>
>>
>>     The fact that “earth” is not schema qualified leads me to suspect
>>     you are getting bit by safe search_path environment rules.
>>
>>     David J.
>
>     Ah. How can I fix that?
>
Oh boy. How can I prevent this from happening again?

---

From: Marco Torres <[email-masked]>
Date: Sat, 30 Nov 2024 20:45:05 -0700

CREATE EXTENSION cube;

I do not know if you might need this one as well. I am assuming that you
are working on a gist server.

CREATE EXTENSION earthdistance;

I am assuming you are working with a gist server. This ought to be useful.
https://gist.cs.berkeley.edu/pggist/

You might want to read this:
https://docs.gitlab.com/ee/install/postgresql_extensions.html

My advice is to go to google, then chat GPT if you do not get any good
feedback here. Hopefully, this will give you good leads.