Wondering how to restore the Postgres database from files? Here’s how we do it.

Basically, this restoration is possible using the pgAdmin tool and certain commands like psql, pg_restore, etc. But, it often ends up with some tricky errors.

At Bobcares, we receive requests to restore the Postgres database from files as part of our Server Management System.

So, today let’s discuss the several restoration methods in detail and see how our Support Engineers fix the errors related to it.

How we restore the Postgres database from files?

Generally, we take the pg_dump command to take the PostgreSQL database backup. It will dump all the contents of the database into a single file.

To restore the Postgres database from files, we use different methods.

Let’s see how each of these methods work.

1. Restore a database with psql command

Recently, one of our customers approached us to restore the Postgres database from files. His database backup was a plaintext file containing SQL scripts.

For restoring, our Support Engineers run the psql command.

psql -U user_db user_name < dump_db.sql

Here, user_db and user_name are the database user and database name respectively. And, dump_db.sql is the name of the backup file.

This command effectively restored the Postgres database from files.

2. Restore a database with pg_restore command

Similarly, another method to restore the Postgres database is using the pg_restore command.

If the backup file created by pg_dump is of custom, directory or archive format, we use the command.

pg_restore -d user_db /path/to/your/file/dump_db.tar -c -U user_name

Here, -c prompts to drop the database before recreating it.

By default, pg_restore has various options similar to this.

3. Using pgAdmin tool

We can also restore the Postgres database using the pgAdmin tool.

1. Firstly, choose the required database in the pgAdmin.

2. Next, we right-click on it and choose the Restore option from the menu list.

3. Then, we choose appropriate options such as the format, filename, restore options, and click on the Restore button.

4. After that, pgAdmin displays the log messages in process watcher for easy troubleshooting.

5. On complete restoration, we receive a “Successfully Completed” message.

Common errors during the restoration

Errors are common while restoring databases. Now, let’s get into some common errors that occur while restoring the Postgres database from files.

1. Error in commands

Recently, a customer came up with a database restoration error in Postgres. He used the pg_dump to create the database backup to a normal text file.

But, when he tried to restore the database using the pg_restore command, it showed up errors.

Our Support Engineers checked and found an error with the command usage. He used the custom format, but the backup was on a normal text file.

So, for restoring from text files, we use psql command instead of pg_restore.

psql -e -U username -d databaseName -f "path_of_the_file"

Executing the command in the above format resolved the error.

2. Version conflicts

Another common error during Postgres database restoration is the Postgres version conflicts.

And, this results in the following error message,

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 5; 2615 2200 SCHEMA public Postgres
pg_restore: [archiver (db)] could not execute query: ERROR: schema "public" already exists
Command was: CREATE SCHEMA public;

But, what causes this error?

Using pg_restore from PostgreSQL 11.2 version, to restore a dump made by pg_dump 9.6 caused this error.

Therefore, we downgraded pg-restore back to 9.6. And this resolved the schema “public” already exists error effectively.

[Need assistance to restore the Postgres database from files?- We’ll help you.]

Conclusion

In short, commands like psql, pg_restore and pgAdmin tool helps to restore the Postgres database from files. In today’s write-up, we also discussed how our Support Engineers fix the errors related to database restoration.