Postgres中的离线受限多主机 [英] offline limited multi-master in Postgres

问题描述

站点A将生成一组记录。他们每晚都会备份数据库并将其ftp传送到站点B。站点B根本不会修改这些记录，但是会添加更多记录，其他表将在站点A的记录中创建FK。

Site A will be generating a set of records. Nightly they will backup their database and ftp it to Site B. Site B will not be modifying those records at all, but will be adding more records and other tables will be creating FK's to Site A's records.

因此，从本质上讲，我需要设置一个系统，以从站点A的转储中获取所有增量更改（主要是插入和更新，但可能进行一些删除），并将其应用于站点B。

So, essentially, I need to setup a system to take all the incremental changes from Site A's dump (mostly inserts and updates, but some deletes possible) and apply them at Site B.

目前，我们正在使用Postgres 8.3，但如果有价值的话，可以进行升级。

At this point, we're using Postgres 8.3, but could upgrade if valuable.

我相信我可以相对简单地做到这一点- Bucardo ，但是在设置Linux之前，我很想听听替代方法（或确认Bucardo）

I believe I can do this relatively straight-forwardly with Bucardo but I'd love to hear alternatives (or confirmation of Bucardo) before I setup a linux box to test it out.

推荐答案

大多数每种复制解决方案都会帮到您。 Postgres Wiki上有一个有关该主题的章节。但是你的情况很简单。我只会使用 dblink 。

这是从我的一个有效实现中总结出来的：

Most every replication solution would do your trick. The Postgres Wiki has a chapter on the topic. But your case is simple enough. I would just use dblink.
This is generalized from a working implementation of mine:

1. 在主数据库中创建视图返回更新的行。
   
   我们称其为 myview 。

在从数据库db 中为每个表创建一个函数，该函数通过dblink来获取行：

Create one function per table in the slave db that fetches rows via dblink:

CREATE OR REPLACE FUNCTION f_lnk_mytbl()
  RETURNS TABLE(col_a integer, col_b text, col_c text) AS
$func$
   SELECT *
   FROM   public.dblink('SELECT col_a, col_b, col_c FROM myview')
                      AS b(col_a integer, col_b text, col_c text);
$func$  LANGUAGE sql SECURITY DEFINER;

REVOKE ALL ON FUNCTION f_lnk_mytbl() FROM public;
GRANT EXECUTE ON FUNCTION f_lnk_mytbl() TO my_user;

1. 在 slave db 建立和关闭服务器连接。

1. Use above function in another function in the slave db that establishes and closes the server connection.

CREATE OR REPLACE FUNCTION f_mysync()
  RETURNS void AS
$func$
BEGIN
   PERFORM dblink_connect(
          'hostaddr=123.45.67.89 port=5432 dbname=mydb user=postgres password=secret');

   -- Fetch data into local temporary table for fast processing.
   CREATE TEMP TABLE tmp_i ON COMMIT DROP AS
   SELECT * FROM f_lnk_mytbl();

   -- *Or* read local files into temp tables with COPY so you don't need dblink.
   -- UPDATE what's already there (instead of DELETE, to keep integrity).
   UPDATE mytbl m
   SET   (  col_a,   col_b,   col_c) =
         (i.col_a, i.col_b, i.col_c)
   FROM   tmp_i i
   WHERE  m.id = i.id
   AND   (m.col_a, m.col_b, m.col_c) IS DISTINCT FROM
         (i.col_a, i.col_b, i.col_c);

   -- INSERT new rows
   INSERT INTO mytbl
   SELECT * FROM tmp_i i
   WHERE  NOT EXISTS (SELECT 1 FROM mytbl m WHERE m.id = i.id);

   -- DELETE anything? More tables?

   PERFORM dblink_disconnect();
END
$func$  LANGUAGE plpgsql SECURITY DEFINER;

REVOKE ALL ON FUNCTION f_mysync() FROM public;
GRANT EXECUTE ON FUNCTION f_mysync() TO my_user;

1. 现在，此呼叫就是您所需要的。以超级用户或 my_user 的身份调用。安排cronjob之类的事情。

1. Now, this call is all you need. Call as superuser or as my_user. Schedule a cronjob or something.

SELECT f_sync_mytbl();

在PostgreSQL 9.1或更高版本中也有新的创建外表。可能会更优雅。

In PostgreSQL 9.1 or later there is also the new CREATE FOREIGN TABLE. Might be more elegant.

这篇关于Postgres中的离线受限多主机的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！