Terminate all open connections using single CLI command on PostgreSQL 12

Question

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 postgres in this CLI command fix this command? Why can't I populate ||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.

Answer 1

Your single quotes don't need to protected from the shell, because they are already inside double quotes. And to protect them from psql, you double them, not back-whack them. So

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';")

But you could probably use \gexec to make this better, by having only one psql call rather than two.