3

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.

Dennis
  • 1,805
  • 3
  • 22
  • 41
  • 1
    You could always do `$ sudo systemctl restart postgresql` – Red Cricket Oct 18 '20 at 01:22
  • No. In My CI system, I cannot do `systemctl restart`. Also I will do some other tasks after i drop those databases. I do other parts successfully. I am only unable to terminate connections. In PG13, this is introduced as `DROP DATABASE my_db WITH(FORCE)` however some of my applications still depends on PG12. – Dennis Oct 18 '20 at 01:23

1 Answers1

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.

jjanes
  • 37,812
  • 5
  • 27
  • 34