Backing up and restoring database info is very useful during database development.

If you haven’t installed Postgres on your mac, use the command brew install postgresql in your terminal.

Backing up the database

The command pg_dump can be used to backup the schema and data in a Postgres database (See the docs). Here is an example of creating a backup file which on MacOS is saved in your Home directory.

PowerShell
pg_dump -U postgres --encoding utf8 -h localhost -p 5432 myappdb > myappdbdump.sql
  • -U is the username
  • --encoding utf8 sets the encoding rather than letting the OS choose
  • -h is the host
  • -p is the port
  • myappdb > myappdbdump.sql is the database name, followed by the filename of the backup dump file.

This file can be used to import the backup into an empty database.

Importing over existing data

If you look at the newly created .sql file above, you can see it uses ‘CREATE TABLE’ commands. This means if you you can only restore this backup file into a database with an empty table.

If you want to overwrite existing data where these tables already exist with ‘ALTER TABLE IF EXISTS ONLY’ instead, you can add the flags --clean --if-exists to the dump command. Our backup command now looks like this:

PowerShell
pg_dump -U postgres --encoding utf8 -h localhost -p 5432 --clean --if-exists myappdb > myappdbdump.sql

NOTE: This is the same format the popular app hosting platform render.com downloads it’s postgres backups in.

Restoring the database

To restore a database use this command, defining the database name and backup dump filename:

PowerShell
psql -U postgres -h localhost -p 5432 myappdb < myappdbdump.sql

Backup and restore with compressed files

To create a compressed backup dump file you need to use the pg_dump and pg_restore commands:

PowerShell
pg_dump -U postgres -h localhost -p 5432 -Fc -Z 9 --clean --if-exists myappdb > myappdbdump.sql
  • Fc means the filetype is custom so we can use compression
  • -Z 9 means compression of level 9 (goes from 0-9, with a higher number taking longer but compressing better)

To restore the file, use pg_restore:

PowerShell
pg_restore -h localhost -p 5432 -U postgres --clean --if-exists -d myappdb myappdbdump.sql
  • --clean --if-exists is used since my test database tables already existed
  • -d defines the database name

Backing up a test database without compression created a 8.3mb file, using -Z 9 the file shrunk to 647kb!

Tools

Tools like Beekeeper Studio and pgAdmin provide a handy UI for interacting with your database.

Further reading