Thread: [GENERAL] Emptying a database.

[GENERAL] Emptying a database.

From

Stuart Rison

Date:

08 October 1998, 14:02:40

Dear All,

I'm currently doing some database design work and find myself entering
loads of test records into my database tables.

Occasionally I want to 'flush' out the database (i.e. keep all tables,
views, etc. but delete all records).  Currently I have to pg_dump the
database and then use a text editor to cut out all the 'copy' statements
out and recreate a 'new' database.

Is there a way of flushing the content of all tables?

Thanks for any help you may provide.

Cheers,

Stuart.


+-------------------------+--------------------------------------+
| Stuart Rison            | Ludwig Institute for Cancer Research |
+-------------------------+ 91 Riding House Street               |
| Tel. (0171) 878 4041    | London, W1P 8BT, UNITED KINGDOM.     |
| Fax. (0171) 878 4040    | stuart@ludwig.ucl.ac.uk              |
+-------------------------+--------------------------------------+

Re: [GENERAL] Emptying a database.

From

Jeremiah Davis

Date:

08 October 1998, 14:09:12

you just want to dump all the data?  just use delete then..

DELETE FROM tablename;

Deletes all the data in the table....



On Thu, 8 Oct 1998, Stuart Rison wrote:

> Dear All,
>
> I'm currently doing some database design work and find myself entering
> loads of test records into my database tables.
>
> Occasionally I want to 'flush' out the database (i.e. keep all tables,
> views, etc. but delete all records).  Currently I have to pg_dump the
> database and then use a text editor to cut out all the 'copy' statements
> out and recreate a 'new' database.
>
> Is there a way of flushing the content of all tables?
>
> Thanks for any help you may provide.
>
> Cheers,
>
> Stuart.
>
>
> +-------------------------+--------------------------------------+
> | Stuart Rison            | Ludwig Institute for Cancer Research |
> +-------------------------+ 91 Riding House Street               |
> | Tel. (0171) 878 4041    | London, W1P 8BT, UNITED KINGDOM.     |
> | Fax. (0171) 878 4040    | stuart@ludwig.ucl.ac.uk              |
> +-------------------------+--------------------------------------+
>
>
>
>

Re: [GENERAL] Emptying a database.

From

Stuart Rison

Date:

08 October 1998, 14:52:31

>you just want to dump all the data?  just use delete then..
>
>DELETE FROM tablename;
>
>Deletes all the data in the table....
>

Yes (thanks ;) ), but I want to do so for ALL my tables within a db without
having to type in the command for each table (I mean currently I have 20
tables but I'm likely to have more).

The way around it I've thought of is to write a delete for each table in a
file and then just run them with \i.

I just wondered if there was a pg command to do so (a 'flush' as it were).

>
>
>On Thu, 8 Oct 1998, Stuart Rison wrote:
>
>> Dear All,
>>
>> I'm currently doing some database design work and find myself entering
>> loads of test records into my database tables.
>>
>> Occasionally I want to 'flush' out the database (i.e. keep all tables,
>> views, etc. but delete all records).  Currently I have to pg_dump the
>> database and then use a text editor to cut out all the 'copy' statements
>> out and recreate a 'new' database.
>>
>> Is there a way of flushing the content of all tables?
>>
>> Thanks for any help you may provide.
>>
>> Cheers,
>>
>> Stuart.
>>

+-------------------------+--------------------------------------+
| Stuart Rison            | Ludwig Institute for Cancer Research |
+-------------------------+ 91 Riding House Street               |
| Tel. (0171) 878 4041    | London, W1P 8BT, UNITED KINGDOM.     |
| Fax. (0171) 878 4040    | stuart@ludwig.ucl.ac.uk              |
+-------------------------+--------------------------------------+

RE: [GENERAL] Emptying a database.

From

"Jackson, DeJuan"

Date:

08 October 1998, 15:54:54

> >you just want to dump all the data?  just use delete then..
> >
> >DELETE FROM tablename;
> >
> >Deletes all the data in the table....
> >
>
> Yes (thanks ;) ), but I want to do so for ALL my tables within a db
> without
> having to type in the command for each table (I mean currently I have
> 20
> tables but I'm likely to have more).
>
> The way around it I've thought of is to write a delete for each table
> in a
> file and then just run them with \i.
>
> I just wondered if there was a pg command to do so (a 'flush' as it
> were).
>
There's no pg command to do it that I've ever seen, but you could easily
write a bash script to do it.  Just query pg_class for all non-system
tables then execute a loop of deletes for each table_name.  You could
even set up a way to tell your script not to delete the data in certain
tables.
(I would suggest a table to join to in the pg_class select that includes
the table which contains the tables not to delete as a table not to be
deleted. %^P)

Oh well, hope this helps.
        -DEJ

> >
> >
> >On Thu, 8 Oct 1998, Stuart Rison wrote:
> >
> >> Dear All,
> >>
> >> I'm currently doing some database design work and find myself
> entering
> >> loads of test records into my database tables.
> >>
> >> Occasionally I want to 'flush' out the database (i.e. keep all
> tables,
> >> views, etc. but delete all records).  Currently I have to pg_dump
> the
> >> database and then use a text editor to cut out all the 'copy'
> statements
> >> out and recreate a 'new' database.
> >>
> >> Is there a way of flushing the content of all tables?
> >>
> >> Thanks for any help you may provide.
> >>
> >> Cheers,
> >>
> >> Stuart.
> >>
>
> +-------------------------+--------------------------------------+
> | Stuart Rison            | Ludwig Institute for Cancer Research |
> +-------------------------+ 91 Riding House Street               |
> | Tel. (0171) 878 4041    | London, W1P 8BT, UNITED KINGDOM.     |
> | Fax. (0171) 878 4040    | stuart@ludwig.ucl.ac.uk              |
> +-------------------------+--------------------------------------+
>
>

Re: [GENERAL] Emptying a database.

From

Karl Auer

Date:

09 October 1998, 05:29:11

Hi there.

Am 08-Oct-98 schrieb Stuart Rison:
>>you just want to dump all the data?  just use delete then..
>>
>>DELETE FROM tablename;
>>
>>Deletes all the data in the table....
>>
>
> Yes (thanks ;) ), but I want to do so for ALL my tables within a db without
> having to type in the command for each table (I mean currently I have 20
> tables but I'm likely to have more).
>
> The way around it I've thought of is to write a delete for each table in a
> file and then just run them with \i.

Or duplicate the database, delete everything from the duplicate (only need to
do this once, or whenever you add/delete tables from your design), and simply
copy the duplicate over the original whenever you want to clear it.

Regards, K.

---
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Karl Auer (auer@kom.id.ethz.ch)              Geschaeft/work     +41-1-6327531
Kommunikation, ETHZ RZ                          Privat/home     +41-1-4517941
Clausiusstrasse 59                                      Fax     +41-1-6321225
CH-8092 ZUERICH Switzerland

Re: [GENERAL] Emptying a database.

From

Herouth Maoz

Date:

13 October 1998, 12:39:41

At 8:28 +0200 on 9/10/98, Karl Auer wrote:

> Or duplicate the database, delete everything from the duplicate (only need to
> do this once, or whenever you add/delete tables from your design), and simply
> copy the duplicate over the original whenever you want to clear it.

"Prometheus", the name of the mythological protector of mankind, means
"Forethought".

I always believe in creating a "database creation script" in advance, which
deletes the old tables, sequences, functions, and whatnot, and creates them
anew.

It overcomes the problem with pg_dump, which doesn't dump or restore
certain things like views etc, or which dumps things in an order which
doesn't work for everybody.

I also create a backup script which dumps the contents (only) of the
relevant tables, and a restore script which restores the contents.

In this way, when I have to change something in the structure of the
database, I add it to the "creation" script, dump the contents, run the
creation script, and restore them with the "restore" script, into the
modified database.

If the change involved a change in a table structure (added field, changed
its type, dropped a field), you have to change the dumped data before
restoring, which is easily done either by search-and-replace in you
favourite editor (worked for me in all cases until now), or using a perl
script. Since it is a consistent change most of the time (probably just
addind a "\t\\N" after some column), it's very easily done.

If the change was just a change of a function, a default value, a
constraint, an index, etc. - you are free of changing your data. Just dump,
re-create, and restore. Don't forget to dump and restore sequence values as
well.

Running such a "creation" script on its own is bound to leave you with
empty tables, all ready in the correct structure. I find this is the
preferable way to "flush" a database. I also find that when I'm done
developing an application, the resulting "creation" script will be the best
basis for the new production database.

The "creation" script also has the benefit of being full of comments which
explain what is hapenning, what the fields are, etc.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma

Re: [GENERAL] Emptying a database.

From

Herouth Maoz

Date:

13 October 1998, 13:31:13

At 15:49 +0200 on 13/10/98, Andy Lewis wrote:


> You wouldn't want to share your backup and restore scripts would you?

Well, they are not that brilliant. Their main merit is that the names of
all the tables and sequences are gathered together at the top of the file
so it's easy to add and drop tables and sequences, or adapt the script to
another database. The tables are dumped to separate files, named
<tablename>.dmp, located in the current working directory.

Run the script with the name of the database as command-line argument.

Backup:
=======

#!/bin/csh
#
set tables=(auth cat_link categories clct_cat collections links priv
session session_reserve)
set seqs=(category_no clct_no link_no sess_no)
#
if ( $#argv != 1 ) then
        echo "usage: $0 dbname"
        exit 1
endif
#
# dump tables
#
foreach table ( $tables )
        echo "Now dumping table: $table"
        pg_dump -f $table.dmp -a -t $table $1
end
#
# dump sequences
#
foreach seq ( $seqs )
        echo "Now dumping sequence: $seq"
        echo "DROP SEQUENCE $seq;" > $seq.dmp
        pg_dump -a -t $seq $1 | sed '/\\connect/d' >> $seq.dmp
end


Restore:
========

#!/bin/csh
#
set tables=(auth cat_link categories clct_cat collections links priv
session session_reserve)
set seqs=(category_no clct_no link_no sess_no)
#
if ( $#argv != 1 ) then
        echo "usage: $0 dbname"
        exit 1
endif
#
# restore tables
#
foreach table ( $tables )
        echo "Now restoring $table"
        psql $1 < $table.dmp
end
#
# restore sequences
#
foreach seq ( $seqs )
        echo "Now restoring sequence $seq"
        psql $1 < $seq.dmp ;
end

(Sorry, the first shell languague I learned is csh, so that's what I use).

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma