Postgresql backup - how to backup a Postgres database

Postgresql Backup FAQ: How do I create a backup of a Postgresql database?

Solution: I create a Postgresql backup (a Postgresql dump) for this website using the pg_dump command, like this:

pg_dump -h db_server -U db_user -W db_name > db.dump

Password:

With this Postgres pg_dump command I'm doing the following:

  • Connecting to a Postgres server named "db_server"
  • Connecting as the Postgres user "db_user"
  • Forcing a password prompt with "-W"
  • Dumping the Postgres database (a "Postgres dump" of the database) named "db_name"
  • Sending the Postgres output to a file named "db.dump"

Notice that when you run this command, it will prompt you for the password for the Postgres database user you supply.

Using a Postgres database password in a backup script

In theory you should never include a Postgres database password in a backup script. There, you have been warned.

However, if for some reason you want to do this, I just tried this on a throwaway Raspberry Pi Linux system and found that it works:

pg_dump --no-owner \
        --dbname=postgresql://username:password@localhost:5432/db_name > db_name.dump

This approach also works:

export PGPASSWORD=my-secret-password
pg_dump -h localhost -U <username> <database-name> > db_name.dump

Again, I wouldn’t want to do that on a production server, but if you want to do it on a personal system (like my Raspberry Pi), that might be okay.

Always test your backups!

As an important note, always make sure you test your backups. For that information, here’s a link to some "backup and restore" documentation at the Postgresql web site.

For my own notes, this also looks like an okay page: