Db-support » History » Version 4
Philippe May, 24/10/2018 11:01
| 1 | 1 | Philippe May | h1. Database |
|---|---|---|---|
| 2 | 1 | Philippe May | |
| 3 | 3 | Philippe May | This documentation assumes that the Postgis package has been installed (see [[CSR_server#Database]]). |
| 4 | 3 | Philippe May | |
| 5 | 3 | Philippe May | h2. Creation of the database |
| 6 | 3 | Philippe May | |
| 7 | 4 | Philippe May | As @postgres@ user: |
| 8 | 3 | Philippe May | |
| 9 | 4 | Philippe May | createdb -E utf8 -T template0 avgis |
| 10 | 3 | Philippe May | |
| 11 | 1 | Philippe May | h2. Backups |
| 12 | 1 | Philippe May | |
| 13 | 1 | Philippe May | h3. Primary |
| 14 | 1 | Philippe May | |
| 15 | 1 | Philippe May | The database is backed up every day at midnight. The dump file is located in @/var/backups/postgres/@. |
| 16 | 1 | Philippe May | |
| 17 | 1 | Philippe May | h3. Secondary |
| 18 | 1 | Philippe May | |
| 19 | 1 | Philippe May | The whole virtual machine is backed up by BackupNinja on the "dom0" controller, using: |
| 20 | 1 | Philippe May | - rdiff backups every day |
| 21 | 1 | Philippe May | - tar files on Saturdays. |
| 22 | 1 | Philippe May | |
| 23 | 1 | Philippe May | See @/etc/backups.d@ on the dom0 (192.168.0.12). |
| 24 | 1 | Philippe May | |
| 25 | 1 | Philippe May | h3. Tertiary |
| 26 | 1 | Philippe May | |
| 27 | 1 | Philippe May | TODO: remote backup. |
| 28 | 1 | Philippe May | |
| 29 | 1 | Philippe May | |
| 30 | 1 | Philippe May | h2. Restoration |
| 31 | 1 | Philippe May | |
| 32 | 1 | Philippe May | If the VM is not shutdown properly, there's a chance that the database is corrupt, and needs to be restored from one of the backups. |
| 33 | 1 | Philippe May | |
| 34 | 1 | Philippe May | After the restoration, restart gisaf: |
| 35 | 1 | Philippe May | <pre> |
| 36 | 1 | Philippe May | systemctl restart uwsgi.service |
| 37 | 1 | Philippe May | </pre> |
| 38 | 1 | Philippe May | |
| 39 | 1 | Philippe May | h3. From primary backup |
| 40 | 1 | Philippe May | |
| 41 | 1 | Philippe May | With user @postgres@: |
| 42 | 1 | Philippe May | <pre> |
| 43 | 2 | Philippe May | # Optionally, rename the corrupt database (selecting a name for a database like "avgis_c2")... |
| 44 | 1 | Philippe May | psql -c "ALTER DATABASE avgis RENAME TO avgis_c2;" |
| 45 | 2 | Philippe May | # ... or drop the existing database |
| 46 | 1 | Philippe May | psql -c "drop database avgis;" |
| 47 | 1 | Philippe May | # Create a new database: |
| 48 | 1 | Philippe May | createdb -E utf8 -T template0 avgis |
| 49 | 1 | Philippe May | # Restore the database |
| 50 | 1 | Philippe May | pg_restore -d avgis /var/backups/postgres/avgis.pg_dump |
| 51 | 1 | Philippe May | </pre> |