Using dalibo.pglift Ansible collection¶
This tutorial illustrates the use of the dalibo.pglift collection
that leverages pglift with Ansible. The collection ships the following
modules: dalibo.pglift.instance, dalibo.pglift.database,
dalibo.pglift.role, dalibo.pglift.postgres_exporter and
dalibo.pglift.dsn_info. This tutorial also demonstrates how to integrate
these modules with other PostgreSQL-related community modules, namely
community.postgresql.
Note
Ansible modules require Python 3 so, depending on the Ansible version being
used, one may need to configure managed machines to use Python 3 through
the ansible_python_interpreter inventory variable or -e
command-line option.
Setup¶
In the following we consider two nodes (or machines): the control node,
where Ansible commands or playbooks will be executed and, the managed node
where operations should apply.
On the control node, the collection should be installed:
user@control:~$ ansible-galaxy collection install dalibo.pglift
Documentation for each module can be obtained by using ansible-doc
<modulename>, e.g.:
user@control:~$ ansible-doc dalibo.pglift.instance
> DALIBO.PGLIFT.INSTANCE
(.../ansible/ansible_collections/dalibo/pglift/plugins/modules/instance.py)
Manage a PostgreSQL server instance
OPTIONS (= is mandatory):
[...]
On the managed node, pglift needs to be installed and
configured. Let’s detail a bit how to configure a pglift
site with Ansible. We’ll use the following playbook:
- name: pglift site configuration for the Ansible tutorial
hosts: localhost
tasks:
- name: The prefix path for all pglift data
ansible.builtin.tempfile:
state: directory
suffix: .pglift
register: pglift_prefix
- name: Install pglift site settings
ansible.builtin.template:
src: tutorial-settings.yaml.j2
dest: ~/.config/pglift/settings.yaml
- name: Perform site configuration
ansible.builtin.command: pglift site-configure install
which:
creates a writable (temporary) directory to host PostgreSQL instances,
installs pglift site settings based on the following template:
tutorial-settings.yml¶prefix: "{{ pglift_prefix.path }}" run_prefix: "{{ pglift_prefix.path }}/run" postgresql: auth: local: md5 host: md5 passfile: "{{ pglift_prefix.path }}/.pgpass" surole: pgpass: true backuprole: pgpass: true replrole: replication pgbackrest: repository: mode: path path: "{{ pglift_prefix.path }}/backups" prometheus: execpath: /usr/bin/prometheus-postgres-exporter
and finally perform site configuration.
Our site settings make use of the temporary directory created in the first task. The site is configured to use pgbackrest for physical backups (stored locally) and Prometheus postgres_exporter for monitoring. Finally note that we are using a PostgreSQL passfile in order to store passwords for managed roles.
Executing this playbook will complete the setup of the managed node:
user@control:~$ ansible-playbook site-configure.yml
Note
The hosts field in this playbook uses localhost for testing purpose
and should be adapted to actual managed node.
Back on the control node, we will define passwords for the postgres user
and other roles used in the following playbooks; these will be stored and
encrypted with Ansible vault:
user@control:~$ cat << EOF | ansible-vault encrypt > pglift-vars
postgresql_surole_password: $(openssl rand -base64 9)
prod_bob_password: $(openssl rand -base64 9)
backup_role_password: $(openssl rand -base64 9)
prometheus_role_password: $(openssl rand -base64 9)
EOF
To view actual passwords:
user@control:~$ ansible-vault view pglift-vars
Initial deployment¶
The following playbook installs and configures 3 PostgreSQL instances on the
managed node; the first two instances are started while the third one is
not:
---
- name: my postgresql instances
hosts: localhost
tasks:
- name: production instance
dalibo.pglift.instance:
name: prod
state: started
port: 5433
settings:
max_connections: 100
shared_buffers: 1GB
unix_socket_directories: /tmp
shared_preload_libraries: "pg_stat_statements, passwordcheck"
surole_password: "{{ postgresql_surole_password }}"
pgbackrest:
stanza: stanza_prod
password: "{{ backup_role_password }}"
prometheus:
password: "{{ prometheus_role_password }}"
port: 9186
roles:
- name: bob
login: true
password: "{{ prod_bob_password }}"
connection_limit: 10
validity: '2025-01-01T00:00'
in_roles:
- pg_read_all_stats
- pg_signal_backend
- name: peter
in_roles:
- pg_signal_backend
databases:
- name: db
owner: bob
settings:
work_mem: 3MB
extensions:
- name: unaccent
schema: public
- name: db2
owner: bob
settings:
work_mem: 3MB
extensions:
- name: unaccent
schema: public
- name: db3
owner: peter
extensions:
- name: unaccent
schema: public
- name: pre-production instance
dalibo.pglift.instance:
name: preprod
port: 5434
state: started
settings:
unix_socket_directories: /tmp
surole_password: "{{ postgresql_surole_password }}"
pgbackrest:
stanza: stanza_preprod
password: "{{ backup_role_password }}"
prometheus:
password: "{{ prometheus_role_password }}"
port: 9188
- name: register preprod conninfo
dalibo.pglift.dsn_info:
name: preprod
register: preprod
- name: |
create database test on pre-production instance using
community.postgresql module
community.postgresql.postgresql_db:
name: test
login_host: "{{ preprod.PGHOST }}"
login_port: "{{ preprod.PGPORT }}"
environment: "{{ preprod }}"
- name: dev instance, not running at the moment
dalibo.pglift.instance:
name: dev
port: 5444
state: stopped
settings:
unix_socket_directories: /tmp
surole_password: "{{ postgresql_surole_password }}"
pgbackrest:
stanza: stanza_dev
password: "{{ backup_role_password }}"
prometheus:
password: "{{ prometheus_role_password }}"
port: 9189
Finally, run:
user@control:~$ ansible-playbook --extra-vars @pglift-vars --ask-vault-password instances-create.yml
PLAY [my postgresql instances] ***************************************************************************
TASK [Gathering Facts] ***********************************************************************************
ok: [localhost]
TASK [production instance] *******************************************************************************
changed: [localhost]
TASK [pre-production instance] ***************************************************************************
changed: [localhost]
TASK [dev instance, not running at the moment] ***********************************************************
changed: [localhost]
PLAY RECAP ***********************************************************************************************
localhost : ok=4 changed=3 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0
We can see our instances installed and running on the managed node:
user@managed:~$ tree -L 3 $tmpdir/srv/pgsql
/tmp/.../srv/pgsql
└── 16
├── dev
│ ├── data
│ └── wal
├── preprod
│ ├── data
│ └── wal
└── prod
├── data
└── wal
user@managed:~$ ps xf
[...]
19777 ? Ss 0:00 \_ /usr/lib/postgresql/16/bin/postgres -D .../srv/pgsql/16/pr
19780 ? Ss 0:00 | \_ postgres: prod: logger
19781 ? Ss 0:00 | \_ postgres: prod: checkpointer
19782 ? Ss 0:00 | \_ postgres: prod: background writer
19784 ? Ss 0:00 | \_ postgres: prod: walwriter
19785 ? Ss 0:00 | \_ postgres: prod: autovacuum launcher
19786 ? Ss 0:00 | \_ postgres: prod: archiver last was 000000010000000000000001
19787 ? Ss 0:00 | \_ postgres: prod: logical replication launcher
19843 ? Ssl 0:00 \_ /usr/bin/prometheus-postgres-exporter --log.level=info
20180 ? Ss 0:00 \_ /usr/lib/postgresql/16/bin/postgres -D .../srv/pgsql/16/pr
20183 ? Ss 0:00 | \_ postgres: preprod: logger
20184 ? Ss 0:00 | \_ postgres: preprod: checkpointer
20185 ? Ss 0:00 | \_ postgres: preprod: background writer
20187 ? Ss 0:00 | \_ postgres: preprod: walwriter
20188 ? Ss 0:00 | \_ postgres: preprod: autovacuum launcher
20189 ? Ss 0:00 | \_ postgres: preprod: archiver last was 000000010000000000000001
20190 ? Ss 0:00 | \_ postgres: preprod: logical replication launcher
20246 ? Ssl 0:00 \_ /usr/bin/prometheus-postgres-exporter --log.level=info
pgBackRest is set up and initialized for started instances:
user@managed:~$ tree -L 2 $tmpdir/backups/backup
/tmp/.../backups/backup
├── stanza_dev
│ ├── backup.info
│ └── backup.info.copy
└── stanza_prod
├── backup.info
└── backup.info.copy
And a systemd timer has been added for our instances:
user@managed:~$ systemctl --user list-timers
NEXT LEFT LAST PASSED UNIT ACTIVATES
Sat 2023-09-30 00:00:00 CEST 7h left n/a n/a postgresql-backup@16-preprod.timer postgresql-backup@13-preprod.service
Sat 2023-00-30 00:00:00 CEST 7h left n/a n/a postgresql-backup@16-prod.timer postgresql-backup@13-prod.service
2 timers listed.
Instances update¶
In the following version of our previous playbook, we are dropping the “preprod”
instance and set the “dev” one to be started while changing its
configuration a bit.
We also remove the pg_stat_statements from the shared_preload_libraries in the prod instance. For this to be taken into account, the instance needs to be restarted, hence the addition of restart_on_changes:
---
- name: my postgresql instances
hosts: localhost
tasks:
- name: production instance
dalibo.pglift.instance:
name: prod
port: 5433
state: started
restart_on_changes: true
settings:
max_connections: 100
shared_buffers: 1GB
unix_socket_directories: /tmp
shared_preload_libraries: "passwordcheck"
surole_password: "{{ postgresql_surole_password }}"
pgbackrest:
stanza: stanza_prod
password: "{{ backup_role_password }}"
prometheus:
password: "{{ prometheus_role_password }}"
port: 9186
register: prod
- name: db, dropped
dalibo.pglift.database:
name: db
state: absent
instance: prod
- name: role bob, dropped
dalibo.pglift.role:
name: bob
state: absent
instance: prod
drop_owned: true
- name: role peter, dropped
dalibo.pglift.role:
name: peter
state: absent
instance: prod
reassign_owned: postgres
- name: pre-production instance, now dropped
dalibo.pglift.instance:
name: preprod
state: absent
pgbackrest:
stanza: stanza_preprod
- name: dev instance, re-configured and started
dalibo.pglift.instance:
name: dev
port: 5455
state: started
settings:
max_connections: 42
unix_socket_directories: /tmp
surole_password: "{{ postgresql_surole_password }}"
pgbackrest:
stanza: stanza_dev
password: "{{ backup_role_password }}"
prometheus:
password: "{{ prometheus_role_password }}"
port: 9189
As you can see you can feed third-party ansible modules (like
community.postgresql) with libpq environment variables obtained by
dalibo.pglift.instance or dalibo.pglift.dsn_info.
user@control:~$ ansible-playbook --extra-vars @pglift-vars --ask-vault-password instances-update.yml
PLAY [my postgresql instances] ***************************************************************************
...
PLAY RECAP ***********************************************************************************************
localhost : ok=7 changed=6 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0
user@managed:~$ tree -L 2 $tmpdir/srv/pgsql
/tmp/.../srv/pgsql
└── 16
├── dev
├── preprod
└── prod
Cleanup¶
Finally, in this last playbook, we drop all our instances:
---
- name: my postgresql instances
hosts: localhost
tasks:
- name: production instance, dropped
dalibo.pglift.instance:
name: prod
state: absent
pgbackrest:
stanza: stanza_prod
- name: preproduction instance, dropped
dalibo.pglift.instance:
name: preprod
state: absent
pgbackrest:
stanza: stanza_preprod
- name: dev instance, dropped
dalibo.pglift.instance:
name: dev
state: absent
pgbackrest:
stanza: stanza_dev
user@control:~$ ansible-playbook --extra-vars @pglift-vars --ask-vault-password instances-delete.yml
PLAY [my postgresql instances] ***************************************************************************
...
PLAY RECAP ***********************************************************************************************
localhost : ok=4 changed=2 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0