Getting started¶
General configuration¶
On a new host, PostgreSQL server will be configured with a default cluster
main
running on port 5432
. Cluster will only listen to connections from
localhost
, Ansible management account and root
account will be able to
login to the PostgreSQL server using postgres
role, for example:
user@host:~$ psql -U postgres
The PostgreSQL version installed by the role will be a default version offered by the distribution. If you want PostgreSQL 9.4 on Debian Wheezy, or an upstream version of the server, you can enable the upstream APT repository by adding in inventory:
postgresql_server__upstream: True
Check postgresql_server__preferred_version to learn more about selecting custom PostgreSQL versions.
After installation you can use debops.postgresql role to configure PostgreSQL roles and databases.
Remote access to the database¶
PostgreSQL server listens only for connections on localhost
by default. To
enable remote access, you need to change the
postgresql_server__listen_addresses
list to specify either IP addresses of
the interfaces you want your host to listen on or *
for all interfaces.
Because firewall by default blocks all connections to PostgreSQL server, you
will also need to specify IP addresses or CIDR subnets which should be able to
connect to the clusters. Example configuration of variables in inventory:
postgresql_server__listen_addresses: [ '*' ]
postgresql_server__allow: [ '192.0.2.0/24', '2001:db8::/32' ]
Default set of Host-Based Authentication rules permit connections from remote hosts that are in the same subnet as the server, only over SSL, and require the correct password to be provided to accept connections. If you want to allow connections from other subnets than the server, you will need to add your own HBA entries to the PostgreSQL cluster configuration. Example for the default cluster:
postgresql_server__cluster_main:
name: 'main'
port: '5432'
hba:
- type: 'hostssl'
database: 'samerole'
user: 'all'
address: [ '192.0.2.0/24', '2001:db8::/32' ]
method: 'md5'
The debops.postgresql_server
role is designed to use the PKI infrastructure
managed by debops.pki role. See its documentation for more details.
Example inventory¶
To install and configure PostgreSQL server on a host, you need to add the host
to the [debops_service_postgresql_server]
Ansible host group:
[debops_service_postgresql_server]
hostname
Example playbook¶
Here's an example playbook which uses the debops.postgresql_server
role:
---
- name: Manage PostgreSQL server
collections: [ 'debops.debops', 'debops.roles01',
'debops.roles02', 'debops.roles03' ]
hosts: [ 'debops_service_postgresql_server' ]
become: True
environment: '{{ inventory__environment | d({})
| combine(inventory__group_environment | d({}))
| combine(inventory__host_environment | d({})) }}'
roles:
- role: keyring
tags: [ 'role::keyring', 'skip::keyring', 'role::postgresql_server' ]
keyring__dependent_apt_keys:
- '{{ postgresql_server__keyring__dependent_apt_keys }}'
- role: apt_preferences
tags: [ 'role::apt_preferences', 'skip::apt_preferences' ]
apt_preferences__dependent_list:
- '{{ postgresql_server__apt_preferences__dependent_list }}'
- role: etc_services
tags: [ 'role::etc_services', 'skip::etc_services' ]
etc_services__dependent_list:
- '{{ postgresql_server__etc_services__dependent_list }}'
- role: ferm
tags: [ 'role::ferm', 'skip::ferm' ]
ferm__dependent_rules:
- '{{ postgresql_server__ferm__dependent_rules }}'
- role: python
tags: [ 'role::python', 'skip::python', 'role::postgresql' ]
python__dependent_packages3:
- '{{ postgresql_server__python__dependent_packages3 }}'
python__dependent_packages2:
- '{{ postgresql_server__python__dependent_packages2 }}'
- role: locales
tags: [ 'role::locales', 'skip::locales' ]
locales__dependent_list:
- '{{ postgresql_server__locales__dependent_list }}'
- role: postgresql_server
tags: [ 'role::postgresql_server', 'skip::postgresql_server' ]
Ansible tags¶
You can use Ansible --tags
or --skip-tags
parameters to limit what
tasks are performed during Ansible run. This can be used after the host is first
configured to speed up playbook execution, when you are sure that most of the
configuration is already in the desired state.
Available role tags:
role::postgresql_server
- Main role tag, should be used in the playbook to execute all of the role tasks as well as role dependencies.
role::postgresql_server:packages
- Run tasks related to package installation
role::postgresql_server:config
- Run tasks related to PostgreSQL Server configuration.
role::postgresql_server:auto_backup
- Run tasks that configure AutoPostgreSQLBackup scripts.