Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pPs8J-00051u-D0 for pgsql-general@arkaria.postgresql.org; Wed, 08 Feb 2023 21:37:39 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pPs8H-000060-QX for pgsql-general@arkaria.postgresql.org; Wed, 08 Feb 2023 21:37:37 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pPs8H-0008VB-F7 for pgsql-general@lists.postgresql.org; Wed, 08 Feb 2023 21:37:37 +0000 Received: from portal-smtp-out.hot.ee ([194.126.101.125]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pPs89-0002CQ-Rc for pgsql-general@postgresql.org; Wed, 08 Feb 2023 21:37:36 +0000 Received: from localhost (localhost [127.0.0.1]) by vm-hot-relay10.estpak.ee (Postfix) with ESMTP id 0BF73319A; Wed, 8 Feb 2023 23:37:27 +0200 (EET) X-Virus-Scanned: Debian amavisd-new at vm-hot-relay10.estpak.ee Received: from portal-smtp-out.hot.ee ([127.0.0.1]) by localhost (vm-hot-relay10.estpak.ee [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id TkUy7xT5UVbf; Wed, 8 Feb 2023 23:37:24 +0200 (EET) Received: from mail.hot.ee (hot-relayhost3.estpak.ee [213.180.2.81]) by vm-hot-relay10.estpak.ee (Postfix) with ESMTP id C90EA31BD; Wed, 8 Feb 2023 23:37:24 +0200 (EET) Content-Type: multipart/alternative; boundary="------------AuSxFltrspHfdDlULlGR3c7k" Message-ID: <6422167c-c725-d305-5ee5-d16f310e7fc7@hot.ee> Date: Wed, 8 Feb 2023 23:37:26 +0200 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:102.0) Gecko/20100101 Thunderbird/102.6.1 Subject: Re: How to create directory format backup Content-Language: et To: Erik Wienhold , depesz@depesz.com Cc: pgsql-general References: <6da54c55-6752-ca53-cdc8-2342b2c75258@hot.ee> <531831758.189866.1675891078338@office.mailbox.org> From: Andrus In-Reply-To: <531831758.189866.1675891078338@office.mailbox.org> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------AuSxFltrspHfdDlULlGR3c7k Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Hi! >> How to create backup in format from which tables can selectively restored? > Dump as custom-format archive (-F custom) and use that with pg_restore and > options --table or --list/--use-list to select what should be restored. How to select tables interactively like pgAdmin allows to select when directory format is used ? Database contains hundreds of schemas. I need to restore public and other other schema. Whole backup file is scanned to restore only two schemas. It takes lot of time. Also directory format allows to use all cores with --jobs=32 parameter. Dump and partial restore using custom format are much slower. Andrus. > -- > Erik --------------AuSxFltrspHfdDlULlGR3c7k Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit

Hi!


How to create backup in format from which tables can selectively restored?
Dump as custom-format archive (-F custom) and use that with pg_restore and
options --table or --list/--use-list to select what should be restored.

How to select tables interactively like pgAdmin allows to select when directory format is used ?

Database contains hundreds of schemas. I need to restore public and other other schema.

Whole backup file is scanned to restore only two schemas. It takes lot of time.

Also directory format allows to use all cores with --jobs=32 parameter. Dump and partial restore using custom format are much slower.

Andrus.


--
Erik
--------------AuSxFltrspHfdDlULlGR3c7k--