Pivotal Knowledge Base

Follow

Scripts - Add / Remove parameters from postgresql.conf

Problem

  • Added a wrong entry in the postgresql.conf now my database wont come up, since my database is not up gpconfig is not working to remove the invalid parameter, how do i remove the invalid entry from all the segments at one go ?
  • How to add / remove entry to / from postgresql.conf in all the segment cluster ?

Solution

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

Comments

Powered by Zendesk