I'm writing a shell script (will become a cronjob) that will:
1: dump my production database
2: import the dump into my development database
Between step 1 and 2, I need to clear the development database (drop all tables?). How is this best accomplished from a shell script? So far, it looks like this:
#!/bin/bash
time=`date '+%Y'-'%m'-'%d'`
# 1. export(dump) the current production database
pg_dump -U production_db_name > /backup/dir/backup-${time}.sql
# missing step: drop all tables from development database so it can be re-populated
# 2. load the backup into the development database
psql -U development_db_name < backup/dir/backup-${time}.sql
I'd just drop the database and then re-create it. On a UNIX or Linux system, that should do it:
$ dropdb development_db_name
$ createdb developmnent_db_name
That's how I do it, actually.