Re: Postgres WarmStandby using ZFS or Snapshot to create Web DB?

On Thu, 29 Nov 2007, Jennifer Spencer wrote:

> 4. We need to have a Warm Standby available in case of disaster. We plan to
> use PITR with WAL files for this (again, unless there is a compelling reason
> not to).
> ...
> Another plan involves using a Solaris 10 ZFS solution to clone the warm
> standby B's files to act as a web database's files (see:
> http://www.lethargy.org/~jesus/archives ... crack.html for more). I am
> not sure either one of the above solutions will work quickly. We'd like
> a turnaround time from A to B to Web of less than 30 minutes for
> newly-created tables, or new data in existing tables.

As long as you're using PostgreSQL 8.2 you can set the archive_timeout to
force a new WAL file to hop over at whatever frequency you want.  That
will keep the turnaround time from A->B under control.  Something like 10
minutes is completely reasonable, and you could go shorter (at the cost of
increasing overhead) if you wanted.

Once you get the warm standby consuming new WAL files, the time to clone
the ZFS read/write mirror is pretty short.  Theo said about a minute in
his case in that article.  I would guess you could easily support going
from A->Web every 15 minutes by this path, other than the pause during
your weekly backup.

I'm sure someone else will chime in about your Slony questions.

> I am new to PostGres and the idea of no tech support phone calls when
> things break is a scary one!

You can get phone numbers if that's important to you. Check out
http://www.postgresql.org/support/professional_support for a long list of
people and companies.  To point out a couple of examples you'll find
there, based on the things you seemed concerned about:

-That ZFS article you referenced comes from the founder of OmniTI

-If you have problems with PITR, a phone number that leads toward
the person who probably wrote the code you're worried about is on the
2ndQuadrant site

-There's a whole menu going from PostgreSQL incident support to 24x7
packages with Slony over at Command Prompt

Those are just three obvious ones, you can explore yourself to see if they
or others seems like a good fit to what you want.  Think of it this way:
instead of one phone number to call for support, now you've got
dozens--and that's not even considering the free help you can often find
on these mailing lists.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD