Ansible Role: PostgreSQL

This role installs and sets up the PostgreSQL server on RHEL/CentOS or Debian/Ubuntu servers.

Requirements

No special prerequisites; however, this role needs root access. You can run it in a playbook with become: yes:

- hosts: database
  roles:
    - role: geerlingguy.postgresql
      become: yes

Role Variables

Here are the available variables and their default values (found in defaults/main.yml):

postgresql_enablerepo: ""

(Only for RHEL/CentOS) Set which repository to use for installing PostgreSQL.

postgresql_restarted_state: "restarted"

Specify the service state after configuration changes. Recommended values are restarted or reloaded.

postgresql_python_library: python-psycopg2

The library Ansible uses to connect with PostgreSQL. If you're using Python 3, change it to python3-psycopg2.

postgresql_user: postgres
postgresql_group: postgres

The user and group PostgreSQL will run under.

postgresql_unix_socket_directories:
  - /var/run/postgresql

The directories (usually one but can be more) where PostgreSQL's socket will be created.

postgresql_service_state: started
postgresql_service_enabled: true

Manage whether the PostgreSQL service is running and starts on boot.

postgresql_global_config_options:
  - option: unix_socket_directories
    value: '{{ postgresql_unix_socket_directories | join(",") }}'
  - option: log_directory
    value: 'log'

Global configuration options that will be applied in postgresql.conf. If using PostgreSQL version earlier than 9.3, make sure to set the unix_socket_directory.

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 }

Configure host-based authentication in the pg_hba.conf. Required fields are:

• type
• database
• user
• auth_method
• address (one of these must be present)

If you change existing entries, make sure to copy from defaults/main.yml.

postgresql_locales:
  - 'en_US.UTF-8'

(Only for Debian/Ubuntu) Used to create the locales for PostgreSQL databases.

postgresql_databases:
  - name: exampledb # required; others are optional
    lc_collate: # defaults to 'en_US.UTF-8'
    lc_ctype: # defaults to 'en_US.UTF-8'
    encoding: # defaults to 'UTF-8'
    template: # defaults to 'template0'
    login_host: # defaults to 'localhost'
    login_password: # defaults to not set
    login_user: # defaults to 'postgresql_user'
    login_unix_socket: # defaults to first of `postgresql_unix_socket_directories`
    port: # defaults to not set
    owner: # defaults to `postgresql_user`
    state: # defaults to 'present'

A list of databases to create on the server. Only the name is necessary; all other details are optional.

postgresql_users:
  - name: jdoe # required; others are optional
    password: # defaults to not set
    encrypted: # defaults to not set
    priv: # defaults to not set
    role_attr_flags: # defaults to not set
    db: # defaults to not set
    login_host: # defaults to 'localhost'
    login_password: # defaults to not set
    login_user: # defaults to '{{ postgresql_user }}'
    login_unix_socket: # defaults to first of `postgresql_unix_socket_directories`
    port: # defaults to not set
    state: # defaults to 'present'

A list of users to ensure exist on the server. Only the name is required; all other properties are optional.

postgres_users_no_log: true

Specifies whether to show user data when managing users, which may include sensitive information like passwords.

postgresql_version: [OS-specific]
postgresql_data_dir: [OS-specific]
postgresql_bin_path: [OS-specific]
postgresql_config_path: [OS-specific]
postgresql_daemon: [OS-specific]
postgresql_packages: [OS-specific]

OS-specific variables set in this role's vars directory. Avoid changing these unless you're using a non-standard PostgreSQL installation.

Dependencies

None.

Example Playbook

- hosts: database
  become: yes
  vars_files:
    - vars/main.yml
  roles:
    - geerlingguy.postgresql

Inside vars/main.yml:

postgresql_databases:
  - name: example_db
postgresql_users:
  - name: example_user
    password: supersecure

License

MIT / BSD

Author Information

This role was created in 2016 by Jeff Geerling, author of Ansible for DevOps.