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

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 :-)

--
Guillaume.

>