HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Set PostgreSQL config from command line with ALTER SYSTEM

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
postgresqllinewithsystemconfigcommandfromalterset

Problem

I need to write a shell/bash script that automatically changes some PostgreSQL configurations.

Here's the command that I wrote:
sudo -u postgres psql -U postgres -d postgres -c "
ALTER SYSTEM SET listen_addresses = '127.0.0.1';
ALTER SYSTEM SET max_connections = '200';
ALTER SYSTEM SET shared_buffers = '24GB';
ALTER SYSTEM SET work_mem = '128MB';
...
"


However I get this error:

ERROR:  ALTER SYSTEM cannot run inside a transaction block


What can I do to solve this error?

Solution

Use several -c options:
psql -c "ALTER SYSTEM SET param1 = 'val1'" \
-c "ALTER SYSTEM SET param2 = 'val2'" \
-c "SELECT pg_reload_conf()"


The final function call is required to activate the change.

Context

StackExchange Database Administrators Q#322050, answer score: 8

Revisions (0)

No revisions yet.