# ansible task to cat a file with sql commands and pipe the commands into database using psql with synchronise module (remotely)

**URL:** <https://forum.ansible.com/t/ansible-task-to-cat-a-file-with-sql-commands-and-pipe-the-commands-into-database-using-psql-with-synchronise-module-remotely/31728>\
**Category:** Ansible Developer\
**Created:** [November 26, 2019, 5:12pm UTC](https://forum.ansible.com/t/ansible-task-to-cat-a-file-with-sql-commands-and-pipe-the-commands-into-database-using-psql-with-synchronise-module-remotely/31728 "2019-11-26T17:12:24Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jonin](https://avatars.discourse-cdn.com/v4/letter/j/87869e/32.png) [@Jonin](https://forum.ansible.com/u/Jonin)\
**Post date:** [November 26, 2019, 5:12pm UTC](https://forum.ansible.com/t/ansible-task-to-cat-a-file-with-sql-commands-and-pipe-the-commands-into-database-using-psql-with-synchronise-module-remotely/31728/1 "2019-11-26T17:12:24Z")

</div>

Hi,

I have created the following task to be run on a remote host:

- hosts: nodes  
remote\_user: root  
tasks:
- name: Step Seven - remove all current postcode data and then re-populate the database with most recent data  
become\_user: postgres  
become: yes  
command: “cat /var/lib/pgsql/temp/truncate\_sql /var/lib/pgsql/temp/postcodesio-\* | psql postcodesiodb”

but it is failing with the below memory issue. A google makes me suspect it is because I am trying to make ansible handle too large a file (a postcode database) which it is loading into RAM. : [https://stackoverflow.com/questions/41367278/how-to-fix-memory-error-in-ansible/51320108](https://stackoverflow.com/questions/41367278/how-to-fix-memory-error-in-ansible/51320108). The suggestion is to use the synchronise module or rsync directly as a command. For either I am struggling to visualise the syntax / structure to do this. Any help much appreciated.

TASK [Step Seven - remove all current postcode data and then re-populate the database with most recent data] \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
fatal: [[vm015018.bskyb.com](http://vm015018.bskyb.com)]: FAILED! =\> {“changed”: false, “module\_stderr”: “Shared connection to [vm015018.bskyb.com](http://vm015018.bskyb.com) closed.\r\n”, “module\_stdout”: “Traceback (most recent call last):\r\n File "/tmp/ansible\_ar0FKd/ansible\_module\_command.py", line 213, in \r\n main()\r\n File "/tmp/ansible\_ar0FKd/ansible\_module\_command.py", line 207, in main\r\n module.fail\_json(msg=‘non-zero return code’, \*\*result)\r\n File "/tmp/ansible\_ar0FKd/ansible\_modlib.zip/ansible/module\_utils/basic.py", line 2294, in fail\_json\r\n File "/tmp/ansible\_ar0FKd/ansible\_modlib.zip/ansible/module\_utils/basic.py", line 2273, in \_return\_formatted\r\n File "/tmp/ansible\_ar0FKd/ansible\_modlib.zip/ansible/module\_utils/basic.py", line 2227, in jsonify\r\n File "/tmp/ansible\_ar0FKd/ansible\_modlib.zip/ansible/module\_utils/basic.py", line 782, in jsonify\r\n File "/usr/lib64/python2.7/json/ **init**.py", line 250, in dumps\r\n sort\_keys=sort\_keys, \*\*kw).encode(obj)\r\n File "/usr/lib64/python2.7/json/encoder.py", line 207, in encode\r\n chunks = self.iterencode(o, \_one\_shot=True)\r\n File "/usr/lib64/python2.7/json/encoder.py", line 270, in iterencode\r\n return \_iterencode(o, 0)\r\nMemoryError\r\n”, “msg”: “MODULE FAILURE”, “rc”: 1}  
to retry, use: --limit @/home/sco94/mwe-playbooks/playbooks/patching/postcodes.retry

---

<div class="post-metadata">

**Author:** ![tkuratom](https://avatars.discourse-cdn.com/v4/letter/t/b19c9b/32.png) [@tkuratom](https://forum.ansible.com/u/tkuratom)\
**Post date:** [November 26, 2019, 6:34pm UTC](https://forum.ansible.com/t/ansible-task-to-cat-a-file-with-sql-commands-and-pipe-the-commands-into-database-using-psql-with-synchronise-module-remotely/31728/2 "2019-11-26T18:34:33Z")

</div>

What happens if you use shell instead of command?

I'm not 100% certain but I think what's happening is that since you're  
using the ansible command module instead of the shell module, it is  
running the equivalent of:  
&nbsp;&nbsp;cat '/var/lib/pgsql/temp/truncate\_sql'  
'/var/lib/pgsql/temp/postcodesio-\*' '|' 'psql' 'postcodesiodb'

That's sending the entire output of the first couple files to stdout  
which the command module is then buffering to send back to the  
controller.

If you use the shell module then the pipe will actually be interpreted  
by the shell and so it will do what you want. (If I'm wrong about why  
the MemoryError is happening, there might be another problem in  
addition but I am hopeful that it's all tied together).

-Toshio

---

<div class="post-metadata">

**Author:** ![Jonin](https://avatars.discourse-cdn.com/v4/letter/j/87869e/32.png) [@Jonin](https://forum.ansible.com/u/Jonin)\
**Post date:** [November 27, 2019, 11:08am UTC](https://forum.ansible.com/t/ansible-task-to-cat-a-file-with-sql-commands-and-pipe-the-commands-into-database-using-psql-with-synchronise-module-remotely/31728/3 "2019-11-27T11:08:36Z")

</div>

Thank you Toshio.

The use of shell has worked perfectly. Much appreciated.