Howto Backup and Restore your PostgreSQL databases

Every installation of postgresql datbases comes with the command line tools pg_dump and pg_restore. With this two commands we have all that we need to backup and restore our databases. Of course there are also graphical tools that we can use to backup and restore.

Backup and restore with pg_dump and pg_restore.

The TLDR; is as sample as:

For text file backups:

Backup:  $ pg_dump -U {user-name} {source_db} -f {dumpfilename.sql}
Restore: $ psql -U {user-name} -d {desintation_db}-f {dumpfilename.sql}

For tar.gz backups:

Backup:  $ pg_dump -U {user-name} {source_db} -F tar  -f {dumpfilename.tar.gz}
Restore: $ psql -U {user-name} -d {desintation_db}-f {dumpfilename.tar.gz}

Backup A Single Database

The following command will backup the "mydatabase" that belongs to "dbuser" to the file mybackup.sql

pg_dump -h 127.0.0.1 -U dbuser mydatabase -f mybackup.sql

You will be prompted for the password. If you were going to put this in a script, then you can set the environment variable PGPASSWORD and pg_dump will use it instead of prompting you as follows:

PGPASSWORD="mypassword" 
pg_dump -h 127.0.0.1 -U dbuser mydatabase -f mybackup.sql

Restoring the backup

Above we created our backup.

To restore the .sql file backup assuming the database already exists:

pg_restore -h 127.0.0.1 -U dbuser  -d mydatabase mybackup.sql

If the database does not yet exist. (For example we are restoring to a new server)

pg_restore -h 127.0.0.1 -U dbuser  -C -d  mydatabase mybackup.sql