Damien Damien - 2 years ago 103
SQL Question

How to empty a SQL database?

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.



Answer Source

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.


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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download