Skip to content

Latest commit

 

History

History
148 lines (94 loc) · 4.86 KB

databasescripts.rst

File metadata and controls

148 lines (94 loc) · 4.86 KB
Raw
.. index:: database

Database Management Scripts

There are a number of python and shell scripts, in the /scripts/ directory and elsewhere, which are used to manage socorro in a staging and development environment, as well as to deploy upgrades. These scripts are detailed below.

Upgrade Scripts

These scripts are used on a weekly basis to upgrade the various socorro PostgreSQL database servers.

auto-upgrade.py

Location: /sql/upgrade/

Purpose: to "auto-upgrade" StageDB and Crash-Stats-Dev after each automatic refresh of the data in each database.

Called By: auto-refresh cron job

auto-upgrade.py [options]

options:
-P DIRPATH, --path=DIRPATH  path to the upgrade directory
        default: /data/socorro/application/sql/upgrade
-D DBNAME, --database=DBNAME  database to upgrade
    default: breakpad
-p DBPORT, --port=DBPORT  database port
    default: 5432
-H DBHOST, --host=DBHOST  database hostname
    default: localhost
-l LOGFILE, --log=LOGFILE logfile location for output
    default: socorro_upgrade.log in the current directory

auto-upgrade.py -P /data/socorro/application/sql/upgrade \
        -l /var/log/upgrade/db_upgrade.log -D breakpad

auto-upgrade queries the socorro_db_version.current_version field in the database to determine the current database version. It then walks the path given, running upgrade.sh from each numbered directory in sorted version order. If it hits an error in any upgrade, it stops.

Note: must be run as the "postgres" user, in its shell environment.

upgrade.sh

Location: /sql/upgrade/#.#/

Called By: auto-upgrade script or manually by DBA

upgrade.sh [dbname]

dbname parameter is optional. Defaults to "breakpad" if not supplied.

An upgrade.sh script exists in each numbered upgrade directory, and is used to upgrade the database from one database version to another. For example, 15.0/upgrade.sh will upgrade the database from 14.0 to 15.0.

All upgrade scripts are designed to quit on the first error. To run them against a database on an alternate port, you must set the PGPORT environment variable. They are designed to be run by the database superuser and won't run otherwise.

MiniDB Scripts

This directory contains scripts for extracting and loading a smaller copy of the socorro PostgreSQL database ... called a "MiniDB" ... from production data. This MiniDB is used for testing and staging.

All of these scripts are peculiar to the Mozilla environment, and would need adaptation to run elsewhere.

ExtractMiniDB

Location: /scripts/staging

Purpose: create the raw data for a "mini" version of the breakpad database, containing only a few weeks of data.

extractMiniDB.py --weeks 2 --database breakpad --file extractdb.tgz
extractMiniDB.py -w 2 -d breakpad -f extractdb.tgz
weeks
the number of weeks of data to extract. Optional, defaults to 2.
database
database to connect to. optional, defaults to 'breakpad'
file
tarball file to create. optional, defaults to 'extractdb.tgz'

Notes: Produces a file called "extractdb.tgz" which must be loaded using LoadMiniDBonDev.py. May take a couple hours to run.

LoadMiniDBonDev

Purpose: loads the file created by ExtractMiniDB.py onto DevDB.

loadMiniDBonDev.py --file extractdb.tgz --database breakpad --postscript postsql.sh
loadMiniDBonDev.py -f extractdb.tgz -d breakpad -P postsql.sh
file
file to load from. Defaults to exractdb.tgz in the current directory.
database
database to load into. Defaults to "breakpad".
postscript
location of script to run after load. contains database objects not automatically created by load. Defaults to "/data/socorro/application/scripts/staging/postsql/postsql.sh"

Notes: the extractdb.tgz file will be uncompressed into the current directory, creating several GB of files. If the script errors out, these files will need to be cleaned up manually; for convenience they are all named *.dump. Can take several hours to complete.

loadprep.sh

Script to be run on StageDB in order to prep it for loading a new database copy buy kicking off all users and automation. Must be run as root.

Shuts down pgbouncer and restarts PostgreSQL.

afterload.sh

Script to be run after database is loaded on StageDB, which updates authentication and then restores Postgres to testing condition. Must be run as root.

Restarts PostgreSQL and pgbouncer.

backupdatadir.sh

Creates a copy of /pgdata/9.0/data for backup so that it can be restored later for testing. Intended for DevDB and StageDB.

postsql directory

Contains several SQL scripts which create database objects which error out during load due to broken dependencies, particularly views based on matviews. postsql.sh shell script calls these. Intended to be called by loadMiniDBonDev.py.