December 17, 2015

How to Back Up and Restore a PostgreSQL Database

Backing up and restoring your PostgreSQL database is easy. Follow this guide and do it in 60 seconds.


To back up

To back up a PostgreSQL database, we use pg_dump to save the database into a text file.

$ pg_dump database -U username -h localhost -f filename.sql -C

The -h localhost option is very important if you intend to authenticate with password, or you might get this error:

pg_dump: [archiver (db)] connection to database "dbname" failed: FATAL: Peer authentication failed for user "username"

The short explanation is that, due to its default configuration, if you do not specify the host, PostgreSQL will attempt to log you in as the user that has the same username as your system login, thus it will not prompt you for your password and in the most likely case that your usernames are not the same, it will fail.

So remember to specify the host!


To restore

Restoring is easy, follow the line below:

$ psql -d database -U username -h localhost -f filename.sql

You might also be interested to know how to download or upload your .sql file via SSH / SCP in case you need to move the .sql file across servers.

***

Hello! My name is Jian Jye and I work on Laravel projects as my main job. If my article was helpful to you, a shoutout on Twitter would be awesome! I'm also available for hire if you need any help with Laravel. Contact me.