query psql

[Pierre Ochsenbein]

Hi Guys

I want to use a SQL query in PostgreSQL Database with ansible and a varibale which is call db_name.
I have the result with my varibale.
My problem is I don't know how interpret ' ' command in ansible with my variable

My simple query works fine:

-bash-4.2$ psql -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'vro03';"
pg_terminate_backend
----------------------
t
(1 row)


Command line in my Playbook:
  command: 'scl enable rh-postgresql10 "psql -Upostgres --command=\"SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = {{ db_name }}\""'


Output Error:

TASK [../roles/removedb : Kill Session on Database] *****************************************************************************************************************************************
fatal: [DEMO0249]: FAILED! => {"changed": false, "cmd": ["scl", "enable", "rh-postgresql10", "psql -Upostgres --command=\"SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = vro03\""], "delta": "0:00:00.043902", "end": "2019-05-16 16:24:47.597238", "msg": "non-zero return code", "rc": 1, "start": "2019-05-16 16:24:47.553336", "stderr": "ERROR:  column \"vro03\" does not exist\nLINE 1: ...ate_backend(pid) FROM pg_stat_activity WHERE usename = vro03\n                                                                  ^", "stderr_lines": ["ERROR:  column \"vro03\" does not exist", "LINE 1: ...ate_backend(pid) FROM pg_stat_activity WHERE usename = vro03", "                                                                  ^"], "stdout": "", "stdout_lines": []}
        to retry, use: --limit @/home/ansible/projects/linuxbootstrap/playbooks/postgresql_removedb.retry
PLAY RECAP **********************************************************************************************************************************************************************************

DEMO0249                   : ok=1    changed=0    unreachable=0    failed=1

[Pierre Ochsenbein]

Any feedback about my error?

--

Cordialement,

Pierre Ochsenbein

Mobile: +33668295394

pierreoc...@gmail.com

[Stefan Hornburg (Racke)]

On 5/17/19 10:54 AM, Pierre Ochsenbein wrote:
> Any feedback about my error?
>

The quotes are missing around vro03 when you execute it with Ansible.

BTW: Ansible 2.8. comes with a postgresql_query module.

Regards
Racke

> *Pierre Ochsenbein*
> Mobile: +33668295394
> pierreoc...@gmail.com <mailto:pierreoc...@gmail.com>
>
> --
> You received this message because you are subscribed to the Google Groups "Ansible Project" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to
> ansible-proje...@googlegroups.com <mailto:ansible-proje...@googlegroups.com>.
> To post to this group, send email to ansible...@googlegroups.com <mailto:ansible...@googlegroups.com>.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/ansible-project/CABG8FoN6Jz1FgNgN2aj5GsF0VJHzMCChXz1D_v-sxdAhva92dQ%40mail.gmail.com
> <https://groups.google.com/d/msgid/ansible-project/CABG8FoN6Jz1FgNgN2aj5GsF0VJHzMCChXz1D_v-sxdAhva92dQ%40mail.gmail.com?utm_medium=email&utm_source=footer>.
> For more options, visit https://groups.google.com/d/optout.


--
Ecommerce and Linux consulting + Perl and web application programming.
Debian and Sympa administration. Provisioning with Ansible.