pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to fully restore a single table from a custom dump?
10+ messages / 6 participants
[nested] [flat]
* How to fully restore a single table from a custom dump?
@ 2022-08-09 12:06 Thomas Kellerer <shammat@gmx.net>
0 siblings, 2 replies; 10+ messages in thread
From: Thomas Kellerer @ 2022-08-09 12:06 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: How to fully restore a single table from a custom dump?
@ 2022-08-09 12:49 MichaelDBA Vitale <michaeldba@sqlexec.com>
parent: Thomas Kellerer <shammat@gmx.net>
1 sibling, 1 reply; 10+ messages in thread
From: MichaelDBA Vitale @ 2022-08-09 12:49 UTC (permalink / raw)
To: Thomas Kellerer <shammat@gmx.net>; pgsql-admin
<!doctype html>
<html>
<head>
<meta charset="UTF-8">
</head>
<body>
<div>
Hi,
</div>
<div class="default-style">
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.
</div>
<div class="default-style">
</div>
<div class="default-style">
Regards,
</div>
<div class="default-style">
Michael Vitale
</div>
<div class="default-style">
</div>
<blockquote type="cite">
<div>
On 08/09/2022 8:06 AM EDT Thomas Kellerer <<a href="mailto:shammat@gmx.net">shammat@gmx.net</a>> wrote:
</div>
<div>
</div>
<div>
</div>
<div>
Hello,
</div>
<div>
</div>
<div>
I just realized that using
</div>
<div>
</div>
<div>
pg_restore -t some_table ... some_dump_file
</div>
<div>
</div>
<div>
doesn't restore things like identity attributes
</div>
<div>
or indexes on the specified table.
</div>
<div>
</div>
<div>
The dump contains much more than just that table, so simply
</div>
<div>
using pg_restore without -t is not an option.
</div>
<div>
</div>
<div>
While I could extract the indexes manually using some clever regex
</div>
<div>
on the index names, I don't see a way to make sure that identity
</div>
<div>
definitions (or sequence values) are restored properly for the selected table.
</div>
<div>
</div>
<div>
Any ideas, how I can _fully_ restore a single table from a custom dump?
</div>
<div>
</div>
<div>
Thomas
</div>
</blockquote>
</body>
</html>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: How to fully restore a single table from a custom dump?
@ 2022-08-09 13:21 Holger Jakobs <holger@jakobs.com>
parent: MichaelDBA Vitale <michaeldba@sqlexec.com>
0 siblings, 1 reply; 10+ messages in thread
From: Holger Jakobs @ 2022-08-09 13:21 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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@gmx.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.
Best regards,
Holger
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachments:
OpenPGP_signature (application/pgp-signature, 202B) download
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: How to fully restore a single table from a custom dump?
@ 2022-08-09 13:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: Thomas Kellerer <shammat@gmx.net>
1 sibling, 1 reply; 10+ messages in thread
From: Tom Lane @ 2022-08-09 13:27 UTC (permalink / raw)
To: Thomas Kellerer <shammat@gmx.net>; +Cc: pgsql-admin
Thomas Kellerer <shammat@gmx.net> writes:
> Any ideas, how I can _fully_ restore a single table from a custom dump?
"pg_restore -l -v" will give you a table-of-contents with
dependencies, like this:
8155; 0 40431 MATERIALIZED VIEW DATA public analytics_materialized_view postgres
; depends on: 960 8161
This is object number 8155 and it depends on objects 960 and 8161.
Find the table you want, then look for things with dependencies
on it (some of them may not be things you want to restore).
Edit away all the lines for things you don't want, then feed
the reduced list to "pg_restore -L".
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: How to fully restore a single table from a custom dump?
@ 2022-08-09 16:41 Ron <ronljohnsonjr@gmail.com>
parent: Holger Jakobs <holger@jakobs.com>
0 siblings, 1 reply; 10+ messages in thread
From: Ron @ 2022-08-09 16:41 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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@gmx.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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: How to fully restore a single table from a custom dump?
@ 2022-08-09 17:13 Guillaume Lelarge <guillaume@lelarge.info>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Guillaume Lelarge @ 2022-08-09 17:13 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Le mar. 9 août 2022, 18:41, Ron <ronljohnsonjr@gmail.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@gmx.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.
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: How to fully restore a single table from a custom dump?
@ 2022-08-09 17:18 Ron <ronljohnsonjr@gmail.com>
parent: Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 1 reply; 10+ messages in thread
From: Ron @ 2022-08-09 17:18 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On 8/9/22 12:13, Guillaume Lelarge wrote:
> Le mar. 9 août 2022, 18:41, Ron <ronljohnsonjr@gmail.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@gmx.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?
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: How to fully restore a single table from a custom dump?
@ 2022-08-09 20:17 Guillaume Lelarge <guillaume@lelarge.info>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Guillaume Lelarge @ 2022-08-09 20:17 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Le mar. 9 août 2022 à 19:18, Ron <ronljohnsonjr@gmail.com> a écrit :
> On 8/9/22 12:13, Guillaume Lelarge wrote:
>
> Le mar. 9 août 2022, 18:41, Ron <ronljohnsonjr@gmail.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@gmx.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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: How to fully restore a single table from a custom dump?
@ 2022-08-09 20:25 Ron <ronljohnsonjr@gmail.com>
parent: Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 0 replies; 10+ messages in thread
From: Ron @ 2022-08-09 20:25 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On 8/9/22 15:17, Guillaume Lelarge wrote:
> Le mar. 9 août 2022 à 19:18, Ron <ronljohnsonjr@gmail.com> a écrit :
>
> On 8/9/22 12:13, Guillaume Lelarge wrote:
>> Le mar. 9 août 2022, 18:41, Ron <ronljohnsonjr@gmail.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@gmx.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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: How to fully restore a single table from a custom dump?
@ 2022-08-10 05:14 Thomas Kellerer <shammat@gmx.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 10+ messages in thread
From: Thomas Kellerer @ 2022-08-10 05:14 UTC (permalink / raw)
To: ; +Cc: pgsql-admin
Tom Lane schrieb am 09.08.2022 um 15:27:
> Thomas Kellerer <shammat@gmx.net> writes:
>> Any ideas, how I can _fully_ restore a single table from a custom dump?
>
> "pg_restore -l -v" will give you a table-of-contents with
> dependencies, like this:
>
> 8155; 0 40431 MATERIALIZED VIEW DATA public analytics_materialized_view postgres
> ; depends on: 960 8161
>
> This is object number 8155 and it depends on objects 960 and 8161.
>
> Find the table you want, then look for things with dependencies
> on it (some of them may not be things you want to restore).
> Edit away all the lines for things you don't want, then feed
> the reduced list to "pg_restore -L".
Ah, thanks. I wasn't aware that -v would include dependencies.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2022-08-10 05:14 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-08-09 12:06 How to fully restore a single table from a custom dump? Thomas Kellerer <shammat@gmx.net>
2022-08-09 12:49 ` MichaelDBA Vitale <michaeldba@sqlexec.com>
2022-08-09 13:21 ` Holger Jakobs <holger@jakobs.com>
2022-08-09 16:41 ` Ron <ronljohnsonjr@gmail.com>
2022-08-09 17:13 ` Guillaume Lelarge <guillaume@lelarge.info>
2022-08-09 17:18 ` Ron <ronljohnsonjr@gmail.com>
2022-08-09 20:17 ` Guillaume Lelarge <guillaume@lelarge.info>
2022-08-09 20:25 ` Ron <ronljohnsonjr@gmail.com>
2022-08-09 13:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-08-10 05:14 ` Thomas Kellerer <shammat@gmx.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox