Duplication to External Server

[David Hofmann]

I'm setting up 2 database servers. The first is on our local network which
is our staging server. The second is an external server setup at my hosting
company. On a nightly bases I want to copy all the data from the local
Postgre database to the production server at hosting company overriding
whatever was there previously.

Does anyone have any suggestions on an easy was to do this ?

_______________ _______________ _______________ _______________ _____
Tired of slow downloads? Compare online deals from your local high-speed
providers now. https://broadband.msn.com


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Keith C. Perry]

Re: Duplication to External Server

Quoting David Hofmann <mor4321@hotmai l.com>:
[color=blue]
> I'm setting up 2 database servers. The first is on our local network which
> is our staging server. The second is an external server setup at my hosting
> company. On a nightly bases I want to copy all the data from the local
> Postgre database to the production server at hosting company overriding
> whatever was there previously.
>
> Does anyone have any suggestions on an easy was to do this ?
>
> _______________ _______________ _______________ _______________ _____
> Tired of slow downloads? Compare online deals from your local high-speed
> providers now. https://broadband.msn.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)
>[/color]

You should check out some of the replication software on gborg.postgresq l.org
but off the top of my head I would say that if you truely want to copy the data
up to the server you can use the psql program over an SSL connection. I'm not
quite sure if psql supports SSL natively but you can always tunnel the
connection with ssh if your ISP supports it. If you are using 7.4 you could
even use pg_dump with ssh in a similar manner.

--
Keith C. Perry, MS E.E.
Director of Networks & Applications
VCSN, Inc.

http://vcsn.com

_______________ _______________ ______
This email account is being host by:
VCSN, Inc : http://vcsn.com

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Richard Huxton]

Re: Duplication to External Server

On Friday 19 December 2003 05:33, Keith C. Perry wrote:[color=blue]
> Quoting David Hofmann <mor4321@hotmai l.com>:[color=green]
> > I'm setting up 2 database servers. The first is on our local network
> > which is our staging server. The second is an external server setup at my
> > hosting company. On a nightly bases I want to copy all the data from the
> > local Postgre database to the production server at hosting company
> > overriding whatever was there previously.[/color]
>
> You should check out some of the replication software on
> gborg.postgresq l.org but off the top of my head I would say that if you
> truely want to copy the data up to the server you can use the psql program
> over an SSL connection. I'm not quite sure if psql supports SSL natively
> but you can always tunnel the connection with ssh if your ISP supports it.
> If you are using 7.4 you could even use pg_dump with ssh in a similar
> manner.[/color]

For this particular case, I'd be tempted to pg_dump the local database, use
rsync over ssh to update that dump onto the hosted server, then pg_restore.

If you could stop the local copy of PG, in theory you could just move the
whole data directory, but I'd feel uncomfortable about that.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html