19

I'm searching for a simple way to delete all data from a database and keep the structure (table, relationship, etc...). I using postgreSQL but I think, if there a command to do that, it's not specific to postgres.

Thanks,

Damien

Adam Matan
  • 128,757
  • 147
  • 397
  • 562
Damien
  • 583
  • 3
  • 7
  • 18
  • 1
    You should't dump db for this operation.This problem can be resolved more efficiently. http://stackoverflow.com/questions/2829158 – ijusti Jun 18 '14 at 08:00

5 Answers5

31

Dump the schema using pg_dump. drop the database, recreate it and load the schema.

Dump you database schema (the -s tag) to a file:

pg_dump -s -f db.dump DB-NAME

Delete the database:

dropdb DB-NAME

Recreate it:

createdb DB-NAME

Restore the schema only:

pg_restore db.dump > psql DB-NAME

This should work on PostgreSQL; Other DBMS might have their own tools for that. I do no know of any generic tool to do it.

EDIT:

Following comments, you might want to skip the dropdb command, and simply create another database with the dumped schema. If all went through well, you can drop the old database:

pg_dump -s -f db.dump DB-NAME
createdb DB-NEW-NAME
pg_restore db.dump > psql DB-NEW-NAME

At this point, you have the full database at DB-NAME, and an empty schema at DB-NEW-NAME. after you're sure everything is OK, use dropdb DB-NAME.

taco
  • 1,367
  • 17
  • 32
Adam Matan
  • 128,757
  • 147
  • 397
  • 562
  • 1
    An interesting option. I'd be wary of it, however. I guess I'm just inherently nervous about dropping the whole database, but that's just me. :) – ZombieSheep Jan 22 '10 at 14:12
  • No need to be wary. If the OP wants a way to truncate the entire database anyways, then this is a better way to do it. No logs for truncating, and much faster. – Timothy Jan 22 '10 at 14:15
  • Sad to realize there is no more simple way to do this, Thanks anyway – Damien Mar 21 '11 at 15:14
  • you better try to restore first and then dropdb, because now it says for me `input file does not appear to be a valid archive` – scythargon Jun 05 '14 at 09:22
  • The last command ("pg_restore db.dump > psql DB-NEW-NAME") doesn't work. – mYnDstrEAm Mar 29 '17 at 12:03
  • Recreate the database (createdb DB-NAME) and run ```psql DB-NAME < db.dump``` – Evers Jul 04 '17 at 09:09
6

You can do something like this:

export PGUSER=your_pg_user
export PGHOST=database.host
export PGPORT=port
export PGDATABASE=your_database

psql -qAtX -c "select 'TRUNCATE table ' || quote_ident(table_schema) || '.' || quote_ident(table_name) || ' CASCADE;' from information_schema.tables where table_type = 'BASE TABLE' and not table_schema ~ '^(information_schema|pg_.*)$'" | psql -qAtX

It will do what's necessary.

Of course these exports are not necessary, but they will make it simpler to run 2 copies of psql without having to givem them all standard -U, -d, and so on, switches.

One thing though - using TRUNCATE to do so, while faster than DELETE, has it's drowbacks - for example - it is not being replicated by Slony and any other replication system that works on triggers. Unless you are working on PostgreSQL 8.4, and your replication knows how to use triggers on TRUNCATE.

2

I'm not a Postgres guy, but one option would be to iterate through the tables and issue a Truncate command against each one. You'll have to take otable relationships into account, though - you will not be able to delete reference data before data that refers to it, for example.

ZombieSheep
  • 29,603
  • 12
  • 67
  • 114
1

In pgAdmin you can do:

  • Right-click database -> backup, select "Schema only"
  • Drop the database
  • Create a new database and name it like the former
  • Right-click the new database -> restore -> select the backup, select "Schema only"
mYnDstrEAm
  • 751
  • 2
  • 8
  • 26
-1

your can delete all records of your database without restriction of foreign keys by following three steps

  1. Take script of your Database
    1. Right Click on your database (your DB Name)
    2. click on task and then "Generate script"
    3. Specify location
  2. Delete your database base
  3. recreate a database with the same name and run you generated script

This way you can empty all of your database

syed irfan
  • 505
  • 4
  • 8
  • "right click on your database" refers to a specific SQL (GUI) client. You should mention which SQL client you are referring to. –  May 14 '16 at 12:12
  • Microsft Sql server @a_horse_with_no_name thanks for Pointing out – syed irfan May 14 '16 at 12:47
  • 2
    Well the question is for Postgres, not for SQL Server. –  May 14 '16 at 13:08