README.md

   1 ## PostgreSQL Ansible role [![Build Status](https://travis-ci.org/trainline-eu/ansible-postgresql-role.svg?branch=master)](https://travis-ci.org/trainline-eu/ansible-postgresql-role)
   2
   3 Ansible role which installs and configures PostgreSQL clusters, databases and users.
   4
   5 See [role page on Ansible Galaxy](https://galaxy.ansible.com/trainline-eu/ansible_postgresql_role).
   6
   7 #### Installation
   8
   9 This role has been tested on Ansible 2.5.0 and higher.
  10
  11 To install:
  12
  13 ```
  14 ansible-galaxy install trainline-eu.ansible_postgresql_role
  15 ```
  16
  17 #### Dependencies
  18
  19 No dependencies
  20
  21 Recommended dependencies:
  22 - [trainline-eu.ansible_barman_role](https://galaxy.ansible.com/trainline-eu/ansible_barman_role)
  23
  24 #### Compatibility matrix
  25
  26 This table lists the tested version of OS/PostgreSQL couples.
  27
  28 | Distribution / PostgreSQL | 9.1                           | 9.4                           | 9.5                | 9.6                | 10                 | 11                 | 12                 |
  29 | ------------------------- | :---:                         | :---:                         | :---:              | :---:              | :---:              | :---:              | :---:              |
  30 | Debian 8.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: |
  31 | 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: |
  32
  33 - :white_check_mark: - tested, works fine
  34 - :grey_question: - will work in the future (help out if you can)
  35 - :interrobang: - maybe works, not tested
  36 - :no_entry: - PostgreSQL has reached EOL
  37
  38 #### Variables
  39
  40 ```yaml
  41 # Basic settings
  42 postgres_listen_addresses: [ '127.0.0.1' ] # Optional
  43 postgres_log_dir: '/home/postgres-logs'    # Optional
  44 postgres_data_dir: '/home/postgres'        # Optional
  45 postgres_clusters:                         # Mandatory
  46   - version: 11                              # Mandatory
  47     name: 'main'                             # Mandatory
  48     port: 5432                               # Mandatory
  49     checksums: True                          # Optional
  50     fsync_enabled: False                     # Optional
  51     archive_enabled: False                   # Optional
  52     wal_level: 'logical'                     # Optional
  53     max_replication_slots: 10                # Optional
  54     barman_directory: None                   # Optional
  55     # Define cluster as a standby server
  56     primary:                                 # Optional
  57       host: '127.0.1.1'                        # Mandatory
  58       port: 5433                               # Mandatory
  59       replication_user:     'replicator'       # Mandatory
  60       replication_password: 'SuperSecret'      # Mandatory
  61       restore_command: None                    # Optional
  62       restore_barman_directory: None           # Optional
  63     # List of users to be created (optional)
  64     users:
  65       - username: 'replicator'                 # Mandatory
  66         password: 'SuperSecret'                # Mandatory
  67         permissions: 'REPLICATION'             # Mandatory
  68     # List of databases to be created (optional)
  69     databases:
  70       - dbname: my_database                    # Mandatory
  71         owner: john                            # Mandatory
  72         extensions:                            # Optional
  73           - names: [ 'postgis', 'postgis_topology' ]
  74             apt_deps: [ 'postgresql-11-postgis' ]
  75
  76 # Postgres config (Optional)
  77 postgres_log_line_prefix: '%m [%p] database: %d host: %h user: %u '
  78 postgres_datestyle: 'iso, dmy'
  79 postgres_locale_formats: fr_FR.UTF-8
  80 postgres_text_search_config: pg_catalog.french
  81
  82 # Postgres pg_hba config (optional)
  83 postgres_allowed_hosts:
  84   - user: all
  85     range: 10.0.0.0/24
  86 postgres_replication_hosts:
  87   - user: replicator
  88     range: 10.0.0.0/24
  89
  90 # Ansible related Configuration
  91 postgres_become_method: su  # Optional
  92
  93 # Barman connectivity
  94 postgres_barman_server: barman.example.com  # Required if at least one server has archive_enabled enabled
  95 postgres_barman_rsync_enabled: False        # Optional
  96 postgres_barman_rsync_options: ''           # Optional
  97 postgres_barman_remote_user: barman         # Optional
  98 postgres_barman_path_prefix: '~'            # Optional, required if using rsync
  99 ```
 100
 101 #### Testing
 102
 103 This project comes with a `test/main.yml` testing playbook. It uses `Docker` to provision containers locally and sets up a 3 node postgresql cluster with a barman server.
 104
 105 Coverage of this playbook is probably not complete but at least it's there.
 106
 107 If you are contributing, please first test your changes in a new playbook in the `test/` directory within docker containers, (using the targeted distribution), and if possible, ensure your change is covered in the tests found in [.travis.yml](./.travis.yml).
 108
 109 #### License
 110
 111 Licensed under the MIT License. See the [LICENSE](./LICENSE) file for details.
 112
 113
 114 #### Thanks
 115
 116 Creators:
 117 - [Gaëtan Duchaussois](https://twitter.com/gduchaussois)
 118 - [Théophile Helleboid](https://twitter.com/chtitux)
 119 - [Paul Bonaud](https://twitter.com/paulRb_r)
 120
 121 Maintainers:
 122 - [Théophile Helleboid](https://twitter.com/chtitux)
 123 - [Paul Bonaud](https://twitter.com/paulRb_r)
 124
 125 #### Feedback, bug-reports, requests, ...
 126
 127 Are [welcome](https://github.com/trainline-eu/postgresql/issues)!