I am using pg_ctl -D $PGDATA status to monitor whether my postgres standby database is up and running. Sometimes the database fails to start due to a missing WAL file, but its status is reported as "available".

Is there a simple way to see if postgres is available using the pg_ctl status option, or do I need to write a script to query the postgres database and monitor the output from script?

  • Details please - exact PostgreSQL version, PostgreSQL log file output, etc. Apr 21 '15 at 11:20
  • sorry, postgres 9.2 running on redhat 6. It's not really an issue which requires the log file. Startup is hanging due to a missing WAL file (which is correct behaviour), but the pg_ctl .. status shows it as available. So is there a magic flag to use with pg_ctl (or some other simple method) of checking postgres is available? Apr 21 '15 at 11:23
  • Is it in hot_standby mode? If so, it is available, if it's replayed enough that it's accepting connections and responding to queries. Apr 21 '15 at 11:24
  • it is in hot_standby, but you can't connect to it as it has been unable to bring itself up to date due to lost WAL file (i'm testing what happens in the event of the standby being required after a failure. so trying to access psql give "psql: FATAL: the database system is starting up". But status shows as "available" Apr 21 '15 at 11:29
  • Uh, that doesn't seem right at all then... Apr 21 '15 at 11:31

The pg_ctl status command tests that the postmaster process (corresponding to $PGDATA) does exist from the point of view of the operating system. In the positive case, it reports that it's running (note that the word available is not used).

Essentially that's implemented by reading postmaster.pid and checking the result of kill(that_pid, 0).

It does not mean that this PostgreSQL instance does accept connections or is able to execute queries. To that purpose, better use psql or a script like Nagios check_postgres plugin.

  • It strikes me that we should add pg_ctl status -w or similar, i.e. something that, like -w does for start/stop, makes an actual libpq connection. Apr 22 '15 at 0:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.