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:
books i’ve written
 |
 |
 |
 |