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.

user330315 · Accepted Answer · 2013-12-29 19:52:28Z

26

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

answered Dec 29, 2013 at 19:52

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

hugomg Over a year ago

So far, this is doing exactly what I wanted. Thanks!

Denis de Bernardy · Accepted Answer · 2013-12-29 22:59:30Z

3

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.

edited Dec 29, 2013 at 22:59

answered Dec 29, 2013 at 19:44

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related