public inbox for [email protected]
help / color / mirror / Atom feedRe: Errors when restoring backup created by pg_dumpall
6+ messages / 3 participants
[nested] [flat]
* Re: Errors when restoring backup created by pg_dumpall
@ 2024-12-01 01:17 PopeRigby <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: PopeRigby @ 2024-12-01 01:17 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; 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?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Errors when restoring backup created by pg_dumpall
@ 2024-12-01 01:27 David G. Johnston <[email protected]>
parent: PopeRigby <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2024-12-01 01:27 UTC (permalink / raw)
To: PopeRigby <[email protected]>; +Cc: Adrian Klaver <[email protected]>; pgsql-general
On Saturday, November 30, 2024, PopeRigby <[email protected]> wrote:
> On 11/29/24 17:47, Adrian Klaver wrote:
>
>> On 11/29/24 17:34, PopeRigby wrote:
>>
>> 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(radia
>> ns($1)))::earth
>> CONTEXT: SQL function "ll_to_earth" during inlining
>> 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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Errors when restoring backup created by pg_dumpall
@ 2024-12-01 02:11 PopeRigby <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: PopeRigby @ 2024-12-01 02:11 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Adrian Klaver <[email protected]>; pgsql-general
On 11/30/24 17:27, David G. Johnston wrote:
> On Saturday, November 30, 2024, PopeRigby <[email protected]> wrote:
>
> On 11/29/24 17:47, Adrian Klaver wrote:
>
> On 11/29/24 17:34, PopeRigby wrote:
>
> 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
> 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?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Errors when restoring backup created by pg_dumpall
@ 2024-12-01 02:41 David G. Johnston <[email protected]>
parent: PopeRigby <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2024-12-01 02:41 UTC (permalink / raw)
To: PopeRigby <[email protected]>; +Cc: Adrian Klaver <[email protected]>; pgsql-general
On Saturday, November 30, 2024, PopeRigby <[email protected]> wrote:
> On 11/30/24 17:27, David G. Johnston wrote:
>
> On Saturday, November 30, 2024, PopeRigby <[email protected]> wrote:
>
>> On 11/29/24 17:47, Adrian Klaver wrote:
>>
>>> On 11/29/24 17:34, PopeRigby wrote:
>>>
>>> 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(rad
>>> ians($1))*cos(radians($2))),earth()*cos(radians($1))*sin(rad
>>> ians($2))),earth()*sin(radians($1)))::earth
>>> CONTEXT: SQL function "ll_to_earth" during inlining
>>> 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?
>
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Errors when restoring backup created by pg_dumpall
@ 2024-12-01 03:26 PopeRigby <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: PopeRigby @ 2024-12-01 03:26 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Adrian Klaver <[email protected]>; pgsql-general
On 11/30/24 18:41, David G. Johnston wrote:
> On Saturday, November 30, 2024, PopeRigby <[email protected]> wrote:
>
> On 11/30/24 17:27, David G. Johnston wrote:
>> On Saturday, November 30, 2024, PopeRigby <[email protected]>
>> wrote:
>>
>> On 11/29/24 17:47, Adrian Klaver wrote:
>>
>> On 11/29/24 17:34, PopeRigby wrote:
>>
>> 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
>> 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?
>
> 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.
>
Oh boy. How can I prevent this from happening again?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Errors when restoring backup created by pg_dumpall
@ 2024-12-01 03:45 Marco Torres <[email protected]>
parent: PopeRigby <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Marco Torres @ 2024-12-01 03:45 UTC (permalink / raw)
To: PopeRigby <[email protected]>; +Cc: David G. Johnston <[email protected]>; Adrian Klaver <[email protected]>; pgsql-general
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.
On Sat, Nov 30, 2024, 8:27 PM PopeRigby <[email protected]> wrote:
> On 11/30/24 18:41, David G. Johnston wrote:
>
> On Saturday, November 30, 2024, PopeRigby <[email protected]> wrote:
>
>> On 11/30/24 17:27, David G. Johnston wrote:
>>
>> On Saturday, November 30, 2024, PopeRigby <[email protected]> wrote:
>>
>>> On 11/29/24 17:47, Adrian Klaver wrote:
>>>
>>>> On 11/29/24 17:34, PopeRigby wrote:
>>>>
>>>> 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
>>>> 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?
>>
> 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.
>
> Oh boy. How can I prevent this from happening again?
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-12-01 03:45 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-12-01 01:17 Re: Errors when restoring backup created by pg_dumpall PopeRigby <[email protected]>
2024-12-01 01:27 ` David G. Johnston <[email protected]>
2024-12-01 02:11 ` PopeRigby <[email protected]>
2024-12-01 02:41 ` David G. Johnston <[email protected]>
2024-12-01 03:26 ` PopeRigby <[email protected]>
2024-12-01 03:45 ` Marco Torres <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox