I have a CLI command like this:
$ psql -U postgres < <(psql -U postgres -Atc "select 'SELECT pg_terminate_backend(pid)
FROM pg_stat_activity WHERE datname = \''||datname||'\'; /* I WILL ADD MORE QUERIES HERE LATER. */
'from pg_database where
datistemplate=false AND datname != 'postgres';")
Actual Result:
ERROR: syntax error at or near "\"
LINE 2: FROM pg_stat_activity WHERE datname = \''||datname||'\';
^
Expected Result:
WHERE datname = 'database_name'
-- instead of
WHERE datname = \''||datname||'\'
This will print and echo to psql
for each found database instance. However, datname
is not properly populated. It is because of '
(Escape?). How can I terminate open connections to the databases other than fix this command? Why can't I populate postgres
in this CLI command||datname||
here?
Goal:
My goal is to drop all connections if any, to all databases other than postgres
database. But doing this by in CLI/Bash. Not by logging in to sudo -u postgres
. I want to drop all connections via CLI-only. I will use this in CI/CD system therefore user interaction is not possible.