Published on

How to Backup Your PostgreSQL Database with pg_dump

Authors

In order to back up our database, we'll need to have pg-dump cli that comes preinstalled with the Postgres server when you install it on your machine.

If you don't have it then you'll need to install it.

For MacOS you can install it easily with brew.sh

brew install postgresql

To verify that you've properly installed you can run the following command:

pg_dump --version

This should output your pg_dump version. If you get a command not found error then make sure that PostgreSQL binaries are added to your PATH (in ~/.bash_profile, ~/.zshrc, etc.)

Talking backup

You can run the following command to take the backup of your database

pg_dump "postgresql://<username>@<hostname>:5432/<database_name>" -F c -b -v -f <backup_file_name>.dump

It'll ask for the database when you run the command.

And don't want to enter the password each time it runs. Then you can set PGPASSWORD env property like this:

PGPASSWORD=<password> pg_dump "postgresql://<username>@<hostname>:5432/<database_name>" -F c -b -v -f <backup_file_name>.dump

TLDR of the command args

  • PGPASSWORD -> Sets your password for PostgreSQL user
  • pg_dump -> CLI to create a Backup
  • -F c -> Create backup in custom file format
  • -b -> includes binary data in the backup
  • -v -> enables verbose mode to display progress info
  • -f -> specifies the name and location of the backup file to be created

Common Issues

Happy backing-up database!