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

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.

--
Guillaume.