3) This is ok. But=E2=80=A6=E2=80=A6

4) OK, no= w 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 u= se the

=E2=80=9C= -x=E2=80=9D 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 u= nusable without the wal files. To recover you need the "base backup + all t= he wal files=E2=80=9D

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

Your re= covery procedure sounds good. Make sure all the file permission are c= orrect after the restore.

Let me know if you have any questions, hopefully I explained m= yself well

<= br>

-jason&n= bsp;

On September 19, 2014 at 8:12:45 AM, Camp= bell, 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 =3D 'off'

primary_conninfo =3D 'user=3Dxyz port=3D5432 sslmode=3Dprefer sslcompression=3D1'

restore_command =3D '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 C= ampbell

Software Architect

Web Services at Public Affairs

217-333-0382

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 se= nder at (303) 386-3955 or by return e-mail to the sender.

--541c5193_42c296bd_fd65-- --541c5193_6b94764_fd65 Content-Type: application/octet-stream Content-Transfer-Encoding: base64 Content-ID: <0F752EA6-ED8A-462D-B35F-ACB84FA8E721> Content-Disposition: inline; filename="=?utf-8?Q?image001.png=4001CFD3E4.015A67E0?=" iVBORw0KGgoAAAANSUhEUgAAAJYAAAAZCAYAAADT59fvAAAFkElEQVRoge2aQWjbVhjH/xop85hD lMuQ2WEKdcG7rNKtYQmxTovZITIkdIVCLXoqZLi+ubQQB1aWm2Ma6KmVC4WsJBCH0dmnWiEt6Wl+ vQXqEfUUMQZVmQZqN3g7ODJ2otSSY3uk0w9MYiN9+kv663t63/eYhftP6GJRQy9YSMWRUySmJ8EC TjVDAPBV2MTlyB7e/HOmqyAjQ3/j4f5YT4UFnG6GAGDkzDv8/Mfn2H79WVdBJkd/x8iZdz0VFnC6 +ei/FhDwYTLk9mNc4JGeG8eLutH8bUrg27aR0mpfhQWcblyNtaBI4DkWpmWD51gADbMBgGnZKD3d HZjAgNOJq7G2iA5+WsBWbQ+vIqMYCYeaxpLSKkhLJgsIcMP1HSunVjF2MY9ihWBK4PHGsgEAGtED UwV4wtfLu26Y/dIR8IHhy1ivAmMFeCQoNwT0hcBYAX3BdVbYazIrFUpe7gMA2OEQ0rPjiAv8e3uK umHSxaIGff91c7/8fAI8x/rqRRbLNfqgQprfZyZiuD437itGt1pK27u0sL4DoFEHdOujKksl6sTl I6NQs7InbaZl0+W1HTyokGZJiNQN8ByL9OwFpBKi53PUiE4X1Wrzu3Augvz8tGcdytIG9H0TbDgE jehIJcTBGCs/P81IaZVqRMfeo4wnc/Acy6hZGeLVu5TUDbx+fANsOOS7wZ1KiAwb/oQmb63i+ty4 b1OdRIs8GWP4CEvFq3ehER1xcYwefqDUrMwkb61S538vekzLplJaBR9hUbt3ranFtGyaWalAWSph 68Ur6jVeXOAZKBKV0iquz417NhUAJG+ugh0OoXbvGgMApG5Q8erdwQ2FTuXeb8ZhwyHnb9erJtjh RoyRTz/uNkTXWoQoxzj7Jm+ugtQNenib82c5nD/LedYhpVWYlg01m2zTwoZDjJqVGXkyhmK5hpxa PXKsTvi5RrphUo3ouPKN2PxNiHKMmpX9GWvk4MIG+EfNJmFaNpQfN2Batu8b7lAs1yipG0jPXjjW 4Pn5BACgsP6828P4whnuHVIJkfFlLCHq/akKaEeejDFqVgapG07G6cpcm88a7TThXOTYbXiOZYQo B9OyoRG9axN3gudYJi7w0IgOKa1S3TCbxwqMNUBSCZFJJUSQugFlaaOrGOafjS5Ip3vBDmh02bh9 CUKUg0Z0jF3MN4ffY40lT8SQUyTohokvDmYdbDiEnCINRPCHipqVmVRCRGl7F8pSqets0qm1dng1 Sr9gwyGmdu8as5CKAwAWixoyKxXqaix5IoaZyS8xJfDgObY5nQUay4/3HmWC7HUC8vPTEKJcVy/Y zkTEKVEcxxbRG9sPKHPlFIk5mKFieW3HPWNNiWNtZgIaDWjnE/QMTwYbDjHVggIhymGxqGHTxzKk ma9jAIDW2pwbTk3LmZX2g9L2bttDIUS5ZuZyrWNl7pR7LuLNX297Gk8jOi2s72Djh0snunBSWqX5 7xN9vQFAY2reWmphwyFGvZGkzjKkmYmYpziphMgU1p9TjegolmvUrRCaU6vUtGxs3L7UwzM4SmF9 B+xwqK02x3OjAAbU0jEtm2q1PQBwreO8bz8nOy6v7VCN6M1P5k4ZU+d5T3Gcqv/m0120xlhe26Gk bngyVauW1tlPx2MfnK9z/q0IUY6pFhTfw5WT7ZSlkmMi6mjMqVVaWH8ONSt37G604gytL37zvixq SuDbanOmZdPFYhXyZAzMwv0ntPjTY1yOHD1xPzzcH0Pqu29d2xZSWm27EVemhY4tB1I3aKfMqd5I diy4traT3Diu1dJJi5csd3i/41olxXKNmpbtuytQLNfo5rNdkJcGTMuGEOUgnIsgPXvBVyH6cNsL AKoFxdPDllmpgLzch26Y4DnWaZmBUX/59UjQbvFimID/B/8CO2CIAn28iA0AAAAASUVORK5CYII= --541c5193_6b94764_fd65--