66 lines
2.6 KiB
YAML
66 lines
2.6 KiB
YAML
|
|
- 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
|