Thread: Replication and PITR
Hi ... I have been trying to find a replication to a payment system at the company I work, and Slony-I is of cause the first thing that game into my attention. But when reading chapter 23.3 in the PG manual, there is this comment of PITR used as a replication tool. I also saw the "pgpitrha" project, and this sounds really nice too, but is this a good way to go ? Will PITR be more replication friendly in the future or even form the basis for a future buildin async replication form ? I may be naive, but to me it sound like we/I only need some kind of protocol (or API in postgres) to move PITR data from one server to another, and we could end up with a nice async replication system. pros : - DDL replications - low overhead - no trickers Cons: - binary alike master slave Is this possible, or is it possible to write a module for PG that provide this kind of transportation, or have I been spending too much time together with MySQL :-) Regards /BL
In response to Bo Lorentsen <bl@netgroup.dk>: > Hi ... > > I have been trying to find a replication to a payment system at the > company I work, and Slony-I is of cause the first thing that game into > my attention. But when reading chapter 23.3 in the PG manual, there is > this comment of PITR used as a replication tool. > > I also saw the "pgpitrha" project, and this sounds really nice too, but > is this a good way to go ? Will PITR be more replication friendly > in the future or even form the basis for a future buildin async > replication form ? > > I may be naive, but to me it sound like we/I only need some kind of > protocol (or API in postgres) to move PITR data from one server to > another, and we could end up with a nice async replication system. > > pros : > - DDL replications > - low overhead > - no trickers > > Cons: > - binary alike master slave - No reliability. On slow days, WAL logs could take a long time to rotate, so small but important transactions might not be replicated for a long time. -- Bill Moran Collaborative Fusion Inc.
> > Cons: > - No reliability. On slow days, WAL logs could take a long time to > rotate, so small but important transactions might not be replicated > for a long time. That's gone with 8.2, it will be possible to stream the last modifications, or force a WAL recycle periodically, whatever fits you better. There is some new infrastructure which allows these things, although I didn't have the time to play with them. The big improvement would be indeed to have the infrastructure to start up a standby by simply pointing it to the master server, no other setup needed. Implement that, make it reliable, and any beginner to postgres will be able to easily set up a WAL shipping based standby. Right now you still have to do some complicated scripting to make it work (no idea how much 8.2 will help here, didn't try yet). Cheers, Csaba.
In response to Csaba Nagy <nagy@ecircle-ag.com>: > > > Cons: > > - No reliability. On slow days, WAL logs could take a long time to > > rotate, so small but important transactions might not be replicated > > for a long time. > > That's gone with 8.2, it will be possible to stream the last > modifications, or force a WAL recycle periodically, whatever fits you > better. There is some new infrastructure which allows these things, > although I didn't have the time to play with them. > > The big improvement would be indeed to have the infrastructure to start > up a standby by simply pointing it to the master server, no other setup > needed. Implement that, make it reliable, and any beginner to postgres > will be able to easily set up a WAL shipping based standby. Right now > you still have to do some complicated scripting to make it work (no idea > how much 8.2 will help here, didn't try yet). That will be some neat stuff. I didn't know that was coming up. -- Bill Moran Collaborative Fusion Inc.
On Thu, 2006-09-21 at 17:30 +0200, Bo Lorentsen wrote:
> Hi ...
>
> I have been trying to find a replication to a payment system at the
> company I work, and Slony-I is of cause the first thing that game into
> my attention. But when reading chapter 23.3 in the PG manual, there is
> this comment of PITR used as a replication tool.
>
> I also saw the "pgpitrha" project, and this sounds really nice too, but
> is this a good way to go ? Will PITR be more replication friendly
> in the future or even form the basis for a future buildin async
> replication form ?
>
8.2 makes PITR much easier to use for the situation you'd like. In 8.1,
a WAL might sit around for a while before it becomes full and then sent
off. 8.2 allows you to force a WAL to be sent, and it also allows a
standby mode.
Slony is a good system now, and it's nice because you can use different
versions of PostgreSQL. PITR requires that it's the same version.
However, if you're working with a payment system or accounting system,
you may need synchronous replication. With any asynchronous solution
(Slony or PITR standby), there is a possibility (although not likely) to
lose *committed* transactions.
If you do need synchronous replication, consider using two-phase commit
to prepare transactions on several machines before committing them. This
ensures that the data will be on multiple machines before committing to
any of them.
Regards,
Jeff Davis
Csaba Nagy wrote:
Keep in mind that while Slony-I provides you with a read-only replica (able to service queries). At present WAL log replication (in 8.2 or otherwise) would allow you to have a 'warm standby' type database - which would be somewhat in sync (pending the latest transactions), but would be unable to service queries (essentially, you'd have a server that was sitting with postmaster in a recovery state).Cons:- No reliability. On slow days, WAL logs could take a long time to rotate, so small but important transactions might not be replicated for a long time.That's gone with 8.2, it will be possible to stream the last modifications, or force a WAL recycle periodically, whatever fits you better. There is some new infrastructure which allows these things, although I didn't have the time to play with them.
Chander Ganesan Open Technology Group, Inc. - Expert PostgreSQL Training One Copley Parkway, Suite 210 Morrisville, NC 27560 Phone: 877-258-8987/919-463-0999 http://www.otg-nc.com
The big improvement would be indeed to have the infrastructure to start
up a standby by simply pointing it to the master server, no other setup
needed. Implement that, make it reliable, and any beginner to postgres
will be able to easily set up a WAL shipping based standby. Right now
you still have to do some complicated scripting to make it work (no idea
how much 8.2 will help here, didn't try yet).
Cheers,
Csaba.
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq Csaba Nagy wrote: > That's gone with 8.2, it will be possible to stream the last > modifications, or force a WAL recycle periodically, whatever fits you > better. There is some new infrastructure which allows these things, > although I didn't have the time to play with them. > This sound very nice, where can I find more info about this ... and when is 8.2 expected to be released ? And are there anyone else that plays with making build in replication for PG 8.2 ? > The big improvement would be indeed to have the infrastructure to start > up a standby by simply pointing it to the master server, no other setup > needed. Implement that, make it reliable, and any beginner to postgres > will be able to easily set up a WAL shipping based standby. Right now > you still have to do some complicated scripting to make it work (no idea > how much 8.2 will help here, didn't try yet). > Do you know what the motivation for these changes have been ? Better PITR or is replication a direct goal ? /BL
Bill Moran wrote: > - No reliability. On slow days, WAL logs could take a long time to > rotate, so small but important transactions might not be replicated > for a long time. > So it is all right for backup but for replication it could end up laking too much behind, and a fail over could be hours behind. So PITR can be used, but one of the cons is the unpredictable delay of data. I thought one of the ideas behind the PITR system was to get rather reason backups of data ... /BL
Chander Ganesan wrote: > Keep in mind that while Slony-I provides you with a read-only replica > (able to service queries). At present WAL log replication (in 8.2 or > otherwise) would allow you to have a 'warm standby' type database - > which would be somewhat in sync (pending the latest transactions), but > would be unable to service queries (essentially, you'd have a server > that was sitting with postmaster in a recovery state). > Interesting note ... do you know how fare PG would be from being able to be in "read-only" state when receiving PITR data ? Is it a complex problem or a simple one to solve ? /BL
Jeff Davis wrote: > 8.2 makes PITR much easier to use for the situation you'd like. In 8.1, > a WAL might sit around for a while before it becomes full and then sent > off. 8.2 allows you to force a WAL to be sent, and it also allows a > standby mode. > This sounds really neat ! To me this sound like we may be able to replicate using different priorities, balancing performance and data integrity. But what do you mean by "standby mode" ? That PG maý be able to serve as a readonly DB and replication client at the same time ? > Slony is a good system now, and it's nice because you can use different > versions of PostgreSQL. PITR requires that it's the same version. > The PITR requirement all makes sense, and I don't like the missing DDL updates and the fact that Slony is trigger. > However, if you're working with a payment system or accounting system, > you may need synchronous replication. With any asynchronous solution > (Slony or PITR standby), there is a possibility (although not likely) to > lose *committed* transactions. > Hmm, yes but again this have to be balanced with performance, and at the moment I can live with a replication that is a little behind but, I will try to keep the overhead down. > If you do need synchronous replication, consider using two-phase commit > to prepare transactions on several machines before committing them. This > ensures that the data will be on multiple machines before committing to > any of them. > Hmm, I saw that feature announced in 8.1, and I am sure it will come in handy one day, but right now async is acceptable. /BL
On Fri, 2006-09-22 at 08:12 +0200, Bo Lorentsen wrote:
> Jeff Davis wrote:
> > 8.2 makes PITR much easier to use for the situation you'd like. In 8.1,
> > a WAL might sit around for a while before it becomes full and then sent
> > off. 8.2 allows you to force a WAL to be sent, and it also allows a
> > standby mode.
> >
> This sounds really neat !
>
> To me this sound like we may be able to replicate using different
> priorities, balancing performance and data integrity. But what do you
> mean by "standby mode" ? That PG maý be able to serve as a readonly DB
> and replication client at the same time ?
Standby mode means that the database is kept almost up to date with the
master, but is not "up". When the master goes down, you can bring the
standby machine up. Until then, you unfortunately can't even do read
queries on that machine.
If you want more of a master/slave setup for performance, you should
take a second look at Slony. A PITR standby doesn't help you with
performance at all.
> > Slony is a good system now, and it's nice because you can use different
> > versions of PostgreSQL. PITR requires that it's the same version.
> >
> The PITR requirement all makes sense, and I don't like the missing DDL
> updates and the fact that Slony is trigger.
Why don't you like the fact that Slony is trigger-based? Does that cause
you a problem?
And missing DDL is mainly a problem when you want to provide postgresql
to many people, and you have no idea how they will use it. If that's the
case, standby PITR might be a better solution for you. Slony has nice
"execute script" functionality that is useful for making DDL changes on
all machines.
> > However, if you're working with a payment system or accounting system,
> > you may need synchronous replication. With any asynchronous solution
> > (Slony or PITR standby), there is a possibility (although not likely) to
> > lose *committed* transactions.
> >
> Hmm, yes but again this have to be balanced with performance, and at the
> moment I can live with a replication that is a little behind but, I will
> try to keep the overhead down.
Asynchronous does have very good performance.
> > If you do need synchronous replication, consider using two-phase commit
> > to prepare transactions on several machines before committing them. This
> > ensures that the data will be on multiple machines before committing to
> > any of them.
> >
> Hmm, I saw that feature announced in 8.1, and I am sure it will come in
> handy one day, but right now async is acceptable.
>
I prefer working with async when possible because it's easier to do
well. I was just making sure you knew that it is possible to lose
transactions.
By the way, no matter what you do, you probably do want to use the PITR
to at least do backups for you. It won't help to use replication if
someone accidentally does an unqualified "DELETE FROM mytable".
Regards,
Jeff Davis
On Fri, 2006-09-22 at 07:47 +0200, Bo Lorentsen wrote:
> Bill Moran wrote:
> > - No reliability. On slow days, WAL logs could take a long time to
> > rotate, so small but important transactions might not be replicated
> > for a long time.
> >
> So it is all right for backup but for replication it could end up laking
> too much behind, and a fail over could be hours behind.
>
> So PITR can be used, but one of the cons is the unpredictable delay of
> data. I thought one of the ideas behind the PITR system was to get
> rather reason backups of data ...
>
8.2 will fix this. You can send the WALs periodically even if they're
not full. In general, PITR will be substantially improved in 8.2 (thanks
Simon!).
The beta should be out soon enough. Download it (or the CVS) and try it
out. It never hurts to actually simulate a failure and see how quickly
and effectively you actually can recover. That is especially true in
8.1, where PITR is still somewhat rough around the edges.
Regards,
Jeff Davis
Jeff Davis wrote: > 8.2 will fix this. You can send the WALs periodically even if they're > not full. In general, PITR will be substantially improved in 8.2 (thanks > Simon!). > This sounds very nice, and this will make PG an even more reliable tool. > The beta should be out soon enough. Download it (or the CVS) and try it > out. It never hurts to actually simulate a failure and see how quickly > and effectively you actually can recover. That is especially true in > 8.1, where PITR is still somewhat rough around the edges. > Yes, this is a good idea, and I will try it some time. /BL
Jeff Davis wrote: > Standby mode means that the database is kept almost up to date with the > master, but is not "up". When the master goes down, you can bring the > standby machine up. Until then, you unfortunately can't even do read > queries on that machine. > Do you know if this will change in the future ? > If you want more of a master/slave setup for performance, you should > take a second look at Slony. A PITR standby doesn't help you with > performance at all. > Ok, I can see that ... so PITR is for a standby backup DB, with at the best ... manual fail over ? > Why don't you like the fact that Slony is trigger-based? Does that cause > you a problem? > Hmm, well i guess i dislike the idea of having a high level mechanism to collect data, not a rational argument, sorry. The PITR just seemed so right as it has a more prober low level approach, but it sound to me like Slony is the only real choice at the moment, and it will do the job with a relatively low overhead. > And missing DDL is mainly a problem when you want to provide postgresql > to many people, and you have no idea how they will use it. If that's the > case, standby PITR might be a better solution for you. Slony has nice > "execute script" functionality that is useful for making DDL changes on > all machines. > Ok, I think that the only thing I really need to do is to try to work more with Slony and learn to understand it. And the DDL problem is more when others need to maintain the system, and I then have to explain how to do this and that, and I think I am a bit spoiled by the easy working of the mysql replication :-) > Asynchronous does have very good performance. > So, Slony also do some queuing to gain low overhead ? > I prefer working with async when possible because it's easier to do > well. I was just making sure you knew that it is possible to lose > transactions. > Thanks, one never know :-) > By the way, no matter what you do, you probably do want to use the PITR > to at least do backups for you. It won't help to use replication if > someone accidentally does an unqualified "DELETE FROM mytable". > Yeps, a master and and slave with Slony replication and backup from the slave database, that seems to be the plan at the moment. /BL
On Mon, 2006-09-25 at 13:48 +0200, Bo Lorentsen wrote:
> Jeff Davis wrote:
> > Standby mode means that the database is kept almost up to date with the
> > master, but is not "up". When the master goes down, you can bring the
> > standby machine up. Until then, you unfortunately can't even do read
> > queries on that machine.
> >
> Do you know if this will change in the future ?
I don't know for sure, but I would guess not any time soon. A PITR
standby works by operating in recovery mode while it's waiting for the
WAL files to arrive. When you bring the database up, you're telling it
there are no more files to wait for, and to finish recovering and start
up. I have no idea how difficult it would be to try to allow read
queries while in recovery mode. In recovery mode, I don't think you can
create new backends.
I would think that the data pages are written and consistent while in
recovery mode, so maybe it's reasonable to do. However, I'm only
speculating and anything like this would probably not be coming soon.
> > If you want more of a master/slave setup for performance, you should
> > take a second look at Slony. A PITR standby doesn't help you with
> > performance at all.
> >
> Ok, I can see that ... so PITR is for a standby backup DB, with at the
> best ... manual fail over ?
There's no reason it can't be automated. But the database doesn't know
when you want to fail over, so you just need to tell it. In 8.1, you can
have restore_command return a non-zero exit status and that will stop
the recovery mode and start up the backup database. Then have some
scripts redirect the traffic from the other database to the backup
database.
Since we're talking about async replication, a failover is the process
that could result in lost transactions. That's the reason the database
can't make the decision to fail over automatically.
> > And missing DDL is mainly a problem when you want to provide postgresql
> > to many people, and you have no idea how they will use it. If that's the
> > case, standby PITR might be a better solution for you. Slony has nice
> > "execute script" functionality that is useful for making DDL changes on
> > all machines.
> >
> Ok, I think that the only thing I really need to do is to try to work
> more with Slony and learn to understand it. And the DDL problem is more
> when others need to maintain the system, and I then have to explain how
> to do this and that, and I think I am a bit spoiled by the easy working
> of the mysql replication :-)
Sometimes "easy working" means that it's not doing what you think it's
doing. Replication is complicated and heavily dependent on what your
business needs it for, and what should be done in the case of failure.
There are no perfect answers to those questions, and if MySQL is making
the decisions for you maybe it's making choices wrong for your business.
Disclaimer: I don't know much about MySQL's replication.
> > Asynchronous does have very good performance.
> >
> So, Slony also do some queuing to gain low overhead ?
As I understand it, Slony does batch updates on the slaves, which would
be better performance than re-executing every transaction.
Regards,
Jeff Davis
Jeff Davis wrote: > I don't know for sure, but I would guess not any time soon. A PITR > standby works by operating in recovery mode while it's waiting for the > WAL files to arrive. When you bring the database up, you're telling it > there are no more files to wait for, and to finish recovering and start > up. I have no idea how difficult it would be to try to allow read > queries while in recovery mode. In recovery mode, I don't think you can > create new backends. > > I would think that the data pages are written and consistent while in > recovery mode, so maybe it's reasonable to do. However, I'm only > speculating and anything like this would probably not be coming soon. > Ok, but this gives me a clear picture of what I am able to do at the moment, and no matter what I think, Slony is the replication method I will be using and PITR is nice for backup, as it is designed for. > Since we're talking about async replication, a failover is the process > that could result in lost transactions. That's the reason the database > can't make the decision to fail over automatically. > Ok, makes sense, it has to be some external logic that makes this failover happened, and that logic must be related to whatever system the database is supporting. > Sometimes "easy working" means that it's not doing what you think it's > doing. Replication is complicated and heavily dependent on what your > business needs it for, and what should be done in the case of failure. > There are no perfect answers to those questions, and if MySQL is making > the decisions for you maybe it's making choices wrong for your business. > MySQL only takes care of the replication, not the failover ... but it seems like they have some kind of statement queue (no trigger setup) and a transfer protocol all integrated in the server, and that makes it "simpel". There is no understanding regarding transactions, as far as I have seen. > Disclaimer: I don't know much about MySQL's replication. > That is ok. > As I understand it, Slony does batch updates on the slaves, which would > be better performance than re-executing every transaction. > That makes sense ... then the only thing to worry about is where these "baches" are written. On the same disk as the master database or on the client side, or will it be advisable to use a NFS mount between these to machines to balance the disk writing ? Thanks for your valuable answers ! /BL
> I would think that the data pages are written and consistent while in > recovery mode, so maybe it's reasonable to do. However, I'm only > speculating and anything like this would probably not be coming soon. I was thinking at one point about what problems could prevent the standby to allow read only access, and got to the conclusion that it would be difficult to assure consistency. Think about it: you start a read only transaction on the standby, but if the standby continues to apply changes from the master, it is entirely possible it will rewrite/delete some pages which contain data which your read only transaction should be able to see, but it won't see it. So to make the read only transactions consistent, the standby would be required to stop applying changes which are newer than the oldest read only transaction running. That would mean you will have a backlog of WAL records to apply at least as big as the age of your oldest running read only transaction, therefore the data will be delayed the same compared to the master, which might be OK for some scenarios... Of course there might be other problems too. Cheers, Csaba.
On Tue, Sep 26, 2006 at 11:00:56AM +0200, Csaba Nagy wrote: > Of course there might be other problems too. Another thing would be that the read-only transaction still needs a snapshot, and whatever transaction ID it uses will have been used by the server also. I think the visibility issue may be solvable as long as the transaction ID on the slave doesn't pass the VACUUM horizon of the server. But it would require careful studying of the WAL write order to be confident it would actually work. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
On Fri, Sep 22, 2006 at 07:58:41AM +0200, Bo Lorentsen wrote:
> Interesting note ... do you know how fare PG would be from being able to
> be in "read-only" state when receiving PITR data ? Is it a complex
> problem or a simple one to solve ?
I don't know that it's even possible. The PITR replica is
essentially a node in permanent crash-recovery mode until it's caught
up. Think of Oracle's comparable product -- you can't read from
those replicas either.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
If they don't do anything, we don't need their acronym.
--Josh Hamilton, on the US FEMA
On Tue, Sep 26, 2006 at 08:21:44AM +0200, Bo Lorentsen wrote:
> seems like they have some kind of statement queue (no trigger setup) and
> a transfer protocol all integrated in the server, and that makes it
> "simpel". There is no understanding regarding transactions, as far as I
> have seen.
Note that, the last time I looked at it, there was no interlock to
ensure that your statement queue (which is basically just a log of
statements as executed on the "master") was not accidentally blown
away by your cleanup process before your target replicas were up to
date. This might have improved recently, but when I looked at it
MySQL's async replication was high on the "ease of use" and low on
the "works in sticky situations". As I say, they may have fixed it;
but I advise people to look very carefully at how it works before
deciding it is adequate.
The important thing to remember about database replicas is that
you're _already_ planning for the small percentage of cases where
things break. Therefore, an 80/20 solution is not good enough: the
thing has to work when most things have broken, or it's no use to
you.
> That makes sense ... then the only thing to worry about is where these
> "baches" are written. On the same disk as the master database or on the
> client side, or will it be advisable to use a NFS mount between these to
> machines to balance the disk writing ?
No. I suggest you have a look at the docs, and take these questions
to the (again functioning) Slony list, where people can advise about
that.
The short answer is that the things to write are stored in the origin
for the table (don't think of it as a database replica, because you
can have different tables originating in different nodes). You can
_also_ write sets out to disk, if you like. Someone (my colleagues,
in fact) appear to have a nasty bug in that functionality that they
can't nail down; nobody else has reproduced it.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
Unfortunately reformatting the Internet is a little more painful
than reformatting your hard drive when it gets out of whack.
--Scott Morris
On Tue, 2006-09-26 at 08:21 +0200, Bo Lorentsen wrote:
> MySQL only takes care of the replication, not the failover ... but it
> seems like they have some kind of statement queue (no trigger setup) and
> a transfer protocol all integrated in the server, and that makes it
> "simpel". There is no understanding regarding transactions, as far as I
> have seen.
If it's a statement queue, what happens when you do "INSERT ... VALUES
(random())"? Can the statements be executed out of order on the slave or
are they serialized?
> > As I understand it, Slony does batch updates on the slaves, which would
> > be better performance than re-executing every transaction.
> >
> That makes sense ... then the only thing to worry about is where these
> "baches" are written. On the same disk as the master database or on the
> client side, or will it be advisable to use a NFS mount between these to
> machines to balance the disk writing ?
>
The updates are queued on the master and transferred over the network to
the slave. You don't need to do any nfs tricks.
Slony is designed to improve read performance. If you want better write
performance pretty much all you can do is use a better I/O system or
partition the data so that all the data is not stored on every server.
Often, read queries are the bottleneck, so that's why so many people
find replication like slony useful.
Regards,
Jeff Davis
On Sep 22, 2006, at 1:34 PM, Jeff Davis wrote: > On Fri, 2006-09-22 at 07:47 +0200, Bo Lorentsen wrote: >> Bill Moran wrote: >>> - No reliability. On slow days, WAL logs could take a long time to >>> rotate, so small but important transactions might not be >>> replicated >>> for a long time. >>> >> So it is all right for backup but for replication it could end up >> laking >> too much behind, and a fail over could be hours behind. >> >> So PITR can be used, but one of the cons is the unpredictable >> delay of >> data. I thought one of the ideas behind the PITR system was to get >> rather reason backups of data ... >> > > 8.2 will fix this. You can send the WALs periodically even if they're > not full. In general, PITR will be substantially improved in 8.2 > (thanks > Simon!). You can work around it right now, too; you just need an external process that will find the active WAL file and periodically copy it to the backup. I'm pretty sure there's info in the archives about the details of setting this up, and there's also the PITRHA project on pgFoundry. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
On Monday 25 September 2006 07:48, Bo Lorentsen wrote:
> Jeff Davis wrote:
> > Standby mode means that the database is kept almost up to date with the
> > master, but is not "up". When the master goes down, you can bring the
> > standby machine up. Until then, you unfortunately can't even do read
> > queries on that machine.
>
> Do you know if this will change in the future ?
>
> > If you want more of a master/slave setup for performance, you should
> > take a second look at Slony. A PITR standby doesn't help you with
> > performance at all.
>
> Ok, I can see that ... so PITR is for a standby backup DB, with at the
> best ... manual fail over ?
>
> > Why don't you like the fact that Slony is trigger-based? Does that cause
> > you a problem?
>
> Hmm, well i guess i dislike the idea of having a high level mechanism to
> collect data, not a rational argument, sorry. The PITR just seemed so
> right as it has a more prober low level approach, but it sound to me
> like Slony is the only real choice at the moment, and it will do the job
> with a relatively low overhead.
>
> > And missing DDL is mainly a problem when you want to provide postgresql
> > to many people, and you have no idea how they will use it. If that's the
> > case, standby PITR might be a better solution for you. Slony has nice
> > "execute script" functionality that is useful for making DDL changes on
> > all machines.
>
> Ok, I think that the only thing I really need to do is to try to work
> more with Slony and learn to understand it. And the DDL problem is more
> when others need to maintain the system, and I then have to explain how
> to do this and that, and I think I am a bit spoiled by the easy working
> of the mysql replication :-)
>
Hmm.... almost sounds like what you really want is mammoth replicator... lower
level than slony, built into the db, can handle ddl (iirc).... not oss
though.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
Jeff Davis wrote: > If it's a statement queue, what happens when you do "INSERT ... VALUES > (random())"? Can the statements be executed out of order on the slave or > are they serialized? > That is very relevant, and my ref to MySQL replication was only the relatively ease of its setup. And in most of the situation it works OK, but it has its limits and is not to be trusted 100% (I had to make special test records to see if the queue was stock). I Just imagined PITR data used instead of SQL update queues, for replication. > The updates are queued on the master and transferred over the network to > the slave. You don't need to do any nfs tricks. > Ok, nice ... as long as Slony don't write local files there is no problem. I really have to start reading about Slony, to understand it better ... I may get surprised :-) > Slony is designed to improve read performance. If you want better write > performance pretty much all you can do is use a better I/O system or > partition the data so that all the data is not stored on every server. > Classic for databases :-) > Often, read queries are the bottleneck, so that's why so many people > find replication like slony useful. > Yes and that goes for me too. /BL
Jim Nasby wrote: > You can work around it right now, too; you just need an external > process that will find the active WAL file and periodically copy it to > the backup. I'm pretty sure there's info in the archives about the > details of setting this up, and there's also the PITRHA project on > pgFoundry. I have seen the PITRHA project, but it looked a bit to much like a hack to me. By doing to much hacking I just end up locking me into a fixed PG version, and that I don't like the sound of. /BL
Robert Treat wrote: > Hmm.... almost sounds like what you really want is mammoth replicator... lower > level than slony, built into the db, can handle ddl (iirc).... not oss > though. > Yes, that may be true .... but I think I will try out Slony first, as the design of the DB (DDL) is quite static. I dislike getting locked up to some non OSS version at the moment. But thanks for the advise. /BL
Andrew Sullivan wrote: > Note that, the last time I looked at it, there was no interlock to > ensure that your statement queue (which is basically just a log of > statements as executed on the "master") was not accidentally blown > away by your cleanup process before your target replicas were up to > date. This might have improved recently, but when I looked at it > MySQL's async replication was high on the "ease of use" and low on > the "works in sticky situations". As I say, they may have fixed it; > but I advise people to look very carefully at how it works before > deciding it is adequate. > I know the MySQL scheme is not perfect, but the setup of one is relatively easy, but you still have to know what is going on, otherwise you are not going to get a good night sleep :-) > The important thing to remember about database replicas is that > you're _already_ planning for the small percentage of cases where > things break. Therefore, an 80/20 solution is not good enough: the > thing has to work when most things have broken, or it's no use to > you. > I agree, and that is why you have to be very careful about your choice :-) Well the nice thing about using a slave DB for reporting is the focus to keep it in sync. If it is a backup server you may ignore it for a while, and then Murphy strikes at you :-) > No. I suggest you have a look at the docs, and take these questions > to the (again functioning) Slony list, where people can advise about > that. > Thanks, I willl ! /BL