On Friday 19 December 2003 05:33, Keith C. Perry wrote:
> Quoting David Hofmann <mor4321@hotmail.com>:
> > 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.
>
> You should check out some of the replication software on
> gborg.postgresql.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.
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