I’ve been asked about my opinion on backup strategy and best practices, and it so happens that I have some kind of an opinion on the matter.
I tend to think best practice here begins with defining properly the backup plan you want to implement. It’s quite a complex matter, so be sure to ask yourself about your needs: what do you want to be protected from?
The two main things to want to protect from are hardware loss (crash
disaster, plane in the data center, fire, water flood, etc) and human error
UPDATE without a where clause). Replication is an answer to the former,
archiving and dumps to the latter. You generally need both.
Often enough “backups” include
shipping and nightly or
base backups, with some retention and some scripts or procedures
ready to setup
Point In Time Recovery and recover some data without
interfering with the WAL archiving and shipping. Of course with PostgreSQL
9.0 and 9.1, the
WAL Shipping can be implemented with
and you can even have a
Hot Standby. But for backups you still want
Mostly I still implement
pg_dump -Fc nightly backups with a custom retention
(for example, 1 backup a month kept 2 years, 1 backup a week kept 6 or 12
months, 1 backup a night kept 1 to 2 weeks), when the database size allow
pg_dump run to remain constrained in the
maintenance window, if any.
Don’t forget that while
pg_dump runs, you can’t roll out
DDL changes to the
production system any more, so you want to be careful about this
maintenance window thing. When you have one.
Physical backups are not locking rollouts away, but they often suck a good deal of the IO bandwidth so you need to pick up a right timing to do them. That’s how you can get to once a week base backup and WAL archiving.
If you can’t
pg_dump production, maybe you can have
automated restore jobs
physical backups that you then
pg_dump -Fc, so that you still have
that. That can come up handy, really: you can’t test your
major upgrade out
Also, obviously, never consider your backup strategy implemented until you have either automated restores in place or a regular schedule to exercise them ( staging instances, devel instances).
Then as far as the practical tools go, I tend to think that
worth its installation complexity, and for WAL archiving and base backup I
Skytools, that’s a very handy tool. When using
9.1, consider using
walmgr3 so that it’s behaving nice