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

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.

--
Angular momentum makes the world go 'round.