Database¶

This documentation assumes that the Postgis package has been installed (see CSR_server).

Configure the server¶

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:

host all all 192.168.0.0/24 md5

Creation of the database¶

As postgres user:

createdb -E utf8 -T template0 avgis

Backups¶

Primary¶

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

Secondary¶

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

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

Remote¶

TODO: remote backup.

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:

systemctl restart uwsgi.service

From primary backup¶

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

With user postgres:

# 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

From secondary backup¶

autopostgresqlbackup backs up the roles in postgres_globals.

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