使用 pg_restore 从较新版本的 PostgreSQL 恢复
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4599696/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Use pg_restore to restore from a newer version of PostgreSQL
提问by Phrogz
I have a (production) DB server running PostgreSQL v9.0 and a development machine running PostgreSQL v8.4. I would like to take a dump of the production DB and use it on the development machine. I cannot upgrade the postgres on the dev machine.
我有一台运行 PostgreSQL v9.0 的（生产）数据库服务器和一台运行 PostgreSQL v8.4 的开发机器。我想转储生产数据库并在开发机器上使用它。我无法在开发机器上升级 postgres。
On the production machine, I run:
在生产机器上，我运行：
pg_dump -f nvdls.db -F p -U nvdladmin nvdlstats
On the development machine, I run:
在开发机器上，我运行：
pg_restore -d nvdlstats -U nvdladmin nvdls.db
And I got this error:
我收到了这个错误：
pg_restore: [archiver] unsupported version (1.12) in file header
This occurs regardless of whether I choose the custom, tar, or plain_text format when dumping.
无论我在转储时选择 custom、tar 还是 plain_text 格式，都会发生这种情况。
I found one discussion onlinewhich suggests that I should use a newer version of pg_restoreon the dev machine. I tried this by simply copying the 9.0 binary to the dev machine, but this fails (not unexpectedly) due to linking problems.
我在网上找到了一个讨论，建议我应该pg_restore在开发机器上使用更新版本。我通过简单地将 9.0 二进制文件复制到开发机器来尝试此操作，但由于链接问题而失败（并非意外）。
I thought that the point of using a plain_text dump was that it would be raw, portable SQL. Apparently not.
我认为使用 plain_text 转储的目的是它是原始的、可移植的 SQL。显然不是。
How can I get the 9.0 DB into my 8.4 install?
如何将 9.0 DB 安装到 8.4 安装中？
回答by a_horse_with_no_name
pg_restore is onlyfor restoring dumps taken in the "custom" format.
pg_restore仅用于恢复以“自定义”格式获取的转储。
If you do a "plain text" dump you have to use psql to run the generated SQL script:
如果您执行“纯文本”转储，则必须使用 psql 来运行生成的 SQL 脚本：
psql -f nvdls.db dbname username
回答by Magnus Hagander
Using pg_dump/pg_restore to move from 9.0 to 8.4 is not supported - only moving forward is supported.
不支持使用 pg_dump/pg_restore 从 9.0 移动到 8.4 - 仅支持向前移动。
However, you can usually get the data across (in a data-only dump), and in some cases you can get the schema - but that's mostly luck, it depends on which features you're using.
但是，您通常可以获取数据（在仅数据转储中），并且在某些情况下您可以获得模式 - 但这主要是运气，这取决于您使用的功能。
You should normally use the target version of pg_dump and pg_restore - meaning in this case you should use the binaries from 8.4. But you should use the sameversion of pg_dump and pg_restore. Both tools will work fine across the network, so there should be no need to copy the binaries around.
您通常应该使用 pg_dump 和 pg_restore 的目标版本 - 这意味着在这种情况下您应该使用 8.4 中的二进制文件。但是你应该使用相同版本的 pg_dump 和 pg_restore。这两种工具都可以在网络上正常工作，因此不需要复制二进制文件。
And as a_horse_with_no_name says, you may be better off using pg_dump in plaintext mode - that will allow you to hand-edit the dump if necessary. In particular, you can make one schema only dump (with -s) and one data only dump - only the schema dump is likely to require any editing.
正如 a_horse_with_no_name 所说，您最好在纯文本模式下使用 pg_dump - 这将允许您在必要时手动编辑转储。特别是，您可以制作一个仅模式转储（使用 -s）和一个仅数据转储 - 只有模式转储可能需要任何编辑。
回答by Matthew
If the 9.0 database contains any bytea columns, then bigger problems await.
如果 9.0 数据库包含任何 bytea 列，则等待更大的问题。
These columns will be exported by pg_dump using the "hex" representation and appear in your dump file like:
这些列将由 pg_dump 使用“十六进制”表示导出，并出现在您的转储文件中，如：
SELECT pg_catalog.lowrite(0, '\x0a2')
选择 pg_catalog.lowrite(0, '\x0a2')
Any version of the postgres backend below 9.0 can't grok the hex representation of bytea, and I can't find an option to tell pg_dump on the 9.0 side to not use it. Setting the default "bytea_output" setting to ESCAPE for either the database or the whole server is seemingly ignored by pg_dump.
任何低于 9.0 的 postgres 后端版本都无法理解 bytea 的十六进制表示，而且我找不到告诉 9.0 端的 pg_dump 不使用它的选项。将数据库或整个服务器的默认“bytea_output”设置设置为 ESCAPE 似乎被 pg_dump 忽略了。
I suppose it would be possible to post-process the dump file and actually change every hex-encoded bytea value to an escaped one, but the risk of untraceably corrupting the kind of things normally stored in a bytea (images, PDFs etc) does not excite me.
我想有可能对转储文件进行后处理并将每个十六进制编码的 bytea 值实际更改为转义的值，但是无法追踪损坏通常存储在 bytea 中的东西（图像、PDF 等）的风险不会令我兴奋，...令我高兴，...令我感兴趣。
回答by Blake
I solved this by upgrading postgresql from 8.X to 9.2.4. If you're using brew on Mac OS-X, use -
我通过将 postgresql 从 8.X 升级到 9.2.4 解决了这个问题。如果您在 Mac OS-X 上使用 brew，请使用 -
brew upgrade postgresql
Once this is done, just make sure your new postgres installation is at the top of your path. It'll look something like (depending on the version installation path) -
完成此操作后，只需确保您的新 postgres 安装位于路径的顶部。它看起来像（取决于版本安装路径）-
export PATH=/usr/local/Cellar/postgresql/9.2.4/bin:$PATH
回答by Somnath Muluk
I had same issue. I used pgdump and psql for export/import DB.
我有同样的问题。我使用 pgdump 和 psql 来导出/导入数据库。
1.Set PGPASSWORD
1.设置PGPASSWORD
export PGPASSWORD='h0ld1tn0w';
2.Export DB with pg_dump
2.使用 pg_dump 导出数据库
pg_dump -h <<host>> -U <<username>> <<dbname>> > /opt/db.out
/opt/db.out is dump path. You can specify of your own.
/opt/db.out 是转储路径。您可以指定自己的。
3.Then set again PGPASSWORD of you another host. If host is same or password is same then this is not required.
3.然后再设置你另一台主机的PGPASSWORD。如果主机相同或密码相同，则不需要。
4.Import db at your another host
4.在你的另一台主机上导入数据库
psql -h <<host>> -U <<username>> -d <<dbname>> -f /opt/db.out
If username is different then find and replace with your local username in db.out file. And make sure on username is replaced and not data.
如果用户名不同，则在 db.out 文件中查找并替换为您的本地用户名。并确保用户名被替换而不是数据。
