Content-Type: multipart/alternative;
 boundary="------------yYpTDcn2G7bLu69Nthml0nMV"
Message-ID: <93797605-23dc-de94-d49e-0849475ad426@gmail.com>
Date: Tue, 9 Aug 2022 11:41:33 -0500
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.9.1
Subject: Re: How to fully restore a single table from a custom dump?
Content-Language: en-US
To: pgsql-admin@lists.postgresql.org
References: <e434fe3f-0159-2223-8670-411d5c21c5bd@gmx.net>
 <1622540476.1110533.1660049347030@email.ionos.com>
 <9ca2d8ca-a175-630e-e449-48c2dee5e0f6@jakobs.com>
From: Ron <ronljohnsonjr@gmail.com>
In-Reply-To: <9ca2d8ca-a175-630e-e449-48c2dee5e0f6@jakobs.com>
Archived-At: 
 <https://www.postgresql.org/message-id/93797605-23dc-de94-d49e-0849475ad426%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------yYpTDcn2G7bLu69Nthml0nMV
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

On 8/9/22 08:21, Holger Jakobs wrote:
> Am 09.08.22 um 14:49 schrieb MichaelDBA Vitale:
>> Hi,
>> 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.  
>>  You run pg_restore once to generate the listing. Then run pg_restore 
>> again using that modified listing to load into the target database. See 
>> the pg_restore docs for exact syntax.
>> Regards,
>> Michael Vitale
>>> On 08/09/2022 8:06 AM EDT Thomas Kellerer <shammat@gmx.net> wrote:
>>> Hello,
>>> I just realized that using
>>> pg_restore -t some_table ... some_dump_file
>>> doesn't restore things like identity attributes
>>> or indexes on the specified table.
>>> The dump contains much more than just that table, so simply
>>> using pg_restore without -t is not an option.
>>> While I could extract the indexes manually using some clever regex
>>> on the index names, I don't see a way to make sure that identity
>>> definitions (or sequence values) are restored properly for the selected 
>>> table.
>>> Any ideas, how I can _fully_ restore a single table from a custom dump?
>>> Thomas
>
> Creating a list of contained items and restoring some of them works the 
> same with custom dumps. Directory dumps have no advantage here.
>
> Just comment out all items you don't want to restore by putting a ; in 
> front of the lines or delete the unwanted lines altogether and restore.
>

Which is less than convenient when there's 4000 tables, and each one has 3 
or four indices, a Primary Key and one or more Foreign Keys.

-- 
Angular momentum makes the world go 'round.
--------------yYpTDcn2G7bLu69Nthml0nMV
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    On 8/9/22 08:21, Holger Jakobs wrote:<br>
    <blockquote type="cite"
      cite="mid:9ca2d8ca-a175-630e-e449-48c2dee5e0f6@jakobs.com">
      <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
      <div class="moz-cite-prefix">Am 09.08.22 um 14:49 schrieb
        MichaelDBA Vitale:<br>
      </div>
      <blockquote type="cite"
        cite="mid:1622540476.1110533.1660049347030@email.ionos.com">
        <meta http-equiv="content-type" content="text/html;
          charset=UTF-8">
        <meta charset="UTF-8">
        <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.   You run pg_restore once to generate the listing. 
          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">   </div>
        <div class="default-style"> Regards, </div>
        <div class="default-style"> Michael Vitale </div>
        <div class="default-style">   </div>
        <blockquote type="cite">
          <div> On 08/09/2022 8:06 AM EDT Thomas Kellerer &lt;<a
              href="mailto:shammat@gmx.net" moz-do-not-send="true"
              class="moz-txt-link-freetext">shammat@gmx.net</a>&gt;
            wrote: </div>
          <div>   </div>
          <div>   </div>
          <div> Hello, </div>
          <div>   </div>
          <div> I just realized that using </div>
          <div>   </div>
          <div> pg_restore -t some_table ... some_dump_file </div>
          <div>   </div>
          <div> doesn't restore things like identity attributes </div>
          <div> or indexes on the specified table. </div>
          <div>   </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>   </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>   </div>
          <div> Any ideas, how I can _fully_ restore a single table from
            a custom dump? </div>
          <div>   </div>
          <div> Thomas </div>
        </blockquote>
      </blockquote>
      <p>Creating a list of contained items and restoring some of them
        works the same with custom dumps. Directory dumps have no
        advantage here.</p>
      <p>Just comment out all items you don't want to restore by putting
        a ; in front of the lines or delete the unwanted lines
        altogether and restore.</p>
    </blockquote>
    <br>
    Which is less than convenient when there's 4000 tables, and each one
    has 3 or four indices, a Primary Key and one or more Foreign Keys.<br>
    <br>
    <div class="moz-signature">-- <br>
      Angular momentum makes the world go 'round.</div>
  </body>
</html>

--------------yYpTDcn2G7bLu69Nthml0nMV--