Support #6954

Create a VM for DB

Added by Philippe May over 5 years ago. Updated over 5 years ago.

Start date:
Due date:
% Done:




We have identified that the gis.csr.av domU has issues with the file system. It switches to read only at times (heavy load).

In order to mitigate the risks for the database (which is now used for, eg, polling weather station data), the database should be hosted in its own virtual machine (domU).


#1 Updated by Philippe May over 5 years ago

Follow the instruction on , with:

export vm_name=gisdb.csr.av
root@dream:~# export vm_ip_addr=
root@dream:~# export vm_password=foobar
root@dream:~# xen-create-image --broadcast= --dist=stretch --fs=ext4 --gateway= --hostname ${vm_name} --ip=${vm_ip_addr} --lvm=dream.csr --maxmem=512M --memory=256M --mirror= --netmask= --password=${vm_password} --size=10G --swap=1G --vcpus=1 --bridge=br0

IP address (in /etc/systemd/network/

Next: Ansible.

#2 Updated by Philippe May over 5 years ago

Ansible done (role postgis).

Set up the postgres server: follow Db-support

#3 Updated by Philippe May over 5 years ago

Migrate the database

Copy the backup files:

rsync -a gis.csr.av:/var/backups/postgres /var/backup
chgrp -R postgres /var/backups/postgres
chmod -R g+r /var/backups/postgres

## Backups from autopostgresqlbackup
rsync -a gis.csr.av:/var/lib/autopostgresqlbackup/ /var/lib/autopostgresqlbackup
chgrp -R postgres /var/lib/autopostgresqlbackup
chmod -R g+r /var/lib/autopostgresqlbackup

Restore the database from secondary backups:

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

#4 Updated by Philippe May over 5 years ago

Change the config for gisaf (db connection) on gis.csr.av. Restart:

- gisaf (systemctl restart uwsgi.service)
- notebooks (systemctl restart systemctl restart jupyter-gisaf.service, systemctl restart systemctl restart jupyter-gisaf-admin.service)

All OK.

#5 Updated by Philippe May over 5 years ago

Backups (dom0)

Setup backupninja on dom0 to backup the new VM (see /etc/backup.d/61.gisdb.tar)

Create mount point

mkdir /mnt/lvs/dream.csr-gisdb.csr.av


mount /dev/mapper/dream.csr-gisdb.csr.av--disk /mnt/lvs/dream.csr-gisdb.csr.av

Run ninjahelper: select 61.gisdb.tar => "Run now".

Finally (tomorrow): check that the daily rdiff backup is OK.

#6 Updated by Philippe May over 5 years ago

  • Status changed from New to Resolved
  • % Done changed from 0 to 100

Backup to dom0 OK:

root@dream:~# ll /var/backups/rdiff/mnt/lvs/dream.csr-gisdb.csr.av/var/lib/autopostgresqlbackup/daily/avgis/
total 126964
drwxr-xr-x 2 root libvirt     4096 Nov  9 11:55 ./
drwxr-xr-x 6 root libvirt     4096 Oct 31 20:10 ../
-rw------- 1 root root    21619342 Nov  4 11:55 avgis_2018-11-04_11h55m.Sunday.sql.gz
-rw------- 1 root root    21629016 Nov  5 11:55 avgis_2018-11-05_11h55m.Monday.sql.gz
-rw------- 1 root root    21642026 Nov  6 11:55 avgis_2018-11-06_11h55m.Tuesday.sql.gz
-rw------- 1 root root    21644811 Nov  7 11:55 avgis_2018-11-07_11h55m.Wednesday.sql.gz
-rw------- 1 root root    21791729 Nov  8 11:55 avgis_2018-11-08_11h55m.Thursday.sql.gz
-rw------- 1 root root    21663268 Nov  9 11:55 avgis_2018-11-09_11h55m.Friday.sql.gz

Also available in: Atom PDF