Introduction to PostgreSQL Restore Database
The Database Restore process in the PostgreSQL is used to copy the data from backup. Which means we use the database backup file and convert it into the database. By using the Data Restore we create a copy of the data for regenerating corrupted data or missed data also we make sure that the loss data is completely recovered and consistent at a particular time, generally, the time before the data corrupted or damage occurred. There are several cases where we need to perform the Data Restore, first human error, like by mistake user has deleted the data. second, the data has been hacked, stolen, or exposed to the outside world or data corrupted due to power cut or some natural calamities, some kind of theft or hardware or software failure.
How to Restore the Database in PostgreSQL?
In order to perform the database restore in the PostgreSQL, we have to terminate all active connections for the database to be restoring. There are two ways to perform data restore in the PostgreSQL as follows:
Method #1
1. Restore SQL script created by ‘pg_dump’ and ‘pg_dumpall’ using the psql utility.
Consider the following examples in order to understand the psql utility commands.
In order to perform a full backup, we have to use the following command and ignore all of the errors that occurred during the restore by skipping the option –set ON_ERROR_STOP as defined below.
Code:
psql
-U username
-f backupSqlFile.sql
We can use the following command with the option –set ON_ERROR_STOP=on, If while performing database restores in the PostgreSQL and some error occurs and we want to stop the process immediately.
Code:
psql
-U username
--set ON_ERROR_STOP=on
-f backupSqlFile
We can use the following command to restore the particular database in the PostgreSQL as defined below.
Code:
psql
-U username
-d databaseName
-f objectDB.sql
Method #2
2. Restore tar file and directory format
- We can restore the backed up files generated by pg_dump or pg_dumpall tools with the help of the pg_restore program in the PostgreSQL.
- We can use multiple threads for restoring the database by using the pg_restore program, to do the same we have specified the option -j which is used to define a number of threads getting used for the restoration process.
- We can perform transition on different database versions by using a pg_restore program which means we can restore an older version database backup to a newer version.
- We can restore a particular database by using pg_restore program if we are having a full database backup file.
Consider the following examples in order to understand the pg_restore program in the PostgreSQL
We will create a database named eduCBADB by using the following CREATE DATABASE statement. Consider we have backup eduCBADB.tar file create at path c:\PostgreSQLEduCBA\eduCBADB.tar
Code:
CREATE DATABASE eduCBADB;
We can restore the eduCBADB database in the tar file format by using the following command:
Code:
pg_restore
--dbname=eduCBADB
--verbose c:\PostgreSQLEduCBA\eduCBADB.tar
If we want to restore the database in the PostgreSQL which is similar to the backed up a database then we can use the following command:
Code:
pg_restore
--dbname=eduCBADB
--create
--verbose c:\PostgreSQLEduCBA\eduCBADB.tar
PostgreSQL Restore Databases using pgAdmin tool
We can use the pgAdmin restore tool for performing restore databases in the PostgreSQL.
Consider the following examples which show how can we restore the NewEduCBADB database from the eduCBADB.tar file
- DROP the existing NewEduCBADBdatabase: DROP DATABASE NewEduCBADB;
- Create a new empty eduCBADB database: CREATE DATABASE NewEduCBADB;
Consider the following screenshot which is used to create a database in the PostgreSQL by using pgAdmin 4:
Consider the following screenshot which shows the list of the database in the PostgreSQL by using pgAdmin 4:
3. Now we can perform the following actions on UI
- Select the eduCBADB database
- Right mouse click
- Select the Restore…
And then
- Select required options like as user, backed up file and restore options
- Click on the Restore button to start restoring the database.
We will have to go through the following dialogs step by step:
Step1: Consider the following screenshot which shows the context menu items available on the database in pgAdmin 4:
Step 2: Consider the following screenshot which shows the Restore dialog when we click on “Restore…” context menu item available on the database in pgAdmin 4:
Step 3: Consider the following screenshot which shows on successful creation of the job to restore the database in pgAdmin 4:
Recommended Articles
This is a guide to PostgreSQL Restore Database. Here we discuss an introduction to PostgreSQL Restore Database, syntax and parameters, examples to implement. You can also go through our other related articles to learn more –
