GIS

h1. Database

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

h2. Configure the server

h3. 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. Creation of the database

As @postgres@ user:

<pre>

createdb -E utf8 -T template0 avgis

</pre>

h2. Backups

h3. Primary

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

h3. Secondary

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

h3. 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).

h3. Remote

TODO: remote backup.

h2. 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.

After the restoration, restart gisaf:

<pre>

systemctl restart uwsgi.service

</pre>

h3. 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. 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>