MIME-Version: 1.0
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>
In-Reply-To: <ff600e57-d0aa-4fe9-b86d-b4a353091a9c@dunslane.net>
From: Mahendra Singh Thalor <mahi6run@gmail.com>
Date: Mon, 31 Mar 2025 21:46:01 +0530
Message-ID: 
 <CAKYtNAoBUAP0k_eRvs6J-b61wSqgQRiASHFj-SNzcY_tFvGj3Q@mail.gmail.com>
Subject: Re: Non-text mode for pg_dumpall
To: Andrew Dunstan <andrew@dunslane.net>
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
Content-Type: multipart/mixed; boundary="00000000000022f83e0631a5bdd4"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKYtNAoBUAP0k_eRvs6J-b61wSqgQRiASHFj-SNzcY_tFvGj3Q%40mail.gmail.com>
Precedence: bulk

--00000000000022f83e0631a5bdd4
Content-Type: text/plain; charset="UTF-8"

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 and Regards
Mahendra Singh Thalor
EnterpriseDB: http://www.enterprisedb.com

--00000000000022f83e0631a5bdd4
Content-Type: application/octet-stream;
	name="delta-0001-31march-pg_restore-skip-error-if-db-already-created.noci"
Content-Disposition: attachment;
	filename="delta-0001-31march-pg_restore-skip-error-if-db-already-created.noci"
Content-Transfer-Encoding: base64
Content-ID: <f_m8x9se360>
X-Attachment-Id: f_m8x9se360

RnJvbSBmYzg5MDJlZDc0MWM4MzQwMmMxMzkzZTZkMDY3N2Y0YjljYmM1MmZiIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBNYWhlbmRyYSBTaW5naCBUaGFsb3IgPG1haGk2cnVuQGdtYWls
LmNvbT4KRGF0ZTogTW9uLCAzMSBNYXIgMjAyNSAyMDozNjoyNSArMDUzMApTdWJqZWN0OiBbUEFU
Q0ggMS8yXSBwZ19yZXN0b3JlOiBpZiBkYXRhYmFzZSBpcyBhbHJlYWR5IGNyZWF0ZWQsIHRoZW4g
c2V0CiBjcmVhdGVkYiAgYXMgMAoKSWYgZGF0YWJhc2UgaXMgYWxyZWFkeSBjcmVhdGVkLCB0aGVu
IHNldCBjcmVhdGVkYiBhcyAwIHNvIHRoYXQgdXNlcgp3aWxsIG5vdCBnZXQgZXJyb3JzLgoKQWxz
byByZXNldCBzb21lIGZsYWdzIGZvciBlYWNoIGRhdGFiYXNlLgphcyBkdW1wRGF0YSwgZHVtcFNj
aGVtYSwgZHVtcFN0YXRpc3RpY3MKLS0tCiBzcmMvYmluL3BnX2R1bXAvcGdfcmVzdG9yZS5jIHwg
MzcgKysrKysrKysrKysrKysrKysrKysrKysrKysrKysrKysrKysrCiAxIGZpbGUgY2hhbmdlZCwg
MzcgaW5zZXJ0aW9ucygrKQoKZGlmZiAtLWdpdCBhL3NyYy9iaW4vcGdfZHVtcC9wZ19yZXN0b3Jl
LmMgYi9zcmMvYmluL3BnX2R1bXAvcGdfcmVzdG9yZS5jCmluZGV4IGNlNzBiN2UxMmIyLi5kODAz
N2RhNjUzZiAxMDA2NDQKLS0tIGEvc3JjL2Jpbi9wZ19kdW1wL3BnX3Jlc3RvcmUuYworKysgYi9z
cmMvYmluL3BnX2R1bXAvcGdfcmVzdG9yZS5jCkBAIC0xMTA3LDYgKzExMDcsMTQgQEAgcmVzdG9y
ZUFsbERhdGFiYXNlcyhQR2Nvbm4gKmNvbm4sIGNvbnN0IGNoYXIgKmR1bXBkaXJwYXRoLAogCWlu
dAkJCW51bV90b3RhbF9kYjsKIAlpbnQJCQluX2Vycm9yc190b3RhbDsKIAlpbnQJCQljb3VudCA9
IDA7CisJY2hhcgkJKmNvbm5lY3RlZF9kYiA9IE5VTEw7CisJYm9vbAkJZHVtcERhdGEgPSBvcHRz
LT5kdW1wRGF0YTsKKwlib29sCQlkdW1wU2NoZW1hID0gb3B0cy0+ZHVtcFNjaGVtYTsKKwlib29s
CQlkdW1wU3RhdGlzdGljcyA9IG9wdHMtPmR1bXBTY2hlbWE7CisKKwkvKiBTYXZlIGRiIG5hbWUg
dG8gcmV1c2UgaXQgZm9yIGFsbCB0aGUgZGF0YWJhc2UuICovCisJaWYgKG9wdHMtPmNwYXJhbXMu
ZGJuYW1lKQorCQljb25uZWN0ZWRfZGIgPSBvcHRzLT5jcGFyYW1zLmRibmFtZTsKIAogCW51bV90
b3RhbF9kYiA9IGdldF9kYm5hbWVfb2lkX2xpc3RfZnJvbV9tZmlsZShkdW1wZGlycGF0aCwgJmRi
bmFtZV9vaWRfbGlzdCk7CiAKQEAgLTEyMDksOSArMTIxNywzOCBAQCByZXN0b3JlQWxsRGF0YWJh
c2VzKFBHY29ubiAqY29ubiwgY29uc3QgY2hhciAqZHVtcGRpcnBhdGgsCiAKIAkJcGdfbG9nX2lu
Zm8oInJlc3RvcmluZyBkYXRhYmFzZSBcIiVzXCIiLCBkYl9jZWxsLT5zdHIpOwogCisJCS8qIElm
IGRhdGFiYXNlIGlzIGFscmVhZHkgY3JlYXRlZCwgdGhlbiBkb24ndCBzZXQgY3JlYXRlREIgZmxh
Zy4gKi8KKwkJaWYgKG9wdHMtPmNwYXJhbXMuZGJuYW1lKQorCQl7CisJCQljb25uID0gQ29ubmVj
dERhdGFiYXNlKGRiX2NlbGwtPnN0ciwgTlVMTCwgb3B0cy0+Y3BhcmFtcy5wZ2hvc3QsCisJCQkJ
CW9wdHMtPmNwYXJhbXMucGdwb3J0LCBvcHRzLT5jcGFyYW1zLnVzZXJuYW1lLCBUUklfREVGQVVM
VCwKKwkJCQkJZmFsc2UsIHByb2duYW1lLCBOVUxMLCBOVUxMLCBOVUxMLCBOVUxMKTsKKworCQkJ
aWYgKGNvbm4pCisJCQl7CisJCQkJb3B0cy0+Y3JlYXRlREIgPSAwOworCQkJCVBRZmluaXNoKGNv
bm4pOworCisJCQkJLyogVXNlIGFscmVhZHkgY3JlYXRlZCBkYXRhYmFzZSBmb3IgY29ubmVjdGlv
bi4gKi8KKwkJCQlvcHRzLT5jcGFyYW1zLmRibmFtZSA9IGRiX2NlbGwtPnN0cjsKKwkJCX0KKwkJ
fQorCiAJCS8qIFJlc3RvcmUgc2luZ2xlIGRhdGFiYXNlLiAqLwogCQluX2Vycm9ycyA9IHJlc3Rv
cmVPbmVEYXRhYmFzZShzdWJkaXJwYXRoLCBvcHRzLCBudW1Xb3JrZXJzLCB0cnVlLCBjb3VudCk7
CiAKKwkJLyogU2V0IG9wdHMtPmNyZWF0ZURCIGZsYWcuICovCisJCWlmIChvcHRzLT5jcmVhdGVE
QiA9PSAwKQorCQl7CisJCQlvcHRzLT5jcmVhdGVEQiA9IDE7CisJCQlvcHRzLT5jcGFyYW1zLmRi
bmFtZSA9IGNvbm5lY3RlZF9kYjsKKwkJfQorCisJCS8qIFJlc2V0IGZsYWdzIGZvciBuZXh0IGRh
dGFiYXNlLiAqLworCQlvcHRzLT5kdW1wRGF0YSA9IGR1bXBEYXRhOworCQlvcHRzLT5kdW1wU2No
ZW1hID0gZHVtcFNjaGVtYTsKKwkJb3B0cy0+ZHVtcFN0YXRpc3RpY3MgPSBkdW1wU3RhdGlzdGlj
czsKKwogCQkvKiBQcmludCBhIHN1bW1hcnkgb2YgaWdub3JlZCBlcnJvcnMgZHVyaW5nIHNpbmds
ZSBkYXRhYmFzZSByZXN0b3JlLiAqLwogCQlpZiAobl9lcnJvcnMpCiAJCXsKLS0gCjIuMzkuMwoK
--00000000000022f83e0631a5bdd4
Content-Type: application/octet-stream;
	name="delta-0002-31march-pg_restore-skip-error-for-CRETE-ROLE-username.noci"
Content-Disposition: attachment;
	filename="delta-0002-31march-pg_restore-skip-error-for-CRETE-ROLE-username.noci"
Content-Transfer-Encoding: base64
Content-ID: <f_m8x9si9c1>
X-Attachment-Id: f_m8x9si9c1

RnJvbSBkNzU4NTZiMjAwZTVhYjcwN2YzNjExOWI3OWIyNThiNWUzYmFhN2M3IE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBNYWhlbmRyYSBTaW5naCBUaGFsb3IgPG1haGk2cnVuQGdtYWls
LmNvbT4KRGF0ZTogTW9uLCAzMSBNYXIgMjAyNSAyMTozOToyNiArMDUzMApTdWJqZWN0OiBbUEFU
Q0hdIHBnX3Jlc3RvcmU6IHNraXAgZXJyb3IgZm9yIENSRVRFIFJPTEUgdXNlcm5hbWUKCi0tLQot
LS0KLS0tCiBzcmMvYmluL3BnX2R1bXAvcGdfcmVzdG9yZS5jIHwgMjUgKysrKysrKysrKysrKysr
KysrKysrKystLQogMSBmaWxlIGNoYW5nZWQsIDIzIGluc2VydGlvbnMoKyksIDIgZGVsZXRpb25z
KC0pCgpkaWZmIC0tZ2l0IGEvc3JjL2Jpbi9wZ19kdW1wL3BnX3Jlc3RvcmUuYyBiL3NyYy9iaW4v
cGdfZHVtcC9wZ19yZXN0b3JlLmMKaW5kZXggZDgwMzdkYTY1M2YuLjQ4MDA1MjdhMmYzIDEwMDY0
NAotLS0gYS9zcmMvYmluL3BnX2R1bXAvcGdfcmVzdG9yZS5jCisrKyBiL3NyYy9iaW4vcGdfZHVt
cC9wZ19yZXN0b3JlLmMKQEAgLTU0Miw4ICs1NDIsNyBAQCBtYWluKGludCBhcmdjLCBjaGFyICoq
YXJndikKIAkJCSAqIE9wZW4gZ2xvYmFsLmRhdCBmaWxlIGFuZCBleGVjdXRlL2FwcGVuZCBhbGwg
dGhlIGdsb2JhbCBzcWwKIAkJCSAqIGNvbW1hbmRzLgogCQkJICovCi0JCQluX2Vycm9ycyA9IHBy
b2Nlc3NfZ2xvYmFsX3NxbF9jb21tYW5kcyhjb25uLCBpbnB1dEZpbGVTcGVjLAotCQkJCQkJCQkJ
CQkJICAgb3B0cy0+ZmlsZW5hbWUpOworCQkJbl9lcnJvcnMgPSBwcm9jZXNzX2dsb2JhbF9zcWxf
Y29tbWFuZHMoY29ubiwgaW5wdXRGaWxlU3BlYywgb3B0cy0+ZmlsZW5hbWUpOwogCiAJCQlpZiAo
Y29ubikKIAkJCQlQUWZpbmlzaChjb25uKTsKQEAgLTEyODQsOCArMTI4MywxMyBAQCBwcm9jZXNz
X2dsb2JhbF9zcWxfY29tbWFuZHMoUEdjb25uICpjb25uLCBjb25zdCBjaGFyICpkdW1wZGlycGF0
aCwgY29uc3QgY2hhciAqbwogCWNoYXIJCWdsb2JhbF9maWxlX3BhdGhbTUFYUEdQQVRIXTsKIAlQ
R3Jlc3VsdCAgICpyZXN1bHQ7CiAJU3RyaW5nSW5mb0RhdGEgc3Fsc3RhdGVtZW50OworCVN0cmlu
Z0luZm9EYXRhIHJvbGVzcWxzdGF0ZW1lbnQ7CiAJRklMRQkgICAqcGZpbGU7CiAJaW50CQkJbl9l
cnJvcnMgPSAwOworCWJvb2wJCWNoZWNrX3JvbGVfY21kID0gdHJ1ZTsKKworCS8qIFNob3VsZCBo
YXZlIHZhbGlkIGNvbm5lY3Rpb24uICovCisJQXNzZXJ0KGNvbm4pOwogCiAJc25wcmludGYoZ2xv
YmFsX2ZpbGVfcGF0aCwgTUFYUEdQQVRILCAiJXMvZ2xvYmFsLmRhdCIsIGR1bXBkaXJwYXRoKTsK
IApAQCAtMTMwOCw5ICsxMzEyLDI2IEBAIHByb2Nlc3NfZ2xvYmFsX3NxbF9jb21tYW5kcyhQR2Nv
bm4gKmNvbm4sIGNvbnN0IGNoYXIgKmR1bXBkaXJwYXRoLCBjb25zdCBjaGFyICpvCiAJLyogSW5p
dCBzcWxzdGF0ZW1lbnQgdG8gYXBwZW5kIGNvbW1hbmRzLiAqLwogCWluaXRTdHJpbmdJbmZvKCZz
cWxzdGF0ZW1lbnQpOwogCisJLyogUHJlYXByZSAiQ1JFQVRFIFJPTEUgdXNlcm5hbWUiIGNvbW1h
bmQuICovCisJaW5pdFN0cmluZ0luZm8oJnJvbGVzcWxzdGF0ZW1lbnQpOworCWFwcGVuZFN0cmlu
Z0luZm9TdHJpbmcoJnJvbGVzcWxzdGF0ZW1lbnQsICJcblxuLS1cbi0tIFJvbGVzXG4tLVxuXG5D
UkVBVEUgUk9MRSAiKTsKKwlhcHBlbmRTdHJpbmdJbmZvU3RyaW5nKCZyb2xlc3Fsc3RhdGVtZW50
LCBQUXVzZXIoY29ubikpOworCWFwcGVuZFN0cmluZ0luZm9TdHJpbmcoJnJvbGVzcWxzdGF0ZW1l
bnQsICI7Iik7CisKIAkvKiBQcm9jZXNzIGZpbGUgdGlsbCBFT0YgYW5kIGV4ZWN1dGUgc3FsIHN0
YXRlbWVudHMuICovCiAJd2hpbGUgKHJlYWRfb25lX3N0YXRlbWVudCgmc3Fsc3RhdGVtZW50LCBw
ZmlsZSkgIT0gRU9GKQogCXsKKworCQkvKgorCQkgKiBJZiB0aGlzIGNvbW1hbmQgaXMgZm9yICJD
UkVBVEUgUk9MRSB1c2VybmFtZSIsIHRoZW4gc2tpcCB0aGlzIGFzCisJCSAqIGN1cnJlbnQgdXNl
ciBpcyBhbHJlYWR5IGNyZWF0ZWQuCisJCSAqLworCQlpZiAoY2hlY2tfcm9sZV9jbWQgJiYgKHN0
cmNtcChzcWxzdGF0ZW1lbnQuZGF0YSwgcm9sZXNxbHN0YXRlbWVudC5kYXRhKSA9PSAwKSkKKwkJ
eworCQkJY2hlY2tfcm9sZV9jbWQgPSBmYWxzZTsKKwkJCWNvbnRpbnVlOworCQl9CisKIAkJcGdf
bG9nX2luZm8oImV4ZWN1dGluZyBxdWVyeTogJXMiLCBzcWxzdGF0ZW1lbnQuZGF0YSk7CiAJCXJl
c3VsdCA9IFBRZXhlYyhjb25uLCBzcWxzdGF0ZW1lbnQuZGF0YSk7CiAKLS0gCjIuMzkuMwoK
--00000000000022f83e0631a5bdd4--