Versions Compared

Old Version 141

changes.mady.by.user Dan King

Saved on

compared with

New Version 142

changes.mady.by.user Dan King

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Put non-production steps prior to production steps.

Table of Contents
Overview

...

Stage NumberStageCategorySummaryEnvironmentIterationsEstimated Person TimeEstimated Computer Time
1Import and parse reference dataset (Optional)ParsingThis optional step in the Bulk Loader process is to cross check an address for a match in a reference data set. If a source address is found in the reference dataset the address makes it to the next step. If not found the address is put aside in an exclusion set for later review.

Python 3

PostgreSQL / pgAdmin

Once per Bulk Loader process1 hour10 minutes
2Import, parse and filter source datasetParsingImport the dataset destined for the EAS. Parse and filter the set.

Python 3

PostgreSQL / pgAdmin

Once per Bulk Loader process90 minutes15 minutes

3

Geocode and filterGeocodingGeocode the set and filter further based on the geocoder score and status.ArcMapOnce per Bulk Loader process1 hour 5 minutes
4Export full set (single batch) or subset (multiple batches)GeocodingFor large datasets, create one of many subsets that will be run through the Bulk Loader in multiple batches.ArcMapOne or more batches for each Bulk Loader process30 minutes per batch5 minutes per batch
5Bulk Load batch (full set or subset)Bulk LoadingRun the entire batch or each subset batch through the Bulk Loader.

EAS <environment>(+)

PostgreSQL / pgAdmin

One or more batches for each Bulk Loader process1 hour per batch5 minutes per batch
6Extract resultsBulk LoadingExtract and archive the list of addresses that were added to the EAS . Also archive the unique EAS 'change request id' associated with this batch. Also archive the addresses that were rejected by the Bulk Loader in this batch.PostgreSQL / pgAdminOne or more batches for each Bulk Loader process1 hour per batch5 minutes per batch
7Cleanup and RestorationBulk LoadingClean up database, restore services and in the event of a failure, restore from backup.PostgreSQL / pgAdminOne or more batches for each Bulk Loader process1 hour per batch5 minutes per batch

...

Anchor
stage1
stage1
Stage 1 - Import and parse reference dataset (Optional)

This optional stage is run once per Bulk Loader process. This stage can be skipped if the reference dataset is already available or if the optional 'filter by reference' step (Step 2.5) is skipped.

...

Anchor
stage3
stage3
Stage 3 - Geocode and filter

  •  

    Step 3.1 - Geocode source dataset

...

Anchor
stage4
stage4
Stage 4 - Export full set (single batch) or subset (multiple batches)

Note
titleA note about batches

Stages 4, 5 and 6 can be run one time with the results from Stage 3, or they can be run in multiple batches of subsets.

A major consideration of when to run the full set at once versus in batches is the number of records being Bulk Loaded.

The size of each Bulk Loader operation affects the following aspects of the EAS:

  • The disk space consumed by the database server
  • The EAS user interface section that lists addresses loaded in a given Bulk Loader operation
  • The weekly email attachment listing new addresses added to the EAS

For medium-to-large datasets (input sets with over 1,000 records) it is recommended that the process first be run on a development server to assess the implications of the operation. Where appropriate, perform the Bulk Loading process in batches over several days or weeks.

The remaining steps will document a single batch example iteration of a multi-batch process.

...

Anchor
stage5
stage5
Stage 5 - Run the Bulk Loader

(info) For a complete set of steps and background about the Bulk Loader, see also Running the Bulk Loader, a page dedicated to its input, operation and results.

  •  
  •  

    Step 5.1 - Non-

    Production

    production-specific preparation

  1. Halt Services

    WarningtitleReason for Restore Database
    1. Restore database from latest daily production backup
  2. Halt web application

    1. Place EAS web application into maintenance mode

      • Place the web server into maintenance mode (SF DEV WEB, SF QA WEB).


        Code Block
        languagebash
        titlePlace EAS web application into maintenance mode
        linenumberstrue
        cd /var/www/html
sudo ./set_eas_mode.sh MAINT


  •  

    Step 5.2 - Production-specific preparation

  •  

  1. Halt Services

    Warning
    titleReason for halting services

    These steps are being performed to facilitate immediate roll-back of the EAS database if the Bulk Load Process ends in failure.


    1. Place EAS web application into maintenance mode

      1. Place the Web servers into maintenance mode (SF PROD WEB, DR PROD WEB).


        Code Block
        languagebash
        titlePlace EAS web application into maintenance mode
        linenumberstrue
        cd /var/www/html
sudo ./set_eas_mode.sh MAINT


    2. SKIP Turn off the replication server

      1. Disable database replication by shutting down the database service on the replication server (DR PROD DB).


        Code Block
        languagebash
        titleStop PostgreSQL
        linenumberstrue
        sudo -u postgres -i
/usr/pgsql-9.0/bin/pg_ctl -D /data/9.0/data stop


    3. Turn off downstream database propagation service(s)

      1. Suspend downstream replication to internal business system database (SF PROD WEB).

        Code Block
        languagetext
        titlestop xmit
        linenumberstrue
        sudo /var/www/html/eas/bin/xmit_change_notifications.bsh stop


  2. Backup Database
    1. Make a backup of the EAS database


  •  

    Step 5.2 - Non-production-specific preparation

...

  1. Restore database from latest daily production backup

...

Place EAS web application into maintenance mode

Place the web server into maintenance mode (SF DEV WEB, SF QA WEB).

Code Block
languagebash
titlePlace EAS web application into maintenance mode
linenumberstrue
cd /var/www/html
sudo ./set_eas_mode.sh MAINT
  •  Step 5.3 - Database Preparation

  1. Connect to the database, <environment>_DB, and clear any leftover records from previous Bulk Loader batches.

    Code Block
    languagesql
    titleTRUNCATE
    linenumberstrue
    TRUNCATE bulkloader.address_extract, bulkloader.blocks_nearest;


    Code Block
    languagesql
    titleVACUUM
    linenumberstrue
    VACUUM FULL ANALYZE bulkloader.address_extract;

VACUUM FULL ANALYZE bulkloader.blocks_nearest;



  2. Make note of EAS record counts before the Bulk Loading operation.

    Code Block
    languagesql
    firstline1
    titleRecord Counts
    linenumberstrue
    SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables ORDER BY schemaname,relname,n_live_tup
    1. Save results in artifact as record-counts-before.csv 
    2. Also save results in Excel spreadsheet artifact as bulkloader_results_YYYYMMDD.xlsx (TODO provide path to a template)

  3. Make note of the database partition size on the file system at the current point in time.

    Code Block
    languagebash
    firstline1
    titledisk usage
    linenumberstrue
    date; df /data


...

Anchor
stage6
stage6
Stage 6 - Extract results

  •  

    Step 6.1 - Archive exceptions

...

  1. If the Bulk Loader Process was run on the production server then restore services
    1. SKIP Turn on production-to-replication service

      • Re-enable database replication by restarting the database service on the replication server (DR PROD DB).


        Code Block
        languagebash
        titleStop PostgreSQL
        linenumberstrue
        sudo -u postgres -i
/usr/pgsql-9.0/bin/pg_ctl -D /data/9.0/data start


    2. SKIP Turn on downstream database propagation service(s)

      • Resume downstream replication to internal business system database (SF PROD WEB).

        Code Block
        languagetext
        firstline1
        titlestart xmit
        sudo /var/www/html/eas/bin/xmit_change_notifications.bsh start


    3. Enable front-end access to EAS

      • Place the Web servers into live mode (SF PROD WEB, DR PROD WEB).

        Code Block
        languagebash
        linenumberstrue
        cd /var/www/html
sudo ./set_eas_mode.sh LIVE


...