Modifying postgresql.conf through shell?

Question

I want to modify a postgresql.conf parameter through the shell. From the documentation I can see that I can use the postgres command with the -c flag.

However, on my attempt, for example,

postgres -c autovacuum=off

postgres returns:

Execution of PostgreSQL by a user with administrative permissions is not permitted.

The server must be started under an unprivileged user ID to prevent possible system security compromises. See the documentation for more information on how to properly start the server.

How can I overcome this or what is the correct procedure? Also, I don't really mind for security compromises.

To be clear, postgres -c foo=bar (when it works) doesn't update postgresql.conf -- instead, it starts the postgres server, with foo overridden to have the value bar only that one time the server is started (but leaving foo at its normal value in the ongoing configuration for future invocations). — Charles Duffy
– Charles Duffy, Commented Apr 21, 2017 at 0:02
BTW, it's not clear what "don't really mind for" means. Do you mean to say you don't mind compromises -- that is, you find compromises acceptable? — Charles Duffy
– Charles Duffy, Commented Apr 21, 2017 at 0:03
If you want to modify the config file, just append to it with echo, or use sed to rewrite it. Or enable an include_file or include_directory and write new parameters there. Or use ALTER SYSTEM SET. — Craig Ringer
– Craig Ringer, Commented Apr 21, 2017 at 2:43
@CharlesDuffy I am doing something personal, locally and temporarily so I don't need this extra protection which the quoted message is referring to turned on. I thought this was quite clear. Also thanks for the information. — audlift-bit
– audlift-bit, Commented Apr 21, 2017 at 21:33
@CraigRinger Thanks, your solutions are very neat. That tackles what I had in mind while writing this question and much more. — audlift-bit
– audlift-bit, Commented Apr 21, 2017 at 21:36

Emmanuel Espitia · Accepted Answer · 2021-08-24 14:40:45Z

1

Given the differences on the underlying OS, I usually prefer to do this via PostgreSQL itself, which comes handy when you're dealing with a managed service that do not give you filesystem access, like so:

sudo -u postgres psql -U postgres -d database_name -c "alter system set postgresql_parameter = 'new_value';"

As an example when I have to install TimeScaleDB extension, I can do:

sudo -u postgres psql -U postgres -d database_name -c "alter system set shared_preload_libraries = 'timescaledb';"
sudo service postgresql restart
sudo -u postgres psql -U postgres -d database_name -c "create extension if not exists timescaledb;"

answered Aug 24, 2021 at 14:40

Emmanuel Espitia

811 silver badge2 bronze badges

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

Collectives™ on Stack Overflow

Modifying postgresql.conf through shell?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related