How to restore PostgreSQL dump file into Postgres databases?

sql

database-management

backup-and-restore

postgresql

byAnton Shumikhin·Oct 23, 2024

Are you looking to restore a PostgreSQL dump? Use pg_restore for binary dumps or psql for SQL scripts. Give these commands a spin:

Binary:

pg_restore -d your_database -U your_username your_dump_file.dump

SQL:

//Everyone loves SQL, right? ;)
psql -d your_database -U your_username -f your_dump_file.sql

Swap your_database, your_username, and your_dump_file with your real values. Make sure the Postgres service is humming nicely and you have squared away database permissions.

Preparatory steps and cool tricks

Before diving in, ensure you have a compatible PostgreSQL version for your dump file. If your dump file is compressed, better have the right decompression tools handy.

If you're the database superuser or have restore permissions, you're good to go. However, if permission errors are raining on your parade, modify file permissions using chmod or run as the superuser (sudo if you ask nicely).

Create your future home i.e., the target database using createdb before restoring the dump. Need to set ownership? Just use the -O flag in the createdb command.

For those stubborn permission errors, try sudo su postgres to become the Postgres user. For those special .backup files made with pg_dump using -Fc, remember to use pg_restore, and don't forget the -d flag for the target database.

Common roadblocks (and how to bulldoze them)

Running into restoration roadblocks? Here's your GPS:

Permissions: Feel like a VIP yet? Get those rights sorted.
File Paths: Use forward slashes. No backtalk.
Database Version: Got the compatible version?
File Accessibility: Ensure your dump file isn't playing hide-and-seek.
Content Review: Does your SQL dump play the part for a satisfying restoration with schema creation and data insertion commands?

Efficiency hacks and power-boosters

Working with large databases? Speedrun your restoration with these tips:

Selective Restore: Flaunt the --table or --schema flag with pg_restore to restore only your prized tables or glamorous schemas.
Concurrent Restore: The -j option is your best friend for a parallelized restoration session.
Clean Restore: The --clean flag drops database objects and recreates them. Out with the old!
No Owner? No Problem: Don't want original owners? Use --no-owner.

Special cases (Because databases are special, just like you)

Got unique conditions? We've got answers:

If you're an pgAdmin aficionado, its built-in restore function handles "Custom or tar" or "Directory" backups with ease.
Want to exclude some unsavory or sensitive commands? Edit your dump file - just make sure to keep those all-important ALTER TABLE commands.
Befuddled by psql output? It's not a list of PostgreSQL default tables - it's your restoration progress report!