Hi all,
I have a DB with one schema named "Common" holding data referenced by other schemas. All other
schemas have the same structure (tables and fields) and are named "cXXX" where XXX is just an int.
Thus the only cross-schema foreign keys are in "cXXX" pointing to "Common", and each "cXXX" is
completely independent of other "cXXX" schemas.
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).
Note : to check the behaviour of pg_restore above, I pass -f- to check the SQL as it is far quicker
than to actually restore a DB.
Maybe a new --include-create-schema option should be added to emit CREATE SCHEMA in addition to
objects inside it ? That way I could :
1. --create --include-create-schema --schema=Common and have a DB with all DB-level properties
(DEFAULT PRIVILEGES, COMMENT, SET parameter, etc.) and one schema with all of its data and
schema-level properties (DEFAULT PRIVILEGES, COMMENT, GRANT USAGE).
2. then at any point later without the --create, with as many schemas I need :
--include-create-schema --schema=cXXX. And if I need to reset a "cXXX" schema, just manually DROP
SCHEMA and restore again.
Similarly, maybe add --exclude-create-schema to additionally exclude CREATE SCHEMA for schemas
targeted by --exclude-schema.
IOW --schema and --exclude-schema both target objects inside schemas, these 2 new options would
allow to also have control on the schemas themselves (and their properties like DEFAULT PRIVILEGES,
COMMENT, etc.)
Cheers,
Sylvain
