Project

General

Profile

Db-support » History » Version 12

Philippe May, 16/09/2019 23:20

1 1 Philippe May
h1. Database
2 1 Philippe May
3 11 Philippe May
h2. Troubleshooting
4 10 Philippe May
5 12 Philippe May
h3. Layers missing in the map's tree
6 12 Philippe May
7 10 Philippe May
Gisaf relies on counting features through Postgres statistics collector subsystem.
8 10 Philippe May
9 10 Philippe May
In case the server is restarted *dirty* (eg. without clean shutdown), then the count of the tables might be wrong or just 0, leaving the layers apparently empty and thus not even appearing.
10 10 Philippe May
11 10 Philippe May
The fix is as easy as:
12 10 Philippe May
13 10 Philippe May
<pre>
14 10 Philippe May
sudo -u postgres psql avgis -c VACUUM
15 10 Philippe May
</pre>
16 10 Philippe May
17 10 Philippe May
h2. Installation
18 10 Philippe May
19 5 Philippe May
This documentation assumes that the Postgis package has been installed (see [[CSR_server#Database]]).
20 1 Philippe May
21 10 Philippe May
h3. Configure the server
22 5 Philippe May
23 10 Philippe May
h4. Allow connections from other hosts in the local network
24 5 Philippe May
25 9 Philippe May
Set the server to listen to addresses, set listen_addresses to @*@ in @/etc/postgresql/9.6/main/postgresql.conf@.
26 9 Philippe May
27 9 Philippe May
28 9 Philippe May
Allow the connections, add in @/etc/postgresql/9.6/main/pg_hba.conf@:
29 5 Philippe May
30 5 Philippe May
<pre>
31 1 Philippe May
host all all 192.168.0.0/24 md5
32 5 Philippe May
</pre>
33 10 Philippe May
34 5 Philippe May
35 3 Philippe May
h2. Creation of the database
36 4 Philippe May
37 3 Philippe May
As @postgres@ user:
38 4 Philippe May
39 5 Philippe May
<pre>
40 3 Philippe May
createdb -E utf8 -T template0 avgis
41 5 Philippe May
</pre>
42 1 Philippe May
43 1 Philippe May
h2. Backups
44 1 Philippe May
45 1 Philippe May
h3. Primary
46 1 Philippe May
47 7 Philippe May
The database is backed up every day at midnight. The dump file is located in @/var/backups/postgres/@.
48 7 Philippe May
49 8 Philippe May
h3. Secondary
50 8 Philippe May
51 1 Philippe May
There are other backups (daily, weekly, monthly) thanks to Debian package @autopostgresqlbackup@), located (default) in @/var/lib/autopostgresqlbackup@.
52 1 Philippe May
53 8 Philippe May
h3. Tertiary (dom0)
54 1 Philippe May
55 1 Philippe May
The whole virtual machine is backed up by BackupNinja on the "dom0" controller, using:
56 1 Philippe May
- rdiff backups every day
57 1 Philippe May
- tar files on Saturdays.
58 1 Philippe May
59 1 Philippe May
See @/etc/backups.d@ on the dom0 (192.168.0.12).
60 1 Philippe May
61 8 Philippe May
h3. Remote
62 1 Philippe May
63 1 Philippe May
TODO: remote backup.
64 1 Philippe May
65 1 Philippe May
66 1 Philippe May
h2. Restoration
67 1 Philippe May
68 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.
69 1 Philippe May
70 1 Philippe May
After the restoration, restart gisaf:
71 1 Philippe May
<pre>
72 1 Philippe May
systemctl restart uwsgi.service
73 1 Philippe May
</pre>
74 1 Philippe May
75 1 Philippe May
h3. From primary backup
76 1 Philippe May
77 8 Philippe May
*Note*: the roles aren't restored with this method.
78 8 Philippe May
79 1 Philippe May
With user @postgres@:
80 1 Philippe May
<pre>
81 2 Philippe May
# Optionally, rename the corrupt database (selecting a name for a database like "avgis_c2")...
82 1 Philippe May
psql -c "ALTER DATABASE avgis RENAME TO avgis_c2;"
83 2 Philippe May
# ... or drop the existing database
84 1 Philippe May
psql -c "drop database avgis;"
85 1 Philippe May
# Create a new database:
86 1 Philippe May
createdb -E utf8 -T template0 avgis
87 1 Philippe May
# Restore the database
88 1 Philippe May
pg_restore -d avgis /var/backups/postgres/avgis.pg_dump
89 8 Philippe May
</pre>
90 8 Philippe May
91 8 Philippe May
h3. From secondary backup
92 8 Philippe May
93 8 Philippe May
@autopostgresqlbackup@ backs up the roles in @postgres_globals@.
94 8 Philippe May
95 8 Philippe May
<pre>
96 8 Philippe May
zcat /var/lib/autopostgresqlbackup/daily/postgres_globals/postgres_globals_2018-10-24_06h25m.Wednesday.sql.gz | psql
97 8 Philippe May
zcat /var/lib/autopostgresqlbackup/daily/avgis/avgis_2018-10-24_06h25m.Wednesday.sql.gz | psql
98 1 Philippe May
</pre>