Thread: dbmirror
Hi,
Does anybody know if the slave database(s) in the dbmirror replication model can be queried (via SQL SELECT statement) by a client program as if it is quering the master database (I understand that the slave database may not be a 100% mirror of the master at any given moment but that is good enough) ? We are exploring the possibility of a multi-server architecture where the master postgres database would handle all the data update maintenance workload whereas the slave(s) would handle all the query workload.
Any suggestions would be much appreciated.
TIA.
Fred
Fred --
Yes, the slave database(s) can be safely used in a R/O mode, given the issue you raise of not necessarily having an up-to-date copy of the data in the master. We haven't ever run enough queries against a slave to reveal any lurking contention issues, if indeed there are any, relating to the inserts/updates being done by the dbmirror perl code (such a momentary locks), so this may need some testing under stress.
HTH,
Greg Williamson
DBA
GlobeXplorer LLC
-----Original Message-----
From: Fred Fung [mailto:fred.fung@versaterm.com]
Sent: Wednesday, May 12, 2004 8:27 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] dbmirrorHi,Does anybody know if the slave database(s) in the dbmirror replication model can be queried (via SQL SELECT statement) by a client program as if it is quering the master database (I understand that the slave database may not be a 100% mirror of the master at any given moment but that is good enough) ? We are exploring the possibility of a multi-server architecture where the master postgres database would handle all the data update maintenance workload whereas the slave(s) would handle all the query workload.Any suggestions would be much appreciated.TIA.Fred
On Wed, May 12, 2004 at 05:53:05PM -0700, Gregory S. Williamson wrote: > Fred -- > > Yes, the slave database(s) can be safely used in a R/O mode, Does it also block write transactions in those slaves? The ability for clients to write into the slave replicated tables is a problem, because it makes promoting a slave node somewhat risky. Slony-I has a trick to solve this problem, BTW. A -- Andrew Sullivan | ajs@crankycanuck.ca
Does dbmirror do that? No, it does not. It also doesn't support promoting a slave database to a master; that has to be done manually, so I wouldn't consider that too big a problem. Worse in my opinion is that sequences don't get updated... so a slave that tries to do an insert on a replicated table (for example, when it gets manually promoted to master) will find the sequence not where the master left it, but where it was loaded. Every sequence has to be manually updated before the database is usable. dbmirror was never intended to be anything but a poor man's replication... and it worked remarkably well for that purpose. Now it's time to look forward to Slony-I :) Greg Andrew Sullivan wrote: > On Wed, May 12, 2004 at 05:53:05PM -0700, Gregory S. Williamson wrote: > >>Fred -- >> >>Yes, the slave database(s) can be safely used in a R/O mode, > > > Does it also block write transactions in those slaves? The ability > for clients to write into the slave replicated tables is a problem, > because it makes promoting a slave node somewhat risky. > > Slony-I has a trick to solve this problem, BTW. > > A >
Thanks for all your comments. Is it really true that the sequences in the tables in the Slaves will not be in sync with those in the Master ? If so then this is a show-stopper factor for us to consider using dbmirror, as we use sequences as a unique key in many of the tables in our application. What is Slony-I ? Is it a commerical software ? Is there a website I can read more about it ? Thanks. Fred ----- Original Message ----- From: "Gregory Wood" <gwood@ewebengine.com> To: "Andrew Sullivan" <ajs@crankycanuck.ca> Cc: <pgsql-general@postgresql.org> Sent: Thursday, May 13, 2004 4:10 PM Subject: Re: [GENERAL] dbmirror > Does dbmirror do that? No, it does not. It also doesn't support > promoting a slave database to a master; that has to be done manually, so > I wouldn't consider that too big a problem. > > Worse in my opinion is that sequences don't get updated... so a slave > that tries to do an insert on a replicated table (for example, when it > gets manually promoted to master) will find the sequence not where the > master left it, but where it was loaded. Every sequence has to be > manually updated before the database is usable. > > dbmirror was never intended to be anything but a poor man's > replication... and it worked remarkably well for that purpose. Now it's > time to look forward to Slony-I :) > > Greg > > Andrew Sullivan wrote: > > On Wed, May 12, 2004 at 05:53:05PM -0700, Gregory S. Williamson wrote: > > > >>Fred -- > >> > >>Yes, the slave database(s) can be safely used in a R/O mode, > > > > > > Does it also block write transactions in those slaves? The ability > > for clients to write into the slave replicated tables is a problem, > > because it makes promoting a slave node somewhat risky. > > > > Slony-I has a trick to solve this problem, BTW. > > > > A > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
I recall recently seeing something in the CVS logs for dbmirror about the sequence replication capability having been added, so you might check on that. On Friday May 14 2004 7:11, Fred Fung wrote: > Thanks for all your comments. > > Is it really true that the sequences in the tables in the Slaves will not > be in sync with those in the Master ? If so then this is a show-stopper > factor for us to consider using dbmirror, as we use sequences as a unique > key in many of the tables in our application. > > What is Slony-I ? Is it a commerical software ? Is there a website I can > read more about it ? > > > Thanks. > > > Fred > > > ----- Original Message ----- > From: "Gregory Wood" <gwood@ewebengine.com> > To: "Andrew Sullivan" <ajs@crankycanuck.ca> > Cc: <pgsql-general@postgresql.org> > Sent: Thursday, May 13, 2004 4:10 PM > Subject: Re: [GENERAL] dbmirror > > > Does dbmirror do that? No, it does not. It also doesn't support > > promoting a slave database to a master; that has to be done manually, > > so I wouldn't consider that too big a problem. > > > > Worse in my opinion is that sequences don't get updated... so a slave > > that tries to do an insert on a replicated table (for example, when it > > gets manually promoted to master) will find the sequence not where the > > master left it, but where it was loaded. Every sequence has to be > > manually updated before the database is usable. > > > > dbmirror was never intended to be anything but a poor man's > > replication... and it worked remarkably well for that purpose. Now it's > > time to look forward to Slony-I :) > > > > Greg > > > > Andrew Sullivan wrote: > > > On Wed, May 12, 2004 at 05:53:05PM -0700, Gregory S. Williamson wrote: > > >>Fred -- > > >> > > >>Yes, the slave database(s) can be safely used in a R/O mode, > > > > > > Does it also block write transactions in those slaves? The ability > > > for clients to write into the slave replicated tables is a problem, > > > because it makes promoting a slave node somewhat risky. > > > > > > Slony-I has a trick to solve this problem, BTW. > > > > > > A > > > > ---------------------------(end of > > broadcast)--------------------------- TIP 3: if posting/reading through > > Usenet, please send an appropriate subscribe-nomail command to > > majordomo@postgresql.org so that your message can get through to the > > mailing list cleanly > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
On May 14, 2004, at 9:11 AM, Fred Fung wrote: > Thanks for all your comments. > > Is it really true that the sequences in the tables in the Slaves will > not be > in sync with those in the Master ? If so then this is a show-stopper > factor > for us to consider using dbmirror, as we use sequences as a unique key > in > many of the tables in our application. > > What is Slony-I ? Is it a commerical software ? Is there a website I > can > read more about it ? > Slony-I is an async replication system. It is pre-beta, but already it works very well. I'm using it in production with 5 slaves! http://gborg.postgresql.org/projects/slony1/ -- Jeff Trout <jeff@jefftrout.com> http://www.jefftrout.com/ http://www.stuarthamm.net/
The sequences do not update automatically on the slave(s) in the version we are running ... we do have a cron job that
kicksoff every few minutes and updates them based opn the current value of their respective columns, which so far has
beenall we needed.
G
-----Original Message-----
From: Fred Fung [mailto:fred.fung@versaterm.com]
Sent: Fri 5/14/2004 6:11 AM
To: pgsql-general@postgresql.org
Cc:
Subject: Re: [GENERAL] dbmirror
Thanks for all your comments.
Is it really true that the sequences in the tables in the Slaves will not be
in sync with those in the Master ? If so then this is a show-stopper factor
for us to consider using dbmirror, as we use sequences as a unique key in
many of the tables in our application.
What is Slony-I ? Is it a commerical software ? Is there a website I can
read more about it ?
Thanks.
Fred
----- Original Message -----
From: "Gregory Wood" <gwood@ewebengine.com>
To: "Andrew Sullivan" <ajs@crankycanuck.ca>
Cc: <pgsql-general@postgresql.org>
Sent: Thursday, May 13, 2004 4:10 PM
Subject: Re: [GENERAL] dbmirror
> Does dbmirror do that? No, it does not. It also doesn't support
> promoting a slave database to a master; that has to be done manually, so
> I wouldn't consider that too big a problem.
>
> Worse in my opinion is that sequences don't get updated... so a slave
> that tries to do an insert on a replicated table (for example, when it
> gets manually promoted to master) will find the sequence not where the
> master left it, but where it was loaded. Every sequence has to be
> manually updated before the database is usable.
>
> dbmirror was never intended to be anything but a poor man's
> replication... and it worked remarkably well for that purpose. Now it's
> time to look forward to Slony-I :)
>
> Greg
>
> Andrew Sullivan wrote:
> > On Wed, May 12, 2004 at 05:53:05PM -0700, Gregory S. Williamson wrote:
> >
> >>Fred --
> >>
> >>Yes, the slave database(s) can be safely used in a R/O mode,
> >
> >
> > Does it also block write transactions in those slaves? The ability
> > for clients to write into the slave replicated tables is a problem,
> > because it makes promoting a slave node somewhat risky.
> >
> > Slony-I has a trick to solve this problem, BTW.
> >
> > A
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
Jeff wrote: > Slony-I is an async replication system. > It is pre-beta, but already it works very well. I'm using it in > production with 5 slaves! > > http://gborg.postgresql.org/projects/slony1/ Any chance of a quick review - installation issues, experience in use? -- Richard Huxton Archonet Ltd
On Mon, 17 May 2004 18:13:36 +0100 Richard Huxton <dev@archonet.com> wrote: > Jeff wrote: > > Slony-I is an async replication system. > > It is pre-beta, but already it works very well. I'm using it in > > production with 5 slaves! > > > > http://gborg.postgresql.org/projects/slony1/ > > Any chance of a quick review - installation issues, experience in use? > It is a little light on the documentation front at the moment, when I get some time I'll be writing some. But I found the test scripts gave enough info (along with what documentation was there) to get going. I ran into a couple problems which I fixed (an issue with multiple replication sets and another thread locking issue that tickled a kerberos issue (Which actually was fixed in 7.5's libpq)). Its now working fine in production. We're replicating about 150k insert/update/deletes every hour. The author of slony is very nice and willing to help out. We have an irc channel on irc.freenode.net - #slony where we can try to help if you have problems. I must say, it does work better than eRserver which has a nasty habit of running into java errors and dying (and dying again when restarting). (encountering > 7bit ascii in a sql_ascii db causes jdbc to die.. although libpq, etc. have no issues with the 7bit ascii) -- Jeff Trout <jeff@jefftrout.com> http://www.jefftrout.com/ http://www.stuarthamm.net/
On Mon, May 17, 2004 at 01:47:27PM -0400, Jeff wrote: > > I must say, it does work better than eRserver which has a nasty habit of > running into java errors and dying (and dying again when restarting). > (encountering > 7bit ascii in a sql_ascii db causes jdbc to die.. > although libpq, etc. have no issues with the 7bit ascii) I think that's due to the ancient JDBC driver in there. But I wouldn't counsel anyone to return to erserver from Slony. It's why Afilias is sponsoring Slony development, after all :) A -- Andrew Sullivan | ajs@crankycanuck.ca
Gregory S. Williamson wrote: > The sequences do not update automatically on the slave(s) in the version we are running ... we do have a cron job thatkicks off every few minutes and updates them based opn the current value of their respective columns, which so far hasbeen all we needed. > G Slony-I does replicate sequences in a way that could be considered a compromise. The whole replication process is done in a way that the slaves "leap" from one consistent state into another, and these consistent states are about 10 seconds apart. The sequences get adjusted to a value equal or greater than what is "compatible" with that current sync status, so that a failover would not cause duplicate values. That way, sequences still retain all their concurrency advantages while being adjusted continuously to avoid problems on failover. The mechanism has of course the usual gap-problem, since a sequences value can be adjusted higher than the rows that actually get replicated in that replication round. They get properly adjusted to exact values on a controlled switchover, where the master and slave cooperate and the old master assumes a fully synchroized slave status when the switchover is complete. Jan > > -----Original Message----- > From: Fred Fung [mailto:fred.fung@versaterm.com] > Sent: Fri 5/14/2004 6:11 AM > To: pgsql-general@postgresql.org > Cc: > Subject: Re: [GENERAL] dbmirror > Thanks for all your comments. > > Is it really true that the sequences in the tables in the Slaves will not be > in sync with those in the Master ? If so then this is a show-stopper factor > for us to consider using dbmirror, as we use sequences as a unique key in > many of the tables in our application. > > What is Slony-I ? Is it a commerical software ? Is there a website I can > read more about it ? > > > Thanks. > > > Fred > > > ----- Original Message ----- > From: "Gregory Wood" <gwood@ewebengine.com> > To: "Andrew Sullivan" <ajs@crankycanuck.ca> > Cc: <pgsql-general@postgresql.org> > Sent: Thursday, May 13, 2004 4:10 PM > Subject: Re: [GENERAL] dbmirror > > >> Does dbmirror do that? No, it does not. It also doesn't support >> promoting a slave database to a master; that has to be done manually, so >> I wouldn't consider that too big a problem. >> >> Worse in my opinion is that sequences don't get updated... so a slave >> that tries to do an insert on a replicated table (for example, when it >> gets manually promoted to master) will find the sequence not where the >> master left it, but where it was loaded. Every sequence has to be >> manually updated before the database is usable. >> >> dbmirror was never intended to be anything but a poor man's >> replication... and it worked remarkably well for that purpose. Now it's >> time to look forward to Slony-I :) >> >> Greg >> >> Andrew Sullivan wrote: >> > On Wed, May 12, 2004 at 05:53:05PM -0700, Gregory S. Williamson wrote: >> > >> >>Fred -- >> >> >> >>Yes, the slave database(s) can be safely used in a R/O mode, >> > >> > >> > Does it also block write transactions in those slaves? The ability >> > for clients to write into the slave replicated tables is a problem, >> > because it makes promoting a slave node somewhat risky. >> > >> > Slony-I has a trick to solve this problem, BTW. >> > >> > A >> > >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 3: if posting/reading through Usenet, please send an appropriate >> subscribe-nomail command to majordomo@postgresql.org so that your >> message can get through to the mailing list cleanly > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Using perl DBI I recently read that if autocommit is turned on, it does a commit after every transaction. I wanted to confirm if this also applies to select statements. When I'm loading data, I need to do a select before inserting a new row and I don't want to commit after the select because it's unnecessary. So I am thinking of turning off the autocommit. Any thoughts? Thanks Sally
From: "Sally Sally" <dedeb17@hotmail.com> > Using perl DBI I recently read that if autocommit is turned on, it does a > commit after every transaction. I wanted to confirm if this also applies to > select statements. in fact, it wraps every statement that is not aleady in a transaction, into one. > When I'm loading data, I need to do a select before inserting a new row and > I don't want to commit after the select because it's unnecessary. So I am > thinking of turning off the autocommit. Any thoughts? $dbh->begin_work; <your statements here> ... $dbh->commit; gnari