Creating a Postgresql Development Database

Notes on how to restore a Pg database for a dev environment to make it simple to create additional copies or revert back to the production schema.

Drop and restore your db from a recent backup. Include a datestamp in the database name to keep track of the version you're restoring from. The specifics of this commands will vary depending on your permissions, backup method, etc.

$ dropdb mydevdb_20070106
$ createdb -U postgres -E LATIN1 mydevdb_20070106
$ pg_restore -U postgres -d mydevdb_20070106 ~/db-backup.2007-01-06

If you're restoring from production data, be sure to munge it at this point to remove all valuable/sensitive information, and delete the backup file so your dev environment isn't a target.

Now with this restored version of the database you can create any number of additional copies to actually work on. Leave the initial restore untouched so you can always revert back to it.

$ psql mydevdb_20070106
mydevdb_20070106=# CREATE DATABASE mydevdb WITH TEMPLATE mydevdb_20070106;
CREATE DATABASE
mydevdb_20070106=# CREATE DATABASE toms_devdb WITH TEMPLATE mydevdb_20070106;
CREATE DATABASE