It’s an absolute breeze to manage databases via user-friendly panels.

pgAdmin is one such tool that helps to manage PostgreSQL databases.

Using pgAdmin to restore a database from the SQL file is a common task in database management.

At Bobcares, we often get requests to fix database restore errors, as a part of Server Management Services.

Today, let’s see how to restore a database using pgAdmin and also discuss how our Support Engineers fix the related errors.

How to restore a database using pgAdmin?

pgAdmin is a handy and user-friendly tool with a graphical interface. So, this tool makes the restoring task easy for all kind of users.

Today let’s discuss how we use pgAdmin to restore a database from the SQL file.

1. Firstly, we create a full backup of the database in the form of an SQL file.
2. Next, in the pgAdmin tool, we create a new database for restoring the SQL file.
3. Now, the list will contain the newly created database. So, we right-click the database and select the Restore option.
4. Here, a new window opens up displaying two tabs, General and Restore options.
5. After selecting appropriate options under each tab, click on the Restore button.
6. Now, a dialog box pops up denoting the status of the restore process.
7. The More details tab in the dialog box launches the Process Watcher. It logs all the activities of the restoring process.
8. Finally, the window shows a successful completion message.

For instance, the restore option in pgAdmin 4 shows up as:

Errors while restoring the database using pgAdmin

Although the restore process looks quite straight-forward, often users face errors with Postgresql databases.

Now, let’s discuss a few errors while restoring the database in pgAdmin.

Usually, missing privileges while restoring, redundant files while uninstalling, etc. results in errors. Let’s see how our Support Engineers fix them.

1. Missing role reference in the backup file

Mostly, restoring the database without roles often ends up in error during a database query.

Usually, databases have predefined roles which are a collection of permission and privileges for users.

So, when our Support engineers restore a database, we always dump their roles first. To accomplish this, while taking the backup, we use the command,

pd_dumpall --globals-only > globals.dump

Then, we dump the roles and restore it using the command,

pg_restore -f globals.dump

2. Redundant files in pgAdmin directory

Sometimes, redundant files create problems with pgAdmin.

Recently, one of our customers tried to restore the Postgresql database via pgAdmin. It ended up in an error. To get rid of the error, he did a reinstall of pgAdmin. But, even after reinstalling pgAdmin, the previous restore process error showed up.

This is because of pgadmin.db file retained in the pgAdmin directory even after failed restoration.

In this case, our Support Engineers checked the files in the pgAdmin folder. There was a redundant pgadmin.db file in the pgAdmin directory. So, we removed this file to fix the error. This made pgAdmin working again.

[Still, have difficulty in restoring a database using pgAdmin? – We will do it for you.]

Conclusion

So far, we saw how to use pgAdmin to restore a database from the SQL file. We also saw how our Support Engineers fixed the restoration errors due to improper backup, redundant files and so on.