pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: MichaelDBA Vitale <michaeldba@sqlexec.com>
To: Thomas Kellerer <shammat@gmx.net>
To: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: How to fully restore a single table from a custom dump?
Date: Tue, 9 Aug 2022 08:49:07 -0400 (EDT)
Message-ID: <1622540476.1110533.1660049347030@email.ionos.com> (raw)
In-Reply-To: <e434fe3f-0159-2223-8670-411d5c21c5bd@gmx.net>
References: <e434fe3f-0159-2223-8670-411d5c21c5bd@gmx.net>

<!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>

view thread (10+ messages)  latest in thread

Message-ID: <1622540476.1110533.1660049347030@email.ionos.com>
Permalink:  ../1622540476.1110533.1660049347030@email.ionos.com/
Also on:    postgresql.org/message-id/1622540476.1110533.1660049347030@email.ionos.com

copy link · copy postgr.es

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: pgsql-admin@postgresql.org
  Cc: michaeldba@sqlexec.com, shammat@gmx.net
  Subject: Re: How to fully restore a single table from a custom dump?
  In-Reply-To: <1622540476.1110533.1660049347030@email.ionos.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox