...

1. Establish your SSH tunnel to the target EAS database server.
2. Invoke pgAdmin and navigate to the target database, and then the output table (bulkloader.address_extract).
3. Right-click the output table and invoke the Query Tool.

4. Verify that the output table is not emptyRemove leftover records from the previous Bulk Loader iteration.

   Code Block
   language sql
   SELECT COUNT(*) AS record_count FROM TRUNCATE bulkloader.address_extract, bulkloader.addressblocks_extract;

   If the output table is not empty (record_count > 0), then truncate the output table and vacuum the database

   nearest;

   
   

5. Run garbage collection to free disk space held by truncated tuples.

   Code Block
   language sql
   TRUNCATEVACUUM FULL ANALYZE bulkloader.address_extract; VACUUM FULL ANALYZE bulkloader.blocks_nearest;

   
   

6. Verify that the output table is now empty (record_count = 0)Optional: count all records in the database for use in a before/after record count comparison.

   Code Block
   language sql
   SELECT COUNT(*) AS record_count FROM bulkloader.address_extract;

7. After your next bulk-load attempt, the output table will only contain results from that bulk-load attempt.
8. Repeat these steps as needed.

9. schemaname,relname,n_live_tup FROM pg_stat_user_tables ORDER BY schemaname,relname,n_live_tup;

   
   

Run the Bulk Loader

The Bulk Loader is run on the ETL server, and depending on the number of address records that are going to be processed, the Bulk Loader will complete its work at any time from a few minutes, to a few hours.  The following table contains some information for some typical runs.

...

After successful loading and evaluating large batches of addresses, clean up the database by truncating and vacuuming the the Bulk Loader output table (bulkloader.address_extract) and vacuuming the database.tables.

TRUNCATE bulkloader.address_extract, bulkloader.blocks_nearest;
VACUUM FULL ANALYZE bulkloader.address_extract;
VACUUM FULL ANALYZE bulkloader.blocks_nearest;