PostgreSQL – Restore Database

Restoring a database, is the action of copying the backed up data and restoring it at its original or new place. We perform restore action on a database so as to return the data to its original state before being lost, stolen or corrupted. There are several causes due to which one needs to restore a database like – due as human errors sometimes data gets corrupted; some malicious attacks on the database might infect it; due to power outages; natural calamities like floods, storms etc. Thus, in these cases, a data restore makes a usable copy of data and ensures that restored backup data is inline and consistent with the state of the data before the damage occurred.

Approach:

In PostgreSQL also, we can back up and restore the data. To restore a database, we have 2 main approaches:

1. To restore via PgAdmin4 GUI
2. To restore via command line

Here we are going to see both the approaches in detail.

1. Using PgAdmin GUI 

In the first approach, we are going to restore our database from the pgAdmin4. This is a very simple and straightforward process. For this we are going to follow the steps listed as below –

1. Start the pgAdmin database server and login to the pgAdmin4 control panel via valid credentials.

2. Expand the Servers section and navigate till Databases. Now, here you will find all your databases the server is currently hosting (if any).

3. Now we need to create a new database which will serve as a placeholder for our database which is to be restored. For this, right click on the Databases section, select Create -> Database.

4. In the General tab specify the name of your database which is to be restored. Owner must be same as the current postgres Admin username. You  can also add optional comments.

5. Now to go Definition tab, select appropriate character encoding, tablespaces etc. Fill all the required details of any in similar ways on the alongside tabs like Security, Parameters tabs as well.

6. After filling all necessary details, select Save option. Database will now be created successfully and will be listed alongside the existing databases in the Databases section.

7. Right-click on the newly created database by you and select Restore option from the list.

8. After clicking Restore option, a new pop-up dialogue box menu of Restore will show up on the screen as below.

9. Now specify the Format of the file. Right-click on the horizontal kebab menu to browse for the location of file, select the role name as postgres (root owner). Hit Restore button.

10. A new restore job is now created on the pgAdmin4 server and usually appears in the bottom right corner of the window. 

11. After successful restore operation is a success prompt is displayed at the same position as said above with the success message. In case of a failure of the restore option, a failure message is also given when we click on the More Details (i) button.

12. Success !! Our PostgreSQL database is now completely restored. It is now in fully up and running state.

2. Restoration via command line

To restore a database via the command line, we are going to follow the following procedure –

1. Firstly, we need to login to the PostgreSQL terminal via command line. To do so, type in the following command :-

psql -U <username>

2. We can now see that we are successfully logged into the psql client terminal and have got the postgres command line input prompt.

3. We will now follow the same process as we followed in the above part I, the only difference being here is that, we are going to do this time via the command line terminal of PostgreSQL.

4.  Let’s now create a placeholder database for our purpose which will be used to restore the backup. To do so, run the below script.

CREATE DATABASE BackupDB ENCODING='UTF-8' OWNER='postgres';

5. Database is now created. Let us restore it now. To restore the database, we are going to use the pg_restore command supplied with some arguments. It is important to note here that, we need to exit from the psql terminal in order to run pg_restore command. To exit from psql terminal, type “\q” to exit.

6. Key in the pg_restore command with the following arguments –

pg_restore -U postgres -d backupdb -v "D:\Backup.sql"

The detailed explanation of the arguments for PostgreSQL can be found on the PostgreSQL’s official website on the documentation section of pg_restore.

7. After successful restoration of  database, we will se that our schema are restored alongwith the tables and their data.

Thus, in this article, we have successfully explored the two ways of successfully restoring the database.