Project

General

Profile

Db-support » History » Version 5

Philippe May, 24/10/2018 11:08

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 5 Philippe May
h2. Configure the server
6 5 Philippe May
7 5 Philippe May
h3. Allow connections from other hosts in the local network
8 5 Philippe May
9 5 Philippe May
Add in @/etc/postgresql/9.6/main/pg_hba.conf@:
10 5 Philippe May
11 5 Philippe May
<pre>
12 5 Philippe May
host all all 192.168.0.0/24 md5
13 5 Philippe May
</pre>
14 5 Philippe May
15 3 Philippe May
h2. Creation of the database
16 4 Philippe May
17 3 Philippe May
As @postgres@ user:
18 4 Philippe May
19 5 Philippe May
<pre>
20 3 Philippe May
createdb -E utf8 -T template0 avgis
21 5 Philippe May
</pre>
22 1 Philippe May
23 1 Philippe May
h2. Backups
24 1 Philippe May
25 1 Philippe May
h3. Primary
26 1 Philippe May
27 5 Philippe May
The database is backed up every day at midnight (Thanks to Debian package @autopostgresqlbackup@). The dump file is located in @/var/backups/postgres/@.
28 1 Philippe May
29 1 Philippe May
h3. Secondary
30 1 Philippe May
31 1 Philippe May
The whole virtual machine is backed up by BackupNinja on the "dom0" controller, using:
32 1 Philippe May
- rdiff backups every day
33 1 Philippe May
- tar files on Saturdays.
34 1 Philippe May
35 1 Philippe May
See @/etc/backups.d@ on the dom0 (192.168.0.12).
36 1 Philippe May
37 1 Philippe May
h3. Tertiary
38 1 Philippe May
39 1 Philippe May
TODO: remote backup.
40 1 Philippe May
41 1 Philippe May
42 1 Philippe May
h2. Restoration
43 1 Philippe May
44 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.
45 1 Philippe May
46 1 Philippe May
After the restoration, restart gisaf:
47 1 Philippe May
<pre>
48 1 Philippe May
systemctl restart uwsgi.service
49 1 Philippe May
</pre>
50 1 Philippe May
51 1 Philippe May
h3. From primary backup
52 1 Philippe May
53 1 Philippe May
With user @postgres@:
54 1 Philippe May
<pre>
55 2 Philippe May
# Optionally, rename the corrupt database (selecting a name for a database like "avgis_c2")...
56 1 Philippe May
psql -c "ALTER DATABASE avgis RENAME TO avgis_c2;"
57 2 Philippe May
# ... or drop the existing database
58 1 Philippe May
psql -c "drop database avgis;"
59 1 Philippe May
# Create a new database:
60 1 Philippe May
createdb -E utf8 -T template0 avgis
61 1 Philippe May
# Restore the database
62 1 Philippe May
pg_restore -d avgis /var/backups/postgres/avgis.pg_dump
63 1 Philippe May
</pre>