Re: Non-text mode for pg_dumpall

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).

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.

cheers

andrew

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