Restoring vcdb3

Project

Restoring vcdb3
Project Information
Has title	Restoring vcdb3
Has owner	Ed Egan
Has start date
Has deadline date
Has project status	Complete
Copyright © 2019 edegan.com. All Rights Reserved.

See also

The VentureXpert Data build notes.

Restore the dbase

Run the following:

cd /bulk/mcnair/backups
su researcher
createdb vcdb3
pg_restore -Fc -d vcdb3 vcdb3_Fc_230818.dump
cd /bulk
mkdir vcdb3
cd vcdb3
psql vcdb3

Reload the last changes

1. make vcdb3 in E:\projects
2. copy C:\Users\ed\Dropbox\coauthoredprojects\NonMcNair\MatchingVCs\RevisedDbaseCode.sql to e:\projects\vcdb3
3. Run that code!

1.6m queries running in <7s index on 1.6m in <1s Big table (99m) used to take ~2mins, now takes 2:15. Might want to reoptimize config. Big reduction used to take 12mins. Now takes 55 secs! In later queries (not as big), allocated cpu 97-100% but mem only 3.2% Copy up to 3.6% mem usage. Max observed 4.3%. All of the code ran correctly, with the right counts, etc.

Consolidate code

Code was in:

E:\mcnair\Projects\VentureXpert Database\vcdb3\LoadingScripts

The following files were copied to E:\projects\vcdb3\OriginalSQL

• Adding GDP.sql
• Agglomeration.SQL - this is the agglomeration work
• CohortPortCoMatch.sql
• GeoLoad.sql
• LoadingScriptsV1.sql - this does the load!
• MatchingEntrepsV3.sql - this is the main build, after the load
• MiscTasks.SQL
• Population.sql
• PortcoMatchMaster.SQL
• RoundRanking.sql - this is the city ranking script
• RoundRankingStates.sql - this is the state ranking script

Note that all code, including ranking code was run before backup.