backup and restore with WAL files

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

Software Architect

Web Services at Public Affairs

217-333-0382

University of Illinois at Urbana-Champaign logo

 

 


[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux