Support #6954
Create a VM for DB
100%
Description
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).
History
#1 Updated by Philippe May about 6 years ago
Follow the instruction on https://redmine.auroville.org.in/projects/gis/wiki/CSR_server , with:
export vm_name=gisdb.csr.av
root@dream:~# export vm_ip_addr=172.16.0.7
root@dream:~# export vm_password=foobar
root@dream:~# xen-create-image --broadcast=172.16.0.255 --dist=stretch --fs=ext4 --gateway=172.16.0.1 --hostname ${vm_name} --ip=${vm_ip_addr} --lvm=dream.csr --maxmem=512M --memory=256M --mirror=http://ftp.de.debian.org/debian/ --netmask=255.255.255.0 --password=${vm_password} --size=10G --swap=1G --vcpus=1 --bridge=br0
IP address (in /etc/systemd/network/50-wired.network): 192.168.0.18
Next: Ansible.
#2 Updated by Philippe May about 6 years ago
Ansible done (role postgis).
Set up the postgres server: follow Db-support
#3 Updated by Philippe May about 6 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 about 6 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 about 6 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
Test:
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 about 6 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