.::: Testing Routing Balancing PostgreSQL EDB using PGPool-II Write & Read :::.

 

A. queri normal select normal

SELECT
    CASE inet_server_addr()
        WHEN '10.10.10.31'::inet THEN 'teguhth01'
        WHEN '10.10.10.32'::inet THEN 'teguhth02'
        WHEN '10.10.10.33'::inet THEN 'teguhth03'
        ELSE 'UNKNOWN'
    END AS hostname,
    inet_server_addr() AS ip,
    inet_server_port() AS port,
    CASE inet_server_addr()
        WHEN '10.10.10.31'::inet THEN 'Master'
        WHEN '10.10.10.32'::inet THEN 'Slave'
        WHEN '10.10.10.33'::inet THEN 'Slave'
        ELSE 'UNKNOWN'
    END AS role,
    CASE inet_server_addr()
        WHEN '10.10.10.31'::inet THEN 'Write'
        WHEN '10.10.10.32'::inet THEN 'Read'
        WHEN '10.10.10.33'::inet THEN 'Read'
        ELSE 'UNKNOWN'
    END AS access_mode,
    inet_client_addr() AS ipclient,
    inet_client_port() AS ipclientport,
    current_database() AS database,
    current_schema() AS dbschema,
    current_user AS username,
    CURRENT_TIMESTAMP AS current_time;
 
B. queri normal create table & insert     

CREATE TABLE loadpgpool (
    hostname VARCHAR(100),
    ip INET,
    port INTEGER,
    role VARCHAR(20),
    access_mode VARCHAR(20),
    ipclient INET,
    ipclientport INTEGER,
    database VARCHAR(100),
    dbschema VARCHAR(100),
    username VARCHAR(100),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO loadpgpool (
    hostname,
    ip,
    port,
    role,
    access_mode,
    ipclient,
    ipclientport,
    database,
    dbschema,
    username
)
SELECT
    CASE inet_server_addr()
        WHEN '10.10.10.31'::inet THEN 'teguhth01'
        WHEN '10.10.10.32'::inet THEN 'teguhth02'
        WHEN '10.10.10.33'::inet THEN 'teguhth03'
        ELSE 'UNKNOWN'
    END AS hostname,

    inet_server_addr() AS ip,

    inet_server_port() AS port,

    CASE inet_server_addr()
        WHEN '10.10.10.31'::inet THEN 'Master'
        WHEN '10.10.10.32'::inet THEN 'Slave'
        WHEN '10.10.10.33'::inet THEN 'Slave'
        ELSE 'UNKNOWN'
    END AS role,

    CASE inet_server_addr()
        WHEN '10.10.10.31'::inet THEN 'Write'
        WHEN '10.10.10.32'::inet THEN 'Read'
        WHEN '10.10.10.33'::inet THEN 'Read'
        ELSE 'UNKNOWN'
    END AS access_mode,

    inet_client_addr() AS ipclient,
    inet_client_port() AS ipclientport,

    current_database() AS database,
    current_schema() AS dbschema,
    current_user AS username;

C. select using loop to check master or slave from .32/ slave without pgpool-II

echo "insert direct to master"

for i in {1..5}; do
    PGPASSWORD=admin psql -h 10.10.10.31 -p 5432 -U admin -d teguhth -Atc "INSERT INTO loadpgpool (  hostname,ip, port, role, access_mode, ipclient, ipclientport, database, dbschema, username ) SELECT CASE inet_server_addr() WHEN '10.10.10.31'::inet THEN 'teguhth01' WHEN '10.10.10.32'::inet THEN 'teguhth02' WHEN '10.10.10.33'::inet THEN 'teguhth03' ELSE 'UNKNOWN' END AS hostname, inet_server_addr() AS ip, inet_server_port() AS port, CASE inet_server_addr() WHEN '10.10.10.31'::inet THEN 'Master' WHEN '10.10.10.32'::inet THEN 'Slave' WHEN '10.10.10.33'::inet THEN 'Slave' ELSE 'UNKNOWN' END AS role, CASE inet_server_addr() WHEN '10.10.10.31'::inet THEN 'Write' WHEN '10.10.10.32'::inet THEN 'Read' WHEN '10.10.10.33'::inet THEN 'Read' ELSE 'UNKNOWN' END AS access_mode, inet_client_addr() AS ipclient, inet_client_port() AS ipclientport, current_database() AS database, current_schema() AS dbschema, current_user AS username;"
done

PGPASSWORD=admin psql -h 10.10.10.25 -p 9999 -U userpgpool -d teguhth -c "SELECT * from loadpgpool;"
 

D. using select loop to check master or slave from pgpool-II or direct

for i in {1..5}; do
    PGPASSWORD=admin psql -h 10.10.10.25 -p 9999 -U userpgpool -d teguhth -Atc "

SELECT
    CASE inet_server_addr()
        WHEN '10.10.10.31'::inet THEN 'teguhth01'
        WHEN '10.10.10.32'::inet THEN 'teguhth02'
        WHEN '10.10.10.33'::inet THEN 'teguhth03'
        ELSE 'UNKNOWN'
    END AS hostname,
    inet_server_addr() AS ip,
    inet_server_port() AS port,
    CASE inet_server_addr()
        WHEN '10.10.10.31'::inet THEN 'Master'
        WHEN '10.10.10.32'::inet THEN 'Slave'
        WHEN '10.10.10.33'::inet THEN 'Slave'
        ELSE 'UNKNOWN'
    END AS role,
    CASE inet_server_addr()
        WHEN '10.10.10.31'::inet THEN 'Write'
        WHEN '10.10.10.32'::inet THEN 'Read'
        WHEN '10.10.10.33'::inet THEN 'Read'
        ELSE 'UNKNOWN'
    END AS access_mode,
    inet_client_addr() AS ipclient,
    inet_client_port() AS ipclientport,
    current_database() AS database,
    current_schema() AS dbschema,
    current_user AS username,
    CURRENT_TIMESTAMP AS current_time;
    "
done

for i in {1..5}; do
    PGPASSWORD=admin psql -h 10.10.10.25 -p 9999 -U userpgpool -d teguhth -Atc " SELECT CASE inet_server_addr() WHEN '10.10.10.31'::inet THEN 'teguhth01' WHEN '10.10.10.32'::inet THEN 'teguhth02' WHEN '10.10.10.33'::inet THEN 'teguhth03' ELSE 'UNKNOWN' END AS hostname, inet_server_addr() AS ip, inet_server_port() AS port, CASE inet_server_addr() WHEN '10.10.10.31'::inet THEN 'Master' WHEN '10.10.10.32'::inet THEN 'Slave' WHEN '10.10.10.33'::inet THEN 'Slave' ELSE 'UNKNOWN' END AS Role, CASE inet_server_addr() WHEN '10.10.10.31'::inet THEN 'Write' WHEN '10.10.10.32'::inet THEN 'Read' WHEN '10.10.10.33'::inet THEN 'Read' ELSE 'UNKNOWN' END AS access_mode, inet_client_addr() as ipclient, inet_client_port() as ipclientport, current_database() AS database, current_schema() as dbschema, current_user AS "username",CURRENT_TIMESTAMP AS current_time;"
done

echo "select from pgpool"

for i in {1..2}; do
    PGPASSWORD=admin psql -h 10.10.10.25 -p 9999 -U userpgpool -d teguhth -c " SELECT CASE inet_server_addr() WHEN '10.10.10.31'::inet THEN 'teguhth01' WHEN '10.10.10.32'::inet THEN 'teguhth02' WHEN '10.10.10.33'::inet THEN 'teguhth03' ELSE 'UNKNOWN' END AS hostname, inet_server_addr() AS ip, inet_server_port() AS port, CASE inet_server_addr() WHEN '10.10.10.31'::inet THEN 'Master' WHEN '10.10.10.32'::inet THEN 'Slave' WHEN '10.10.10.33'::inet THEN 'Slave' ELSE 'UNKNOWN' END AS Role, CASE inet_server_addr() WHEN '10.10.10.31'::inet THEN 'Write' WHEN '10.10.10.32'::inet THEN 'Read' WHEN '10.10.10.33'::inet THEN 'Read' ELSE 'UNKNOWN' END AS access_mode, inet_client_addr() as ipclient, inet_client_port() as ipclientport, current_database() AS database, current_schema() as dbschema, current_user AS "username",CURRENT_TIMESTAMP AS current_time;"
done

echo "select from master"

for i in {1..2}; do
    PGPASSWORD=admin psql -h 10.10.10.31 -p 5432 -U admin -d teguhth -c " SELECT CASE inet_server_addr() WHEN '10.10.10.31'::inet THEN 'teguhth01' WHEN '10.10.10.32'::inet THEN 'teguhth02' WHEN '10.10.10.33'::inet THEN 'teguhth03' ELSE 'UNKNOWN' END AS hostname, inet_server_addr() AS ip, inet_server_port() AS port, CASE inet_server_addr() WHEN '10.10.10.31'::inet THEN 'Master' WHEN '10.10.10.32'::inet THEN 'Slave' WHEN '10.10.10.33'::inet THEN 'Slave' ELSE 'UNKNOWN' END AS Role, CASE inet_server_addr() WHEN '10.10.10.31'::inet THEN 'Write' WHEN '10.10.10.32'::inet THEN 'Read' WHEN '10.10.10.33'::inet THEN 'Read' ELSE 'UNKNOWN' END AS access_mode, inet_client_addr() as ipclient, inet_client_port() as ipclientport, current_database() AS database, current_schema() as dbschema, current_user AS "username",CURRENT_TIMESTAMP AS current_time;"
done
    
echo "select from slave"

for i in {1..2}; do
    PGPASSWORD=admin psql -h 10.10.10.33 -p 5432 -U admin -d teguhth -c " SELECT CASE inet_server_addr() WHEN '10.10.10.31'::inet THEN 'teguhth01' WHEN '10.10.10.32'::inet THEN 'teguhth02' WHEN '10.10.10.33'::inet THEN 'teguhth03' ELSE 'UNKNOWN' END AS hostname, inet_server_addr() AS ip, inet_server_port() AS port, CASE inet_server_addr() WHEN '10.10.10.31'::inet THEN 'Master' WHEN '10.10.10.32'::inet THEN 'Slave' WHEN '10.10.10.33'::inet THEN 'Slave' ELSE 'UNKNOWN' END AS Role, CASE inet_server_addr() WHEN '10.10.10.31'::inet THEN 'Write' WHEN '10.10.10.32'::inet THEN 'Read' WHEN '10.10.10.33'::inet THEN 'Read' ELSE 'UNKNOWN' END AS access_mode, inet_client_addr() as ipclient, inet_client_port() as ipclientport, current_database() AS database, current_schema() as dbschema, current_user AS "username",CURRENT_TIMESTAMP AS current_time;"
done
 

   
E. using insert loop to check master or slave from pgpool-II    

INSERT INTO loadpgpool (
    hostname,
    ip,
    port,
    role,
    access_mode,
    ipclient,
    ipclientport,
    database,
    dbschema,
    username
)
SELECT
    CASE inet_server_addr()
        WHEN '10.10.10.31'::inet THEN 'teguhth01'
        WHEN '10.10.10.32'::inet THEN 'teguhth02'
        WHEN '10.10.10.33'::inet THEN 'teguhth03'
        ELSE 'UNKNOWN'
    END AS hostname,

    inet_server_addr() AS ip,

    inet_server_port() AS port,

    CASE inet_server_addr()
        WHEN '10.10.10.31'::inet THEN 'Master'
        WHEN '10.10.10.32'::inet THEN 'Slave'
        WHEN '10.10.10.33'::inet THEN 'Slave'
        ELSE 'UNKNOWN'
    END AS role,

    CASE inet_server_addr()
        WHEN '10.10.10.31'::inet THEN 'Write'
        WHEN '10.10.10.32'::inet THEN 'Read'
        WHEN '10.10.10.33'::inet THEN 'Read'
        ELSE 'UNKNOWN'
    END AS access_mode,

    inet_client_addr() AS ipclient,
    inet_client_port() AS ipclientport,

    current_database() AS database,
    current_schema() AS dbschema,
    current_user AS username;
    
for i in {1..5}; do
    PGPASSWORD=admin psql -h 10.10.10.25 -p 9999 -U userpgpool -d teguhth -Atc "

INSERT INTO loadpgpool (
    hostname,
    ip,
    port,
    role,
    access_mode,
    ipclient,
    ipclientport,
    database,
    dbschema,
    username
)
SELECT
    CASE inet_server_addr()
        WHEN '10.10.10.31'::inet THEN 'teguhth01'
        WHEN '10.10.10.32'::inet THEN 'teguhth02'
        WHEN '10.10.10.33'::inet THEN 'teguhth03'
        ELSE 'UNKNOWN'
    END AS hostname,

    inet_server_addr() AS ip,

    inet_server_port() AS port,

    CASE inet_server_addr()
        WHEN '10.10.10.31'::inet THEN 'Master'
        WHEN '10.10.10.32'::inet THEN 'Slave'
        WHEN '10.10.10.33'::inet THEN 'Slave'
        ELSE 'UNKNOWN'
    END AS role,

    CASE inet_server_addr()
        WHEN '10.10.10.31'::inet THEN 'Write'
        WHEN '10.10.10.32'::inet THEN 'Read'
        WHEN '10.10.10.33'::inet THEN 'Read'
        ELSE 'UNKNOWN'
    END AS access_mode,

    inet_client_addr() AS ipclient,
    inet_client_port() AS ipclientport,

    current_database() AS database,
    current_schema() AS dbschema,
    current_user AS username;
    "
done

echo "insert direct to pgpool-II"
for i in {1..5}; do
    PGPASSWORD=admin psql -h 10.10.10.25 -p 9999 -U userpgpool -d teguhth -Atc "INSERT INTO loadpgpool (  hostname,ip, port, role, access_mode, ipclient, ipclientport, database, dbschema, username ) SELECT CASE inet_server_addr() WHEN '10.10.10.31'::inet THEN 'teguhth01' WHEN '10.10.10.32'::inet THEN 'teguhth02' WHEN '10.10.10.33'::inet THEN 'teguhth03' ELSE 'UNKNOWN' END AS hostname, inet_server_addr() AS ip, inet_server_port() AS port, CASE inet_server_addr() WHEN '10.10.10.31'::inet THEN 'Master' WHEN '10.10.10.32'::inet THEN 'Slave' WHEN '10.10.10.33'::inet THEN 'Slave' ELSE 'UNKNOWN' END AS role, CASE inet_server_addr() WHEN '10.10.10.31'::inet THEN 'Write' WHEN '10.10.10.32'::inet THEN 'Read' WHEN '10.10.10.33'::inet THEN 'Read' ELSE 'UNKNOWN' END AS access_mode, inet_client_addr() AS ipclient, inet_client_port() AS ipclientport, current_database() AS database, current_schema() AS dbschema, current_user AS username;"
done

PGPASSWORD=admin psql -h 10.10.10.25 -p 9999 -U userpgpool -d teguhth -c "SELECT * from loadpgpool;"

 

F. compare using insert loop to check master or slave from pgpool-II    
 
echo "insert direct to master"

for i in {1..1}; do
    PGPASSWORD=admin psql -h 10.10.10.31 -p 5432 -U admin -d teguhth -Atc "INSERT INTO loadpgpool (  hostname,ip, port, role, access_mode, ipclient, ipclientport, database, dbschema, username ) SELECT CASE inet_server_addr() WHEN '10.10.10.31'::inet THEN 'teguhth01' WHEN '10.10.10.32'::inet THEN 'teguhth02' WHEN '10.10.10.33'::inet THEN 'teguhth03' ELSE 'UNKNOWN' END AS hostname, inet_server_addr() AS ip, inet_server_port() AS port, CASE inet_server_addr() WHEN '10.10.10.31'::inet THEN 'Master' WHEN '10.10.10.32'::inet THEN 'Slave' WHEN '10.10.10.33'::inet THEN 'Slave' ELSE 'UNKNOWN' END AS role, CASE inet_server_addr() WHEN '10.10.10.31'::inet THEN 'Write' WHEN '10.10.10.32'::inet THEN 'Read' WHEN '10.10.10.33'::inet THEN 'Read' ELSE 'UNKNOWN' END AS access_mode, inet_client_addr() AS ipclient, inet_client_port() AS ipclientport, current_database() AS database, current_schema() AS dbschema, current_user AS username;"
done

PGPASSWORD=admin psql -h 10.10.10.25 -p 9999 -U userpgpool -d teguhth -c "SELECT * from loadpgpool;"

echo "insert direct to pgpool-II"
for i in {1..1}; do
    PGPASSWORD=admin psql -h 10.10.10.25 -p 9999 -U userpgpool -d teguhth -Atc "INSERT INTO loadpgpool (  hostname,ip, port, role, access_mode, ipclient, ipclientport, database, dbschema, username ) SELECT CASE inet_server_addr() WHEN '10.10.10.31'::inet THEN 'teguhth01' WHEN '10.10.10.32'::inet THEN 'teguhth02' WHEN '10.10.10.33'::inet THEN 'teguhth03' ELSE 'UNKNOWN' END AS hostname, inet_server_addr() AS ip, inet_server_port() AS port, CASE inet_server_addr() WHEN '10.10.10.31'::inet THEN 'Master' WHEN '10.10.10.32'::inet THEN 'Slave' WHEN '10.10.10.33'::inet THEN 'Slave' ELSE 'UNKNOWN' END AS role, CASE inet_server_addr() WHEN '10.10.10.31'::inet THEN 'Write' WHEN '10.10.10.32'::inet THEN 'Read' WHEN '10.10.10.33'::inet THEN 'Read' ELSE 'UNKNOWN' END AS access_mode, inet_client_addr() AS ipclient, inet_client_port() AS ipclientport, current_database() AS database, current_schema() AS dbschema, current_user AS username;"
done

PGPASSWORD=admin psql -h 10.10.10.25 -p 9999 -U userpgpool -d teguhth -c "SELECT * from loadpgpool;"