Thread

1. Best way to replicate a DB between two servers (master/slave)

   Mirko Zeibig <mirko@picard.inka.de> — 2000-12-20T23:18:14Z

   Hello everybody,
   I know there was an announcement on www.postgresql.com, that sometime in the
   future there will be a sort of replication mechanism for PostgreSQL.
   
   Now the problem:
   
   I have two servers, one providing content for a website (using PHP),
   anotherone where users are editing the contents. I now have to update the
   content-server on a regular base with the changes made in the
   editing-server. I thought of dumping the whole database through ssh to a new
   database on the content-server, then drop the old one and rename the new one.
   I guess the content to sth. around 5MB, so having a 5Mbit leased line,
   network traffic should be no problem.
   
   I see I will run into problems, when an old postgres-process is still
   connected to the database. Alternatively I thought of creating a
   modification timestamp for every recordset involved and pumping only the
   modificated sets to the content-server. I already have triggers running for
   providing information about updated/inserted recordsets. But what about
   deleted ones? I guess best would be to collect information about these in a
   seperate table and delete the ones on the content-server based on this table.
   
   Does anyone know of a more sensible way to get replication?
   
   Best Regards
   Mirko
   
   

2. Re: Best way to replicate a DB between two servers (master/slave)

   Sandeep Joshi <sjoshi@zambeel.com> — 2000-12-20T23:46:36Z

   Hi,
      Is there anyway of turning off triggers dynamically?
   
   Sandeep
   
   
   > Hello everybody,
   > I know there was an announcement on www.postgresql.com, that sometime in the
   > future there will be a sort of replication mechanism for PostgreSQL.
   >
   > Now the problem:
   >
   > I have two servers, one providing content for a website (using PHP),
   > anotherone where users are editing the contents. I now have to update the
   > content-server on a regular base with the changes made in the
   > editing-server. I thought of dumping the whole database through ssh to a new
   > database on the content-server, then drop the old one and rename the new one.
   > I guess the content to sth. around 5MB, so having a 5Mbit leased line,
   > network traffic should be no problem.
   >
   > I see I will run into problems, when an old postgres-process is still
   > connected to the database. Alternatively I thought of creating a
   > modification timestamp for every recordset involved and pumping only the
   > modificated sets to the content-server. I already have triggers running for
   > providing information about updated/inserted recordsets. But what about
   > deleted ones? I guess best would be to collect information about these in a
   > seperate table and delete the ones on the content-server based on this table.
   >
   > Does anyone know of a more sensible way to get replication?
   >
   > Best Regards
   > Mirko
   
   

3. Re: Best way to replicate a DB between two servers (master/slave)

   Martijn van Oosterhout <kleptog@cupid.suninternet.com> — 2000-12-21T11:18:55Z

   Mirko Zeibig wrote:
   > 
   > Hello everybody,
   > I know there was an announcement on www.postgresql.com, that sometime in the
   > future there will be a sort of replication mechanism for PostgreSQL.
   > 
   > Now the problem:
   > 
   > I have two servers, one providing content for a website (using PHP),
   > anotherone where users are editing the contents. I now have to update the
   > content-server on a regular base with the changes made in the
   > editing-server. I thought of dumping the whole database through ssh to a new
   > database on the content-server, then drop the old one and rename the new one.
   > I guess the content to sth. around 5MB, so having a 5Mbit leased line,
   > network traffic should be no problem.
   
   We do that here too. We have an internal server and an external server
   and we update the external server regularly.
   
   So we have a script called copytoexternal which does:
   
   1. Dumps the required tables to a tar.gz (not all tables are copied out)
   2. Copies the tar.gz externally
   3. Unpacks the tar.gz into a new database (same server) called "temp"
   4. Vacuums the new temp DB
   5. Then quickly, in one step:
     a. Stops the external DB server
     b. Switches the directories in the $PGDATA directory
     c. Restarts the postmaster
   6. Delete the old, now replaced external DB.
   
   The whole process takes about 15 minutes but the external DB is only
   out for 10 seconds or so. You can run it anytime really.
   
   We've found this quite reliable.
   
   Hope this helps...
   -- 
   Martijn van Oosterhout <kleptog@cupid.suninternet.com>
   http://cupid.suninternet.com/~kleptog/