PgPool ADM extension

pgpool_adm is a PgPool extension for PostgreSQL that provides functions similar to the PCP commands. This article explains how to install and use the pgpool_adm extension.

List of packages available:

pgpool-II-pg10-extensions.x86_64 : PostgreSQL extensions for pgpool-II
pgpool-II-pg11-extensions.x86_64 : PostgreSQL extensions for pgpool-II
pgpool-II-pg12-extensions.x86_64 : PostgreSQL extensions for pgpool-II
pgpool-II-pg13-extensions.x86_64 : PostgreSQL extensions for pgpool-II
pgpool-II-pg14-extensions.x86_64 : PostgreSQL extensions for pgpool-II
pgpool-II-pg15-extensions.x86_64 : PostgreSQL extensions for pgpool-II
pgpool-II-pg16-extensions.x86_64 : PostgreSQL extensions for pgpool-II

Installation example:

dnf install pgpool-II-pg16-extensions

Once installed, you can find the following extensions in the PostgreSQL lib directory:

/usr/pgsql-16/lib/pgpool_adm.so  
/usr/pgsql-16/lib/pgpool-recovery.so 
/usr/pgsql-16/lib/pgpool-regclass.so

List of packages available:

edb-as13-pgpool41-extensions.x86_64 : The server extension modules for pgpool
edb-as13-pgpool42-extensions.x86_64 : The server extension modules for pgpool
edb-as13-pgpool43-extensions.x86_64 : The server extension modules for pgpool
edb-as13-pgpool44-extensions.x86_64 : The server extension modules for pgpool
edb-as13-pgpool45-extensions.x86_64 : The server extension modules for pgpool
edb-as14-pgpool43-extensions.x86_64 : The server extension modules for pgpool
edb-as14-pgpool44-extensions.x86_64 : The server extension modules for pgpool
edb-as14-pgpool45-extensions.x86_64 : The server extension modules for pgpool
edb-as15-pgpool44-extensions.x86_64 : The server extension modules for pgpool
edb-as15-pgpool45-extensions.x86_64 : The server extension modules for pgpool
edb-as16-pgpool44-extensions.x86_64 : The server extension modules for pgpool
edb-as16-pgpool45-extensions.x86_64 : The server extension modules for pgpool

Installation example:

yum install -y edb-as16-pgpool45-extensions

Once installed, you can find the following extensions in the PostgreSQL lib directory:

/usr/edb/as16/lib/pgpool_adm.so 
/usr/edb/as16/lib/pgpool-recovery.so

The pgpool_adm.so provides functions that work similar to PCP commands, the pgpool-recovery.so is used for online recovery (not covered in this article), and the pgpool-regclass.so is an obsolete extension used from PostgreSQL 8.0 to 9.3. Its functionality is now part of PostgreSQL core. This article focuses only on the pgpool_adm.so extension.

To create the extension in any PostgreSQL database, you do not need to add it to shared_preload_libraries. Simply connect to any database and create the extension, for example:

\c postgres

CREATE EXTENSION pgpool_adm;

Expected values:

• node_id (int);
• pgpool_host (text);
• pcp_port (int);
• pcp_username (text);
• pcp_user_password(text).

Example:

SELECT * FROM pcp_node_info(0, '192.168.1.249', 9898, 'pcpuser', '12345678');

Expected values:

• node_id (int);
• pgpool_host (text);
• pcp_port (int);
• pcp_username (text);
• pcp_user_password(text).

Example:

SELECT * FROM pcp_health_check_stats(0, '192.168.1.249' ,9898, 'pcpuser', '12345678');

Expected values:

• pgpool_host (text);
• pcp_port (int);
• pcp_username (text);
• pcp_user_password(text).

Example:

SELECT * FROM pcp_pool_status('192.168.1.249', 9898, 'pcpuser', '12345678');

You can also filter by backend:

SELECT * FROM pcp_pool_status('192.168.1.249', 9898, 'pcpuser', '12345678') WHERE item ~ 'backend.*0';

Expected values:

• pgpool_host (text);
• pcp_port (int);
• pcp_username (text);
• pcp_user_password(text).

Example:

SELECT * FROM pcp_node_count('192.168.1.249', 9898, 'pcpuser', '12345678');

Expected values:

• node_id (int);
• gracefully (boolean);
• pgpool_host (text);
• pcp_port (int);
• pcp_username (text);
• pcp_user_password(text).

Example:

SELECT * FROM pcp_detach_node(2, true, '192.168.1.249' ,9898, 'pcpuser', '12345678');

Expected values:

• node_id (int);
• pgpool_host (text);
• pcp_port (int);
• pcp_username (text);
• pcp_user_password(text).

Example:

SELECT * FROM pcp_attach_node(2,'192.168.1.249', 9898, 'pcpuser', '12345678');