Delete all data in Postgres database

You don't have to drop the database, it should be enough to drop all the objects in the database. This can be done using

drop owned by adminuser

If you then create the SQL dump including the create table statements (so without the --data-only option) everything should be fine.

You can also remove the --column-inserts then, which will make the import a lot faster.

---

However, if you do want to delete everything, you can do that with a little dynamic SQL:

do
$$
declare
  l_stmt text;
begin
  select 'truncate ' || string_agg(format('%I.%I', schemaname, tablename), ',')
    into l_stmt
  from pg_tables
  where schemaname in ('public');

  execute l_stmt;
end;
$$

This will truncate all tables in the schema public with a single statement which will also work even if there are many foreign key constraints connecting all tables. If your tables are spread over multiple schemas, you need to add them in the where condition.

pg_restore has a --clean flag (or possibly --create) which will auto delete data before running operations..

The Excellent Documentation should help you greatly...

Just to clarify, in case it's confusing:

Clean (drop) database objects before recreating them. (Unless --if-exists is used, this might generate some harmless error messages, if any objects were not present in the destination database.)

This will not drop the actual database .. only the tables/views/etc.

If, for some reason, dropping and recreating the tables is not acceptable, then you're going to have to put it more work to manually create a script that creates a data only dump from the source db, issues TRUNCATE or DELETE in the target database, and then loads the data dump. There's no quick/slick way to do this, as far as I'm aware.

SELECT 'TRUNCATE ' || input_table_name || ' CASCADE;' AS truncate_query FROM(SELECT table_schema || '.' || table_name AS input_table_name FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog', 'information_schema') AND table_schema NOT LIKE 'pg_toast%') AS information;  

The above query will generate truncate queries for all tables in the database.