在PostgreSQL中镜像特定的表 [英] Mirror specific tables in postgreSQL

问题描述

这个问题很简单，但是我找不到关于它的任何文档：

The question is rather simple, but I can't find any documentation on it:

如何将特定表从数据库镜像到另一个表？

How can I mirror specific tables from a database to another?

基本思想是让两个孪生数据库之间仅共享特定表

The basic idea is having twin databases that share only specific tables between them

任何建议都将不胜感激！如果PostgreSQL无法做到这一点，那么还有另一个RDBMS可以做到吗？
预先感谢！

Any advice will be appreciated! If PostgreSQL can't do it, is there another RDBMS that can? Thanks in advance!

编辑：
我要这样做的原因是共享任意信息使用django跨两个数据库，而不会丢失适当的参照完整性。
例如：

EDIT : The reason I want to do this is to share "arbitrary" info across two databases using django, without losing proper referential integrity. For example:

假设我们有一个客户，产品和销售表。我们希望
在两家公司之间共享我们的客户和产品基础，而不是
的销售额。这可以扩展到任何特定情况（股票
而不是客户，用户而不是权限等）。因此，我认为
最简单的解决方案是在数据库之间共享特定的表。如果
有更好的解决方法，请随时分享
的经验！预先感谢

Let's say we have a clients, products and sales tables. We want to share our client and product base between two companies, but not our sales. This can be extended to any particular situation (share stocks but not clients, users but not permissions, etc). So I thought the easiest solution was to share specific tables among databases. If there is a better approach to the problem, feel free to share your experiences! Thanks in advance

推荐答案

可能性很小：

• 主/主复制（Bucardo），主/从复制（Slony）

• Master/Master replication (Bucardo), Master/Slave replication (Slony)

使用外部数据包装器-您可以访问其他数据库中的任何表。 9.2提供舒适的FDW只读驱动程序，9.3包含读/写FDW驱动程序

Using foreign data wrappers - you can access a any table from other databases. 9.2 provide comfort FDW read only driver, 9.3 contains read/write FDW driver

CREATE EXTENSION postgres_fdw ;
CREATE SERVER omega FOREIGN DATA WRAPPER postgres_fdw 
   OPTIONS (host 'localhost', dbname 'other_database');
CREATE USER MAPPING FOR pavel SERVER omega;
CREATE FOREIGN TABLE oo (a int) SERVER omega;

postgres=# EXPLAIN ANALYZE VERBOSE SELECT * FROM oo WHERE a BETWEEN 1 AND 100;

FDW可能是最简单的共享数据的解决方案。

FDW is probably most simple solution how to share data.

这篇关于在PostgreSQL中镜像特定的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！