Thread: updating remote database
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
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
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...
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
Hi, is there a way to encode a bytea in such a way that the resulting text stream be readily available (\\ escaped for unprintable chars) for usage in an insert statement? None of base64,hex,escape options in encode() seem to produce anything close. This is meant to be used with generating insert statements in xml files for remote processing. I can always (in java) ResultSet.getString(bytea_col_idx), get the result (ala psql) with one "\" trimmed out, and process the output (by adding an extra "\" where needed) from there, but i am not sure if the jdbc driver will be allowing this ... convinience in the future. Thanx. -- -Achilleus
Achilleus Mantzios wrote:
> is there a way to encode a bytea in such a way that the resulting
> text stream be readily available (\\ escaped for unprintable chars) for
> usage in an insert statement?
>
> None of base64,hex,escape options in encode() seem to produce
> anything close.
>
> This is meant to be used with generating insert statements in xml files
> for remote processing.
Is this what you need?
create table t(f bytea);
insert into b values ('a\\003\\000\\001b');
create or replace function bytea2text(bytea) returns text as '
begin return $1;
end;
' language plpgsql;
regression=# select 'insert into t values(' ||
quote_literal(bytea2text(f)) || ');' from t; ?column?
-------------------------------------------- insert into t values('a\\003\\000\\001b');
(1 row)
regression=# insert into t values('a\\003\\000\\001b');
INSERT 292656 1
HTH,
Joe
O kyrios Joe Conway egrapse stis Mar 3, 2004 :
> Achilleus Mantzios wrote:
>
> > is there a way to encode a bytea in such a way that the resulting
> > text stream be readily available (\\ escaped for unprintable chars) for
> > usage in an insert statement?
> >
> > None of base64,hex,escape options in encode() seem to produce
> > anything close.
> >
> > This is meant to be used with generating insert statements in xml files
> > for remote processing.
>
> Is this what you need?
Yes thanx. ( i always forget about the casting function trick)
>
> create table t(f bytea);
> insert into b values ('a\\003\\000\\001b');
>
> create or replace function bytea2text(bytea) returns text as '
> begin
> return $1;
> end;
> ' language plpgsql;
>
> regression=# select 'insert into t values(' ||
> quote_literal(bytea2text(f)) || ');' from t;
> ?column?
> --------------------------------------------
> insert into t values('a\\003\\000\\001b');
> (1 row)
>
> regression=# insert into t values('a\\003\\000\\001b');
> INSERT 292656 1
>
> HTH,
>
> Joe
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
-Achilleus