DEV Community

Muhammad Azeez
Muhammad Azeez

Posted on • Originally published at mazeez.dev

Backing up and restoring databases in Postgres

#postgres

To get a dump of a database you can use pg_dump or pg_dumpall for dumping an entire cluster. It supports 4 formats:

Format Description Restore via
plain Output a plain-text SQL script file (the default). psql
custom Output a custom-format archive suitable for input into pg_restore. Together with the directory output format, this is the most flexible output format in that it allows manual selection and reordering of archived items during restore. This format is also compressed by default. pg_restore
directory Output a directory-format archive suitable for input into pg_restore. This will create a directory with one file for each table and blob being dumped, plus a so-called Table of Contents file describing the dumped objects in a machine-readable format that pg_restore can read. A directory format archive can be manipulated with standard Unix tools; for example, files in an uncompressed archive can be compressed with the gzip tool. This format is compressed by default. pg_restore
tar Output a tar-format archive suitable for input into pg_restore. The tar format is compatible with the directory format: extracting a tar-format archive produces a valid directory-format archive. However, the tar format does not support compression. Also, when using tar format the relative order of table data items cannot be changed during restore. pg_restore

How to backup a database

To create a dump of sample-db in custom format and save it to sample-db.dump:

pg_dump -U postgres --encoding utf8 -F c -f sample-db.dump sample-db
Enter fullscreen mode Exit fullscreen mode

To create a dump of sample-db in plain format and save it to sample-db.sql:

pg_dump -U postgres --encoding utf8 -F p -f stoplist.sql stoplist
Enter fullscreen mode Exit fullscreen mode

How to restore a database dump

First create an empty database to restore the dump to.

# We use template0 because it is empty and it doesn't conflict with the schemas and tables in the dump.
createdb -U postgres restored-db --template=template0
Enter fullscreen mode Exit fullscreen mode

Restore custom, directory, and tar format dumps using pg_restore:

pg_restore -U postgres -d restored-db < ./sample-db.dump
Enter fullscreen mode Exit fullscreen mode

Restore plain format dumps using psql:

psql -U postgres -d restored-db < ./sample-db.sql
Enter fullscreen mode Exit fullscreen mode

Note: In Powershell the < operator doesn't work. So you'll have to use cmd on Windows.

Errors you might come across:

  1. Corrupted dumps 
pg_restore: [archiver] found unexpected block id (x) when reading data -- expected y
Enter fullscreen mode Exit fullscreen mode 
pg_restore: error unrecognized data block type
Enter fullscreen mode Exit fullscreen mode

This might mean the dump is corrupted. One possible reason is the database contained Unicode data and the dump was not encoded in utf8. Use --encoding utf8 when running pg_dump to fix that.

  1. Restoring plain format dumps using pg_restore: 
pg_restore: [archiver] did not find magic string in file header
Enter fullscreen mode Exit fullscreen mode 
pg_restore: [archiver] input file does not appear to be a valid archive
Enter fullscreen mode Exit fullscreen mode

This happens if you run pg_restore on a plain format dump. Use psql to restore it instead.

If you have any other tips/tricks, please write the down in the comments!

Top comments (0)

Read next

gwenshap profile image

Debunking 6 common pgvector myths

Gwen (Chen) Shapira -

pfilaretov42 profile image

Spring Boot Avengers: Uniting Spring Data JDBC and PostgreSQL's JSONB

Petr Filaretov -

seewhy profile image

How to Consolidate Multiple PostgreSQL Databases into a Single Database Using Debezium, Kafka, and Power BI for Analytics

CYRIL OSSAI -

pawsql profile image

🚀 Boosting TPC-H Q2 Query Performance by 1000x times: PawSQL Optimization Techniques

Tomas@PawSQL -