Обсуждение: How to remove the current database and populate the database with new data?

Hi,

I've some test data in a database and would like to delete that database and clean everything that is associated with
thatdatabase.  Then I'd like to populate the same database with different data. 
My plan is to:
(1) Remove the /usr/local/pgsql/data directory
(2) psql -e mydatabase -f /tmp/indumpfile.txt & > /tmp/outdumpfile.txt (/tmp/indumpfile.txt has all the sql statements
torestore the database) 
(3) Restart the postgres server

Not sure if I need to run the VACCUM command, because I know Postgres 8.3.8 has the auto-vacuum daemon on to perform
VACCUMswhen it's necessary.  Did I miss any other steps for cleaning up? 

Please advise.

Thanks
Mary

On Thu, 2010-06-03 at 16:05 -0700, Wang, Mary Y wrote:
> Hi,
>
> I've some test data in a database and would like to delete that database and clean everything that is associated with
thatdatabase.  Then I'd like to populate the same database with different data. 
> My plan is to:
> (1) Remove the /usr/local/pgsql/data directory
> (2) psql -e mydatabase -f /tmp/indumpfile.txt & > /tmp/outdumpfile.txt (/tmp/indumpfile.txt has all the sql
statementsto restore the database) 
> (3) Restart the postgres server
>
> Not sure if I need to run the VACCUM command, because I know Postgres 8.3.8 has the auto-vacuum daemon on to perform
VACCUMswhen it's necessary.  Did I miss any other steps for cleaning up? 

How about:

psql -U postgres template1 -c "drop database <foo>";
psql -U postgres <foo> < inputfile.txt
psql -U postgres <foo> -c "ANALYZE VERBOSE"

What you have above won't work anyway as you need to stop postgres,
initdb, recreate your users etc...

Joshua D. Drake


--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering

On Thursday 03 June 2010 4:05:14 pm Wang, Mary Y wrote:
> Hi,
>
> I've some test data in a database and would like to delete that database
> and clean everything that is associated with that database.  Then I'd like
> to populate the same database with different data. My plan is to:
> (1) Remove the /usr/local/pgsql/data directory
> (2) psql -e mydatabase -f /tmp/indumpfile.txt & > /tmp/outdumpfile.txt
> (/tmp/indumpfile.txt has all the sql statements to restore the database)
> (3) Restart the postgres server
>
> Not sure if I need to run the VACCUM command, because I know Postgres 8.3.8
> has the auto-vacuum daemon on to perform VACCUMs when it's necessary.  Did
> I miss any other steps for cleaning up?
>
> Please advise.
>
> Thanks
> Mary

Why not use DROP DATABASE? Removing the data directory removes the whole
Postgres cluster, possibly including the config files.

--
Adrian Klaver
adrian.klaver@gmail.com

I thought about DROP DATABASE, but wasn't sure if it would clean up EVERYTHING.
I had a bad experience early this year when I restored a database that was running on Postgres 7.x.x.  The database
crashedbadly, that I couldn't recover it.  It ended up that I had to restore it from a previous night's backup.  I
noticeda huge decrease in performance after the restore. I always have thought that there was something that hasn't
beencleaned up (Yes, I did run the VACUUM command).  I decided not to investigate it anymore, because I already had a
planto upgrade to 8.3.8 anyway. 

I assume most of you would just do the DROP DATABASE for the scenario that I described.   Is that correct?

Mary


-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@gmail.com]
Sent: Thursday, June 03, 2010 4:10 PM
To: pgsql-general@postgresql.org
Cc: Wang, Mary Y
Subject: Re: [GENERAL] How to remove the current database and populate the database with new data?

On Thursday 03 June 2010 4:05:14 pm Wang, Mary Y wrote:
> Hi,
>
> I've some test data in a database and would like to delete that
> database and clean everything that is associated with that database.
> Then I'd like to populate the same database with different data. My plan is to:
> (1) Remove the /usr/local/pgsql/data directory
> (2) psql -e mydatabase -f /tmp/indumpfile.txt & > /tmp/outdumpfile.txt
> (/tmp/indumpfile.txt has all the sql statements to restore the
> database)
> (3) Restart the postgres server
>
> Not sure if I need to run the VACCUM command, because I know Postgres
> 8.3.8 has the auto-vacuum daemon on to perform VACCUMs when it's
> necessary.  Did I miss any other steps for cleaning up?
>
> Please advise.
>
> Thanks
> Mary

Why not use DROP DATABASE? Removing the data directory removes the whole Postgres cluster, possibly including the
configfiles. 

--
Adrian Klaver
adrian.klaver@gmail.com

On 4/06/2010 7:26 AM, Wang, Mary Y wrote:
> I thought about DROP DATABASE, but wasn't sure if it would clean up EVERYTHING.

It won't remove your users and roles, or anything else that you see
reported in pg_dumpall --globals-only .

> I assume most of you would just do the DROP DATABASE for the scenario that I described.   Is that correct?

Yep. I essentially _never_ re-initdb, personally. For one thing, I often
have other databases in a cluster that I'd rather not lose, but it's
also generally unnecessary.

( I do frequently wish for the ability to create roles at the database
rather than cluster level, though, as it'd make cleaning the DB for
testing of schema creation scripts and the like considerably easier. Not
to mention backups. )

--
Craig Ringer

On Thu, 2010-06-03 at 16:05 -0700, Wang, Mary Y wrote:
> Hi,
>
> I've some test data in a database and would like to delete that database and clean everything that is associated with
thatdatabase.  Then I'd like to populate the same database with different data. 
> My plan is to:
> (1) Remove the /usr/local/pgsql/data directory
> (2) psql -e mydatabase -f /tmp/indumpfile.txt & > /tmp/outdumpfile.txt (/tmp/indumpfile.txt has all the sql
statementsto restore the database) 
> (3) Restart the postgres server
>
> Not sure if I need to run the VACCUM command, because I know Postgres 8.3.8 has the auto-vacuum daemon on to perform
VACCUMswhen it's necessary.  Did I miss any other steps for cleaning up? 

How about:

psql -U postgres template1 -c "drop database <foo>";
psql -U postgres <foo> < inputfile.txt
psql -U postgres <foo> -c "ANALYZE VERBOSE"

What you have above won't work anyway as you need to stop postgres,
initdb, recreate your users etc...

Joshua D. Drake


--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering