tag:blogger.com,1999:blog-7476449567742726187.post4042229147367062855..comments2023-12-18T12:25:52.296-08:00Comments on Database Soup: Determining Furthest Ahead ReplicaJosh Berkushttp://www.blogger.com/profile/09671139717468724246noreply@blogger.comBlogger11125tag:blogger.com,1999:blog-7476449567742726187.post-29655976804627161502015-01-23T07:53:09.395-08:002015-01-23T07:53:09.395-08:00great article. and a late question , even with syn...great article. and a late question , even with sync standby(with streaming replication) the standby servers might fall out of sync. so for, 9.3 are there any updates you can suggest related to streaming replication on how to find the most up-to-date slave?Anonymoushttps://www.blogger.com/profile/14046395774570242532noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-63436624013932694132013-08-14T14:55:19.601-07:002013-08-14T14:55:19.601-07:00Thanks, fixed.Thanks, fixed.Josh Berkushttps://www.blogger.com/profile/09671139717468724246noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-73818179935248053762013-08-13T19:20:11.589-07:002013-08-13T19:20:11.589-07:00In your post, you refer to "xlog_position_num...In your post, you refer to "xlog_position_numeric()", but in the code, the function is "xlog_location_numeric()"<br />--Andyhttps://www.blogger.com/profile/14111536594641624850noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-29889128589906321502013-04-02T03:22:07.690-07:002013-04-02T03:22:07.690-07:00Thanks so much for your post it was very helpful. ...Thanks so much for your post it was very helpful. I've created a basic script that will use the functions against a list of nodes to perform the checks to see if the node is furthest ahead and has the least replay lag. <br /><br />I'm looking at trying to integrate or create a OCF resource agent that will combine this logic also for a pacemaker cluster.<br /><br />https://github.com/smbambling/scripts_bash/blob/master/pgsql_replica_xlog_check.shS. Bamblinghttps://www.blogger.com/profile/09460075685847552179noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-10485881699188525122012-11-10T18:08:52.897-08:002012-11-10T18:08:52.897-08:00perhaps because the pg community is becoming aware...perhaps because the pg community is becoming aware of other environments that simply civil.<br /><br />bouncability has ALWAYS been important, which is what i believe josh is addressing in this great post.<br /><br />from the front-jmscottAnonymoushttps://www.blogger.com/profile/09357615310412531794noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-17338752747538090222012-10-09T10:33:31.628-07:002012-10-09T10:33:31.628-07:00Heikki,
I'm definitely able to get a larger-t...Heikki,<br /><br />I'm definitely able to get a larger-than-32-bit signed integer from the offset field. Are you sure of the above? Josh Berkushttps://www.blogger.com/profile/09671139717468724246noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-43832340288649737542012-10-09T01:05:33.633-07:002012-10-09T01:05:33.633-07:00> xlog_position_numeric() returns a numeric val...> xlog_position_numeric() returns a numeric value which can exceed a 64-bit<br />> integer in size. So make sure your calling and sorting code is prepared<br />> to handle a larger-than-eight-byte number.<br /><br />An XLogRecPtr is internally two 32-bit integers in PostgreSQL 9.2, and will be a single 64-bit integer in PostgreSQL 9.3. Given that, I don't see how xlog_position_numeric() can return a value that doesn't fit in a 64-bit integer. Theoretically I suppose it might not fit in a *signed* 64-bit integer, but getting the XLOG position into that range would require generating 8 exabytes of WAL data, and once you get there you'd start to get uncomfortably close to reaching the hard 2^64 limit anyway.Heikki Linnakangashttps://www.blogger.com/profile/08839773439531311324noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-5451771238352599682012-10-08T12:27:25.813-07:002012-10-08T12:27:25.813-07:00I see. Thanks for your post and the answer!I see. Thanks for your post and the answer!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-90427551918362413322012-10-08T12:21:03.521-07:002012-10-08T12:21:03.521-07:00Well, manual failover is the solution for some use...Well, manual failover is the solution for some users. If you don't have the tooling to prevent split-brain, then it's a bad idea to do automatic failover.<br /><br />For folks who set up automated failover, there are a variety of packages, including PGX's internal code (soon to be open sourced), RepMgr, OmniPITR, pgPool2 and others, which can be integrated with monitoring software and network control software to manage failover.<br /><br />Even in that case, though, the software needs a determinative algorithm to determine which replica to fail over *to*, which is what I'm trying to provide.<br /><br />The problem with providing packaged one-size-fits-all software is that no two setups with multiple replicas are the same, especially when you take other systems tools into account. I agree that we could stand to have some one-size-fits-some software for some of the most obvious cases, such as AWS web load balancing. There's a lot more work to be done there, certainly.Josh Berkushttps://www.blogger.com/profile/09671139717468724246noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-57346580489066349232012-10-08T11:29:17.526-07:002012-10-08T11:29:17.526-07:00Sorry, I'm kind of new in Postgresql setup and...Sorry, I'm kind of new in Postgresql setup and wonder if there is some automatic solution for failover. <br /><br />"<br />the master is down, and you want to promote the "farthest ahead" replica so that you can remaster the other replicas from it with a minimum of fuss and data loss.<br />"<br /><br />Do you really expect a human sitting and waiting for master failing and then fix all manually? Is there no software for it?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-63500558895773518122012-10-08T11:25:51.598-07:002012-10-08T11:25:51.598-07:00Great. I don't understand how we have gone th...Great. I don't understand how we have gone this long without being forced to provide more helpful tooling for this.Bruce Momjianhttps://www.blogger.com/profile/07875088787463864011noreply@blogger.com