Postgresql
Postgres is an opensource object-relational database.
Ansible Role: db.postgres
Manage MariaDB.
Role handles all steps that are provided in this documentation.
# DB
Manage Maria and Postgres DB's.
## Requirements
No additional requirements.
## Role Variables
Settings have been throughly documented for usage.
* [defaults/maria.yml](https://github.com/r-pufky/ansible_db/blob/main/defaults/main/maria.yml).
* [defaults/postgres.yml](https://github.com/r-pufky/ansible_db/blob/main/defaults/main/postgres.yml).
* [defaults/ports.yml](https://github.com/r-pufky/ansible_db/blob/main/defaults/main/ports.yml).
## Dependencies
None. Based on:
* https://github.com/geerlingguy/ansible-role-mysql
* https://github.com/geerlingguy/ansible-role-postgresql
With patches, postgres 13 support, bullseye support.
## Example Playbook
Maria (Mysql) Configuration
host_vars/db.example.com/vars/maria.yml
``` yaml
db_maria_root_home: '/root'
db_maria_root_username: 'root'
db_maria_root_password: '{{ vault_db_maria_root_password }}'
db_maria_databases:
- name: 'digikam'
- name: 'firefly'
db_maria_users:
- name: 'digikam'
host: '%' # If recreated set to server host.
password: '{{ vault_users_db_maria_digikam_password }}'
priv: 'digikam.*:ALL'
- name: 'firefly'
host: '%' # If recreated set to server host.
password: '{{ vault_users_db_maria_firefly_password }}'
priv: 'firefly.*:ALL'
db_maria_mysqld_bind_address: '*'
db_maria_mysqld_port: 3306
```
Prostgres Configuration
host_vars/db.example.com/vars/postgres.yml
``` yaml
db_postgres_version: '13'
db_postgres_data_dir: '/var/lib/postgresql/{{ db_postgres_version }}/main'
db_postgres_backup_dir: '/var/lib/postgresql-backup'
db_postgres_bin_path: '/usr/lib/postgresql/{{ db_postgres_version }}/bin'
db_postgres_config_path: '/etc/postgresql/{{ db_postgres_version }}/main'
db_postgres_packages:
- 'postgresql-{{ db_postgres_version }}'
db_postgres_databases:
- name: 'gitea'
owner: 'gitea'
- name: 'roundcube'
owner: 'roundcube'
db_postgres_users:
- name: 'gitea'
password: '{{ vault_users_db_postgres_gitea_password }}'
priv: 'all'
db: 'gitea'
- name: 'roundcube'
password: '{{ vault_users_db_postgres_roundcube_password }}'
priv: 'all'
db: 'roundcube'
db_postgres_ssl: 'off'
db_postgres_connections_authentication_extensions: |
listen_addresses = '*'
superuser_reserved_connections = 1
db_postgres_log_timezone: 'America/Los_Angeles'
db_postgres_timezone: 'America/Los_Angeles'
db_postgres_lc_messages: 'en_US.utf8'
db_postgres_lc_monetary: 'en_US.utf8'
db_postgres_lc_numeric: 'en_US.utf8'
db_postgres_lc_time: 'en_US.utf8'
db_postgres_hba:
- {type: 'host', database: 'sameuser', user: 'gitea', address: 'samenet', method: '{{ db_postgres_auth_method }}'}
- {type: 'host', database: 'sameuser', user: 'roundcube', address: 'samenet', method: '{{ db_postgres_auth_method }}'}
- {type: 'local', database: 'all', user: 'postgres', address: '', method: 'peer'}
- {type: 'local', database: 'all', user: 'all', address: '', method: 'peer'}
- {type: 'host', database: 'all', user: 'all', address: '127.0.0.1/32', method: '{{ db_postgres_auth_method }}'}
- {type: 'host', database: 'all', user: 'all', address: '::1/128', method: '{{ db_postgres_auth_method }}'}
```
site.yml
``` yaml
- name: 'db server'
hosts: 'db.example.com'
become: true
roles:
- 'r_pufky.db/postgres'
- 'r_pufky.db/maria'
```
## Issues
Create a bug and provide as much information as possible.
Associate pull requests with a submitted bug.
## License
[AGPL-3.0 License](https://github.com/r-pufky/ansible_db/blob/main/LICENSE)
## Author Information
https://keybase.io/rpufky
BLOCKING OS Distribution upgrades REQUIRE: Requires upstream source repo update.
Role Details: Updated: 2022-10-09 galaxy source service docs
Ports
---
###############################################################################
# Ports Configuration
###############################################################################
# Ports should be managed externally via an OS role.
#
# Reference:
# * https://docs.ansible.com/ansible/latest/collections/community/general/ufw_module.html
ports:
- {proto: 'tcp', from_ip: 'any', to_port: 3306, direction: 'in', comment: 'mysql service'}
- {proto: 'tcp', from_ip: 'any', to_port: 5432, direction: 'in', comment: 'postgresql service'}
Defaults
---
###############################################################################
# postgresql Role Configuration
###############################################################################
# Loosely from https://github.com/geerlingguy/ansible-role-postgresql
#
# postgres will fail to install if pre-existing databases are in data dir. It
# expects a clean /etc/postgresql{-common}, /var/lib/postgresql.
#
# Two ways to setup role:
# 1. Use local disk for postgresql installation, do not mount postgres dirs.
# Use backup and restore.
# 1. Use mounts to carryover over install from machine to machine. Map:
# * data: /var/lib/postgresql
# * config: /etc/postgresql
# * common: /etc/postgresql-common
#
# Set pg_hba if setting firewall.
#
# Reference:
# * https://wiki.postgresql.org/wiki/Apt
# * https://stackoverflow.com/questions/2748607/how-to-thoroughly-purge-and-reinstall-postgresql-on-ubuntu
# * https://github.com/geerlingguy/ansible-role-postgresql
# User that postgres will run under. Assumes externally managed.
db_postgres_user: 'postgres'
db_postgres_group: 'postgres'
# Create 'postgres' user if not detected? See: vars/main.yml.
db_postgres_create_user: false
db_postgres_version: '12'
db_postgres_data_dir: '/var/lib/postgresql/{{ db_postgres_version }}/main'
db_postgres_backup_dir: '/var/lib/postgresql/{{ db_postgres_version }}/backup'
db_postgres_bin_path: '/usr/lib/postgresql/{{ db_postgres_version }}/bin'
db_postgres_config_path: '/etc/postgresql/{{ db_postgres_version }}/main'
db_postgres_packages:
- 'postgresql-{{ db_postgres_version }}'
db_postgres_python_library: 'python3-psycopg2'
db_postgres_locales:
- 'en_US.UTF-8'
# `md5` or `scram-sha-256` (https://www.postgresql.org/docs/10/auth-methods.html)
# Default: 'md5'
db_postgres_auth_method: 'md5'
# Used for local connection to issue postgres commands
db_postgres_unix_sockets:
- '/var/run/postgresql'
# pg_dumpall pg_dump options
db_postgres_backup_options: >
--no-owner
--no-privileges
# Enable separate weekly cronjob to backup databases (includes compression)
db_postgres_enable_weekly: true
# databases created if non-existent, and auto-imported from latest backup.
# Reference: https://docs.ansible.com/ansible/latest/collections/community/postgresql/postgresql_db_module.html
db_postgres_databases: []
# - name: exampledb # required
# lc_collate: # default: 'en_US.UTF-8'
# lc_ctype: # default: 'en_US.UTF-8'
# encoding: # default: 'UTF-8'
# template: # default: 'template0'
# login_host: # default: 'localhost'
# login_password: # default: not set
# login_user: # default: '{{ db_postgres_user }}'
# login_unix_socket: # default: '{{ db_postgres_unix_sockets[0] }}'
# port: # default: not set
# owner: # default: '{{ db_postgres_user }}'
# conn_limit: # default: not set
# state: # default: 'present'
# Reference: https://docs.ansible.com/ansible/latest/collections/community/postgresql/postgresql_users_module.html
db_postgres_users: []
# - name: example-user # required
# password: # default: not set
# encrypted: # default: not set
# priv: # default: not set
# role_attr_flags: # default: not set
# db: # default: not set
# login_host: # default: 'localhost'
# login_password: # default: not set
# login_user: # default: '{{ db_postgres_user }}'
# login_unix_socket: # default: '{{ db_postgres_unix_sockets[0] }}'
# port: # default: not set
# conn_limit: # default: not set
# state: # default: 'present'
###############################################################################
# postgresql postgresql.conf Configuration
###############################################################################
# Defaults from Debian 10
# [file locations]
db_postgres_external_pid_file: '/var/run/postgresql/{{ db_postgres_version }}-main.pid'
# [connections and authentication]
db_postgres_port: 5432 # tcp port
db_postgres_max_connections: 100
db_postgres_unix_socket_directories: '/var/run/postgresql'
db_postgres_ssl: 'on'
db_postgres_ssl_cert_file: '/etc/ssl/certs/ssl-cert-snakeoil.pem'
db_postgres_ssl_key_file: '/etc/ssl/private/ssl-cert-snakeoil.key'
# Freeform configuration text for adding additional section
db_postgres_connections_authentication_extensions: ''
# [resource usage]
db_postgres_shared_buffers: '128MB'
db_postgres_dynamic_shared_memory_type: 'posix'
# Freeform configuration text for adding additional section
db_postgres_resource_usage_extensions: ''
# [write ahead log]
db_postgres_max_wal_size: '1GB'
db_postgres_min_wal_size: '80MB'
# Freeform configuration text for adding additional section
db_postgres_write_ahead_log_extensions: ''
# [replication]
# Freeform configuration text for adding additional section
db_postgres_replication_extensions: ''
# [query tuning]
# Freeform configuration text for adding additional section
db_postgres_query_tuning_extensions: ''
# [reporting and logging]
db_postgres_log_line_prefix: '%m [%p] %q%u@%d '
db_postgres_log_timezone: 'Etc/UTC'
# Freeform configuration text for adding additional section
db_postgres_reporting_logging_extensions: ''
# [process title]
db_postgres_cluster_name: '{{ db_postgres_version }}/main'
# Freeform configuration text for adding additional section
db_postgres_process_title_extensions: ''
# [statistics]
db_postgres_stats_temp_directory: '/var/run/postgresql/{{ db_postgres_version }}-main.pg_stat_tmp'
# Freeform configuration text for adding additional section
db_postgres_statistics_extensions: ''
# [autovacuum]
# Freeform configuration text for adding additional section
db_postgres_autovacuum_extensions: ''
# [client connection defaults]
db_postgres_datestyle: 'iso, mdy'
db_postgres_timezone: 'Etc/UTC'
db_postgres_lc_messages: 'C'
db_postgres_lc_monetary: 'C'
db_postgres_lc_numeric: 'C'
db_postgres_lc_time: 'C'
db_postgres_default_text_search_config: 'pg_catalog.english'
# Freeform configuration text for adding additional section
db_postgres_client_connection_defaults_extensions: ''
# [lock management]
# Freeform configuration text for adding additional section
db_postgres_lock_management_extensions: ''
# [version and platform compatibility]
# Freeform configuration text for adding additional section
db_postgres_version_platform_compatiblity_extensions: ''
# [error handling]
# Freeform configuration text for adding additional section
db_postgres_error_handling_extensions: ''
# [config file includes]
# Freeform configuration text for adding additional section
db_postgres_include_dir: 'conf.d'
db_postgres_config_file_includes_extensions: ''
# [customized options]
# Freeform configuration text for adding additional section
db_postgres_customized_options_extensions: ''
###############################################################################
# postgresql pg_hba.conf Configuration
###############################################################################
# Defaults from Debian 10
#
# TYPE DATABASE USER ADDRESS METHOD
# Reference:
# * https://www.postgresql.org/docs/current/auth-pg-hba-conf.html
db_postgres_hba:
- {type: 'local', database: 'all', user: 'postgres', address: '', method: 'peer'}
- {type: 'local', database: 'all', user: 'all', address: '', method: 'peer'}
- {type: 'host', database: 'all', user: 'all', address: '127.0.0.1/32', method: '{{ db_postgres_auth_method }}'}
- {type: 'host', database: 'all', user: 'all', address: '::1/128', method: '{{ db_postgres_auth_method }}'}
- {type: 'local', database: 'replication', user: 'all', address: '', method: 'peer'}
- {type: 'host', database: 'replication', user: 'all', address: '127.0.0.1/32', method: '{{ db_postgres_auth_method }}'}
- {type: 'host', database: 'replication', user: 'all', address: '::1/28', method: '{{ db_postgres_auth_method }}'}
###############################################################################
# postgresql pg_ident.conf Configuration
###############################################################################
# Freeform configuration text for adding additional section options.
db_postgres_ident_extensions: ''
###############################################################################
# postgresql pg_ctl.conf Configuration
###############################################################################
# Freeform configuration text for adding additional section options.
db_postgres_ctl_options: ''
###############################################################################
# postgresql start.conf Configuration
###############################################################################
# Freeform configuration text for adding additional section options.
db_postgres_start: 'auto'
###############################################################################
# postgresql environment Configuration
###############################################################################
# Freeform configuration text for adding additional section options.
db_postgres_environment_extensions: ''