Ansible Playbook that will run a query on a postgres SQL db instance

Question

I am new to using Ansible and am looking to understand how to write an Ansible Playbook that will run a query on a postgres SQL db instance as a particular user and display the output in the terminal.

For example:

SELECT * FROM example_db;

Could someone provide a simple example?

yantaq · Accepted Answer · 2015-10-06 18:40:24Z

3

Based on @Christofides' example. I made some modifications and tested.
this works:

- hosts: all
  sudo: yes
  sudo_user: postgres
  tasks:
    - name: Select all from example table
      command: psql -c "SELECT * FROM example_table" example_db
      register: select_all_from_example

    - name : Display example table contents
      debug: msg="{{ select_all_from_example.stdout }}"

Result:

edited Oct 6, 2015 at 18:40

answered Oct 6, 2015 at 18:17

yantaq

4,0682 gold badges37 silver badges34 bronze badges

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

1 Comment

Sysadmin1234 Over a year ago

Nice ! thanks yantaq! This did indeed work. I'll just need to work on the formatting but it looks pretty promising

Antonis Christofides · Accepted Answer · 2015-10-06 07:42:00Z

2

This is an untested example which shows the essentials. It may require tweaking depending on your setup.

- name: Select all from example table
  sudo: yes
  sudo_user: postgres
  command: psql -c "SELECT * FROM example_table" example_db
  register: select_all_from_example

- name: Display example table contents
  debug: msg="{{ select_all_from_example.stdout }}"

answered Oct 6, 2015 at 7:42

Antonis Christofides

7,0483 gold badges49 silver badges67 bronze badges

Comments

Denis Gasparin · Accepted Answer · 2017-10-01 19:58:03Z

0

You can use a new role that provides four new modules:

postgresql_table: ensure that a table is present (or absent) in database
postgresql_row: ensure that a row is present (or absent) in a table
postgresql_query: execute an arbitrary query in database and return results
postgresql_command: execute an arbitrary query in database

Look here for docs: https://github.com/rtshome/ansible_pgsql

answered Oct 1, 2017 at 19:58

Denis Gasparin

111 bronze badge

Comments

Manju N · Accepted Answer · 2021-07-02 07:46:03Z

Using Ansible Shell Module

To create Database and User

Here I am creating : airflow db, user airflow_user and password airflow_password

Set postgres_user(the main user) password in PGPASSWORD variable


  - name: Created Airflow Database and User in Postgres
    shell: /usr/bin/psql --host=10.0.0.39  --dbname=postgres --username=postgres -c "CREATE DATABASE airflow" ; /usr/bin/psql --host=10.0.0.39  --dbname=postgres --username=postgres -c "CREATE USER airflow_user WITH PASSWORD 'airflow_password'" ;  /usr/bin/psql --host=10.0.0.39  --dbname=postgres --username=postgres -c "GRANT ALL PRIVILEGES ON DATABASE airflow TO airflow_user " ;
    environment:
      PGPASSWORD: postgres_password
    register: postgres_query_status
    failed_when: "'already exists' not in postgres_query_status.stderr and  'CREATE' not in postgres_query_status.stdout"
    tags:
    - postgres

Collectives™ on Stack Overflow

Ansible Playbook that will run a query on a postgres SQL db instance

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related