MIME-Version: 1.0
In-Reply-To: <CANzqJaBVSAEd8eTKeocbqf1v7hHbA6XAY5t63Sh9vhAm62_+Eg@mail.gmail.com>
References: <6a6439f1-8039-44e2-8fb9-59028f7f2014@mailbox.org>
 <cba48d2c-0987-4233-a86f-2e693677efd3@aklaver.com> <9c5ba566-27b8-4e8c-bf7d-2dc561509991@mailbox.org>
 <CAKFQuwaB5GuUNuyiUbS0ah-LYXepHLPnOHrP7g8RMz8ywOm3zQ@mail.gmail.com>
 <ff94f628-27af-4c3a-9590-cba38bb5d2d1@mailbox.org> <CAKFQuwYQUequkYVS8SLgC1L0TOdWNK=xVyxnPD9RPPwfvYRFSQ@mail.gmail.com>
 <ce6c2e3a-b8da-408c-be44-c4dc87747af3@mailbox.org> <ae7a05a0-3efd-445b-ad2f-4c7a7f9ed15e@aklaver.com>
 <CANzqJaBVSAEd8eTKeocbqf1v7hHbA6XAY5t63Sh9vhAm62_+Eg@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Mon, 9 Dec 2024 21:24:17 -0700
Message-ID: <CAKFQuwY4xnqN7YgASFgwX9ZWJYZO4R3-yQowyv87EnYC0JAJbA@mail.gmail.com>
Subject: Re: Errors when restoring backup created by pg_dumpall
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000bf9f8d0628e2da17"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwY4xnqN7YgASFgwX9ZWJYZO4R3-yQowyv87EnYC0JAJbA%40mail.gmail.com>
Precedence: bulk

--000000000000bf9f8d0628e2da17
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Monday, December 9, 2024, Ron Johnson <ronljohnsonjr@gmail.com> wrote:

> On Sat, Nov 30, 2024 at 10:36=E2=80=AFPM Adrian Klaver <adrian.klaver@akl=
aver.com>
> 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=3D{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.

--000000000000bf9f8d0628e2da17
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Monday, December 9, 2024, Ron Johnson &lt;<a href=3D"mailto:ronljohnsonj=
r@gmail.com">ronljohnsonjr@gmail.com</a>&gt; wrote:<br><blockquote class=3D=
"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding=
-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr">On Sat, Nov 30, 2024 at 10:36=
=E2=80=AFPM Adrian Klaver &lt;<a href=3D"mailto:adrian.klaver@aklaver.com" =
target=3D"_blank">adrian.klaver@aklaver.com</a>&gt; wrote:</div><div class=
=3D"gmail_quote"><div>[snip]=C2=A0</div><blockquote class=3D"gmail_quote" s=
tyle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pad=
ding-left:1ex">In future schema qualify all references.<br>
<br>
For now in the dump file you could search for<br>
<br>
SELECT pg_catalog.set_config(&#39;search_<wbr>path&#39;, &#39;&#39;, false)=
;<br>
<br>
and set to<br>
<br>
SELECT pg_catalog.set_config(&#39;search_<wbr>path&#39;, &#39;public&#39;, =
false);<br></blockquote></div><div><br clear=3D"all"></div><div>What if thi=
s had been a pg_dump --format=3D{custom,directory} backup?</div></div></blo=
ckquote><div><br></div><div>pg_restore has a mode where it can dump out SQL=
 to a script instead of directly restoring to the database.</div><div><br><=
/div><div>David J.</div><div><br></div>

--000000000000bf9f8d0628e2da17--