Backup Postgres database (Windows 7)

backup_postgres

Most of what I learned came from here: http://www.postgresql.org/docs/9.1/static/app-pgdump.html

The goal is to backup my application database into a single .sql file and store that on a network drive, so if my machine craps out I can get a new machine, reinstall Postgres and restore my database.

Here's the steps to do that on Windows 7:

Step 1: cd into Postgres bin directory so you can run pg_dump.exe

$ cd \Program Files\PostgreSQL\9.3\bin

Step 2: backup a specific database

$ pg_dump.exe --username=postgres -C my_database > c:/my_database.sql

Note: Use the --username switch to run the command as the master postgres user to gain the correct permissions. The -C switch tells pg_dump to also include the CREATE DATABASE statement in the backup (makes a restore easier). The > symbol just tells the Windows command prompt where to save the file.

Throw the my_database.sql file onto a network drive and you're covered in case you machine fails!

Good explanation, but I prefer to use PostgreSQL-Backup http://postgresql-backup.com/postgresql-blog/backup-postgres-database/ . This is the simple tool, which makes backups according to your schedule and sends them to selected destinations.