Re: [HACKERS] How can we tell how far behind the standby is?
On Tue, Nov 9, 2010 at 7:57 AM, Josh Berkus j...@agliodbs.com wrote: The patch which I'm proposing is helpful for you? http://archives.postgresql.org/pgsql-hackers/2010-11/msg00167.php Depends. Is that the timestamp on the master (when it was synced), or the timestamp on the standby (when it was replayed)? It's only helpful if it's the former. It's the former. The function which I'm proposing returns the timestamp of the last replayed commit/abort log record. That timestamp is given when the commit/abort log record is generated in the master. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How can we tell how far behind the standby is?
It's the former. The function which I'm proposing returns the timestamp of the last replayed commit/abort log record. That timestamp is given when the commit/abort log record is generated in the master. That would be *extremely* helpful for all kinds of monitoring tools. Please complete/submit this. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How can we tell how far behind the standby is?
On Sat, Nov 6, 2010 at 3:46 AM, Josh Berkus j...@agliodbs.com wrote: I'm continuing in my efforts now to document how to deploy and manage replication on our wiki. One of the things a DBA needs to do is to use pg_current_xlog_location() (and related functions) to check how far behind the master the standby is. However, there's some serious problems with that: (1) comparing these numbers is quite mathematically complex -- and, for that matter, undocumented. (2) pg_rotate_xlog and/or archive_timeout will create a gap in the xlog positions, quite a large one if it happens near the beginning of a file. There is no way for any monitoring on the standby to tell the difference between a gap created by forced rotation as opposed to being most of a file behind, until the next record shows up. Hello, nagios false alerts! (3) There is no easy way to relate a difference in log positions to an amount of time. The patch which I'm proposing is helpful for you? http://archives.postgresql.org/pgsql-hackers/2010-11/msg00167.php Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How can we tell how far behind the standby is?
--On 5. November 2010 11:46:08 -0700 Josh Berkus j...@agliodbs.com wrote: I'll work on some tools to make this a bit more palatable, but I disagree with earlier assertions that we have the replication monitoring done. There's still a *lot* of work to do. While getting familiar with our SR/HS infrastructure i scripted this https://github.com/psoo/pg_standby_status/blob/master/pg_standby_status.pl Not sure if it does all things right, but it helped me a lot while load testing SR. AFAIK Magnus has a monitoring script, too, i think this one is it: https://github.com/mhagander/munin-plugins/blob/master/postgres/postgres_streaming_.in Maybe that helps, too. -- Thanks Bernd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How can we tell how far behind the standby is?
The patch which I'm proposing is helpful for you? http://archives.postgresql.org/pgsql-hackers/2010-11/msg00167.php Depends. Is that the timestamp on the master (when it was synced), or the timestamp on the standby (when it was replayed)? It's only helpful if it's the former. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How can we tell how far behind the standby is?
On 6 November 2010 05:46, Josh Berkus j...@agliodbs.com wrote: I'm continuing in my efforts now to document how to deploy and manage replication on our wiki. One of the things a DBA needs to do is to use pg_current_xlog_location() (and related functions) to check how far behind the master the standby is. However, there's some serious problems with that: (1) comparing these numbers is quite mathematically complex -- and, for that matter, undocumented. Our solution to this was to strip the slash out of the numbers and then feed them to `bc` for comparison. The shell script for our zabbix item looks something like this: #!/bin/bash errval=-1 primary=$(psql -At -h $1 -p $2 -c SELECT replace(pg_current_xlog_location(), '/', ''); postgres) standby=$(psql -At -h $3 -p $4 -c SELECT replace(pg_last_xlog_receive_location(), '/', ''); postgres) if [ -n $primary -a -n $standby ] then echo $(echo ibase=16; obase=10; $primary-$standby | bc) else echo $errval fi I'm posting this snippet a) in the hopes that it might help others, and b) by way of agreement with Josh's point. Requiring every user who wants to monitor replication to set something like this up for themselves is ... not awesome. Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] How can we tell how far behind the standby is?
Folks, I'm continuing in my efforts now to document how to deploy and manage replication on our wiki. One of the things a DBA needs to do is to use pg_current_xlog_location() (and related functions) to check how far behind the master the standby is. However, there's some serious problems with that: (1) comparing these numbers is quite mathematically complex -- and, for that matter, undocumented. (2) pg_rotate_xlog and/or archive_timeout will create a gap in the xlog positions, quite a large one if it happens near the beginning of a file. There is no way for any monitoring on the standby to tell the difference between a gap created by forced rotation as opposed to being most of a file behind, until the next record shows up. Hello, nagios false alerts! (3) There is no easy way to relate a difference in log positions to an amount of time. I'll work on some tools to make this a bit more palatable, but I disagree with earlier assertions that we have the replication monitoring done. There's still a *lot* of work to do. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How can we tell how far behind the standby is?
On Fri, Nov 5, 2010 at 2:46 PM, Josh Berkus j...@agliodbs.com wrote: I'm continuing in my efforts now to document how to deploy and manage replication on our wiki. One of the things a DBA needs to do is to use pg_current_xlog_location() (and related functions) to check how far behind the master the standby is. However, there's some serious problems with that: (1) comparing these numbers is quite mathematically complex -- and, for that matter, undocumented. (2) pg_rotate_xlog and/or archive_timeout will create a gap in the xlog positions, quite a large one if it happens near the beginning of a file. There is no way for any monitoring on the standby to tell the difference between a gap created by forced rotation as opposed to being most of a file behind, until the next record shows up. Hello, nagios false alerts! (3) There is no easy way to relate a difference in log positions to an amount of time. I'll work on some tools to make this a bit more palatable, but I disagree with earlier assertions that we have the replication monitoring done. There's still a *lot* of work to do. I've heard the same complaint, and I agree with your concerns. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How can we tell how far behind the standby is?
On Fri, Nov 5, 2010 at 5:39 PM, Robert Haas robertmh...@gmail.com wrote: On Fri, Nov 5, 2010 at 2:46 PM, Josh Berkus j...@agliodbs.com wrote: I'm continuing in my efforts now to document how to deploy and manage replication on our wiki. One of the things a DBA needs to do is to use pg_current_xlog_location() (and related functions) to check how far behind the master the standby is. However, there's some serious problems with that: (1) comparing these numbers is quite mathematically complex -- and, for that matter, undocumented. (2) pg_rotate_xlog and/or archive_timeout will create a gap in the xlog positions, quite a large one if it happens near the beginning of a file. There is no way for any monitoring on the standby to tell the difference between a gap created by forced rotation as opposed to being most of a file behind, until the next record shows up. Hello, nagios false alerts! (3) There is no easy way to relate a difference in log positions to an amount of time. I'll work on some tools to make this a bit more palatable, but I disagree with earlier assertions that we have the replication monitoring done. There's still a *lot* of work to do. I've heard the same complaint, and I agree with your concerns. All this has happened before, and all of it will happen again. At this point pg has the equivalent of MySQL's show slave status in 4.0. The output of that change significantly over time: http://dev.mysql.com/doc/refman/4.1/en/show-slave-status.html http://dev.mysql.com/doc/refman/5.5/en/show-slave-status.html Also of interest http://dev.mysql.com/doc/refman/4.1/en/show-binary-logs.html -- Rob Wultsch wult...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
