Thread: 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

Software Architect

Web Services at Public Affairs

217-333-0382

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

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

<image001.png@01CFD3E4.015A67E0>

Jason,

I did some more reading.  It appears that when doing pg_basebackup you need to use –X option if your intent is to use the backup as a standalone restorable backup.  That way the logs that are written during the backup process are stored with it. 

If your intention is to include the wal archives during a restore process then the use of –X would not be needed since you will already have them archived.

I will still test this out.

Thanks again for your wisdom in looking over my plan.

On September 19, 2014 at 10:48:30 AM, Campbell, Lance (lance@illinois.edu) wrote:

Jason,

Thanks for the reply.

 

Since I am saving the wal files will it cause a problem to use -x when doing a pg base backup?  Will that cause duplicate archive logs?  Or will PostgreSQL work around that?  Or should I leave the -x off since I will always have the logs?

 

Thanks so much!

 

Lance

 

Sent from my iPhone

On Sep 19, 2014, at 10:54 AM, Jason Mathis <jmathis@redzonesoftware.com> wrote:

There can be some issues here: 

 

Re: Storing the data.

 

1) great! i do the same. 

2) Keep in mind that storing the wal files on the same server is not best practice. What if the whole server blows up? Do you have a nas/shared drive you can use? 

3) This is ok. But……

4) OK, now you can ONLY go back to the last base backup and forward from there. The other 4 base backups are unusable. There are a few exceptions like if you use the 

“-x” option. read here: http://www.postgresql.org/docs/9.2/static/app-pgbasebackup.html

 

If you only want to go back to the last backup then its fine. But if you need to go back to a few days ago at a certain time you cannot, again the older 4 base backups are unusable without the wal files. To recover you need the "base backup + all the wal files”

 

one good read, there are many more: http://www.postgresql.org/docs/9.1/static/continuous-archiving.html

 

Your recovery procedure sounds good.  Make sure all the file permission are correct after the restore. 

 

Let me know if you have any questions, hopefully I explained myself well

 

-jason 

 

On September 19, 2014 at 8:12:45 AM, Campbell, Lance (lance@illinois.edu) wrote:

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

<image001.png@01CFD3E4.015A67E0>

 

 

 

This transmission contains confidential and privileged information intended solely for the party identified above. If you receive this message in error, you must not use it or convey it to others. Please destroy it immediately and contact the sender at (303) 386-3955 or by return e-mail to the sender.

This transmission contains confidential and privileged information intended solely for the party identified above. If you receive this message in error, you must not use it or convey it to others. Please destroy it immediately and contact the sender at (303) 386-3955 or by return e-mail to the sender.