Message-ID: <9c5ba566-27b8-4e8c-bf7d-2dc561509991@mailbox.org>
Date: Sat, 30 Nov 2024 17:17:31 -0800
MIME-Version: 1.0
From: PopeRigby <poperigby@mailbox.org>
Subject: Re: Errors when restoring backup created by pg_dumpall
To: Adrian Klaver <adrian.klaver@aklaver.com>, pgsql-general@postgresql.org
References: <6a6439f1-8039-44e2-8fb9-59028f7f2014@mailbox.org>
 <cba48d2c-0987-4233-a86f-2e693677efd3@aklaver.com>
Content-Language: en-US
In-Reply-To: <cba48d2c-0987-4233-a86f-2e693677efd3@aklaver.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/9c5ba566-27b8-4e8c-bf7d-2dc561509991%40mailbox.org>
Precedence: bulk

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?