|
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, Software Architect Web Services at Public Affairs 217-333-0382
|
