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
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.
pg_dump -U postgres --encoding utf8 -h localhost -p 5432 myappdb > myappdbdump.sql
-Uis the username
--encoding utf8sets the encoding rather than letting the OS choose
-his the host
-pis the port
myappdb > myappdbdump.sqlis 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:
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:
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:
pg_dump -U postgres -h localhost -p 5432 -Fc -Z 9 --clean --if-exists myappdb > myappdbdump.sql
Fcmeans the filetype is custom so we can use compression
-Z 9means compression of level 9 (goes from 0-9, with a higher number taking longer but compressing better)
To restore the file, use pg_restore:
pg_restore -h localhost -p 5432 -U postgres --clean --if-exists -d myappdb myappdbdump.sql
--clean --if-existsis used since my test database tables already existed
-ddefines the database name
Backing up a test database without compression created a 8.3mb file, using
-Z 9 the file shrunk to 647kb!