Getting started

General configuration

On a new host, PostgreSQL server will be configured with a default cluster main running on port 5432. The cluster will only listen to connections from localhost. The ansible management account and the root account will be able to login to the PostgreSQL server using the postgres role. For example:

user@host:~$ psql -U postgres

The PostgreSQL version installed by the role will be a default version offered by the distribution. If you want PostgreSQL 9.4 on Debian Wheezy, or an upstream version of the server, you can enable the upstream APT repository from inventory, by adding:

postgresql_server__upstream: True

Check postgresql_server__preferred_version to learn more about selecting custom PostgreSQL versions.

After installation you can use debops.postgresql role to configure PostgreSQL roles and databases.

Remote access to the database

By default, PostgreSQL listens only for connections on localhost.

To enable remote access, you need to change the postgresql_server__listen_addresses list to specify either IP addresses of the interfaces you want your host to listen on, or * for all interfaces.

As the firewall blocks all connections to the PostgreSQL server by default, you will also need to specify IP addresses or CIDR subnets which should be able to connect to the clusters. Example configuration of variables in inventory:

postgresql_server__listen_addresses: [ '*' ]
postgresql_server__allow: [ '192.0.2.0/24', '2001:db8::/32' ]

The default set of Host-Based Authentication rules permit connections from remote hosts that are in the same subnet as the server, only over SSL, and require the correct password to be provided to accept connections.

If you want to allow connections from other subnets than the server, you will need to add your own HBA entries to the PostgreSQL cluster configuration. Example for the default cluster:

postgresql_server__cluster_main:
  name: 'main'
  port: '5432'

  hba:
    - type: 'hostssl'
      database: 'samerole'
      user: 'all'
      address: [ '192.0.2.0/24', '2001:db8::/32' ]
      method: 'md5'

The debops.postgresql_server role is designed to use the PKI infrastructure managed by the debops.pki role.

A pki realm can be picked with the postgresql_server__pki_realm variable, then remote connections to the server will be automatically encrypted over SSL, otherwise dropped by default.

Example inventory

To install and configure PostgreSQL server on a host, you need to add the host to the [debops_service_postgresql_server] Ansible host group:

[debops_service_postgresql_server]
hostname

Example playbook

Here's an example playbook which uses the debops.postgresql_server role:

---

- name: Manage PostgreSQL server
  collections: [ 'debops.debops', 'debops.roles01',
                 'debops.roles02', 'debops.roles03' ]
  hosts: [ 'debops_service_postgresql_server' ]
  become: True

  environment: '{{ inventory__environment | d({})
                   | combine(inventory__group_environment | d({}))
                   | combine(inventory__host_environment  | d({})) }}'

  roles:

    - role: keyring
      tags: [ 'role::keyring', 'skip::keyring', 'role::postgresql_server' ]
      keyring__dependent_apt_keys:
        - '{{ postgresql_server__keyring__dependent_apt_keys }}'

    - role: apt_preferences
      tags: [ 'role::apt_preferences', 'skip::apt_preferences' ]
      apt_preferences__dependent_list:
        - '{{ postgresql_server__apt_preferences__dependent_list }}'

    - role: etc_services
      tags: [ 'role::etc_services', 'skip::etc_services' ]
      etc_services__dependent_list:
        - '{{ postgresql_server__etc_services__dependent_list }}'

    - role: ferm
      tags: [ 'role::ferm', 'skip::ferm' ]
      ferm__dependent_rules:
        - '{{ postgresql_server__ferm__dependent_rules }}'

    - role: python
      tags: [ 'role::python', 'skip::python', 'role::postgresql' ]
      python__dependent_packages3:
        - '{{ postgresql_server__python__dependent_packages3 }}'
      python__dependent_packages2:
        - '{{ postgresql_server__python__dependent_packages2 }}'

    - role: locales
      tags: [ 'role::locales', 'skip::locales' ]
      locales__dependent_list:
        - '{{ postgresql_server__locales__dependent_list }}'

    - role: postgresql_server
      tags: [ 'role::postgresql_server', 'skip::postgresql_server' ]

Ansible tags

You can use Ansible --tags or --skip-tags parameters to limit what tasks are performed during Ansible run. This can be used after the host is first configured to speed up playbook execution, when you are sure that most of the configuration is already in the desired state.

Available role tags:

role::postgresql_server

Main role tag, should be used in the playbook to execute all of the role tasks as well as role dependencies.

role::postgresql_server:packages

Run tasks related to package installation

role::postgresql_server:config

Run tasks related to PostgreSQL Server configuration.

role::postgresql_server:auto_backup

Run tasks that configure AutoPostgreSQLBackup scripts.