Is there a neat way to clean out a database via SQL commands?

i.e. get rid of tables, sequences, integers, etc.

At present I'm using dropdb/createdb, but thats' far from ideal
and I think it's causing postgres to do more mork than it needs to...

--
'My life, and by extension everyone else's, is meaningless.'
-- Bender
Rasputin :: Jack of All Trades - Master of Nuns

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Clinging to sanity, rasputnik@hellooperator.net (Dick Davies) mumbled into her beard:
> Is there a neat way to clean out a database via SQL commands?
>
> i.e. get rid of tables, sequences, integers, etc.
>
> At present I'm using dropdb/createdb, but thats' far from ideal
> and I think it's causing postgres to do more mork than it needs to...

If you truly need for all of the objects to go away, dropping the
database seems like a reasonable way to do this. I'm not sure what
work you are imagining is "too much" or "unnecessary."

If you're regularly recreating a not-totally-empty database,
containing some set of "fresh" tables/sequences/views/such, then I'd
think you're doing the right thing, but need to take a further step...

If you're recreating a database that has some non-zero "initial
configuration," then what you might do is to set up a 'template'
database, let's call it "mytemplate" that contains that configuration.
Then you can do the following:

$ createdb --template=mytemplate mydatabase
CREATE DATABASE
$ do_some_work_with mydatabase
$ dropdb mydatabase
DROP DATABASE
$ createdb --template=mytemplate mydatabase
CREATE DATABASE
$ do_some_work_with mydatabase
$ dropdb mydatabase
DROP DATABASE
$ createdb --template=mytemplate mydatabase
CREATE DATABASE
$ do_some_work_with mydatabase
$ dropdb mydatabase
DROP DATABASE
$ createdb --template=mytemplate mydatabase
CREATE DATABASE
$ do_some_work_with mydatabase
$ dropdb mydatabase
DROP DATABASE
$ createdb --template=mytemplate mydatabase
CREATE DATABASE
$ do_some_work_with mydatabase
$ dropdb mydatabase
DROP DATABASE
--
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne> rate me
http://linuxdatabases.info/~cbbrowne/slony.html
"Wintel: A Wasteland of Useless Software - If the bazillions of
programs out there actually amount to something, why is everyone using
MICROS~1 Office, Word, Excel, PowerPoint, Access, ..."
-- cbbrowne@hex.net

Dick Davies <rasputnik@hellooperator.net> writes:
> Is there a neat way to clean out a database via SQL commands?
> i.e. get rid of tables, sequences, integers, etc.

> At present I'm using dropdb/createdb, but thats' far from ideal
> and I think it's causing postgres to do more mork than it needs to...

Well, if you put everything into one or a few schemas then dropping
and recreating those schemas would do it. I suspect though that this
is *not* faster than dropdb/createdb.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

* Tom Lane <tgl@sss.pgh.pa.us> [0232 16:32]:
> Dick Davies <rasputnik@hellooperator.net> writes:
> > Is there a neat way to clean out a database via SQL commands?
> > i.e. get rid of tables, sequences, integers, etc.
>
> > At present I'm using dropdb/createdb, but thats' far from ideal
> > and I think it's causing postgres to do more mork than it needs to...
>
> Well, if you put everything into one or a few schemas then dropping
> and recreating those schemas would do it. I suspect though that this
> is *not* faster than dropdb/createdb.

Thanks Tom.

It's not just the speed, it's the constant deletes and creations in
~pgsql/data - as I said the other day, this is recreating a test db from the
production one as part of unit tests, so this happens dozens of times a day...


--
'...and then we wrote scripts to write the configs for us, and using
these scripts, we made mistakes in a faster, more automated manner.'
-- A Gentle Introduction to Cricket, on MRTG configuration
Rasputin :: Jack of All Trades - Master of Nuns

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

* Christopher Browne <cbbrowne@acm.org> [0223 20:23]:
> Clinging to sanity, rasputnik@hellooperator.net (Dick Davies) mumbled into her beard:
> > Is there a neat way to clean out a database via SQL commands?
> >
> > i.e. get rid of tables, sequences, integers, etc.
> >
> > At present I'm using dropdb/createdb, but thats' far from ideal
> > and I think it's causing postgres to do more mork than it needs to...
>
> If you truly need for all of the objects to go away, dropping the
> database seems like a reasonable way to do this. I'm not sure what
> work you are imagining is "too much" or "unnecessary."

It just seems a bit extreme, but the alternatives are all a bit gnarly,
so think I'll live with it....

Thanks for all the suggestions, though, everybody.

--
'The old 'give em a Linux box and they think they're Jean-Luc Picard' syndrome.'
-- Pete Bentley
Rasputin :: Jack of All Trades - Master of Nuns

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster