Article Preview

COLUMN

A Backup and Recovery Approach

PostgreSQL Continuous Archiving and Point-in-Time Recovery (PITR)

Issue: 16.3 (May/June 2018)
Author: Craig Boyd
Author Bio: Craig Boyd is currently a data architect for a major fashion retail brand. But in his 23 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): 9,330
Starting Page Number: 77
Article Number: 16307
Related Link(s): None

Excerpt of article text...

In this column we are going to continue our discussion of Point-In-Time-Recovery (PITR). This is all part of the preparatory work that must be done before we can actually setup and use Barman, one of PostgreSQL's managed backup solutions. For the sake of time and space I am going to jump right in where we left off. Please do read the last couple of columns to refresh your memory.

As you may recall, we had finished setting up continuous archiving for our PostgreSQL instance. Before we move on to the testing, a brief word about performance. If this is going to be a busy production server, then you will want to make sure that the WAL logs and the data files are on physically different hard disks. If you don't do this, you could end up with some serious performance issues. Databases are by their very nature IO intensive and, if you don't take the time to design things properly, then you will likely spend a lot of time troubleshooting issues you could have avoided. The other piece of the puzzle is to make sure that your log files are getting backed up throughout the day. Since I am using a test database that doesn't have any real activity, I wrote a little Xojo program to insert a large chunk of text into a file in a loop. That way I can run it in a loop and see the log file numbers change.

Before we attempt a recovery, we need to run through the checklist to make sure all of our bases are covered. This might be a good checklist for you to use on all your PostgreSQL databases.

• File system or OS level backup; For the purpose of this article I am just making a copy of the data directory as well as pg_xlog (this will be in the PostgreSQL data directory). If you are on a Linux or Mac be sure to make note of the permissions, owner, and group. From the psql prompt, you can do show data_directory; and it will return the data directory location. Your default and global tablespaces should be located in there as well. You can use the two following queries to verify that:

  select setting||'/base' from pg_settings where name='data_directory';

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