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

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 development_db_name

That's how I do it, actually.

If you don't actually need a backup of the database dumped onto disk in a 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

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

psql -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 Windows should be %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.

To dump:

pg_dump -Fc mydb > db.dump

To restore:

pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d my_db db/latest.dump

If you want to clean your database named "example_db":

1) Login to another db(for example 'postgres'):

psql postgres

2) Remove your database:

DROP DATABASE example_db;

3) Recreate your database:

CREATE DATABASE example_db;