PostgreSQL – How to Delete All Data in a Database

pg-dumppostgresqlpostgresql-9.5

I have created a fresh db dump from a production server with the --data-only and --column-inserts flags, so I only have a bunch of insert statements to insert data when performing a restore on a staging server.

pg_dump -h localhost -U adminuser --data-only --column-inserts maindb > maindb.sql

How do I delete all data in the staging server database first, before restoring the data from the production dump?

I want to delete all data only so I don't have to drop and create the database and all that stuff. I just want to remove data and insert new data that is all.

I don't have the option to drop and create the database for several reasons. I will have to remove all data and just insert only, so whatever it takes to find how to do this, am willing to go for it but need help obviously to start with.

I also need to automate this process. Will automate "dumping data from production db", then "deleting data on staging db", and then "restoring data to staging db". I just need help on the "deleting data on staging db" part.

I am running on PostgreSQL 9.5.2

Best Answer

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.

Related Question