Subject: Re: Non-text mode for pg_dumpall

From: tushar <[email-masked]>
Date: Wed, 7 Jan 2026 13:52:58 +0530

On Tue, Jan 6, 2026 at 11:56 AM Mahendra Singh Thalor <[email-masked]>
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 of
> 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_dumpall.
> >
> >
> > 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=1 -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=10 -d postgres abc.tar -p 9000 -C)
table is not restored

if i remove --transaction-size switch then this works.

regards,

---

From: tushar <[email-masked]>
Date: Mon, 12 Jan 2026 13:05:52 +0530

On Wed, Jan 7, 2026 at 1:52 PM tushar <[email-masked]> wrote:

> 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,

---

From: Mahendra Singh Thalor <[email-masked]>
Date: Sat, 17 Jan 2026 01:39:03 +0530

Thanks Tushar for the testing.

On Wed, 7 Jan 2026 at 13:53, tushar <[email-masked]> wrote:
>
>
>
> On Tue, Jan 6, 2026 at 11:56 AM Mahendra Singh Thalor <[email-masked]> wrote:
>>
>>
>>
>>
>> > 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 of 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_dumpall.
>> >
>> >
>> > cheer
>> >
>> >
>> > andrew
>>

Thanks Andrew for the feedback.

In the attached patch, I fixed some comments. In the next version, I
will try to make it much identical to a plain pg_dumpall.

> I found a scenario  in which the table is not restored if --transaction-size switch is used  at the time of pg_restore operation
>
> Case A --pg_restore operation with "--transaction-size" against the  dump (taken using pg_dump) -
> perform pg_restore using switch "--transaction-size" ( ./pg_restore --transaction-size=1 -d test xyz.tar)
> Case B --pg_restore operation with "--transaction-size" against the  dump (taken using pg_dumpall) -
> perform pg_restore using switch "--transaction-size" (./pg_restore -Ft --transaction-size=10 -d postgres abc.tar -p 9000 -C)
> table is not restored
>

Fixed.

On Mon, 12 Jan 2026 at 13:39, tushar <[email-masked]> wrote:
>
>
>
> On Tue, Jan 6, 2026 at 11:56 AM Mahendra Singh Thalor <[email-masked]> wrote:
>>
>>
>> Note: some of the review comments are still not fixed. I am working on
>> those and will post an updated patch.
>>
> Hi Mahendra,
> Please refer this scenario - if we are using with "--jobs" switch then getting an error at the time of restore
>
> Create a table (  create table t(n int); insert into t values (1);  )
> Perform pg_dumpall ( ./pg_dumpall -Fd -f abc1.dr )
> Create a new cluster, start the server against a different port
> Perform pg_restore using switch "--jobs 4 " (./pg_restore -j 4 -d postgres abc1.dr/  -p 9000 -C )
>
> "
> [edb@1a1c15437e7c bin]$ ./pg_restore -j 4 -d postgres abc1.dr/  -p 9000 -C
> pg_restore: error: could not execute query: ERROR:  role "edb" already exists
> Command was: CREATE ROLE edb;
> ALTER ROLE edb WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS;
>
>
> pg_restore: error: could not execute query: ERROR:  syntax error at or near "\"
> LINE 1: \connect template1
>         ^
> Command was: \connect template1
>
>
>
> pg_restore: error: could not execute query: ERROR:  syntax error at or near "\"
> LINE 1: \connect postgres
>         ^
> Command was: \connect postgres
>
>
>
> pg_restore: warning: errors ignored on restore: 3
> [edb@1a1c15437e7c bin]$
> "
>
> regards,

Fixed this syntax error but user error is still there for parallel
mode(for non-parallel, fixed). This will be fixed in the next version.

-- 
Thanks and Regards
Mahendra Singh Thalor
EnterpriseDB: http://www.enterprisedb.com

---

From: tushar <[email-masked]>
Date: Fri, 23 Jan 2026 12:21:17 +0530

On Sat, Jan 17, 2026 at 1:39 AM Mahendra Singh Thalor <[email-masked]>
wrote:

> >> > 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
> of extraneous entries of type COMMENT and CONNECT. Why is that? As far as
> > I found a scenario  in which the table is not restored if
> --transaction-size switch is used  at the time of pg_restore operation
> >
Thanks Mahendra, a minor  observation -  The pg_restore output shows a
double slash in the map.dat path (e.g., abc.tar//map.dat).
While it doesn't break the restore, we may want to clean up the path
joining logic.

[edb@1a1c15437e7c bin]$ ./pg_restore -Ft -C abc.tar/ -d postgres -p 9011
 -U  ed -v
pg_restore: found database "template1
" (OID: 1) in file "abc.tar//map.dat"
pg_restore: found database "postgres
" (OID: 5) in file "abc.tar//map.dat"

regards,

---

From: tushar <[email-masked]>
Date: Fri, 23 Jan 2026 19:06:47 +0530

> " (OID: 5) in file "abc.tar//map.dat"
Please refer to this scenario where - Objects  created under template1 and
the postgres database by a specific user are failing during a cross-cluster
restore.
When restoring to a new cluster as a different superuser, pg_restore throws
the error: ERROR: role "edb" does not exist.
It appears the restore is attempting to preserve the original ownership of
template1 objects even when the target environment lacks those specific
roles.

*Steps to reproduce:*
initdb ( ./initdb -U edb -D data) , start the server , connect to postgres
and  template1 database one by one and  create
this table ( create table test(n int); )
perform pg_dumpall operation ( ./pg_dumpall -Ft -f abc.tar)
initdb (./initdb -U xyz) , start the server , create a database ( create
database abc;)
perform pg_restore operation ( ./pg_restore -Ft -C abc.tar/ -d postgres -p
9033 -U xyz)
--getting an error,  table 'test' will be created on 'template1' database
but failed to create on an another database ( in this case - 'abc' database)

regards,

---

From: Mahendra Singh Thalor <[email-masked]>
Date: Tue, 27 Jan 2026 21:10:50 +0530

On Fri, 23 Jan 2026 at 19:07, tushar <[email-masked]> wrote:
>
>
>
> On Fri, Jan 23, 2026 at 12:21 PM tushar <[email-masked]> wrote:
>>
>>
>> Thanks Mahendra, a minor  observation -  The pg_restore output shows a double slash in the map.dat path (e.g., abc.tar//map.dat).
>> While it doesn't break the restore, we may want to clean up the path joining logic.
>>
>> [edb@1a1c15437e7c bin]$ ./pg_restore -Ft -C abc.tar/ -d postgres -p 9011  -U  ed -v
> Please refer to this scenario where - Objects  created under template1 and the postgres database by a specific user are failing during a cross-cluster restore.
> When restoring to a new cluster as a different superuser, pg_restore throws the error: ERROR: role "edb" does not exist.
> It appears the restore is attempting to preserve the original ownership of template1 objects even when the target environment lacks those specific roles.
>
> Steps to reproduce:
> initdb ( ./initdb -U edb -D data) , start the server , connect to postgres and  template1 database one by one and  create
> initdb (./initdb -U xyz) , start the server , create a database ( create database abc;)
> perform pg_restore operation ( ./pg_restore -Ft -C abc.tar/ -d postgres -p 9033 -U xyz)
> --getting an error,  table 'test' will be created on 'template1' database but failed to create on an another database ( in this case - 'abc' database)
>
> regards,

Hi,
Here I am attaching an updated patch for the review and testing.
Thanks Jian for the reporting rebase issue.