Using Ansible to Set Up PostgreSQL

Wikipedia defines configuration management as a systems engineering process for establishing and maintaining consistency of a product's performance. Over the years, there has been lot of effort going into softwares that help orchestration/configuration of remote servers.

Here is a basic plot showing the popularity of major configuration-management-tools, as per google trends :

View full report in Google Trends

As from the trends, puppet beats all the tools, but in this post we are going to talk about Ansible. Ansible unlike puppet, has neither a special master server, nor special agent executables to install. Everythin can be done from the desk of the sysadmin.

Ansible is a model-driven configuration management, multi-node and remote task execution system. It uses SSH by default, so there is no special software to be installed on the nodes you manage.

The name "ansible" is taken from the science fiction concept of a machine capable of instantaneous or superluminal communication.

In this post, we will be setting up a PostgreSQL server on a host.

Installing Ansible


sudo yum install ansible


sudo apt-get install software-properties-common
sudo apt-add-repository -y ppa:ansible/ansible
sudo apt-get update
sudo apt-get install -y ansible


sudo easy_install pip
sudo pip install ansible

Test the Connection

ansible all -i username@XX.XX.XX.XXX, -m ping

If you are successful, you will have similar output

username@XX.XX.XX.XXX | success >> {
    "changed": false,
    "ping": "pong"

Inventory File

For Mac create your own Ansible directory and then set the path in an Ansible configuration file:

mkdir -p ~/Ansible/ansible
touch ~/Ansible/ansible/hosts
touch ~/.ansible.cfg

For other Unix based systems, the file is located at /etc/ansible/hosts

Put the following into hosts file:


Open ~/.ansible.cfg file and add the following lines:

inventory = ~/Ansible/ansible/hosts
remote_user = root
ask_sudo_pass= true

If all goes correct, you will see the following

XX.XX.XX.XXX | success >> {
    "changed": false,
    "ping": "pong"

Understanding Basic Terminology


Ansible contains information about the hosts and groups of hosts to be managed in the hosts file. This is also called an inventory file. We just finished with Inventory File.


Ansible playbooks help the management of a remote computer in a scripted way. A group of systems can be configured by passing scripts to those systems, using ansible. These scripts are called playbooks. They are written in YAML format.

Each playbook contains one or more roles that provision one or more hosts by executing tasks.


In our case, we are provisioning a db server, in other cases, it would be db server, mail server, web server, so on and so forth. To manage all these different type of servers, it will become difficult to put the contents in a single file. For this purpose, we can have multiple directories, for multiple purpose(roles).

Roles, are abstractable, and one can inherit from the other. For instance, the web and mail server needs to install basic packages like build-essential, ntp, and supervisor. A common role can be created, and the web & mail server can execute the common tasks, present in the common role.

A typical role structure looks like the following :

|__ defaults

    |__ main.yml - includes information about default variables used by this role

|__ files        - files which need to be deployed to your hosts without any modification.

|__ templates    - using jinja2 templating system, configuration variables can
               be passed to templates and those modified templates will be
               placed on the hosts

|__ tasks        - each play can contain multiple task, and each task can perform multiple actions.
    |__ main.yml

|__ meta         - environment Description, Author, Licensing Attributes etc. are placed.
    |__ main.yml

|__ vars         - variables as username, folder name are stored in vars.
    |__ main.yml

|__ handlers     - tasks which are executed on completion of other tasks.
               think of them as callbacks.
    |__ main.yml

Explore a basic Playbook

- name: Create a PostgreSQL DB server
  hosts: dbserver
  sudo: yes
  sudo_user: root
  remote_user: ubuntu
    - vars/main.yml

  - createdb

The three dashes in the beginning mark the beginning of a YAML file. The first one, "name", is sort of what this playbook intends to do.

db_user: dbusername
db_name: dbname
db_password: dbpassword

Following this, we have a set of key-value pairs that store the value to the variables, written in YAML syntax. We have set the variables for our database user name, database name and database password.


- name: restart postgresql
  service: name=postgresql state=restarted

The above function is a handler. It might be required, to restart the PG server, say once the database is updated. The first one, "name", is sort of a description, mentioning what task is going to be performed. Handlers are invoked, using "notify". A list in a YAML file one item, called "restart postgresql", will invoke the above handler.

Handlers are invoked, using "notify". A list in a YAML file one item, called "restart postgresql", will invoke the above handler.

createdb/tasks/main.yml ---

- name: Ensure bash, OpenSSl, and libssl are the latest versions
  apt: name={{ item }} update_cache=true state=latest
    - bash
    - openssl
    - libssl-dev
    - libssl-doc
  tags: packages

- name: Install PostgreSQL
  apt: name={{ item }} update_cache=true state=installed
    - postgresql
    - postgresql-contrib
    - libpq-dev
    - python-psycopg2
  tags: packages

- name: Ensure the PostgreSQL service is running
  service: name=postgresql state=started enabled=yes

- name: Ensure database is created
  sudo_user: postgres
  postgresql_db: name={{ db_name }}

- name: Ensure user has access to the database
  sudo_user: postgres
  postgresql_user: db={{ db_name }}
               name={{ db_user }}
               password={{ db_password }}

- name: Ensure user does not have unnecessary privileges
  sudo_user: postgres
  postgresql_user: name={{ db_user }}

In the above set of tasks, we accomplish the following steps :

  1. Ensure, bash, OpenSSL and libssl are installed and are latest versions.

  2. Install PostgreSQL

  3. Run the PostgreSQL service

  4. Create the database.

  5. Manage Permission for the User

  6. Test the Database is Available

The directory structure for the files looks like this:

├── postgresql_playbook.yml
├── roles
│   └── createdb
│       ├── handlers
│       │   └── main.yml
│       └── tasks
│           └── main.yml
└── vars
    └── main.yml

To create a PostgreSQL server we can run the above playbook ansible-playbook postgresql_playbook.yml.

If everything goes well, you will have the following console output -

PLAY [Create a PostgreSQL DB server] ******************************************

GATHERING FACTS ***************************************************************
ok: [XX.XX.XX.120]

TASK: [createdb | Ensure bash, OpenSSl, and libssl are the latest versions] ***
ok: [XX.XX.XX.120] => (item=bash,openssl,libssl-dev,libssl-doc)

TASK: [createdb | Install PostgreSQL] *****************************************
ok: [XX.XX.XX.120] => (item=postgresql,postgresql-contrib,libpq-dev,python-psycopg2)

TASK: [createdb | Ensure the PostgreSQL service is running] *******************
ok: [XX.XX.XX.120]

TASK: [createdb | Ensure database is created] *********************************
ok: [XX.XX.XX.120]

TASK: [createdb | Ensure user has access to the database] *********************
ok: [XX.XX.XX.120]

TASK: [createdb | Ensure user does not have unnecessary privileges] ***********
ok: [XX.XX.XX.120]

PLAY RECAP ********************************************************************
XX.XX.XX.120               : ok=7    changed=0    unreachable=0    failed=0

The source can be found over here on github..

I hope you liked the post, do let me know, if you are looking for any other content related to Ansible.