Teguh Triharto Learning Center: .::: Test Pgpool-II Monitor PostgreSQL Routing to Master or Slave replication streaming Using Lucee & PHP :::.

1. PostgreSQL Connection

chmod 755 /opt/lucee/bin/pgpool_connection.sh
chmod 755 /opt/lucee/bin/pgpool_nodes.sh
chmod 755 /opt/lucee/bin/pgpool_replication.sh

[root@teguhth-all ROOT]# cat /opt/lucee/bin/pgpool_connection.sh
#!/bin/bash

export PGPASSWORD='admin'

/usr/bin/psql -h 10.10.10.25 -p 9999 -U userpgpool -d teguhth -c "SELECT inet_client_addr(), inet_client_port(), inet_server_addr(), inet_server_port(),'PostgreSQL ' || current_setting('server_version') as edbversion;"
[root@teguhth-all ROOT]#

2. Pgpool-II SHOW pool_nodes

[root@teguhth-all ROOT]# cat /opt/lucee/bin/pgpool_nodes.sh
#!/bin/bash

export PGPASSWORD='admin'

/usr/bin/psql -h 10.10.10.25 -p 9999 -U userpgpool -d teguhth -c "SHOW pool_nodes;"

[root@teguhth-all ROOT]#

3. PostgreSQL Streaming Replication

[root@teguhth-all ROOT]# cat /opt/lucee/bin/pgpool_replication.sh
#!/bin/bash

export PGPASSWORD='admin'

/usr/bin/psql -h 10.10.10.25 -p 9999 -U userpgpool -d teguhth -c "
SELECT
client_addr,
state,
sync_state,
sent_lsn,
write_lsn,
flush_lsn,
replay_lsn,
pg_size_pretty(
pg_wal_lsn_diff(sent_lsn, replay_lsn)
) AS replication_lag
FROM pg_stat_replication;
"
[root@teguhth-all ROOT]#

4. run script monitor

http://10.10.10.90:8888/test_pgpool_route.cfm

http://10.10.10.90/test_pgpool_route.php

5. script lucee

[root@teguhth-all ROOT]# pwd
/opt/lucee/tomcat/webapps/ROOT
[root@teguhth-all ROOT]#
[root@teguhth-all ROOT]# cat test_pgpool_route.cfm
<cfsetting showdebugoutput="false">

<!DOCTYPE html>
<html>

<head>

<meta charset="UTF-8">

<title>PostgreSQL Pgpool-II Monitor</title>

<style>

body {
font-family: Arial, Helvetica, sans-serif;
margin: 20px;
background: #ffffff;
color: #222222;
}

h2 {
margin-bottom: 20px;
}

h3 {
margin-top: 30px;
margin-bottom: 10px;
}

.info {
border: 1px solid #cccccc;
background: #f7f7f7;
padding: 12px;
margin-bottom: 20px;
}

.output {
border: 1px solid #cccccc;
background: #fafafa;
padding: 15px;
overflow-x: auto;
white-space: pre;
font-family: "Courier New", monospace;
font-size: 13px;
line-height: 1.4;
}

.ok {
color: green;
font-weight: bold;
}

.err {
color: red;
font-weight: bold;
}

</style>

</head>

<body>

<h2>PostgreSQL Pgpool-II Monitor</h2>

<div class="info">

<b>Pgpool-II:</b>
10.10.10.25:9999

<br>

<b>Database:</b>
teguhth

<br>

<b>User:</b>
userpgpool

</div>





<h3>1. PostgreSQL Connection</h3>

<cftry>

<cfexecute
name="/bin/sh"
arguments="/opt/lucee/bin/pgpool_connection.sh"
timeout="30"
variable="connectionOutput"
errorVariable="connectionError">
</cfexecute>

<cfset connectionOK = true>

<cfcatch>

<cfset connectionOK = false>
<cfset connectionError = cfcatch.message>

</cfcatch>

</cftry>

<cfif connectionOK>

<div class="output">

<cfoutput>#connectionOutput#</cfoutput>

</div>

<cfif len(trim(connectionError)) GT 0>

<h3>Error</h3>

<div class="output err">

<cfoutput>#connectionError#</cfoutput>

</div>

</cfif>

<cfelse>

<div class="output err">

<cfoutput>
ERROR: #connectionError#
</cfoutput>

</div>

</cfif>





<h3>2. Pgpool-II SHOW pool_nodes</h3>

<cftry>

<cfexecute
name="/bin/sh"
arguments="/opt/lucee/bin/pgpool_nodes.sh"
timeout="30"
variable="poolOutput"
errorVariable="poolError">
</cfexecute>

<cfset poolOK = true>

<cfcatch>

<cfset poolOK = false>
<cfset poolError = cfcatch.message>

</cfcatch>

</cftry>

<cfif poolOK>

<div class="output">

<cfoutput>#poolOutput#</cfoutput>

</div>

<cfif len(trim(poolError)) GT 0>

<h3>Error</h3>

<div class="output err">

<cfoutput>#poolError#</cfoutput>

</div>

</cfif>

<cfelse>

<div class="output err">

<cfoutput>
ERROR: #poolError#
</cfoutput>

</div>

</cfif>





<h3>3. PostgreSQL Streaming Replication</h3>

<cftry>

<cfexecute
name="/bin/sh"
arguments="/opt/lucee/bin/pgpool_replication.sh"
timeout="30"
variable="replicationOutput"
errorVariable="replicationError">
</cfexecute>

<cfset replicationOK = true>

<cfcatch>

<cfset replicationOK = false>
<cfset replicationError = cfcatch.message>

</cfcatch>

</cftry>

<cfif replicationOK>

<div class="output">

<cfoutput>#replicationOutput#</cfoutput>

</div>

<cfif len(trim(replicationError)) GT 0>

<h3>Error</h3>

<div class="output err">

<cfoutput>#replicationError#</cfoutput>

</div>

</cfif>

<cfelse>

<div class="output err">

<cfoutput>
ERROR: #replicationError#
</cfoutput>

</div>

</cfif>





<div style="
font-family:Courier New, monospace;
font-size:14px;
margin-top:30px;
padding-top:15px;
border-top:1px solid #cccccc;
">

Copyright by          :
Teguh Triharto

<br>

Website              :

<a
href="https://www.linkedin.com/in/teguhth"
target="_blank">

https://www.linkedin.com/in/teguhth

</a>

</div>

</body>

</html>
[root@teguhth-all ROOT]#

6. script php

[root@teguhth-all ROOT]# cat /var/www/html/test_pgpool_route.php
<?php

/*
* PostgreSQL Pgpool-II Monitor
*
* Shell scripts:
* /opt/lucee/bin/pgpool_connection.sh
* /opt/lucee/bin/pgpool_nodes.sh
* /opt/lucee/bin/pgpool_replication.sh
*/

function runScript($script)
{
$output = [];
$returnCode = 0;

exec(
'/bin/sh ' . escapeshellarg($script) . ' 2>&1',
$output,
$returnCode
);

return [
'output' => implode("\n", $output),
'code' => $returnCode
];
}

/* ==========================================================
1. CONNECTION INFORMATION
========================================================== */

$connection = runScript(
'/opt/lucee/bin/pgpool_connection.sh'
);

/* ==========================================================
2. PGPOOL-II SHOW pool_nodes
========================================================== */

$pool = runScript(
'/opt/lucee/bin/pgpool_nodes.sh'
);

/* ==========================================================
3. STREAMING REPLICATION
========================================================== */

$replication = runScript(
'/opt/lucee/bin/pgpool_replication.sh'
);

?>

<!DOCTYPE html>
<html>

<head>

<meta charset="UTF-8">

<title>PostgreSQL Pgpool-II Monitor</title>

<style>

body {
font-family: Arial, Helvetica, sans-serif;
margin: 20px;
background: #ffffff;
color: #222222;
}

h2 {
margin-bottom: 20px;
}

h3 {
margin-top: 25px;
margin-bottom: 10px;
}

.info {
border: 1px solid #cccccc;
background: #f7f7f7;
padding: 12px;
margin-bottom: 20px;
}

.output {
border: 1px solid #cccccc;
background: #fafafa;
padding: 15px;
overflow-x: auto;
white-space: pre;
font-family: "Courier New", monospace;
font-size: 13px;
line-height: 1.4;
}

.ok {
color: green;
font-weight: bold;
}

.err {
color: red;
font-weight: bold;
}

</style>

</head>

<body>

<h2>PostgreSQL Pgpool-II Monitor</h2>

<div class="info">

<b>Pgpool-II:</b>
10.10.10.25:9999

<br>

<b>Database:</b>
teguhth

<br>

<b>User:</b>
userpgpool

</div>



<h3>1. PostgreSQL Connection</h3>

<?php if ($connection['code'] === 0): ?>

<div class="output"><?php
echo htmlspecialchars($connection['output']);
?></div>

<?php else: ?>

<div class="output err"><?php
echo "ERROR:\n";
echo htmlspecialchars($connection['output']);
?></div>

<?php endif; ?>



<h3>2. Pgpool-II SHOW pool_nodes</h3>

<?php if ($pool['code'] === 0): ?>

<div class="output"><?php
echo htmlspecialchars($pool['output']);
?></div>

<?php else: ?>

<div class="output err"><?php
echo "ERROR:\n";
echo htmlspecialchars($pool['output']);
?></div>

<?php endif; ?>



<h3>3. PostgreSQL Streaming Replication</h3>

<?php if ($replication['code'] === 0): ?>

<div class="output"><?php
echo htmlspecialchars($replication['output']);
?></div>

<?php else: ?>

<div class="output err"><?php
echo "ERROR:\n";
echo htmlspecialchars($replication['output']);
?></div>

<?php endif; ?>



<div style="
font-family:Courier New, monospace;
font-size:14px;
margin-top:30px;
padding-top:15px;
border-top:1px solid #cccccc;
">

Copyright by          :
Teguh Triharto

<br>

Website              :

<a
href="https://www.linkedin.com/in/teguhth"
target="_blank">

https://www.linkedin.com/in/teguhth

</a>

</div>

</body>

</html>
[root@teguhth-all ROOT]#

Teguh Triharto Learning Center

Blog List

Wednesday, August 19, 2026

.::: Test Pgpool-II Monitor PostgreSQL Routing to Master or Slave replication streaming Using Lucee & PHP :::.

No comments:

Post a Comment

Popular Posts