Postgres Backups with pg_dump, pg_restore & psql

Sep 30, 2022

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.

pg_dump

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 127.0.0.1 -p 5432 -W -f out.sql.tar.gz -Z 9 portal2

pg_restore

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

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

psql

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

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

More posts

• Next: Postgres Show Table Size on Disk
• Previous: Left Shift with Float Conversion in Go!