Project

General

Profile

1
Installation:
2
	Check out svn:
3
		sudo apt-get install subversion # does not come preinstalled on Ubuntu
4
		svn co https://code.nceas.ucsb.edu/code/projects/bien/trunk bien
5
	cd bien/
6
	Install:
7
		**WARNING**: This will delete the public schema of your VegBIEN DB!
8
		make install
9
		# at "[sudo] password for user", enter your password and press Enter
10
		# at "Modifying postgresql.conf and pg_hba.conf", type y and press Enter
11
		# at "kernel.shmmax [...] Press ENTER to continue":
12
			# open a new window
13
			# run what it says
14
			# press Ctrl-D
15
			# return to the previous window
16
			# press Enter
17
		# at "restart PostgreSQL manually ... Press ENTER to continue":
18
			# open a new window
19
			# run what it says
20
			# press Ctrl-D
21
			# return to the previous window
22
			# press Enter
23
		# at "This will delete the current public schema of your VegBIEN DB",
24
			type y and press Enter
25
	Uninstall: make uninstall
26
		**WARNING**: This will delete your entire VegBIEN DB!
27
		This includes all archived imports and staging tables.
28

    
29
Connecting to vegbiendev:
30
	ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
31
	cd /home/bien # should happen automatically at login
32

    
33
Single datasource refresh:
34
	ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
35
	# -> Maintenance > to back up the vegbiendev databases
36
	# place updated extract in inputs/ACAD/_src/
37
	# place extracted flat file(s) in the appropriate table subdirs 
38
	rm=1 inputs/<datasrc>/run # reload staging tables
39
	make inputs/<datasrc>/reimport_scrub by_col=1 &
40
		# this works whether or not datasource is already imported
41
	tail -150 inputs/<datasrc>/*/logs/public.log.sql # view progress
42
	# -> Full database import > To re-run geoscrubbing
43
	# -> Full database import > To remake analytical DB
44
	# -> Maintenance > to back up the vegbiendev databases
45

    
46
Notes on system stability:
47
	**WARNING**: when shutting down the VM, always first stop Postgres:
48
		sudo service postgresql stop
49
		this prevents the OS from SIGKILLing Postgres, which sometimes causes
50
		database corruption 
51

    
52
Notes on running programs:
53
	**WARNING**: always start with a clean shell, to avoid spurious bugs. the
54
		shell should not have changes to the env vars. (there have been bugs
55
		that went away after closing and reopening the terminal window.) note
56
		that running `exec bash` is not sufficient to *reset* the env vars.
57

    
58
Notes on editing files:
59
	**WARNING**: shell scripts should always be read-only, so that editing them
60
		while an import is in progress will not crash the import (see
61
		http://vegpath.org/links/#**%20modifying%20a%20running%20shell%20script)
62

    
63
Full database import:
64
	**WARNING**: You must perform *every single* step listed below, to avoid
65
		breaking column-based import
66
	**WARNING**: always start with a clean shell, as described above under
67
		"Notes on running programs"
68
	**IMPORTANT**: the beginning of the import should be scheduled at a time
69
		when the DB will not be needed for other uses. this is necessary because
70
		vegbiendev will be slow for the first few hours of the import, due to
71
		the import using all the available cores.
72
	do steps under Maintenance > "to synchronize vegbiendev, jupiter, and
73
		your local machine"
74
	On local machine:
75
		make inputs/upload
76
		make inputs/upload live=1
77
		make test by_col=1 # runtime: 1 h ("53m7.383s") @starscream
78
			if you encounter errors, they are most likely related to the
79
				PostgreSQL error parsing in /lib/sql.py parse_exception()
80
			See note under Testing below
81
	ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
82
	Ensure there are no local modifications: svn st
83
	up
84
	make inputs/download
85
	make inputs/download live=1
86
	For each newly-uploaded datasource above: make inputs/<datasrc>/reinstall
87
	Update the auxiliary schemas: make schemas/reinstall
88
		**WARNING**: requires sudo access!
89
		The public schema will be installed separately by the import process
90
	Delete imports before the last so they won't bloat the full DB backup:
91
		make backups/vegbien.<version>.backup/remove
92
		To keep a previous import other than the public schema:
93
			export dump_opts='--exclude-schema=public --exclude-schema=<version>'
94
			# env var will be inherited by `screen` shell
95
	restart Postgres to free up any disk space used by temp tables from the last
96
		import (this is apparently not automatically reclaimed):
97
		make postgres_restart
98
	Make sure there is at least 1 TB of disk space on /: df -h
99
		although the import schema itself is only 315 GB, Postgres uses
100
			significant temporary space at the beginning of the import.
101
			the total disk usage oscillates between 1.2 TB and the entire disk
102
			for the first day (for import started @12:55:09, high-water marks of
103
			1.7 TB @14:00:25, 1.8 TB @15:38:32; then next day w/ 2 datasources
104
			running: entire disk for 4 min @05:35:44, 1.8 TB @11:15:05).
105
		To free up space, remove backups that have been archived on jupiter:
106
			List backups/ to view older backups
107
			Check their MD5 sums using the steps under On jupiter below
108
			Remove these backups
109
	for full import:
110
		screen
111
		Press ENTER
112
	$0 # nested shell to prevent errexit from closing the window
113
	the following must happen within screen to avoid affecting the outer shell:
114
	unset TMOUT # TMOUT causes shell to exit even with background processes
115
	set -o ignoreeof # prevent Ctrl+D from exiting shell to keep attached jobs
116
	on local machine:
117
		unset n # clear any limit set in .profile (unless desired)
118
		unset log # allow logging output to go to log files
119
	unset version # clear any version from last import, etc.
120
	if no commits have been made since the last import (eg. if retrying an
121
		import), set a custom version that differs from the auto-assigned one
122
		(would otherwise cause a collision with the last import):
123
		svn info
124
		extract the svn revision after "Revision:"
125
		export version=r[revision]_2 # +suffix to distinguish from last import
126
			# env var will be inherited by `screen` shell
127
	to import just a subset of the datasources:
128
		declare -ax inputs; inputs=(inputs/{src,...}/) # no () in declare on Mac
129
			# array vars *not* inherited by `screen` shell
130
		export version=custom_import_name
131
	Start column-based import: . bin/import_all
132
		To use row-based import: . bin/import_all by_col=
133
		To stop all running imports: . bin/stop_imports
134
		**WARNING**: Do NOT run import_all in the background, or the jobs it
135
			creates won't be owned by your shell.
136
		Note that import_all will take up to an hour to import the NCBI backbone
137
			and other metadata before returning control to the shell.
138
		To view progress:
139
			tail inputs/{.,}??*/*/logs/$version.log.sql
140
	note: at the beginning of the import, the system may send out CPU load
141
		warning e-mails. these can safely be ignored. (they happen because the
142
		parallel imports use all the available cores.)
143
	for test import, turn off DB backup (also turns off analytical DB creation):
144
		kill % # cancel after_import()
145
	Wait (4 days) for the import to finish
146
	**WARNING**: do *not* run backups/pg_snapshot while the import is running,
147
		due to continuously-changing files
148
	**WARNING**: do *not* run backups/pg_snapshot until the previous import has
149
		been replaced, to avoid running into disk space limits
150
	To recover from a closed terminal window: screen -r
151
	To restart an aborted import for a specific table:
152
		export version=<version>
153
		(set -o errexit; make inputs/<datasrc>/<table>/import_scrub by_col=1 continue=1; make inputs/<datasrc>/publish) &
154
		bin/after_import $! & # $! can also be obtained from `jobs -l`
155
	Get $version: echo $version
156
	Set $version in all vegbiendev terminals: export version=<version>
157
	When there are no more running jobs, exit `screen`: exit # not Ctrl+D
158
	upload logs: make inputs/upload live=1
159
	On local machine: make inputs/download-logs live=1
160
	check for disk space errors:
161
		grep --files-with-matches -F 'No space left on device' inputs/{.,}??*/*/logs/$version.log.sql
162
		if there are any matches:
163
			manually reimport these datasources using the steps under
164
				Single datasource import
165
			bin/after_import &
166
			wait for the import to finish
167
	tail inputs/{.,}??*/*/logs/$version.log.sql
168
	In the output, search for "Command exited with non-zero status"
169
	For inputs that have this, fix the associated bug(s)
170
	If many inputs have errors, discard the current (partial) import:
171
		make schemas/$version/uninstall
172
	Otherwise, continue
173
	In PostgreSQL:
174
		Go to wiki.vegpath.org/VegBIEN_contents
175
		Get the # observations
176
		Get the # datasources
177
		Get the # datasources with observations
178
		in the r# schema:
179
		Check that analytical_stem contains [# observations] rows
180
		Check that source contains [# datasources] rows up through XAL. If this
181
			is not the case, manually check the entries in source against the
182
			datasources list on the wiki page (some datasources may be near the
183
			end depending on import order).
184
		Check that provider_count contains [# datasources with observations]
185
			rows with dataset="(total)" (at the top when the table is unsorted)
186
	Check that TNRS ran successfully:
187
		tail -100 inputs/.TNRS/tnrs/logs/tnrs.make.log.sql
188
		If the log ends in an AssertionError
189
			"assert sql.table_col_names(db, table) == header":
190
			Figure out which TNRS CSV columns have changed
191
			On local machine:
192
				Make the changes in the DB's TNRS and public schemas
193
				rm=1 inputs/.TNRS/schema.sql.run export_
194
				make schemas/remake
195
				inputs/test_taxonomic_names/test_scrub # re-run TNRS
196
				rm=1 inputs/.TNRS/data.sql.run export_
197
				Commit
198
			ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
199
				If dropping a column, save the dependent views
200
				Make the same changes in the live TNRS.tnrs table on vegbiendev
201
				If dropping a column, recreate the dependent views
202
				Restart the TNRS client: make scrub by_col=1 &
203
	Publish the new import:
204
		**WARNING**: Before proceeding, be sure you have done *every single*
205
			verification step listed above. Otherwise, a previous valid import
206
			could incorrectly be overwritten with a broken one.
207
		make schemas/$version/publish # runtime: 1 min ("real 1m10.451s")
208
	unset version
209
	make backups/upload live=1
210
	on local machine:
211
		make backups/vegbien.$version.backup/download live=1
212
			# download backup to local machine
213
	ssh aaronmk@jupiter.nceas.ucsb.edu
214
		cd /data/dev/aaronmk/bien/backups
215
		For each newly-archived backup:
216
			make -s <backup>.md5/test
217
			Check that "OK" is printed next to the filename
218
	If desired, record the import times in inputs/import.stats.xls:
219
		On local machine:
220
		Open inputs/import.stats.xls
221
		If the rightmost import is within 5 columns of column IV:
222
			Copy the current tab to <leftmost-date>~<rightmost-date>
223
			Remove the previous imports from the current tab because they are
224
				now in the copied tab instead
225
		Insert a copy of the leftmost "By column" column group before it
226
		export version=<version>
227
		bin/import_date inputs/{.,}??*/*/logs/$version.log.sql
228
		Update the import date in the upper-right corner
229
		bin/import_times inputs/{.,}??*/*/logs/$version.log.sql
230
		Paste the output over the # Rows/Time columns, making sure that the
231
			row counts match up with the previous import's row counts
232
		If the row counts do not match up, insert or reorder rows as needed
233
			until they do. Get the datasource names from the log file footers:
234
			tail inputs/{.,}??*/*/logs/$version.log.sql
235
		Commit: svn ci -m 'inputs/import.stats.xls: updated import times'
236
	Running individual steps separately:
237
	To run TNRS:
238
		To use an import other than public: export version=<version>
239
		to rescrub all names:
240
			make inputs/.TNRS/reinstall
241
			re-create public-schema views that were cascadingly deleted
242
		make scrub &
243
		To view progress:
244
			tail -100 inputs/.TNRS/tnrs/logs/tnrs.make.log.sql
245
	To re-run geoscrubbing:
246
		$ screen
247
		# press Enter
248
		# to use an import other than public: $ export version=<version>
249
		$ bin/psql_verbose_vegbien <<<'SELECT geoscrub_input_view_modify();' &
250
			# runtime: 8 min ("7:40.54") @r14089 @vegbiendev
251
		# wait until done
252
		$ rm=1 exports/geoscrub_input.csv.run
253
			# runtime: 25 s ("0m24.936s") @r14089 @vegbiendev
254
		$ rm=1 inputs/.geoscrub/geoscrub_output/geoscrub.csv.run &
255
			# runtime: 2.5 h
256
		# wait until done
257
		$ rm=1 inputs/.geoscrub/run &
258
			# runtime: 15 min ("16m34.052s") @r14089 @vegbiendev
259
		# wait until done
260
		# re-create public-schema views that were cascadingly deleted
261
		# press Ctrl+D
262
		# remake the analytical DB (below)
263
	To remake analytical DB:
264
		To use an import other than public: export version=<version>
265
		bin/make_analytical_db & # runtime: 13 h ("12:43:57elapsed")
266
		To view progress:
267
			tail -150 inputs/analytical_db/logs/make_analytical_db.log.sql
268
	To back up DB (staging tables and last import):
269
		To use an import *other than public*: export version=<version>
270
		make backups/TNRS.backup-remake &
271
		dump_opts=--exclude-schema=public make backups/vegbien.$version.backup/test &
272
			If after renaming to public, instead set dump_opts='' and replace
273
			$version with the appropriate revision
274
		make backups/upload live=1
275

    
276
Datasource setup:
277
	On local machine:
278
	Example steps for a datasource: wiki.vegpath.org/Import_process_for_Madidi
279
	umask ug=rwx,o= # prevent files from becoming web-accessible
280
	Add a new datasource: make inputs/<datasrc>/add
281
		<datasrc> may not contain spaces, and should be abbreviated.
282
		If the datasource is a herbarium, <datasrc> should be the herbarium code
283
			as defined by the Index Herbariorum <http://sweetgum.nybg.org/ih/>
284
	For a new-style datasource (one containing a ./run runscript):
285
		"cp" -f inputs/.NCBI/{Makefile,run,table.run} inputs/<datasrc>/
286
	For MySQL inputs (exports and live DB connections):
287
		For .sql exports:
288
			Place the original .sql file in _src/ (*not* in _MySQL/)
289
			Follow the steps starting with Install the staging tables below.
290
				This is for an initial sync to get the file onto vegbiendev.
291
			ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
292
				Create a database for the MySQL export in phpMyAdmin
293
				Give the bien user all database-specific privileges *except*
294
					UPDATE, DELETE, ALTER, DROP. This prevents bugs in the
295
					import scripts from accidentally deleting data.
296
				bin/mysql_bien database <inputs/<datasrc>/_src/export.sql &
297
		mkdir inputs/<datasrc>/_MySQL/
298
		cp -p lib/MySQL.{data,schema}.sql.make inputs/<datasrc>/_MySQL/
299
		Edit _MySQL/*.make for the DB connection
300
			For a .sql export, use server=vegbiendev and --user=bien
301
		Skip the Add input data for each table section
302
	For MS Access databases:
303
		Place the .mdb or .accdb file in _src/
304
		Download and install Bullzip's MS Access to PostgreSQL from
305
			http://bullzip.com/download.php > Access To PostgreSQL > Download
306
		Use Access To PostgreSQL to export the database:
307
			Export just the tables/indexes to inputs/<datasrc>/<file>.schema.sql
308
				using the settings in the associated .ini file where available
309
			Export just the data to inputs/<datasrc>/<file>.data.sql using the
310
				settings in the associated .ini file where available
311
		In <file>.schema.sql, make the following changes:
312
			Replace text "BOOLEAN" with "/*BOOLEAN*/INTEGER"
313
			Replace text "DOUBLE PRECISION NULL" with "DOUBLE PRECISION"
314
		Skip the Add input data for each table section
315
	Add input data for each table present in the datasource:
316
		For .sql exports, you must use the name of the table in the DB export
317
		For CSV files, you can use any name. It's recommended to use a table
318
			name from <https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/VegCSV#Suggested-table-names>
319
		Note that if this table will be joined together with another table, its
320
			name must end in ".src"
321
		make inputs/<datasrc>/<table>/add
322
			Important: DO NOT just create an empty directory named <table>!
323
				This command also creates necessary subdirs, such as logs/.
324
		If the table is in a .sql export: make inputs/<datasrc>/<table>/install
325
			Otherwise, place the CSV(s) for the table in
326
			inputs/<datasrc>/<table>/ OR place a query joining other tables
327
			together in inputs/<datasrc>/<table>/create.sql
328
		Important: When exporting relational databases to CSVs, you MUST ensure
329
			that embedded quotes are escaped by doubling them, *not* by
330
			preceding them with a "\" as is the default in phpMyAdmin
331
		If there are multiple part files for a table, and the header is repeated
332
			in each part, make sure each header is EXACTLY the same.
333
			(If the headers are not the same, the CSV concatenation script
334
			assumes the part files don't have individual headers and treats the
335
			subsequent headers as data rows.)
336
		Add <table> to inputs/<datasrc>/import_order.txt before other tables
337
			that depend on it
338
		For a new-style datasource:
339
			"cp" -f inputs/.NCBI/nodes/run inputs/<datasrc>/<table>/
340
			inputs/<datasrc>/<table>/run
341
	Install the staging tables:
342
		make inputs/<datasrc>/reinstall quiet=1 &
343
		For a MySQL .sql export:
344
			At prompt "[you]@vegbiendev's password:", enter your password
345
			At prompt "Enter password:", enter the value in config/bien_password
346
		To view progress: tail -f inputs/<datasrc>/<table>/logs/install.log.sql
347
		View the logs: tail -n +1 inputs/<datasrc>/*/logs/install.log.sql
348
			tail provides a header line with the filename
349
			+1 starts at the first line, to show the whole file
350
		For every file with an error 'column "..." specified more than once':
351
			Add a header override file "+header.<ext>" in <table>/:
352
				Note: The leading "+" should sort it before the flat files.
353
					"_" unfortunately sorts *after* capital letters in ASCII.
354
				Create a text file containing the header line of the flat files
355
				Add an ! at the beginning of the line
356
					This signals cat_csv that this is a header override.
357
				For empty names, use their 0-based column # (by convention)
358
				For duplicate names, add a distinguishing suffix
359
				For long names that collided, rename them to <= 63 chars long
360
				Do NOT make readability changes in this step; that is what the
361
					map spreadsheets (below) are for.
362
				Save
363
		If you made any changes, re-run the install command above
364
	Auto-create the map spreadsheets: make inputs/<datasrc>/
365
	Map each table's columns:
366
		In each <table>/ subdir, for each "via map" map.csv:
367
			Open the map in a spreadsheet editor
368
			Open the "core map" /mappings/Veg+-VegBIEN.csv
369
			In each row of the via map, set the right column to a value from the
370
				left column of the core map
371
			Save
372
		Regenerate the derived maps: make inputs/<datasrc>/
373
	Accept the test cases:
374
		For a new-style datasource:
375
			inputs/<datasrc>/run
376
			svn di inputs/<datasrc>/*/test.xml.ref
377
			If you get errors, follow the steps for old-style datasources below
378
		For an old-style datasource:
379
			make inputs/<datasrc>/test
380
			When prompted to "Accept new test output", enter y and press ENTER
381
			If you instead get errors, do one of the following for each one:
382
			-	If the error was due to a bug, fix it
383
			-	Add a SQL function that filters or transforms the invalid data
384
			-	Make an empty mapping for the columns that produced the error.
385
				Put something in the Comments column of the map spreadsheet to
386
				prevent the automatic mapper from auto-removing the mapping.
387
			When accepting tests, it's helpful to use WinMerge
388
				(see WinMerge setup below for configuration)
389
		make inputs/<datasrc>/test by_col=1
390
			If you get errors this time, this always indicates a bug, usually in
391
				the VegBIEN unique constraints or column-based import itself
392
	Add newly-created files: make inputs/<datasrc>/add
393
	Commit: svn ci -m "Added inputs/<datasrc>/" inputs/<datasrc>/
394
	Update vegbiendev:
395
		ssh aaronmk@jupiter.nceas.ucsb.edu
396
			up
397
		On local machine:
398
			./fix_perms
399
			make inputs/upload
400
			make inputs/upload live=1
401
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
402
			up
403
			make inputs/download
404
			make inputs/download live=1
405
			Follow the steps under Install the staging tables above
406

    
407
Maintenance:
408
	on a live machine, you should put the following in your .profile:
409
--
410
# make svn files web-accessible. this does not affect unversioned files, because
411
# these get the right permissions on the local machine instead.
412
umask ug=rwx,o=rx
413

    
414
unset TMOUT # TMOUT causes screen to exit even with background processes
415
--
416
	if http://vegbiendev.nceas.ucsb.edu/phppgadmin/ goes down:
417
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
418
			make phppgadmin-Linux
419
	regularly, re-run full-database import so that bugs in it don't pile up.
420
		it needs to be kept in working order so that it works when it's needed.
421
	to back up the vegbiendev databases:
422
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
423
		back up MySQL: # usually few changes, so do this first
424
			backups/mysql_snapshot
425
			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
426
			on local machine:
427
			l=1 swap=1 overwrite=1 inplace=1 local_dir=~ sync_remote_subdir=                          subpath=~/Documents/BIEN/var/lib/mysql.bak/                          bin/sync_upload
428
		back up Postgres:
429
			backups/pg_snapshot
430
	to synchronize vegbiendev, jupiter, and your local machine:
431
		**WARNING**: pay careful attention to all files that will be deleted or
432
			overwritten!
433
		install put if needed:
434
			download https://uutils.googlecode.com/svn/trunk/bin/put to ~/bin/ and `chmod +x` it
435
		when changes are made on vegbiendev:
436
			avoid extraneous diffs when rsyncing:
437
				on local machine:
438
					up; ./fix_perms
439
				ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
440
					up; ./fix_perms
441
				ssh aaronmk@jupiter.nceas.ucsb.edu
442
					up; ./fix_perms
443
			ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
444
				upload:
445
				overwrite=1 bin/sync_upload --size-only
446
					then review diff, and rerun with `l=1` prepended
447
			on your machine:
448
				download:
449
				overwrite=1 swap=1 src=. dest='aaronmk@jupiter.nceas.ucsb.edu:~/bien' put --exclude=.svn web/BIEN3/TWiki
450
					then review diff, and rerun with `l=1` prepended
451
				swap=1 bin/sync_upload backups/TNRS.backup
452
					then review diff, and rerun with `l=1` prepended
453
				overwrite=1 swap=1 bin/sync_upload --size-only
454
					then review diff, and rerun with `l=1` prepended
455
				overwrite=1 sync_remote_url=~/Dropbox/svn/ bin/sync_upload --existing --size-only # just update mtimes/perms
456
					then review diff, and rerun with `l=1` prepended
457
	to back up e-mails:
458
		on local machine:
459
		/Applications/gmvault-v1.8.1-beta/bin/gmvault sync --multiple-db-owner --type quick aaronmk.nceas@gmail.com
460
		open Thunderbird
461
		click the All Mail folder for each account and wait for it to download the e-mails in it
462
	to back up the version history:
463
		# back up first on the local machine, because often only the svnsync
464
			command gets run, and that way it will get backed up immediately to
465
			Dropbox (and hourly to Time Machine), while vegbiendev only gets
466
			backed up daily to tape
467
		on local machine:
468
		svnsync sync file://"$HOME"/Dropbox/docs/BIEN/svn_repo/ # initial runtime: 1.5 h ("08:21:38" - "06:45:26") @vegbiendev
469
		(cd ~/Dropbox/docs/BIEN/git/; git svn fetch)
470
		# use absolute path for vegbiendev commands because the Ubuntu 14.04
471
			version of rsync doesn't expand ~ properly
472
		overwrite=1        src=~ dest='aaronmk@jupiter.nceas.ucsb.edu:/data/dev/aaronmk/' put Dropbox/docs/BIEN/svn_repo/ # runtime: 1 min ("1:05.08")
473
			then review diff, and rerun with `l=1` prepended
474
		overwrite=1        src=~ dest='aaronmk@jupiter.nceas.ucsb.edu:/data/dev/aaronmk/' put Dropbox/docs/BIEN/git/
475
			then review diff, and rerun with `l=1` prepended
476
	to back up vegbiendev:
477
		do steps under Maintenance > "to synchronize vegbiendev, jupiter, and
478
			your local machine"
479
		on local machine:
480
			l=1 overwrite=1 inplace=1 src=root@vegbiendev.nceas.ucsb.edu:/ dest=~/Documents/BIEN/vegbiendev/ sudo -E put --exclude=/var/lib/mysql.bak --exclude=/var/lib/postgresql.bak --exclude='/var/lib/postgresql/9.3/main/*/' --exclude=/home/aaronmk/bien
481
			# enable --link-dest to work:
482
				chmod -R o+r ~/bien/.svn/; find ~/bien/.svn -type d -exec chmod o+rx {} \; # match perms
483
				l=1 overwrite=1 del= src='aaronmk@vegbiendev.nceas.ucsb.edu:~/bien/' dest=~/bien/ put --existing --size-only .svn/pristine/ # match times and perms
484
			l=1 overwrite=1 inplace=1 src=aaronmk@vegbiendev.nceas.ucsb.edu:/ dest=~/Documents/BIEN/vegbiendev/ sudo -E put --link-dest="$HOME"/Documents/BIEN/svn/ --no-owner --no-group home/aaronmk/bien/
485
				# --no-owner --no-group: needed to allow --link-dest to work
486
				# --link-dest: relative to dest, not currdir, so need abs path
487
	to back up the local machine's settings:
488
		do step when changes are made on vegbiendev > on your machine, download
489
		ssh aaronmk@jupiter.nceas.ucsb.edu
490
			(cd ~/Dropbox/svn/; up)
491
		on your machine:
492
			sudo find / -name .DS_Store -print -delete
493
			rm ~/'Library/Thunderbird/Profiles/9oo8rcyn.default/ImapMail/imap.googlemail.com/[Gmail].sbd/Spam'
494
				# remove the downloaded Spam folder, because spam e-mails often contain viruses that would trigger clamscan
495
			overwrite=1           sync_local_dir=~/Dropbox/svn/ sync_remote_subdir=Dropbox/svn/ bin/sync_upload --size-only # just update mtimes
496
				then review diff, and rerun with `l=1` prepended
497
			overwrite=1 inplace=1 sync_local_dir=~/             sync_remote_subdir=                          bin/sync_upload ~/"VirtualBox VMs/**" # need inplace=1 because they are very large files
498
				then review diff, and rerun with `l=1` prepended
499
			overwrite=1           sync_local_dir=~/             sync_remote_subdir= sudo -E env USER=aaronmk bin/sync_upload --exclude="/Library/Saved Application State/" --exclude="/.Trash/" --exclude="/bin/" --exclude="/bin/pg_ctl" --exclude="/bin/unzip" --exclude="/Dropbox/home/" --exclude="/.profile" --exclude="/.shrc" --exclude="/.bashrc" --exclude="/software/**/.svn/"
500
				# sudo -E: needed for Documents/BIEN/vegbiendev*/
501
				then review diff, and rerun with `l=1` prepended
502
			pause Dropbox: system tray > Dropbox icon > gear icon > Pause Syncing
503
				this prevents Dropbox from trying to capture filesystem
504
				events while syncing
505
			overwrite=1           sync_local_dir=~/             sync_remote_url=~/Dropbox/home/              bin/sync_upload --exclude="/Library/Saved Application State/" --exclude="/.Trash/" --exclude="/.dropbox/" --exclude="/Documents/BIEN/" --exclude="/Dropbox/" --exclude=/gmvault-db/ --exclude="/software/" --exclude="/VirtualBox VMs/**.sav" --exclude="/VirtualBox VMs/**.vdi" --exclude="/VirtualBox VMs/**.vmdk"
506
				then review diff, and rerun with `l=1` prepended
507
			resume Dropbox: system tray > Dropbox icon > gear icon > Resume Syncing
508
	to backup files not in Time Machine:
509
		**IMPORTANT**: need to use 2 TB external hard drive instead of Time
510
			Machine drive because Time Machine drive does not have
511
			~/Documents/BIEN/ in a location where it can be hardlinked against
512
		On local machine:
513
		on first run, create parent dirs:
514
			sudo mkdir -p '/Volumes/BIEN3.**SAVE**/Users/aaronmk/Documents/BIEN/'
515
			sudo mkdir -p '/Volumes/BIEN3.**SAVE**/usr/local/var/postgres/'
516
			l=1 src=/ dest='/Volumes/BIEN3.**SAVE**/' sudo -E put --existing
517
		l=1 overwrite=1 src=/ dest='/Volumes/BIEN3.**SAVE**/' sudo -E put --include='/vegbiendev**' --exclude='**' Users/aaronmk/Documents/BIEN/
518
			# this cannot be backed up by Time Machine because it dereferences hard links:
519
			#  `sudo find /Volumes/Time\ Machine\ Backups/Backups.backupdb/ ! -type d -links +1`
520
			#  returns no files when there is a single timestamped backup, but
521
			#  `sudo find / ! -type d -links +1` does
522
		l=1 overwrite=1 src=/ dest='/Volumes/BIEN3.**SAVE**/' sudo -E put usr/local/var/postgres/
523
			# this cannot be backed up by Time Machine because it prevents the backup process from ending
524
		launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist # stop the PostgreSQL server
525
		l=1 overwrite=1 src=/ dest='/Volumes/BIEN3.**SAVE**/' sudo -E put usr/local/var/postgres/
526
		launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist # start the PostgreSQL server
527
	to back up the local machine's hard drive:
528
		turn on and connect the 2 TB external hard drive
529
		screen
530
		# --exclude='/\**': exclude *-files indicating the (differing) retention
531
		#  statuses of the partitions involved
532
		pause Dropbox: system tray > Dropbox icon > gear icon > Pause Syncing
533
			otherwise, the backup of ~/.dropbox will be corrupted
534
		launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist # stop the PostgreSQL server
535
		l=1 overwrite=1 src=/ dest='/Volumes/BIEN3.**SAVE**/' sudo -E put --exclude='/\**' --exclude=/.fseventsd/ --exclude=/private/var/vm/
536
			# no --extended-attributes: rsync has to visit every file for this
537
			# runtime: 10 min (~600); initial runtime: 4-13 h ("2422.84"+"12379.91" .. "45813.19"+"747.96")
538
		launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist # start the PostgreSQL server
539
		resume Dropbox: system tray > Dropbox icon > gear icon > Resume Syncing
540
	to restore from Time Machine:
541
		# restart holding Alt
542
		# select Time Machine Backups
543
		# restore the last Time Machine backup to Macintosh HD
544
		# restart holding Alt
545
		# select Macintosh HD
546
		$ screen
547
		$ l=1 swap=1 src=/ dest=/Volumes/Time\ Machine\ Backups/ sudo -E put usr/local/var/postgres/ # runtime: 1 h ("4020.61")
548
		$ make postgres_restart
549
	VegCore data dictionary:
550
		Regularly, or whenever the VegCore data dictionary page
551
			(https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/VegCore)
552
			is changed, regenerate mappings/VegCore.csv:
553
			On local machine:
554
			make mappings/VegCore.htm-remake; make mappings/
555
			apply new data dict mappings to datasource mappings/staging tables:
556
				inputs/run postprocess # runtime: see inputs/run
557
				time yes|make inputs/{NVS,SALVIAS,TEAM}/test # old-style import; runtime: 1 min ("0m59.692s") @starscream
558
			svn di mappings/VegCore.tables.redmine
559
			If there are changes, update the data dictionary's Tables section
560
			When moving terms, check that no terms were lost: svn di
561
			svn ci -m 'mappings/VegCore.htm: regenerated from wiki'
562
			ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
563
				perform the steps under "apply new data dict mappings to
564
					datasource mappings/staging tables" above
565
	Important: Whenever you install a system update that affects PostgreSQL or
566
		any of its dependencies, such as libc, you should restart the PostgreSQL
567
		server. Otherwise, you may get strange errors like "the database system
568
		is in recovery mode" which go away upon reimport, or you may not be able
569
		to access the database as the postgres superuser. This applies to both
570
		Linux and Mac OS X.
571

    
572
Backups:
573
	Archived imports:
574
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
575
		Back up: make backups/<version>.backup &
576
			Note: To back up the last import, you must archive it first:
577
				make schemas/rotate
578
		Test: make -s backups/<version>.backup/test &
579
		Restore: make backups/<version>.backup/restore &
580
		Remove: make backups/<version>.backup/remove
581
		Download: make backups/<version>.backup/download
582
	TNRS cache:
583
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
584
		Back up: make backups/TNRS.backup-remake &
585
			runtime: 3 min ("real 2m48.859s")
586
		Restore:
587
			yes|make inputs/.TNRS/uninstall
588
			make backups/TNRS.backup/restore &
589
				runtime: 5.5 min ("real 5m35.829s")
590
			yes|make schemas/public/reinstall
591
				Must come after TNRS restore to recreate tnrs_input_name view
592
	Full DB:
593
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
594
		Back up: make backups/vegbien.<version>.backup &
595
		Test: make -s backups/vegbien.<version>.backup/test &
596
		Restore: make backups/vegbien.<version>.backup/restore &
597
		Download: make backups/vegbien.<version>.backup/download
598
	Import logs:
599
		On local machine:
600
		Download: make inputs/download-logs live=1
601

    
602
Datasource refreshing:
603
	VegBank:
604
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
605
		make inputs/VegBank/vegbank.sql-remake
606
		make inputs/VegBank/reinstall quiet=1 &
607

    
608
Schema changes:
609
	On local machine:
610
	When changing the analytical views, run sync_analytical_..._to_view()
611
		to update the corresponding table
612
	Remember to update the following files with any renamings:
613
		schemas/filter_ERD.csv
614
		mappings/VegCore-VegBIEN.csv
615
		mappings/verify.*.sql
616
	Regenerate schema from installed DB: make schemas/remake
617
	Reinstall DB from schema: make schemas/public/reinstall schemas/reinstall
618
		**WARNING**: This will delete the public schema of your VegBIEN DB!
619
	If needed, reinstall staging tables:
620
		On local machine:
621
			sudo -E -u postgres psql <<<'ALTER DATABASE vegbien RENAME TO vegbien_prev'
622
			make db
623
			. bin/reinstall_all
624
			Fix any bugs and retry until no errors
625
			make schemas/public/install
626
				This must be run *after* the datasources are installed, because
627
				views in public depend on some of the datasources
628
			sudo -E -u postgres psql <<<'DROP DATABASE vegbien_prev'
629
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
630
			repeat the above steps
631
			**WARNING**: Do not run this until reinstall_all runs successfully
632
			on the local machine, or the live DB may be unrestorable!
633
	update mappings and staging table column names:
634
		on local machine:
635
			inputs/run postprocess # runtime: see inputs/run
636
			time yes|make inputs/{NVS,SALVIAS,TEAM}/test # old-style import; runtime: 1 min ("0m59.692s") @starscream
637
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
638
			manually apply schema changes to the live public schema
639
			do steps under "on local machine" above
640
	Sync ERD with vegbien.sql schema:
641
		Run make schemas/vegbien.my.sql
642
		Open schemas/vegbien.ERD.mwb in MySQLWorkbench
643
		Go to File > Export > Synchronize With SQL CREATE Script...
644
		For Input File, select schemas/vegbien.my.sql
645
		Click Continue
646
		In the changes list, select each table with an arrow next to it
647
		Click Update Model
648
		Click Continue
649
		Note: The generated SQL script will be empty because we are syncing in
650
			the opposite direction
651
		Click Execute
652
		Reposition any lines that have been reset
653
		Add any new tables by dragging them from the Catalog in the left sidebar
654
			to the diagram
655
		Remove any deleted tables by right-clicking the table's diagram element,
656
			selecting Delete '<table name>', and clicking Delete
657
		Save
658
		If desired, update the graphical ERD exports (see below)
659
	Update graphical ERD exports:
660
		Go to File > Export > Export as PNG...
661
		Select schemas/vegbien.ERD.png and click Save
662
		Go to File > Export > Export as SVG...
663
		Select schemas/vegbien.ERD.svg and click Save
664
		Go to File > Export > Export as Single Page PDF...
665
		Select schemas/vegbien.ERD.1_pg.pdf and click Save
666
		Go to File > Print...
667
		In the lower left corner, click PDF > Save as PDF...
668
		Set the Title and Author to ""
669
		Select schemas/vegbien.ERD.pdf and click Save
670
		Commit: svn ci -m "schemas/vegbien.ERD.mwb: Regenerated exports"
671
	Refactoring tips:
672
		To rename a table:
673
			In vegbien.sql, do the following:
674
				Replace regexp (?<=_|\b)<old>(?=_|\b) with <new>
675
					This is necessary because the table name is *everywhere*
676
				Search for <new>
677
				Manually change back any replacements inside comments
678
		To rename a column:
679
			Rename the column: ALTER TABLE <table> RENAME <old> TO <new>;
680
			Recreate any foreign key for the column, removing CONSTRAINT <name>
681
				This resets the foreign key name using the new column name
682
	Creating a poster of the ERD:
683
		Determine the poster size:
684
			Measure the line height (from the bottom of one line to the bottom
685
				of another): 16.3cm/24 lines = 0.679cm
686
			Measure the height of the ERD: 35.4cm*2 = 70.8cm
687
			Zoom in as far as possible
688
			Measure the height of a capital letter: 3.5mm
689
			Measure the line height: 8.5mm
690
			Calculate the text's fraction of the line height: 3.5mm/8.5mm = 0.41
691
			Calculate the text height: 0.679cm*0.41 = 0.28cm
692
			Calculate the text height's fraction of the ERD height:
693
				0.28cm/70.8cm = 0.0040
694
			Measure the text height on the *VegBank* ERD poster: 5.5mm = 0.55cm
695
			Calculate the VegBIEN poster height to make the text the same size:
696
				0.55cm/0.0040 = 137.5cm H; *1in/2.54cm = 54.1in H
697
			The ERD aspect ratio is 11 in W x (2*8.5in H) = 11x17 portrait
698
			Calculate the VegBIEN poster width: 54.1in H*11W/17H = 35.0in W
699
			The minimum VegBIEN poster size is 35x54in portrait
700
		Determine the cost:
701
			The FedEx Kinkos near NCEAS (1030 State St, Santa Barbara, CA 93101)
702
				charges the following for posters:
703
				base: $7.25/sq ft
704
				lamination: $3/sq ft
705
				mounting on a board: $8/sq ft
706

    
707
Testing:
708
	On a development machine, you should put the following in your .profile:
709
		umask ug=rwx,o= # prevent files from becoming web-accessible
710
		export log= n=2
711
	For development machine specs, see /planning/resources/dev_machine.specs/
712
	On local machine:
713
	Mapping process: make test
714
		Including column-based import: make test by_col=1
715
			If the row-based and column-based imports produce different inserted
716
			row counts, this usually means that a table is underconstrained
717
			(the unique indexes don't cover all possible rows).
718
			This can occur if you didn't use COALESCE(field, null_value) around
719
			a nullable field in a unique index. See sql_gen.null_sentinels for
720
			the appropriate null value to use.
721
	Map spreadsheet generation: make remake
722
	Missing mappings: make missing_mappings
723
	Everything (for most complete coverage): make test-all
724

    
725
Debugging:
726
	"Binary chop" debugging:
727
		(This is primarily useful for regressions that occurred in a previous
728
		revision, which was committed without running all the tests)
729
		up -r <rev>; make inputs/.TNRS/reinstall; make schemas/public/reinstall; make <failed-test>.xml
730
	.htaccess:
731
		mod_rewrite:
732
			**IMPORTANT**: whenever you change the DirectorySlash setting for a
733
				directory, you *must* clear your browser's cache to ensure that
734
				a cached redirect is not used. this is because RewriteRule
735
				redirects are (by default) temporary, but DirectorySlash
736
				redirects are permanent.
737
				for Firefox:
738
					press Cmd+Shift+Delete
739
					check only Cache
740
					press Enter or click Clear Now
741

    
742
WinMerge setup:
743
	In a Windows VM:
744
	Install WinMerge from <http://winmerge.org/>
745
	Open WinMerge
746
	Go to Edit > Options and click Compare in the left sidebar
747
	Enable "Moved block detection", as described at
748
		<http://manual.winmerge.org/Configuration.html#d0e5892>.
749
	Set Whitespace to Ignore change, as described at
750
		<http://manual.winmerge.org/Configuration.html#d0e5758>.
751

    
752
Documentation:
753
	To generate a Redmine-formatted list of steps for column-based import:
754
		On local machine:
755
		make schemas/public/reinstall
756
		make inputs/ACAD/Specimen/logs/steps.by_col.log.sql
757
	To import and scrub just the test taxonomic names:
758
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
759
		inputs/test_taxonomic_names/test_scrub
760

    
761
General:
762
	To see a program's description, read its top-of-file comment
763
	To see a program's usage, run it without arguments
764
	To remake a directory: make <dir>/remake
765
	To remake a file: make <file>-remake
(6-6/11)