Backup and Restore PostgreSQL Database

Creating a database backup of a PostgreSQL database using pg_dump and restoring it using psql is pretty straightforward. A utility like gzip can be used to save disk space when creating the database dump.

Backup

We first define all required variables to make the script easier to read and edit. PGPASSWORD has to be set as an environment variable to prevent a password prompt. We use pg_dump to dump the database and then pipe the output through gzip to save disk space.

export POSTGRES_HOST=production.example.com
export POSTGRES_PORT=5432
export POSTGRES_DATABASE=database
export POSTGRES_USER=user
export PGPASSWORD=password

POSTGRES_HOST_OPTS="-h $POSTGRES_HOST -p $POSTGRES_PORT -U $POSTGRES_USER"

pg_dump $POSTGRES_HOST_OPTS $POSTGRES_DATABASE | gzip > dump.sql.gz

Restore

To restore the just created database dump we first read the file into cat, pipe it through gunzip to unzip it and eventually pipe the unzipped dump through psql to import it into the database.

export POSTGRES_HOST=localhost
export POSTGRES_PORT=5432
export POSTGRES_DATABASE=database
export POSTGRES_USER=user
export PGPASSWORD=password

POSTGRES_HOST_OPTS="-h $POSTGRES_HOST -p $POSTGRES_PORT -U $POSTGRES_USER"

cat dump.sql.gz | gunzip | psql $POSTGRES_HOST_OPTS -d $POSTGRES_DATABASE