Thread: Postgres WarmStandby using ZFS or Snapshot to create Web DB?
I am looking for suggestions in setting up a large postgres database scenario. We are running a science project with a lot of data expected from the science instrument. If you have time to comment, any advice is most welcome! Here's the deal: 1. We expect to store ~1TB per year of data for 10 years. Mostly this is a warehouse situation - not a lot of updates, or deletes, but a lot of inserts. 2. We need to replicate a subset of our postgres data to an international science community, and in turn take in some data from them (we plan to do this via Slony-1 unless there is a compelling reason not to). 3. We need to make a copy of our database available to the general internet community. 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). 5. We need to make regular full tape backups (~weekly) and occasionally scheduled maintenance (think quarterly maintenance). We do not have an endless budget, sadly, so I could use some help as to how to go about this. Having gone from a job where my database software actually had paid tech support to one that doesn't (PostGres), I am pretty concerned about what could go wrong. Assume our Primary server (A) is good enough to serve our in-house users, and our Warm Standby (B) is a physical duplicate of A. My plan is to copy WAL files to B. Make a tape backup from B weekly, keeping it out of recovery mode for ~6 hours, or alternatively make a snapshot of B's data files at a given time and tape off the snapshot. This takes care of A & B, and the backups, but what about the other requirements? How do we get data to our web community w/out fear of hacking to the primary? And how do we do that economically? There is one plan in place to use a set of snapshot disks from A's data files to act as the web database's files. Can we do that? Is that exceptionally stupid? 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. Lastly, we plan to pinhole our firewall for trusted Slony-1 science "customers". People that we already know who have specific IP addresses. We have yet to figure out the drag to our Primary (A) due to Slony-1. Any experience with that out there? My prior work experience involves a 1TB Sybase database, its warm-standby and regular backups & quarterly maintenance. I am new to PostGres and the idea of no tech support phone calls when things break is a scary one! I am trying to create a belt-and-suspenders redundant solution so that if something breaks, I have time to figure out what went wrong and fix it before the users even know there's a problem.
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
On Thursday 29 November 2007 16:08, Jennifer Spencer wrote: > I am looking for suggestions in setting up a large postgres database > scenario. We are running a science project with a lot of data expected from > the science instrument. If you have time to comment, any advice is most > welcome! > > Here's the deal: > 1. We expect to store ~1TB per year of data for 10 years. Mostly this is a > warehouse situation - not a lot of updates, or deletes, but a lot of > inserts. > 2. We need to replicate a subset of our postgres data to an international > science community, and in turn take in some data from them (we plan to do > this via Slony-1 unless there is a compelling reason not to). > 3. We need to make a copy of our database available to the general internet > community. 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). > 5. We need to make regular full tape backups (~weekly) and occasionally > scheduled maintenance (think quarterly maintenance). > > We do not have an endless budget, sadly, so I could use some help as to how > to go about this. Having gone from a job where my database software > actually had paid tech support to one that doesn't (PostGres), I am pretty > concerned about what could go wrong. > > Assume our Primary server (A) is good enough to serve our in-house users, > and our Warm Standby (B) is a physical duplicate of A. My plan is to copy > WAL files to B. Make a tape backup from B weekly, keeping it out of > recovery mode for ~6 hours, or alternatively make a snapshot of B's data > files at a given time and tape off the snapshot. This takes care of A & B, > and the backups, but what about the other requirements? > Using ZFS snapshots as a base backup for setting up PITR works extremely well, though we've found trying to push incremental snapshots to tape not as efficient as one would hope, but you'll probably have less traffic than we do, so it could work; still, you can probably do full snapshots to tape once a week without causing too much trouble. > How do we get data to our web community w/out fear of hacking to the > primary? And how do we do that economically? There is one plan in place to > use a set of snapshot disks from A's data files to act as the web > database's files. Can we do that? Is that exceptionally stupid? 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. > Using the ZFS method, you can make new snapshot clones in a matter of minutes, and you can script it to make it a pretty brain dead operation. > Lastly, we plan to pinhole our firewall for trusted Slony-1 science > "customers". People that we already know who have specific IP addresses. We > have yet to figure out the drag to our Primary (A) due to Slony-1. Any > experience with that out there? > It shouldn't be too cumbersome... in the single digit percentages I'd think, but it will be workload/hardware dependent. > My prior work experience involves a 1TB Sybase database, its warm-standby > and regular backups & quarterly maintenance. I am new to PostGres and the > idea of no tech support phone calls when things break is a scary one! I am > trying to create a belt-and-suspenders redundant solution so that if > something breaks, I have time to figure out what went wrong and fix it > before the users even know there's a problem. > If you really want paid technical support, there are many options available, of which OmniTI is one. HTH. -- Robert Treat Database Architect http://www.omniti.com/