PGPool 'SHOW pool_nodes' returns 'ERROR: unrecognized configuration parameter "pool_nodes"' over jdbc

Question

I have successfully configured our application with PGpool, and I must say, it is pretty awesome. I am trying to expose information about the PGPool cluster in our app, and I'm having trouble using 'SHOW' commands over JDBC with the postgres Driver.

Driver:

postgresql-9.3-1101-jdbc41.jar

When I try to execute the query "SHOW pool_nodes", I get this error:

ERROR: unrecognized configuration parameter "pool_nodes"

I'm pretty certain I'm connected to pgpool and not postgres, since failover is working great. My guess is that the postgres driver is doing some checks up front. Does anyone have an suggestions for getting a hold of this data in my java application?

Answer 1

It's no method to get result from show command, so I used pgadmin and add some PHP code to got it.

First you must install pgadmin and add getNodeInfo.php to the folder, and getNodeInfo.php code:

<?php

/**
* The status value can refrence:
* http://www.pgpool.net/docs/latest/en/html/pcp-node-info.html
* 
* Status is represented by a digit from [0 to 3].
* 
* 0 - This state is only used during the initialization. PCP will never display it.
* 
* 1 - Node is up. No connections yet.
* 
* 2 - Node is up. Connections are pooled.
* 
* 3 - Node is down.
*/

require_once('common.php');
require_once('command.php');

$_SESSION[SESSION_LOGIN_USER]          = "your_pgadmin_account";
$_SESSION[SESSION_LOGIN_USER_PASSWORD] = "your_pgadmin_password";

$nodeCount = getNodeCount();

$nodeInfo = array();

for($i = 0;$i < $nodeCount; $i++){
    $nodeInfo[$i] = getNodeInfo($i);
}

echo json_encode($nodeInfo);

exit();

If your pgadmin is OK to work, you can call http://localhost/getNodeInfo.php to get like

[
  {
    "hostname": "host1 ip",
    "port": "5432",
    "status": "2",
    "weight": "0.500"
  },
  {
    "hostname": "host2 ip",
    "port": "5432",
    "status": "2",
    "weight": "0.500"
  }
]

If status == 3, the host is down.