From: | PopeRigby <poperigby(at)mailbox(dot)org> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Errors when restoring backup created by pg_dumpall |
Date: | 2024-12-01 01:17:31 |
Message-ID: | 9c5ba566-27b8-4e8c-bf7d-2dc561509991@mailbox.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2024-12-01 01:27:26 | Re: Errors when restoring backup created by pg_dumpall |
Previous Message | Durgamahesh Manne | 2024-11-30 13:18:01 | Regarding logical slots allocation with logical rw |