public inbox for [email protected]
help / color / mirror / Atom feedRe: Errors when restoring backup created by pg_dumpall
5+ messages / 3 participants
[nested] [flat]
* Re: Errors when restoring backup created by pg_dumpall
@ 2024-12-01 03:36 Adrian Klaver <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Adrian Klaver @ 2024-12-01 03:36 UTC (permalink / raw)
To: PopeRigby <[email protected]>; David G. Johnston <[email protected]>; +Cc: pgsql-general
On 11/30/24 19:26, PopeRigby 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?
>
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
[email protected]
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Errors when restoring backup created by pg_dumpall
@ 2024-12-10 04:02 Ron Johnson <[email protected]>
parent: Adrian Klaver <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Ron Johnson @ 2024-12-10 04:02 UTC (permalink / raw)
To: pgsql-general
On Sat, Nov 30, 2024 at 10:36 PM Adrian Klaver <[email protected]>
wrote:
[snip]
> 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);
>
What if this had been a pg_dump --format={custom,directory} backup?
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Errors when restoring backup created by pg_dumpall
@ 2024-12-10 04:24 David G. Johnston <[email protected]>
parent: Ron Johnson <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2024-12-10 04:24 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: pgsql-general
On Monday, December 9, 2024, Ron Johnson <[email protected]> wrote:
> On Sat, Nov 30, 2024 at 10:36 PM Adrian Klaver <[email protected]>
> wrote:
> [snip]
>
>> 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);
>>
>
> What if this had been a pg_dump --format={custom,directory} backup?
>
pg_restore has a mode where it can dump out SQL to a script instead of
directly restoring to the database.
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Errors when restoring backup created by pg_dumpall
@ 2024-12-10 04:54 Ron Johnson <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Ron Johnson @ 2024-12-10 04:54 UTC (permalink / raw)
To: pgsql-general
On Mon, Dec 9, 2024 at 11:24 PM David G. Johnston <
[email protected]> wrote:
> On Monday, December 9, 2024, Ron Johnson <[email protected]> wrote:
>
>> On Sat, Nov 30, 2024 at 10:36 PM Adrian Klaver <[email protected]>
>> wrote:
>> [snip]
>>
>>> 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);
>>>
>>
>> What if this had been a pg_dump --format={custom,directory} backup?
>>
>
> pg_restore has a mode where it can dump out SQL to a script instead of
> directly restoring to the database.
>
That Would Be Very, Very Bad if this were a database big enough to have
required a multi-threaded dump.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Errors when restoring backup created by pg_dumpall
@ 2024-12-10 05:31 Adrian Klaver <[email protected]>
parent: Ron Johnson <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Adrian Klaver @ 2024-12-10 05:31 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; pgsql-general
On 12/9/24 20:54, Ron Johnson wrote:
> On Mon, Dec 9, 2024 at 11:24 PM David G. Johnston
> <[email protected] <mailto:[email protected]>> wrote:
>
> On Monday, December 9, 2024, Ron Johnson <[email protected]
> <mailto:[email protected]>> wrote:
>
> On Sat, Nov 30, 2024 at 10:36 PM Adrian Klaver
> <[email protected] <mailto:[email protected]>>
> wrote:
> [snip]
>
> 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);
>
>
> What if this had been a pg_dump --format={custom,directory} backup?
>
>
> pg_restore has a mode where it can dump out SQL to a script instead
> of directly restoring to the database.
>
>
> That Would Be Very, Very Bad if this were a database big enough to have
> required a multi-threaded dump.
From the original post:
https://www.postgresql.org/message-id/6a6439f1-8039-44e2-8fb9-59028f7f2014%40mailbox.org
"My HDD recently failed so I'm trying to restore my backup, but I'm
running into some errors."
There really was no choice.
Not tested but in the pg_restore case I could see at least trying:
1) pg_restore -s -f schema_definitions.sql custom_format_file
2) Then making the change in the search_path in schema_definitions.sql
and then load the schema in the database using psql
3) Then pg_restore -a -j <number_of_jobs> custom_format_file.
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-12-10 05:31 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-12-01 03:36 Re: Errors when restoring backup created by pg_dumpall Adrian Klaver <[email protected]>
2024-12-10 04:02 ` Ron Johnson <[email protected]>
2024-12-10 04:24 ` David G. Johnston <[email protected]>
2024-12-10 04:54 ` Ron Johnson <[email protected]>
2024-12-10 05:31 ` Adrian Klaver <[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