Best of Both Worlds: Integrating Pgpool with EDB Postgres Failover Manager

 

EDB Postgres Failover Manager (EFM) is a high availability module from EnterpriseDB (EDB) that monitors the health of Postgres clusters and verifies failures quickly. Should one occur, EFM can automatically promote a Standby node to Master to ensure continued performance and protect against data loss.

Among EDB customers, virtually all of them use EDB Postgres Failover Manager in production within their high availability infrastructures, often alongside other solutions. EDB, in fact, developed EFM with hooks and parameters to ease the tool’s integration with external systems and other software.

One such piece of software is Pgpool, the open source middleware that sits between the database clients and the Postgres server. The Pgpool module helps in connection pooling and load balancing of SELECT queries on multiple standbys in EDB Postgres clusters. EDB has also invested resources in Pgpool development and supports multiple engineers whose work advances and maintains the tool.

A commonly asked question is how best to integrate the two tools because of the benefits they both provide. In the event an EDB Postgres Master fails and EFM promotes a Standby, DBAs would also like to be able to update the Pgpool about the new Master.

The following are the steps for automating the Pgpool update should EFM execute a Master failover:

On Pgpool server:

  1. Disable the auto-failover of Pgpool by modifying the following parameter in pgpool conf:
backend_flag = 'DISALLOW_TO_FAILOVER'

The above parameter is important in order to avoid a split-brain situation between Pgpool and EFM for Postgres Failover/switchover.

For more information, please refer the following link:

http://www.pgpool.net/docs/latest/en/html/runtime-config-backend-settings.html#RUNTIME-CONFIG-BACKEND-CONNECTION-SETTINGS

  1. Configure the conf.

For more information on PCP settings, please use the following link:

http://www.pgpool.net/docs/latest/en/html/pcp-commands.html

On EDB Postgres database nodes:

  1. Install the Pgpool binaries.

Pgpool binaries come with PCP commands. Users can use the PCP command to notify pgpool about new master through EFM

  1. The following is a sample script which uses the PCP command to notify Pgpool about the new master:
#!/bin/bash

###################################################################################
#title           : EFM fencing script for updating pgpool
#description     : This script executes pcp commands. Therefore pcp & pgpool 
#                : should be available on the server
#author          : Vibhor Kumar (vibhor.aim@gmail.com).
#date            : Jan 5 2018
#version         : 1.0
#notes           : Install Vim and Emacs to use this script.
#                : configure the pcppass file for EFM user and set 
#                : the password correctly
#bash_version    : GNU bash, version 4.2.46(2)-release (x86_64-redhat-linux-gnu)
###################################################################################
# quit on any error
set -e
# verify any  undefined shell variables
set -u

###################################################################################
# Code for notifiying pgpool for promote the standby
###################################################################################

NEW_PRIMARY=$1                              # argument from EFM fencing hook
PCP_USER=enterprisedb                       # PCP user name
PCP_PORT=9051                               # PCP port number as in pgpool.conf
PCP_HOST=pgpool                             # hostname of Pgpool-II  
PGPOOL_PATH=/usr/edb/pgpool3.6              # Pgpool-II installation path
PCPPASSFILE=/var/efm/pcppass                # Path to PCPPASS file

PCP_NODE_COUNT=${PGPOOL_PATH}/bin/pcp_node_count
PCP_NODE_INFO=${PGPOOL_PATH}/bin/pcp_node_info
PCP_PROMOTE=${PGPOOL_PATH}/bin/pcp_promote_node
 
export PCPPASSFILE PCP_USER PCP_PORT PGPOOL_PATH \
       PCP_PROMOTE PCP_NODE_INFO PCP_NODE_COUNT

###################################################################################
# find the number of nodes and search for node-id of NEW_PRIMARY
###################################################################################
NO_OF_NODES=$(${PCP_NODE_COUNT} --host=${PCP_HOST} \
                      --username=${PCP_USER} \
                      --port=${PCP_PORT} \
                      --no-password )

for (( i=0 ; i < ${NO_OF_NODES} ; i++ ))
do
   exists=$(${PCP_NODE_INFO} --host=${PCP_HOST} \
                    --username=${PCP_USER} \
                    --port=${PCP_PORT} \
                    --no-password ${i} |grep ${NEW_PRIMARY}|wc -l)
   if [[ ${exists} -eq 1 ]]; then
      NODE_ID=${i}
      break
   fi
done

###################################################################################
# Promote the specific node id using PCP command
###################################################################################
if [[ ! -z ${NODE_ID} ]]; then
    ${PCP_PROMOTE} --host=${PCP_HOST} \
                   --username=${PCP_USER} \
                   --port=${PCP_PORT} \
                   --no-password \
                   --verbose \
                   ${NODE_ID}
fi
exit 0
  1. Modify the following parameters in properties file of EDB Postgres Servers (Master and Standby):
script.fence=/usr/efm-2.1/bin/efm_pgpool_notify %p

Please note the above script only covers the failover scenario of an EDB Postgres cluster. However, the above script can be extended to cover a switchover use case too.

Let’s have a look at how it works in an environment:

  1. Connect to the Pgpool server and verify the nodes:
[root@master /]# psql -h pgpool
psql.bin (9.6.6.11)
Type "help" for help.

edb=# show pool_nodes;
 node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay 
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------
 0       | master   | 5444 | up     | 0.500000  | primary | 13         | true              | 0
 1       | standby  | 5444 | up     | 0.500000  | standby | 19         | false             | 0
(2 rows)

Above output shows that we have Master and Standby EDB Postgres nodes and both are up and running.

  1. Connect to the EDB Postgres nodes and check the status of EFM. The following is a snapshot:
[root@master /]# /usr/efm-2.1/bin/efm cluster-status efm
Cluster Status: efm
VIP: 

    Agent Type  Address              Agent  DB       Info
    --------------------------------------------------------------
    Witness     pgpool               UP     N/A       
    Standby     standby              UP     UP        
    Master      master               UP     UP        

Allowed node host list:
    master pgpool standby

Membership coordinator: master

Standby priority host list:
    standby

Promote Status:

    DB Type     Address              XLog Loc         Info
    --------------------------------------------------------------
    Master      master               0/E0001E0        
    Standby     standby              0/E0001E0        

    Standby database(s) in sync with master. It is safe to promote.
    1. Perform a failover as shown below:
/usr/efm-2.1/bin/efm promote efm
Promote/switchover command accepted by local agent. Proceeding with promotion and will reconfigure original master. Run the 'cluster-status' command for information about the new cluster state.
  1. Connect to Pgpool node and verify the status of the EDB Postgres nodes:
[root@master /]# psql -h pgpool
psql.bin (9.6.6.11)
Type "help" for help.

edb=# show pool_nodes;
 node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay 
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------
 0       | master   | 5444 | down   | 0.500000  | standby | 13         | false             | 0
 1       | standby  | 5444 | up     | 0.500000  | primary | 20         | true              | 0
(2 rows)

The above shows that the Master server which was primary is now down and the Standby is promoted to primary.

Monitoring Amazon PostgreSQL RDS with PEM version 6.0

The EDB Postgres Enterprise Manager is an enterprise management tool designed to tune, manage, and monitor a large number of Postgres deployments on servers on-premises or spread out across geographical boundaries. It is the cornerstone of the Management Suite of tools that is integrated into the EDB Postgres Platform from EnterpriseDB® (EDB) and it emerged to solve a great many challenges database administrators faced daily. (The blog, How to Be the Master of Your Database Domain, explored EDB Postgres Enterprise Manager in greater detail.)

EDB customers use EDB Postgres Enterprise Manager for the following key features:

  1. Management of multiple EDB Postgres Advanced Server deployments
  2. Capacity planning
  3. Performance monitoring
  4. Alert Management
  5. Auditing violation
  6. Database Analysis
  7. Postgres Expert

Recently one of EDB’s customers acquired a company that was hosting its data on Amazon PostgreSQL RDS. The customer wanted to leverage EDB Postgres Enterprise Manager to monitor the database deployments on PostgreSQL RDS.

At the time, this request was considered a real possibility. A significant amount of development expertise has gone into EDB Postgres Enterprise Manager to create a sophisticated, enterprise-class tool for managing vast deployments of Postgres. As for remote monitoring, EDB Postgres Enterprise Manager had the capacity to monitor remote databases using an agent on a designated server. EDB Postgres Enterprise Manager also provides capabilities for users, such as database administrators, to create their own custom probes, custom dashboards, and custom alerts. Combined, all of these capabilities mean EDB Postgres Enterprise Manager ultimately provides the kinds of control and flexibility DBAs needed for managing databases that are hosted in different environments.

EDB worked closely with the customer to develop a process for utilizing their subscription for the EDB Postgres Enterprise Manager, which is part of the EDB Postgres Platform, with PostgreSQL databases on Amazon RDS. The steps were formalized into a repeatable process so others could follow suit and enjoy the benefits of EDB Postgres Enterprise Manager in their cloud deployments.

The following are the steps for using EDB Postgres Enterprise Manager with Amazon PostgreSQL RDS:

Amazon PostgreSQL RDS comes with an rds_superuser, which is not the same as a super user in PostgreSQL. Rds_super role has a lot of limitations, and they effect the monitoring capabilities.

To use EDB Postgres Enterprise Manager with PostgreSQL RDS, follow these steps:

  1. Use the following function to modify the EDB Postgres Enterprise Manager probes so that it can leverage user defined views on some catalog tables:
CREATE OR REPLACE FUNCTION strip_pg_catalog_from_probe()
RETURNS boolean
LANGUAGE plpgsql
AS
$function$
 DECLARE
   rec RECORD;
 BEGIN
   CREATE TABLE pem.probe_code_backup AS SELECT id, probe_code FROM pem.probe WHERE id IN (1, 2, 3, 9, 10, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 23, 24, 25, 29, 36, 39, 47, 52, 53, 54);
   FOR rec IN SELECT id, probe_code FROM pem.probe WHERE id IN (1, 2, 3, 9, 10, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 23, 24, 25, 29, 36, 39, 47, 52, 53, 54)
   LOOP
      UPDATE pem.probe SET probe_code=replace(rec.probe_code,'pg_catalog.','') where id=rec.id;
   END LOOP;
   RETURN true;
END;
$function$;
  1. After creating the above function, execute the above function in the EDB Postgres Enterprise Manager server database as shown below:

psql -h localhost –c “SELECT strip_pg_catalog_from_probe()” pem

  1. Connect to PostgreSQL RDS database as rds_super user and execute the following SQL in Postgres and the user’s defined database:
CREATE USER monitor PASSWORD 'password';
GRANT rds_superuser TO monitor;
CREATE SCHEMA rds_pem_views;
SET search_path TO rds_pem_views;
CREATE OR REPLACE VIEW pg_database AS SELECT oid,* FROM pg_catalog.pg_database WHERE datname <> 'rdsadmin';

CREATE OR REPLACE VIEW pg_tablespace AS SELECT oid, * FROM pg_tablespace WHERE spcname <> 'pg_global';

CREATE FUNCTION rds_pem_views.pg_ls_dir(text)
RETURNS TEXT
LANGUAGE sql
AS
$function$
   SELECT ''::TEXT;
$function$;

GRANT ALL ON FUNCTION rds_pem_views.pg_ls_dir(text) TO monitor;
GRANT ALL ON SCHEMA rds_pem_views TO monitor;
GRANT ALL ON  rds_pem_views.pg_database TO monitor;
GRANT ALL ON  rds_pem_views.pg_tablespace TO monitor;

ALTER USER monitor SET search_path TO rds_pem_views,pg_catalog, "$user", public;

The above SQL will create a database user “monitor” with “rds_superuser” privileges.

As mentioned above, rds_superuser in RDS is not the same as a true superuser in PostgreSQL, therefore a schema with a view for pg_database and for pg_tablespace is created.

Views pg_database and pg_tablespace are created because the rds_superuser role has the following restrictions:

  1. Rds_superuser or normal user in PostgreSQL RDS cannot access rdsadmin database;
  2. Rds_superuser or normal user in PostgreSQL RDS cannot calculate the size of pg_global tablespace;
  3. Rds_superuser cannot use pg_ls_dir to list the WAL files in pg_xlog directory.
  1. After executing SQLs, mentioned in step three, we can now use the database user monitor for EDB Postgres Enterprise Manager monitoring.
  2. For remote monitoring of PostgreSQL RDS, use the following link to add the following server:

https://www.enterprisedb.com/docs/en/6.0.2/pemgetstarted/getting_started_guide.1.14.html#

When you are following the documentation, please remember to click on “remote monitoring” and choose the EDB Postgres Enterprise Manager agent (PEM Agent) for remote monitoring as shown below:

snap1.png

In the above Bound Agent is pemagent running on PEM Server. Also note that the database is monitor.

After performing the above steps, the EDB Postgres Enterprise Manager server can monitor PostgreSQL RDS, and it can collect all database level statistics. Also, DBAs and other users can configure all database level alerts like autovacuum, etc. EDB Postgres Enterprise Manager provides 63 templates for database level alerts. It also provides flexibility for DBAs and other users to create their own alerts based on custom probes.

Now, some users might have questions or concerns about about system level information from Amazon PostgreSQL RDS, and whether EDB Postgres Enterprise Manager can monitor that data. The answer is yes.

As an example, the following are the steps for configuring EDB Postgres Enterprise Manager to monitor the CPU utilization of PostgreSQL RDS:

  1. Install the Amazon Cloudwatch tools on the EDB Postgres Enterprise Manager Server using following command:
wget http://ec2-downloads.s3.amazonaws.com/CloudWatch-2010-08-01.zip
unzip CloudWatch-2010-08-01.zip

For more information on setting the Amazon CloudWatch command line, please use the following link:

http://docs.aws.amazon.com/AmazonCloudWatch/latest/cli/SetupCLI.html

  1. Create an IAM user on the AWS panel, and attach the managed policy “CloudWatchReadOnlyAccess”.

For more information, please refer to the following link:

http://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/iam-identity-based-access-control-cw.html

  1. Create an AWS Credential File using the following sample:
AWSAccessKeyId=
AWSSecretKey=

For more information on AWS Credential file, please use the following link:

http://docs.aws.amazon.com/cli/latest/userguide/cli-chap-getting-started.html

  1. Change the permission of the above Credential file using the following command:
chmod 600 credential_file
  1. Create a following script to get the CPUUtiliztion information from Amazon CloudWatch:
#!/bin/bash
export AWS_CREDENTIAL_FILE=/usr/local/aws/credential/credential-file-path
export AWS_CLOUDWATCH_HOME=/root/CloudWatch-1.0.20.0
export PATH=$PATH:$AWS_CLOUDWATCH_HOME/bin
export JAVA_HOME=/usr/lib/jvm/jre
export AWS_CLOUDWATCH_URL=http://monitoring.us-west-2.amazonaws.com
echo -e "avg_cpu\tminimum_cpu\tmax_cpu"
mon-get-stats CPUUtilization --namespace="AWS/RDS" --dimensions="DBInstanceIdentifier=postgres" --statistics Average,Minimum,Maximum|tail -n 1|awk '{print $3"\t"$4"\t"$5}'
  1. After creating the above script, make the script executable as given below:
chmod +x rds_cpu.sh

Now you are ready to create a custom probe to use this script for monitoring with EDB Postgres Enterprise Manager.

To create a custom probe, please visit the following link:

https://www.enterprisedb.com/docs/en/6.0.2/pemgetstarted/getting_started_guide.1.29.html#pID0E0TBB0HA

Below is a snapshot of a custom probe which I have used:

snap2

By using above probes, user can create their custom alerts in EDB Postgres Enterprise Manager for monitoring CPU utilization on Amazon PostgreSQL RDS. Also they can use data for creating a custom dashboard for RDS.

The following is a snapshot of RDS CPU utilization from EDB Postgres Enterprise Manager.

snap3

If you want to know more about Custom Alerts and Custom Dashboards, please use the following links:

  1. Creating Custom Alerts templates

https://www.enterprisedb.com/docs/en/6.0.2/pemgetstarted/getting_started_guide.1.30.html#pID0E0E40HA

  1. Creating Custom Alerts

https://www.enterprisedb.com/docs/en/6.0.2/pemgetstarted/getting_started_guide.1.30.html#pID0E0Z60HA

  1. Creating Custom Charts

https://www.enterprisedb.com/docs/en/6.0.2/pemgetstarted/getting_started_guide.1.28.html#

  1. Creating Custom Ops Dashboard

https://www.enterprisedb.com/docs/en/6.0.2/pemgetstarted/getting_started_guide.1.27.html#pID0E0HFB0HA

 

As you can see, the EDB Postgres Enterprise Manager is very flexible and customizable to meet specific needs.