1
|
Installation:
|
2
|
Check out svn:
|
3
|
svn co https://code.nceas.ucsb.edu/code/projects/bien/trunk bien
|
4
|
cd bien/
|
5
|
Install: make install
|
6
|
**WARNING**: This will delete the public schema of your VegBIEN DB!
|
7
|
Uninstall: make uninstall
|
8
|
**WARNING**: This will delete your entire VegBIEN DB!
|
9
|
This includes all archived imports and staging tables.
|
10
|
|
11
|
Connecting to vegbiendev:
|
12
|
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
|
13
|
cd /home/bien # should happen automatically at login
|
14
|
|
15
|
Notes on system stability:
|
16
|
**WARNING**: when shutting down the VM, always first stop Postgres:
|
17
|
sudo service postgresql stop
|
18
|
this prevents the OS from SIGKILLing Postgres, which sometimes causes
|
19
|
database corruption
|
20
|
**WARNING**: system upgrades can break key parts of the full-database
|
21
|
import, causing errors such as disk space overruns. for this reason, it
|
22
|
is recommended to maintain a snapshot copy of the VM as it was at the
|
23
|
last successful import, for fallback use if a system upgrade breaks
|
24
|
anything. system upgrades on the snapshot VM should be disabled
|
25
|
completely, and because this will also disable security fixes, the
|
26
|
snapshot VM should be disconnected from the internet and all networking
|
27
|
interfaces. (this is an unfortunate consequence of modern OSes being
|
28
|
written in non-memory-safe languages such as C and C++.)
|
29
|
|
30
|
Notes on running programs:
|
31
|
**WARNING**: always start with a clean shell, to avoid spurious bugs. the
|
32
|
shell should not have changes to the env vars. (there have been bugs
|
33
|
that went away after closing and reopening the terminal window.) note
|
34
|
that running `exec bash` is not sufficient to *reset* the env vars.
|
35
|
|
36
|
Notes on editing files:
|
37
|
**WARNING**: shell scripts should always be read-only, so that editing them
|
38
|
while an import is in progress will not crash the import (see
|
39
|
http://vegpath.org/links/#**%20modifying%20a%20running%20shell%20script)
|
40
|
|
41
|
Single datasource import:
|
42
|
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
|
43
|
(Re)import and scrub: make inputs/<datasrc>/reimport_scrub by_col=1 &
|
44
|
(Re)import only: make inputs/<datasrc>/reimport by_col=1 &
|
45
|
Note that these commands also work if the datasource is not yet imported
|
46
|
Remake analytical DB: see Full database import > To remake analytical DB
|
47
|
|
48
|
Full database import:
|
49
|
**WARNING**: You must perform *every single* step listed below, to avoid
|
50
|
breaking column-based import
|
51
|
**WARNING**: always start with a clean shell, as described above under
|
52
|
"Notes on running programs"
|
53
|
**IMPORTANT**: the beginning of the import should be scheduled at a time
|
54
|
when the DB will not be needed for other uses. this is necessary because
|
55
|
vegbiendev will be slow for the first few hours of the import, due to
|
56
|
the import using all the available cores.
|
57
|
do steps under Maintenance > "to synchronize vegbiendev, jupiter, and
|
58
|
your local machine"
|
59
|
On local machine:
|
60
|
make inputs/upload
|
61
|
make inputs/upload live=1
|
62
|
make test by_col=1 # runtime: 1 h ("53m7.383s") @starscream
|
63
|
if you encounter errors, they are most likely related to the
|
64
|
PostgreSQL error parsing in /lib/sql.py parse_exception()
|
65
|
See note under Testing below
|
66
|
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
|
67
|
Ensure there are no local modifications: svn st
|
68
|
up
|
69
|
make inputs/download
|
70
|
make inputs/download live=1
|
71
|
For each newly-uploaded datasource above: make inputs/<datasrc>/reinstall
|
72
|
Update the auxiliary schemas: make schemas/reinstall
|
73
|
**WARNING**: requires sudo access!
|
74
|
The public schema will be installed separately by the import process
|
75
|
Delete imports before the last so they won't bloat the full DB backup:
|
76
|
make backups/vegbien.<version>.backup/remove
|
77
|
To keep a previous import other than the public schema:
|
78
|
export dump_opts='--exclude-schema=public --exclude-schema=<version>'
|
79
|
# env var will be inherited by `screen` shell
|
80
|
restart Postgres to free up any disk space used by temp tables from the last
|
81
|
import (this is apparently not automatically reclaimed):
|
82
|
make postgres_restart
|
83
|
Make sure there is at least 1 TB of disk space on /: df -h
|
84
|
although the import schema itself is only 315 GB, Postgres uses
|
85
|
significant temporary space at the beginning of the import.
|
86
|
the total disk usage oscillates between 1.2 TB and the entire disk
|
87
|
for the first day (for import started @12:55:09, high-water marks of
|
88
|
1.7 TB @14:00:25, 1.8 TB @15:38:32; then next day w/ 2 datasources
|
89
|
running: entire disk for 4 min @05:35:44, 1.8 TB @11:15:05).
|
90
|
To free up space, remove backups that have been archived on jupiter:
|
91
|
List backups/ to view older backups
|
92
|
Check their MD5 sums using the steps under On jupiter below
|
93
|
Remove these backups
|
94
|
for full import:
|
95
|
screen
|
96
|
Press ENTER
|
97
|
$0 # nested shell to prevent errexit from closing the window
|
98
|
the following must happen within screen to avoid affecting the outer shell:
|
99
|
unset TMOUT # TMOUT causes shell to exit even with background processes
|
100
|
set -o ignoreeof # prevent Ctrl+D from exiting shell to keep attached jobs
|
101
|
on local machine:
|
102
|
unset n # clear any limit set in .profile (unless desired)
|
103
|
unset log # allow logging output to go to log files
|
104
|
unset version # clear any version from last import, etc.
|
105
|
if no commits have been made since the last import (eg. if retrying an
|
106
|
import), set a custom version that differs from the auto-assigned one
|
107
|
(would otherwise cause a collision with the last import):
|
108
|
svn info
|
109
|
extract the svn revision after "Revision:"
|
110
|
export version=r[revision]_2 # +suffix to distinguish from last import
|
111
|
# env var will be inherited by `screen` shell
|
112
|
to import just a subset of the datasources:
|
113
|
declare -ax inputs; inputs=(inputs/{src,...}/) # no () in declare on Mac
|
114
|
# array vars *not* inherited by `screen` shell
|
115
|
export version=custom_import_name
|
116
|
Start column-based import: . bin/import_all
|
117
|
To use row-based import: . bin/import_all by_col=
|
118
|
To stop all running imports: . bin/stop_imports
|
119
|
**WARNING**: Do NOT run import_all in the background, or the jobs it
|
120
|
creates won't be owned by your shell.
|
121
|
Note that import_all will take up to an hour to import the NCBI backbone
|
122
|
and other metadata before returning control to the shell.
|
123
|
To view progress:
|
124
|
tail inputs/{.,}??*/*/logs/$version.log.sql
|
125
|
note: at the beginning of the import, the system may send out CPU load
|
126
|
warning e-mails. these can safely be ignored. (they happen because the
|
127
|
parallel imports use all the available cores.)
|
128
|
for test import, turn off DB backup (also turns off analytical DB creation):
|
129
|
kill % # cancel after_import()
|
130
|
Wait (4 days) for the import to finish
|
131
|
**WARNING**: do *not* run backups/pg_snapshot while the import is running,
|
132
|
due to continuously-changing files
|
133
|
**WARNING**: do *not* run backups/pg_snapshot until the previous import has
|
134
|
been replaced, to avoid running into disk space limits
|
135
|
To recover from a closed terminal window: screen -r
|
136
|
To restart an aborted import for a specific table:
|
137
|
export version=<version>
|
138
|
(set -o errexit; make inputs/<datasrc>/<table>/import_scrub by_col=1 continue=1; make inputs/<datasrc>/publish) &
|
139
|
bin/after_import $! & # $! can also be obtained from `jobs -l`
|
140
|
Get $version: echo $version
|
141
|
Set $version in all vegbiendev terminals: export version=<version>
|
142
|
When there are no more running jobs, exit `screen`: exit # not Ctrl+D
|
143
|
upload logs: make inputs/upload live=1
|
144
|
On local machine: make inputs/download-logs live=1
|
145
|
check for disk space errors:
|
146
|
grep --files-with-matches -F 'No space left on device' inputs/{.,}??*/*/logs/$version.log.sql
|
147
|
if there are any matches:
|
148
|
manually reimport these datasources using the steps under
|
149
|
Single datasource import
|
150
|
bin/after_import &
|
151
|
wait for the import to finish
|
152
|
tail inputs/{.,}??*/*/logs/$version.log.sql
|
153
|
In the output, search for "Command exited with non-zero status"
|
154
|
For inputs that have this, fix the associated bug(s)
|
155
|
If many inputs have errors, discard the current (partial) import:
|
156
|
make schemas/$version/uninstall
|
157
|
Otherwise, continue
|
158
|
In PostgreSQL:
|
159
|
Go to wiki.vegpath.org/VegBIEN_contents
|
160
|
Get the # observations
|
161
|
Get the # datasources
|
162
|
Get the # datasources with observations
|
163
|
in the r# schema:
|
164
|
Check that analytical_stem contains [# observations] rows
|
165
|
Check that source contains [# datasources] rows up through XAL. If this
|
166
|
is not the case, manually check the entries in source against the
|
167
|
datasources list on the wiki page (some datasources may be near the
|
168
|
end depending on import order).
|
169
|
Check that provider_count contains [# datasources with observations]
|
170
|
rows with dataset="(total)" (at the top when the table is unsorted)
|
171
|
Check that TNRS ran successfully:
|
172
|
tail -100 inputs/.TNRS/tnrs/logs/tnrs.make.log.sql
|
173
|
If the log ends in an AssertionError
|
174
|
"assert sql.table_col_names(db, table) == header":
|
175
|
Figure out which TNRS CSV columns have changed
|
176
|
On local machine:
|
177
|
Make the changes in the DB's TNRS and public schemas
|
178
|
rm=1 inputs/.TNRS/schema.sql.run export_
|
179
|
make schemas/remake
|
180
|
inputs/test_taxonomic_names/test_scrub # re-run TNRS
|
181
|
rm=1 inputs/.TNRS/data.sql.run export_
|
182
|
Commit
|
183
|
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
|
184
|
If dropping a column, save the dependent views
|
185
|
Make the same changes in the live TNRS.tnrs table on vegbiendev
|
186
|
If dropping a column, recreate the dependent views
|
187
|
Restart the TNRS client: make scrub by_col=1 &
|
188
|
Publish the new import:
|
189
|
**WARNING**: Before proceeding, be sure you have done *every single*
|
190
|
verification step listed above. Otherwise, a previous valid import
|
191
|
could incorrectly be overwritten with a broken one.
|
192
|
make schemas/$version/publish # runtime: 1 min ("real 1m10.451s")
|
193
|
unset version
|
194
|
make backups/upload live=1
|
195
|
on local machine:
|
196
|
make backups/vegbien.$version.backup/download live=1
|
197
|
# download backup to local machine
|
198
|
ssh aaronmk@jupiter.nceas.ucsb.edu
|
199
|
cd /data/dev/aaronmk/bien/backups
|
200
|
For each newly-archived backup:
|
201
|
make -s <backup>.md5/test
|
202
|
Check that "OK" is printed next to the filename
|
203
|
If desired, record the import times in inputs/import.stats.xls:
|
204
|
On local machine:
|
205
|
Open inputs/import.stats.xls
|
206
|
If the rightmost import is within 5 columns of column IV:
|
207
|
Copy the current tab to <leftmost-date>~<rightmost-date>
|
208
|
Remove the previous imports from the current tab because they are
|
209
|
now in the copied tab instead
|
210
|
Insert a copy of the leftmost "By column" column group before it
|
211
|
export version=<version>
|
212
|
bin/import_date inputs/{.,}??*/*/logs/$version.log.sql
|
213
|
Update the import date in the upper-right corner
|
214
|
bin/import_times inputs/{.,}??*/*/logs/$version.log.sql
|
215
|
Paste the output over the # Rows/Time columns, making sure that the
|
216
|
row counts match up with the previous import's row counts
|
217
|
If the row counts do not match up, insert or reorder rows as needed
|
218
|
until they do. Get the datasource names from the log file footers:
|
219
|
tail inputs/{.,}??*/*/logs/$version.log.sql
|
220
|
Commit: svn ci -m 'inputs/import.stats.xls: updated import times'
|
221
|
Running individual steps separately:
|
222
|
To run TNRS:
|
223
|
To use an import other than public: export version=<version>
|
224
|
to rescrub all names:
|
225
|
make inputs/.TNRS/reinstall
|
226
|
re-create public-schema views that were cascadingly deleted
|
227
|
make scrub &
|
228
|
To view progress:
|
229
|
tail -100 inputs/.TNRS/tnrs/logs/tnrs.make.log.sql
|
230
|
To re-run geoscrubbing:
|
231
|
$ screen
|
232
|
# press Enter
|
233
|
# to use an import other than public: $ export version=<version>
|
234
|
$ bin/psql_verbose_vegbien <<<'SELECT geoscrub_input_view_modify();' &
|
235
|
# wait until done
|
236
|
$ rm=1 exports/geoscrub_input.csv.run # runtime: 30 s
|
237
|
$ rm=1 inputs/.geoscrub/geoscrub_output/geoscrub.csv.run &
|
238
|
# runtime: 2.5 h
|
239
|
# wait until done
|
240
|
$ rm=1 inputs/.geoscrub/run &
|
241
|
# wait until done
|
242
|
# re-create public-schema views that were cascadingly deleted
|
243
|
# press Ctrl+D
|
244
|
# remake the analytical DB (below)
|
245
|
To remake analytical DB:
|
246
|
To use an import other than public: export version=<version>
|
247
|
bin/make_analytical_db & # runtime: 13 h ("12:43:57elapsed")
|
248
|
To view progress:
|
249
|
tail -150 inputs/analytical_db/logs/make_analytical_db.log.sql
|
250
|
To back up DB (staging tables and last import):
|
251
|
To use an import *other than public*: export version=<version>
|
252
|
make backups/TNRS.backup-remake &
|
253
|
dump_opts=--exclude-schema=public make backups/vegbien.$version.backup/test &
|
254
|
If after renaming to public, instead set dump_opts='' and replace
|
255
|
$version with the appropriate revision
|
256
|
make backups/upload live=1
|
257
|
|
258
|
Datasource setup:
|
259
|
On local machine:
|
260
|
Example steps for a datasource: wiki.vegpath.org/Import_process_for_Madidi
|
261
|
umask ug=rwx,o= # prevent files from becoming web-accessible
|
262
|
Add a new datasource: make inputs/<datasrc>/add
|
263
|
<datasrc> may not contain spaces, and should be abbreviated.
|
264
|
If the datasource is a herbarium, <datasrc> should be the herbarium code
|
265
|
as defined by the Index Herbariorum <http://sweetgum.nybg.org/ih/>
|
266
|
For a new-style datasource (one containing a ./run runscript):
|
267
|
"cp" -f inputs/.NCBI/{Makefile,run,table.run} inputs/<datasrc>/
|
268
|
For MySQL inputs (exports and live DB connections):
|
269
|
For .sql exports:
|
270
|
Place the original .sql file in _src/ (*not* in _MySQL/)
|
271
|
Follow the steps starting with Install the staging tables below.
|
272
|
This is for an initial sync to get the file onto vegbiendev.
|
273
|
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
|
274
|
Create a database for the MySQL export in phpMyAdmin
|
275
|
Give the bien user all database-specific privileges *except*
|
276
|
UPDATE, DELETE, ALTER, DROP. This prevents bugs in the
|
277
|
import scripts from accidentally deleting data.
|
278
|
bin/mysql_bien database <inputs/<datasrc>/_src/export.sql &
|
279
|
mkdir inputs/<datasrc>/_MySQL/
|
280
|
cp -p lib/MySQL.{data,schema}.sql.make inputs/<datasrc>/_MySQL/
|
281
|
Edit _MySQL/*.make for the DB connection
|
282
|
For a .sql export, use server=vegbiendev and --user=bien
|
283
|
Skip the Add input data for each table section
|
284
|
For MS Access databases:
|
285
|
Place the .mdb or .accdb file in _src/
|
286
|
Download and install Access To PostgreSQL from
|
287
|
http://www.bullzip.com/download.php
|
288
|
Use Access To PostgreSQL to export the database:
|
289
|
Export just the tables/indexes to inputs/<datasrc>/<file>.schema.sql
|
290
|
Export just the data to inputs/<datasrc>/<file>.data.sql
|
291
|
In <file>.schema.sql, make the following changes:
|
292
|
Replace text "BOOLEAN" with "/*BOOLEAN*/INTEGER"
|
293
|
Replace text "DOUBLE PRECISION NULL" with "DOUBLE PRECISION"
|
294
|
Skip the Add input data for each table section
|
295
|
Add input data for each table present in the datasource:
|
296
|
For .sql exports, you must use the name of the table in the DB export
|
297
|
For CSV files, you can use any name. It's recommended to use a table
|
298
|
name from <https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/VegCSV#Suggested-table-names>
|
299
|
Note that if this table will be joined together with another table, its
|
300
|
name must end in ".src"
|
301
|
make inputs/<datasrc>/<table>/add
|
302
|
Important: DO NOT just create an empty directory named <table>!
|
303
|
This command also creates necessary subdirs, such as logs/.
|
304
|
If the table is in a .sql export: make inputs/<datasrc>/<table>/install
|
305
|
Otherwise, place the CSV(s) for the table in
|
306
|
inputs/<datasrc>/<table>/ OR place a query joining other tables
|
307
|
together in inputs/<datasrc>/<table>/create.sql
|
308
|
Important: When exporting relational databases to CSVs, you MUST ensure
|
309
|
that embedded quotes are escaped by doubling them, *not* by
|
310
|
preceding them with a "\" as is the default in phpMyAdmin
|
311
|
If there are multiple part files for a table, and the header is repeated
|
312
|
in each part, make sure each header is EXACTLY the same.
|
313
|
(If the headers are not the same, the CSV concatenation script
|
314
|
assumes the part files don't have individual headers and treats the
|
315
|
subsequent headers as data rows.)
|
316
|
Add <table> to inputs/<datasrc>/import_order.txt before other tables
|
317
|
that depend on it
|
318
|
For a new-style datasource:
|
319
|
"cp" -f inputs/.NCBI/nodes/run inputs/<datasrc>/<table>/
|
320
|
inputs/<datasrc>/<table>/run
|
321
|
Install the staging tables:
|
322
|
make inputs/<datasrc>/reinstall quiet=1 &
|
323
|
For a MySQL .sql export:
|
324
|
At prompt "[you]@vegbiendev's password:", enter your password
|
325
|
At prompt "Enter password:", enter the value in config/bien_password
|
326
|
To view progress: tail -f inputs/<datasrc>/<table>/logs/install.log.sql
|
327
|
View the logs: tail -n +1 inputs/<datasrc>/*/logs/install.log.sql
|
328
|
tail provides a header line with the filename
|
329
|
+1 starts at the first line, to show the whole file
|
330
|
For every file with an error 'column "..." specified more than once':
|
331
|
Add a header override file "+header.<ext>" in <table>/:
|
332
|
Note: The leading "+" should sort it before the flat files.
|
333
|
"_" unfortunately sorts *after* capital letters in ASCII.
|
334
|
Create a text file containing the header line of the flat files
|
335
|
Add an ! at the beginning of the line
|
336
|
This signals cat_csv that this is a header override.
|
337
|
For empty names, use their 0-based column # (by convention)
|
338
|
For duplicate names, add a distinguishing suffix
|
339
|
For long names that collided, rename them to <= 63 chars long
|
340
|
Do NOT make readability changes in this step; that is what the
|
341
|
map spreadsheets (below) are for.
|
342
|
Save
|
343
|
If you made any changes, re-run the install command above
|
344
|
Auto-create the map spreadsheets: make inputs/<datasrc>/
|
345
|
Map each table's columns:
|
346
|
In each <table>/ subdir, for each "via map" map.csv:
|
347
|
Open the map in a spreadsheet editor
|
348
|
Open the "core map" /mappings/Veg+-VegBIEN.csv
|
349
|
In each row of the via map, set the right column to a value from the
|
350
|
left column of the core map
|
351
|
Save
|
352
|
Regenerate the derived maps: make inputs/<datasrc>/
|
353
|
Accept the test cases:
|
354
|
For a new-style datasource:
|
355
|
inputs/<datasrc>/run
|
356
|
svn di inputs/<datasrc>/*/test.xml.ref
|
357
|
If you get errors, follow the steps for old-style datasources below
|
358
|
For an old-style datasource:
|
359
|
make inputs/<datasrc>/test
|
360
|
When prompted to "Accept new test output", enter y and press ENTER
|
361
|
If you instead get errors, do one of the following for each one:
|
362
|
- If the error was due to a bug, fix it
|
363
|
- Add a SQL function that filters or transforms the invalid data
|
364
|
- Make an empty mapping for the columns that produced the error.
|
365
|
Put something in the Comments column of the map spreadsheet to
|
366
|
prevent the automatic mapper from auto-removing the mapping.
|
367
|
When accepting tests, it's helpful to use WinMerge
|
368
|
(see WinMerge setup below for configuration)
|
369
|
make inputs/<datasrc>/test by_col=1
|
370
|
If you get errors this time, this always indicates a bug, usually in
|
371
|
the VegBIEN unique constraints or column-based import itself
|
372
|
Add newly-created files: make inputs/<datasrc>/add
|
373
|
Commit: svn ci -m "Added inputs/<datasrc>/" inputs/<datasrc>/
|
374
|
Update vegbiendev:
|
375
|
ssh aaronmk@jupiter.nceas.ucsb.edu
|
376
|
up
|
377
|
On local machine:
|
378
|
./fix_perms
|
379
|
make inputs/upload
|
380
|
make inputs/upload live=1
|
381
|
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
|
382
|
up
|
383
|
make inputs/download
|
384
|
make inputs/download live=1
|
385
|
Follow the steps under Install the staging tables above
|
386
|
|
387
|
Maintenance:
|
388
|
on a live machine, you should put the following in your .profile:
|
389
|
--
|
390
|
# make svn files web-accessible. this does not affect unversioned files, because
|
391
|
# these get the right permissions on the local machine instead.
|
392
|
umask ug=rwx,o=rx
|
393
|
|
394
|
unset TMOUT # TMOUT causes screen to exit even with background processes
|
395
|
--
|
396
|
if http://vegbiendev.nceas.ucsb.edu/phppgadmin/ goes down:
|
397
|
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
|
398
|
make phppgadmin-Linux
|
399
|
regularly, re-run full-database import so that bugs in it don't pile up.
|
400
|
it needs to be kept in working order so that it works when it's needed.
|
401
|
to back up the vegbiendev databases:
|
402
|
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
|
403
|
back up MySQL: # usually few changes, so do this first
|
404
|
live= backups/mysql_snapshot
|
405
|
then review diff, and rerun without `live=`
|
406
|
l=1 overwrite=1 inplace=1 local_dir=/ remote_url="$USER@jupiter:/data/dev/aaronmk/Documents/BIEN/" subpath=/var/lib/mysql.bak/ sudo -E env PATH="$PATH" bin/sync_upload
|
407
|
on local machine:
|
408
|
l=1 swap=1 overwrite=1 inplace=1 local_dir=~ sync_remote_subdir= subpath=~/Documents/BIEN/var/lib/mysql.bak/ bin/sync_upload
|
409
|
back up Postgres:
|
410
|
live= backups/pg_snapshot
|
411
|
then review diff, and rerun without `live=`
|
412
|
to synchronize vegbiendev, jupiter, and your local machine:
|
413
|
**WARNING**: pay careful attention to all files that will be deleted or
|
414
|
overwritten!
|
415
|
install put if needed:
|
416
|
download https://uutils.googlecode.com/svn/trunk/bin/put to ~/bin/ and `chmod +x` it
|
417
|
when changes are made on vegbiendev:
|
418
|
avoid extraneous diffs when rsyncing:
|
419
|
on all machines:
|
420
|
up
|
421
|
./fix_perms
|
422
|
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
|
423
|
upload:
|
424
|
overwrite=1 bin/sync_upload --size-only
|
425
|
then review diff, and rerun with `l=1` prepended
|
426
|
on your machine:
|
427
|
download:
|
428
|
overwrite=1 swap=1 src=. dest='aaronmk@jupiter.nceas.ucsb.edu:~/bien' put --exclude=.svn web/BIEN3/TWiki
|
429
|
then review diff, and rerun with `l=1` prepended
|
430
|
swap=1 bin/sync_upload backups/TNRS.backup
|
431
|
then review diff, and rerun with `l=1` prepended
|
432
|
overwrite=1 swap=1 bin/sync_upload --size-only
|
433
|
then review diff, and rerun with `l=1` prepended
|
434
|
overwrite=1 sync_remote_url=~/Dropbox/svn/ bin/sync_upload --existing --size-only # just update mtimes/perms
|
435
|
then review diff, and rerun with `l=1` prepended
|
436
|
to back up e-mails:
|
437
|
on local machine:
|
438
|
/Applications/gmvault-v1.8.1-beta/bin/gmvault sync --multiple-db-owner --type quick aaronmk.nceas@gmail.com
|
439
|
/Applications/gmvault-v1.8.1-beta/bin/gmvault sync --multiple-db-owner --type quick aaronmk@nceas.ucsb.edu
|
440
|
open Thunderbird
|
441
|
click the All Mail folder for each account and wait for it to download the e-mails in it
|
442
|
to back up the version history:
|
443
|
# back up first on the local machine, because often only the svnsync
|
444
|
command gets run, and that way it will get backed up immediately to
|
445
|
Dropbox (and hourly to Time Machine), while vegbiendev only gets
|
446
|
backed up daily to tape
|
447
|
on local machine:
|
448
|
svnsync sync file://"$HOME"/Dropbox/docs/BIEN/svn_repo/ # initial runtime: 1.5 h ("08:21:38" - "06:45:26") @vegbiendev
|
449
|
(cd ~/Dropbox/docs/BIEN/git/; git svn fetch)
|
450
|
overwrite=1 src=~ dest='aaronmk@jupiter.nceas.ucsb.edu:/data/dev/aaronmk/' put Dropbox/docs/BIEN/svn_repo/ # runtime: 1 min ("1:05.08")
|
451
|
then review diff, and rerun with `l=1` prepended
|
452
|
overwrite=1 src=~ dest='aaronmk@jupiter.nceas.ucsb.edu:/data/dev/aaronmk/' put Dropbox/docs/BIEN/git/
|
453
|
then review diff, and rerun with `l=1` prepended
|
454
|
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
|
455
|
# use absolute path for vegbiendev commands because the Ubuntu 14.04
|
456
|
version of rsync doesn't expand ~ properly
|
457
|
overwrite=1 swap=1 src=~ dest='aaronmk@jupiter.nceas.ucsb.edu:/data/dev/aaronmk/' put Dropbox/docs/BIEN/svn_repo/ # runtime: 30 s ("36.19")
|
458
|
then review diff, and rerun with `l=1` prepended
|
459
|
overwrite=1 swap=1 src=~ dest='aaronmk@jupiter.nceas.ucsb.edu:/data/dev/aaronmk/' put Dropbox/docs/BIEN/git/
|
460
|
then review diff, and rerun with `l=1` prepended
|
461
|
to back up the local machine's settings:
|
462
|
do step when changes are made on vegbiendev > on your machine, download
|
463
|
ssh aaronmk@jupiter.nceas.ucsb.edu
|
464
|
(cd ~/Dropbox/svn/; up)
|
465
|
on your machine:
|
466
|
rm ~/'Library/Thunderbird/Profiles/9oo8rcyn.default/ImapMail/imap.googlemail.com/[Gmail].sbd/Spam'
|
467
|
# remove the downloaded Spam folder, because spam e-mails often contain viruses that would trigger clamscan
|
468
|
overwrite=1 del= sync_local_dir=~/Dropbox/svn/ sync_remote_subdir=Dropbox/svn/ bin/sync_upload --size-only # just update mtimes
|
469
|
then review diff, and rerun with `l=1` prepended
|
470
|
overwrite=1 inplace=1 sync_local_dir=~ sync_remote_subdir= bin/sync_upload ~/"VirtualBox VMs/**" # need inplace=1 because they are very large files
|
471
|
then review diff, and rerun with `l=1` prepended
|
472
|
overwrite=1 sync_local_dir=~ sync_remote_subdir= bin/sync_upload --exclude="/Library/Saved Application State" --exclude="/Library/Thunderbird/Profiles/9oo8rcyn.default/global-messages-db.sqlite" --exclude="/.Trash" --exclude="/bin" --exclude="/bin/pg_ctl" --exclude="/bin/unzip" --exclude="/Dropbox/home" --exclude="/.profile" --exclude="/.shrc" --exclude="/.bashrc" --exclude="/software/**/.svn"
|
473
|
then review diff, and rerun with `l=1` prepended
|
474
|
stop Dropbox: system tray > Dropbox icon > gear icon > Quit Dropbox
|
475
|
this prevents Dropbox from trying to capture filesystem
|
476
|
events while syncing
|
477
|
overwrite=1 sync_local_dir=~ sync_remote_url=~/Dropbox/home bin/sync_upload --exclude="/Library/Saved Application State" --exclude="/Library/Thunderbird/Profiles/9oo8rcyn.default/global-messages-db.sqlite" --exclude="/.Trash" --exclude="/.dropbox" --exclude="/Documents/BIEN" --exclude="/Dropbox" --exclude="/software" --exclude="/VirtualBox VMs/**.sav" --exclude="/VirtualBox VMs/**.vdi" --exclude="/VirtualBox VMs/**.vmdk"
|
478
|
then review diff, and rerun with `l=1` prepended
|
479
|
start Dropbox: /Applications > double-click Dropbox.app
|
480
|
to backup files not in Time Machine:
|
481
|
On local machine:
|
482
|
overwrite=1 src=/ dest=/Volumes/Time\ Machine\ Backups/ sudo -E put usr/local/var/postgres
|
483
|
then review diff, and rerun with `l=1` prepended
|
484
|
launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist # stop the PostgreSQL server
|
485
|
overwrite=1 src=/ dest=/Volumes/Time\ Machine\ Backups/ sudo -E put usr/local/var/postgres
|
486
|
then review diff, and rerun with `l=1` prepended
|
487
|
launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist # start the PostgreSQL server
|
488
|
to back up the local machine's hard drive:
|
489
|
turn on and connect the 2 TB external hard drive
|
490
|
screen
|
491
|
l=1 overwrite=1 src=/ dest=/Volumes/Macintosh\ HD\ backup/ sudo -E put
|
492
|
# runtime: 10 min (~600); initial runtime: 4 h ("2422.84"+"12379.91")
|
493
|
to restore from Time Machine:
|
494
|
# restart holding Alt
|
495
|
# select Time Machine Backups
|
496
|
# restore the last Time Machine backup to Macintosh HD
|
497
|
# restart holding Alt
|
498
|
# select Macintosh HD
|
499
|
$ screen
|
500
|
$ l=1 swap=1 src=/ dest=/Volumes/Time\ Machine\ Backups/ sudo -E put usr/local/var/postgres # runtime: 1 h ("4020.61")
|
501
|
$ make postgres_restart
|
502
|
VegCore data dictionary:
|
503
|
Regularly, or whenever the VegCore data dictionary page
|
504
|
(https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/VegCore)
|
505
|
is changed, regenerate mappings/VegCore.csv:
|
506
|
On local machine:
|
507
|
make mappings/VegCore.htm-remake; make mappings/
|
508
|
apply new data dict mappings to datasource mappings/staging tables:
|
509
|
inputs/run postprocess # runtime: see inputs/run
|
510
|
time yes|make inputs/{NVS,SALVIAS,TEAM}/test # old-style import; runtime: 1 min ("0m59.692s") @starscream
|
511
|
svn di mappings/VegCore.tables.redmine
|
512
|
If there are changes, update the data dictionary's Tables section
|
513
|
When moving terms, check that no terms were lost: svn di
|
514
|
svn ci -m 'mappings/VegCore.htm: regenerated from wiki'
|
515
|
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
|
516
|
perform the steps under "apply new data dict mappings to
|
517
|
datasource mappings/staging tables" above
|
518
|
Important: Whenever you install a system update that affects PostgreSQL or
|
519
|
any of its dependencies, such as libc, you should restart the PostgreSQL
|
520
|
server. Otherwise, you may get strange errors like "the database system
|
521
|
is in recovery mode" which go away upon reimport, or you may not be able
|
522
|
to access the database as the postgres superuser. This applies to both
|
523
|
Linux and Mac OS X.
|
524
|
|
525
|
Backups:
|
526
|
Archived imports:
|
527
|
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
|
528
|
Back up: make backups/<version>.backup &
|
529
|
Note: To back up the last import, you must archive it first:
|
530
|
make schemas/rotate
|
531
|
Test: make -s backups/<version>.backup/test &
|
532
|
Restore: make backups/<version>.backup/restore &
|
533
|
Remove: make backups/<version>.backup/remove
|
534
|
Download: make backups/<version>.backup/download
|
535
|
TNRS cache:
|
536
|
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
|
537
|
Back up: make backups/TNRS.backup-remake &
|
538
|
runtime: 3 min ("real 2m48.859s")
|
539
|
Restore:
|
540
|
yes|make inputs/.TNRS/uninstall
|
541
|
make backups/TNRS.backup/restore &
|
542
|
runtime: 5.5 min ("real 5m35.829s")
|
543
|
yes|make schemas/public/reinstall
|
544
|
Must come after TNRS restore to recreate tnrs_input_name view
|
545
|
Full DB:
|
546
|
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
|
547
|
Back up: make backups/vegbien.<version>.backup &
|
548
|
Test: make -s backups/vegbien.<version>.backup/test &
|
549
|
Restore: make backups/vegbien.<version>.backup/restore &
|
550
|
Download: make backups/vegbien.<version>.backup/download
|
551
|
Import logs:
|
552
|
On local machine:
|
553
|
Download: make inputs/download-logs live=1
|
554
|
|
555
|
Datasource refreshing:
|
556
|
VegBank:
|
557
|
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
|
558
|
make inputs/VegBank/vegbank.sql-remake
|
559
|
make inputs/VegBank/reinstall quiet=1 &
|
560
|
|
561
|
Schema changes:
|
562
|
On local machine:
|
563
|
When changing the analytical views, run sync_analytical_..._to_view()
|
564
|
to update the corresponding table
|
565
|
Remember to update the following files with any renamings:
|
566
|
schemas/filter_ERD.csv
|
567
|
mappings/VegCore-VegBIEN.csv
|
568
|
mappings/verify.*.sql
|
569
|
Regenerate schema from installed DB: make schemas/remake
|
570
|
Reinstall DB from schema: make schemas/public/reinstall schemas/reinstall
|
571
|
**WARNING**: This will delete the public schema of your VegBIEN DB!
|
572
|
If needed, reinstall staging tables:
|
573
|
On local machine:
|
574
|
sudo -E -u postgres psql <<<'ALTER DATABASE vegbien RENAME TO vegbien_prev'
|
575
|
make db
|
576
|
. bin/reinstall_all
|
577
|
Fix any bugs and retry until no errors
|
578
|
make schemas/public/install
|
579
|
This must be run *after* the datasources are installed, because
|
580
|
views in public depend on some of the datasources
|
581
|
sudo -E -u postgres psql <<<'DROP DATABASE vegbien_prev'
|
582
|
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
|
583
|
repeat the above steps
|
584
|
**WARNING**: Do not run this until reinstall_all runs successfully
|
585
|
on the local machine, or the live DB may be unrestorable!
|
586
|
update mappings and staging table column names:
|
587
|
on local machine:
|
588
|
inputs/run postprocess # runtime: see inputs/run
|
589
|
time yes|make inputs/{NVS,SALVIAS,TEAM}/test # old-style import; runtime: 1 min ("0m59.692s") @starscream
|
590
|
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
|
591
|
manually apply schema changes to the live public schema
|
592
|
do steps under "on local machine" above
|
593
|
Sync ERD with vegbien.sql schema:
|
594
|
Run make schemas/vegbien.my.sql
|
595
|
Open schemas/vegbien.ERD.mwb in MySQLWorkbench
|
596
|
Go to File > Export > Synchronize With SQL CREATE Script...
|
597
|
For Input File, select schemas/vegbien.my.sql
|
598
|
Click Continue
|
599
|
In the changes list, select each table with an arrow next to it
|
600
|
Click Update Model
|
601
|
Click Continue
|
602
|
Note: The generated SQL script will be empty because we are syncing in
|
603
|
the opposite direction
|
604
|
Click Execute
|
605
|
Reposition any lines that have been reset
|
606
|
Add any new tables by dragging them from the Catalog in the left sidebar
|
607
|
to the diagram
|
608
|
Remove any deleted tables by right-clicking the table's diagram element,
|
609
|
selecting Delete '<table name>', and clicking Delete
|
610
|
Save
|
611
|
If desired, update the graphical ERD exports (see below)
|
612
|
Update graphical ERD exports:
|
613
|
Go to File > Export > Export as PNG...
|
614
|
Select schemas/vegbien.ERD.png and click Save
|
615
|
Go to File > Export > Export as SVG...
|
616
|
Select schemas/vegbien.ERD.svg and click Save
|
617
|
Go to File > Export > Export as Single Page PDF...
|
618
|
Select schemas/vegbien.ERD.1_pg.pdf and click Save
|
619
|
Go to File > Print...
|
620
|
In the lower left corner, click PDF > Save as PDF...
|
621
|
Set the Title and Author to ""
|
622
|
Select schemas/vegbien.ERD.pdf and click Save
|
623
|
Commit: svn ci -m "schemas/vegbien.ERD.mwb: Regenerated exports"
|
624
|
Refactoring tips:
|
625
|
To rename a table:
|
626
|
In vegbien.sql, do the following:
|
627
|
Replace regexp (?<=_|\b)<old>(?=_|\b) with <new>
|
628
|
This is necessary because the table name is *everywhere*
|
629
|
Search for <new>
|
630
|
Manually change back any replacements inside comments
|
631
|
To rename a column:
|
632
|
Rename the column: ALTER TABLE <table> RENAME <old> TO <new>;
|
633
|
Recreate any foreign key for the column, removing CONSTRAINT <name>
|
634
|
This resets the foreign key name using the new column name
|
635
|
Creating a poster of the ERD:
|
636
|
Determine the poster size:
|
637
|
Measure the line height (from the bottom of one line to the bottom
|
638
|
of another): 16.3cm/24 lines = 0.679cm
|
639
|
Measure the height of the ERD: 35.4cm*2 = 70.8cm
|
640
|
Zoom in as far as possible
|
641
|
Measure the height of a capital letter: 3.5mm
|
642
|
Measure the line height: 8.5mm
|
643
|
Calculate the text's fraction of the line height: 3.5mm/8.5mm = 0.41
|
644
|
Calculate the text height: 0.679cm*0.41 = 0.28cm
|
645
|
Calculate the text height's fraction of the ERD height:
|
646
|
0.28cm/70.8cm = 0.0040
|
647
|
Measure the text height on the *VegBank* ERD poster: 5.5mm = 0.55cm
|
648
|
Calculate the VegBIEN poster height to make the text the same size:
|
649
|
0.55cm/0.0040 = 137.5cm H; *1in/2.54cm = 54.1in H
|
650
|
The ERD aspect ratio is 11 in W x (2*8.5in H) = 11x17 portrait
|
651
|
Calculate the VegBIEN poster width: 54.1in H*11W/17H = 35.0in W
|
652
|
The minimum VegBIEN poster size is 35x54in portrait
|
653
|
Determine the cost:
|
654
|
The FedEx Kinkos near NCEAS (1030 State St, Santa Barbara, CA 93101)
|
655
|
charges the following for posters:
|
656
|
base: $7.25/sq ft
|
657
|
lamination: $3/sq ft
|
658
|
mounting on a board: $8/sq ft
|
659
|
|
660
|
Testing:
|
661
|
On a development machine, you should put the following in your .profile:
|
662
|
umask ug=rwx,o= # prevent files from becoming web-accessible
|
663
|
export log= n=2
|
664
|
For development machine specs, see /planning/resources/dev_machine.specs/
|
665
|
On local machine:
|
666
|
Mapping process: make test
|
667
|
Including column-based import: make test by_col=1
|
668
|
If the row-based and column-based imports produce different inserted
|
669
|
row counts, this usually means that a table is underconstrained
|
670
|
(the unique indexes don't cover all possible rows).
|
671
|
This can occur if you didn't use COALESCE(field, null_value) around
|
672
|
a nullable field in a unique index. See sql_gen.null_sentinels for
|
673
|
the appropriate null value to use.
|
674
|
Map spreadsheet generation: make remake
|
675
|
Missing mappings: make missing_mappings
|
676
|
Everything (for most complete coverage): make test-all
|
677
|
|
678
|
Debugging:
|
679
|
"Binary chop" debugging:
|
680
|
(This is primarily useful for regressions that occurred in a previous
|
681
|
revision, which was committed without running all the tests)
|
682
|
up -r <rev>; make inputs/.TNRS/reinstall; make schemas/public/reinstall; make <failed-test>.xml
|
683
|
.htaccess:
|
684
|
mod_rewrite:
|
685
|
**IMPORTANT**: whenever you change the DirectorySlash setting for a
|
686
|
directory, you *must* clear your browser's cache to ensure that
|
687
|
a cached redirect is not used. this is because RewriteRule
|
688
|
redirects are (by default) temporary, but DirectorySlash
|
689
|
redirects are permanent.
|
690
|
for Firefox:
|
691
|
press Cmd+Shift+Delete
|
692
|
check only Cache
|
693
|
press Enter or click Clear Now
|
694
|
|
695
|
WinMerge setup:
|
696
|
In a Windows VM:
|
697
|
Install WinMerge from <http://winmerge.org/>
|
698
|
Open WinMerge
|
699
|
Go to Edit > Options and click Compare in the left sidebar
|
700
|
Enable "Moved block detection", as described at
|
701
|
<http://manual.winmerge.org/Configuration.html#d0e5892>.
|
702
|
Set Whitespace to Ignore change, as described at
|
703
|
<http://manual.winmerge.org/Configuration.html#d0e5758>.
|
704
|
|
705
|
Documentation:
|
706
|
To generate a Redmine-formatted list of steps for column-based import:
|
707
|
On local machine:
|
708
|
make schemas/public/reinstall
|
709
|
make inputs/ACAD/Specimen/logs/steps.by_col.log.sql
|
710
|
To import and scrub just the test taxonomic names:
|
711
|
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
|
712
|
inputs/test_taxonomic_names/test_scrub
|
713
|
|
714
|
General:
|
715
|
To see a program's description, read its top-of-file comment
|
716
|
To see a program's usage, run it without arguments
|
717
|
To remake a directory: make <dir>/remake
|
718
|
To remake a file: make <file>-remake
|