replication
Are there any type of replication features in postgresql 7.0?
I would like it where two databases have the same structure, but say at
midnight every night Database 1 synchs up database 2.
Granted, I could always write code to do that, but it wouldn't be very
sophisticated. (If I coded it, would do something like find rows in table
1 which aren't in the second database, append them database two, same with
the second table, etc.)
Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
On Thu, 21 Sep 2000, Adam Lang wrote:
Are there any type of replication features in postgresql 7.0?
Not exist some standard solution for PG for DB replication ...
Maybe in a far future (via some WAL logs?).
Karel
Could this possibly be done using triggers? I'm new to
postgres, but I know on a project I was doing using oracle
the dba could setup triggers to run on the OnInsert() (not
sure what it's actually called in oracle...). Do maybe
on the "OnInsert" of table foo you could do:
Insert into foo@remotesite1 ....
Is this possible in postgres? I'm looking at using postgres
for the next version of my SW and if replication isn't in,
I'm gonna need something like this :).
btw, remotesite could be setup in what oracle referred to
as "tnsnames.ora". It was a file that had a list of
hosts, ports and the database name so that you wouldn't
have to know all that info to connect to an oracle database,
just what you named it, your username and your password.
Thanks,
--------------------------------------------------------
| Daryl Chance | I have made this letter longer then |
| Valuedata, LLC | usual because I lacked the time to |
| Memphis, TN | make it shorter. -- Blaise Pascal |
--------------------------------------------------------
----- Original Message -----
From: "Adam Lang" <aalang@rutgersinsurance.com>
To: "PGSQL General" <pgsql-general@postgresql.org>
Sent: Thursday, September 21, 2000 7:52 AM
Subject: [GENERAL] replication
Are there any type of replication features in postgresql 7.0?
I would like it where two databases have the same structure, but say at
midnight every night Database 1 synchs up database 2.Granted, I could always write code to do that, but it wouldn't be very
sophisticated. (If I coded it, would do something like find rows in
table
Show quoted text
1 which aren't in the second database, append them database two, same with
the second table, etc.)Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
Adam Lang wrote:
Are there any type of replication features in postgresql 7.0?
I would like it where two databases have the same structure, but say at
midnight every night Database 1 synchs up database 2.Granted, I could always write code to do that, but it wouldn't be very
sophisticated. (If I coded it, would do something like find rows in table
1 which aren't in the second database, append them database two, same with
the second table, etc.)
And you would also have to check which records have been modified and
replicate them.
You are not the first person to ask for this feature and it is on the
TODO list:
http://www.postgresql.org/docs/pgsql/doc/TODO.detail/replication
but it is categorized under "exotic features", so I don't know when
we'll see it implemented :(
But I think www.psql.com are working on replication right now.
Poul L. Christiansen
Show quoted text
Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
On Thu, 21 Sep 2000, Daryl Chance wrote:
Could this possibly be done using triggers? I'm new to
postgres, but I know on a project I was doing using oracle
the dba could setup triggers to run on the OnInsert() (not
sure what it's actually called in oracle...). Do maybe
on the "OnInsert" of table foo you could do:Insert into foo@remotesite1 ....
Is this possible in postgres? I'm looking at using postgres
for the next version of my SW and if replication isn't in,
I'm gonna need something like this :).
You could probably write a C trigger that would propogate
changes, except that there are still problems. What do you
do when you roll back the transaction? Currently, there
aren't triggers for transaction start and end. Triggers that
do stuff outside the database right now are a bad idea unless
you have some other mechanism to determine whether something
was really supposed to be done. It could be done, but isn't
trivial.
Well, if I end up needing to do something with it, I'll get back to the list
on ideas/solutions I encountered and see about potential pitfalls.
Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
To: "Daryl Chance" <dchance@valuedata.net>
Cc: <pgsql-general@postgresql.org>
Sent: Thursday, September 21, 2000 12:59 PM
Subject: Re: [GENERAL] replication
Show quoted text
On Thu, 21 Sep 2000, Daryl Chance wrote:
Could this possibly be done using triggers? I'm new to
postgres, but I know on a project I was doing using oracle
the dba could setup triggers to run on the OnInsert() (not
sure what it's actually called in oracle...). Do maybe
on the "OnInsert" of table foo you could do:Insert into foo@remotesite1 ....
Is this possible in postgres? I'm looking at using postgres
for the next version of my SW and if replication isn't in,
I'm gonna need something like this :).You could probably write a C trigger that would propogate
changes, except that there are still problems. What do you
do when you roll back the transaction? Currently, there
aren't triggers for transaction start and end. Triggers that
do stuff outside the database right now are a bad idea unless
you have some other mechanism to determine whether something
was really supposed to be done. It could be done, but isn't
trivial.
Well, this is the situation. A client has a database application currently
running on Access (which multiple people share over a network). They want
to make data accessible via the internet. So, what I was thinking was keep
the production database at their location, convert it to postgresql, and
make a replacement application for them to do their work. Then, host their
webserver remotely and have another database there. The main location has a
DSL connection. I figured then that once a day, three times a day
(whatever) the main database synchs up the remote one. (The remote database
will only be used by PHP to extract data, no modifications).
The point of this is that no matter what the main location will always be
able to get their work done while still being able to host their website
offsite.
If there was only a single database at either end, the whole setup is
susceptible to a telecomm link going down and cutting something off.
Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "Rob Hutton" <rhutton@istmanagement.com>
To: "Adam Lang" <aalang@rutgersinsurance.com>; "Stephan Szabo"
<sszabo@megazone23.bigpanda.com>; "Daryl Chance" <dchance@valuedata.net>
Cc: <pgsql-general@postgresql.org>
Sent: Thursday, September 21, 2000 3:19 PM
Subject: RE: [GENERAL] replication
I asked a similar question earlier in the week and got no response. If
there is transaction logging, then it is fairly easy to implement syncing,
even real time, the only question is to play back the log every x amount
of
time on the remote db. The tricky part is two fold.
First, you have to maintain a table of all of the remote DBs that are
syncing and the last two send and receive timestamps, and whether the last
was successful. If not, then at the next sync time, you have to replay
the
logs again back to the previous time that was successful. Then you have
to
have a process that cleans up all of the transactions that are previous to
the most recent successful sync.The second part is collision resolution. That is, what happens when an
edit is made to a DB at both ends in between the sync period. Most of the
time the later timestamp wins, but what happens if that effects business
rules, range limitations, etc. For instance, if b must be between a and
c,
and b and c are lowered on one end, and on the other b is raised above the
new setting for c, how is this resolved. You end up writing a rules
engine
Show quoted text
for this stuff...
Rob
-----Original Message-----
From: pgsql-general-owner@hub.org [mailto:pgsql-general-owner@hub.org]On
Behalf Of Adam Lang
Sent: Thursday, September 21, 2000 12:42 PM
To: Stephan Szabo; Daryl Chance
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] replicationWell, if I end up needing to do something with it, I'll get back
to the list
on ideas/solutions I encountered and see about potential pitfalls.Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
To: "Daryl Chance" <dchance@valuedata.net>
Cc: <pgsql-general@postgresql.org>
Sent: Thursday, September 21, 2000 12:59 PM
Subject: Re: [GENERAL] replicationOn Thu, 21 Sep 2000, Daryl Chance wrote:
Could this possibly be done using triggers? I'm new to
postgres, but I know on a project I was doing using oracle
the dba could setup triggers to run on the OnInsert() (not
sure what it's actually called in oracle...). Do maybe
on the "OnInsert" of table foo you could do:Insert into foo@remotesite1 ....
Is this possible in postgres? I'm looking at using postgres
for the next version of my SW and if replication isn't in,
I'm gonna need something like this :).You could probably write a C trigger that would propogate
changes, except that there are still problems. What do you
do when you roll back the transaction? Currently, there
aren't triggers for transaction start and end. Triggers that
do stuff outside the database right now are a bad idea unless
you have some other mechanism to determine whether something
was really supposed to be done. It could be done, but isn't
trivial.
Import Notes
Reference msg id not found: MBEIICFHGAEOEOLFOHDLIEDOCAAA.rhutton@istmanagement.com | Resolved by subject fallback
I asked a similar question earlier in the week and got no response. If
there is transaction logging, then it is fairly easy to implement syncing,
even real time, the only question is to play back the log every x amount of
time on the remote db. The tricky part is two fold.
First, you have to maintain a table of all of the remote DBs that are
syncing and the last two send and receive timestamps, and whether the last
was successful. If not, then at the next sync time, you have to replay the
logs again back to the previous time that was successful. Then you have to
have a process that cleans up all of the transactions that are previous to
the most recent successful sync.
The second part is collision resolution. That is, what happens when an
edit is made to a DB at both ends in between the sync period. Most of the
time the later timestamp wins, but what happens if that effects business
rules, range limitations, etc. For instance, if b must be between a and c,
and b and c are lowered on one end, and on the other b is raised above the
new setting for c, how is this resolved. You end up writing a rules engine
for this stuff...
Rob
Show quoted text
-----Original Message-----
From: pgsql-general-owner@hub.org [mailto:pgsql-general-owner@hub.org]On
Behalf Of Adam Lang
Sent: Thursday, September 21, 2000 12:42 PM
To: Stephan Szabo; Daryl Chance
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] replicationWell, if I end up needing to do something with it, I'll get back
to the list
on ideas/solutions I encountered and see about potential pitfalls.Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
To: "Daryl Chance" <dchance@valuedata.net>
Cc: <pgsql-general@postgresql.org>
Sent: Thursday, September 21, 2000 12:59 PM
Subject: Re: [GENERAL] replicationOn Thu, 21 Sep 2000, Daryl Chance wrote:
Could this possibly be done using triggers? I'm new to
postgres, but I know on a project I was doing using oracle
the dba could setup triggers to run on the OnInsert() (not
sure what it's actually called in oracle...). Do maybe
on the "OnInsert" of table foo you could do:Insert into foo@remotesite1 ....
Is this possible in postgres? I'm looking at using postgres
for the next version of my SW and if replication isn't in,
I'm gonna need something like this :).You could probably write a C trigger that would propogate
changes, except that there are still problems. What do you
do when you roll back the transaction? Currently, there
aren't triggers for transaction start and end. Triggers that
do stuff outside the database right now are a bad idea unless
you have some other mechanism to determine whether something
was really supposed to be done. It could be done, but isn't
trivial.
That is not the situation though. The reason I want to have to databases is
to have one at the site that actual production is going to be worked on, and
then a replicated database at a remote location where the webserver is.
As for the interface, they use Access forms, which I'll just write a new VB
app using ODBC to replace.
Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "Len Morgan" <len-morgan@crcom.net>
To: "Adam Lang" <aalang@rutgersinsurance.com>
Sent: Thursday, September 21, 2000 2:52 PM
Subject: Re: [GENERAL] replication
There is another approach that I have used: Convert all of the tables in
the
current Access system to Postgres tables and then use ODBC links in the
current program instead of internal tables. This way you only have one
database, no need to replicate and it's much more "live". If you give the
tables in Postgres the same names they have in access, you won't have to
change any of the forms/queries/reports, etc.Just my 2 cent's worth.
Len Morgan
-----Original Message-----
From: Adam Lang <aalang@rutgersinsurance.com>
Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Date: Thursday, September 21, 2000 1:43 PM
Subject: Re: [GENERAL] replicationWell, this is the situation. A client has a database application
currently
running on Access (which multiple people share over a network). They
want
to make data accessible via the internet. So, what I was thinking was
keep
the production database at their location, convert it to postgresql, and
make a replacement application for them to do their work. Then, host
their
webserver remotely and have another database there. The main location has
a
DSL connection. I figured then that once a day, three times a day
(whatever) the main database synchs up the remote one. (The remote
database
will only be used by PHP to extract data, no modifications).
The point of this is that no matter what the main location will always be
able to get their work done while still being able to host their website
offsite.If there was only a single database at either end, the whole setup is
susceptible to a telecomm link going down and cutting something off.Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "Rob Hutton" <rhutton@istmanagement.com>
To: "Adam Lang" <aalang@rutgersinsurance.com>; "Stephan Szabo"
<sszabo@megazone23.bigpanda.com>; "Daryl Chance" <dchance@valuedata.net>
Cc: <pgsql-general@postgresql.org>
Sent: Thursday, September 21, 2000 3:19 PM
Subject: RE: [GENERAL] replicationI asked a similar question earlier in the week and got no response.
If
there is transaction logging, then it is fairly easy to implement
syncing,
even real time, the only question is to play back the log every x
amount
of
time on the remote db. The tricky part is two fold.
First, you have to maintain a table of all of the remote DBs that are
syncing and the last two send and receive timestamps, and whether thelast
was successful. If not, then at the next sync time, you have to replay
the
logs again back to the previous time that was successful. Then you
have
to
have a process that cleans up all of the transactions that are previous
to
the most recent successful sync.
The second part is collision resolution. That is, what happens when
an
edit is made to a DB at both ends in between the sync period. Most of
the
time the later timestamp wins, but what happens if that effects
business
rules, range limitations, etc. For instance, if b must be between a
and
Show quoted text
c,
and b and c are lowered on one end, and on the other b is raised above
the
new setting for c, how is this resolved. You end up writing a rules
engine
for this stuff...
Rob
-----Original Message-----
From: pgsql-general-owner@hub.org[mailto:pgsql-general-owner@hub.org]On
Behalf Of Adam Lang
Sent: Thursday, September 21, 2000 12:42 PM
To: Stephan Szabo; Daryl Chance
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] replicationWell, if I end up needing to do something with it, I'll get back
to the list
on ideas/solutions I encountered and see about potential pitfalls.Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
To: "Daryl Chance" <dchance@valuedata.net>
Cc: <pgsql-general@postgresql.org>
Sent: Thursday, September 21, 2000 12:59 PM
Subject: Re: [GENERAL] replicationOn Thu, 21 Sep 2000, Daryl Chance wrote:
Could this possibly be done using triggers? I'm new to
postgres, but I know on a project I was doing using oracle
the dba could setup triggers to run on the OnInsert() (not
sure what it's actually called in oracle...). Do maybe
on the "OnInsert" of table foo you could do:Insert into foo@remotesite1 ....
Is this possible in postgres? I'm looking at using postgres
for the next version of my SW and if replication isn't in,
I'm gonna need something like this :).You could probably write a C trigger that would propogate
changes, except that there are still problems. What do you
do when you roll back the transaction? Currently, there
aren't triggers for transaction start and end. Triggers that
do stuff outside the database right now are a bad idea unless
you have some other mechanism to determine whether something
was really supposed to be done. It could be done, but isn't
trivial.
Import Notes
Reference msg id not found: 00b101c023fd$1c5367a0$0908a8c0@H233.bstx.cc | Resolved by subject fallback
You can continue to use the same Access app, just link the tables through
ODBC to the postgres sever (save you from reinventing the wheel)
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
On Thu, 21 Sep 2000, Adam Lang wrote:
Show quoted text
That is not the situation though. The reason I want to have to databases is
to have one at the site that actual production is going to be worked on, and
then a replicated database at a remote location where the webserver is.As for the interface, they use Access forms, which I'll just write a new VB
app using ODBC to replace.Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "Len Morgan" <len-morgan@crcom.net>
To: "Adam Lang" <aalang@rutgersinsurance.com>
Sent: Thursday, September 21, 2000 2:52 PM
Subject: Re: [GENERAL] replicationThere is another approach that I have used: Convert all of the tables in
the
current Access system to Postgres tables and then use ODBC links in the
current program instead of internal tables. This way you only have one
database, no need to replicate and it's much more "live". If you give the
tables in Postgres the same names they have in access, you won't have to
change any of the forms/queries/reports, etc.Just my 2 cent's worth.
Len Morgan
-----Original Message-----
From: Adam Lang <aalang@rutgersinsurance.com>
Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Date: Thursday, September 21, 2000 1:43 PM
Subject: Re: [GENERAL] replicationWell, this is the situation. A client has a database application
currently
running on Access (which multiple people share over a network). They
want
to make data accessible via the internet. So, what I was thinking was
keep
the production database at their location, convert it to postgresql, and
make a replacement application for them to do their work. Then, hosttheir
webserver remotely and have another database there. The main location has
a
DSL connection. I figured then that once a day, three times a day
(whatever) the main database synchs up the remote one. (The remotedatabase
will only be used by PHP to extract data, no modifications).
The point of this is that no matter what the main location will always be
able to get their work done while still being able to host their website
offsite.If there was only a single database at either end, the whole setup is
susceptible to a telecomm link going down and cutting something off.Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "Rob Hutton" <rhutton@istmanagement.com>
To: "Adam Lang" <aalang@rutgersinsurance.com>; "Stephan Szabo"
<sszabo@megazone23.bigpanda.com>; "Daryl Chance" <dchance@valuedata.net>
Cc: <pgsql-general@postgresql.org>
Sent: Thursday, September 21, 2000 3:19 PM
Subject: RE: [GENERAL] replicationI asked a similar question earlier in the week and got no response.
If
there is transaction logging, then it is fairly easy to implement
syncing,
even real time, the only question is to play back the log every x
amount
of
time on the remote db. The tricky part is two fold.
First, you have to maintain a table of all of the remote DBs that are
syncing and the last two send and receive timestamps, and whether thelast
was successful. If not, then at the next sync time, you have to replay
the
logs again back to the previous time that was successful. Then you
have
to
have a process that cleans up all of the transactions that are previous
to
the most recent successful sync.
The second part is collision resolution. That is, what happens when
an
edit is made to a DB at both ends in between the sync period. Most of
the
time the later timestamp wins, but what happens if that effects
business
rules, range limitations, etc. For instance, if b must be between a
and
c,
and b and c are lowered on one end, and on the other b is raised above
the
new setting for c, how is this resolved. You end up writing a rules
engine
for this stuff...
Rob
-----Original Message-----
From: pgsql-general-owner@hub.org[mailto:pgsql-general-owner@hub.org]On
Behalf Of Adam Lang
Sent: Thursday, September 21, 2000 12:42 PM
To: Stephan Szabo; Daryl Chance
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] replicationWell, if I end up needing to do something with it, I'll get back
to the list
on ideas/solutions I encountered and see about potential pitfalls.Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
To: "Daryl Chance" <dchance@valuedata.net>
Cc: <pgsql-general@postgresql.org>
Sent: Thursday, September 21, 2000 12:59 PM
Subject: Re: [GENERAL] replicationOn Thu, 21 Sep 2000, Daryl Chance wrote:
Could this possibly be done using triggers? I'm new to
postgres, but I know on a project I was doing using oracle
the dba could setup triggers to run on the OnInsert() (not
sure what it's actually called in oracle...). Do maybe
on the "OnInsert" of table foo you could do:Insert into foo@remotesite1 ....
Is this possible in postgres? I'm looking at using postgres
for the next version of my SW and if replication isn't in,
I'm gonna need something like this :).You could probably write a C trigger that would propogate
changes, except that there are still problems. What do you
do when you roll back the transaction? Currently, there
aren't triggers for transaction start and end. Triggers that
do stuff outside the database right now are a bad idea unless
you have some other mechanism to determine whether something
was really supposed to be done. It could be done, but isn't
trivial.
I'm not keeping the Access forms because I would like to move them to
something a little more robust. Also, if I'm going to continue support the
application, moving the code from Acces-VBA to VB is bit cleaner. Even for
people after me.
Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "Michael Fork" <mfork@toledolink.com>
To: "Adam Lang" <aalang@rutgersinsurance.com>
Cc: "PGSQL General" <pgsql-general@postgresql.org>
Sent: Thursday, September 21, 2000 4:19 PM
Subject: Re: [GENERAL] replication
You can continue to use the same Access app, just link the tables through
ODBC to the postgres sever (save you from reinventing the wheel)Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo OhioOn Thu, 21 Sep 2000, Adam Lang wrote:
That is not the situation though. The reason I want to have to
databases is
to have one at the site that actual production is going to be worked on,
and
then a replicated database at a remote location where the webserver is.
As for the interface, they use Access forms, which I'll just write a new
VB
app using ODBC to replace.
Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "Len Morgan" <len-morgan@crcom.net>
To: "Adam Lang" <aalang@rutgersinsurance.com>
Sent: Thursday, September 21, 2000 2:52 PM
Subject: Re: [GENERAL] replicationThere is another approach that I have used: Convert all of the tables
in
the
current Access system to Postgres tables and then use ODBC links in
the
current program instead of internal tables. This way you only have
one
database, no need to replicate and it's much more "live". If you give
the
tables in Postgres the same names they have in access, you won't have
to
change any of the forms/queries/reports, etc.
Just my 2 cent's worth.
Len Morgan
-----Original Message-----
From: Adam Lang <aalang@rutgersinsurance.com>
Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Date: Thursday, September 21, 2000 1:43 PM
Subject: Re: [GENERAL] replicationWell, this is the situation. A client has a database application
currently
running on Access (which multiple people share over a network). They
want
to make data accessible via the internet. So, what I was thinking
was
keep
the production database at their location, convert it to postgresql,
and
make a replacement application for them to do their work. Then, host
their
webserver remotely and have another database there. The main location
has
a
DSL connection. I figured then that once a day, three times a day
(whatever) the main database synchs up the remote one. (The remotedatabase
will only be used by PHP to extract data, no modifications).
The point of this is that no matter what the main location will
always be
able to get their work done while still being able to host their
website
offsite.
If there was only a single database at either end, the whole setup is
susceptible to a telecomm link going down and cutting something off.Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "Rob Hutton" <rhutton@istmanagement.com>
To: "Adam Lang" <aalang@rutgersinsurance.com>; "Stephan Szabo"
<sszabo@megazone23.bigpanda.com>; "Daryl Chance"
<dchance@valuedata.net>
Cc: <pgsql-general@postgresql.org>
Sent: Thursday, September 21, 2000 3:19 PM
Subject: RE: [GENERAL] replicationI asked a similar question earlier in the week and got no
response.
If
there is transaction logging, then it is fairly easy to implement
syncing,
even real time, the only question is to play back the log every x
amount
of
time on the remote db. The tricky part is two fold.
First, you have to maintain a table of all of the remote DBs that
are
syncing and the last two send and receive timestamps, and whether
the
last
was successful. If not, then at the next sync time, you have to
replay
the
logs again back to the previous time that was successful. Then you
have
to
have a process that cleans up all of the transactions that are
previous
to
the most recent successful sync.
The second part is collision resolution. That is, what happens
when
an
edit is made to a DB at both ends in between the sync period. Most
of
the
time the later timestamp wins, but what happens if that effects
business
rules, range limitations, etc. For instance, if b must be between
a
and
c,
and b and c are lowered on one end, and on the other b is raised
above
the
new setting for c, how is this resolved. You end up writing a
rules
engine
for this stuff...
Rob
-----Original Message-----
From: pgsql-general-owner@hub.org[mailto:pgsql-general-owner@hub.org]On
Behalf Of Adam Lang
Sent: Thursday, September 21, 2000 12:42 PM
To: Stephan Szabo; Daryl Chance
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] replicationWell, if I end up needing to do something with it, I'll get back
to the list
on ideas/solutions I encountered and see about potential
pitfalls.
Show quoted text
Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
To: "Daryl Chance" <dchance@valuedata.net>
Cc: <pgsql-general@postgresql.org>
Sent: Thursday, September 21, 2000 12:59 PM
Subject: Re: [GENERAL] replicationOn Thu, 21 Sep 2000, Daryl Chance wrote:
Could this possibly be done using triggers? I'm new to
postgres, but I know on a project I was doing using oracle
the dba could setup triggers to run on the OnInsert() (not
sure what it's actually called in oracle...). Do maybe
on the "OnInsert" of table foo you could do:Insert into foo@remotesite1 ....
Is this possible in postgres? I'm looking at using postgres
for the next version of my SW and if replication isn't in,
I'm gonna need something like this :).You could probably write a C trigger that would propogate
changes, except that there are still problems. What do you
do when you roll back the transaction? Currently, there
aren't triggers for transaction start and end. Triggers that
do stuff outside the database right now are a bad idea unless
you have some other mechanism to determine whether something
was really supposed to be done. It could be done, but isn't
trivial.
Adam Lang wrote:
Are there any type of replication features in postgresql 7.0?
I would like it where two databases have the same structure, but say at
midnight every night Database 1 synchs up database 2.Granted, I could always write code to do that, but it wouldn't be very
sophisticated. (If I coded it, would do something like find rows in table
1 which aren't in the second database, append them database two, same with
the second table, etc.)
I would suggest to consider the xmin values stored with each row in
database. This number does contain the transaction-number of the
last change to this tuple.
An replication should copy all tuples having larger xmin than
copied by the last replication. If each table has an primary key,
replication can decide wether the tuple is updated or inserted.
For deletes you should consider to use triggers copying the keys
of deleted tuples to an "delete-log-table". If an delete-transaction
is aborted, the entries to this table should be cancelled
automatically by the transaction control.
I'm not sure wether you can use the oid as an key for this purpose.
Is oid updatable to keep it in sync on both databases?
Elmar
On Fri, Sep 22, 2000 at 09:58:24AM +0200, Elmar Haneke wrote:
Adam Lang wrote:
Are there any type of replication features in postgresql 7.0?
I've been thinking that one way to emulate replication would be to run a
pgsql "shim" that would redirect queries to two back-ends
simultaneously, creating transactions for update commands, and aborting
when necessary. The shim would be a long-running gateway process, that
could check for data consistency when it started up. This is all off
the top of my head, so is this at all a reasonable idea?
I am running into a similar situation, where I want to set up two live
redundant sites, where nightly updates aren't an option.
--
Bruce Guenter <bruceg@em.ca> http://em.ca/~bruceg/
Bruce Guenter <bruceg@em.ca> writes:
I've been thinking that one way to emulate replication would be to run a
pgsql "shim" that would redirect queries to two back-ends
simultaneously, creating transactions for update commands, and aborting
when necessary. The shim would be a long-running gateway process, that
could check for data consistency when it started up. This is all off
the top of my head, so is this at all a reasonable idea?
Awhile back, someone reported that they were successfully using exactly
this idea. Check the PG list archives (sorry, I forget which list).
I'm not convinced that this scales real well to multiple concurrent
clients ... at the very least, you'd need a single gateway that
serializes all the requests. You might still have problems with the
two databases not executing requests in exactly the same order. Also
it'd be really dangerous to use OIDs as a way of identifying rows in
application queries. But with a cooperative application it could
probably be made to work.
regards, tom lane
On Fri, Sep 22, 2000 at 12:03:13PM -0400, Tom Lane wrote:
Bruce Guenter <bruceg@em.ca> writes:
I've been thinking that one way to emulate replication would be to run a
pgsql "shim" that would redirect queries to two back-ends
simultaneously, creating transactions for update commands, and aborting
when necessary. The shim would be a long-running gateway process, that
could check for data consistency when it started up. This is all off
the top of my head, so is this at all a reasonable idea?Awhile back, someone reported that they were successfully using exactly
this idea. Check the PG list archives (sorry, I forget which list).
Cool! I'll take a look.
I'm not convinced that this scales real well to multiple concurrent
clients ... at the very least, you'd need a single gateway that
serializes all the requests.
Or multiple gateways, one "near" each database server, that try to keep
some synchronization with each other.
You might still have problems with the
two databases not executing requests in exactly the same order.
Also
it'd be really dangerous to use OIDs as a way of identifying rows in
application queries.
Oh, definitely agreed. OIDs go out the window, unless both servers have
have identical queries delivered to them at all times. This is rather
difficult to guarantee.
--
Bruce Guenter <bruceg@em.ca> http://em.ca/~bruceg/