4

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?

collimarco
  • 653
  • 2
  • 9
  • 20

1 Answers1

8

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.

Laurenz Albe
  • 61,070
  • 4
  • 55
  • 90