Re: Non-text mode for pg_dumpall

On 2025-03-31 Mo 12:16 PM, Mahendra Singh Thalor wrote:
> 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. Here are patches that contain (my version of) all the cleanups.
With this I get a clean restore run in my test case with no error messages.

cheers

andrew

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