How to restore a PostgreSQL .dump or .sql file on Windows

How you restore depends on what kind of file you have, so check that first.

Which kind of backup is it?

When unsure, ask pg_restore: pg_restore -l backup.dump lists the contents of an archive, or fails with "input file appears to be a text format dump. Please use psql." The details are in custom vs. plain format.

Restore a .dump into a new database

$bin = 'C:\Program Files\PostgreSQL\18\bin'
& "$bin\createdb.exe" -h localhost -U postgres shop_restored
& "$bin\pg_restore.exe" -h localhost -U postgres -d shop_restored --no-owner --no-privileges 'D:\Backups\shop-20261014-020000.dump'

• --no-owner --no-privileges let the restore succeed even when the roles that owned things on the original server don't exist here. Omit them when restoring onto the same server with the same roles.
• Add -j 4 to restore with four parallel jobs (custom and directory formats only). It's much faster for big databases.
• Use a pg_restore at least as new as the pg_dump that made the file. An older one fails with "unsupported version in file header".

Restore over an existing database

To replace a database's contents with the backup:

& "$bin\pg_restore.exe" -h localhost -U postgres -d shop --clean --if-exists --no-owner --no-privileges 'D:\Backups\shop-20261014-020000.dump'

--clean drops each object before recreating it; --if-exists stops it complaining about objects that aren't there. Objects in the database that aren't in the backup are left alone. For an exact copy, drop and recreate the database first:

& "$bin\dropdb.exe" -h localhost -U postgres --if-exists shop
& "$bin\createdb.exe" -h localhost -U postgres shop

Restore a plain .sql file

& "$bin\createdb.exe" -h localhost -U postgres shop_restored
& "$bin\psql.exe" -h localhost -U postgres -d shop_restored -v ON_ERROR_STOP=1 -f 'D:\Backups\shop.sql'

-v ON_ERROR_STOP=1 stops at the first error instead of running the rest of the file on top of a half-restored database. Use -f rather than Get-Content … | psql or < redirection, which re-encode the file in Windows PowerShell 5.1.

Restore just one table

From a custom-format dump:

& "$bin\pg_restore.exe" -h localhost -U postgres -d shop_restored -t orders --no-owner 'D:\Backups\shop.dump'

A common recovery pattern is to restore the whole backup into a new database, then copy the rows you need back into production with SQL. That beats restoring over production to undo one bad DELETE.

Common errors

• role "xyz" does not exist: add --no-owner --no-privileges.
• database "shop" does not exist: create it with createdb first, or use -C -d postgres to have pg_restore create it.
• unsupported version (1.16) in file header: your pg_restore is older than the pg_dump that made the file. Use newer client tools.
• extension "…" is not available: the target server lacks an extension the source had (PostGIS, for example). Install it, or restore elsewhere.
• Errors on schemas like auth or storage: a Supabase backup restored outside Supabase. Your public data is usually fine; see the Supabase guide.
• "errors ignored on restore: N" at the end: pg_restore continues past errors by default. Read the messages above it; ownership and permission warnings are often harmless. Add --exit-on-error to stop at the first one.

How PostgresBackup restores

In PostgresBackup, open Backup History and click Restore on any backup, or pick any .dump or .sql file. Choose the target server and database (new or existing), optionally delete everything in the target first for an exact copy, and confirm. It picks pg_restore or psql based on the file and runs the right options, including parallel jobs for custom-format files (one per processor core, up to 4), and it creates the database if needed. Restoring is free, with or without a license.