Project

General

Profile

Home » Database development » VegBIEN »

maintenance FAQ

from the 2014-08-21 conference call:

how to maintain server

  • when installing system updates, note that Postgres updates will cause the server to be restarted and cancel any running queries
  • when upgrading Ubuntu, first stop Postgres (sudo service postgresql stop), shut down the VM, and make a complete, easily-restorable backup of the VM, including the database and bootloader
  • before and after database changes, snapshot the DB so the latest changes end up on tape: see README.TXT > Maintenance > to back up the vegbiendev databases
  • to restart DB: sudo service postgresql restart
    • no need to manually start DB on reboot because this happens automatically
  • note archival needs for files and database backups

user account creation

see adding a database user

upgrading packages

see how to maintain server above

where things are

see import steps

how to run refreshes and updates

see README.TXT > Single datasource refresh

how to fix small bugs

edit applicable code and reload the database or the applicable datasource

status of bugs

see the Redmine issue tracker

from the 2014-08-21 meeting with Mark and Nick:

OS version

Ubuntu 14.04.1

packages and additional dependencies

see top-level Makefile, geoscrubbing README

config files changed

  • kernel settings in /etc/sysctl.conf
  • ssh settings in /etc/ssh/sshd_config
  • Postgres config files in /var/lib/postgresql/9.3/main/
  • Apache config files in /etc/apache2/
  • phpPgAdmin config files at /etc/phppgadmin/config.inc.php, /usr/share/phppgadmin/login.php
  • phpMyAdmin config files at /etc/phpmyadmin/config.inc.php, /usr/share/phpmyadmin/libraries/plugins/auth/AuthenticationCookie.class.php
  • TWiki config files in /home/bien/web/BIEN3/TWiki/

Makefile that has dependencies

see top-level Makefile

database refresh/reload

see README.TXT > Full database import

adding a table/column

see mapping a new table in a normalized SQL datasource

how to update original source databases

see how to run refreshes and updates above

where mapping and import process takes place

see import steps

from Mark on 2014-8-20:

how big is the codebase in SVN?

The svn checkout is currently 440 MB, but 365 MB of that is svn metadata in the .svn folder. That leaves 75 MB of actual code.

can't we do snapshots of the system "minus" the DB contents?

No, the DB contents are the most important part. Without them, BIEN will not function.

We will clearly need to upgrade the OS and packages due to security and support concerns

In that case, you should be sure to make a complete backup of the VM before doing so, in case anything breaks.

what is "@starscream"?

My local computer, which is actually now called frenzy.

a list of the specific "datasources" in the README.txt file

They are listed under VegBIEN contents.

what table(s) holds [datasources] in the VegBIEN schema?

The source table.

why are there so many references to MySQL-- it seems like we must also run MySQL databases in order to import into VegBIEN

Many of our input datasources are MySQL.

Aren't we getting dumps from MySQL that then only require a few syntax changes to import into PostgreSQL?

No, they must be loaded into a MySQL database and exported out into a format acceptable to the MySQL-to-Postgres translation scripts.

When you refer to "sudoing" from the aaronmk account, are there critical environmental dependencies in your shell/path, etc.?

Yes

have you regenerated the CSV's of view-full and analytical-stem-- w GBIF and w/o GBIF

No, the TNRS fixes need to be made before the analytical tables and CSVs can be reloaded, because they use the TNRS results.

Do you have strong confidence that the new queries are correct?

Assuming you are referring to my changes to the geoscrubbing code for Brad's Native Status Resolver, yes, I believe these are correct.

Is there some place where the standard query results (and queries generating them) are being presented on the VegBIEN site-- e.g. the ones that Brian Enquist request of you before the ESA meeting?

They are under VegBIEN FAQ.

from Mark on 2014-08-29

workflow to refresh Darwin Core datasource

see Refreshing a Darwin Core datasource

workflow to refresh GBIF

GBIF no longer provides updates in the form that we originally obtained, so we would have to switch to their new portal's format, and reimplement all the plants filtering rules in Postgres