sync two tables from two different PostgreSQL databases

Question

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.

Answer 1

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.

Answer 2

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