从两个不同的PostgreSQL数据库同步两个表

I have two tables from two different databases and I want a php function to syncronize the data, so that the table number 2 can always verify the content on the table 1 and update it's information. Anyone has one example on how to do that? Thanks in advance.

我有来自两个不同数据库的两个表,我想要一个php函数来同步数据,因此表号2总是可以验证表1上的内容并更新它的信息。任何人都有一个如何做到这一点的例子?提前致谢。

3 个解决方案

#1

this example will connect to both the databases, and for each of the first db's authors will update the destination db's author with the same id. Of course you have to set up any necessary check, search and other details before perform and update (or an insert or replace if you prefer), but it fully depends on what you're going to do :)

此示例将连接到两个数据库,并且对于每个第一个db的作者将使用相同的ID更新目标db的作者。当然,您必须在执行和更新之前设置任何必要的检查,搜索和其他详细信息(或者如果您愿意,可以插入或替换),但这完全取决于您要做什么:)

<?php

if (false !== ($con1 = pg_connect("your source connection string"))) {
  if (false !== ($con2 = pg_connect("your dest connection string"))) {
    if (false !== ($result = pg_query($con1, "SELECT id, author, email FROM authors"))) {
      while (false !== ($row = pg_fetch_assoc($result))) {
        pg_query($con2, "UPDATE authors SET email=".pg_escape_string($con2, $row['email']).
          'WHERE id='.pg_escape_string($con2, $row['id']));
      }
      pg_free_result($result);
    }
    pg_close($con2);
  }
  pg_close($con1);
}

?>

I hope it was useful. Please feel free to ask any question about it. Enjoy! :)

我希望它很有用。请随时询问有关它的任何问题。请享用! :)

#2

D.S.'s answer will get the job done.

D.S.的答案将完成工作。

You could also look into setting up an after insert/update trigger and using dblink. That way, they'll be kept in sync without you needing to worry about it in PHP.

您还可以考虑设置插入/更新后触发器并使用dblink。这样,它们将保持同步,而无需在PHP中担心它。

As a side note, be very wary of what might happen on DB errors in either case. You can end up losing sync with either solution when DB errors occur, because the transactions will be autonomous.

作为旁注,请注意在任何一种情况下DB错误可能发生的情况。发生数据库错误时,您最终可能会失去与任一解决方案的同步,因为事务将是自治的。

#3

Create trigger on Insert, Update, Delete. When trigger procedure called store all the changes done in operation(Insert, update or delete) into database table(let's call this sync_table). Run some script which will copy data from sync_table to another database table. sync_table will store what data modified, inserted and deleted.

在插入,更新,删除时创建触发器。当触发程序将所有在操作中完成的更改(插入,更新或删除)存储到数据库表中时(让我们调用此sync_table)。运行一些脚本,将sync_table中的数据复制到另一个数据库表。 sync_table将存储修改,插入和删除的数据。

#1

<?php

if (false !== ($con1 = pg_connect("your source connection string"))) {
  if (false !== ($con2 = pg_connect("your dest connection string"))) {
    if (false !== ($result = pg_query($con1, "SELECT id, author, email FROM authors"))) {
      while (false !== ($row = pg_fetch_assoc($result))) {
        pg_query($con2, "UPDATE authors SET email=".pg_escape_string($con2, $row['email']).
          'WHERE id='.pg_escape_string($con2, $row['id']));
      }
      pg_free_result($result);
    }
    pg_close($con2);
  }
  pg_close($con1);
}

?>