Re: backup and restore with WAL files

[Jason Mathis <jmathis@xxxxxxxxxxxxxxxxxxx>]

Good question, I am not entirely sure but from reading the docs it sounds to be ok. I would test it. Although, personally I would just keep all the wal files and not do the “-x” if you have the space. I think I keep +1 day more logs than base backups to make sure I have them all in case of restore (paranoid). Then you can go back to ANY point in time from now until the last base backup you have, the most flexibility. If you are tight on space or don’t want point-in-time recovery then try the “-x” or just use pg_dump. The continuous archiving takes some time to setup but its solid, works very well and can save your ass:) They are also several third party options to choose from to simplify the process. I have not used any but here are a few:

https://github.com/wal-e/wal-e

http://www.pgbarman.org/

https://public.commandprompt.com/projects/pitrtools/wiki

https://github.com/omniti-labs/omnipitr

-jason

On September 19, 2014 at 10:48:30 AM, Campbell, Lance (lance@xxxxxxxxxxxx) 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@xxxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxx) 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.