Skip to content

sysadmin_guide

Jump to bottom
Ryan Hodges edited this page Apr 13, 2023 · 13 revisions

TEK DB Technical Information and Maintenance Manual

Dependency Stack

  • Operating System (pick 1):
    • Ubuntu 22.04 LTS (recommended/supported, 20.04 and newer LTS)
    • Other Linux Server distributions
    • Windows Server
  • Web Server (pick 1):
    • NGINX (recommended/supported)
    • Apache
    • IIS (for Windows)
  • Database:
    • PostgreSQL with PostGIS extension
  • Language:
    • Python 3.8+
  • Framework:
    • Django 3.2

Code Source

All code and documentation is available for free (under the MIT license) on Github: https://github.com/Ecotrust/TEKDB

Exporting the data

Automated Export for Backup

In 2022, a new feature was added to simplify the process below – it allows users with administrative privileges to export a zipped archive file containing a 'fixture' file to restore database records, as well as a folder containing all relevant media files (videos, photos, audio, pdfs, etc…) supported by the database.

First, the user must log in using their admin credentials. Next they should click on the menu button at the top right, which should have their username on it. From that button, they should find an option in the dropdown menu saying 'administration' which will take them to the Django Administration dashboard.

In the top right corner they should have a button that reads 'Export to .zip', alongside an 'Info' button, which will remind them about the sensitivity of the file they are about to download: all of the traditional knowledge that has been accumulated into the database thus far, captured in a single, unencrypted file.

Manual Export for Backup

If you are reinstalling the tool and need to preserve or migrate your data (not the same as migrating from the old Microsoft Access-based MTKEDB) you can create a 'fixture' (a backup file) that can be imported into new instances of the TEK DB. To do so:

  • Decide on a safe place to write the file to
  • Decide on a meaningful name for the file, like tek_db_fixture_YYYYMMDD.json
    • where YYYYMMDD is today's date
    • json is the data format. If you don't name it with the .json extension this process will still work.
  • Navigate to your project and activate your virtual environment:
    • Linux 
      cd /usr/local/apps/TEKDB
source env/bin/activate
cd ./TEKDB
    • Windows 
      cd C:\apps\TEKDB
env\scripts\activate.bat
cd .\TEKDB  #if manage.py is not in this TEKDB dir
  • Then run this WHOLE command to dump the data (the order is very specific to protect data dependencies) replacing the file at the end after the > with the file you chose above
python manage.py dumpdata --indent=2 auth.Group Lookup.LookupPlanningUnit Lookup.LookupTribe Lookup.LookupHabitat TEKDB.Places Lookup.LookupResourceGroup TEKDB.Resources Lookup.LookupPartUsed Lookup.LookupCustomaryUse Lookup.LookupSeason Lookup.LookupTiming Relationships.PlacesResourceEvents Lookup.LookupParticipants Lookup.LookupTechniques Lookup.LookupActivity TEKDB.ResourcesActivityEvents Lookup.People Lookup.LookupReferenceType Lookup.LookupAuthorType TEKDB.Citations Relationships.PlacesCitationEvents Lookup.CurrentVersion Lookup.LookupLocalityType TEKDB.Locality Relationships.LocalityGISSelections Relationships.LocalityPlaceResourceEvent Lookup.LookupMediaType Lookup.LookupUserInfo TEKDB.Media Relationships.MediaCitationEvents Relationships.PlaceAltIndigenousName Relationships.PlaceGISSelections Relationships.PlacesMediaEvents Relationships.PlacesResourceCitationEvents Relationships.PlacesResourceMediaEvents Relationships.ResourceActivityCitationEvents Relationships.ResourceActivityMediaEvents Relationships.ResourceAltIndigenousName Relationships.ResourceResourceEvents Relationships.ResourcesCitationEvents Relationships.ResourcesMediaEvents Accounts.UserAccess Accounts.Users explore.PageContent > /path/to/your/file.json

Updating The Code

The code is all on GitHub and available to the public. Git, a tool for sharing the code and tracking changes made to it is already installed on your server. With these two pieces in place, getting code updates fairly easy, but can contain risks:

  • It is highly recommended that you take a snapshot of your server before doing this.
  • If you are not sure what new features you want out of this upgrade, there is no need to do perform an update. If you are unsure, collaborating with Ecotrust could help sort out what features have been added since your last update: ksdev@ecotrust.org.
  • Major risks:
    • New code will include database migrations that did not anticipate types of data in your instance. This could result in loss of data.
    • Local changes could have been made to your code base, resulting in a 'merge conflict'. Without familiarity with 'Git' these can be difficult to resolve, and guessing can quickly break things beyond repair.
    • Restoring from backup will fix both of these issues, but will prevent you from installing new updates. If you hit any of the issues above, please contact Ecotrust and ask for the Software Developers, or email the software team directly at ksdev@ecotrust.org.

Automatic Update Script

As of 2022, there are new scripts (one for Linux installations, one for Windows) that will perform the following tasks for you, assuming:

  • You created your Python virtual environment with the name 'env' and it lives in the top level of your git-cloned repository.
  • You have git installed for the user running the script
  • The user running the script has permissions to read/write/execute
    • Your project folder
    • Your application servers (IIS or NGINX)
    • Git
  • If on Linux, your user has sudo permissions to run /usr/sbin/service nginx restart and /usr/sbin/service uwsgi restart
    • If not, this can be granted by editing the configurations in visudo to grant this access to the user's group
  • If on Windows, the executable to restart IIS is at C:\Windows\System32\iisreset.exe

If all is installed correctly, you should be able to just run one of the following scripts:

  • For Linux:
    • /path/to/app/TEKDB/scripts/Linux/update.sh
  • For Windows:
    • \path\to\app\TEKDB\scripts\Windows\update.bat

The script performs the following tasks:

  1. Pulls the latest code updates for your current branch of the Git repository
  2. Installs any required updated python packages
  3. Applies any updates to your database schema (new or altered tables, columns, etc…)
  4. Prepares static files to be served by the application server
  5. Restarts your servers:
    • Linux – Uwsgi + NGINX
    • Windows – IIS

Manual Update Procedure

  1. Log in to your server (via SSH for Linux)
  2. Change directories to TEK's code:
    • Linux: cd /usr/local/apps/TEKDB
    • Windows: cd C:\apps\TEKDB
  3. Retrieve all code updates from GitHub: 
    git fetch
git merge origin/main
  4. Activate your virtual environment:
    • Linux: source env/bin/activate
    • Windows: env\scripts\activate.bat
  5. If 'manage.py' is not in the current folder, cd into the TEKDB folder that does have it (most likely ./TEKDB)
  6. Run pip install -r requirements.txt
    • If on Linux, also run pip install -r requirements_linux.txt
  7. Run database migrations: python manage.py migrate
    1. If new tables are added or old tables are changed this should update your database schema and transform any necessary data appropriately, but this comes with some risk to your data, hence the backup recommendation.
  8. Collect 'static files' to be served by the application: python manage.py collectstatic
  9. Compress eligible 'static files' for quicker services: python manager.py compress
  10. Restart servers:
    • Linux:
      • Restart uWSGI: sudo service uwsgi restart
      • Restart NGINX: sudo service nginx restart
    • Windows:
      • Restart IIS: C:\Windows\System32\iisreset.exe

OS Security Updates (Linux)

Ubuntu Linux can be set up to download and install security patches by default, and a cron job can be created to restart them during convenient hours if needed.

See the unattended upgrade steps in installation to see how this is (or was) done.

If your server is not to be made accessible via the internet, and you trust all users logging on to your network, you may prefer not to download OS updates, as they can also (very rarely) be a source for corruption.

If you would like to upgrade by hand on Ubuntu Linux, do the following:

  1. Query for all available updates: sudo apt-get update
  2. List all available updates: sudo apt list --upgradable
  3. Install available updates:
    • ALL: sudo apt-get upgrade
    • A single package: sudo apt install {PACKAGE_NAME}

If you are managing a Windows server, it is assumed that you chose this out of proficiency with Windows, and that you have the knowledge to update your server yourself.

Restart the server if prompted and server traffic is anticipated to be light.

Services

A deeper dive into some of the most important and dynamic pieces of the tool stack. Please note that commands below assume you are on Ubuntu Linux, and you may need to find another command to perform the same task on other operating systems. Links to each tool's website are provided in case more information is needed.

PostgreSQL & PostGIS

The database server, if causing problems, can be restarted with sudo service postgres restart.

On Linux, you can find most of PostgreSQL's files here: /etc/postresql

By default, logs will be kept here: /var/log/postgresql/

For more information, check out the PostgreSQL website and the PostGIS website.

It is expected that if the organization installing and running this database tool has a GIS department, they will want to connect to the data in the tool directly. There are some limits to this access, which should also be covered in the GIS User Guide.

  1. If you install on Windows, connecting to the PostgreSQL/PostGIS database from ArcGIS may be difficult, or even disabled.
  2. If the database is installed on Linux, ArcGIS users will be able to access the data as 'read only' – they can view it, they can save and edit it locally for their purposes, but they cannot add to or edit the data that is inside the database directly.
  3. If the GIS team uses other GIS tools, such as QGIS, they very well may be able to add and edit data in the database directly.

NGINX

The default recommended HTTP server for Linux installs. Can be restarted with sudo service nginx restart

NGINX's files can be found in /etc/nginx

Configuration is managed in /etc/nginx/sites-available/tekdb

  • A symlink to this file exists in /etc/nginx/sites-enabled/ if the site is live

Both access and error logs are held in /var/logs/nginx/ by default

For more information, check out the NGINX website.

uWSGI

A service enabling Django projects (and other webapp frameworks) to be served by HTTP servers.

Can be restarted with sudo service uwsgi restart

For more information, check out the uWSGI Project website.

IIS

The default recommended server for Windows installations of this web application

Other Server Management Commands [Ubuntu Linux]

Shutdown: sudo shutdown 0

Restart: sudo reboot

Show Running Processes and Hardware Usage: htop

  • Note: this is an installed app that does not come with the operating system. It is an easier-to-read version of top which should come with the OS by default.

Update the ITK Database application: /path/to/app/TEKDB/scripts/Linux/update.sh

  • this is most likely: /usr/local/apps/TEKDB/scrips/Linux/update.sh

Troubleshooting

Lost Django Admin Password

Only Admin users can create or edit users, and this includes passwords. If no administrator is able to login, this can be alarming, but it is easily fixed from the back end.

  • Log in to the server
  • Navigate to the project folder (likely /usr/local/apps/TEKDB)
  • Activate the virtual environment source env/bin/activate
  • Navigate to the manage.py file (either here or in ./TEKDB)
  • Create a new Super User with python manage.py createsuperuser
    • You will be prompted to provide a username and a password
  • Log in with the new Super User and reset the Admin passwords to something they will remember

Lost Database Name/Username/Password

The only likely reason a user would need credentials to connect directly to the PostgreSQL/PostGIS database is to connect GIS software such as ArcMap or QGIS. If they aren't asking about GIS, they probably mean 'Database Application' credentials, not the underlying RDBMS itself. Kindly direct them to the site administrator, or if they are the site administrator, see the 'Lost Django Admin Password' section above.

There are a few good places to look for these credentials:

  1. A password manager.
    • Hopefully these credentials were shared with either the IT team or the project team during installation, but if not, now's your chance to right that wrong.
  2. Ask the server:
    • The server knows how to connect to the database, so SSH in and confirm its settings.
    • QUICK METHOD:
      • These settings should be found in /usr/local/apps/TEKDB/TEKDB/TEKDB/local_settings.py
      • Open the file for reading with less /usr/local/apps/TEKDB/TEKDB/TEKDB/local_settings.py
        • Once inside, find a section that starts with DATABASES = {
          • Inside this block you will see:
          DATABASES = {
    'default': {
        'ENGINE': 'django.contrib.gis.db.backends.postgis',
        'NAME': '__DATABASE_NAME__',
        'USER': '__DATABASE_OWNER__',
        'PASSWORD': '__PASSWORD__',
        'HOST': '__HOST__',
        'PORT': '__PORT__',
    }
}
          • Note down the values for __DATABASE_NAME__, __DATABASE_OWNER__, __PASSWORD__, __HOST__, and __PORT__.
          • If the value of __HOST__ is 'localhost' (most likely), be sure to share the other 4 values, but replace 'localhost' with the IP address of the server when sharing the 'HOST' value.
    • BEST METHOD:
      • If 'probably' isn't good enough, let's ask more directly:
        • Activate a python virtual environment: source /usr/local/apps/TEKDB/env/bin/activate
        • Open a Django Management Shell (python): python /usr/local/apps/TEKDB/TEKDB/manage.py shell
        • Import settings: from django.conf import settings
        • Get the ACTUAL 'DATABASES' settings values: from django.conf import settings
        • Note down the values as you would above ('NAME', 'USER', 'PASSWORD', 'HOST', and 'PORT')
          • Just like before, if 'HOST' is 'localhost' share the IP address of the server instead
        • Type quit to exit (you can also use the keys 'ctrl' + 'd', then confirm with 'y')
        • Deactivate your python virtual environment: deactivate
        • Log off the server: exit

Site is Down

  • Is the error:
    • 404 - not found:
      • Check that NGINX is running with sudo service nginx status
        • replace status with start if not
      • Check the NGINX logs for clues
      • Check the PostgreSQL logs
      • Check the (virtual) hardware:
        • Is the hard drive full (can happen from old logs building up or lots of media served from the tool)
          • See if you can't clear enough space by deleting old log files
          • Increase the hard drive capacity
      • Check the network:
        • are you on the same network as the tool?
        • does the given URL point to the server's IP address in your DNS records?
        • is NGINX configured to listen to requests to the given url?
        • is NGINX listening on port 80 (or whichever port the request is being made to)
      • If all else fails, turn it off and on again.
    • A page loads, but tool doesn't show:
      • Check network configuration (as above)
      • Check NGINX is running
      • Check NGINX logs to confirm it is receiving the requests
      • Check NGINX config to see that it is correct
      • Check uWSGI is running and configured correctly
      • Restart services one at a time and test in this order:
        • uWSGI
        • NGINX
        • The server itself
    • A Django Error (yellow header with gray debug information):
      • NOTE: this page should not show - be sure to set DEBUG = False in your settings.py and local_settings.py files
      • Review the page content for hints.
      • Review the NGINX docs for hints.
      • Restart uWSGI
      • Reboot the server
      • Create an issue on the GitHub project describing the error and how it was triggered.
    • A Plain Error Page (Bold black header, plan black text on white background):
      • Follow same steps as 404 - Not found error
    • Page content appears but is mangled:
      • Does page still look mangled on a wider browser window?
      • No images or improper page layout:
        • from the virtual environment and project folder, run python manage.py collectstatic
        • Ensure that the file TEKDB/TEKDB/settings.py contains DEBUG=False
        • Ensure that the file TEKDB/TEKDB/local_settings.py DOES NOT contain DEBUG=True
      • Refresh browser cache
Clone this wiki locally