trainline-eu.ansible_postgresql_role

PostgreSQL Ansible Role

This Ansible role helps you install and set up PostgreSQL clusters, databases, and users.

For more details, check this role on Ansible Galaxy.

Installation

This role works with Ansible version 2.5.0 and above.

To install it, run:

ansible-galaxy install trainline-eu.ansible_postgresql_role

Dependencies

This role does not require any dependencies.

However, it is recommended to use:

trainline-eu.ansible_barman_role

Compatibility Matrix

The table below shows the versions of OS/PostgreSQL that have been tested together.

Distribution / PostgreSQL	9.4	9.5	9.6	10	11	12	13
Debian 9.x	:no_entry: :white_check_mark:	:no_entry: :white_check_mark:	:white_check_mark:	:white_check_mark:	:white_check_mark:	:white_check_mark:	:white_check_mark:
Debian 10.x	:no_entry: :white_check_mark:	:no_entry: :white_check_mark:	:white_check_mark:	:white_check_mark:	:white_check_mark:	:white_check_mark:	:white_check_mark:

:white_check_mark: - tested, works well
:grey_question: - will work in the future (help if you can)
:interrobang: - might work, not tested
:no_entry: - PostgreSQL version is no longer supported

Variables

Here are the configuration variables you can use:

# Basic settings
postgres_listen_addresses: [ '127.0.0.1' ] # Optional
postgres_log_dir: '/home/postgres-logs'    # Optional
postgres_data_dir: '/home/postgres'        # Optional
postgres_clusters:                         # Required
  - version: 11                              # Required
    name: 'main'                             # Required
    port: 5432                               # Required
    checksums: True                          # Optional
    fsync_enabled: False                     # Optional
    archive_enabled: False                   # Optional
    wal_level: 'logical'                     # Optional
    max_replication_slots: 10                # Optional
    barman_directory: None                   # Optional
    # Define cluster as a standby server
    primary:                                 # Optional
      host: '127.0.1.1'                        # Required
      port: 5433                               # Required
      replication_user:     'replicator'       # Required
      replication_password: 'SuperSecret'      # Required
      restore_command: None                    # Optional
      restore_barman_directory: None           # Optional
    # Users to be created (optional)
    users:
      - username: 'replicator'                 # Required
        password: 'SuperSecret'                # Required
        permissions: 'REPLICATION'             # Required
    # Databases to be created (optional)
    databases:
      - dbname: my_database                    # Required
        owner: john                            # Required
        extensions:                            # Optional
          - names: [ 'postgis', 'postgis_topology' ]
            apt_deps: [ 'postgresql-11-postgis' ]
    extra_config:                            # Optional
      max_connections: 100                     # Optional
      shared_preload_libraries: ''             # Optional
      max_worker_processes: 8                  # Optional
      track_commit_timestamp: off              # Optional
      max_standby_archive_delay: 30s           # Optional
      max_standby_streaming_delay: 600s        # Optional

# PostgreSQL config (Optional)
postgres_log_line_prefix: '%m [%p] database: %d host: %h user: %u '
postgres_datestyle: 'iso, dmy'
postgres_locale_formats: fr_FR.UTF-8
postgres_text_search_config: pg_catalog.french

# PostgreSQL pg_hba config (optional)
postgres_allowed_hosts:
  - user: all
    range: 10.0.0.0/24
postgres_replication_hosts:
  - user: replicator
    range: 10.0.0.0/24

# Ansible related Configuration
postgres_become_method: su  # Optional

# Barman connectivity
postgres_barman_server: barman.example.com  # Required if archive_enabled is enabled
postgres_barman_rsync_enabled: False        # Optional
postgres_barman_rsync_options: ''           # Optional
postgres_barman_remote_user: barman         # Optional
postgres_barman_path_prefix: '~'            # Optional, needed if using rsync

Testing

This project includes a test/main.yml testing playbook. It uses Docker to set up local containers and creates a 3-node PostgreSQL cluster with a Barman server.

The coverage of this playbook is likely not complete, but it's a start.

If you want to help, please test your changes in a new playbook in the test/ directory, using Docker containers for the intended OS, and make sure your changes are included in the tests if possible.