Project

General

Profile

Db-support » History » Version 10

Philippe May, 16/09/2019 23:18

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