How to Add Entries in All the pg_hba.conf Files that are Part of the Greenplum Cluster

Products

• VMware Tanzu Greenplum

Issue/Introduction

Sometimes backup or restore fails due to a missing entry in the pg_hba.conf of the segments. However, navigating to each segment host and updating every pg_hba.conf file involves a lot of manual labor.

This document provides a simple method (using Unix commands) that can be used to update all the pg_hba.conf files of the segments that are part of the cluster.

 

Environment

Resolution

The script below can be used to add the entry in the pg_hba.conf file of all the segments.

Note- Please make sure all the commands below are on one line after you are in the gpssh prompt since gpssh doesn't take multiple line commands.

GPDV version 4 and 5:

• Connect to all the hosts via gpssh:

  gpssh -f ~/gpconfigs/hostfile

• Run the script shown below to take a backup of the pg_hba.conf file before any modification:

  ps -ef | grep postgres | grep silent | grep -v grep | awk '{print $10}' | while read line ; do cp $line"/pg_hba.conf" $line"/pg_hba.conf.bk."$(date +"%m-%d-%Y-%H:%M:%S") ; done 

• Use the command listed below to add the entry in the pg_hba.conf file, after editing the line (host    all    gpadmin    172.28.12.250/32    trust) as per your requirements:

  ps -ef | grep postgres | grep silent | grep -v grep | awk '{print $10}' | while read line ; do echo "host     all         gpadmin         172.28.12.250/32       trust" >> $line"/pg_hba.conf" ;done

• Verify the entry using the following:

  ps -ef | grep postgres | grep silent | grep -v grep | awk '{print $10}' | while read line ; do tail -1 $line/pg_hba.conf ;done

• Finally, exit from gpssh prompt, and run "gpstop -u" to reload the pg_hba.conf file.

GPDB version 6:

• Connect to all the hosts via gpssh:

  gpssh -f ~/gpconfigs/hostfile

• Run the script shown below to take a backup of the pg_hba.conf file before any modification:

  ps -ef | grep postgres | grep green | grep -v grep | awk '{print $10}' | while read line ; do cp $line"/pg_hba.conf" $line"/pg_hba.conf.bk."$(date +"%m-%d-%Y-%H:%M:%S") ; done 

• Use the command listed below to add the entry in the pg_hba.conf file, after editing the line (host    all    gpadmin    172.28.12.250/32    trust) as per your requirements:

  ps -ef | grep postgres | grep green | grep -v grep | awk '{print $10}' | while read line ; do echo "host     all         gpadmin         172.28.12.250/32       trust" >> $line"/pg_hba.conf" ;done

• Verify the entry using the following:

  ps -ef | grep postgres | grep green | grep -v grep | awk '{print $10}' | while read line ; do tail -1 $line/pg_hba.conf ;done

• Finally, exit from gpssh prompt, and run "gpstop -u" to reload the pg_hba.conf file.

Additional Information

+ Environment:

Pivotal Greenplum Database (GPDB) all versions