Message-ID: <7d869dac-5fe0-488c-a7a1-436b1d939057@ilm-informatique.fr>
Date: Mon, 17 Mar 2025 15:57:04 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Content-Language: fr, en-US
To: pgsql-general@lists.postgresql.org
From: Sylvain Cuaz <sylvain@ilm-informatique.fr>
Subject: Restoring only a subset of schemas
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/7d869dac-5fe0-488c-a7a1-436b1d939057%40ilm-informatique.fr>
Precedence: bulk

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