How to backup and restore a PostgreSQL database

As an open-source databases, PostgreSQL is in the top 3 most popularly used databases which often can be found as a managed service offered by the public cloud giants. In one of the previous posts, I’ve written about how easily you can create a PostgreSQL database in Azure. Anyway, in today’s blog post, I’m going to show you how can you backup and restore PostgreSQL databases from the command line.

Backing up and restoring databases could be found quite handy, especially in situations when something goes wrong. The most common cases are: someone accidentally dropped the databases, rolling back a new release, or even worse, unauthorized access of your company data.

Backup and dump are used interchangeably.

Prerequisites

• PostgreSQL

Backing up a PostgreSQL database

Backing up a PostgreSQL can be done in multiple ways, from the CLI using the pg_dump command utility tool, or via GUI using pgAdmin. Let’s see how can we do a backup via pg_dump.

pg_dump -h 0.0.0.0 -p 5432 -U <username> -Fc <db_name> > <db_name>.dump

Note(s):

• -h: Hostname.
• -p: Port.
• -U: Username.
• -Fc: Custom format for the output file.
• Make sure the PostgreSQL username has enough permissions to execute a backup.
• If you are not exposing the PostgreSQL server to any address which you shouldn’t, replace 0.0.0.0 with localhost.
• For larger size db dumps, you can compress it as well. For instance:

  pg_dump -h 0.0.0.0 -p 5432 -U <username> -d <db_name> | gzip > <db_name>.dump.gz
  

Restoring a PostgreSQL database

Restoring a database can be done in multiple ways as well, from the CLI using psql, pg_restore command utility tools, or via GUI using pgAdmin. Let’s see how can we do it from the command line.
pg_restore:

pg_restore -h 0.0.0.0 -p 5432 -U <username> -d <db_name> <db_name>.dump

psql:

psql -h 0.0.0.0 -p 5432 -U <username> <db_name> < <db_name>.dump

Restore compressed backup using pg_restore:

gunzip -c <db_name>.dump.gz | pg_restore -h 0.0.0.0 -p 5432 -U <username> -d <db_name>

Restore compressed backup using psql:

gunzip -c <db_name>.dump.gz | psql -h 0.0.0.0 -p 5432 -U <username> <db_name>

Conclusion

My 2 cents are to always have a backup and recovery procedures for your databases, or even your infrastructure. Review, update and automate it frequently. An early adoption could save a lot of time and stress, especially if things go south on the weekends.
Feel free to leave a comment below and if you find this tutorial useful, follow our official channel on Telegram.