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