Scripts - Add / Remove Parameters from postgresql.conf

Products

• VMware Tanzu Greenplum

Issue/Introduction

This article describes how to remove the invalid entry from all the segments at one go if you have added a wrong entry in the postgresql.conf and how to add/remove the entry to/from postgresql.conf in all the segment cluster.

 

Resolution

The below scripts need the master database to be up, so start the database in master only mode.

If the master database is having issue with invalid entry in postgresql.conf , manually remove it from postgresql.conf and then start the master using

gpstart -am

-- Take the backup of the postgresql.conf file

PGOPTIONS='-c gp_session_role=utility' psql template1 -Atc " SELECT 'ssh '||hostname 
       ||' \"cp '|| f.fselocation 
       ||'/postgresql.conf ' 
       || f.fselocation 
       || '/postgresql.conf.backupcopy\"' 
FROM pg_filespace_entry f , pg_tablespace t , gp_segment_configuration c
WHERE f.fsefsoid=t.spcfsoid 
AND c.dbid=f.fsedbid
AND t.oid=1663 " > /tmp/backup_postgresql.conf

Execute the script using

/bin/sh /tmp/backup_postgresql.conf

-- To add a entry into the postgresql.conf

Ideally you can use "gpconfig" to add the entry , for any reason you are unable to execute the gpconfig or if gpconfig fails then you can use the below script.

Replace the <parameter-to-add=value> with parameter you want to add to the postgresql.conf

PGOPTIONS='-c gp_session_role=utility' psql template1 -Atc " SELECT 'ssh '||hostname 
       ||' \"echo <parameter-to-add=value> >> '|| f.fselocation 
       ||'/postgresql.conf\"' 
FROM pg_filespace_entry f , pg_tablespace t , gp_segment_configuration c
WHERE f.fsefsoid=t.spcfsoid 
AND c.dbid=f.fsedbid
AND t.oid=1663 " > /tmp/add_postgresql.conf

Execute the script using

/bin/sh /tmp/add_postgresql.conf

-- To remove a entry from the postgresql.conf

Replace the <parameter-to-remove> with parameter or something that you can uniquely identify the parameter, which you wish to remove from the postgresql.conf

PGOPTIONS='-c gp_session_role=utility' psql template1 -Atc " SELECT 'ssh '||hostname 
       ||' \"grep -v <parameter-to-remove> '|| f.fselocation 
       ||'/postgresql.conf.backupcopy > '
       || f.fselocation 
       || '/postgresql.conf\"' 
FROM pg_filespace_entry f , pg_tablespace t , gp_segment_configuration c
WHERE f.fsefsoid=t.spcfsoid 
AND c.dbid=f.fsedbid
AND t.oid=1663 " > /tmp/remove_postgresql.conf

Execute the script using

/bin/sh /tmp/remove_postgresql.conf

Additional Information

+ Environment:

Pivotal Greenplum Database (GPDB) all versions