How to Back Up and Restore a PostgreSQL Database
Jian Jye • December 17, 2015postgresql
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
-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!
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.