backup and restore with WAL files

PostgreSQL 9.3.5
RehHat

I am about to start using WAL archiving and pg_basebackup. I have tested the backing up of data. I want to make sure I understand the proper way to restore. As a note I use the database for web application hosting. The database runs on its own server.

Storing data:

1) I am going to do a nightly pg_basebackup.

2) I am also shipping all WAL archives into their own folder.

3) I only keep the last five pg_basebackup .

4) I delete all WAL archive files that have a sequence ID less than the last pg_basebackup.

Recovery procedure if the worst happens:

1) Stop the database.

2) Rename the base data directory.

3) Uncompress the last pg_basebackup into the same directory as that of where my production data would normally be.

4) If the postgresql.conf file changes since the last pg_basebackup I will then copy it over.

5) I replace pg_hba.conf with pg_hba.conf.local. Only local users can access the database when started.

6) I then start the database with the below recovery.conf file:

standby_mode = 'off'

primary_conninfo = 'user=xyz port=5432 sslmode=prefer sslcompression=1'

restore_command = 'cp /xxx/yyy/wals/%f %p'

7) I then make sure the database is working properly locally.

8) I then stop the database.

9) Replace pg_hba.conf file with pg_hba.conf.applications. This file allows my application servers the ability to connect to the database.

10) Start the fully restored database.

I might need to add to the recovery.conf file a command to restore to a particular date/time in order to not apply some dreadful situation that occurred prior to the restore.

Did I miss anything? I just wanted to make sure that all of the WAL files get applied appropriately. So if I have stored from the latest pg_basebackup then all of the WAL files that have the same ID or greater will be applied. Not the WAL files that were older than the last pg_basebackup I am using. Is that correct? So if I actually restored from a pg_basebackup that was a prior day then the WAL files that would be used would not only be those related to that pg_basebackup but all of the following days as well and the current ones. Is that correct?

Thanks,

Lance Campbell<http://illinois.edu/person/lance>
Software Architect
Web Services at Public Affairs
217-333-0382
[University of Illinois at Urbana-Champaign logo]<http://illinois.edu/>