Working with Ansible, PostgreSQL and Docker

Posted in: Open Source, Technical Track

While working on creating some of our internal training labs for PostgreSQL, I have had the pleasure of working with PostreSQL in Docker and working with it in different ways.  One of the most important ways that I worked with it was to get Ansible to work with a PostgreSQL instance in a Docker container the same way that Ansible would work with a standard server.  Here, I will cover some of the things I learned while going through this process to hopefully help you in your journey.  I will be covering building a new Docker image for PostgreSQL 11, and then configuring it with Ansible.

Deployment

In my local lab, I have the following installed for testing this setup:

  • Docker
  • Ansible
  • PostgreSQL client

Rebuilding with Dockerfile

In building out our labs, we utilize Docker and Ansible for all of the configurations.  I started off with building my own Ansible role for PostgreSQL that would be compatible with CentOS, Debian, and Ubuntu servers that were not in Docker.  This way we could potentially utilize this role in the future for our clients.

But the obstacle that I encountered when trying to run the Ansible role against the PostgreSQL Docker container was that some of the default locations for items, such as the data directory and configuration file path, were different.  I found that if I took the PostgreSQL official Docker image, and built a new Docker image and initialized the database from scratch, all the default paths were restored and used again.  This, in turn, allowed my Ansible role to work against the new Docker container the same way as if it was running against a standard server with a default PostgreSQL installation. Below is the Dockerfile I created.  I also needed to install Python in order for Ansible to run against the container.

Dockerfile

FROM postgres:11

RUN apt-get update && apt-get install -y \
python \
&& rm -rf /var/lib/apt/lists/*

RUN pg_createcluster --start 11 main

ENTRYPOINT ["tail", "-f", "/dev/null"]

There are a couple of things happening in this Dockerfile.  First, I am using the official PostgreSQL image to use as my base.  I then install Python in order for Ansible to run against any containers I deploy from the new image.  I then run pg_createcluster to initialize the PostgreSQL container with the default settings as to where the data directory and configuration files will be stored.

I was building this container for our training lab and one of the training labs is to set up streaming replication.   In order to configure streaming replication, I had to stop the PostgreSQL service.  With the standard PostgreSQL container, when PostgreSQL stops, then the entire container stops, making it hard to work with.  So for my entry point, I tail /dev/null which will allow the container to continue running no matter what happens to the PostgreSQL service inside the container. This allows me to stop the server for setting up streaming replication or breaking it in different ways to learn how to troubleshoot problems without the container stopping.  The downside to this is you need to manually start PostgreSQL in the container after the container is running.

Run the following command in the same directory where the Dockerfile is located, to build the new image with the name posgres11-defaults.

docker build -t postgres11-defaults .

Now with this new image, I can deploy a new container with the following command:

docker run -d --hostname=psql11 --name psql11 -p 5432:5432 postgres11-defaults

Once the container is up and running, I will start PostgreSQL service running in the container.

docker exec -it psql11 service postgresql start

Configure Docker PostgreSQL with Ansible

Now that I have a PostgreSQL container ready, I can use Ansible to update the container to my desired setup. First, I need to create an inventory file that will contain the connection information that Ansible will use.  I added the following line to a file called inventory:

psql11 docker_service_name=psql11

Second, I will test connectivity to the container with the ping module, using the Docker container and the Ansible inventory file I created.  I need to tell Ansible that I will be connecting to a Docker container with the connection command line argument.

[root@sandbox-base1 psql]# ansible psql11 -i ./inventory --connection=docker -m ping
psql11 | SUCCESS => {
    "changed": false,
    "ping": "pong"
}

Here is an example playbook and template that can access the container and configure it with a PostgreSQL database, user, and pg_hba config to enable remote access for the user.  These are the steps I took:

1) Create pg_hba.conf.j2 template for the pg_hba configuration file that manages who is able to log in to the PostgreSQL server:

{{ ansible_managed | comment }}
# PostgreSQL Client Authentication Configuration File
# ===================================================
#
# See: https://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html

{% for client in postgresql_hba_entries %}
{{ client.type }} {{ client.database }} {{ client.user }} {{ client.address|default('') }} {{ client.ip_address|default('') }} {{ client.ip_mask|default('') }} {{ client.auth_method }} {{ client.auth_options|default("") }}
{% endfor %}

2) Create psql_playbook.yml with the following information:

---
- hosts: all
  gather_facts: yes
  connection: docker
  vars:
    postgresql_user: postgres
    postgresql_group: postgres
    postgresql_replication_user: replication_user
    postgresql_replication_pass: replication_pass
    postgresql_pythian_user: pythian
    postgresql_pythian_pass: pythian_pass
    postgresql_version: 11
    postgresql_config_path: "/etc/postgresql/{{ postgresql_version }}/main"
    postgresql_unix_socket_directories:
      - /var/run/postgresql
    postgresql_databases:
      - {name: pythian_db, lc_collate: en_US.UTF-8}
    postgresql_users:
      - {name: "{{ postgresql_pythian_user }}", password: "{{ postgresql_pythian_pass }}", encrypted: true, db: pythian_db, priv: all, role_attr_flags: SUPERUSER,CREATEROLE,CREATEDB,LOGIN}
      - {name: "{{ postgresql_replication_user }}", password: "{{ postgresql_replication_pass }}", encrypted: true, db: postgres, priv: all, role_attr_flags: REPLICATION,LOGIN}
    postgresql_hba_entries:
      - {type: local, database: all, user: postgres, auth_method: peer}
      - {type: local, database: all, user: all, auth_method: peer}
      - {type: host, database: all, user: all, address: '127.0.0.1/32', auth_method: md5}
      - {type: host, database: all, user: all, address: '::1/128', auth_method: md5}
      - {type: host, database: replication, user: all, address: '0.0.0.0/0', auth_method: md5}
      - {type: host, database: pythian_db, user: pythian, address: '0.0.0.0/0', auth_method: md5}
  tasks:
    - name: Start PostgreSQL
      service:
        name: postgresql
        state: started
        enabled: true

    - name: Install Python PostgreSQL lirary for making changes with Ansible
      apt:
        name: "{{ item }}"
        state: installed
      with_items:
        - sudo
        - python-psycopg2

    - name: Ensure PostgreSQL databases are present.
      postgresql_db:
        name: "{{ item.name }}"
        lc_collate: "{{ item.lc_collate | default('en_US.UTF-8') }}"
        lc_ctype: "{{ item.lc_ctype | default('en_US.UTF-8') }}"
        encoding: "{{ item.encoding | default('UTF-8') }}"
        template: "{{ item.template | default('template0') }}"
        login_host: "{{ item.login_host | default('localhost') }}"
        login_password: "{{ item.login_password | default(omit) }}"
        login_user: "{{ item.login_user | default(postgresql_user) }}"
        login_unix_socket: "{{ item.login_unix_socket | default(postgresql_unix_socket_directories[0]) }}"
        port: "{{ item.port | default(omit) }}"
        owner: "{{ item.owner | default(postgresql_user) }}"
        state: "{{ item.state | default('present') }}"
      with_items: "{{ postgresql_databases }}"
      become: yes
      become_method: sudo
      become_user: "{{ postgresql_user }}"
      vars:
        ansible_ssh_pipelining: true

    - name: Ensure PostgreSQL users are present.
      postgresql_user:
        name: "{{ item.name }}"
        password: "{{ item.password | default(omit) }}"
        encrypted: "{{ item.encrypted | default(omit) }}"
        priv: "{{ item.priv | default(omit) }}"
        role_attr_flags: "{{ item.role_attr_flags | default(omit) }}"
        db: "{{ item.db | default(omit) }}"
        login_host: "{{ item.login_host | default('localhost') }}"
        login_password: "{{ item.login_password | default(omit) }}"
        login_user: "{{ item.login_user | default(postgresql_user) }}"
        login_unix_socket: "{{ item.login_unix_socket | default(postgresql_unix_socket_directories[0]) }}"
        port: "{{ item.port | default(omit) }}"
        state: "{{ item.state | default('present') }}"
      with_items: "{{ postgresql_users }}"
      become: yes
      become_method: sudo
      become_user: "{{ postgresql_user }}"
      vars:
        ansible_ssh_pipelining: true

    - name: Configure host based authentication (if entries are configured).
      template:
        src: "pg_hba.conf.j2"
        dest: "{{ postgresql_config_path }}/pg_hba.conf"
        owner: "{{ postgresql_user }}"
        group: "{{ postgresql_group }}"
        mode: 0600

    - name: Restart PostgreSQL
      service:
        name: postgresql
        state: restarted

3) Run the playbook to configure the instance using the inventory created previously and the new playbook:

[root@sandbox-base1 psql]# ansible-playbook -i ./inventory psql_playbook.yml

PLAY [all] ***********************************************************************************************************************************************************************************

TASK [Gathering Facts] ***********************************************************************************************************************************************************************
ok: [psql11]

TASK [Start PostgreSQL] **********************************************************************************************************************************************************************
changed: [psql11]

TASK [Install Python PostgreSQL lirary for making changes with Ansible] **********************************************************************************************************************
changed: [psql11] => (item=[u'sudo', u'python-psycopg2'])

TASK [Ensure PostgreSQL databases are present.] **********************************************************************************************************************************************
changed: [psql11] => (item={u'lc_collate': u'en_US.UTF-8', u'name': u'pythian_db'})

TASK [Ensure PostgreSQL users are present.] **************************************************************************************************************************************************
changed: [psql11] => (item={u'name': u'pythian', u'encrypted': True, u'CREATEDB': None, u'db': u'pythian_db', u'CREATEROLE': None, u'LOGIN': None, u'password': u'pythian_pass', u'role_attr_flags': u'SUPERUSER', u'priv': u'all'})
changed: [psql11] => (item={u'name': u'replication_user', u'encrypted': True, u'db': u'postgres', u'LOGIN': None, u'password': u'replication_pass', u'role_attr_flags': u'REPLICATION', u'priv': u'all'})

TASK [Configure host based authentication (if entries are configured).] **********************************************************************************************************************
changed: [psql11]

TASK [Restart PostgreSQL] ********************************************************************************************************************************************************************
changed: [psql11]

PLAY RECAP ***********************************************************************************************************************************************************************************
psql11                     : ok=7    changed=6    unreachable=0    failed=0

4) Capture the IP of the container and then perform a connection test to log in to PostgreSQL in the Docker container:

[root@sandbox-base1 psql]# docker inspect psql11 | grep -w IPAddress | head -n1
            "IPAddress": "172.17.0.2",
[root@sandbox-base1 psql]# psql pythian_db -h 172.17.0.2 -U pythian -W
Password for user pythian:
psql (9.2.24, server 11.5 (Debian 11.5-1.pgdg90+1))
WARNING: psql version 9.2, server version 11.0.
         Some psql features might not work.
SSL connection (cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256)
Type "help" for help.

pythian_db=# \l
                                      List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |       Access privileges
------------+----------+----------+-------------+-------------+-------------------------------
 postgres   | postgres | UTF8     | en_US.utf8  | en_US.utf8  | =Tc/postgres                 +
            |          |          |             |             | postgres=CTc/postgres        +
            |          |          |             |             | replication_user=CTc/postgres
 pythian_db | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres                 +
            |          |          |             |             | postgres=CTc/postgres        +
            |          |          |             |             | pythian=CTc/postgres
 template0  | postgres | UTF8     | en_US.utf8  | en_US.utf8  | =c/postgres                  +
            |          |          |             |             | postgres=CTc/postgres
 template1  | postgres | UTF8     | en_US.utf8  | en_US.utf8  | =c/postgres                  +
            |          |          |             |             | postgres=CTc/postgres
(4 rows)

Summary

I wouldn’t expect this setup to be used in a production environment, but I wanted to share some of the things I learned when trying to set up a Docker container to test with for PostgreSQL.  This setup allows for the ability to configure different scenarios to use for testing.  I am not sure why the default locations were not used with the original PostgreSQL container, but there are probably a lot of reasons why that I am just not aware of. I hoped this helped you in working with Docker, PostgreSQL and Ansible.

 

 

email

Interested in working with Kevin? Schedule a tech call.

About the Author

MySQL Database Consultant
Kevin Markwardt has twenty years of system administration experience ranging from MySQL, Linux, Windows, and VMware. Over the last six years he has been dedicated to MySQL and Linux administration with a focus on scripting, automation, HA, and cloud solutions. Kevin has lead and assisted with many projects focusing on larger scale implementations of technologies, including ProxySQL, Orchestrator, Pacemaker, GCP, AWS RDS, and MySQL. Kevin Markwardt is a certified GCP Professional Cloud Architect, and a certified AWS Solutions Architect - Associate. Currently he is a Project Engineer at Pythian specializing in MySQL and large scale client projects. One of his new directives is Postgres and is currently supporting multiple internal production Postgres instances.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *