ansible/clean-synapse-db.yml

66 lines
2.6 KiB
YAML
Raw Permalink Normal View History

- name: "Clean up the Synapse DB"
hosts: unhb4
remote_user: root
tasks:
- name: "Install jq"
ansible.builtin.apt:
pkg:
- jq
- python3-psycopg2
- name: "Get IP of DB container for the database synapse uses"
ansible.builtin.shell: |
docker inspect {{ synapse_db_clean_container_db }} | jq -r '.[].NetworkSettings.Networks[].IPAddress'
register: docker_network_inspect_synapse_db
- ansible.builtin.set_fact:
postgres_ip_synapse_db: "{{ docker_network_inspect_synapse_db.stdout }}"
- name: "Ensure Synapse is disabled before accessing the database"
ansible.builtin.shell:
cmd: "docker compose stop {{ synapse_db_clean_container_synapse }}"
chdir: "{{ synapse_db_clean_compose_file_dir }}"
tags: molecule-notest
- name: "Delete orphan state_groups_state"
postgresql_query:
db: "{{ synapse_db_clean_db }}"
login_host: "{{ postgres_ip_synapse_db }}"
login_user: "{{ synapse_db_clean_user }}"
login_password: "{{ synapse_db_clean_password }}"
query: DELETE FROM state_groups_state WHERE room_id IN (SELECT DISTINCT(state_groups.room_id) AS room_id_gone FROM state_groups LEFT JOIN events USING(room_id) WHERE events.room_id IS NULL GROUP BY room_id_gone)
- name: "Delete orphan state_groups_state"
postgresql_query:
db: "{{ synapse_db_clean_db }}"
login_host: "{{ postgres_ip_synapse_db }}"
login_user: "{{ synapse_db_clean_user }}"
login_password: "{{ synapse_db_clean_password }}"
query: DELETE FROM state_groups WHERE room_id IN (SELECT DISTINCT(state_groups.room_id) AS room_id_gone FROM state_groups LEFT JOIN events USING(room_id) WHERE events.room_id IS NULL GROUP BY room_id_gone)
- name: "Reindex DB"
postgresql_query:
db: "{{ synapse_db_clean_db }}"
login_host: "{{ postgres_ip_synapse_db }}"
login_user: "{{ synapse_db_clean_user }}"
login_password: "{{ synapse_db_clean_password }}"
autocommit: yes
query: "REINDEX (VERBOSE) DATABASE {{ synapse_db_clean_db }}"
- name: "Vacuum DB"
postgresql_query:
db: "{{ synapse_db_clean_db }}"
login_host: "{{ postgres_ip_synapse_db }}"
login_user: "{{ synapse_db_clean_user }}"
login_password: "{{ synapse_db_clean_password }}"
autocommit: yes
query: VACUUM FULL VERBOSE
- name: "Ensure Synapse is re-enabled"
ansible.builtin.shell:
cmd: "docker compose up -d {{ synapse_db_clean_container_synapse }}"
chdir: "{{ synapse_db_clean_compose_file_dir }}"
tags: molecule-notest