⭐ Star us on GitHub — it motivates us a lot!
Install and configure PostgreSQL server on Debian and RedHat systems using this Ansible role. It provides a flexible and automated way to set up PostgreSQL databases, users, extensions, and more.
- Role Requirements
- Role Dependencies
- Role Installation
- Features and Tags
- Supported Linux/PostgreSQL Versions
- Role features in use
- Proxy usage
- Installation
- Patroni integration
- Configuration
- Auto tuning
- Physical replication
- Vacuum setup
- Backup setup
- User and database management
- Tablespaces management
- Databases management
- Ownership and privileges management
- Extensions management
- SQL executions
- Advanced customized installation
- Uninstallation
- Full example playbook
- Hardening
- Contributing
- License
- Author information
Ansible >= 2.10
- community.general
- community.postgresql==3.2.0
ansible-galaxy install claranet.postgresql
This role support the following features and tags along with control variables in the following order during execution:
Feature | Control variable(s) | Tag(s) |
---|---|---|
Uninstallation | postgresql_uninstall_1, postgresql_uninstall_2 | uninstallation |
Installation | postgresql_install | install, installation |
Datadir initialization | postgresql_initialize | init,initialize,initialise |
Auto tune (with pg-config.org) | postgresql_autotune | autotune, auto-tune |
Configuration | postgresql_configure | config, configure, configuration |
Replication | postgresql_replication, postgresql_configure_replication | repli, replication |
Vacuum | postgresql_vacuum | vacuum |
Backup | postgresql_backup | backup |
User & membership management | postgresql_manage_objects | user, users |
Tablespace management | postgresql_manage_objects | tblspc, tablespace, tablespaces |
Database management | postgresql_manage_objects | db, database, databases |
Ownership & privileges management | postgresql_manage_objects | owner, owners, ownership, priv, privs, privileges |
Extensions management | postgresql_manage_objects | ext, extension, extensions |
SQL code executions | postgresql_manage_objects | query, script |
Linux/PostgreSQL | 12 | 13 | 14 | 15 | 16 | 17 |
---|---|---|---|---|---|---|
Debian 11 | Yes | Yes | Yes | Yes | Yes | Yes |
Debian 12 | Yes | Yes | Yes | Yes | Yes | Yes |
Ubuntu 22.04 | Yes | Yes | Yes | Yes | Yes | Yes |
Ubuntu 24.04 | Yes | Yes | Yes | Yes | Yes | Yes |
RockyLinux 8.9 | Yes | Yes | Yes | Yes | Yes | Yes |
RockyLinux 9.3 | Yes | Yes | Yes | Yes | Yes | Yes |
Fedora 38 | No | No | No | No | No | No |
This role supports use of proxies.
The variables postgresql_http_general_proxy
and postgresql_https_general_proxy
can be used to specify a proxy for general internet access (such as downloading files).
The variables postgresql_http_pkg_proxy
and postgresql_https_pkg_proxy
can be used to specify a proxy for package manager interaction (such as downloading packages or updating cache).
Notes:
These variables are translated to environnement variables http_proxy
and https_proxy
which are passed to corresponding tasks.
default PostgreSQL version is 16 PostgreSQL and locales installation.
postgresql_version: "16"
# Debian only. Used to generate the locales used by PostgreSQL databases.
postgresql_locales:
- 'en_US.UTF-8'
- 'fr_FR.UTF-8'
# Redhat only. For more info check: https://www.thegeekdiary.com/how-to-add-locale-on-centos-rhel-8/
postgresql_locale_packages:
- glibc-langpack-en
- glibc-langpack-fr
# Controls running tasks handling: postgreSQL packages installation
postgresql_install: true
When using Patroni to manage PostgreSQL replication, Patroni expects PostgreSQL packages be installed upfront. However once the Patroni cluster is bootstrapped, the underlying PostgreSQL instances can be managed just like any other regular replication.
In order to install PostgreSQL pacakges before bootstrapping a Patroni cluster this role can be invoked with the following variables which will cause the role to only perform installation.
postgresql_is_patroni: true
postgresql_install: true
postgresql_only_install: true
After Patroni bootstrap this role can be invoked with the following combination of variables to essentially skip the packages installation and manage the cluster like a pre configuration replication setup:
postgresql_is_patroni: true
postgresql_install: false
Example for configuration related variables:
postgresql_port: 5432
postgresql_listen_addresses: 0.0.0.0
postgresql_shared_preload_libraries:
- pg_stat_statements
postgresql_max_connections: 100
# Custom PostgreSQL configuration options provided by the user
postgresql_global_config_options_extra:
- option: log_statement
value: all
postgresql_hba_entries_extra: []
# - {contype: local, databases: all, users: postgres, method: peer}
# Default authentication method used method for the default hba rules
# postgresql_auth_method: "{{ ansible_fips | ternary('scram-sha-256', 'md5') }}"
postgresql_hba_use_raw: false
postgresql_hba_raw: |
# TYPE DATABASE USER ADDRESS METHOD
local all postgres peer
host all all 127.0.0.1/32 md5
host all all ::1/128 md5
# Allow service restart for configuration changes that require it
postgresql_config_change_allow_restart: true
# Controls running tasks handling: configuration
postgresql_configure: true
# Enable SSL
postgresql_ca_enabled: true
# Certificate file subject used during generation
postgresql_ssl_cert_subj: /C=FR/ST=FR
Notes:
SSL configuration (introduced in v3.0.0
) is enabled by default. The associated key and cert files are only regenerated if they are missing on the remote host.
By default, this role restarts the PostgreSQL service during subsequent configuration changes after the initial engine installation, ensuring all changes are applied immediately. However, this behavior can cause potential service outages.
To prevent automatic restarts, you can set the variable postgresql_config_change_allow_restart
(introduced in v2.1.0
) to false
. Starting with (v3.0.0
), the default value of this variable will change to false
, meaning the role will avoid restarting PostgreSQL by default. If you rely on the current behavior, you will need to explicitly set this variable to true in your configuration.
In relation to HBA rules, you have the option to configure the variable postgresql_hba_use_raw
as true
and specify the contents of postgresql_hba_raw
. These contents will be inserted directly into the pg_hba.conf
file.
Alternatively, if you possess a file containing these rules, you can set the postgresql_hba_template_path
variable to the path of that file on the Ansible controller. In this case, the specified file will be copied to replace the pg_hba.conf
file.
However, it's crucial to note that when using this approach, the entire content of the HBA file becomes your responsibility. You must ensure that there are rules allowing the postgres
system user to connect to the PostgreSQL server without requiring a password and authorizing replication in the relevant context
This role supports the use of the website pgconfig.org for automatically tunning some of configuration parameters of the postgresql server.
You can check the full documentation on the available configurations parameters.
Configuration example for variables (those are the default values):
postgresql_autotune: true
postgresql_autotune_base_url: https://api.pgconfig.org
postgresql_autotune_pg_version: "{{ postgresql_version }}"
# linux/windows/unix
postgresql_autotune_os_type: linux
# 386/x86-64
postgresql_autotune_arch: x86-64
# HDD/SSD/SAN
postgresql_autotune_drive_type: SSD
# WEB/OLTP/DW/Mixed/Desktop
postgresql_autotune_env_name: OLTP
postgresql_autotune_cpus: "{{ ansible_processor_nproc | d('') }}"
# Total ram in GB
postgresql_autotune_total_ram: "{{ ((ansible_memtotal_mb / 1024) | round | int) | d('') }}"
Configuration example for the primary server:
postgresql_replication: true
postgresql_replication_user: replication_user
postgresql_replication_password: replication_password
postgresql_replication_role: primary
# Used to generate hba rules to allow the specified servers to connect to the primary server
postgresql_replication_replica_addresses: [192.168.1.6/32, 192.168.1.7/32]
# User provided replication specific hba rules that overwrites the generated ones
postgresql_replication_hba_entries: []
# - contype: host
# databases: replication
# users: "{{ postgresql_replication_user }}"
# address: "{{ postgresql_replication_replica_address }}"
# method: "{{ postgresql_replication_auth_method }}"
Configuration example for the replicas:
postgresql_replication: true
postgresql_replication_user: replication_user
postgresql_replication_password: replication_password
postgresql_replication_role: replica
postgresql_replication_primary_address: 192.168.1.5
postgresql_replication_primary_port: 5432
postgresql_replication_primary_inventory_name: node1 # primary server name in the ansible inventory
Using slots for replication:
postgresql_replication_slot: replica1_slot
postgresql_replication_create_slot: true
When set to true the variable postgresql_replication_create_slot
ensures the specified replication slot exists before running the pg_basebackup
command run to copy data from the primary.
Notes:
When using the slot feature for replication, make sure to indicate a different slot for each replica. You can set that value in the host_vars for each server.
Advanced configuration:
# Authentication method specific for the replica hosts
# postgresql_replication_auth_method: "{{ postgresql_auth_method }}"
# --checkpoint parameter value of the pg_basebackup command
postgresql_pg_basebackup_checkpoint: fast # spread
# --wal-method parameter value of the pg_basebackup command
postgresql_pg_basebackup_walmethod: stream # none/stream/fetch
# extra arguments appended to the build pg_basebackup command
postgresql_pg_basebackup_args: ""
# Actual pg_basebackup built with the previous parameters
# DO NOT override this variable unless you know what you are doing
postgresql_pg_basebackup_cmd: {{ _postgresql_bin_path }}/pg_basebackup --no-password --host {{ postgresql_replication_primary_address }} --port {{ postgresql_replication_primary_port }} --username {{ postgresql_replication_user }} --pgdata {{ _postgresql_data_dir }} --checkpoint {{ postgresql_pg_basebackup_checkpoint }} {{ (postgresql_replication_slot != '') | ternary('--slot ' ~ postgresql_replication_slot, '') }} --wal-method {{ postgresql_pg_basebackup_walmethod }} --write-recovery-conf --verbose --progress {{ postgresql_pg_basebackup_args }}
# Controls running tasks handling: actual replication configuration
# DO NOT override this variable unless you know what you are doing
postgresql_configure_replication: true
(new in v2.0.0)
By default vaccum is enabled (postgresql_vacuum: true
), with vacuum and analyze planned daily at 23:00
Configuration example for vacuum.
To disable:
postgresql_vacuum: false
To change schedule to 21:00:
postgresql_vacuum_schedule:
minute: 0
hour: 21
To vacuum only (other options : vacuumanalyze, vacuumfull, vacuumonly, analyzeonly)
postgresql_vacuum_option: "vacuumonly"
🚨 The provided backup script is not intended for use within Claranet environments. Claranet has superior and more robust backup solutions that should be used for production systems. This script is designed solely for development, testing, or demonstration purposes and should not replace established backup practices in live environments. 🚨
By default, the backup is disabled (postgresql_backup: false
).
Configuration example for backup.
# Allow ansible to setup postgresql backups when running
postgresql_backup: true
# Root directory containing the backups
postgresql_backup_root_dir: /var/backups/postgresql
postgresql_backup_mail_addr: [email protected]
postgresql_backup_schedule:
hour: 0
minute: 0
# 3 days retentions for daily backups
postgresql_backup_brdaily: 3
# disable weekly and monthly backups
postgresql_backup_doweekly: 0
postgresql_backup_domonthly: 0
# Weekly and monthly backups are disabled so these values don't really matter
postgresql_backup_brweekly: 0
postgresql_backup_brmontly: 0
Configuration example for managing users:
postgresql_users:
# Create two groups 'group1' and 'group2' by making use of thr role_attr_flags attribute
- name: group1
role_attr_flags: NOLOGIN
- name: group2
role_attr_flags: NOLOGIN
# Create 'user1' and 'user2' with default parameters
- name: user1
- name: user2
# Create user 'jdoe' with more personalized parameters
- name: jdoe
password: password
comment: this is a test user
expires: "Jun 21 2029"
postgresql_memberships:
# Ensure the role 'user1' belongs to group 'group1'
- groups:
- group1
target_roles:
- user1
state: present
# Ensure the role 'user2' does not belong to the group 'group2'
- groups:
- group2
target_roles:
- user2
state: absent
# Ensure the role 'jdoe' does not belong to any group
- groups: []
target_roles:
- jdoe
state: exact
Notes:
Check the links for a documentation on all the available options for defining items within the variables:
COnfiguration example for managing tablespaces:
postgresql_tablespaces:
# Create tablespace 'ssd'
- name: ssd
set:
random_page_cost: 1
seq_page_cost: 1
owner: jdoe
location: /tmp/ssd
location_create: true # default is false
state: present # default is present
location_owner: postgres # default is postgres
location_group: postgres # default is postgres
location_mode: '0700' # default is '0700'
# Delete tablespaces 'temp2'
- name: temp2
state: absent
location: /tmp/temp2_tblspc
set:
random_page_cost: 1
owner: user1
Notes:
When combining location_create: true
with state: present
the role will create the location of the tablespace with the specified permissions before creating the tablespace itself.
If you ensure the existence of that location by others means, feel free to not set the variables location_*
.
Configuration example for managing databases:
postgresql_databases:
- name: db1
owner: user1
encoding: UTF-8
lc_collate: en_US.UTF-8
lc_ctype: en_US.UTF-8
conn_limit: 100
template: template0
- name: db2
owner: user2
- name: db3
state: absent
postgresql_schemas:
- name: acme
db: db1
- name: acme
db: db2
- name: not_existing_shema
db: db1
owner: user1
state: absent
cascade_drop: true
postgresql_tables:
- name: table1
db: db1
owner: user1
columns:
- id SERIAL PRIMARY KEY
- name VARCHAR(50)
- age INT
- email VARCHAR(100)
tablespace: ssd
storage_params:
- fillfactor=10
- autovacuum_analyze_threshold=1
- name: acme.table2
db: db1
columns: waste_id int
unlogged: true
Notes:
Check the links for a documentation on all the available options for defining items within the variables:
postgresql_privs:
- roles: group1 # group1 and user1 are granted all privs on all object wihtin the public schema of the example db
db: db1
privs: ALL
objs: table1
type: table
# schema: public
grant_option: true
- roles: user2 # grant user2 user all privs on postgres database
db: postgres
type: database
privs: ALL
objs: db1,db2
grant_option: true
- roles : group1 # grant group1 role all privs on all tables and all sequences of database db1
db: db1
objs: TABLES,SEQUENCES
privs: ALL
type: default_privs
postgresql_ownerships:
- db: db1
new_owner: user1
obj_name: table1
obj_type: table
- db: db2 # reassign all dbs owned by user1 to user2 and all objects in db2 to user2
new_owner: user2
reassign_owned_by: user1
Notes:
Check the links for a documentation on all the available options for defining items within the variables:
Configuration example for extensions management:
postgresql_extensions:
- name: pg_stat_statements
db: db1
cascade: true
version: latest
schema: public
- name: non_existing_extension
db: db1
state: absent
Notes:
For the extensions with state: present, version: latest
, the role will always report changed: false
as the underlying module does not differentiate when the extension is actually updated or not.
Configuration example for running sql:
postgresql_queries:
- query: SELECT version()
db: db1
- query:
- select * from public.table1
db: db1
postgresql_scripts:
- path: /tmp/insert_in_table1.sql
db: db1
Notes:
Check the links for a documentation on all the available options for defining items within the variables:
It is highly recommended you modify these variables only if you know what you're doing.
# Allows overriding packages used to install PostgreSQL
# postgresql_packages: []
# Include pg_hba file update as part of configuration process
postgresql_configure_pghba: true
# New postgresql installation datadir
postgresql_data_dir:
# Extra arguments passed to the initdb binary during database initialization
postgresql_initdb_extra_args: ''
# Debian only. postgresql cluster name
postgresql_cluster_name: main
# PostgreSQL system user/group
postgresql_user: postgres
postgresql_group: postgres
# Postgresql service state after role run
postgresql_service_state: started
# Whether or not to enable the postgresql service after installation
postgresql_service_enabled: true
# PostgreSQL unix_socket_directories config parameter
postgresql_unix_socket_directories: [/run/postgresql]
# Permissions for the PostgreSQL unix sockets (default is distro dependant)
postgresql_unix_socket_directories_mode: ''
# Permissions for the postgresql log directory
postgresql_log_directory_mode: '0700'
# Whether or not to create a tmpfiles.d postgresql file to persist permissions on unix socket directories and log directories accross system rebbots
postgresql_persist_permissions: true
# Path to the template used by Ansible to create the tempfile conf to persist permissions
# You can update this path to a custom file to completely customize the persisting rules
postgresql_tempfile_src_template_path: etc/tmpfiles.d/postgresql-common.conf.j2
# Destination path for the tempfile configuration
postgresql_tempfile_dest_path: /etc/tmpfiles.d/postgresql-common.conf
# File permissions and owner/group of the postgresql tempfile configuration
postgresql_tempfile_mode: '0644'
postgresql_tempfile_owner: root
postgresql_tempfile_group: root
# SSL cert file path, can be absolute or relative (to data dir)
# postgresql_ssl_cert_file: (default is os specific. vars/<os>.yml)
# SSL key file path, can be absolute or relative (to data dir)
# postgresql_ssl_key_file: (default is os specific. vars/<os>.yml)
# Controls running tasks handling: cluster initialization
postgresql_initialize: true
# Controls running tasks handling: engine specific objects like databases,users,tablespaces,ownerships,extensions,sqlquery executions
postgresql_manage_objects: true
# Controls running tasks handling: actual replication configuration
postgresql_configure_replication: true
# PostgreSQl connection vars object
# This variable is used to feed common connection parameters when calling community.postgresql modules
# to manage database objects (users, databases, schemas, etc..)
postgresql_conn_vars:
ca_cert: null # alias ssl_rootcert
connect_params: null
login_host: null
login_password: null
login_unix_socket: "{{ postgresql_unix_socket_directories[0] | d(null, true) }}"
login_user: "{{ postgresql_user }}"
login_port: "{{ postgresql_port }}"
session_role: null
ssl_cert: null
ssl_key: null
ssl_mode: null
If you want to uninstall a Postgresql installation with this role, set both variables postgresql_uninstall_1
, postgresql_uninstall_1
to true
and use the corresponding tag (uninstallation
).
---
- name: Converge
hosts: all
become: true
gather_facts: true
vars:
postgresql_version: "16"
# Run debug tasks within the role
postgresql_debug: true
# Configuration
postgresql_port: 5432
postgresql_listen_addresses: 0.0.0.0
postgresql_shared_preload_libraries:
- pg_stat_statements
postgresql_max_connections: 100
# Custom configuration options provided by the user
postgresql_global_config_options_extra:
- option: log_statement
value: all
postgresql_hba_entries_extra: []
# - {contype: local, databases: all, users: postgres, method: peer}
postgresql_autotune: true
# postgresql_autotune_base_url: http://192.168.56.101:3000
postgresql_users_no_log: false
postgresql_users:
# Create two groups 'group1' and 'group2' by making use of thr role_attr_flags attribute
- name: group1
role_attr_flags: NOLOGIN
- name: group2
role_attr_flags: NOLOGIN
# Create 'user1' and 'user2' with default parameters
- name: user1
- name: user2
# Create user 'jdoe' with more personalized parameters
- name: jdoe
password: password
comment: this is a test user
expires: "Jun 21 2029"
postgresql_memberships:
# Ensure the role 'user1' belongs to group 'group1'
- groups:
- group1
target_roles:
- user1
state: present
# Ensure the role 'user2' does not belong to the group 'group2'
- groups:
- group2
target_roles:
- user2
state: absent
# Ensure the role 'jdoe' does not belong to any group
- groups: []
target_roles:
- jdoe
state: exact
postgresql_tablespaces:
# Create tablespace 'ssd'
- name: ssd
set:
random_page_cost: 1
seq_page_cost: 1
owner: jdoe
location: /tmp/ssd
location_create: true # default is false
state: present # default is present
location_owner: postgres # default is postgres
location_group: postgres # default is postgres
location_mode: '0700' # default is '0700'
# Delete tablespaces 'temp2'
- name: temp2
state: absent
location: /tmp/temp2_tblspc
set:
random_page_cost: 1
owner: user1
postgresql_databases:
- name: db1
owner: user1
encoding: UTF-8
lc_collate: en_US.UTF-8
lc_ctype: en_US.UTF-8
conn_limit: 100
template: template0
- name: db2
owner: user2
- name: db3
state: absent
postgresql_schemas:
- name: acme
db: db1
- name: acme
db: db2
- name: not_existing_shema
db: db1
state: absent
cascade_drop: true
postgresql_tables:
- name: table1
db: db1
owner: user1
columns:
- id SERIAL PRIMARY KEY
- name VARCHAR(50)
- age INT
- email VARCHAR(100)
tablespace: ssd
storage_params:
- fillfactor=10
- autovacuum_analyze_threshold=1
- name: acme.table2
db: db1
columns: waste_id int
unlogged: true
# like: public.table1
# including: comments, indexes
# - name: table2
# db: db1
# truncate: true
# - name: acme.table2
# db: db1
# like: public.table2
# - name: table2
# db: db2
# state: absent
# cascade: true
postgresql_extensions:
- name: pg_stat_statements
db: db1
cascade: true
version: latest
schema: public
- name: non_existing_extension
db: db1
state: absent
postgresql_queries:
- query: SELECT version()
db: db1
- query:
- select * from public.table1
db: db1
postgresql_scripts:
- path: /tmp/insert_in_table1.sql
db: db1
postgresql_privs:
- roles: group1 # group1 and user1 are granted all privs on all object wihtin the public schema of the example db
db: db1
privs: ALL
objs: table1
type: table
# schema: public
grant_option: true
- roles: user2 # grant nreslou user all privs on nreslou database by first connecting to the postgres maintenance db
db: postgres
type: database
privs: ALL
objs: db1,db2
grant_option: true
# - roles: user1
# db: db2
# type: function
# objs: add(int:int)
# privs: ALL
# grant_option: true
postgresql_ownerships:
- db: db1
new_owner: user1
obj_name: table1
obj_type: table
# - db: db2 # reassign all dbs owned by user1 to user2 and all objects in db2 to user2
# new_owner: user2
# reassign_owned_by: user1
# standalone installation
postgresql_replication: false
# Disable backups setup by Ansible
postgresql_backup: false
roles:
- role: claranet.postgresql
Checkout the Contributing if you are looking for a guide on how to setup an environnement so you can test this role as a developper.
©️ License
Mozilla Public License Version 2.0
Proudly made by the Claranet team and inspired by: