Steps for Point in time recovery from Base Backup and WAL

1.    Stop the server, if it's running.

2.    If you have the space to do so, copy the whole cluster data directory and any tablespaces to a temporary location in case you need them later. Note that this precaution will require that you have enough free space on your system to hold two copies of your existing database. If you do not have enough space, you should at least save the contents of the cluster's pg_xlog subdirectory, as it might contain logs which were not archived before the system went down.

3.    Remove all existing files and subdirectories under the cluster data directory and under the root directories of any tablespaces you are using.

4.    Restore the database files from your file system backup. Be sure that they are restored with the right ownership (the database system user, not root!) and with the right permissions. If you are using tablespaces, you should verify that the symbolic links in pg_tblspc/ were correctly restored.

5.    Remove any files present in pg_xlog/; these came from the file system backup and are therefore probably obsolete rather than current. If you didn't archive pg_xlog/ at all, then recreate it with proper permissions, being careful to ensure that you re-establish it as a symbolic link if you had it set up that way before.

6.    If you have unarchived WAL segment files that you saved in step 2, copy them into pg_xlog/. (It is best to copy them, not move them, so you still have the unmodified files if a problem occurs and you have to start over.)

7.    Create a recovery command file recovery.conf in the cluster data directory. You might also want to temporarily modify pg_hba.conf to prevent ordinary users from connecting until you are sure the recovery was successful.

8.    Start the server. The server will go into recovery mode and proceed to read through the archived WAL files it needs. Should the recovery be terminated because of an external error, the server can simply be restarted and it will continue recovery. Upon completion of the recovery process, the server will rename recovery.conf to recovery.done (to prevent accidentally re-entering recovery mode later) and then commence normal database operations.

9.    Inspect the contents of the database to ensure you have recovered to the desired state. If not, return to step 1. If all is well, allow your users to connect by restoringpg_hba.conf to normal.



I learn something tougher on

Harry Bell's picture

I learn something tougher on distinct blogs every day. This is a unique post from your side and looking it thoroughly it seems that you done a great job by writing this beautiful post. I would love to visit again on your website so please share more articles like these so that can help us in our lives. Go for this cheap essay writing service to having a wonderful assistant in writing and even you can get the guidelines too.