How to Restore PostgreSQL from a .dmp File

It’s a fairly straightforward process, but if you’ve never done it, it can be a bit daunting.

It’s a fairly straightforward process, but if you’ve never done it, it can be a bit daunting. The first time I did it, I was lost and confused as well.

Step 1: Ensure `pg_restore` is installed

Make sure you have PostgreSQL installed. You can check by running:

pg_restore --version

If it’s not installed, install PostgreSQL first:

Ubuntu/Debian:
```
sudo apt install postgresql
```
Mac (Homebrew):
```
brew install postgresql
```
Windows: Install PostgreSQL from postgresql.org.

Step 2: Navigate to the Dump File Location

Open a terminal and change to the directory where your .dmp file is stored (like /Downloads for example):

cd /path/to/your/dump

Step 3: Restore the Schema

Before restoring, ensure your target database exists. If not, create it:

createdb -U your_db_user your_database_name

Then restore the schema (without data). You want to restore the schema first because otherwise you’ll run into foreign key doesn’t exist errors.

You’ll be prompted to enter your database password. Make sure you set your host, port, username, and database name correctly, and reference the .dmp file properly:

pg_restore --verbose --clean --if-exists --no-owner --no-acl \\
  --host=your_remote_host \\
  --port=5432 \\
  --username=your_db_user \\
  --dbname=your_database_name \\
  --schema-only \\
  your_dump_file.dmp

Step 4: Restore the Data

Now restore the actual data:

pg_restore --verbose --clean --if-exists --no-owner --no-acl \\
  --host=your_remote_host \\
  --port=5432 \\
  --username=your_db_user \\
  --dbname=your_database_name \\
  --data-only \\
  your_dump_file.dmp

Alternative: Full Restore in One Command

If you want to restore everything at once (schema + data):

pg_restore --verbose --clean --if-exists --no-owner --no-acl \\
  --host=your_remote_host \\
  --port=5432 \\
  --username=your_db_user \\
  --dbname=your_database_name \\
  your_dump_file.dmp

Important Notes:

If the dump was created in plain SQL format (.sql), use psql instead:

psql -U your_db_user -d your_database_name -f your_dump_file.sql

If restoring to a fresh database, you don’t need -clean or -if-exists.
If restoring from a remote dump, ensure network settings and firewall rules allow access.

Done!

Hope this was helpful :) Your PostgreSQL database should now be restored successfully. 🚀