Login and executing PostgreSQL script automation using PostgreSQL role in Ansible playbook

Question

Required some help on Ansible script

Login and executing some samples required using Ansible playbook.

No need to install PostgreSQL.

We tried with below command but it's not working.

- name: Login to DB and run command 
  command:PGPASSWORD='{{pgpass_filepath}}'; psql -U "{{ db_user }}" -d "{{ db_name }}" -h "{{ db_host }}" -p 5555-c "select count(*) from student";

kb-0 · Accepted Answer · 2018-12-10 08:52:33Z

1

You need to keep in mind that you have to escape the bracket characters, also the ; at the end of the sql statement needs to be inside the escaped brackets. I would also advise you to let ansible execute the shell command with a white space at the beginning so the command with the password won't be logged to the shell history.

- name: Login to DB and run command
  shell: " PGPASSWORD='{{pgpass_filepath}}' psql -U {{ db_user }} -d {{ db_name }} -h {{ db_host }} -p 5555 -c \"select count(*) from student;\""

This should work. Give it a try.

answered Dec 10, 2018 at 8:52

kb-0

985 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

shaik Over a year ago

Hi Thanks for your reply.I tried with your suggestion but I am getting the following error now "stderr": "/bin/sh: psql: command not found", "stdout

shaik Over a year ago

Hi Thanks for your reply.I tried with your suggestion but I am getting the following error now "stderr": "/bin/sh: psql: command not found", "stdout

kb-0 Over a year ago

You need to have the psql client installed on the machine where you execute the playbook. On Ubuntu e.g. packages.ubuntu.com/search?keywords=postgresql-client

kb-0 Over a year ago

To be clearer. It has to be installed on the host you defined in the hosts file and which is used as target of the playbook.

shaik Over a year ago

HI Manually we tried from pgsql command its for fine for the same user. But through ansible playbook its not working.

|

Collectives™ on Stack Overflow

Login and executing PostgreSQL script automation using PostgreSQL role in Ansible playbook

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related