How to recover database using pg basebackup & how to perform pitr using pg basebackup Prerequisite

How to recover database using online pg_basebackup:

Step 2:
Verify archive log folder whether all wal files are archived.

Step 3:
Perform a log switch to archive the current log

As you can see the current log is archived as well.

Step 4:
Take a pg_basebackup of the entire cluster. Using the below command. This is generate 2 tar files
One is of the entire data and other one is wal files(pg_wal.tar)

Step 5:
Stop the cluster and delete the data folder( we are going to mimic a crash here were we lost our data folder).

I removed the data folder using rm –rf and you can see that there is no data folder in the location.

Step 6: Now we are going to restore the data folder using the backup which we took.
First create data folder in the same location where we removed.

Let us start with the restore of data folder and wal files using the pg_basebackup which we took.

This will create all the data inside the data folder. Log in to data folder and check whether all the folder and files are there.

Step 7: Now we need to ensure that the database is consistent and tell our database server to copy files from our archived location to WAL file location. For this we need to edit postgresql.conf file.

Step 8: Start the cluster. (Remember all this while the cluster was down).

Just change the permission on data directory to 700

And start the cluster again

The cluster started successfully. Let us check whether the table test1 with 5 records exist or not.

So we have successfully deleted and restored our database using pg_basebackup.

Step 1: We will use the same table to perform PITR. I will be adding few more to the existing table to generate archives.

Switch the current archive log.

We can see that there are many new archives which are generated in the archive log folder.

Step 2:
Take a fresh pg_basebackup.

Step 3:
Now insert few more rows after that backup in the test1 table. Before my row count was 10 now it is 15 records.

My task is restore the database when the table was with 10 records.

Step 4:
Now I will mimic a crash by deleting my data directory.

I have removed my data directory.
Step 5:
Restore the database from the backup which we took @ step 2.
Make sure we create the data and pg_wal folder before we start the restore.

Start the restore operation


ONLY DATA DIRECTORY SHOULD BE RESTORED. DON’T RESTORE PG_WAL TAR.
Recovery file will guide the Point in time to backup.
Step 6:
I have to recover my database till the point of 10 records. So I will check the archivelog which was generated by that time.

So I am going to recover my databse till 16:24 before I did my pg_basebackup. There are few additional archive files @ 16:30 and 16:32 which hold the new 5 records. I don’t want that.
Step 7:
Create an empty recovery.signal file inside /data folder.

And add the following entries in the postgresql.conf file.

And save the file using wq!. These two parameters are very important for PITR . If we don’t add these two parameters all the archive files will be applied and instead of 10 rows we will get 15 rows.

Step 8:
Now start the cluster and it will recover the database till the specified time. Ensure to
Change the permission on data folder to 700

Step 9:

Step 10:
Check how many rows are there in table test1.