Thread: How to fully restore a single table from a custom dump?
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
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 VitaleOn 08/09/2022 8:06 AM EDT Thomas Kellerer <shammat@gmx.net> wrote:Hello,I just realized that usingpg_restore -t some_table ... some_dump_filedoesn't restore things like identity attributesor indexes on the specified table.The dump contains much more than just that table, so simplyusing pg_restore without -t is not an option.While I could extract the indexes manually using some clever regexon the index names, I don't see a way to make sure that identitydefinitions (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
Attachment
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
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 VitaleOn 08/09/2022 8:06 AM EDT Thomas Kellerer <shammat@gmx.net> wrote:Hello,I just realized that usingpg_restore -t some_table ... some_dump_filedoesn't restore things like identity attributesor indexes on the specified table.The dump contains much more than just that table, so simplyusing pg_restore without -t is not an option.While I could extract the indexes manually using some clever regexon the index names, I don't see a way to make sure that identitydefinitions (or sequence values) are restored properly for the selected table.Any ideas, how I can _fully_ restore a single table from a custom dump?ThomasCreating 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.
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 VitaleOn 08/09/2022 8:06 AM EDT Thomas Kellerer <shammat@gmx.net> wrote:Hello,I just realized that usingpg_restore -t some_table ... some_dump_filedoesn't restore things like identity attributesor indexes on the specified table.The dump contains much more than just that table, so simplyusing pg_restore without -t is not an option.While I could extract the indexes manually using some clever regexon the index names, I don't see a way to make sure that identitydefinitions (or sequence values) are restored properly for the selected table.Any ideas, how I can _fully_ restore a single table from a custom dump?ThomasCreating 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.
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 VitaleOn 08/09/2022 8:06 AM EDT Thomas Kellerer <shammat@gmx.net> wrote:Hello,I just realized that usingpg_restore -t some_table ... some_dump_filedoesn't restore things like identity attributesor indexes on the specified table.The dump contains much more than just that table, so simplyusing pg_restore without -t is not an option.While I could extract the indexes manually using some clever regexon the index names, I don't see a way to make sure that identitydefinitions (or sequence values) are restored properly for the selected table.Any ideas, how I can _fully_ restore a single table from a custom dump?ThomasCreating 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.
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 VitaleOn 08/09/2022 8:06 AM EDT Thomas Kellerer <shammat@gmx.net> wrote:Hello,I just realized that usingpg_restore -t some_table ... some_dump_filedoesn't restore things like identity attributesor indexes on the specified table.The dump contains much more than just that table, so simplyusing pg_restore without -t is not an option.While I could extract the indexes manually using some clever regexon the index names, I don't see a way to make sure that identitydefinitions (or sequence values) are restored properly for the selected table.Any ideas, how I can _fully_ restore a single table from a custom dump?ThomasCreating 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?
--
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 VitaleOn 08/09/2022 8:06 AM EDT Thomas Kellerer <shammat@gmx.net> wrote:Hello,I just realized that usingpg_restore -t some_table ... some_dump_filedoesn't restore things like identity attributesor indexes on the specified table.The dump contains much more than just that table, so simplyusing pg_restore without -t is not an option.While I could extract the indexes manually using some clever regexon the index names, I don't see a way to make sure that identitydefinitions (or sequence values) are restored properly for the selected table.Any ideas, how I can _fully_ restore a single table from a custom dump?ThomasCreating 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.
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.