Run a PostgreSQL script using Ansible

Question:

I am looking for a way to run a Postgres script using Ansible, 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:

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

Answer:

Why it doesn’t work

This:

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:

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:

… 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:

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.

Leave a Reply