Is there a command to delete/drop everything in a Postgres database?

Question

I'm currently learning my way around Postgres so I'm running lots of tests in an experimental database I set up. Is there an easy way to bring back the database into a "clean slate" state without recreating the database from scratch? I don't really want to have to enter the administrator password or reconfigure database permissions every time I want to get rid of all the mess I made.

Answer 1

If all the objects are owned by the same user you can use:

drop owned by db_user_name cascade;

This will drop all objects that the user db_user_name owns. This can be executed while being logged in as that user, so no special privileges are required.

More details in the manual: http://www.postgresql.org/docs/current/static/sql-drop-owned.html

Answer 2

You could, technically, drop the public schema and re-create that, if it's the only one you're using and you're confident that the rest was properly created or configured alongside (e.g. default collation). Be wary that many extensions install functions in public while you do.

Other than that, no… you'll be better off dropping the database and re-creating it if you want to start with a truly clean slate.