Thread: EMPTYING A DATABASE
I am developing a project in which, of course, information is fed to the DB for testing purposes and sometimes I need to get the DB clean, without any record.
What would be the best practice to delete all of the records and start testing with a blank DB?
Should I issue a DELETE command for every table? If this is the case then I need to take care of referential integrity dependency because I cannot delete parent records before child records.
With respect,
Jorge Maldonado
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > I am developing a project in which, of course, information is fed to the DB > for testing purposes and sometimes I need to get the DB clean, without any > record. > What would be the best practice to delete all of the records and start > testing with a blank DB? Your best bet is to recreate the database. Start with an database that has all the tables already emptied out, then make a copy of it. Then drop and recreate your database at will using the copy as a template. So, if your datbase was named "alpha", you would do (one time only): (truncate all tables, get the alpha database in a clean state) CREATE DATABASE alpha_template TEMPLATE alpha; Then, every time you want to create a clean copy, do: DROP DATABASE alpha; CREATE DATABASE alpha TEMPLATE alpha_template; - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201004291214 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAkvZsRwACgkQvJuQZxSWSsifxQCgro7N0RTnNk5wla+GZg2J5fji ifAAoMxzOZwnkeYSi4BHhkXFGru1AMrL =/yIr -----END PGP SIGNATURE-----
"Greg Sabino Mullane" <greg@turnstep.com> writes:
>> I am developing a project in which, of course, information is fed to the DB
>> for testing purposes and sometimes I need to get the DB clean, without any
>> record.
>> What would be the best practice to delete all of the records and start
>> testing with a blank DB?
> Your best bet is to recreate the database. Start with an database that has all
> the tables already emptied out, then make a copy of it. Then drop and
> recreate your database at will using the copy as a template.
That's only going to be convenient if the OP periodically wants to
return to exactly the same database state. If he's doing database
development, it's probably going to be more like he periodically
changes the schema and then wants to start over. I suppose he could
try to remember to apply his schema changes in the template database
and then clone it, but that seems pretty error-prone.
My thought would be to make use of multi-table TRUNCATE, assuming
he's using a PG version new enough to have that. Keep a script
around with a TRUNCATE that lists all of the related tables in one
command, and just run the script to reset.
regards, tom lane
> From: "Tom Lane": > That's only going to be convenient if the OP periodically wants to > return to exactly the same database state. If he's doing database > development, it's probably going to be more like he periodically > changes the schema and then wants to start over. I suppose he could > try to remember to apply his schema changes in the template database > and then clone it, but that seems pretty error-prone. > > My thought would be to make use of multi-table TRUNCATE, assuming > he's using a PG version new enough to have that. Keep a script > around with a TRUNCATE that lists all of the related tables in one > command, and just run the script to reset. Wouldn't using pg_dump with the schema only option accomplish this? George
"George Weaver" <gweaver@shaw.ca> writes:
> From: "Tom Lane":
> My thought would be to make use of multi-table TRUNCATE, assuming
> he's using a PG version new enough to have that. Keep a script
> around with a TRUNCATE that lists all of the related tables in one
> command, and just run the script to reset.
> Wouldn't using pg_dump with the schema only option accomplish this?
Sure. I assume the OP already thought of that, and was curious
about alternatives. (One possible reason to prefer the TRUNCATE
approach though: you don't necessarily have to zap *every* table
in the database.)
regards, tom lane