Re: Restoring only a subset of schemas

Le 17/03/2025 à 16:29, Tom Lane a écrit :
> Sylvain Cuaz <sylvain(at)ilm-informatique(dot)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