How to empty a SQL database?

Question

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

Answer 1

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.