main.yml (10426B)
1 --- 2 - name: Find non-system databases on the destination 3 become: true 4 become_user: postgres 5 community.postgresql.postgresql_query: 6 login_db: postgres 7 query: >- 8 SELECT datname 9 FROM pg_database 10 WHERE NOT datistemplate AND datname <> 'postgres' 11 ORDER BY datname 12 register: database_restore_existing_databases 13 changed_when: false 14 15 - name: Find non-system roles on the destination 16 become: true 17 become_user: postgres 18 community.postgresql.postgresql_query: 19 login_db: postgres 20 query: >- 21 SELECT rolname 22 FROM pg_roles 23 WHERE rolname <> 'postgres' AND rolname !~ '^pg_' 24 ORDER BY rolname 25 register: database_restore_existing_roles 26 changed_when: false 27 28 - name: Require a fresh PostgreSQL cluster 29 ansible.builtin.assert: 30 that: 31 - database_restore_existing_databases.rowcount == 0 32 - database_restore_existing_roles.rowcount == 0 33 fail_msg: >- 34 Refusing to restore into a non-fresh cluster. Non-system databases: 35 {{ database_restore_existing_databases.query_result | map(attribute='datname') | list }}; 36 non-system roles: 37 {{ database_restore_existing_roles.query_result | map(attribute='rolname') | list }}. 38 Reinitialize the cluster before restoring. 39 quiet: true 40 41 - name: Inspect the controller staging directory 42 ansible.builtin.stat: 43 path: "{{ database_restore_controller_directory }}" 44 delegate_to: localhost 45 register: database_restore_controller_directory_stat 46 47 - name: Require a private controller staging directory 48 ansible.builtin.assert: 49 that: 50 - database_restore_controller_directory_stat.stat.exists 51 - database_restore_controller_directory_stat.stat.isdir 52 - database_restore_controller_directory_stat.stat.mode == '0700' 53 fail_msg: The controller restore staging directory is absent or not private. 54 quiet: true 55 56 - name: Set restore working paths 57 ansible.builtin.set_fact: 58 database_restore_controller_dump_path: >- 59 {{ database_restore_controller_directory }}/postgres-backup.sql.gz 60 database_restore_succeeded: false 61 62 - name: Extract, restore, and verify the database snapshot 63 block: 64 - name: Extract the selected database snapshot on the controller 65 ansible.builtin.shell: 66 cmd: >- 67 set -o pipefail && 68 borg extract --stdout 69 ::{{ database_restore_archive | quote }} 70 {{ database_restore_archive_member | quote }} 71 > {{ database_restore_controller_dump_path | quote }} 72 executable: /bin/bash 73 environment: 74 BORG_REPO: "{{ database_restore_borg_repository }}" 75 BORG_PASSPHRASE: >- 76 {{ lookup('ansible.builtin.env', 'BORG_PASSPHRASE') }} 77 delegate_to: localhost 78 register: database_restore_borg_extract 79 changed_when: false 80 no_log: true 81 82 - name: Validate the compressed database snapshot 83 ansible.builtin.command: 84 argv: 85 - gzip 86 - --test 87 - "{{ database_restore_controller_dump_path }}" 88 delegate_to: localhost 89 changed_when: false 90 91 - name: Read the source PostgreSQL version from the dump header 92 ansible.builtin.command: 93 argv: 94 - zgrep 95 - -m 96 - '1' 97 - -- 98 - ^-- Dumped from database version 99 - "{{ database_restore_controller_dump_path }}" 100 delegate_to: localhost 101 register: database_restore_dump_header 102 changed_when: false 103 failed_when: database_restore_dump_header.rc > 1 104 105 - name: Validate the dump header 106 ansible.builtin.assert: 107 that: 108 - database_restore_dump_header.rc == 0 109 - >- 110 database_restore_dump_header.stdout is 111 match('^-- Dumped from database version [0-9]+([.][0-9]+)*') 112 fail_msg: The snapshot does not contain a recognizable pg_dumpall header. 113 quiet: true 114 115 - name: Record the source PostgreSQL major version 116 ansible.builtin.set_fact: 117 database_restore_source_major: >- 118 {{ database_restore_dump_header.stdout | regex_search('[0-9]+') | int }} 119 120 - name: Read the destination PostgreSQL version 121 become: true 122 become_user: postgres 123 community.postgresql.postgresql_query: 124 login_db: postgres 125 query: SHOW server_version_num 126 register: database_restore_destination_version 127 changed_when: false 128 129 - name: Record the destination PostgreSQL major version 130 ansible.builtin.set_fact: 131 database_restore_destination_major: >- 132 {{ database_restore_destination_version.query_result[0].server_version_num | int // 10000 }} 133 134 - name: Reject a dump from a newer PostgreSQL major version 135 ansible.builtin.assert: 136 that: 137 - database_restore_source_major | int <= database_restore_destination_major | int 138 fail_msg: >- 139 PostgreSQL {{ database_restore_source_major }} dump cannot be safely 140 restored by PostgreSQL {{ database_restore_destination_major }}. 141 quiet: true 142 143 - name: Allocate the remote database snapshot file 144 ansible.builtin.tempfile: 145 state: file 146 path: /var/tmp 147 prefix: taler-postgres-restore- 148 suffix: .sql.gz 149 register: database_restore_remote_dump 150 151 - name: Upload the database snapshot 152 ansible.builtin.copy: 153 src: "{{ database_restore_controller_dump_path }}" 154 dest: "{{ database_restore_remote_dump.path }}" 155 owner: postgres 156 group: postgres 157 mode: '0400' 158 diff: false 159 no_log: true 160 161 - name: Allocate the remote restore error log 162 ansible.builtin.tempfile: 163 state: file 164 path: /var/tmp 165 prefix: taler-postgres-restore- 166 suffix: .log 167 register: database_restore_error_log 168 169 - name: Protect the remote restore error log 170 ansible.builtin.file: 171 path: "{{ database_restore_error_log.path }}" 172 owner: postgres 173 group: postgres 174 mode: '0600' 175 176 - name: Restore the PostgreSQL cluster snapshot 177 become: true 178 become_user: postgres 179 ansible.builtin.shell: 180 cmd: >- 181 set -o pipefail && 182 gzip --decompress --stdout {{ database_restore_remote_dump.path | quote }} | 183 psql -X --dbname postgres 184 > /dev/null 185 2> {{ database_restore_error_log.path | quote }} 186 executable: /bin/bash 187 environment: 188 LC_ALL: C 189 register: database_restore_psql 190 changed_when: true 191 failed_when: false 192 no_log: true 193 194 - name: Check the restore log for unexpected SQL errors 195 ansible.builtin.command: 196 argv: 197 - awk 198 - |- 199 /(ERROR|FATAL|PANIC):/ { 200 if ($0 ~ /ERROR:[[:space:]]+role "postgres" already exists[[:space:]]*$/) 201 next 202 unexpected = 1 203 } 204 END { exit unexpected } 205 - "{{ database_restore_error_log.path }}" 206 register: database_restore_error_check 207 changed_when: false 208 failed_when: false 209 no_log: true 210 211 - name: Require a clean restore result 212 ansible.builtin.assert: 213 that: 214 - database_restore_psql.rc == 0 215 - database_restore_error_check.rc == 0 216 fail_msg: >- 217 Database restore failed or emitted an unexpected SQL error. The 218 private log is preserved on the target at 219 {{ database_restore_error_log.path }}. The partially restored cluster 220 must be reinitialized before retrying. 221 quiet: true 222 223 - name: Find restored databases 224 become: true 225 become_user: postgres 226 community.postgresql.postgresql_query: 227 login_db: postgres 228 query: >- 229 SELECT datname 230 FROM pg_database 231 WHERE NOT datistemplate 232 ORDER BY datname 233 register: database_restore_restored_databases 234 changed_when: false 235 236 - name: Require the exchange database 237 ansible.builtin.assert: 238 that: 239 - >- 240 'taler-exchange' in 241 (database_restore_restored_databases.query_result | map(attribute='datname') | list) 242 fail_msg: The restored snapshot does not contain the taler-exchange database. 243 quiet: true 244 245 - name: Find restored exchange schemas 246 become: true 247 become_user: postgres 248 community.postgresql.postgresql_query: 249 login_db: taler-exchange 250 query: >- 251 SELECT schema_name 252 FROM information_schema.schemata 253 WHERE schema_name IN ('exchange', '_v') 254 ORDER BY schema_name 255 register: database_restore_exchange_schemas 256 changed_when: false 257 258 - name: Require the exchange schemas 259 ansible.builtin.assert: 260 that: 261 - >- 262 'exchange' in 263 (database_restore_exchange_schemas.query_result | map(attribute='schema_name') | list) 264 - >- 265 '_v' in 266 (database_restore_exchange_schemas.query_result | map(attribute='schema_name') | list) 267 fail_msg: The restored exchange database is missing required schemas. 268 quiet: true 269 270 - name: Verify connections to all restored databases 271 become: true 272 become_user: postgres 273 community.postgresql.postgresql_query: 274 login_db: "{{ item.datname }}" 275 query: SELECT 1 276 loop: "{{ database_restore_restored_databases.query_result }}" 277 loop_control: 278 label: "{{ item.datname }}" 279 changed_when: false 280 281 - name: Mark the restore as successful 282 ansible.builtin.set_fact: 283 database_restore_succeeded: true 284 285 always: 286 - name: Remove the remote database snapshot 287 ansible.builtin.file: 288 path: "{{ database_restore_remote_dump.path }}" 289 state: absent 290 when: 291 - database_restore_remote_dump is defined 292 - database_restore_remote_dump.path is defined 293 no_log: true 294 295 - name: Remove the controller database snapshot 296 ansible.builtin.file: 297 path: "{{ database_restore_controller_dump_path }}" 298 state: absent 299 delegate_to: localhost 300 301 - name: Remove the successful restore log 302 ansible.builtin.file: 303 path: "{{ database_restore_error_log.path }}" 304 state: absent 305 when: 306 - database_restore_succeeded | bool 307 - database_restore_error_log is defined 308 - database_restore_error_log.path is defined 309 no_log: true