Re: Enterprise readiness - mirroring / incremental backup

Kieran wrote:
> I'm currently starting to evaluate Open Source RDBMSs for use in a
> high-volume, high-availability environment.
>
> My main requirements are:
>
> 1. Ability to store approx 200Gb of data, with about 5Gb of data
> changing per day.

OK.

> 2. Support for high number of concurrent short transactions under
> REPEATABLE READ transaction isolation with row-level locking (or
> equivalent optimistic concurrency control).

We don't have REPEATABLE READ, as far as I know. We have READ COMMITTED
and SERIALIZABLE.

> 3. Fast (i.e. < 5 mins) failover time to a constantly mirrored secondary
> database server.

No mirroring. We are working on replication.

> 4. Ability to perform continous network backups such that in the event
> of both the primary database server and mirrored database server
> suffering total failure, no more than 1 hour of data is lost.

Nope. Point-in-time recovery will be in 7.4.

> First impressions are that PostgreSQL (and SAP DB, but definitely not
> MySQL) appears to meet requirements 1 & 2, but I'm not sure whether it
> (or any Open Source db) can currently meet requirements 3 & 4.

Right.

> My understanding is that while PostgreSQL offers hot backups "out of the
> box", it only offers full backups and does not have built in support for
> mirroring. Clearly, backing up 200Gb of data hourly is not feasible.

Right.

> Are there any third part solutions capable of making PostgreSQL meet
> requirements 3 & 4?

There are master-slave replications in /contrib, specificially rserv and
dbmirror.

> I'd imagine it may be possible to satisfy 3. using file system level
> mirroring, but I'd appreciate it if someone could confirm this.

Uh, yes, you can use RAID.

> My last question is somewhat pie-in-the sky, but assuming that
> PostgreSQL cannot currently meet requirements 3 & 4 even with 3rd party
> solutions, what are people's gut reactions to whether a small team (e.g.
> 5-6) of experienced, full-time paid developers could add mirroring and
> incremental backup support to PostgreSQL within 18 months?

Easily done. We have a point-in-time recovery patch ready for 7.4
already. Full multi-master replication is being worked on at:

http://gborg.postgresql.org/project/pgreplication/projdisplay.php

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073