clean,backup and restore database

[Murugs]

Hello All
I have postgresql 7.1 version installed succesfully.
I have a application which uses a database called "test".
Now I need to wipe the contents of the database "test" completely and populate the database "test" with new data.
When I go to a cygwin window and do
psql test
test=#
what command should I give to clean the database completely.

also could some one explain how to backup and restore my database.

regards
MP

 

[Leland123]

Hi Murugs,

To get ride of a database named test you could:

DROP DATABASE test;


To get ride of a table namned test you could:


DROP TABLE test;


To delete all record in a table named test you could do something like:


DELETE FROM test WHERE somekey > -1;


Make sure your WHERE statement will catch all records in your table.


See the link below for info on backup and restore:

http://www.postgresql.org/idocs/index.php?backup.html

Leland F. Jackson, CPA
Software - Master (TM)

https://www.smvfp.com

Nothing Runs Like the Fox

 

[Murugs]

Hello Leland
Thanks for the answer.
Today I tried to do some testing with pg_dump and pg_restore. My application uses a database called "test" and I added some users so that the database gets populated.
Then I did
pg_dump test > data
and I got a text file data.
Now into my application I deleted all the users,so that If I do a restore I will be able to see all my users.
to restore I did
psql test < data.
When I open my application after a restore I am not able to see my users. Pls someone explain me whether my testing is incorrect or where did the data go?

regards
MP

 

[Murugs]

Also when I say createdb test where does the files in the database test will be located.
Can I navigate using windows explorer to locate the files and backup the same and do a restore in the event if anything fails.

regards
MP

 

[Leland123]

Hi Murugs,

try to create the database first:

CREATE DATABASE test

Then do the restore:

psql test < data


I have also been able to backup/restore a postgres database including all users, etc by using pipes. For example, if I had postgres running on a computer with IP of 192.168.1.5 and also had postgres running on a computer with IP of 192.168.1.7, I could create the database on the 192.168.1.7 server as follows:

pg_dump -h 192.168.1.5 test | psql -h 192.168.1.7 test

I would first need to create the database test on 192.168.1.7 before running the pipe.
Leland F. Jackson, CPA
Software - Master (TM)

https://www.smvfp.com

Nothing Runs Like the Fox