Database Maintenance

The site is powered by two databases. Anyone attempting an operation on the database should read and understand the respective database documentation before attempting any live changes and should attempt the changes on the virtual machine before running them on the live database.

A MySQL backend ( MySQL documentation) is responsible for the content management portion of the site. this database houses the articles, users, projects, accidents, store content, and forums. The database is backed up once a day currently.

Postgresql ( Postgresql documentation) is responsible for river information, gauge information, and photos. This database is backed up on a daily basis and most of the content that is editable by users is designed to be recoverable by a database administrator.

The site runs with two databases currently.

• The Mysql database, under wh2o, contains most of our production data. It is located on the production server.
• The Postgresql database, under wh2o, contains most of the gauge and river information and is located on the database server.

The production website connects to the mySQL database locally and credentials traditionally. The MySQL database does not bind any TCP/IP ports.

The website connects to the postgresql database via an SSH tunnel to the database server which is maintained by xinetd. The website credentials traditionally in the firewall restricts incoming connections on the postgresql port to the website machine.

MySQL is backed up nightly to the production server into the directory /home/var/mysqldump. From there it is picked up by the backup process and moved to an offsite FTP server with a 14 day differential backup.

Postgresql is backed up nightly to the database server into the directory /home/site/pgdump. From that directory the content portion which includes user-entered data is picked up by the backup process and moved to an offsite FTP server with a 14 day differential backup. The historical data which includes gauge histories is moved to an offsite FTP site on a daily basis with no differential due to its size.

Since 2005 the databases have been free of corruption due to a combination of improvements from the database vendors, stability improvements in the operating systems, and a stable production environment.

MySQL databases had previously had problems with corrupt tables which can be fixed with the MySQL repair tools. MySQL has also suffered from upgrade glitches which require the use of the MySQL upgrade script. The only other problem that we have had with MySQL is permissions issues relating to stored procedures. When restoring stored procedures and tables, and is important that the logged in user be the same user as the website uses. MySQL upgrades should be tested on the virtual machine before they're pushed out to the production servers so that potential problems can be anticipated.

Posgresql has been relatively stable. The big issue with Postgresql has been performance. Queries need to be tested under real-world conditions due to the fickle query optimizer.