Re: Non-text mode for pg_dumpall

On Mon, 31 Mar 2025 at 19:27, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
>
> On 2025-03-31 Mo 5:34 AM, Mahendra Singh Thalor wrote:
> >
> > >
> > > There are a couple of rough edges, though.
> > >
> > > First, I see this:
> > >
> > >
> > > andrew(at)ub22arm:inst $ bin/pg_restore -C -d postgres
> > > --exclude-database=regression_dummy_seclabel
> > > --exclude-database=regression_test_extensions
> > > --exclude-database=regression_test_pg_dump dest
> > > pg_restore: error: could not execute query: "ERROR: role "andrew"
> > > already exists
> > > "
> > > Command was: "
> > >
> > > --
> > > -- Roles
> > > --
> > >
> > > CREATE ROLE andrew;"
> > > pg_restore: warning: errors ignored on global.dat file restore: 1
> > > pg_restore: error: could not execute query: ERROR: database "template1"
> > > already exists
> > > Command was: CREATE DATABASE template1 WITH TEMPLATE = template0
> > > ENCODING = 'SQL_ASCII' LOCALE_PROVIDER = libc LOCALE = 'C';
> > >
> > >
> > > pg_restore: warning: errors ignored on database "template1" restore: 1
> > > pg_restore: error: could not execute query: ERROR: database "postgres"
> > > already exists
> > > Command was: CREATE DATABASE postgres WITH TEMPLATE = template0 ENCODING
> > > = 'SQL_ASCII' LOCALE_PROVIDER = libc LOCALE = 'C';
> > >
> > >
> > > pg_restore: warning: errors ignored on database "postgres" restore: 1
> > > pg_restore: warning: errors ignored on restore: 3
> > >
> > >
> > >
> > > It seems pointless to be trying to create the rolw that we are connected
> > > as, and we also expect template1 and postgres to exist.
> >
> > Thanks Andrew for the updated patches.
> >
> > Here, I am attaching a delta patch which solves the errors for the
> > already created database and we need to reset some flags also. Please
> > have a look over this delta patch and merge it.
> >
> > If we want to skip errors for connected user (CREATE ROLE username),
> > then we need to handle it by comparing sql commands in
> > process_global_sql_commands function or we can compare errors after
> > executing it.
> > delta_0002* patch is doing some handling but this is not a proper fix.
> >
> > I think we can merge delta_0001* and later, we can work on delta_0002.
>
>
> Yes, delta 1 looks OK, except that the pstrdup() calls are probably
> unnecessary. Delta 2 needs some significant surgery at least. I think we
> can use it as at least a partial fix, to avoid trying to create the role
> we're running as (Should use PQuser() for that rather than cparams.user).

Thanks for the quick review.

I fixed the above comments and made 2 delta patches. Please have a
look over these.

> BTW, if you're sending delta patches, make sure they don't have .patch
> extensions. Otherwise, the CFBot gets upset. I usually just add .noci to
> the file names.

Sure. I will also use .noci. Thanks for feedback.

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