I am writing a spend script (will end up a cronjob) which will:

1: dump my production database

2: import the dump into my development database

Between step one and a pair of, I have to obvious the expansion database (drop all tables?). How's this best accomplished from the spend script? To date, it appears such as this:

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

Thank you ahead of time!


I'd just drop the database after which re-create it. On the UNIX or A linux systemunix, which should get it done:

$ dropdb development_db_name
$ createdb developmnent_db_name

That's the way i get it done, really.

If you do not really require a backup from the database left onto disk within an plain-text .sql script extendable, you can connect pg_dump and pg_restore directly together on the pipe.

To decrease and recreate tables, you could utilize the --clean command-line choice for pg_dump to emit SQL instructions to wash (drop) database objects just before (the instructions for) creating them. (This can not drop the entire database, just each table/sequence/index/etc. before re-creating them.)

The above mentioned two would look something similar to this:

pg_dump -U username --clean pg_restore -U username

Even though following lines are obtained from a home windows batch script, the command ought to be quite similar:

psql.exe -U username -h localhost -d postgres -c "DROP DATABASE \"%DATABASE%\";"

This command can be used to obvious the entire database, by really shedding it. The %DATABASE% within the command is really a home windows style atmosphere variable that examines towards the database title. You will have to substitute that because of your development_db_name.