gem-hunter
Language: Python
An Ansible role for managing a PostgreSQL server
An Ansible role for installing and managing PostgreSQL servers. This role works with both Debian and RedHat based systems, and provides backup scripts for PostgreSQL Continuous Archiving and Point-in-Time Recovery. It does not create or manage PostgreSQL users, roles, groups, databases, and so forth. For that, see galaxyproject.postgresql_objects.
On RedHat-based platforms, the PostgreSQL Global Development Group (PGDG) packages packages will be installed. On Debian-based platforms, you can choose from the distribution's packages (from APT) or the PGDG packages.
Changes that require a restart will not be applied unless you manually restart PostgreSQL. This role will reload the server for those configuration changes that can be updated with only a reload because reloading is a non-intrusive operation, but options that require a full restart will not cause the server to restart.
This role requires Ansible 2.4+
postgresql_user_name: System username to be used for PostgreSQL (default: postgres).
postgresql_version: PostgreSQL version to install. On Debian-based platforms, the default is whatever version is
pointed to by the postgresql metapackage). On RedHat-based platforms, the default is 10.
postgresql_flavor: On Debian-based platforms, this specifies whether you want to use PostgreSQL packages from pgdg
or the distribution's apt repositories. Possible values: apt, pgdg (default: apt).
postgresql_conf: A list of hashes (dictionaries) of postgresql.conf options (keys) and values. These options are
not added to postgresql.conf directly - the role adds a conf.d subdirectory in the configuration directory and an
include statement for that directory to postgresql.conf. Options set in postgresql_conf are then set in
conf.d/25ansible_postgresql.conf. For legacy reasons, this can also be a single hash, but the list syntax is
preferred because it preserves order.
Due to YAML parsing, you must take care when defining values in
postgresql_conf to ensure they are properly written to the config file. For
example:
postgresql_conf: - max_connections: 250 - archive_mode: "off" - work_mem: "'8MB'"
Becomes the following in 25ansible_postgresql.conf:
max_connections = 250
archive_mode = off
work_mem: '8MB'
postgresql_pg_hba_conf: A list of lines to add to pg_hba.conf
postgresql_pg_hba_local_postgres_user: If set to false, this will remove the postgres user's entry from
pg_hba.conf that is preconfigured on Debian-based PostgreSQL installations. You probably do not want to do this
unless you know what you're doing.
postgresql_pg_hba_local_socket: If set to false, this will remove the local entry from pg_hba.conf that is
preconfigured by the PostgreSQL package.
postgresql_pg_hba_local_ipv4: If set to false, this will remove the host ... 127.0.0.1/32 entry from
pg_hba.conf that is preconfigured by the PostgreSQL package.
postgresql_pg_hba_local_ipv6: If set to false, this will remove the host ... ::1/128 entry from pg_hba.conf
that is preconfigured by the PostgreSQL package.
postgresql_pgdata: Only set this if you have changed the $PGDATA directory from the package default. Note this
does not configure PostgreSQL to actually use a different directory, you will need to do that yourself, it just allows
the role to properly locate the directory.
postgresql_conf_dir: As with postgresql_pgdata except for the configuration directory.
postgresql_install_psycopg2: Attempt to install the correct pacakge providing psycopg2 to the Python interpreter
that Ansible is using on the remote side. This allows for the use of the postgresql_* Ansible modules (perhaps via
galaxyproject.postgresql_objects), which depend on psycopg2. Defaults to true.
This role can deploy and schedule the configuration and scripts to maintain Postgresql PITR backups.
Full backups will be made on the configured interval, whereas write-ahead-log (WAL) segments between full backups will
be archived to {{ postgresql_backup_dir }}/wal_archive/ when instructed by the PostgreSQL server. WAL segments can be
removed from this directory once the oldest backup referencing them has been removed. This is done automatically for you
by the backup script if postgresql_backup_dir is mounted locally.
When postgresql_backup_dir is a remote rsync path (containing a ":"), the backup script will still maintain backups
(including deleting older full backups) but cannot prune the wal_archive/ directory automatically. If you are able to
install the standard pg_archivecleanup utility from the PostgreSQL client package on your backup server, you can run
this role's backup script with the --clean-archive option directly on the backup server instead.
postgresql_backup_dir: If set, enables PITR backups. Set this to a directory where your database will be backed up
(this can be any format supported by rsync, e.g. user@host:/path). The most recent backup will be in a subdirectory
named current.
postgresql_backup_local_dir: Filesystem path on the PostgreSQL server where backup scripts will be placed.
postgresql_backup_[hour|minute]: Controls what time the cron job will run to perform a full backup. Defaults to 1:00
AM.
postgresql_backup_[day|month|weekday]: Additional cron controls for when the full backup is performed (default:
*).
postgresql_backup_post_command: Arbitrary command to run after successful completion of a scheduled backup.
Additional options pertaining to backups can be found in the defaults file.
Backup functionality requires Python 2.7 or 3.5+, psycopg2, and rsync. Note that if installing PGDG versions of PostgreSQL on Enterprise Linux, corresponding psycopg2 packages are available from the PGDG yum repositories.
Standard install: Default postgresql.conf, pg_hba.conf and default version for the OS:
--- - hosts: dbservers roles: - galaxyproject.postgresql
Use the pgdg packages on a Debian-based host:
--- - hosts: dbservers vars: postgresql_flavor: pgdg roles: - galaxyproject.postgresql
Use the PostgreSQL 9.5 packages and set some postgresql.conf options and pg_hba.conf entries:
--- - hosts: dbservers vars: postgresql_version: 9.5 postgresql_conf: - listen_addresses: "''" # disable network listening (listen on unix socket only) - max_connections: 50 # decrease connection limit postgresql_pg_hba_conf: - host all all 10.0.0.0/8 md5 roles: - galaxyproject.postgresql
Enable backups to /archive:
- hosts: all vars: postgresql_backup_dir: /archive roles: - galaxyproject.postgresql
Enable backups to /archive on 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
Academic Free License ("AFL") v. 3.0
The Galaxy Community and contributors