Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1jGXr5-0001Bw-TH for pgsql-admin@arkaria.postgresql.org; Tue, 24 Mar 2020 00:55:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1jGXr4-0003L2-NE for pgsql-admin@arkaria.postgresql.org; Tue, 24 Mar 2020 00:55:42 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1jGXr4-0003Kh-A5 for pgsql-admin@lists.postgresql.org; Tue, 24 Mar 2020 00:55:42 +0000 Received: from jakobs.com ([85.214.83.89] helo=rs.plausibolo.de) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jGXr0-0006g5-Ov for pgsql-admin@lists.postgresql.org; Tue, 24 Mar 2020 00:55:40 +0000 Received: from localhost (localhost [127.0.0.1]) by rs.plausibolo.de (Postfix) with ESMTP id 86843BE0E17 for ; Tue, 24 Mar 2020 01:55:36 +0100 (CET) X-Virus-Scanned: Debian amavisd-new at rs.plausibolo.de Received: from rs.plausibolo.de ([IPv6:::1]) by localhost (h2367442.stratoserver.net [IPv6:::1]) (amavisd-new, port 10024) with ESMTP id oF3IioT69wMD; Tue, 24 Mar 2020 01:55:32 +0100 (CET) Received: from [192.168.178.85] (i5E86B0CA.versanet.de [94.134.176.202]) by rs.plausibolo.de (Postfix) with ESMTPSA id AA9D6BE07CE; Tue, 24 Mar 2020 01:55:32 +0100 (CET) Subject: Re: pg_dump / pgrestore question - cannot restore a single schema To: pgsql-admin@lists.postgresql.org References: <5144169d-9913-2261-2ec0-3045679a8477@quadratum-braccas.com> From: Holger Jakobs Message-ID: <1226ea80-e760-54ff-47e3-c2a0c90cf1e0@jakobs.com> Date: Tue, 24 Mar 2020 01:55:32 +0100 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.4.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------A9B9713B35EC305414A54EFD" Content-Language: de-DE List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------A9B9713B35EC305414A54EFD Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit You could also extract the commands for creating the schema contained in the dump using the following commands: schemaname=whatever_your_schemaname_is pg_restore -l /tmp/dump | grep "SCHEMA - $schemaname" > /tmp/objects_to_re-create pg_restore -L /tmp/objects_to_re-create -f - /tmp/my_custom_dump | psql and then pg_restore -n "$schemaname" /tmp//tmp/my_custom_dump | psql in order to re-create the contents of the schema. Regards, Holger Am 24.03.20 um 01:40 schrieb David G. Johnston: > On Mon, Mar 23, 2020 at 4:47 PM S.Bob > wrote: > > However if I try to restore only one of the schemas it does not > work, seems like it does not create the schema first: > > Since you have to know the schema name anyway in order to restore that > schema only just go ahead an create the schema before performing the > restore.  It seems that since a schema is not "in itself" it doesn't > get restored.  Doesn't seem like a problem. > > David J. > -- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012 --------------A9B9713B35EC305414A54EFD Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

You could also extract the commands for creating the schema contained in the dump using the following commands:

schemaname=whatever_your_schemaname_is
pg_restore -l /tmp/dump | grep "SCHEMA - $schemaname" > /tmp/objects_to_re-create
pg_restore -L /tmp/objects_to_re-create -f - /tmp/my_custom_dump | psql

and then

pg_restore -n "$schemaname" /tmp//tmp/my_custom_dump | psql

in order to re-create the contents of the schema.

Regards,

Holger

Am 24.03.20 um 01:40 schrieb David G. Johnston:
On Mon, Mar 23, 2020 at 4:47 PM S.Bob <sbob@quadratum-braccas.com> wrote:

However if I try to restore only one of the schemas it does not work, seems like it does not create the schema first:

Since you have to know the schema name anyway in order to restore that schema only just go ahead an create the schema before performing the restore.  It seems that since a schema is not "in itself" it doesn't get restored.  Doesn't seem like a problem.

David J.

-- 
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
--------------A9B9713B35EC305414A54EFD--