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.96) (envelope-from ) id 1vfCTE-005C2z-1I for pgsql-hackers@arkaria.postgresql.org; Mon, 12 Jan 2026 07:36:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vfCTC-00DclP-1k for pgsql-hackers@arkaria.postgresql.org; Mon, 12 Jan 2026 07:36:11 +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.96) (envelope-from ) id 1vfCTC-00DclG-0H for pgsql-hackers@lists.postgresql.org; Mon, 12 Jan 2026 07:36:10 +0000 Received: from mail-ed1-x52b.google.com ([2a00:1450:4864:20::52b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vfCTA-005gUi-0A for pgsql-hackers@lists.postgresql.org; Mon, 12 Jan 2026 07:36:08 +0000 Received: by mail-ed1-x52b.google.com with SMTP id 4fb4d7f45d1cf-64b7a38f07eso9388520a12.0 for ; Sun, 11 Jan 2026 23:36:07 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb.com; s=google; t=1768203364; x=1768808164; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=H13Kv8gRNxBNKRz+ZxyeTdmnKcRSlAmgaRqQ6ticdME=; b=astDUK1CNDu0Uq+xj5oTbiSMC7cPFszV7difu50uJBkN9yTDzfUWnjgfhpkvNpWnQG EUTt0R+obhinT3sbuFx8BxhmdNsJPhzTt1suylAQ/C2zkPSyJnKeGnUI4VLL+yC67kvC 9I/Jq9wM0kaNJVFJgMDYZ3PYZ5uS6xFBpS3fDia13205N5tm7o7CgKf/uuuerANa932b 6aj7MU9Gy6Wc2CNLjn+QmFbtD7v8MRaFVlxMyglREndM3S0Dn+HWDzQPbNCYFGBI6ZdD ZBehnJi2AcGVI72jCdAwrXVPgxxe7RW/2dPnLUJ7A3+mXcaZf9Cte4mqG139tPZAed1N CA1w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1768203364; x=1768808164; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=H13Kv8gRNxBNKRz+ZxyeTdmnKcRSlAmgaRqQ6ticdME=; b=J3gzhemd4oQh48auA2/JdvGqaX7Iany9LEAeCChI5T/IyGryr35wrKslbKUAKbG6cZ +8wadAQoNfK6NIOpGiWcIJO+8akZItNbqrsMWdtBCMX+u5ByYdD27h4e+eiwa+eYn2cx M2yUsAZGG/shdyfkduTWfh7vng9EtlLhZvAcubNndrfrxB576DKkbXCzwkQNoSFIdkMK 8peOjwXFznQHHa+q0HHSakBwPxhAjv9MHCw5d3lnwwxmsozrNUY8JxrxuSQW3vYe5dVL 4kdkFX1Uso1LBUU8w73wS+NFYa4uc42rOkKqBbpZtmkjAdmpkiXIA1tU7gpCm3ya9CbB UL1A== X-Forwarded-Encrypted: i=1; AJvYcCWizFi49buk1CxlfTzu0Oe4WCXzgFdosFzQnGscwRO8++ZMWNw5l/b9a3HCF/72JlVMWnlAk70rifEKmR8q@lists.postgresql.org X-Gm-Message-State: AOJu0Yzpuoq3wL6ypG90osP9NgEEgT04El6fnyRwA8CUIc629ZSo9T2e CuHpyUw47ZAYz9uUMdSLnapb9EYKQ2dvUxkRP71ze0pA05yPF8ethbkIOHoJmLXdNT8bRmuekTE /iZp2LafMblYPuqEQQCsmBxrJH7F6yoHwtawRsbsf X-Gm-Gg: AY/fxX6sks4MLEzTRi/7mEaq0r0OXiREIaRdf/Ng+XBmPiKkDg3mdcUNEW4ZXe9bbdH KEwZpJqMTo6NNGvS6m7p/F75MZ7PsZc30hABVReZl261syiypMtqOkSgfqCIoq7C0UEn4nDfnV4 6x/nL2t19jgsNsZy7tJgu6WvmuJzv6i+lXZ7eeMCf8QRLQMsG1ftYcU102EDGYpqukA/hNC95rF JTMaKsDyl+gilXb/XQRw+c3NFJFZxMz/kaPFF/wvm8384lBHRed1zefgzmTLpKLqk2qMdrYaA== X-Google-Smtp-Source: AGHT+IFiewyBtgJSs6QzoLk4hTJpndNE8rWQoZHDys8BbEPbHGi39/hJcyJ/LDXxW6Ex+Q3JuIaynHN5coJGcrJ0ALI= X-Received: by 2002:a05:6402:4601:20b0:64d:ab6b:17cf with SMTP id 4fb4d7f45d1cf-65097e78169mr12392799a12.33.1768203364483; Sun, 11 Jan 2026 23:36:04 -0800 (PST) MIME-Version: 1.0 References: <3f22a8bb-29e8-40cc-97a1-309181da2c13@dunslane.net> <2bed001a-462c-42da-9a6b-3c7884502932@dunslane.net> <20250824010811.4d.nmisch@google.com> <82eb35b8-7f07-493b-b689-0934919e1dc3@dunslane.net> <17555e46-4fb2-4265-90e0-95cb8ed584a6@dunslane.net> In-Reply-To: From: tushar Date: Mon, 12 Jan 2026 13:05:52 +0530 X-Gm-Features: AZwV_QgEMaScD8zw5vaK0ndey0zbaUNdWbIcvXQz6Vqxe9yNqhQqofkwRYP-81Q Message-ID: Subject: Re: Non-text mode for pg_dumpall To: Mahendra Singh Thalor Cc: jian he , Vaibhav Dalvi , pgsql-hackers@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000006e54c706482beda0" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000006e54c706482beda0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, Jan 7, 2026 at 1:52=E2=80=AFPM tushar wrote: > > > On Tue, Jan 6, 2026 at 11:56=E2=80=AFAM Mahendra Singh Thalor > wrote: > >> >> >> >> We have another thread for this. We have patches also. Last year, we >> planned to block these databases at creation time. >> >> > >> > It's probably harmless, we connect to the databases further down to do >> actual work. But it's also not nice. The toc.glo seems to have a bunch o= f >> extraneous entries of type COMMENT and CONNECT. Why is that? As far as >> poible this should have output pretty much identical to a plain pg_dump= all. >> > >> > >> > cheers >> > >> > >> > andrew >> >> If we don't dump those comments in non-text format, then the output of >> "pg_restore -f filename dump_non_text" will not be the same as the >> plain dump of pg_dumpall. >> >> Here, I am attaching an updated patch for the review and testing. >> >> > Hi Mahendra, > > I found a scenario in which the table is not > restored if --transaction-size switch is used at the time of pg_restore > operation > > Please refer this scenario: > Case A --pg_restore operation with "--transaction-size" against the dump > (taken using pg_dump) - > create a table ( create table t(n int); ) > perform pg_dump ( ./pg_dump -Ft postgres -f xyz.tar) > create a database (create database test;) > perform pg_restore using switch "--transaction-size" ( ./pg_restore > --transaction-size=3D1 -d test xyz.tar) > table is restored into test database > > Case B --pg_restore operation with "--transaction-size" against the dump > (taken using pg_dumpall) - > create a table ( create table t(n int); ) > perform pg_dumpall ( ./pg_dumpall -Ft -f abc.tar) > create a new cluster, start the server against a different port > perform pg_restore using switch "--transaction-size" (./pg_restore -Ft > --transaction-size=3D10 -d postgres abc.tar -p 9000 -C) > table is not restored > > if i remove --transaction-size switch then this works. > > right, it seems like we are encountering a recurring issue where pg_restore operations fail if the user account already exists in the target database and due to that got this error: pg_restore: error: could not execute query: ERROR: role "xyz" already exists We need to implement a check to handle existing roles gracefully. regards, --0000000000006e54c706482beda0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Wed, Jan 7, = 2026 at 1:52=E2=80=AFPM tushar <tushar.ahuja@enterprisedb.com> wrote:

On Tue, Jan 6, 2026 at 11:56=E2=80=AFAM Mahendra Singh Thalor <mahi6run@gmail.com>= wrote:



We have another thread for this. We have patches also. Last year, we
planned to block these databases at creation time.

>
> It's probably harmless, we connect to the databases further down t= o do actual work. But it's also not nice. The toc.glo seems to have a b= unch of extraneous entries of type COMMENT and CONNECT. Why is that? As far= as poible this should have output pretty much=C2=A0 identical to a plain p= g_dumpall.
>
>
> cheers
>
>
> andrew

If we don't dump those comments in non-text format, then the output of<= br> "pg_restore -f filename dump_non_text" will not be the same as th= e
plain dump of pg_dumpall.

Here, I am attaching an updated patch for the review and testing.


Hi Mahendra,=C2=A0

= I found a scenario=C2=A0 in which the table is not restored=C2=A0if=C2=A0--= transaction-size switch is used=C2=A0 at the time of pg_restore operation= =C2=A0

Please refer this scenario:
Case = A --pg_restore operation with "--transaction-size" against the=C2= =A0 dump (taken using pg_dump) -
create a table ( create table t(= n int); )=C2=A0
perform pg_dump ( ./pg_dump -Ft postgres -f xyz.t= ar)
create a database (create database test;)=C2=A0
per= form pg_restore using switch "--transaction-size" ( ./pg_restore = --transaction-size=3D1 -d test xyz.tar)=C2=A0
table is restored i= nto test database=C2=A0

Case B --pg_restore operat= ion with "--transaction-size" against the=C2=A0 dump (taken using= pg_dumpall) -
create a table ( create table t(n int); )=C2= =A0
perform pg_dumpall ( ./pg_dumpall -Ft -f abc.tar)
c= reate a new cluster, start the server against a different port=C2=A0
<= div>perform pg_restore using switch "--transaction-size" (./pg_re= store -Ft --transaction-size=3D10 -d postgres abc.tar -p 9000 -C)
table is not restored=C2=A0

if i remove --transac= tion-size switch then this works.=C2=A0

right, it seems like we are encountering a recurring i= ssue where pg_restore operations fail if the user account already exists=C2= =A0
in the target database and due to that got this error:=C2=A0<= /div>
pg_restore: error: could not execute query: ERROR: =C2=A0role &qu= ot;xyz" already exists=C2=A0

We need to imple= ment a check to handle existing roles gracefully.

= regards,

--0000000000006e54c706482beda0--