Message-ID: <f2b1185a-1688-4b05-ad86-39b467af835a@dunslane.net>
Date: Mon, 31 Mar 2025 13:16:19 -0400
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Non-text mode for pg_dumpall
To: Mahendra Singh Thalor <mahi6run@gmail.com>
Cc: jian he <jian.universality@gmail.com>,
 =?UTF-8?Q?=C3=81lvaro_Herrera?= <alvherre@alvh.no-ip.org>,
 Srinath Reddy <srinath2133@gmail.com>, pgsql-hackers@lists.postgresql.org
References: 
 <CAKYtNAr7ToCBSLO2Mm6NGFTZvbdTAFNgjv_YoRqJJghC3n1MiQ@mail.gmail.com>
 <202503111705.xy7fddu36qae@alvherre.pgsql>
 <CACJufxG44zd+KV4B3_0d+Lf2fwnDDptX-DQRqn-o9ukmXEWeNw@mail.gmail.com>
 <4ef51faa-993f-46ea-9e68-7baf736c07b8@dunslane.net>
 <CAKYtNAoebXn-CC_cgv=-DxL1s7hPvx-1UW7CEfsiJZ8fbD6pjg@mail.gmail.com>
 <a29754b5-e926-4385-a7e4-6a3b4c661fe1@dunslane.net>
 <763d1c6e-9298-4bac-9bea-9331db78a154@dunslane.net>
 <CAKYtNAq8PeyK_bhqX7kR_WPp0x+Z3GAVPnqyBCxe5SVa1MRB7A@mail.gmail.com>
 <bca729b1-87c6-45f1-9241-400acf3074df@dunslane.net>
 <CAKYtNApiDSUvBT-abuFUdjuayrbG-tmrxxqn8WrwLKRvXV3J+w@mail.gmail.com>
 <ff600e57-d0aa-4fe9-b86d-b4a353091a9c@dunslane.net>
 <CAKYtNAoBUAP0k_eRvs6J-b61wSqgQRiASHFj-SNzcY_tFvGj3Q@mail.gmail.com>
From: Andrew Dunstan <andrew@dunslane.net>
Content-Language: en-US
Autocrypt: addr=andrew@dunslane.net; keydata=
 xsBNBE7KWFkBCAClridxur2AIc7eW2AR7izbfp3EnNefie2HbLF0izW5Ik5UjX2HBXBx4syI
 gY6b0ugohXrr274+baoAlvSbq6cAoQuEVrk5IZFzt20b1Xkx65FwGSEj526yiKLocqkJceSq
 Xr9xcA5SGY+FZv441chh5SU92v4q6z+6LPpoHOh97ptAVXZYNTtU0LevyvD5lja0TzbvJm6C
 eFXitJfnm1pLEr0DGJCR/iUOl/N62Kh4855zZC7NHIjQHPOvV5Stz/l5ilDhvGVk+xkXFPys
 SjZoUr1rXhYLpiyi5sR0X9FHXT0KnGuz1F5ERO7ZTLSSQ6fJwPj6gOk9K+vvoKvoeql5ABEB
 AAHNJEFuZHJldyBEdW5zdGFuIDxhbmRyZXdAZHVuc2xhbmUubmV0PsLAmwQTAQgARQIbAwIX
 gAIZAQULCQgHAgMiAgEGFQoJCAsCBBYCAwECHgcWIQTkPlhGHfx8v0RpFaWZ+n/LWfw7gQUC
 ZFlxxwUJGVGAbgAKCRCZ+n/LWfw7gXikB/9ZdcUy6CTBFIIuL/bVsc1eLEW/gJBjJBF6HxNY
 xgEkAgXAp4Lg4A5U+QB9GouFr7+GYxF0BU4hzoGhNPUWltxnHdMWP8nC/38LAqgMi8L/bbsm
 HW5YPBdWYaAZAPJQVfOAgjTbRUb26KSprpyrrJKW0ZmrZfjhNPcQ72jpWzoPLQqx2X6B0fru
 1jq+cBh8lb6r1mJTim1T3JIn+F/v5VpdQS+EL8xqsHkfzKjIPsW3CIXpkypSk6saA55Rkkbl
 26AW8ftPVB0Q6Lnn6FLt9CP0MGNixBQ55yq8r1K+nCBvCCjvQjM8RDm0UUum0WNl+ifQgTLO
 E8TWEnwVtkBf+3QWzsBNBE7KWFkBCADRnOM0FCzsYW6jtncg+dWIagjUZpvaClmqn/sJluLa
 Q3v1VXMQJzYs3eC1gh386W+XBwLRpDj3jzH81lX+p73Re3d3oJW7X+ffsxuzu5ZVdMUkqBYo
 nkAbKxr6gyJ12F/+JkUVzLcoTN+d/7YsQvUVi7NaKH8mJgjz112O4fUe3p9wfAaFa0RXHc5S
 GPzRTYRRlv/XZBIho4J2tkZOnteZJZ+GbxQVlINt6fd8P6al3MWOvpP/ExJPguEfjOsO6Njy
 xjo3WfpD4lHMOR/Oc3/8mScEF84rF2jXbsFgelWnbPWAvXY+pD0dXOFRkagGmC/viwBDqq5b
 5tk76kKmUbZxABEBAAHCwHwEGAEIACYCGwwWIQTkPlhGHfx8v0RpFaWZ+n/LWfw7gQUCZFlx
 5wUJGVGAjgAKCRCZ+n/LWfw7gf+iB/4g8CPY5jihf5r/8EsoIGe2H+dpVmpPF8YGBzTIvCz/
 fQoOq8AX/pE76QEuFnFZWfjw+wgBXgCVmkox2Eflkk6z4ND3pcwGZ6CfCxTQCDk/dij+2DQ4
 6bmDCy/sBgcbz9mTpoLC11HLoPae6YN9nBNQRZDcEFEu54OaVOqlIdbA6m+POIBCXZdHOFc0
 WoDTgxHRzC1jgQNidyd6tKqcsVJs0dzF0oKTmFFmUAqTdJO12LBuNA1rlqrR3EtpYk8B/wtS
 5dIMD7Q8hwQpL+4C6GNpb6ZKnPkLi47pDOLhz2qBrqN+rqUEsT3YnExYpzj5yOBi+FlmV1Hw
 49QYe1sn2ZPs
In-Reply-To: 
 <CAKYtNAoBUAP0k_eRvs6J-b61wSqgQRiASHFj-SNzcY_tFvGj3Q@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/f2b1185a-1688-4b05-ad86-39b467af835a%40dunslane.net>
Precedence: bulk


On 2025-03-31 Mo 12:16 PM, Mahendra Singh Thalor wrote:
> On Mon, 31 Mar 2025 at 19:27, Andrew Dunstan <andrew@dunslane.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@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