Home | About | Apps | Github | Rss

Postgres Backups with pg_dump, pg_restore & psql

Postgres by default comes with utilities for running backups and restores. Postgres can dump to either a plain sql file or to its own internal format. The advantage of using the internal format is that it allows you to selectively restore data and schema.


It can dump the database in either sql file format or in postgres’s internal format.

Using this command dumps sql and gzips it

pg_dump -U postgres -h -p 5432 -W -f out.sql.tar.gz -Z 9 portal2


Restores from postgres archive format. Note: this won’t work with just SQL files

pg_restore -U postgres -h -p 5432 -d portal2 -W portal2_2022.09.29_1800.sql


To import just SQL files you’ll need to use psql

psql -U postgres -h -p 5432 -d portal2 -W < portal2_2022.09.29_1800.sql

More posts