[OPEN-ILS-GENERAL] EG data backup retention policy

[Jason Stephenson]

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