PostgreSQL database Backup and Restore from the CLI

By | 1 April, 2016

Quick note about how to backup and restore a PostgreSQL database from the command line.

Things to know:

– Name of the database you want to backup;
– Username and password of the database user;
– your way around a command line – at least how to open it or who to ask to open it for you.

To make a backup:

[user]$ pg_dump -U [usernamehere] -W [databasename] > [backupfilename]

well, an example:

[user]$ pg_dump -U queequeg -W museums > 20160401museus_backup.sql

In the example:

‘pg_dump’ command used to make the backup
‘-U queequeg’ switch to use user ‘queequeg’
‘-W’ prompt for the user’s password
‘museums’ is the name of the database i want to backup
‘20160401museus_backup.sql’ is the name of the backup file

NOTE: if you get a “Access denied.” with this command, its a permission issue, i.e., you need permission to write the backup file. So run it as Administrator/root – in windows, open the command line in administrator mode – or save the backup file to a directory where you have write permissions.

To restore a backup (using same data as in the above example):

[user]$ psql -U queequeg -W museums2 < 20160401museus_backup.sql


‘psql’ command used to restore the backup
‘-U queequeg’ and ‘-W’ same as above
‘museums2’ database to where the backup file will be restored
‘20160401museus_backup.sql’ the backup file

ANOTHER NOTE: If you #@!%& up your databases by issuing the wrong commands/being a jackass/not being worthy of reading this blog, dont blame me.

To learn lots of cool stuff beyond the scope of this post, go read (and really go because this article is a mere reference to the wonderful things you can screw up using these two commands):

PostgreSQL Docs – Chapter 24. Backup and Restore