GIS

h1. %{color:BLUE} Database - Backup and Restoration%

h1. %{color:BLUE} Database%

h2. %{color:BLUE} Troubleshooting%

h3. %{color:BLUE} Layers missing in the map's tree%

Gisaf relies on counting features through Postgres statistics collector subsystem.

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.

The fix is as easy as:

<pre>
sudo -u postgres psql avgis -c VACUUM
</pre>

h2. %{color:BLUE} Installation%

This documentation assumes that the Postgis package has been installed (see [[CSR Geomatics Server Setup#Database]]). [[CSR_server#Database]]).

h1. %{color:BLUE} Configure the server%

h4. %{color:BLUE} Allow connections from other hosts in the local network%

Set the server to listen to addresses, set listen_addresses to @*@ in @/etc/postgresql/9.6/main/postgresql.conf@.

Allow the connections, add in @/etc/postgresql/9.6/main/pg_hba.conf@:

<pre>
host all all 192.168.0.0/24 md5
</pre>

h2. %{color:BLUE} Creation of the database%

As @postgres@ user:

<pre>
createdb -E utf8 -T template0 avgis
</pre>

h2. %{color:BLUE} Backups%

h3. %{color:BLUE} Primary%

The database is backed up every day at midnight. The dump file is located in @/var/backups/postgres/@.

h3.%{color:BLUE} Secondary%

There are other backups (daily, weekly, monthly) thanks to Debian package @autopostgresqlbackup@), located (default) in @/var/lib/autopostgresqlbackup@.

h3. %{color:BLUE} Tertiary (dom0)%

The whole virtual machine is backed up by BackupNinja on the "dom0" controller, using:
- rdiff backups every day
- tar files on Saturdays.

See @/etc/backups.d@ on the dom0 (192.168.0.12).

h1. %{color:BLUE} Remote%

*%{color:RED} TODO: remote backup to be written%*

h1. %{color:BLUE} Restoration%

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.

h2. %{color:BLUE} After the restoration, restart gisaf:%
<pre>
systemctl restart uwsgi.service
</pre>

h1. %{color:BLUE} From primary backup%

*Note*: the roles aren't restored with this method.

With user @postgres@:
<pre>
# Optionally, rename the corrupt database (selecting a name for a database like "avgis_c2")...
psql -c "ALTER DATABASE avgis RENAME TO avgis_c2;"
# ... or drop the existing database
psql -c "drop database avgis;"
# Create a new database:
createdb -E utf8 -T template0 avgis
# Restore the database
pg_restore -d avgis /var/backups/postgres/avgis.pg_dump
</pre>

h3. %{color:BLUE} From secondary backup%

@autopostgresqlbackup@ backs up the roles in @postgres_globals@.

<pre>
zcat /var/lib/autopostgresqlbackup/daily/postgres_globals/postgres_globals_2018-10-24_06h25m.Wednesday.sql.gz | psql
zcat /var/lib/autopostgresqlbackup/daily/avgis/avgis_2018-10-24_06h25m.Wednesday.sql.gz | psql
</pre>