Tuesday, 25 March 2014

psql: FATAL: parameter "listen_addresses" cannot be changed without restarting the server


We faced an issue on one of the machine, where the postgresql commands are not runnning as root. Always getting the below error while executing any psql command

psql: FATAL:  parameter "listen_addresses" cannot be changed without restarting the server.

Initially I thought it was some thing to do with the settings on the pg_hba.conf file and postgresql.conf file where the variable listen_address is specified .The variable listen_adresses has been changed on postgresql.conf from

listen_addresses = '*'


listen_addresses = ''

Then restarted the postgresql service. But still got the same error. One of the strange thing is that I am able to connect to postgresql command line from another machine to the machine having issue, but not from with in the machine as root.

Finally On checking, I found that there was a variable named PGOPTIONS was the issue.  Below is the steps I had done to resolve it.

Find the value of variable PGOPTIONS on the command line using the command


SO I found out that the variable was defined in the server some where . I found out the variable on the environment file /etc/environment.

I changed the value from -i to Null value on /etc/environment

vi /etc/environment

 Now the command echo $PGOPTIONS showed the null value

And now I am able to execute any psql command operations like pg_dump.