This doesn’t happen every day, but once in a while you may find yourself needing to remove all the tables/functions/views from a PostgreSQL database. The key is you don’t want to delete the database itself. Maybe you are on a hosted solution where you don’t really have access to drop/restore a database. For example, I’m using Heroku’s Postgres service and dropping the database is not an option.
Here is a simple command that will remove all the tables and restore to new.
psql postgres://USER:PASSWORD@HOSTNAME/DATABASE -c " DROP SCHEMA public CASCADE; CREATE SCHEMA public; GRANT USAGE ON SCHEMA public to PUBLIC; GRANT CREATE ON SCHEMA public to PUBLIC; COMMENT ON SCHEMA public IS 'standard public schema';"
You have the right to do this. You can check in psq with
my_database=> \dn+ List of schemas Name | Owner | Access privileges | Description --------+----------------+----------------------------------+------------------------ public | MY_USER | MY_USER=UC/MY_USER+ | standard public schema | | =UC/MY_USER | (1 row)
The schema you are trying to drop is the
You can access your database to run commands.
You may be wondering what that cryptic permissions list above even means. Like me, if you find yourself confused, there are a few key things to keep in mind. Maybe you come from a Unix background and the
+ seems like it’s adding a permission?
The + are part of the way psql formats the result, they are not part of the value. (ignore it)
To me that was really confusing. I also didn’t realize that each new line represented a new access privilege. This is confusing because
=UC/MY_USER doesn’t resemble the above one. It’s the access for
PUBLIC. We get from the above SQL commands
GRANT USAGE ON SCHEMA public to PUBLIC; GRANT CREATE ON SCHEMA public to PUBLIC;
Straight from the PG docs
rolename=xxxx -- privileges granted to a role =xxxx -- privileges granted to PUBLIC r -- SELECT ("read") w -- UPDATE ("write") a -- INSERT ("append") d -- DELETE D -- TRUNCATE x -- REFERENCES t -- TRIGGER X -- EXECUTE U -- USAGE C -- CREATE c -- CONNECT T -- TEMPORARY arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects) * -- grant option for preceding privilege /yyyy -- role that granted this privilege
Did this help you out? It took me a few days to piece together all this information together, I hope this saves you some time (who knows, maybe the future me will be thankful I wrote this down). Let me know your thoughts. firstname.lastname@example.org