Hi, all. At C/W MARS we keep a week's worth of daily pgdumps on a dedicated backup server at the colocation facility. These are also sent to "the cloud," but I'm not sure what the retention policy is on those.
We also have backups of other information on this server, including report output, the data, updates, and web subdirectories of /openils/var/ from our nfs server. These are backed up hourly, and there's a daily job to clean up "old" ones, where "old" varies by purpose. We have streaming replication to a second db server, mostly for reporting, and we also ship WAL archives to this second server. The script that does the daily pgdump also cleans out any old WAL archives. We do not backup WAL files offsite. They change so quickly, and are basically useless once a new dump is made. The pgdumps are mostly used to refresh data on a test/development database server. I update it when I feel the need for new data. On 12/19/2017 05:03 PM, Martha Driscoll wrote: > > I constantly think I don't have enough backups. I can't imagine what > the cost would be in time, lost productivity, and diminished service if > a disaster happened and there was no viable backup. Frankly, you have plenty. You'll never need a dump more than a day old and the WAL archives in the event of a crash. Also, if the crash was caused by and/or causes database corruption your last WAL archive or two may be useless. If your data center catches fire, you've not got bigger problems, in addition to restoring your database. :) We have more trouble with the replication server than with the main one. I've had to restart replication a couple of times. It's best if the replication db server has the same amount of space for the database files as the main server. Ours has 1/2 the space, and that can be a problem when something big goes on, like a parallel ingest. Before I started here there was some point in time recovery backup going on. I stopped that when I set up a new server. PITR gets mixed reviews in the PostgreSQL community. I also tried restoring one of these dumps and it didn't work, though I think that was more a by-product of how a previous db upgrade had been done. Lost of links to non-existent files in the archive. Cheers, Jason
