PostgreSQL ships with several utility applications to administer the server life cycle and clean up in the event of problems. I spent some time lately looking at what is probably one of the least well known of these, pg_controldata. This useful utility dumps out a number of useful tidbits about a database cluster, given the data directory it should look at. Here's an example from a little-used 8.3.6 instance:
josh@eddie:~$ pg_controldata pg_control version number: 833 Catalog version number: 200711281 Database system identifier: 5291243377389434335 Database cluster state: in production pg_control last modified: Mon 09 Mar 2009 04:05:23 PM MDT Latest checkpoint location: 0/B70E5B9C Prior checkpoint location: 0/B70E5B5C Latest checkpoint's REDO location: 0/B70E5B9C Latest checkpoint's TimeLineID: 1 Latest checkpoint's NextXID: 0/307060 Latest checkpoint's NextOID: 37410 Latest checkpoint's NextMultiXactId: 1 Latest checkpoint's NextMultiOffset: 0 Time of latest checkpoint: Fri 06 Mar 2009 02:27:02 PM MST Minimum recovery ending location: 0/0 Maximum data alignment: 4 Database block size: 8192 Blocks per segment of large relation: 131072 WAL block size: 8192 Bytes per WAL segment: 16777216 Maximum length of identifiers: 64 Maximum columns in an index: 32 Maximum size of a TOAST chunk: 2000 Date/time type storage: floating-point numbers Maximum length of locale name: 128 LC_COLLATE: en_US.UTF-8 LC_CTYPE: en_US.UTF-8
I can't claim to speak with authority on all these data, but leave it as an exercise to the reader to determine the meaning of those that appear most captivating. One of pg_controldata's more interesting features is that it doesn't have to actually connect to anything; it reads everything from the disk. That means you can use it on databases in the middle of WAL recovery, even though you can't actually query the recovering database. The check_postgres.pl script uses this unique capability to make inferences about the health of a WAL replica, specifically by making sure checkpoints happen fairly regularly. pg_controldata requires only one argument, the data directory of the PostgreSQL instance you're interested in, and that only if you haven't already set the PGDATA environment variable.
2 comments:
Hello I know it a very old blog... My fingers are crossed ..whether I will get a reply on this one or not.
We have a replication setup through WAL archiving in PG 8.3
I came to know about this pg_controldata
But all this parameter in the output is not really understandable ... I need to check real time replication whether it is getting slow ro any other problem between master and slave..
As replication progresses, you'll see the the various checkpoint fields change. check_postgres.pl uses the "Time of latest checkpoint" with its checkpoint action, which is what you probably want to monitor replication. If your master server checkpoints regularly (like if you've set checkpoint_timeout), your slave should do the same, as long as replication is working normally. So in your case, you should probably set up check_postgres to run its checkpoint action on the slave, and tell it to complain if checkpoints don't happen about as often as the master's checkpoint_timeout setting says they should.
Post a Comment