pg_restore 或 pg_dump 是否向模式添加语句？
我有一个数据库，我已经使用了一段时间了。我稍微改进了它的模式，并希望比较新模式和旧模式之间的差异，因此我转储了这两个模式并进行了比较。在旧模式中，我看到一些“OPERATOR FAMILY”语句在新模式中不可见。如果有什么区别，这些“OPERATOR FAMILY”语句与 PostgreSQL 的 tsearch2 contrib 包相关（例如：“CREATE OPERATOR FAMILY gin_tsvector_ops”等）。与 tsearch2 运算符相关的“OPERATOR CLASS”语句出现在这两个模式中。此外，所有添加的“OPERATOR FAMILY”语句后面都跟着同名的“OPERATOR CLASS”语句，尽管“OPERATOR CLASS”语句在两个模式中都可以找到。
真正奇怪的是，当我使用新模式创建数据库时，使用 pg_dump 转储其模式，使用 pg_restore 将其恢复到另一个数据库中，然后再次转储它 - 'OPERATOR FAMILY' 语句出现！我 grep 了 contrib 目录，但找不到任何“CREATE OPERATOR FAMILY”语句。
有人有类似的经历吗？有什么想法可能会引入这些陈述吗？
I have a database that I've been using since a while now. I improved its schema a bit and wanted the compare the differences between the new schema and the old schema and so I dumped both schemas and did a diff. In the old schema, I'm seeing some 'OPERATOR FAMILY' statements that are not visible in the new schema. If it makes any difference, these 'OPERATOR FAMILY' statements relate to the tsearch2 contrib package for PostgreSQL (eg: 'CREATE OPERATOR FAMILY gin_tsvector_ops', etc). The 'OPERATOR CLASS' statements relating to tsearch2 operators are present in both the schemas. Also all of the added 'OPERATOR FAMILY' statements are followed by 'OPERATOR CLASS' statements of the same name, though the 'OPERATOR CLASS' statements are found in both the schemas.
Where it gets really strange is that when I create a database with the new schema, dump its schema using pg_dump, restore it in another database using pg_restore and then dump it again - the 'OPERATOR FAMILY' statements show up! I grep'd the contrib directory but I can't find any 'CREATE OPERATOR FAMILY' statements.
Anyone had a similar experience? Any ideas what might be introducing those statements?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
针对您的评论，我猜测是 PostgreSQL 版本迁移造成的。在 7.2 中 tsearch2 是一个外部 contrib 模块，但在 PostgreSQL 8.3 中它被集成到核心中。因此，那些缺失的 OPERATOR FAMILY 可能来自 PostgreSQL 8.3 的 pg_catalog 而不是您自己的模式。
但我认为你可以使用
contrib/uninstall_tsearch2.sql脚本来修复它（通常在/usr/share/postgresql-VER/contrib/) 卸载，然后使用contrib/tsearch2.sql脚本重新安装。忽略您遇到的任何错误，这些错误应该是安全的，因为您无法删除当前在表中使用的类型。In response to your comment, I would guess the PostgreSQL version migration caused this. In 7.2 tsearch2 was an external contrib module, but in PostgreSQL 8.3 it was integrated into core. So those missing OPERATOR FAMILYs probably come from PostgreSQL 8.3's pg_catalog instead of your own schema.
But I think you can fix it up using the
contrib/uninstall_tsearch2.sqlscript (usually in/usr/share/postgresql-VER/contrib/) to uninstall and then re-install with thecontrib/tsearch2.sqlscript. Ignore any errors you get, those should be safe, since you can't drop types that you're currently using in your tables.