Project

General

Profile

1
Installation:
2
	open a terminal window
3
	Check out svn:
4
		sudo apt-get --yes install subversion # not preinstalled on Ubuntu
5
		svn co https://code.nceas.ucsb.edu/code/projects/bien/trunk bien
6
	cd bien/
7
	Install:
8
		**WARNING**: This will delete the public schema of your VegBIEN DB!
9
		make install
10
		# at "reload PATH" (if displayed), do what it says
11
		# at "Are you sure you want to continue connecting", type "yes" and
12
			press Enter
13
		# at "aaronmk@jupiter's password", enter the applicable password
14
		# at "[sudo] password for user", enter your password and press Enter
15
		# at "Modifying postgresql.conf and pg_hba.conf", type y and press Enter
16
		# at "kernel.shmmax [...] Press ENTER to continue":
17
			# open a new window
18
			# run what it says
19
			# press Ctrl-D
20
			# return to the previous window
21
			# press Enter
22
		# at "restart PostgreSQL manually ... Press ENTER to continue":
23
			# open a new window
24
			# run what it says
25
			# press Ctrl-D
26
			# return to the previous window
27
			# press Enter
28
		# at "This will delete the current public schema of your VegBIEN DB",
29
			type y and press Enter
30
		# at "If asked for MySQL root password", copy the password to the
31
			clipboard and press Enter
32
		# at "Web server to reconfigure automatically", select apache2 and click
33
			Ok
34
		# at "Configure database for phpmyadmin with dbconfig-common?", click
35
			Yes
36
		# at "Password of the database's administrative user", paste the
37
			password and click Ok
38
		# at "MySQL application password for phpmyadmin", just click Ok
39
		# at "An error occurred while installing the database", click Ok
40
		# at "Next step for database installation", select ignore and click Ok
41
		# at "aaronmk@jupiter's password", enter the applicable password
42
	Uninstall: make uninstall
43
		**WARNING**: This will delete your entire VegBIEN DB!
44
		This includes all archived imports and staging tables.
45

    
46
Connecting to vegbiendev:
47
	ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
48
	cd /home/bien # should happen automatically at login
49

    
50
Single datasource refresh:
51
	ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
52
	# -> Maintenance > to back up the vegbiendev databases
53
	# place updated extract in inputs/$datasrc/_src/
54
	# place extracted flat file(s) in the appropriate table subdirs 
55
	rm=1 inputs/<datasrc>/run # reload staging tables
56
	make inputs/<datasrc>/reimport_scrub by_col=1 &
57
		# this works whether or not datasource is already imported
58
	tail -150 inputs/<datasrc>/*/logs/public.log.sql # view progress
59
	# -> Full database import > To re-run geoscrubbing
60
	# -> Full database import > To remake analytical DB
61
	# -> Full database import > To back up DB
62
	# -> Maintenance > to back up the vegbiendev databases
63

    
64
datasource removal:
65
	ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
66
	$ make inputs/$datasrc/rm # runtime: see
67
		# http://vegpath.org/wiki/Individual_datasource_refresh#datasource-removal-runtimes
68

    
69
Notes on system stability:
70
	**WARNING**: when shutting down the VM, always first stop Postgres:
71
		sudo service postgresql stop
72
		this prevents the OS from SIGKILLing Postgres, which sometimes causes
73
		database corruption 
74

    
75
Notes on running programs:
76
	**WARNING**: always start with a clean shell, to avoid spurious bugs. the
77
		shell should not have changes to the env vars. (there have been bugs
78
		that went away after closing and reopening the terminal window.) note
79
		that running `exec bash` is not sufficient to *reset* the env vars.
80

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

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

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

    
442
Maintenance:
443
	on a live machine, you should put the following in your .profile:
444
--
445
# make svn files web-accessible. this does not affect unversioned files, because
446
# these get the right permissions on the local machine instead.
447
umask ug=rwx,o=rx
448

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

    
607
Backups:
608
	Archived imports:
609
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
610
		Back up: make backups/<version>.backup &
611
			Note: To back up the last import, you must archive it first:
612
				make schemas/rotate
613
		Test: make -s backups/<version>.backup/test &
614
		Restore: make backups/<version>.backup/restore &
615
		Remove: make backups/<version>.backup/remove
616
		Download: make backups/<version>.backup/download
617
	TNRS cache:
618
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
619
		Back up: make backups/TNRS.backup-remake &
620
			runtime: 3 min ("real 2m48.859s")
621
		Restore:
622
			yes|make inputs/.TNRS/uninstall
623
			make backups/TNRS.backup/restore &
624
				runtime: 5.5 min ("real 5m35.829s")
625
			yes|make schemas/public/reinstall
626
				Must come after TNRS restore to recreate tnrs_input_name view
627
	Full DB:
628
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
629
		Back up: make backups/vegbien.<version>.backup &
630
		Test: make -s backups/vegbien.<version>.backup/test &
631
		Restore: make backups/vegbien.<version>.backup/restore &
632
		Download: make backups/vegbien.<version>.backup/download
633
	Import logs:
634
		On local machine:
635
		Download: make inputs/download-logs live=1
636

    
637
Datasource refreshing:
638
	VegBank:
639
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
640
		make inputs/VegBank/vegbank.sql-remake
641
		make inputs/VegBank/reinstall quiet=1 &
642

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

    
742
Testing:
743
	On a development machine, you should put the following in your .profile:
744
		umask ug=rwx,o= # prevent files from becoming web-accessible
745
		export log= n=2
746
	For development machine specs, see /planning/resources/dev_machine.specs/
747
	On local machine:
748
	Mapping process: make test
749
		Including column-based import: make test by_col=1
750
			If the row-based and column-based imports produce different inserted
751
			row counts, this usually means that a table is underconstrained
752
			(the unique indexes don't cover all possible rows).
753
			This can occur if you didn't use COALESCE(field, null_value) around
754
			a nullable field in a unique index. See sql_gen.null_sentinels for
755
			the appropriate null value to use.
756
	Map spreadsheet generation: make remake
757
	Missing mappings: make missing_mappings
758
	Everything (for most complete coverage): make test-all
759

    
760
Debugging:
761
	"Binary chop" debugging:
762
		(This is primarily useful for regressions that occurred in a previous
763
		revision, which was committed without running all the tests)
764
		up -r <rev>; make inputs/.TNRS/reinstall; make schemas/public/reinstall; make <failed-test>.xml
765
	.htaccess:
766
		mod_rewrite:
767
			**IMPORTANT**: whenever you change the DirectorySlash setting for a
768
				directory, you *must* clear your browser's cache to ensure that
769
				a cached redirect is not used. this is because RewriteRule
770
				redirects are (by default) temporary, but DirectorySlash
771
				redirects are permanent.
772
				for Firefox:
773
					press Cmd+Shift+Delete
774
					check only Cache
775
					press Enter or click Clear Now
776

    
777
WinMerge setup:
778
	In a Windows VM:
779
	Install WinMerge from <http://winmerge.org/>
780
	Open WinMerge
781
	Go to Edit > Options and click Compare in the left sidebar
782
	Enable "Moved block detection", as described at
783
		<http://manual.winmerge.org/Configuration.html#d0e5892>.
784
	Set Whitespace to Ignore change, as described at
785
		<http://manual.winmerge.org/Configuration.html#d0e5758>.
786

    
787
Documentation:
788
	To generate a Redmine-formatted list of steps for column-based import:
789
		On local machine:
790
		make schemas/public/reinstall
791
		make inputs/ACAD/Specimen/logs/steps.by_col.log.sql
792
	To import and scrub just the test taxonomic names:
793
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
794
		inputs/test_taxonomic_names/test_scrub
795

    
796
General:
797
	To see a program's description, read its top-of-file comment
798
	To see a program's usage, run it without arguments
799
	To remake a directory: make <dir>/remake
800
	To remake a file: make <file>-remake
(6-6/11)