Postgresql 的 pg_dump 和 pg_restore 跨不同主要版本? [英] Postgresql's pg_dump and pg_restore across different major versions?

问题描述

最近我遇到了一个(可能是已知问题)，当 Postgre 的版本在主要编号上不同(总是升级，没有降级)，例如字段类型.

Recently I came across a (maybe known problem) when Postgre's versions differ in major number (always upgrade, no downgrade), for example with field types.

就我而言，冲突的数据很少，所以我手动更改了它，但想了解更多信息，以防我在使用更多数据时再次遇到此问题.

In my case, there was very little conflicting data so I changed it by hand, but wanted to know more, in case I come across this problem again with more data.

在这个具体案例中(但可以扩展到未来其他可能的问题)，我只使用数据插入创建了备份，因为我已经保存了表结构.

In this concrete case (but can be extended to other possible problems in the future), I created the backup using only data inserts, as I had already the table structure saved.

使用 money 类型从 8.x 升级到 9.x 时出现问题，我遇到错误，因为插入的值类似于

The problem came when upgrading from 8.x to 9.x with the money type, I got errors because the inserts had a value something like

INSERT INTO foo(...) VALUES (...,'EUR300',...);

所以 postgres 拒绝在 9.1 中插入它

So postgres was refusing to insert that in 9.1

我的想法，我尝试的是将该字段转换为 DECIMAL 并重做转储，这行得通，但在未来，是否有另一种机制，例如使用新的 pg_dump 连接到旧数据库，而不是当前的?(没有测试过)

My idea, and what I tried is to convert that field to DECIMAL and redoing the dump, that worked, but in a future, is there another mechanism, like using newer pg_dump connecting to old database, instead of current one? (Did not tested this)

推荐答案

在不同版本之间切换时，您应该始终使用 pg_dump 的新 版本.这意味着当您从 8.x 升级到 9.1 时，您应该使用 pg_dump 9.1 版.它通常应该处理任何必要的转换.

When going etween different versions, you should always use the new version of pg_dump. Meaning when you go from 8.x to 9.1, you should use pg_dump version 9.1. It should normally take care of any conversions necessary.

这篇关于Postgresql 的 pg_dump 和 pg_restore 跨不同主要版本?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！