CODEWITHSUNDEEP
phppostgresql
aocferreira
aocferreira

Reputation: 693

sync two tables from two different PostgreSQL databases

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.

Upvotes: 3

Views: 4530

Answers (3)

Hitul
Hitul

Reputation: 357

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.

Upvotes: 0

Denis de Bernardy
Denis de Bernardy

Reputation: 78443

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.

Upvotes: 3

Dmitri Sologoubenko
Dmitri Sologoubenko

Reputation: 2909

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

Upvotes: 1

Related Questions