Search Postgresql Archives

Re: Errors when restoring backup created by pg_dumpall

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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?






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux