Content-Type: multipart/mixed; boundary="------------I0XUnSjhdKlHu5J4bwR025rC"
Message-ID: <238e8c2a-b202-41b4-92e9-c0dbc4700c26@ilm-informatique.fr>
Date: Wed, 19 Mar 2025 09:24:05 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Restoring only a subset of schemas
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general@lists.postgresql.org
References: <7d869dac-5fe0-488c-a7a1-436b1d939057@ilm-informatique.fr>
 <4009720.1742225376@sss.pgh.pa.us>
Content-Language: fr, en-US
From: Sylvain Cuaz <sylvain@ilm-informatique.fr>
In-Reply-To: <4009720.1742225376@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/238e8c2a-b202-41b4-92e9-c0dbc4700c26%40ilm-informatique.fr>
Precedence: bulk

This is a multi-part message in MIME format.
--------------I0XUnSjhdKlHu5J4bwR025rC
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

Le 17/03/2025 à 16:29, Tom Lane a écrit :
> Sylvain Cuaz <sylvain@ilm-informatique.fr> writes:
>>       Now if I want to restore from a full dump of this DB, but with only one "cXXX" and the "Common"
>> schema :
>> - if I pass --create --schema=Common, then the CREATE SCHEMA is missing, i.e. it only emits data
>> inside "Common" and the restore fails.
>> - if I could pass --create --exclude-schema='c*' (fictional notation as patterns are only recognized
>> by pg_dump), then all schemas would be created, with no data inside except for "Common". Creating
>> all schemas is a waste of time, but more importantly would make restoring other schemas more
>> difficult (e.g. rows should be inserted before creating foreign keys).
> In general, the solution for edge-case restore selection needs is to
> make a list of the dump's contents with "pg_restore -l", edit out what
> you don't want using any method you like, then use the edited list with
> "pg_restore -L".

Hi,

     I am aware of that feature, but that forces me to know every type of entry that pertains to a 
schema or database (e.g. DEFAULT ACL, ACL, COMMENT, DATABASE PROPERTIES, etc.) and what about new 
ones that will be added in the future ?

Further, I don't see how it's an edge-case, at the core I just want to restore some but not all the 
schemas. This is possible for pg_dump, see my response to Adrian Klaver.

> While I'd be in favor of improving pg_restore to accept wild-card
> patterns,
That would definitely be appreciated.
>   I'm very hesitant to start inventing new kinds of selection
> switches for it.  The interactions between such switches would be a
> mess.

Which interactions ? It seems to me that the name of the schema should be used as the namespace to 
check in _tocEntryRequired() in pg_backup_archiver.c, and then the dependent entries (e.g. ACL, 
COMMENT) would be handled around line 3050. I've attached a patch with some pseudo-code. In fact, 
were it not for compatibility, I'd argue that my proposed options should be the default, at least 
with --create, so as to neither output invalid SQL (for -n) nor extra unwanted ones (for -N) and to 
behave like pg_dump.

Cheers,

Sylvain


--------------I0XUnSjhdKlHu5J4bwR025rC
Content-Type: text/x-patch; charset=UTF-8; name="pg_backup_archiver.patch"
Content-Disposition: attachment; filename="pg_backup_archiver.patch"
Content-Transfer-Encoding: base64

LS0tIHBnX2JhY2t1cF9hcmNoaXZlci5jCTIwMjUtMDMtMTggMTk6NDM6MTQuMjk3NTQ1NTM3
ICswMTAwCisrKyBwZ19iYWNrdXBfYXJjaGl2ZXJfY3JlYXRlLXNjaGVtYS5jCTIwMjUtMDMt
MTggMjE6MTE6MjUuNjgxNTg2MTM5ICswMTAwCkBAIC0zMDY5LDE3ICszMDY5LDIwIEBACiAJ
CS8qIEFwcGx5IHNlbGVjdGl2ZS1yZXN0b3JlIHJ1bGVzIGZvciBzdGFuZGFsb25lIFRPQyBl
bnRyaWVzLiAqLwogCQlpZiAocm9wdC0+c2NoZW1hTmFtZXMuaGVhZCAhPSBOVUxMKQogCQl7
CisJCQlucyA9ICZyb3B0LT5pbmNsdWRlLWNyZWF0ZS1zY2hlbWEgJiYgc3RyY21wKHRlLT5k
ZXNjLCAiU0NIRU1BIikgPT0gMCA/IHRlLT50YWcgOiB0ZS0+bmFtZXNwYWNlOwogCQkJLyog
SWYgbm8gbmFtZXNwYWNlIGlzIHNwZWNpZmllZCwgaXQgbWVhbnMgYWxsLiAqLwotCQkJaWYg
KCF0ZS0+bmFtZXNwYWNlKQorCQkJaWYgKCFucykKIAkJCQlyZXR1cm4gMDsKLQkJCWlmICgh
c2ltcGxlX3N0cmluZ19saXN0X21lbWJlcigmcm9wdC0+c2NoZW1hTmFtZXMsIHRlLT5uYW1l
c3BhY2UpKQorCQkJaWYgKCFzaW1wbGVfc3RyaW5nX2xpc3RfbWVtYmVyKCZyb3B0LT5zY2hl
bWFOYW1lcywgbnMpKQogCQkJCXJldHVybiAwOwogCQl9CiAKLQkJaWYgKHJvcHQtPnNjaGVt
YUV4Y2x1ZGVOYW1lcy5oZWFkICE9IE5VTEwgJiYKLQkJCXRlLT5uYW1lc3BhY2UgJiYKLQkJ
CXNpbXBsZV9zdHJpbmdfbGlzdF9tZW1iZXIoJnJvcHQtPnNjaGVtYUV4Y2x1ZGVOYW1lcywg
dGUtPm5hbWVzcGFjZSkpCi0JCQlyZXR1cm4gMDsKKwkJaWYgKHJvcHQtPnNjaGVtYUV4Y2x1
ZGVOYW1lcy5oZWFkICE9IE5VTEwpCisJCXsKKwkJCW5zID0gJnJvcHQtPmV4Y2x1ZGUtY3Jl
YXRlLXNjaGVtYSAmJiBzdHJjbXAodGUtPmRlc2MsICJTQ0hFTUEiKSA9PSAwID8gdGUtPnRh
ZyA6IHRlLT5uYW1lc3BhY2U7CisJCQlpZihucyAmJiBzaW1wbGVfc3RyaW5nX2xpc3RfbWVt
YmVyKCZyb3B0LT5zY2hlbWFFeGNsdWRlTmFtZXMsIG5zKSkKKwkJCQlyZXR1cm4gMDsKKwkJ
fQogCiAJCWlmIChyb3B0LT5zZWxUeXBlcykKIAkJewo=

--------------I0XUnSjhdKlHu5J4bwR025rC--