How do I restore a PostgreSQL backup from the command line?

General Discussions
, ,
1

I usually work with pgAdmin3 locally, but on the remote server I’m managing, I only have command-line access, no GUI.

I’ve already created a backup of the PostgreSQL database and transferred it to the remote server.

Now I need to restore the backup using the command line, but most of the resources I find talk about pg_dump or GUI-based tools.

What’s the correct way to use pg_restore (or any other CLI tool) to restore a PostgreSQL backup file?

Are there any flags or options I should be aware of for different formats like custom or plain SQL dumps?

2

I regularly manage PostgreSQL databases on remote servers via CLI, and for custom-format .backup files created using pg_dump -Fc, this is my go-to method:

bash
Copy
Edit
pg_restore -U postgres -d your_database_name /path/to/your/backup_file.backup

Make sure the target database exists beforehand.

If it doesn’t, create it first:

bash
Copy
Edit
createdb -U postgres your_database_name

Also, add the -c flag if you want to drop existing objects before restoring. And don’t forget: pg_restore needs the PGPASSWORD env variable or .pgpass file if password auth is enabled.

3

If your backup file is a plain .sql dump (not a custom format), then pg_restore won’t work.

Instead, you should use:

bash
Copy
Edit
psql -U postgres -d your_database_name -f /path/to/your/backup_file.sql

This method just replays the SQL statements in the file, so it’s simple and direct.

If you’re not sure of the format, open the file. If it starts with – PostgreSQL database dump, it’s probably a plain SQL dump.

4

@archna.vv When dealing with production environments, I like to restore with fine-grained control using options like:

bash
Copy
Edit
pg_restore -U postgres -d new_db --role=myapp_role --no-owner --clean /path/to/file.backup

Here, --no-owner avoids ownership errors, and --clean drops objects before recreating them.

This gives you a cleaner restore if roles or permissions differ between systems.

Also, run this inside a tmux or screen session if you’re on SSH, so the restore doesn’t break if your connection drops!

5