Page Comparison

Table of Contents

Overview

...

...

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)

...

Anchor
stage5 stage5

Stage 5 - Run the Bulk Loader

 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

  - Bulk Loader Preparation

...

Transfer the bulkload.shp shapefile from previous step to an EAS automation machine, <environment>_AUTO.

• Substitute <environment> with one of the relevant environments: SF_DEV, SF_QA, SF_PROD, SD_PROD.

...

• - Production-specific preparation

1. Backup Database
   1. Make a backup of the EAS database
      • See also Backup the EAS Databases

2. Halt Services

   Warning
   title Reason 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

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

        
        

        Code Block
        language bash title Place EAS web application into maintenance mode linenumbers true
        cd /var/www/html sudo ./set_eas_mode.sh MAINT

        
        

   2. Turn off the replication server

      • TODO: Add steps

   3. Turn off downstream database propagation service(s)

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

        Code Block
        language text title stop xmit linenumbers true
        sudo /var/www/html/eas/bin/xmit_change_notifications.bsh stop

        
        

•  

  Step 5.2 - Non-production-specific preparation

1. Restore Database
   1. Restore database from latest daily production backup

•  

  Step 5.3 - Backup Database

1. Make a backup of the EAS database
   • See also Backup the EAS Databases

•  

  Step 5.4 - Database Preparation

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

   Code Block

   language	sql
   title	truncate
   linenumbers	true

   TRUNCATE bulkloader.address_extract, bulkloader.blocks_nearest;

   batches.

   Code Block
   language sql title vacuumtruncate linenumbers true
   VACUUM FULL ANALYZETRUNCATE bulkloader.address_extract;
   Code Block
   language sql title vacuum linenumbers true
   VACUUM FULL ANALYZE , bulkloader.blocks_nearest;

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

   
   

   Code Block
   language sql firstline 1 title Record Countsvacuum linenumbers true
   SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables ORDER BY schemaname,relname,n_live_tup

   Make note of the database partition size on the file system.

   VACUUM FULL ANALYZE bulkloader.address_extract;

   
   

   Place EAS web application into maintenance mode

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

   Code Block
   language bash firstline 1sql title disk usagevacuum linenumbers true
   df /data

•  

  Step 5.2 - Halt Services (production only)

1. VACUUM FULL ANALYZE bulkloader.blocks_nearest;

   
   

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

   
   

   Code Block
   language

   bash

   sql firstline 1 title

   Place EAS web application into maintenance mode

   Record Counts linenumbers true

   cd /var/www/html
   sudo ./set_eas_mode.sh MAINT

3. Turn off the replication server

   • TODO: Add steps

4. Turn off downstream database propagation service(s)

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

   SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables ORDER BY schemaname,relname,n_live_tup

   
   

5. Make note of the database partition size on the file system.

   Code Block
   language

   text

   bash firstline 1 title

   stop xmit

   disk usage linenumbers true

   sudo /var/www/html/eas/bin/xmit_change_notifications.bsh stop

   df /data

   
   

•  

  Step 5.5 - Transfer Shapefiles 

1. Transfer the bulkload.shp shapefile fromStep 4 to an EAS automation machine, <environment>_AUTO.
2. Substitute <environment> with one of the relevant environments: SF_DEV, SF_QA, SF_PROD, SD_PROD.
3. Copy the shapefile to the folder C:\apps\eas_automation\app_data\data\bulkload_shapefile.

•  

  Step 5.

  3 - Backup Database

1. Make a backup of the EAS database
   • See also Backup the EAS Databases

•  Step 5.4 - Run Bulk Loader

  6 - Run Bulk Loader

1. Transfer the bulkload.shp shapefile from Step 4 to an EAS automation machine, <environment>_AUTO.

   • Substitute <environment> with one of the relevant environments: SF_DEV, SF_QA, SF_PROD, SD_PROD.

2. Copy the shapefile to the folder C:\apps\eas_automation\app_data\data\bulkload_shapefile.

3. Open a command prompt and change folders:

   • cd C:\apps\eas_automation\automation\src

4. Run the step to stage the address records:

   • python job.py --job stage_bulkload_shapefile --env SF_DEV --action EXECUTE --v

5. Run the step to bulk load the address records:

   • python job.py --job bulkload --env SF_DEV --action EXECUTE --v

6.  To calculate the time it took to run the Bulk Loader look at the timestamps in the output or use a stopwatch or clock to time the operation.

   
   

•  

  Step 5.

  5

  7 - 

  Anchor
  analysis analysis

  Analysis

1. Make note of EAS record counts after the Bulk Load operation.

   Code Block
   language sql firstline 1 title Record Counts linenumbers true
   SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables ORDER BY schemaname,relname,n_live_tup

   
   

   • A comparison of 'before' and 'after' record counts will indicate the number of new base addresses added to the table `public.address_base` and the number of new addresses and units added to the table `public.addresses`.

   1.  See dedicated Bulk Loader page, Running the Bulk Loader, for more analysis options.
      
      

2. Make note of the database partition size on the file system. Compre with size of partition prior to loading to get the total disk space used as a result of running the Bulk Loader.

   Code Block
   language bash firstline 1 title disk usage linenumbers true
   df /data

   
   

3. Query and make note of totals in the bulkloader.address_extract table. The results here will be used to cross check the results in the next stage.

   1. Count/view new base addresses added to the EAS.

      
      

      Code Block
      language sql firstline 1 title Count/view new base addresses linenumbers true
      SELECT COUNT(*) FROM bulkloader.address_extract WHERE NOT (street_segment_id IS NULL) SELECT * FROM bulkloader.address_extract WHERE NOT (street_segment_id IS NULL)

      
      

   2. Count/view unit addresses (some were already there, some are new)

      Code Block
      language sql firstline 1 title Count/view unit addresses linenumbers true
      SELECT COUNT(*) FROM bulkloader.address_extract WHERE NOT (address_id IS NULL) SELECT * FROM bulkloader.address_extract WHERE NOT (address_id IS NULL)

      
      

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. Turn on production-to-replication service

      • TODO: add steps

   2. Turn on downstream database propagation service(s)

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

        Code Block
        language text firstline 1 title start 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
        language bash linenumbers true
        cd /var/www/html sudo ./set_eas_mode.sh LIVE

        
        

...