public inbox for [email protected]  

help / color / mirror / Atom feed

From: PopeRigby <[email protected]>
To: Adrian Klaver <[email protected]>
To: [email protected]
Subject: Re: Errors when restoring backup created by pg_dumpall
Date: Sat, 30 Nov 2024 17:17:31 -0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>

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?

view thread (6+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Errors when restoring backup created by pg_dumpall
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox