使用PostgreSQL的PgAdmin-III导出DB [英] Export DB with PostgreSQL's PgAdmin-III
问题描述
如何将Postgresql数据库导出为可执行到其他 pgAdmin 的SQL？
How to export a Postgresql db into SQL that can be executed into other pgAdmin?
- 导出为备份文件，当版本有差异时不起作用
- 导出为SQL文件，尝试在不同的
pgAdmin
- Exporting as backup file, doesn't work when there's a difference in version
- Exporting as SQL file, does not execute when tried to run on a different
pgAdmin
我尝试导出一个DB $ pgAdmin III 但是当我尝试在其他 pgAdmin 中执行SQL时，会在SQL中引发错误，当我尝试还原备份文件时，会说有差异在版本中它不能进行导入/恢复。
I tried exporting a DB with pgAdmin III but when I tried to execute the SQL in other pgAdmin it throws error in the SQL, when I tried to "restore" a Backup file, it says there's a difference in version that it can't do the import/restore.
所以有一种安全的方式将DB导出为标准SQL，可以在 pgAdmin SQL编辑器，不管它是哪个版本？
So is there a "safe" way to export a DB into standard SQL that can be executed plainly in pgAdmin SQL editor, regardless of which version it is?
推荐答案
不要尝试使用PgAdmin-III。直接使用 pg_dump 和 pg_restore
Don't try to use PgAdmin-III for this. Use pg_dump and pg_restore directly if possible.
使用来自目标服务器的 pg_dump 的版本转储源服务器。所以如果你从（说）8.4到9.2，你将使用9.2的 pg_dump 来创建转储。如果您创建一个 -Fc 自定义格式转储（推荐），您可以使用 pg_restore 将其应用于新的数据库服务器。如果你做了一个常规的SQL转储，你可以用 psql 应用它。
Use the version of pg_dump from the destination server to dump the origin server. So if you're going from (say) 8.4 to 9.2, you'd use 9.2's pg_dump to create a dump. If you create a -Fc custom format dump (recommended) you can use pg_restore to apply it to the new database server. If you made a regular SQL dump you can apply it with psql.
现在，如果你试图降级，那是一个完整的混乱。
Now, if you're trying to downgrade, that's a whole separate mess.
你将很难创建一个SQL可以在任何版本的PostgreSQL中使用转储。假设您创建了一个使用 WITH 查询的VIEW。因为不支持 WITH ，PostgreSQL 8.3将不起作用。还有很多其他例子。如果您必须支持旧的PostgreSQL版本，请在您仍然支持的最旧版本上进行开发，然后将其转储为较新的版本加载。您不能在新版本上正式开发，并且对旧版本进行导出，如果有的话，它将无法正常工作。
You'll have a hard time creating an SQL dump that'll work in any version of PostgreSQL. Say you created a VIEW that uses a WITH query. That won't work when restored to PostgreSQL 8.3 because it didn't support WITH. There are tons of other examples. If you must support old PostgreSQL versions, do your development on the oldest version you still support and then export dumps of it for newer versions to load. You cannot sanely develop on a new version and export for old versions, it won't work well if at all.
更麻烦的是，在旧版本上开发不会总是给你在新版本上工作的代码。添加新引用的新规范功能的新关键字。有时问题的修改会影响用户代码。例如，如果你要在（古代和不支持的）8.2中开发，你将会在8.3及更高版本上对文本进行隐式转换有很多问题。
More troubling, developing on an old version won't always give you code that works on the new version either. Occasionally new keywords are added where support for new specification features are introduced. Sometimes issues are fixed in ways that affect user code. For example, if you were to develop on the (ancient and unsupported) 8.2, you'd have lots of problems with implicit casts to text on 8.3 and above.
你的最好的选择是测试所有支持的版本。考虑使用Jenkins CI等设置自动测试。是的，这是一个痛苦，但随着时间的推移，软件的价格就会提高。如果Pg保持完美的向前和向前兼容性，那么它永远不会改善。
Your best bet is to test on all supported versions. Consider setting up automated testing using something like Jenkins CI. Yes, that's a pain, but it's the price for software that improves over time. If Pg maintained perfect backward and forward compatibility it'd never improve.
这篇关于使用PostgreSQL的PgAdmin-III导出DB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
