Introduction

Dropping every table in a PostgreSQL database is a destructive reset operation that is usually appropriate only for local development, tests, or disposable environments. The command itself can be short, but the safe workflow always starts with confirming the target database and ends with recreating schema state through migrations.

Verify the Target Before You Destroy Anything

The first step is not the drop command. It is confirming where you are connected and what objects are about to be removed.

1SELECT current_database(), current_user;
2
3SELECT schemaname, tablename
4FROM pg_tables
5WHERE schemaname = 'public'
6ORDER BY tablename;

This sounds obvious, but it is the difference between resetting a local database and destroying a shared one. In multi-environment shells, this check should be routine.

Fastest Reset: Drop and Recreate the Schema

If your application objects live in the public schema, the fastest complete reset is usually to drop the schema with CASCADE and recreate it.

1DROP SCHEMA public CASCADE;
2CREATE SCHEMA public;
3GRANT ALL ON SCHEMA public TO postgres;
4GRANT ALL ON SCHEMA public TO public;

This removes tables, views, sequences, constraints, and other dependent objects inside the schema. It is efficient, but it is deliberately broad, so it belongs in controlled environments only.

Drop Tables Explicitly When You Need More Control

If you do not want to rebuild the whole schema, use a procedural block that drops tables one by one.

1DO $$
2DECLARE
3  item RECORD;
4BEGIN
5  FOR item IN (
6    SELECT schemaname, tablename
7    FROM pg_tables
8    WHERE schemaname = 'public'
9  ) LOOP
10    EXECUTE format(
11      'DROP TABLE IF EXISTS %I.%I CASCADE',
12      item.schemaname,
13      item.tablename
14    );
15  END LOOP;
16END $$;

This gives you more control over scope and is easier to adapt if you want to exclude certain tables or operate on a different schema list.

Recreate the Whole Database for Local Resets

In local workflows, it is sometimes simpler to recreate the entire database instead of dropping objects inside it.

dropdb mydb
createdb mydb

That approach is clean and easy to reason about, but it also resets ownership, permissions, and extensions unless your setup recreates them automatically. It is best paired with migrations and bootstrap scripts.

Watch for Multiple Schemas and Active Sessions

Many systems keep objects outside public, so do not assume one schema tells the whole story. Before running a reset, inspect the non-system schemas:

1SELECT nspname
2FROM pg_namespace
3WHERE nspname NOT LIKE 'pg_%'
4  AND nspname <> 'information_schema';

Also check for active sessions if drops appear to hang or block:

SELECT pid, usename, state, query
FROM pg_stat_activity
WHERE datname = current_database();

Reset scripts behave much better when they run against isolated dev or test databases instead of shared ones with live traffic.

Rebuild Immediately After the Drop

A drop is only half the workflow. After the reset, rerun the migrations or schema bootstrap and verify that the expected objects are back.

SELECT COUNT(*) AS table_count
FROM pg_tables
WHERE schemaname = 'public';

Then verify things your application depends on:

1. core tables exist
2. required extensions are installed
3. seed data loaded when expected
4. the application can start cleanly

This is what turns a destructive reset into a reproducible environment reset.

Add Safety Guards to Automation

If this operation is scripted, add environment checks before the destructive step. Examples include:

1. refusing to run unless the database name matches a dev or test pattern
2. requiring an explicit environment variable such as ALLOW_DB_RESET=true
3. aborting if the connected role looks like a production service account

Those small guards prevent most catastrophic mistakes.

Common Pitfalls

The biggest mistake is running the drop command before verifying the current database. Another is forgetting that CASCADE removes more than tables. Teams also assume everything is stored in public, then wonder why old objects remain in other schemas after the reset.

Summary

• Verify the current database and the target objects before dropping anything.
• Drop and recreate public for the fastest broad reset in disposable environments.
• Use a dynamic SQL loop when you want more controlled table-only deletion.
• Recreating the whole database can be simpler for local workflows.
• Rebuild with migrations and validate the result immediately after the reset.