Message-ID: <db9a5bf2-ef2e-4d95-be80-79104cffecc5@ilm-informatique.fr>
Date: Wed, 19 Mar 2025 09:17:06 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Restoring only a subset of schemas
To: pgsql-general@lists.postgresql.org
References: <7d869dac-5fe0-488c-a7a1-436b1d939057@ilm-informatique.fr>
 <a62bae86-db06-4cfc-a07e-194cdd0f568a@aklaver.com>
Content-Language: en-US
From: Sylvain Cuaz <sylvain@ilm-informatique.fr>
In-Reply-To: <a62bae86-db06-4cfc-a07e-194cdd0f568a@aklaver.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/db9a5bf2-ef2e-4d95-be80-79104cffecc5%40ilm-informatique.fr>
Precedence: bulk

Le 17/03/2025 à 16:21, Adrian Klaver a écrit :
> On 3/17/25 07:57, Sylvain Cuaz wrote:
>> 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.
>
> I am not seeing that.
>
> For:
>
> pg_dump -d test -U postgres -s --create --schema=other_sch --schema=public
>
> What is the complete command you are using for the pg_dump?

Hi,

     As I said I'm restoring, not dumping. I make daily full backups and sometimes need to restore a 
specific day, but the full database is quite big and I would like to only restore one or two schemas.

As you said, if one passes --create --schema to pg_dump then a valid SQL is produced with CREATE 
DATABASE, CREATE SCHEMA, CREATE TABLE and all objects inside the schema.

But if one passes --create --schema to pg_restore then an invalid SQL is produced because it 
contains CREATE DATABASE, CREATE TABLE but it doesn't contain the CREATE SCHEMA needed for the 
tables. Is there any reason for that discrepancy between dump & restore and for outputting invalid 
SQL ?

My proposed --include-create-schema would just add the CREATE SCHEMA so that pg_restore behaves the 
same as pg_dump, and would allow to output valid SQL. But ideally this option shouldn't even be 
needed because pg_restore would just emit CREATE SCHEMA like pg_dump.

>
> What Postgres version(s) are you using? 

A lot :-) But for this problem I'm using 13 & 15.


Cheers,

Sylvain.