current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 0 down vote favorite

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

share|improve this question
up vote 4 down vote accepted

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.

share|improve this answer
    
i see...does it do same thing as @ypercube mentioned above to use this command TRUNCATE table1, table2, ... <list of all tables>;? do they both do same thing? – babababa Nov 2 at 20:13
1  
@babababa: yes, my answer simply generates and runs that statement dynamically, so you don't have to type all table names and if you add a new table it will automatically be included. – a_horse_with_no_name Nov 2 at 20:22
    
nice just tried it and it works, @ypercube one works too...thanks a lot – babababa Nov 4 at 15:08
up vote 0 down vote

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.

share|improve this answer
    
will that --clean flag ONLY delete data and keep database and tables structures same but empty? – babababa Nov 2 at 17:32
    
It will issue a drop table before a create table. Any tables that exist in the dump file. I would HOPE that the the dump file contains the information to recreate the table exactly as it existed before (including FKeys, etc).. But it really depends on how you created the dump file. However, since you keep mentioning "staging", it sounds like what you're really looking for is a way to populate staging tables in a data warehouse with data from a production db. If that's your goal, a dump file is probably the wrong approach.. – Joishi Bodio Nov 2 at 17:48
    
that is not what am looking to do, i just want to delete data..database and tables structure will remain same and untouched...my question is pretty clear what i want to do, even from the title – babababa Nov 2 at 17:50
    
Then, sorry to say, your solution is going to be much more difficult. – Joishi Bodio Nov 2 at 17:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.