Special

Introducing the “Welcome to Xojo” Bundle!

New to Xojo and looking for guidance? We've put together a terrific bundle to welcome you! Xojo Bundle

This bundle includes six back issues of the magazine -- all of year 15 in printed book and digital formats -- plus a one-year subscription so you'll be learning all about Xojo for the next year. It's the perfect way to get started programming with Xojo. And you save as much as $35 over the non-bundle price!

This offer is only available for a limited time as supplies are limited, so hurry today and order this special bundle before the offer goes away!

Article Preview


Buy Now

Issue 16.3 ('XDC 2018')
Instant purchase and download via GumRoad!

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.