Run a PostgreSQL script using Ansible

Question

I am looking for a way to run a Postgres script using Ansible. While I found a reasonably good example Here, I need to:

• Run the script as user postgres
• I don't necessarily need to keep a copy of the script on the server so if I need to have a copy, it will only be for temp use.

Can anyone tell me if this is possible and if so an example of running it. Here is what I tried so far using Ansible and it just hung at these points:

 - name: Testing DB to make sure it is available
   command: psql -U bob image
   register: b
 - debug: b

 - name: Verifying Tables exist in Image
   shell: \d image
   register: c
 - debug: c

 - name: Exiting Image DB
   shell: \q
   register: d
 - debug: d

 - name: Going to Agent DB
   command: psql -U bob agent
   register: e
 - debug: e

This always hangs at the first part of it when logging into the image DB.

Answer 1

Why it doesn't work

This:

 - name: Testing DB to make sure it is available
   command: psql -U bob image
   register: b
 - debug: b

 - name: Verifying Tables exist in Image
   shell: \d image
   register: c
 - debug: c

doesn't do what you think it does.

The first command runs psql -U bob image. This starts a psql session. psql waits for input from stdin. Ansible will never send any, it is simply waiting for the command you specified to exit, so it can check the exit code.

So Ansible waits for psql to exit, and psql waits for Ansible to send some input.

Each task in Ansible is independent. The shell or command modules do not change the shell that subsequent commands run in. You simply can't do this the way you expect.

Even if psql exited after the first task (or went to the background), you'd just get an error from the second task like:

bash: d: command not found

So the way you're trying to do this just isn't going to work.

How to do it

You need to run each task as a separate psql command, with a command string:

 - name: Testing DB to make sure it is available
   command: psql -U bob image -c 'SELECT 1;'

 - name: Verifying Tables exist in Image
   command: psql -U bob image -c '\d image'

... or with standard input, except that Ansible doesn't seem to support supplying a variable as stdin to a command.

... or with a (possibly templated) SQL script:

- name: Template sql script
  template:  src="my.sql.j2" dest="{{sometemplocation}}/my.sql"

- name: Execute sql script
  shell: "psql {{sometemplocation}}/my.sql"

- name: Delete sql script
  file: path="{{sometemplocation}}/my.sql" state=absent

Alternately you can use Ansible's built-in support for querying PostgreSQL to do it, but in that case you cannot use the psql client's backslash commands like \d, you'd have to use only SQL. Query information_schema for table info, etc.

Here's how some of my code looks

Here's an example from an automation module I wrote that does a lot with PostgreSQL.

Really, I should just suck it up and write a psql Ansible task that runs commands via psql, rather than using shell, which is awful and clumsy. For now, though, it works. I use connection strings that're assigned from variables or generated using set_fact to reduce the mess a bit and make connections more flexible.

- name: Wait for the target node to be ready to be joined
  shell: "{{postgres_install_dir}}/bin/psql '{{bdr_join_target_dsn}}' -qAtw 'SELECT bdr.bdr_node_join_wait_for_ready();'"

- name: Template pre-BDR-join SQL script
  template:  src="{{bdr_pre_join_sql_template}}" dest="{{postgres_install_dir}}/bdr_pre_join_{{inventory_hostname}}.sql"

- name: Execute pre-BDR-join SQL script
  shell: "{{postgres_install_dir}}/bin/psql '{{bdr_node_dsn}}' -qAtw -f {{postgres_install_dir}}/bdr_pre_join_{{inventory_hostname}}.sql"

- name: Delete pre-BDR-join SQL script
  file: path="{{postgres_install_dir}}/bdr_pre_join_{{inventory_hostname}}.sql" state=absent

- name: bdr_group_join
  shell: "{{postgres_install_dir}}/bin/psql '{{bdr_node_dsn}}' -qAtw -c \"SELECT bdr.bdr_group_join(local_node_name := '{{inventory_hostname}}', node_external_dsn := '{{bdr_node_dsn}}', join_using_dsn := '{{bdr_join_target_dsn}}');\""

- name: Template post-BDR-join SQL script
  template:  src="{{bdr_post_join_sql_template}}" dest="{{postgres_install_dir}}/bdr_post_join_{{inventory_hostname}}.sql"

- name: Execute post-BDR-join SQL script
  shell: "{{postgres_install_dir}}/bin/psql '{{bdr_node_dsn}}' -qAtw -f {{postgres_install_dir}}/bdr_post_join_{{inventory_hostname}}.sql"

- name: Delete post-BDR-join SQL script
  file: path="{{postgres_install_dir}}/bdr_post_join_{{inventory_hostname}}.sql" state=absent

Answer 2

The answer that Craig gives is good, but fails to solve the problem of running the commands as a specific user. That can be done with my additions to his code:

- name: Testing DB to make sure it is available
  become: true
  become_user: postgres
  command: psql -U bob image -c 'SELECT 1;'

- name: Verifying Tables exist in Image
  become: true
  become_user: postgres
  command: psql -U bob image -c '\d image'

Note the "become" and "become_user" parameters. These will tell Ansible to change to the correct user before running the commands.

IMPORTANT: Ansible Version 1.9 and earlier use sudo: yes and sudo_user: postgres instead of become: true and become_user: postgres

Answer 3

Building on the excellent responses above, you can also specify environment variables in your Ansible task as shown below. Note that this assumes you have set up a .pgpass file with the password for the target db.

-   name: Execute some sql via psql
    command: psql -f /path/to/your/sql
    environment:
        PGUSER: "{{ db_user }}"
        PGDATABASE: "{{ db_name }}"
        PGHOST: "{{ db_host }}"
        PGPASS: "{{ pgpass_filepath }}"