title: PostgreSQL reset tables to original state
body: <p>We have a large PostgreSQL dump with hundreds of tables that I can successfully import with pg_restore. We are developing a software that inserts into a lot of these tables (~100) and for every run we need to return these tables to their original state (that means to the content that was in the dump). Restoring the original dump again takes a lot of time and we just can't wait for half an hour before every debugging session. So I need a relatively fast way to revert these tables to the state they are in after restoring from the dump.</p>
  
  <p>I've tried using pg_restore with -L switch and selecting these tables but I get either a duplicate key error when using both --data-only and --clean or a "cannot drop table X because other objects depend on it" error when using only --clean. Issuing a SET CONSTRAINTS ALL DEFERRED command before pg_restore did not work either. Maybe I have the rows in the table list all wrong, right now it's</p>
  
  <pre><code>491; 1259 39623998 TABLE public some_table some_user
  8021; 0 0 COMMENT public TABLE some_table some_user
  8022; 0 0 ACL public some_table some_user
  </code></pre>
  
  <p>for every table and then </p>
  
  <pre><code>6700; 0 39624062 TABLE DATA public some_table postgres
  8419; 0 0 SEQUENCE SET public some_table_pk_id_seq some_user
  </code></pre>
  
  <p>for every table.</p>
  
  <p>We only insert data and don't update existing rows so deleting all rows above an index and resetting the sequences might work, but I really don't want to have to manually create these commands for all the hundred tables and I'm not even sure it would work even if I set cascade to delete other objects depending on the given row.</p>
  
  <p>Does anyone have any better idea how to handle this?</p>