如何从 .sql postgresql 备份恢复单个表？

How to restore a single table from a .sql postgresql backup?

提问by AnApprentice

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:

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

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

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?

这最终会在本地进行完整还原。但是我们需要的是将单个表的行恢复到生产中。关于如何使用 PostgreSQL 9.1 进行此操作的任何提示？

Thanks

谢谢

采纳答案by Craig Ringer

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

如果您需要单表还原等，请pg_dump-Fc不要进行 SQL 备份。使用's选项- “自定义”格式。这可以使用pg_restore. 选择性还原是可能的，还有各种其他方便的功能。pg_restore如果需要，可以稍后将自定义格式转储转换为 SQL 转储。

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

• Restore the dump to a throwaway database then use pg_dumpto 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 NEVERset that in production.

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

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

回答by Zouppen

I'm not aware of any tool for this, but this one-liner extracts precious_tablefrom my_backup.sqlfile:

我不知道有什么工具可以做到这一点，但是这个单行precious_table从my_backup.sql文件中提取：

sed -n '/^COPY precious_table /,/^\\.$/p' my_backup.sql

回答by mys

You can use grep + sed in roder to get table data:

您可以在roder中使用grep + sed来获取表数据：

First, you need to identify boundaries:

首先，您需要确定边界：

$ fgrep -Ehn '^(COPY |CREATE TABLE )' db.sql
49:CREATE TABLE test (
60:CREATE TABLE test2 (
71:CREATE TABLE test3 (
82:COPY test (i) FROM stdin;
100090:COPY test2 (i) FROM stdin;
200098:COPY test3 (i) FROM stdin;

In order to extract data for table test2:

为了提取表 test2 的数据：

sed -n '100090,200097p' < db.sql | sed -e 's/^COPY test2/COPY new_table_name/' > new_table_name.sql

Note, you need to subtract one from the second number (i.e exclude next copy stmt)

注意，您需要从第二个数字中减去一个（即排除下一个副本 stmt）

Now, you can load new_table_name.sqland restore data which you need. Now, you can load data into new table

现在，您可以加载new_table_name.sql和恢复您需要的数据。现在，您可以将数据加载到新表中

回答by Sergii Mostovyi

There is an easy way.

有一个简单的方法。

'pg_restore' has a '--table/-t' option.

'pg_restore' 有一个 '--table/-t' 选项。

pg_restore -a -t your_table /path/to/dump.sql

Use '-h' for remote host. See other options here

对远程主机使用“-h”。在此处查看其他选项

回答by vyegorov

I happen to have pg_dumpalldump around. And I would like to restore table named usersfrom the database named edc, as you most likely willhave equally named tables in different databases and even schemas.

我碰巧有pg_dumpall转储。我想恢复users从名为的数据库命名的表edc，因为您很可能会在不同的数据库甚至模式中拥有相同命名的表。

For my case, the following sedoneliner works:

就我而言，以下sedoneliner 有效：

sed -ne '/^\connect edc/,/^\connect/{/\susers\(\s\|$\)/,/;\|\\./p}' pg92.dump

What it does:

它能做什么：

1. /^\\connect edc/,/^\\connect/limits the search to be the scope of my database;
2. {…}will perform all inside commands for the range;
3. /\susers\(\s\|$\)/matches all lines with userson it's own, including at the end of the line;
4. /;\|\\\./matches lines with ;or containing \.
5. pforces the matching lines to be outputted (note, that sed is invoked with -n).

1. /^\\connect edc/,/^\\connect/将搜索限制在我的数据库范围内；
2. {…}将执行范围内的所有内部命令；
3. /\susers\(\s\|$\)/匹配所有行users，包括在行尾；
4. /;\|\\\./匹配带有;或包含的行\.
5. p强制输出匹配的行（注意， sed 是用 调用的-n）。

Depending on the complexity of your data, you might need to tweak this.

根据数据的复杂性，您可能需要对此进行调整。

All you have to do is to pipe sedoutput to the psqlcommand with right switches.

您所要做的就是使用正确的开关将sed输出通过管道传输到psql命令。

回答by P. Qualis

I've write some commands to restore only the DATA for one table, from a plain text pg_dumpall. Inspired from Zouppen, thanks. That's for french readers.

我已经编写了一些命令来仅从纯文本 pg_dumpall 恢复一个表的数据。灵感来自 Zouppen，谢谢。那是给法国读者的。

Restaurer les DONNéES d'une table à partir d'une sauvegarde 'plain text SQL' faite, par exemple, avec pg_dumpall

Restaurer les DONNéES d'une table à partir d'une sauvegarde '纯文本 SQL' faite, par exemple, avec pg_dumpall

1. se positionner dans le réperttheitroade [/data/bkp/] contenant le fichier de sauvegarde [pg_full_bak.dmp]

1. se positionner dans le réperttheitroade [/data/bkp/] contenant le fichier de sauvegarde [pg_full_bak.dmp]

$ cd /data/bkp/
$ ls -l

$ cd /data/bkp/
$ ls -l

1. extraire les données de la table "ma_table" du schéma "public" dans le fichier {ma_table.sql}

2. extraire les données de la table "ma_table" du schéma "public" dans le fichier {ma_table.sql}

$ sed -n '/^COPY public.ma_table /,/^\\.$/p' pg_full_bak.dmp > ma_table.sql
$ ls -l

$ sed -n '/^COPY public.ma_table /,/^\\.$/p' pg_full_bak.dmp > ma_table.sql
$ ls -l

1. s'assurer que les données sont bien celles attendues

   $ less ma_table.sql
   

2. se connecter sur la base [db_name] contenant la table [ma_table] vide à restaurer, avec un compte ayant les droits en écriture sur la table [ma_table]

   $ psql -d db_name
   

3. vérifier que la table à restaurer [ma_table] est bien vide

3. s'assurer que les données sont bien celles 出席

   $ less ma_table.sql
   

4. seconnecter sur la base [db_name] contenant la table [ma_table] vide à restaurer, avec un compte ayant les droits en écriture sur la table [ma_table]

   $ psql -d db_name
   

5. vérifier que la table à restaurer [ma_table] est bien vide

postgres@db_name# select count(*) nb from ma_table ;
+------+
|  nb  |
+------+
|   0  |
+------+

postgres@db_name# select count(*) nb from ma_table ;
+------+
|  nb  |
+------+
|   0  |
+------+

1. => importer les données effacées <=

   postgres@db_name# \i ma_table.sql
   

2. vérifier que les données ont bien été importées

   postgres@db_name# select count(*) nb
   from ma_table
   ;
   +-------+
   |   nb  |
   +-------+
   | 9876  |
   +-------+
   

6. => 进口商 les données effacées <=

   postgres@db_name# \i ma_table.sql
   

7. vérifier que les données ont bien été importées

   postgres@db_name# select count(*) nb
   from ma_table
   ;
   +-------+
   |   nb  |
   +-------+
   | 9876  |
   +-------+
   

That's working for me under pg 9.3

在第9.3页下这对我有用

The easy way : steps 2, 4 & 6

简单的方法：步骤 2、4 和 6