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 <pgsql-admin-owner+archive@lists.postgresql.org>)
	id 1oLOfc-0005pd-KX
	for pgsql-admin@arkaria.postgresql.org; Tue, 09 Aug 2022 12:49:16 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-admin-owner+archive@lists.postgresql.org>)
	id 1oLOfb-0007Qr-0M
	for pgsql-admin@arkaria.postgresql.org; Tue, 09 Aug 2022 12:49:15 +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 <michaeldba@sqlexec.com>)
	id 1oLOfa-0007Qh-Lr
	for pgsql-admin@lists.postgresql.org; Tue, 09 Aug 2022 12:49:14 +0000
Received: from mout.perfora.net ([74.208.4.194])
	by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <michaeldba@sqlexec.com>)
	id 1oLOfY-0003hH-1W
	for pgsql-admin@postgresql.org; Tue, 09 Aug 2022 12:49:13 +0000
Received: from oxusgaltgw15.schlund.de ([10.72.72.62]) by mrelay.perfora.net
 (mreueus002 [74.208.5.2]) with ESMTPSA (Nemesis) id 0M1V6N-1nX9Ok2uKX-00tR9l;
 Tue, 09 Aug 2022 14:49:07 +0200
Date: Tue, 9 Aug 2022 08:49:07 -0400 (EDT)
From: MichaelDBA Vitale <michaeldba@sqlexec.com>
To: Thomas Kellerer <shammat@gmx.net>,
	pgsql-admin <pgsql-admin@postgresql.org>
Message-ID: <1622540476.1110533.1660049347030@email.ionos.com>
In-Reply-To: <e434fe3f-0159-2223-8670-411d5c21c5bd@gmx.net>
References: <e434fe3f-0159-2223-8670-411d5c21c5bd@gmx.net>
Subject: Re: How to fully restore a single table from a custom dump?
MIME-Version: 1.0
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit
X-Priority: 3
Importance: Normal
X-Mailer: Open-Xchange Mailer v7.10.6-Rev18
X-Originating-Client: open-xchange-appsuite
X-Provags-ID: V03:K1:swI2vYjqhIj0s5qgJVUu9Amzk/7Y7Uwgu0cQz6WwOXkBp/7+5bw
 ZUsfcQWZblEGgnD32uhnc1jyI6JL3ADDlhTuc6VuP+v1Hg5/I3mxvf5/BH7kUVHvcaEe3xi
 CXTVauQ4pG97RoSwrUI/fdhHKIyZ0uSnk7pPxD/RdHOUgYeH/4h0Tcvb0tidCip423Yi2LH
 fyD91Xolr+Bs9BZMv/YpA==
X-Spam-Flag: NO
X-UI-Out-Filterresults: notjunk:1;V03:K0:5mhVluSCKpg=:ayB0A4Pp2n9GIJF2iDTLm2
 5nHRwGYHRRIyIJVj/FT/PplQG6mJ8v9+98VxhZxrAOaE6Zpg4RdKN3e1itOOQfuLayESfPll0
 lMn/hdSfVFpREuRVKWjz1tJkdt85jGmuGgjcwxqe404JOt2qVYa4sc+NzFqIbCms1TJVyh+hB
 T7pNNzazC1NRqBO/6n9/5NLFm5jN5bwl+m1vMXA9coohZm37YF2DTx2NLR5TYqFVmidr9qMIh
 oG9x6JC6e3G4Y1ARGQ3tlrBbX9HCHlqRPWBEAU39/MKgPZtXh1YZYQJP/N1vsvGLHwYzd43CY
 yyXp3yIdEjVq5k9y+aRy4aalik1uk3QU1468s/SHegdyAXwyIlD6N11wuFcsubaDO5tA889DW
 hVeSxGlDhgxa9a7hLaQ/06JQL2dCgMtCW+J7vqN1so+U835CXVldA6PH55pLL6ytHHKM3wUqd
 Jc5TiX1WgYyKlquttYJV1/8y+tAcM6zr+xzkiI3XqcK8J8aMCMmUoVWb43FDuGuXthlkVzRah
 22vC7AnLo8rb1jsSryQv8n5/mlssgxaFC4YLnVCc4CzMaUGz9zQnubdSGik45SGI4cuqc+A+S
 pR0Qtfp6relN4oUhmH4SKz9y9z5T0EKJgOFh0eQ8eVHURIQGcOsMgs7659/Xq2l0ayNtANtKg
 aiHeW/FbPbhv22b4oQl99KOCrygGGp/FpvxQm4Dv5gUnvW6fQLWFWfzCmgfcVSn9H2PUH6fkx
 EFUvabUVHuBpDAGX
List-Id: <pgsql-admin.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-admin@lists.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-admin>
Archived-At: 
 <https://www.postgresql.org/message-id/1622540476.1110533.1660049347030%40email.ionos.com>
Precedence: bulk

<!doctype html>
<html>
 <head> 
  <meta charset="UTF-8"> 
 </head>
 <body>
  <div>
   Hi,
  </div> 
  <div class="default-style">
   If you use the directory dump method, -Fd, then you could generate an editable listing where you can selectively remove stuff that you don't want to restore, just keeping the stuff related to your specific table.&nbsp; &nbsp;You run pg_restore once to generate the listing.&nbsp; Then run pg_restore again using that modified listing to load into the target database. See the pg_restore docs for exact syntax.
  </div> 
  <div class="default-style">
   &nbsp;
  </div> 
  <div class="default-style">
   Regards,
  </div> 
  <div class="default-style">
   Michael Vitale
  </div> 
  <div class="default-style">
   &nbsp;
  </div> 
  <blockquote type="cite"> 
   <div>
    On 08/09/2022 8:06 AM EDT Thomas Kellerer &lt;<a href="mailto:shammat@gmx.net">shammat@gmx.net</a>&gt; wrote:
   </div> 
   <div>
    &nbsp;
   </div> 
   <div>
    &nbsp;
   </div> 
   <div>
    Hello,
   </div> 
   <div>
    &nbsp;
   </div> 
   <div>
    I just realized that using
   </div> 
   <div>
    &nbsp;
   </div> 
   <div>
    pg_restore -t some_table ... some_dump_file
   </div> 
   <div>
    &nbsp;
   </div> 
   <div>
    doesn't restore things like identity attributes
   </div> 
   <div>
    or indexes on the specified table.
   </div> 
   <div>
    &nbsp;
   </div> 
   <div>
    The dump contains much more than just that table, so simply
   </div> 
   <div>
    using pg_restore without -t is not an option.
   </div> 
   <div>
    &nbsp;
   </div> 
   <div>
    While I could extract the indexes manually using some clever regex
   </div> 
   <div>
    on the index names, I don't see a way to make sure that identity
   </div> 
   <div>
    definitions (or sequence values) are restored properly for the selected table.
   </div> 
   <div>
    &nbsp;
   </div> 
   <div>
    Any ideas, how I can _fully_ restore a single table from a custom dump?
   </div> 
   <div>
    &nbsp;
   </div> 
   <div>
    Thomas
   </div> 
  </blockquote>
 </body>
</html>