I've got a nice empty database with schema defined and reference data populated as a starting point for my app. I want to return to this state often during testing and have a good backup strategy for production data when I go into production next week.For Java coding, I create the entire DDL along with test data in my Ant scripts (code originally courtesy of Adminiscott), wherever possible. This allows a consistent frame of reference for JUnit tests as well.
Should I prefer the text or archive formats with pg_dump? What are the advantages/disadvantages of each?For backing up PG databases, I have created a quickie little shell script:
#!/bin/sh
# Command to backup a Postgresql database
pg_dump -U $DB_USERNAME --create --inserts --file=$1.sql $1
If your database is named foo, then you would invoke this via backup foo and expect to find full DDL along with current data inside ./foo.sql.
HTH.