Cannot make changes into Postgres config file by script

Question

I am trying to create a script for changing settings in PostgreSQL and then reloading config

I need to change by script, the parameter value from something which was before, to "all" ;

I've tried the following:

/xxxxx/../../bin/psql -U postgres -c "ALTER SYSTEM SET log_statement TO 'all';

seems it doesn't work

After, I need to restart the service, where I will have to make su - postgres And run following command

/xxxxx/../../bin/pg_ctl reload -D /xxxx/folder_of_postgres_conf file

Any ideas on how to write the parameter change command?

"doesn't work" isn't a valid Postgres error message. But your first command is missing a ' at the end — user330315
– user330315, Commented Dec 7, 2020 at 6:38

user330315 · Accepted Answer · 2020-12-07 06:40:08Z

2

I would use pg_reload_conf() and pass both SQL statements as a "heredoc" so that you don't need to worry about embedding quotes in quotes:

/xxxxx/../../bin/psql -U postgres << EOF
  ALTER SYSTEM SET log_statement TO 'all';
  select pg_reload_conf();
EOF

answered Dec 7, 2020 at 6:40

user330315

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

1 Answer 1