Re: Non-text mode for pg_dumpall - Mailing list pgsql-hackers

On Wed, 28 Jan 2026 at 13:04, tushar <tushar.ahuja@enterprisedb.com> wrote:
>
>
>
> On Tue, Jan 27, 2026 at 9:11 PM Mahendra Singh Thalor <mahi6run@gmail.com> wrote:
>>
>> On Fri, 23 Jan 2026 at 19:07, tushar <tushar.ahuja@enterprisedb.com> wrote:
>> >
>> >
>> >
>> > On Fri, Jan 23, 2026 at 12:21 PM tushar <tushar.ahuja@enterprisedb.com> 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
>> >> 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"
>> >>
>> >>
>> >
>> > 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,
>>
>> Hi,
>> Here I am attaching an updated patch for the review and testing.
>> Thanks Jian for the reporting rebase issue.
>>
>
> Thanks Mahendra, getting a regression error during the restore process after applying this patch.
>
>  [edb@1a1c15437e7c bin]$ ./pg_restore -Ft -C abc1.tar/ -d postgres -p 9000
> pg_restore: error: could not execute query: ERROR:  non-standard string literals are not supported
> Command was: SET standard_conforming_strings = off;
> pg_restore: warning: errors ignored on restore: 1
>
> in earlier patches - this was not coming.
>
> regards,
>

Thanks Andrew for some design related feedback.

Thanks Jian for the offline discussions, reviews, testing and delta patches.

Thanks Tushar for the detailed testing.

Brief about this patch:

new option to pg_dumpall: --format=d/t/c/p directory/tar/custam/plain

If the user gives a non-text format with pg_dumpall command, then the full cluster will be dumped and global objects (roles. tablespaces, databases) will be dumped into toc.glo file in custom format with drop commands and databases will be dumped into a given archive format one by one with oid.tar/oid.dmp/oid files/dir.

When restoring, if the user gives -g(globals-only) option, then creating commands of  only global users/tablespaces/databases will be restored. (no drop commands will be executed)

toc.glo will be executed with -e(exit-on-error=false) and --transaction-size=0 as some user already created. If the user wants to restore a single database, they can restore it by a single dump file. For --clean and -g(globals-only), we added some error cases so that roles/databases/tablespaces will not be dropped.

 

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

Here's an update after a round of review. Most of the changes are pretty minor, but it should get the cfbot all green, with a Windows fix in the tests.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com