Errors when restoring backup created by pg_dumpall

On Fri, Nov 29, 2024 at 8:35 PM PopeRigby <poperigby@mailbox.org> 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

This is my (redacted) database dump:
https://gist.github.com/poperigby/fcb59eb6c22c6051800e06a0ec482b49

Have you installed whatever software provides extensions like cube, vector
and earthdistance?

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

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

--
Adrian Klaver
adrian.klaver@aklaver.com

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?

On Saturday, November 30, 2024, PopeRigby <poperigby@mailbox.org> 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.

On 11/30/24 17:27, David G. Johnston wrote:

On Saturday, November 30, 2024, PopeRigby <poperigby@mailbox.org> 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?

On Saturday, November 30, 2024, PopeRigby <poperigby@mailbox.org> wrote:

On 11/30/24 17:27, David G. Johnston wrote:

On Saturday, November 30, 2024, PopeRigby <poperigby@mailbox.org> 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.

On 11/30/24 18:41, David G. Johnston wrote:

On Saturday, November 30, 2024, PopeRigby <poperigby@mailbox.org> wrote:

On 11/30/24 17:27, David G. Johnston wrote:

On Saturday, November 30, 2024, PopeRigby <poperigby@mailbox.org>
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?

On 11/30/24 19:26, PopeRigby wrote:

On 11/30/24 18:41, David G. Johnston wrote:

On Saturday, November 30, 2024, PopeRigby <poperigby@mailbox.org> wrote:

On 11/30/24 17:27, David G. Johnston wrote:

On Saturday, November 30, 2024, PopeRigby <poperigby@mailbox.org>
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?

In future schema qualify all references.

For now in the dump file you could search for

SELECT pg_catalog.set_config('search_path', '', false);

and set to

SELECT pg_catalog.set_config('search_path', 'public', false);

--
Adrian Klaver
adrian.klaver@aklaver.com

On 11/30/24 18:41, David G. Johnston wrote:

On Saturday, November 30, 2024, PopeRigby <poperigby@mailbox.org> wrote:

On 11/30/24 17:27, David G. Johnston wrote:

On Saturday, November 30, 2024, PopeRigby <poperigby@mailbox.org>
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 also, it's the schema is specified as public on this line:
https://gist.github.com/poperigby/fcb59eb6c22c6051800e06a0ec482b49#file-redacted_all-sql-L4111

Why is it not finding it? I queried public and earth was in there.

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 <poperigby@mailbox.org> wrote:

On Saturday, November 30, 2024, PopeRigby <poperigby@mailbox.org> wrote:

On 11/30/24 18:41, David G. Johnston wrote:

On Saturday, November 30, 2024, PopeRigby <poperigby@mailbox.org> wrote:

On 11/30/24 17:27, David G. Johnston wrote:

On Saturday, November 30, 2024, PopeRigby <poperigby@mailbox.org> 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.

Oh also, it's the schema is specified as public on this line:
https://gist.github.com/poperigby/fcb59eb6c22c6051800e06a0ec482b
49#file-redacted_all-sql-L4111

Why is it not finding it? I queried public and earth was in there.

Ok, so the error is not emanating from your code but rather the body of the
ll_to_earth function defined in the earthdistance extension.

David J.

On 11/30/24 19:45, David G. Johnston wrote:

On Saturday, November 30, 2024, PopeRigby <poperigby@mailbox.org> wrote:

On 11/30/24 18:41, David G. Johnston wrote:

On Saturday, November 30, 2024, PopeRigby <poperigby@mailbox.org>
wrote:

On 11/30/24 17:27, David G. Johnston wrote:

On Saturday, November 30, 2024, PopeRigby
<poperigby@mailbox.org> 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 also, it's the schema is specified as public on this line:
https://gist.github.com/poperigby/fcb59eb6c22c6051800e06a0ec482b49#file-redacted_all-sql-L4111
<https://gist.github.com/poperigby/fcb59eb6c22c6051800e06a0ec482b49#file-redacted_all-sql-L4111&gt;

Why is it not finding it? I queried public and earth was in there.

Ok, so the error is not emanating from your code but rather the body
of the ll_to_earth function defined in the earthdistance extension.

David J.

By code do you mean my sql file created by pg_dumpall? Sorry, I'm just a
self-hoster so I'm not very well versed in Postgres. I'm just trying to
get my server back online.

On Saturday, November 30, 2024, PopeRigby <poperigby@mailbox.org> wrote:

Ok, so the error is not emanating from your code but rather the body of
the ll_to_earth function defined in the earthdistance extension.

David J.

By code do you mean my sql file created by pg_dumpall? Sorry, I'm just a
self-hoster so I'm not very well versed in Postgres. I'm just trying to get
my server back online.

Yes, your “code” is the user contents of the SQL file.

Your best bet is probably to locate the various “set_config(‘search_path’,
‘’)” commands and add “public” to them. Since you installed earthdistance
to public this should bridge the gap.

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

Ok, so the error is not emanating from your code but rather the body of the
ll_to_earth function defined in the earthdistance extension.

Yeah. That is

CREATE FUNCTION ll_to_earth(float8, float8)
RETURNS earth
LANGUAGE SQL
IMMUTABLE STRICT
PARALLEL SAFE
AS 'SELECT cube(cube(cube(earth()*cos(radians($1))*cos(radians($2))),earth()*cos(radians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth';

which is problematic: the function calls are not schema-qualified and
there's not a really easy way to fix that. There's work afoot to fix
that [1]/messages/by-id/3395418.1618352794@sss.pgh.pa.us, but it's not committed yet let alone in any shipping
version. Nonetheless, your best bet for fixing this might be
to install the earthdistance 1.2 files from the latest patchset in
that thread.

regards, tom lane

[1]: /messages/by-id/3395418.1618352794@sss.pgh.pa.us

On 11/30/24 19:58, Tom Lane wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

Ok, so the error is not emanating from your code but rather the body of the
ll_to_earth function defined in the earthdistance extension.

Yeah. That is

CREATE FUNCTION ll_to_earth(float8, float8)
RETURNS earth
LANGUAGE SQL
IMMUTABLE STRICT
PARALLEL SAFE
AS 'SELECT cube(cube(cube(earth()*cos(radians($1))*cos(radians($2))),earth()*cos(radians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth';

which is problematic: the function calls are not schema-qualified and
there's not a really easy way to fix that. There's work afoot to fix
that [1], but it's not committed yet let alone in any shipping
version. Nonetheless, your best bet for fixing this might be
to install the earthdistance 1.2 files from the latest patchset in
that thread.

regards, tom lane

[1] /messages/by-id/3395418.1618352794@sss.pgh.pa.us

I've applied the following patch to postgres:

/messages/by-id/attachment/122092/0002-earthdistance-sql-functions.patch

I'm still getting this error:

psql:all.sql:4102: ERROR:  type "earth" does not exist
LINE 1: ...ians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth

PopeRigby <poperigby@mailbox.org> writes:

I've applied the following patch to postgres:
/messages/by-id/attachment/122092/0002-earthdistance-sql-functions.patch

Cool. You did actually install the new scripts into your target
installation, right?

I'm still getting this error:
psql:all.sql:4102: ERROR:  type "earth" does not exist
LINE 1: ...ians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth

Hmmm ... a pg_dumpall output script shouldn't really contain that
function body directly; it should just say "CREATE EXTENSION
earthdistance". Is it possible that this database is so old that
it contains a pre-extension (pre-9.1) version of earthdistance?

regards, tom lane

On 12/1/24 12:05, PopeRigby wrote:

On 11/30/24 19:58, Tom Lane wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

Ok, so the error is not emanating from your code but rather the body
of the
ll_to_earth function defined in the earthdistance extension.

Yeah.  That is

CREATE FUNCTION ll_to_earth(float8, float8)
RETURNS earth
LANGUAGE SQL
IMMUTABLE STRICT
PARALLEL SAFE
AS 'SELECT
cube(cube(cube(earth()*cos(radians($1))*cos(radians($2))),earth()*cos(radians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth';

which is problematic: the function calls are not schema-qualified and
there's not a really easy way to fix that.  There's work afoot to fix
that [1], but it's not committed yet let alone in any shipping
version.  Nonetheless, your best bet for fixing this might be
to install the earthdistance 1.2 files from the latest patchset in
that thread.

            regards, tom lane

[1]
/messages/by-id/3395418.1618352794@sss.pgh.pa.us

I've applied the following patch to postgres:

/messages/by-id/attachment/122092/0002-earthdistance-sql-functions.patch

I'm still getting this error:

psql:all.sql:4102: ERROR:  type "earth" does not exist
LINE 1: ...ians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth

The issue is still this:

SELECT pg_catalog.set_config('search_path', '', false);

in the pg_dumpall output.

As was suggested before change the above to:

SELECT pg_catalog.set_config('search_path', 'public', false);

in the pg_dumpall output file.

--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:

On 12/1/24 12:05, PopeRigby wrote:

I'm still getting this error:

psql:all.sql:4102: ERROR:  type "earth" does not exist
LINE 1: ...ians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth

The issue is still this:
SELECT pg_catalog.set_config('search_path', '', false);
in the pg_dumpall output.

We've done that for some time, though.

As was suggested before change the above to:
SELECT pg_catalog.set_config('search_path', 'public', false);
in the pg_dumpall output file.

I'm betting that won't help. The new-in-17 behavior is that
maintenance commands such as CREATE INDEX internally force
a "safe" search_path, regardless of the prevailing setting.

It would be useful to know what is the command at line 4102
of all.sql.

regards, tom lane

On 12/1/24 13:14, Tom Lane wrote:

Adrian Klaver <adrian.klaver@aklaver.com> writes:

On 12/1/24 12:05, PopeRigby wrote:

I'm still getting this error:

psql:all.sql:4102: ERROR:  type "earth" does not exist
LINE 1: ...ians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth

The issue is still this:
SELECT pg_catalog.set_config('search_path', '', false);
in the pg_dumpall output.

We've done that for some time, though.

As was suggested before change the above to:
SELECT pg_catalog.set_config('search_path', 'public', false);
in the pg_dumpall output file.

I'm betting that won't help. The new-in-17 behavior is that
maintenance commands such as CREATE INDEX internally force
a "safe" search_path, regardless of the prevailing setting.

It would be useful to know what is the command at line 4102
of all.sql.

It is here:

https://gist.github.com/poperigby/fcb59eb6c22c6051800e06a0ec482b49

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
);

regards, tom lane

--
Adrian Klaver
adrian.klaver@aklaver.com