galaxyproject.postgresql

PostgreSQL

This is an Ansible role to help you install and manage PostgreSQL servers. It works on both Debian and RedHat systems and includes backup scripts for PostgreSQL Continuous Archiving and Point-in-Time Recovery. However, it does not handle PostgreSQL users, roles, or databases—check galaxyproject.postgresql_objects for that.

On RedHat systems, it installs packages from the PostgreSQL Global Development Group (PGDG). On Debian systems, you can use either the distribution's packages or the PGDG packages (PGDG APT).

Keep in mind: Changes that need a restart won’t take effect until you manually restart PostgreSQL. This role can reload the server for changes that only need a reload, but it won’t do a full restart automatically.

Requirements

You need Ansible version 2.4 or higher.

Role Variables

All variables are optional

postgresql_user_name: The system username for PostgreSQL (default: postgres).
postgresql_version: The version of PostgreSQL to install. For Debian, it defaults to whatever version the postgresql metapackage points to. For RedHat, the default is 10.
postgresql_flavor: On Debian systems, choose apt for distribution's packages or pgdg for PGDG packages (default: apt).
postgresql_conf: A list of configuration options for postgresql.conf. These settings are stored in a subdirectory (conf.d) inside the configuration directory.
postgresql_pg_hba_conf: A list of lines to add to pg_hba.conf.
postgresql_pg_hba_local_postgres_user: If set to false, removes the postgres user's entry from pg_hba.conf in Debian PostgreSQL installations.
postgresql_pg_hba_local_socket: If set to false, removes the local entry from pg_hba.conf.
postgresql_pg_hba_local_ipv4: If set to false, removes the host ... 127.0.0.1/32 entry from pg_hba.conf.
postgresql_pg_hba_local_ipv6: If set to false, removes the host ... ::1/128 entry from pg_hba.conf.
postgresql_pgdata: Change this only if you've modified the $PGDATA directory from the default.
postgresql_conf_dir: Change this if you've modified the configuration directory.
postgresql_install_psycopg2: Set to true to install psycopg2 for using PostgreSQL Ansible modules.

Backups

This role can set up and schedule backups using PITR (Point-in-Time Recovery).

postgresql_backup_dir: If set, enables PITR backups—specify where your database will be backed up.
postgresql_backup_local_dir: The directory on the PostgreSQL server for backup scripts.
postgresql_backup_[hour|minute]: When to run the full backup (default: 1:00 AM).
postgresql_backup_[day|month|weekday]: Additional timing settings for backups (default: *).
postgresql_backup_post_command: A command to run after a successful backup.

Dependencies

Backup functionality needs Python 2.7 or 3.5+, psycopg2, and rsync. If using PGDG versions on Enterprise Linux, corresponding psycopg2 packages can be found in PGDG yum repositories.

Example Playbook

Standard Install

To install the default PostgreSQL configuration:

---
- hosts: dbservers
  roles:
    - galaxyproject.postgresql

Using PGDG Packages on Debian

To use the PGDG packages on a Debian system:

---
- hosts: dbservers
  vars:
    postgresql_flavor: pgdg
  roles:
    - galaxyproject.postgresql

Specifying Version and Configurations

To use PostgreSQL 9.5 with specific configurations:

---
- hosts: dbservers
  vars:
    postgresql_version: 9.5
    postgresql_conf:
      - listen_addresses: "''"    # Disable network listening
      - max_connections: 50       # Limit connections
    postgresql_pg_hba_conf:
      - host all all 10.0.0.0/8 md5
  roles:
    - galaxyproject.postgresql

Enabling Backups

To enable backups to a local directory:

- hosts: all
  vars:
    postgresql_backup_dir: /archive
  roles:
    - galaxyproject.postgresql

Enabling Remote Backups

To enable backups to a remote server:

- hosts: dbservers
  vars:
    postgresql_backup_dir: backup.example.org:/archive
  roles:
    - galaxyproject.postgresql

- hosts: backupservers
  tasks:
    - name: Install PostgreSQL scripts
      ansible.builtin.apt:
        name: postgresql-common
    - name: Copy backup script
      ansible.builtin.copy:
        src: roles/galaxyproject.postgresql/files/backup.py
        dest: /usr/local/bin/pgbackup.py
        mode: "0755"
    - name: Schedule WAL pruning
      ansible.builtin.cron:
        name: Prune PostgreSQL Archived WALs
        hour: 22
        minute: 0
        job: /usr/local/bin/pgbackup.py --clean-archive /archive