Pivotal Knowledge Base

Follow

How to add entries in all the pg_hba.conf files that are part of the GPDB cluster

Environment

Product Version
Pivotal Greenplum (GPDB) All versions 

Purpose

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.

Procedure

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.

  • 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.

Comments

Powered by Zendesk