patternsqlMinor
Set PostgreSQL config from command line with ALTER SYSTEM
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:
However I get this error:
What can I do to solve this error?
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 blockWhat can I do to solve this error?
Solution
Use several
The final function call is required to activate the change.
-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.