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

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(at)cupid(dot)suninternet(dot)com>
http://cupid.suninternet.com/~kleptog/