如何从 .sql postgresql 备份恢复单个表? [英] How to restore a single table from a .sql postgresql backup?

问题描述

一个表的行被错误地从数据库中删除.我们有一个 db 备份，它会生成一个可以像这样恢复的 sql 文件:

A table's rows were mistakenly deleted from the database. We have a db backup which results in a sql file that can restored like so:

psql -h localhost -d proddump -f /Users/U/Desktop/prod_db_backup/PostgreSQL/site_prod.sql

这最终会在本地进行完整还原.但是我们需要的是将单个表的行恢复到生产中.关于如何在 PostgreSQL 9.1 中实现此功能的任何提示?

This ends up doing a full restore locally. But what we need is to restore a single table's rows to production. Any tips on how to make this work with PostgreSQL 9.1?

谢谢

推荐答案

如果需要单表还原等，不要做 SQL 备份 使用 pg_dump 的 -Fc 选项 - 自定义"格式.这可以使用 pg_restore 恢复.选择性恢复是可能的，还有各种其他方便的功能.pg_restore 可以在以后需要时将自定义格式的转储转换为 SQL 转储.

Don't do SQL backups if you need single table restore, etc. Use pg_dump's -Fc option - the "custom" format. This can be restored using pg_restore. Selective restore is possible, as are all sorts of other handy features. pg_restore can convert a custom-format dump into an SQL dump later if you need it.

如果您坚持使用现有的转储，您唯一的选择是:

If you're stuck with an existing dump, your only options are:

• 使用文本编辑器将目标表数据提取到一个单独的文件中，然后将其恢复；或

• Use a text editor to extract the target table data to a separate file and just restore that; or

将转储恢复到一次性数据库，然后使用 pg_dump 进行选择性转储，仅包括该表.由于它是一次性的，你可以在一些卸载的快速但不安全的机器上使用一个单独的 Pg 实例，在那里你打开所有让它快速但如果你喜欢吃我的数据"选项，比如 fsync=off.你应该永远在生产中设置它.

Restore the dump to a throwaway database then use pg_dump to take a selective dump including just that table. Since it's throwaway, you can use a separate Pg instance on some unloaded fast-but-unsafe machine where you turn on all the "make it fast but eat my data if you like" options like fsync=off. You should NEVER set that in production.

这篇关于如何从 .sql postgresql 备份恢复单个表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！