Re: Backup Postgre server

2009/11/23 AnthonyV <avequeau(at)gmail(dot)com>:
> Hello,
>
> I have a pg8.3 server (an 8.4 upgrade is scheduled) on Windows.
> I would like an other pg server in backup in case of hardware problem
> on my first server.
>
> I use read/write queries and I would like the second pg become master
> without any human intervention.

ugh. risky (see David's post)

>
> I saw Slony, but it seems that the backup server is a read-only server
> which is not good for me.

hmm, half-true.
with slony-I, the slave server is read only as long as the replication
is taking place.
when you detect that your master server is dead, you can issue a
FAILOVER and the slave will be fully operational (R/W).
see http://slony.info/documentation/failover.html

> I also saw pgpool. Is it a good solution for me? Does it work alone or
> with Slony? Is a Windows version exist?

Pgpool can work alone (in so-called replication mode), but it has some
important limitations (volatile functions).
Is it good for you? depends on your db usage patterns (what types of
queries are used)

AFAIK, most real world postgres clustering setups use a combination of
a load balancer (eg pgpool) and replication tool (eg Slony-I)

>
> Finally, I saw others products like pgcluster or Bucardo, but I don't
> understand the differences between all those products...
>

pgcluster is (at best) and early beta.
bucardo is similar to slony but claims to be asynchronous multi master
(not sure how it's possible :-)

did you see http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling
?

did you see http://www.postgresql.org/docs/8.4/static/continuous-archiving.html
?

cheers,

--
Filip Rembiałkowski
JID,mailto:filip(dot)rembialkowski(at)gmail(dot)com
http://filip.rembialkowski.net/