Software development and beyond

Install and configure PostgreSQL with Ansible

Automating PostgreSQL setup with Ansible

This post describes how to install and configure PostgreSQL database with Ansible, whether that is for a server deployment, or for local development. See my previous post How to automatically set up a development machine with Ansible where I also explain some basic Ansible concepts.

What we will achieve

In this example I will assume that we have an application for which we want to configure the database. We will:

The Ansible playbook is written for a Fedora system, but it is easy to switch to another system by changing the Ansible package module from dnf to another one.

Variables

We will reference vars.yml in our playbook to externalize some configuration and make our playbook more reusable. The variables in question are the database user, the user's password, and the database name.

vars.yml:

---
db_user: app1user
db_password: app1user
db_name: app1

Note that storing passwords in plain text is not the best idea. When needed, use Ansible Vault or environment variables when running the playbook.

The inventory file

The playbook expects that we configure the target machines using an inventory file. Just create ansible.cfg and hosts.ini files with the proper connection information, or modify the playbook itself.

ansible.cfg:

[defaults]
inventory = hosts.ini

hosts.ini:

[app1]
192.168.122.89

PostgreSQL installation

Let's now create the Ansible playbook! First, we need to install the database itself, along with psycopg2-binary Python library that will allow us to use Ansible PostgreSQL modules postgresql_db, postgresql_user, postgresql_privs and postgresql_pg_hba.

---
- hosts: all
become: yes

vars_files:
- vars.yml

pre_tasks:
- name: "Install packages"
dnf: "name={{ item }} state=present"
with_items:
- postgresql
- postgresql-server

- name: "Install Python packages"
pip: "name={{ item }} state=present"
with_items:
- psycopg2-binary

tasks:
- name: "Find out if PostgreSQL is initialized"
ansible.builtin.stat:
path: "/var/lib/pgsql/data/pg_hba.conf"
register: postgres_data

- name: "Initialize PostgreSQL"
shell: "postgresql-setup initdb"
when: not postgres_data.stat.exists

- name: "Start and enable services"
service: "name={{ item }} state=started enabled=yes"
with_items:
- postgresql

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

The playbook references our vars.yml variables and specifies that the tasks should be run on all machines defined in our inventory with hosts: all.

In the pre_tasks section we make sure that all the necessary packages are present on the target system. That includes the PostgreSQL database (postgresql and postgresql-server RPM packages) and the necessary Python package that allows Ansible modules to interact with the database.

After that we define a couple of tasks that will setup the database. The ansible.builtin.stat module is used to check whether pg_hba.conf file exists. If it doesn't, it means that Postgres has to be initialized first by running postgresql-setup initdb.

Finally, we make sure that PostgreSQL is started and enabled to start automatically after computer restarts. The handler restart postgres will be used later. It enables any of the Ansible tasks to invoke a database restart.

We can also install PostgreSQL extensions using the postgresql_ext module.

Creating the database and the database user

In the following section we will:

  tasks:
- name: "Create app database"
postgresql_db:
state: present
name: "{{ db_name }}"
become: yes
become_user: postgres

- name: "Create db user"
postgresql_user:
state: present
name: "{{ db_user }}"
password: "{{ db_password }}"
become: yes
become_user: postgres

- name: "Grant db user access to app db"
postgresql_privs:
type: database
database: "{{ db_name }}"
roles: "{{ db_user }}"
grant_option: no
privs: all
become: yes
become_user: postgres

- name: "Allow md5 connection for the db user"
postgresql_pg_hba:
dest: "~/data/pg_hba.conf"
contype: host
databases: all
method: md5
users: "{{ db_user }}"
create: true
become: yes
become_user: postgres
notify: restart postgres

The Ansible module postgresql_db can be used to manipulate databases, in our case it is used just for creating a new database db_name as set in the vars file. With become: yes and become_user: postgres we tell Ansible to run the task as the postgres system user. This is necessary as other users don't have permissions to manage our PostgreSQL cluster.

Creating a new database user is similar, just done using the postgresql_user module instead. The interesting part is the ability to grant access with postgresql_privs. We need to set the type of object in question (database), its name, roles, and what kind of access we want to set. privs: all will allow the new database user to do everything.

Finally, since many applications connect to databases using a connection string, we can see how the md5 method can be enabled by changing the Postgres config file pg_hba.conf. Also notice the notify: restart postgres, which will execute our handler and restart the database server after the configuration is changed.

Running SQL scripts against the database

By now we have finished both the installation and configuration. For completeness, let's see how to run an arbitrary SQL script as well:

  tasks:
- name: "Load SQL script into a variable"
set_fact:
migration_sql: "{{ lookup('file', 'conf/migration.sql') }}"

- name: "Execute script from variable"
command: "psql {{ db_name }} -c {{ migration_sql }}"
become_user: postgres
register: sql_response_variable

Ansible's lookup function can be used to load various things inside a variable. In this case a script from conf/migration.sql is loaded as migration_sql. The next task then executes it using plain command module and PostgreSQL's own psql command line tool.

Interested in efficient software development? In my book Efficient Developer I cover topics like product specifications, development tools, processes, testing, and software estimation.

Check it out!

The final playbook

We can save the playbook as postgresql.yml and run it with ansible-playbook postgresql.yml.

---
- hosts: all
become: yes

vars_files:
- vars.yml

pre_tasks:
- name: "Install packages"
dnf: "name={{ item }} state=present"
with_items:
- postgresql
- postgresql-server

- name: "Install Python packages"
pip: "name={{ item }} state=present"
with_items:
- psycopg2-binary

tasks:
- name: "Find out if PostgreSQL is initialized"
ansible.builtin.stat:
path: "/var/lib/pgsql/data/pg_hba.conf"
register: postgres_data

- name: "Initialize PostgreSQL"
shell: "postgresql-setup initdb"
when: not postgres_data.stat.exists

- name: "Start and enable services"
service: "name={{ item }} state=started enabled=yes"
with_items:
- postgresql

- name: "Create app database"
postgresql_db:
state: present
name: "{{ db_name }}"
become: yes
become_user: postgres

- name: "Create db user"
postgresql_user:
state: present
name: "{{ db_user }}"
password: "{{ db_password }}"
become: yes
become_user: postgres

- name: "Grant db user access to app db"
postgresql_privs:
type: database
database: "{{ db_name }}"
roles: "{{ db_user }}"
grant_option: no
privs: all
become: yes
become_user: postgres

- name: "Allow md5 connection for the db user"
postgresql_pg_hba:
dest: "~/data/pg_hba.conf"
contype: host
databases: all
method: md5
users: "{{ db_user }}"
create: true
become: yes
become_user: postgres
notify: restart postgres

- name: "Load SQL script into a variable"
set_fact:
migration_sql: "{{ lookup('file', 'conf/migration.sql') }}"

- name: "Execute script from variable"
command: "psql {{ db_name }} -c {{ migration_sql }}"
become_user: postgres
register: sql_response_variable

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

We have seen how to install and configure a PostgreSQL database with a database user, and how to run an initial SQL migration (or any other SQL script). In the next article, I discuss how to Backup and restore PostgreSQL databases with Ansible using a database dump.

Interested in efficient software development? In my book Efficient Developer I cover topics like product specifications, development tools, processes, testing, and software estimation.

Check it out!

Last updated on 29.3.2021.

ansible data devops