Postgres: clear entire database before re-creating / re-populating from bash script

Question

hi folks,

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

Many thanks in advance!

Martin

Answer 1

Although the following line is taken from a windows batch script, the command should be quite similar:

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

This command is used to clear the whole database, by actually dropping it. The %DATABASE% in the command is a windows style environment variable that evaluates to the database name. You will need to substitute that by your development_db_name.

Answer 2

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.

Answer 3

If you don't actually need a backup of the database dumped onto disk in an plain-text .sql script file format, you could connect pg_dump and pg_restore directly together over a pipe.

To drop and recreate tables, you could use the --clean command-line option for pg_dump to emit SQL commands to clean (drop) database objects prior to (the commands for) creating them. (This will not drop the whole database, just each table/sequence/index/etc. before recreating them.)

The above two would look something like this:

pg_dump -U username --clean | pg_restore -U username