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:

Install PostgreSQL database
Set up a new database user
See how to create and run a migration using a SQL script

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:

Create a database for our application app1
Create a new database user that can be used by the application
Grant the new user the access to the database
Modify pg_hba.conf file to allow the new user to connect with a connection string

  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.

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.

Last updated on 29.3.2021.