Le 17/03/2025 à 16:29, Tom Lane a écrit :
Sylvain Cuaz <sylvain@xxxxxxxxxxxxxxxxxxx> 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
--- pg_backup_archiver.c 2025-03-18 19:43:14.297545537 +0100
+++ pg_backup_archiver_create-schema.c 2025-03-18 21:11:25.681586139 +0100
@@ -3069,17 +3069,20 @@
/* Apply selective-restore rules for standalone TOC entries. */
if (ropt->schemaNames.head != NULL)
{
+ ns = &ropt->include-create-schema && strcmp(te->desc, "SCHEMA") == 0 ? te->tag : te->namespace;
/* If no namespace is specified, it means all. */
- if (!te->namespace)
+ if (!ns)
return 0;
- if (!simple_string_list_member(&ropt->schemaNames, te->namespace))
+ if (!simple_string_list_member(&ropt->schemaNames, ns))
return 0;
}
- if (ropt->schemaExcludeNames.head != NULL &&
- te->namespace &&
- simple_string_list_member(&ropt->schemaExcludeNames, te->namespace))
- return 0;
+ if (ropt->schemaExcludeNames.head != NULL)
+ {
+ ns = &ropt->exclude-create-schema && strcmp(te->desc, "SCHEMA") == 0 ? te->tag : te->namespace;
+ if(ns && simple_string_list_member(&ropt->schemaExcludeNames, ns))
+ return 0;
+ }
if (ropt->selTypes)
{
