PostgreSQL system role
The PostgreSQL system role installs, configures, and starts the PostgreSQL server.
The role also optimizes the database server settings to improve performance.
Requirements
The role currently works with the PostgreSQL server 10, 12, and 13.
Role Variables
postgresql_verison
You can set the version of the PostgreSQL server to 10, 12, or 13.
postgresql_version: "13"
postgresql_password
Optionally, you can set a password for the postgres
database
superuser. By default, no password is set, and a datababase is
accessible from the postgres
system account through a UNIX socket. It
is recommended to encrypt the password by using Ansible Vault.
postgresql_password: !vault |
$ANSIBLE_VAULT;1.2;AES256;dev
....
postgresql_pg_hba_conf
The content of the postgresql_pg_hba_conf
variable replaces the
default upstream configuration in the /var/lib/pgsql/data/pg_hba.conf
file.
postgresql_pg_hba_conf:
- type: local
database: all
user: all
auth_method: peer
- type: host
database: all
user: all
address: '127.0.0.1/32'
auth_method: ident
- type: host
database: all
user: all
address: '::1/128'
auth_method: ident
postgresql_server_conf
The content of the postgresql_server_conf
variable is added to the end
of the /var/lib/pgsql/data/postgresql.conf
file. As a result, the
default settings are overwritten.
postgresql_server_conf:
ssl: on
shared_buffers: 128MB
huge_pages: try
postgresql_ssl_enable
To set up an SSL/TLS connection, set the postgresql_ssl_enable
variable to true
and provide a server certificate and a private key.
postgresql_ssl_enable: true
postgresql_cert_name
If you want to use your own certificate and private key, use the
postgresql_cert_name
variable to specify the certificate name. You
must keep both certificate and key files in the same directory and under
the same name with the .crt
and .key
suffixes.
For example, if your certificate file is located in
/etc/certs/server.crt
and your private key in /etc/certs/server.key
,
set the postgresql_cert_name
value to:
postgresql_cert_name: /etc/certs/server
postgresql_certificates
The postgresql_certificates
variable requires a list
of dict
in
the same format as used by the redhat.rhel_system_roles.certificate
role. Specify the postgresql_certificates
variable if you want the
certificate role to generate certificates for the PostgreSQL server
configured by the PostgreSQL role. In the following example, a
self-signed
certificate postgresql_cert.crt
is generated in the
/etc/pki/tls/certs/
directory. By default, no certificates are
automatically generated ([]
).
postgresql_certificates:
- name: postgresql_cert
dns: ['localhost', 'www.example.com']
ca: self-sign
postgresql_input_file
To run an SQL script, define a path to your SQL file by using the
postgresql_input_file
variable:
postgresql_input_file: "/tmp/mypath/file.sql"
postgresql_server_tuning
By default, the PostgreSQL system role enables server settings
optimization based on system resources. To disabe the tuning, set the
postgresql_server_tuning
variable to false
.
postgresql_server_tuning: false
See the examples/
for details.
Idempotence
This section should cover role behavior for repeated runs.
Password change
Once you set the password by using the postgresql_password
variable,
it is impossible to change the password by setting another value. You
must use the postgresql_password
variable for every database acces
under the superuser, including running an SQL script (the functionality
of the postgresql_input_file
variable).
Config file redefinition
Configuration files generated from postgresql_pg_hba_conf
and
postgresql_conf
are regenerated within each single run. Therefore,
every change rewrites the previous configuration.
Version change
Once the PostgreSQL server is installed, it is impossible to upgrade or
downgrade the server by increasing or decreasing the version number in
the postgresql_version
variable.
Server tunning
This option reflects the setup of the latest run of the role.
SSL usage
This option reflects the setup of the latest run of the role. The PostgreSQL server needs properly defined certificates and keys to run with enabled SSL/TLS.
Example Playbook
- name: Manage postgres
hosts: all
vars:
postgresql_version: "13"
postgresql_password: !vault |
$ANSIBLE_VAULT;1.2;AES256;dev
....
roles:
- redhat.rhel_system_roles.postgresql
You can find more examples in the examples/
directory.
License
MIT