Ansible Pgsql Role

October 1, 2017 No comments Article

rtshome.pgsql is a role available in Ansible Galaxy that provides four new ansible modules for Postgresql:

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

For additional docs look project’s wiki: https://github.com/rtshome/ansible_pgsql/wiki

Installation

$ ansible-galaxy install rtshome.pgsql

Requirements

It requires psycopg2 installed as per Ansible’s PostgreSQL modules: http://docs.ansible.com/ansible/latest/list_of_database_modules.html#postgresql

Example Playbook

Sample playbook that:

creates the table config in acme database
ensures that a row is present in config table
performs a SELECT query on config and stores results in query var
execute a command removing all records in logs table

YAML
 - hosts: servers
      tasks:
        - postgresql_table:
            database: acme
            name: config
            state: present
            columns:
              - {
                name: key,
                type: text,
                null: False
              }
              - {
                name: value,
                type: text,
                null: False
              }
            primary_key:
              - key
        
        - postgresql_row:
            database: acme
            table: config
            row:
              key: env
              value: production

        - postgresql_query:
            database: acme
            query: SELECT * FROM config WHERE env = %(env)s
            parameters:
              env: production 
          register: query

        - postgresql_command:
            database: acme
            command: "TRUNCATE logs"
      roles:
         - rtshome.pgsql

				
					
				1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

						 - hosts: servers
      tasks:
        - postgresql_table:
            database: acme
            name: config
            state: present
            columns:
              - {
                name: key,
                type: text,
                null: False
              }
              - {
                name: value,
                type: text,
                null: False
              }
            primary_key:
              - key
        
        - postgresql_row:
            database: acme
            table: config
            row:
              key: env
              value: production
 
        - postgresql_query:
            database: acme
            query: SELECT * FROM config WHERE env = %(env)s
            parameters:
              env: production 
          register: query
 
        - postgresql_command:
            database: acme
            command: "TRUNCATE logs"
      roles:
         - rtshome.pgsql

					

			

Categories: PGDay2017, Planet PostgreSQL, PostgreSQL, Talks

Denis Gasparin

Hardware and Software Solutions for your Company

Contact me: denis@gasparin.net

Ansible Pgsql Role

Installation

Requirements

Example Playbook