0

I'm using django-tenants, and for some tests I need to delete all schemas at once, so I was wondering how could I delete all schemas with a single sentence/script from postgresql shell, because deleting one by one is not scalable.

Thx so much.

David Heredia
  • 3
  • 4
  • 2
    Would it be worth it to drop the entire database and create it again? – David Heredia Mar 22 '22 at 23:47
  • You might wanna check out [this thread](https://stackoverflow.com/questions/31627223/how-to-drop-all-schemas-in-postgresql-with-sql-query) for a script that drops all schemas. Then put that script in a file `myscript.sql` and run it using `psql -d mydatabase -U myuser -p 5432 -h myhostname -f myscript.sql`. – Mushroomator Mar 23 '22 at 00:43
  • 1
    Yes, drop the database. – Laurenz Albe Mar 23 '22 at 03:31
  • I've se seen that its not as easy as dropping the database, because there are still connections alive, so postgres won't let me do it. About [the solution](https://stackoverflow.com/questions/31627223/how-to-drop-all-schemas-in-postgresql-with-sql-query) proposed in the link, that does what I want, I don't understand it. Could someone explain a bit please? Thanks in advance. – David Heredia Mar 23 '22 at 08:52

1 Answers1

1

For deleting all schemas you must use dynamic SQL. And schema names you can get from statistic system tables (example: information_schema). Example Query:

do
$body$
declare
    f_rec record;
begin
    
    for f_rec in 
        SELECT schema_name::text
        FROM information_schema.schemata
        where schema_name <> 'public'
    loop 
        execute 'DROP SCHEMA ' || f_rec.schema_name || ' CASCADE';
    end loop;   
    
end;
$body$
language 'plpgsql';
Ramin Faracov
  • 3,032
  • 1
  • 2
  • 8
  • Finally I used your code avoiding to delete some of system's important schemas: `do $body$ declare f_rec record; begin for f_rec in SELECT schema_name::text FROM information_schema.schemata where schema_name <> 'pg_toast' and schema_name <> 'pg_catalog' and schema_name <> 'public' and schema_name <> 'information_schema' loop execute 'DROP SCHEMA ' || f_rec.schema_name || ' CASCADE'; end loop; end; $body$ language 'plpgsql';` – David Heredia Mar 28 '22 at 07:25