[clug] [tech] Postgres Replication

[Grant Allen]

steve jenkin wrote:
> I'll be looking after some small MySQL and Postgres DB's and I'd like to
> run replication, if I can. There are some useful things to be done with
> a 'read-only' replica running on another system. And it gives us a
> reasonable, not instantaneous, 'fail-over' option.
> 
> Have read the MySQL doco, and it seems very sophisticated & is built-in.
> 
> Reading the Postgres docs has been less than enlightening.
> It seems Replication is provided by 3rd-party modules - and not nearly
> so well done.
> 
> I've found this link:
> <http://wiki.postgresql.org/wiki/Replication%2C_Clustering%2C_and_Connection_Pooling#Comparison_matrix>
> 
> Anyone know of any better resource?
> Anyone actually had to *do* either clustering or replication with
> Postgres (or can dob in a friend).
> 
> Do you know any alternative means?
> [eg Simplistic rsync copy of DB files or GFS or ...]
> 
> TIA
> 
> steve

Steve,

Postgres replication is most commonly done with Slony (Brendan's covered this in his post).  While not baked in to the engine, it certainly gets the job done for straight-forward replication scenarios.  It works well for us.

An alternative is the Londiste component of Skytools (http://wiki.postgresql.org/wiki/Skytools) ... this is the toolset Skype built to manage their rather large set of Postgres replicas.  Pretty cool stuff IMNSHO.

Ciao
Fuzzy
:-)