pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedupdating remote database
4+ messages / 4 participants
[nested] [flat]
* updating remote database
@ 2004-02-26 02:54 Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-02-26 08:15 ` Re: updating remote database Richard Huxton <dev@archonet.com>
2004-02-26 09:51 ` Re: updating remote database Olivier Hubaut <olivier@scmbb.ulb.ac.be>
0 siblings, 2 replies; 4+ messages in thread
From: Kenneth Gonsalves @ 2004-02-26 02:54 UTC (permalink / raw)
To: pgsql-sql
i have a database on a local machine, and another on a remote machine. I have
a dialup connection between the two - so band width is a problem. What is the
most efficient way of updating the remote from the local? Does SQL or
postgres have simple commands for this?
--
regards
kg
--
http://www.ootygolfclub.org
poor man's tally: http://avsap.sourceforge.net
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: updating remote database
2004-02-26 02:54 updating remote database Kenneth Gonsalves <lawgon@thenilgiris.com>
@ 2004-02-26 08:15 ` Richard Huxton <dev@archonet.com>
2004-02-26 09:55 ` Re: updating remote database Achilleus Mantzios <achill@matrix.gatewaynet.com>
1 sibling, 1 reply; 4+ messages in thread
From: Richard Huxton @ 2004-02-26 08:15 UTC (permalink / raw)
To: Kenneth Gonsalves <lawgon@thenilgiris.com>; pgsql-sql
On Thursday 26 February 2004 02:54, Kenneth Gonsalves wrote:
> i have a database on a local machine, and another on a remote machine. I
> have a dialup connection between the two - so band width is a problem. What
> is the most efficient way of updating the remote from the local? Does SQL
> or postgres have simple commands for this?
Sounds like you want some form of (batched) asynchronous replication (as it's
called). If you've not already set that up though, that won't help here.
Assuming you don't have a complete list of all changes logged somewhere, you
might want to try:
1. pg_dump the tables you want to synchronise on the local machine (one per
file)
2. Do the same on the remote machine
3. Use rsync to update the remote dump based on the local one
4. Restore the updated dump on the remote machine.
Failing that, you might want to look into the replication options available -
you may be able to adapt contrib/dbmirror, or perhaps erserver/rservimp on
gborg.postgresql.org
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: updating remote database
2004-02-26 02:54 updating remote database Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-02-26 08:15 ` Re: updating remote database Richard Huxton <dev@archonet.com>
@ 2004-02-26 09:55 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 0 replies; 4+ messages in thread
From: Achilleus Mantzios @ 2004-02-26 09:55 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Kenneth Gonsalves <lawgon@thenilgiris.com>; pgsql-sql
O kyrios Richard Huxton egrapse stis Feb 26, 2004 :
> On Thursday 26 February 2004 02:54, Kenneth Gonsalves wrote:
> > i have a database on a local machine, and another on a remote machine. I
> > have a dialup connection between the two - so band width is a problem. What
> > is the most efficient way of updating the remote from the local? Does SQL
> > or postgres have simple commands for this?
>
You said "update", which i assume is different from dump/restore.
I have made substantial modifications to dbmirror, which now provides
Foreign Key dependency oriented, row grained, conditional, Asynchronous,
Lazy replication.
FK dependency oriented means that rows tables involved as parent in FK
dependencies are replicated only if needed by a child row.
Row grained means the decision of mirroring depends on the
value of the actual row, thus giving the potential to
selectively mirror rows to certain slaves.
Conditional means that a table is either
- not mirrored at all
- unconditionally mirrored to all slaves (you can have many slaves)
- conditionally mirrored based on the value of column.
Async means you can have your updates in .sql text files
(in xaction order), and then transfer them (possibly bzip2'ed) and
execute them to the client.
Also if you have tcp/ip you can call dbmirror in the traditional
way (online), leaving the job of compression to libpq.
Lazy means that you dont need to mirror anything in advance untill
its really needed.
Lets say you have a parent table, and you insert 1m rows.
Then a child table row that is to be mirrored is updated
to point to a row of the parent.
Then only this parent row will be mirrored to your client.
Now if a change is made on this very parent row, then
this change will be also mirrored to the client.
This way you will mirror only the minimal ammount of transactions
needed in order the 2 dbs to be in sync.
I warn you that i dont consider this work fully tested
(altho i think it will surely suit simple setups).
Also a planing phase is mandatory in order to have
the system running correctly.
There is an accounting mechanism that remembers who slave has
which row.
you can see some discussion here:
http://gborg.postgresql.org/pipermail/pgreplication-general/2003-December/001251.html
My version of dbmirror didnt make it to be an applied patch
mainly due to the different goals between the two.
However if you want the code or guidance just ask.
> Sounds like you want some form of (batched) asynchronous replication (as it's
> called). If you've not already set that up though, that won't help here.
>
> Assuming you don't have a complete list of all changes logged somewhere, you
> might want to try:
>
> 1. pg_dump the tables you want to synchronise on the local machine (one per
> file)
> 2. Do the same on the remote machine
> 3. Use rsync to update the remote dump based on the local one
> 4. Restore the updated dump on the remote machine.
>
> Failing that, you might want to look into the replication options available -
> you may be able to adapt contrib/dbmirror, or perhaps erserver/rservimp on
> gborg.postgresql.org
>
>
--
-Achilleus
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: updating remote database
2004-02-26 02:54 updating remote database Kenneth Gonsalves <lawgon@thenilgiris.com>
@ 2004-02-26 09:51 ` Olivier Hubaut <olivier@scmbb.ulb.ac.be>
1 sibling, 0 replies; 4+ messages in thread
From: Olivier Hubaut @ 2004-02-26 09:51 UTC (permalink / raw)
To: Kenneth Gonsalves <lawgon@thenilgiris.com>; +Cc: pgsql-sql
Kenneth Gonsalves wrote:
> i have a database on a local machine, and another on a remote machine. I have
> a dialup connection between the two - so band width is a problem. What is the
> most efficient way of updating the remote from the local? Does SQL or
> postgres have simple commands for this?
Is the remote database a clone of the local one? In this case, rsync
seems to me to be the faster method to do this, but you need to stop the
two databases before doing this.
--
Signature en cours de maintenance,
Veuillez patienter...
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-02-26 09:55 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-26 02:54 updating remote database Kenneth Gonsalves <lawgon@thenilgiris.com>
2004-02-26 08:15 ` Richard Huxton <dev@archonet.com>
2004-02-26 09:55 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-02-26 09:51 ` Olivier Hubaut <olivier@scmbb.ulb.ac.be>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox