Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tKsGg-00BJ4g-8Y for pgsql-general@arkaria.postgresql.org; Tue, 10 Dec 2024 04:54:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tKsGd-00Fm1A-AC for pgsql-general@arkaria.postgresql.org; Tue, 10 Dec 2024 04:54:40 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tKsGc-00Fm12-Ua for pgsql-general@lists.postgresql.org; Tue, 10 Dec 2024 04:54:40 +0000 Received: from mail-oa1-x2e.google.com ([2001:4860:4864:20::2e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tKsGb-0021Jw-BW for pgsql-general@postgresql.org; Tue, 10 Dec 2024 04:54:38 +0000 Received: by mail-oa1-x2e.google.com with SMTP id 586e51a60fabf-29737adb604so2629751fac.1 for ; Mon, 09 Dec 2024 20:54:37 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1733806476; x=1734411276; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=/ua62dvVyrgb+ntWvLsJCUIpFZsaP/FFj0YVNJB9wAM=; b=JebiWgdw6s+lFD3FTT+2cxxckR7u/+jcDcf4om59lF0X19Rp84083OKmhMl4oe0WA/ NzhypsmokmOqtw10mEm7GHWfPEuVbLJm1bwCkXc+OF15QHvYJevD5+RAOExrpz7A63ds oR/zsp/Hle6KLCdNH2T/geXxLwP0sMYS+rEcooes3T94F6Svj5lIgEWyrY9Sso4+sjFY 84Gd4qCAkWtmcbY+Ig+b/e/p5/Q5dn8HDguhaeCxIlTiGQaf1E7YSyS6223wAMcUk6VC 3UWhie/Xs1+DGru40aVrpSGCjSWwUYWWykPKkNBXqUpQvNjiqXvQ+LXampG+DAMruOYi /CHw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1733806476; x=1734411276; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=/ua62dvVyrgb+ntWvLsJCUIpFZsaP/FFj0YVNJB9wAM=; b=Zf6xSO5DlqIVSl1tXTlcsJr38EgR89YswWt1vqM4+uF41fkH2IHlu0/VzTpmm+4+DP TJj9ASge+ATNGEM7tHoAE0lJk2QH3EoFy0l1OAYGbR2QS2O/3tWZgfQrS3Owv41iuf/6 2eOVO01ZbPJ3QBqQFjoMdnXYDTbGQ3zOXVLO7AbRr5dljDdtMzRPjGUKMbZf+GZplZrA PlIrB/5tzrnseOu5fQ1oBLx62Jk+X33FFLBTfc7fLv5JkeI7wGdRTPuDTAWpkCIOzV9Z ojHqXJ+/NkyqrqXLl8VXFqG7jx+oEjioAQHUQFFfws228YqArGJkrv7IbkEvjNNVCJCq ML4Q== X-Gm-Message-State: AOJu0YzGEXtw12mA7829ya1EkqYEvvZYE4Z3OfDsZjubsIuEjoz7obD1 3Qw5ULD73VDkp5kpFodIWESribXbEIboRRvzuJxz4VjOQBjIaPDb2uQAf/dHB3xypyCfe1y7LZ4 RR6kQXl3eOcShJMsq3DjrfbQ3KTSlTw== X-Gm-Gg: ASbGncspdEUAz5n9RysW1EdRwU5opxKmlyzAhuJhWXZXPDIgC7kwsjHpwICJEGC1Khw +ze4B7Kpr4Mr6K/jl8lT3INJ1VIHGiEopfZGMqqdCGDrybWD8GgLqKWO0MZFXB3ONrxjw8g== X-Google-Smtp-Source: AGHT+IFTN4ZQEVBkcteD6aqwRif1Axkt/uHsbmA7N4aqscwU1F+TDJUvx70vPDAedt08wLIYQY1hX3xSHrgJsmox4KI= X-Received: by 2002:a05:6871:64b:b0:29e:460d:f74d with SMTP id 586e51a60fabf-29f72de751amr12845092fac.0.1733806476389; Mon, 09 Dec 2024 20:54:36 -0800 (PST) MIME-Version: 1.0 References: <6a6439f1-8039-44e2-8fb9-59028f7f2014@mailbox.org> <9c5ba566-27b8-4e8c-bf7d-2dc561509991@mailbox.org> In-Reply-To: From: Ron Johnson Date: Mon, 9 Dec 2024 23:54:25 -0500 Message-ID: Subject: Re: Errors when restoring backup created by pg_dumpall To: pgsql-general Content-Type: multipart/alternative; boundary="0000000000002274110628e3472d" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000002274110628e3472d Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Dec 9, 2024 at 11:24=E2=80=AFPM David G. Johnston < david.g.johnston@gmail.com> wrote: > On Monday, December 9, 2024, Ron Johnson wrote: > >> On Sat, Nov 30, 2024 at 10:36=E2=80=AFPM Adrian Klaver >> 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. > That Would Be Very, Very Bad if this were a database big enough to have required a multi-threaded dump. --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --0000000000002274110628e3472d Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Mon, Dec 9, 2024 at 11:24=E2=80=AFPM D= avid G. Johnston <david.g.= johnston@gmail.com> wrote:
On Monday, December 9, 2024, Ron J= ohnson <ron= ljohnsonjr@gmail.com> wrote:
On Sat, Nov 30, 2024 at 10:= 36=E2=80=AFPM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
[snip]=C2=A0
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.

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.
<Redact= ed> lobster!
--0000000000002274110628e3472d--