Article Preview

COLUMN

Backup Best Practices and PostgreSQL

Understanding best backup practices is critical

Issue: 15.3 (May/June 2017)
Author: Craig Boyd
Author Bio: Craig Boyd is currently a data architect for a major fashion retail brand. But in his 20 years of IT experience, he has been everything from a PC Technician, iSeries System Administrator, iSeries Programmer, Sr. Technical Lead, Data Modeler, Data Architect, Oracle DBA, BI Consultant and Solution Architect. He lives in the great state of Texas with his wife and two kids.
Article Description: No description available.
Article Length (in bytes): 11,901
Starting Page Number: 92
Article Number: 15307
Related Web Link(s):

http://www.luisrocha.net/2012/12/chinook-database-is-now-available-on.html

Excerpt of article text...

So the last bit of columns have all revolved around topics in data management. For the next bit I am going to switch over to more practical or hands-on columns. This month we are going to start discussing best backup practices and begin the discussion of backups for PostgreSQL databases.

Before we go any further I need to throw out a few caveats. First, always trust the PostgreSQL documentation over any blog, book, article, or column including my own. Second always test, test, and re-test to make sure everything works the way you expect it to. Backups are too important.

When dealing with backups on any DBMS, there are several best practices you should be aware of and/or follow on a regular basis.

Perform both database and OS level backups

This may sound a tad bit excessive, but it is none-the-less important. OS backups will pick up things that a database backup won't catch. For example, suppose you have implemented some feature that depends on a certain patch being applied to the database. If you are backing up only the database and a year from now you have to recover because of a hard disk crash, you will likely waste several hours troubleshooting database errors that result from that patch no longer being there. Or worse, you end up with corrupted data because the patch is missing and it goes on for a long period of time after the recovery without being noticed. It is true that a rigorous change management process will allow you to capture this bit of information and thus be aware of it. But I have been too many places where the change management process was not rigorous enough and things slipped through. So take the time to backup the OS especially after any OS or database patches have been applied. Because OS changes are not happening as fast as database changes (structures or data) they don't have to happen hourly or daily, but they should be happening frequently enough so that you don't run into the situation where the patch you need is only on one backup set and then you discover that for one reason or another it is completely unusable.

Test your backups and recovery strategies as often as is reasonably possible.

...End of Excerpt. Please purchase the magazine to read the full article.