Automating Backup & Restore

On Wednesday 24 October 2007 10:39:23 smiley2211 wrote:

Does someone have a script that backups a database from one server and
restores it to another server???  I am NEW to Postgresql so I am starting
from scratch...

$ pg_dump -h server1 database | psql -h server2

jan

--
--------------------------------------------------------------
Jan de Visser                     jdevisser@digitalfairway.com

                Baruk Khazad! Khazad ai-menu!
--------------------------------------------------------------

Jan de Visser wrote:

On Wednesday 24 October 2007 10:39:23 smiley2211 wrote:

Does someone have a script that backups a database from one server
and restores it to another server??? I am NEW to Postgresql so I
am starting from scratch...

$ pg_dump -h server1 database | psql -h server2

jan

Further to this, Shane Ambler posted this response just yesterday in
response to a similar question:

If you want to copy your database to a development machine
occasionally, then if they have the same hardware and postgres
version then you should be able to copy the data files across while
the production server is stopped without problems. Some differences
between CPU and postgres version can stop this from working.

You could also do the dump/restore directly from one machine to
another if they are on the same network without having the dump file
to transfer across and import which will give similar results.

eg. instead of - pg_dumpall > mybackupfile psql -h
my.other.machine.address < mybackupfile

you can -

pg_dumpall | psql -h my.other.machine.address

If you have different cpu's (one Intel and one AMD64) or different
postgres versions then you will have to do the dump/restore even if
it is sent straight from one to another.

See this thread:
http://archives.postgresql.org/pgsql-general/2007-10/msg01178.php

brian

On 10/24/07, smiley2211 <smiley2211@yahoo.com> wrote:

Hello all,

Does someone have a script that backups a database from one server and
restores it to another server??? I am NEW to Postgresql so I am starting
from scratch...

so, in essence - what I want to do is (I CURRENTLY DO THIS MANUALLY):

Server1 (IS IT POSSIBLE TO DUMP DIRECTLY TO SERVER2??)

pg_dump > filename.dmp

scp filename.dmp to SERVER2

Server2
psql -d mydb -f filename.dmp
vacuum
reindex

server1$ pg_dump mydb | psql -h server2 -d mydb

Regards

M

THANKS ALL...
--
View this message in context: http://www.nabble.com/Automating-Backup---Restore-tf4684052.html#a13392360
Sent from the PostgreSQL - general mailing list archive at Nabble.com.