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.
我有一台(生产)DB服务器运行PostgreSQL v9.0，还有一台运行PostgreSQL v8.4的开发机器。我想把生产的DB转储在开发机器上使用。我不能升级开发机器上的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.
无论我在转储时选择自定义、tar还是明文格式，都会发生这种情况。
I found one discussion online which suggests that I should use a newer version of pg_restore on 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.
我认为使用明文转储的意义在于它是原始的、可移植的SQL。显然不是。
How can I get the 9.0 DB into my 8.4 install?
如何将9.0 DB插入到8.4安装中?
5 个解决方案
#1
26
pg_restore is only for 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
#2
3
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 same version 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)和一个数据只转储—只有模式转储可能需要任何编辑。
#3
2
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.
postgres后端在9.0以下的任何版本都无法理解bytea的十六进制表示，我也找不到一个选项来告诉9.0端的pg_dump不要使用它。为数据库或整个服务器设置默认的“bytea_output”设置，似乎被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.
我认为有可能对转储文件进行后处理，并将每个hex编码的bytea值更改为一个转义的值，但是不可跟踪地破坏通常存储在bytea(图像、PDFs等)中的东西的风险不会让我感到兴奋。
#4
2
I solved this by upgrading postgresql from 8.X to 9.2.4. If you're using brew on Mac OS-X, use -
我通过从8升级postgresql解决了这个问题。9.2.4 X。如果你在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
#5
0
I had same issue. I used pgdump and psql for export/import DB.
我有同样的问题。我将pgdump和psql用于导出/导入DB。
1.Set PGPASSWORD
1。设置PGPASSWORD
export PGPASSWORD='h0ld1tn0w';
2.Export DB with pg_dump
2。出口DB 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。转储文件路径。您可以指定您自己的。
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所示。在另一个主机上导入db
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中查找并替换您的本地用户名。出文件。确保用户名被替换，而不是数据。
#1
26
pg_restore is only for 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
#2
3
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 same version 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)和一个数据只转储—只有模式转储可能需要任何编辑。
#3
2
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.
postgres后端在9.0以下的任何版本都无法理解bytea的十六进制表示，我也找不到一个选项来告诉9.0端的pg_dump不要使用它。为数据库或整个服务器设置默认的“bytea_output”设置，似乎被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.
我认为有可能对转储文件进行后处理，并将每个hex编码的bytea值更改为一个转义的值，但是不可跟踪地破坏通常存储在bytea(图像、PDFs等)中的东西的风险不会让我感到兴奋。
#4
2
I solved this by upgrading postgresql from 8.X to 9.2.4. If you're using brew on Mac OS-X, use -
我通过从8升级postgresql解决了这个问题。9.2.4 X。如果你在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
#5
0
I had same issue. I used pgdump and psql for export/import DB.
我有同样的问题。我将pgdump和psql用于导出/导入DB。
1.Set PGPASSWORD
1。设置PGPASSWORD
export PGPASSWORD='h0ld1tn0w';
2.Export DB with pg_dump
2。出口DB 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。转储文件路径。您可以指定您自己的。
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所示。在另一个主机上导入db
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中查找并替换您的本地用户名。出文件。确保用户名被替换，而不是数据。