Re: How to fully restore a single table from a custom dump?

Guillaume Lelarge <guillaume@xxxxxxxxxxxx> · Tue, 9 Aug 2022 22:17:21 +0200

Le mar. 9 août 2022 à 19:18, Ron <ronljohnsonjr@xxxxxxxxx> a écrit :

    On 8/9/22 12:13, Guillaume Lelarge wrote:

          Le mar. 9 août 2022,
            18:41, Ron <ronljohnsonjr@xxxxxxxxx>
            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@xxxxxxx>
                        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?

My point is that if you have 4k tables to dump, it's already a burden with or without indices and constraints. It's gonna be hard anyway.

-- 
Guillaume.