How to Resetting Your PostgreSQL Database?

Maintaining a clean development environment or resetting a staging database to a known state is often crucial in database-driven projects. In PostgreSQL, the public schema is the default namespace for all your objects—so it’s often the first place you want to “wipe and rebuild.” Below, you’ll find what the commands DROP SCHEMA public CASCADE; and CREATE SCHEMA public; do, when to use them, and how to do so safely.

What Does DROP SCHEMA public CASCADE; Do?

• Drops the public schema: removes the namespace itself.
• CASCADE: automatically drops all objects within it—tables, views, sequences, functions, etc.
• Irreversible action: data and definitions are gone unless backed up first.

-- Remove the public schema and everything inside it
      DROP SCHEMA public CASCADE;
      

Use Case: resetting a development or staging database before a fresh migration run.

What Does CREATE SCHEMA public; Do?

After dropping, many tools expect public to exist. Recreating it restores defaults:

-- Recreate the public schema
      CREATE SCHEMA public;
      GRANT ALL ON SCHEMA public TO postgres;
      GRANT ALL ON SCHEMA public TO public;
      

Step-by-Step Guide

1. Back Up Your Database
   pg_dump -U your_user -h your_host -Fc -f backup.dump your_database
2. Connect to PostgreSQL
   psql -U your_user -d your_database
3. Drop & Recreate Inside a Transaction:
   

   BEGIN;
           DROP SCHEMA public CASCADE;
           CREATE SCHEMA public;
           GRANT ALL ON SCHEMA public TO postgres;
           GRANT ALL ON SCHEMA public TO public;
         COMMIT;

4. Run Your Migrations
   Use your migration tool (Flyway, Liquibase, Rails, Django, etc.) to rebuild objects.

When Not to Use These Commands

• Production data: you’ll erase live customer data.
• Partial resets: use TRUNCATE on specific tables instead.
• Complex cross-schema dependencies: may cascade unintentionally.

Alternatives & Best Practices

• Selective Truncation:
  TRUNCATE table1, table2 RESTART IDENTITY CASCADE;
• Schema Versioning: use migration frameworks for safe rollbacks.
• Dedicated Schemas: create dev or test schemas to isolate environments.

Conclusion

The commands DROP SCHEMA public CASCADE; and CREATE SCHEMA public; give you a clean slate for your PostgreSQL database—perfect for dev resets or CI pipelines. With reliable backups and migration tooling, you’ll keep your schema in sync with your application’s needs.