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.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"]
Now with this new image, I can deploy a new container with the following command:docker build -t postgres11-defaults .
Once the container is up and running, I will start PostgreSQL service running in the container.docker run -d --hostname=psql11 --name psql11 -p 5432:5432 postgres11-defaults
docker exec -it psql11 service postgresql start
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.psql11 docker_service_name=psql11
[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:
2) Create psql_playbook.yml with the following information:# PostgreSQL Client Authentication Configuration File # =================================================== # # See: https://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html
---
- 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)
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.
Looking to innovate through PostgreSQL?