sync two tables from two different PostgreSQL databases

I have two tables from two different databases and I want a php function to syncr开发者_运维知识库onize 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.

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

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.

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.

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 :)

<?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! :)

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.