20 BETY Database Administration

This section provides additional details about the BETY database used by PEcAn. It will discuss best practices for setting up the BETY database, how to backup the database and how to restore the database.

20.0.1 Best practices

When using the BETY database in non testing mode, it is best not to use the default users. This is accomplished when running the initialize of the database. When the database is initally created the database will be created with some default users (best known is the carya user) as well as the guestuser that can be used in the BETY web application. To disable these users you will either need to disable the users from the web interface, or you can reinitialize the database and remove the -u flag from the command line (the -u flag will create the default users). To disable the guestuser as well you can remove the -g flag from the command line, or disable the account from BETY.

The default installation of BETY and PEcAn will assume there is a database called bety with a default username and password. The default installation will setup the database account to not have any superuser abilities. It is also best to limit access to the postgres database from trusted hosts, either by using firewalls, or configuring postgresql to only accept connections from a limited set of hosts.

20.0.2 Backup of BETY database

It is good practice to make sure you backup the BETY database. Just creating a copy of the files on disk is not enough to ensure you have a valid backup. Most likely if you do this you will end up with a corrupted backup of the database.

To backup the database you can use the pg_dump command, which will make sure the database id backed up in a consistent state. You can run sudo -u postgres pg_dump -d bety -Z 9 -f bety.sql.gz, this will create a compressed file that can be used to resotore the database.

In the PEcAn distribution in scripts folder there is a script called backup.bety.sh. This script will create the backup of the database. It will create multiple backups allowing you to restore the database from one of these copies. The database will be backed up to one of the following files: - bety-d-X, daily backup, where X is the day of the month. - bety-w-X, weekly backup, where X is the week number in the year - bety-m-X, montly backup, where X is the month of the year - bety-y-X, yearly backup, where X is the actual year. Using this scheme, we can restore the database using any of the files generated.

It is recommeneded to run this script using a cronjob at midnight such that you have a daily backup of the database and do not have to remember to create these backups. When running this script (either cron or by hand) make sure to place the backups on a different machine than the machine that holds the database in case of a larger system failure.

20.0.3 Restore of BETY database

Hopefully this section will never need to be used. Following are 5 steps that have been used to restore the database. Before you start it is worth it to read up online a bit on restoring the database as well as join the slack channel and ask any of the people there for help.

  1. stop apache (BETY/PEcAn web apps) service httpd stop or service apache2 stop
  2. backup database (you know just incase) pg_dump -d bety > baddb.sql
  3. drop database sudo -u postgres psql -c 'drop database bety'
  4. create database sudo -u postgres psql -c 'create database bety with owner bety'
  5. load database (assuming dump is called bety.sql.gz) zcat bety.sql.gz | grep -v search_path | sudo -u postgres psql -d bety
  6. start apache again service httpd start or service apache2 start

If during step 5 there is a lot of errors, it is helpful to add -v ON_ERROR_STOP=1 to the end of the command. This will stop the restore at the first error and will help with debugging the issue.