Ansible Role: PostgreSQL on Docker

This is an Ansible role that sets up and runs PostgreSQL using a Docker container.

Overview

• Requirements
• Role Variables
• Dependencies
• Example Playbooks
  • Create a network
  • Custom name with bind mounts
  • Initialization scripts
• License
• Author Information

Requirements

You need Docker installed. It's recommended to use the role geerlingguy.docker for Docker setup.

Role Variables

• postgres_docker_name: Name for the Docker container.

• postgres_docker_admin_password: Password for the superuser.

• postgres_docker_initscripts: Optional scripts (like .sql, sql.gz, or .sh) to copy into the PostgreSQL container. They need to be on the target system for copying.

  The container will run these scripts when it starts, but only if the data directory is empty. If there’s an existing database, it won't be affected. Be careful: if a script fails and the container restarts, already running scripts won’t execute again.

  Scripts run in order, and *.sql files execute as the postgres superuser. For commands in *.sh scripts, use the --username "$POSTGRES_USER" flag to connect without a password.

• postgres_docker_use_volumes: Decide if you want to use Docker volumes. If true, it creates two volumes:

  • One for config files at /etc/postgresql.
  • One for data files at /var/lib/postgresql/data.

• postgres_docker_memory: Memory allocated to the PostgreSQL container.

• postgres_docker_cpu: Number of vCPUs allocated to the PostgreSQL container.

• postgres_docker_network_create: Decide to create a Docker network for the PostgreSQL container. If true, it creates a new network. If false, it joins the specified network in postgres_docker_network_name.

• postgres_docker_network_name: Name of the Docker network to join. This is required if postgres_docker_network_create is false.

• postgres_docker_image: Name of the Docker image to use.

• postgres_docker_available_externally: Enable in Traefik Proxy.

Check defaults/main.yml for a full list.

Dependencies

None.

Example Playbooks

Create a network

- hosts: all
  vars:
    postgres_docker_network_create: true
    postgres_docker_memory: "2GB"
    postgres_docker_cpu: "2"
  roles:
    - geerlingguy.docker
    - organicveggie.postgres_docker

Custom name with bind mounts

- hosts: all
  vars:
    postgres_docker_name: "pgsql-example"
    postgres_docker_use_volumes: false
  roles:
    - geerlingguy.docker
    - organicveggie.postgres_docker

Initialization scripts

To create a new user and database, use this script: 01_create_example.sh

#!/bin/sh
set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES" --dbname "molecule" <<-EOSQL
    CREATE USER example;
    CREATE DATABASE exampledb;
    GRANT ALL PRIVILEGES ON DATABASE exampledb TO example;
    CREATE TABLE test (
        id int CONSTRAINT id_pk PRIMARY KEY
    );
    INSERT INTO test VALUES (42), (54);
EOSQL

Playbook:

- hosts: all
  roles:
    - geerlingguy.docker

- hosts: all
  vars:
    postgres_docker_initscripts:
      - "/tmp/01_create_example.sh"

  tasks:
    - name: Copy init script
      ansible.builtin.copy:
        src: "files/01_create_example.sh"
        dest: "/tmp"
        mode: "755"

  roles:
    - organicveggie.postgres_docker

License

Apache

Author Information

Sean Laurent