Content-Type: multipart/alternative;
 boundary="------------wCJByNKdM6IjKRZC7riRHy3Y"
Message-ID: <3ff41470-0eb2-0f29-86c3-86c37dd15f20@gmail.com>
Date: Tue, 9 Aug 2022 12:18:24 -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 <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>
 <93797605-23dc-de94-d49e-0849475ad426@gmail.com>
 <CAECtzeXyzFE_D6sU3w0h8OCjFhQRcvaAFHQKM=jZk59hc4w5ug@mail.gmail.com>
From: Ron <ronljohnsonjr@gmail.com>
In-Reply-To: 
 <CAECtzeXyzFE_D6sU3w0h8OCjFhQRcvaAFHQKM=jZk59hc4w5ug@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/3ff41470-0eb2-0f29-86c3-86c37dd15f20%40gmail.com>
Precedence: bulk

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

On 8/9/22 12:13, Guillaume Lelarge wrote:
> Le mar. 9 août 2022, 18:41, Ron <ronljohnsonjr@gmail.com> a écrit :
>
>     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.
>
>
> Agreed, but it's already less convenient to give 4000 -t's :-)

What's your point?

-- 
Angular momentum makes the world go 'round.
--------------wCJByNKdM6IjKRZC7riRHy3Y
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 12:13, Guillaume Lelarge wrote:<br>
    <blockquote type="cite"
cite="mid:CAECtzeXyzFE_D6sU3w0h8OCjFhQRcvaAFHQKM=jZk59hc4w5ug@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="auto">
        <div>
          <div data-smartmail="gmail_signature">Le mar. 9 août 2022,
            18:41, Ron &lt;<a href="mailto:ronljohnsonjr@gmail.com"
              moz-do-not-send="true" class="moz-txt-link-freetext">ronljohnsonjr@gmail.com</a>&gt;
            a écrit :<br>
          </div>
          <div class="gmail_quote">
            <blockquote class="gmail_quote" style="margin:0 0 0
              .8ex;border-left:1px #ccc solid;padding-left:1ex">
              <div> On 8/9/22 08:21, Holger Jakobs wrote:<br>
                <blockquote type="cite">
                  <div>Am 09.08.22 um 14:49 schrieb MichaelDBA Vitale:<br>
                  </div>
                  <blockquote type="cite">
                    <div> Hi, </div>
                    <div> 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>   </div>
                    <div> Regards, </div>
                    <div> Michael Vitale </div>
                    <div>   </div>
                    <blockquote type="cite">
                      <div> On 08/09/2022 8:06 AM EDT Thomas Kellerer
                        &lt;<a href="mailto:shammat@gmx.net"
                          target="_blank" rel="noreferrer"
                          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>
              </div>
            </blockquote>
          </div>
        </div>
        <div dir="auto"><br>
        </div>
        <div dir="auto">Agreed, but it's already less convenient to give
          4000 -t's :-)</div>
      </div>
    </blockquote>
    <br>
    What's your point?<br>
    <br>
    <div class="moz-signature">-- <br>
      Angular momentum makes the world go 'round.</div>
  </body>
</html>

--------------wCJByNKdM6IjKRZC7riRHy3Y--