public inbox for [email protected]
help / color / mirror / Atom feedFrom: Sylvain Cuaz <[email protected]>
To: Tom Lane <[email protected]>
Cc: [email protected]
Subject: Re: Restoring only a subset of schemas
Date: Wed, 19 Mar 2025 09:24:05 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
Le 17/03/2025 à 16:29, Tom Lane a écrit :
> Sylvain Cuaz <[email protected]> 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
Attachments:
[text/x-patch] pg_backup_archiver.patch (1.0K, 2-pg_backup_archiver.patch)
download | inline diff:
--- 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)
{
view thread (6+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Restoring only a subset of schemas
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox