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 shapefile - 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.

...

1. 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. SKIP 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
         language bash title Stop PostgreSQL linenumbers true
         sudo -u postgres -i /usr/pgsql-9.0/bin/pg_ctl -D /data/9.0/data stop

         
         

   2. Turn off downstream database propagation service(s)

      1. 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

         
         

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

sudo -u postgres -i
/home/dba/scripts/dbbackup.sh > /var/tmp/dbbackup.log
ls -l /var/tmp # ensure the log file is 0 bytes
ls -la /mnt/backup/pg/daily/easproddb.sfgov.org-* # the timestamp on the last file listed should match timestamp of backup
exit # logout of user postgres when done

•  

  Step 5.3 - 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;

   
   

   Code Block
   language sql title VACUUM linenumbers true
   VACUUM FULL ANALYZE bulkloader.address_extract;

   
   

   Code Block
   language sql title VACUUM linenumbers true
   VACUUM FULL ANALYZE bulkloader.blocks_nearest;

   
   

2. Make note of EAS record counts before the Bulk Loading 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

   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
   language bash firstline 1 title disk usage linenumbers true
   date; df /data # 1st of 3

   
   

•  

  Step 5.4 - Transfer Shapefiles 

...

1. Open a command prompt and change folders:

   Code Block
   language bash linenumbers true
   cd C:\apps\eas_automation\automation\src

   
   

2. Run the step to stage the address records:

   Code Block
   language bash linenumbers true
   language bash linenumbers true
   python job.py --job stage_bulkload_shapefile --env <environment> --action EXECUTE --v --python job.py --job stage_bulkload_shapefile --env SF_DEV --action EXECUTE --v --python job.py --job stage_bulkload_shapefile --env SF_QA --action EXECUTE --v --python job.py --job stage_bulkload_shapefile --env SF_PROD --action EXECUTE --v

   
   

3. Run the step to bulk load the address records

   
   

   Code Block
   language bash linenumbers true
   python job.py --job bulkload --env <environment> --action EXECUTE --v --python job.py --job bulkload --env SF_DEV --action EXECUTE --v --python job.py --job stage_bulkload_shapefile --env <environment>SF_QA --action EXECUTE --v

   Run the step to bulk load the address records

   Code Block
   language bash linenumbers true
   --python job.py --job bulkload --env <environment>SF_PROD --action EXECUTE --v

   
   

4.  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.

   
   

5. Save Bulk Loader command line output artifact as bulk-loader-CLI-output.txt.

...

1. Make note of the database partition size on the file system at this point. 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
   date; df /data # 2nd of 3

   
   

2. 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

   
   

   • Save results as artifact record-counts-after.csv
   • Also save results in Excel spreadsheet artifact as bulkloader_results_YYYYMMDD.xlsx

   • In the spreadsheet, calculate the difference between the 'before' and 'after' record counts. The results 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`.

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

Anchor
stage6 stage6

Stage 6 - Extract results

•  

  Step 6.1 - Archive exceptions

...

1. Get all the base records added to the EAS during the Bulk Loader operation.
   

   1. Query the public.address_base table on the new change_request_id value.

      Code Block
      language sql firstline 1 title address_base linenumbers true
      SELECT activate_change_request_id, address_id, public.address_base.* FROM public.address_base, public.addresses WHERE public.address_base.address_base_id = public.addresses.address_base_id AND public.addresses.address_base_flg = TRUE AND public.addresses.activate_change_request_id = <change_request_id>;

      
      

   2. Save the file in the network folder dedicated to artifacts for the Bulk Loader iteration.
      • For example, R:\Tec\..\Eas\_Task\path\to\archive\bulkloader_YYYYMMDD\bulkloader\batch_002\address_base.csv
2. Extract sample base address from the output
   1. Pick a random record from the results. Gather the value in the address_base_id field.
   2. Construct a URL from this value like this: http://eas.sfgov.org/?address=NNNNNN
      • Where NNNNNN is the value from the address_base_id field.
   3. Make note of this URL for use in Step 7 when testing EAS after services are restored.
3. Artifacts
   1. address_base.csv - All the base records added to the EAS during the Bulk Loader operation.

...

date; df /data # 3rd of 3

•  

  Step 7.2 - Clean automation machine

1. Return to automation machine and remove shapefile from 'bulkload_shapefile' folder.
2. Logout of automation machine. 

•  

  Step 7.3 - On Failure Restore Database

1. If the Bulk Loader failed and corrupted any data then restore from the database backup.
   1. Follow these steps to restore from backup.

•  

  Step 7.

  3

  4 - Restore Services (Non-production Only)

1. TODO: Remove this step if nothing to populate with

•  

  Step 7.

  4

  5 - Restore Services (Production Only)

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
     language bash title Stop PostgreSQL linenumbers true
     sudo -u postgres -i /usr/pgsql-9.0/bin/pg_ctl -D /data/9.0/data start

     
     

2. SKIP SKIP 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

     
     

...