Transfer SQL file, then use it in module to execute queries against PostgreSQL

Archives Ansible Developer
1

Hello all,

I tried to execute SQL queries to set up a database with contents as part of a playbook. After doing so with a combination of the existing modules I created a small module to conditionally execute a SQL file against PostgreSQL.
However it seemed quite complicated to transfer the SQL file to the target host without relying on the copy module. Therefore I’m seeking clarification if I’ve simply overlooked the intended way of doing file transmissions as part of modules.

The module code can be found at https://github.com/AugustusKling/ansible/compare/postgresql_migration and works for my cases. So if anyone tries to do something similar, it might be a good starting point. Basically, it transfers a SQL file to the target host and executes the contained queries if, and only if, another supplied query evaluates to true. My current use is to do database schema migrations as part of application updates.

My questions to you are:

  • lib/ansible/runner/action_plugins/postgresql_migration.py: The file solely handles the SQL file transmission and is essentially a stripped down version of the copy plugin. Is there a way to get rid of this file entirely in favour of reusing the existing API/modules/plugins?
  • library/database/postgresql_migration: This expects the SQL file on the target host and conditionally executes the contained queries. Do you see any use for such a module outwit my own playbooks? In other words do you expect it is worth some time to mature the module?
    Thanks for any feedback.

Augustus

2

“However it seemed quite complicated to transfer the SQL file to the target host without relying on the copy module”

Not reading through your source of the action plugin – if you just want to push a script and run it, the “script” module does that.

This is probably better than trying to make the copy as part of the module – just make a role for ‘postgresql_migration’ that uses that and takes the name of the migration script as a parameter, and you might not even need the module.

(I should also point out there’s support for South migrations in the django module, which is how AWX postgresql migrations are done – though obviously not everyone is using South, it’s useful if someone is).