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

On 8/9/22 15:17, Guillaume Lelarge wrote:
> Le mar. 9 août 2022 à 19:18, Ron <ronljohnsonjr(at)gmail(dot)com> a écrit :
>
> On 8/9/22 12:13, Guillaume Lelarge wrote:
>> Le mar. 9 août 2022, 18:41, Ron <ronljohnsonjr(at)gmail(dot)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(at)gmx(dot)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?
>
>
> 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.

At some point, you just restore the whole database, and then drop what you
don't need.

--
Angular momentum makes the world go 'round.