Project

General

Profile

Support #6954

Create a VM for DB

Added by Philippe May about 3 years ago. Updated about 3 years ago.

Status:
Resolved
Priority:
Normal
Assignee:
Start date:
23/10/2018
Due date:
% Done:

100%

Close

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 3 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 3 years ago

Ansible done (role postgis).

Set up the postgres server: follow Db-support

#3 Updated by Philippe May about 3 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 3 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 3 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 3 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