Creation of PostgreSQL Schema with Ansible Playbooks

themaheshbiradar ansible

In this article we will use ansible automation to create schema for postgres database.

We will use all the described aspects of ansible automation which we have seen in first blog.

Prerequisites

  • Postgres DB
  • Ansible Installation
  • Python installation
  • IDE (Any of your choice) with YAML support.

Understanding Modules

As we have seen in first blog, ansible works with pre-defined modules like command etc. Now we need to create a schema in postgres database so we need to find those required modules.

You can find all the module list here on ansible site.

We have two primary tasks and hence we need following modules.

ModuleDescription
postgresql_queryRun PostgreSQL queries
postgresql_userAdd or remove a user (role) from a PostgreSQL server instance

Understanding variables

Ansible has features of variables like any other programming languages where you can define variables and use them at multiple places.

Let’s see how to define and use them in playbook.

Understanding Paybook

---
- name: postgres schema creation using ansible
  hosts: localhost
  vars:
    db: mahesh
    schema: wealthmanagement
    user: wealthmanagement_cloud
    password: do_not_share

  tasks:
  
    - name: Provision Schema
      postgresql_query:
        db: "{{ db }}"
        login_host: localhost
        login_user: mahesh
        query: "CREATE SCHEMA {{ schema }}"
      become: true

    - name: Set credentials for user
      postgresql_user:
        db: "{{ db }}"
        login_host: localhost
        login_user: mahesh
        name: "{{ user }}"
        password: "{{ password }}"
        encrypted: true
        priv: ALL
        role_attr_flags: NOSUPERUSER,NOCREATEDB
      become: true

  • We need to define variables like which database we need to use , which user and what is shema name we need to create. like 
vars:
    db: mahesh
    schema: wealthmanagement
    user: wealthmanagement_cloud
    password: do_not_share
  • Now we need to create schema and we express it in an query , please note here we are going to use already defined varibles.
  - name: Provision Schema
      postgresql_query:
        db: "{{ db }}"
        login_host: localhost
        login_user: mahesh
        query: "CREATE SCHEMA {{ schema }}"
      become: true
  • Now check the schema list before we run the ansible playbook. We don’t see wealthmanagement schema

  • Running Playbook We need to run playbook with -K parameter to pass become password. Meaning we have mentined become = true in playbook which instructs ansible to become other user other than root. It will prompt for password as mentioned below.
mahesh@maheshs-mbp Dev % ansible-playbook schema-creation.yaml -K
BECOME password: 
[WARNING]: No inventory was parsed, only implicit localhost is available
[WARNING]: provided hosts list is empty, only localhost is available. Note that the implicit localhost does not match 'all'
  • Let’s check the Database if Schema has been created. As you can see wealthmanagement schema has been created.

  • As you can see schema has been has created. Similarly you can use extend this to create tables or other objects etc.
  • You can find the code and additional information here at github code.

Conclusion

  • Ansible helps you to create immutable, programmable schema creation in postgres using ansibleto automate your infrastructure provision needs including application deployment etc.

Leave a comment