how change postgresql config via ssh

Question

I have a project in php, there I connect to the server via ssh and my task is to change the postgresql.conf parameters. I tried this:

psql -Upgadmin -p 11935 -d postgres -c array_nulls=off
ERROR:  syntax error at or near "array_nulls"
LINE 1: array_nulls=off
        ^

or should I change the settings in postgresql.conf using sed, which command should I use?

Mike Sherrill 'Cat Recall' · Accepted Answer · 2015-03-10 13:59:07Z

1

Any server setting that you succeed in changing with psql will only last as long as your session. To make the change persist, you have to edit postgresql.conf, either with your favorite text editor (vim, emacs, etc.) or with a utility program (sed, awk, etc.). Everything that follows assumes that you have suitable privileges to edit postgresql.conf.

Your first job is to find it. Different Linux distributions store it in different places. In Ubuntu, it's in

/etc/postgresql/9.4/main
                ^^^

Note the version number. Different for each version of PostgreSQL, of course. If I wanted to change a setting, I'd do this after putting the file under version control.

$ sudoedit /etc/postgresql/9.4/main/postgresql.conf
[sudo] password for mike:

Then find the setting and change it. Here's what the relevant section of postgresql.conf looks like here.

#------------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#------------------------------------------------------------------------------

# - Previous PostgreSQL Versions -

#array_nulls = on

I'd change the setting to

array_nulls = off    # array_nulls = on

This kind of change usually requires either a reload or a restart of the PostgreSQL dbms.

answered Mar 10, 2015 at 13:59

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Danil Over a year ago

Nice answer! Thank you. And how do I change the setting through the sed? need to find the line with the parameter, remove it and insert a new one with the new value of the parameter.

Collectives™ on Stack Overflow

how change postgresql config via ssh

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related