Working with Ansible, PostgreSQL and Docker

by Kevin Markwardt on Sep 24, 2019 12:00:00 AM

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:

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

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//main"
  postgresql_unix_socket_directories:
  - /var/run/postgresql
  postgresql_databases:
  - {name: pythian_db, lc_collate: en_US.UTF-8}
  postgresql_users:
  - {name: "", password: "", encrypted: true, db: pythian_db, priv: all, role_attr_flags: SUPERUSER,CREATEROLE,CREATEDB,LOGIN}
  - {name: "", password: "", 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}}"
  lc_ctype: "{{item.lc_ctype}}"
  encoding: "{{item.encoding}}"
  template: "{{item.template}}"
  login_host: "{{item.login_host}}"
  login_password: "{{item.login_password}}"
  login_user: "{{item.login_user}}"
  login_unix_socket: "{{item.login_unix_socket}}"
  port: "{{item.port}}"
  owner: "{{item.owner}}"
  state: "{{item.state}}"
  with_items: ""
  become: yes
  become_method: sudo
  become_user: ""
  vars:
  ansible_ssh_pipelining: true
 
  - name: Ensure PostgreSQL users are present.
  postgresql_user:
  name: "{{item.name}}"
  password: "{{item.password}}"
  encrypted: "{{item.encrypted}}"
  priv: "{{item.priv}}"
  role_attr_flags: "{{item.role_attr_flags}}"
  db: "{{item.db}}"
  login_host: "{{item.login_host}}"
  login_password: "{{item.login_password}}"
  login_user: "{{item.login_user}}"
  login_unix_socket: "{{item.login_unix_socket}}"
  port: "{{item.port}}"
  state: "{{item.state}}"
  with_items: ""
  become: yes
  become_method: sudo
  become_user: ""
  vars:
  ansible_ssh_pipelining: true
 
  - name: Configure host based authentication (if entries are configured).
  template:
  src: "pg_hba.conf.j2"
  dest: "/pg_hba.conf"
  owner: ""
  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.

No Comments Yet

Let us know what you think

Share this