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

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

    
432
Maintenance:
433
	on a live machine, you should put the following in your .profile:
434
--
435
# make svn files web-accessible. this does not affect unversioned files, because
436
# these get the right permissions on the local machine instead.
437
umask ug=rwx,o=rx
438

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

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

    
627
Datasource refreshing:
628
	VegBank:
629
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
630
		make inputs/VegBank/vegbank.sql-remake
631
		make inputs/VegBank/reinstall quiet=1 &
632

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

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

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

    
767
WinMerge setup:
768
	In a Windows VM:
769
	Install WinMerge from <http://winmerge.org/>
770
	Open WinMerge
771
	Go to Edit > Options and click Compare in the left sidebar
772
	Enable "Moved block detection", as described at
773
		<http://manual.winmerge.org/Configuration.html#d0e5892>.
774
	Set Whitespace to Ignore change, as described at
775
		<http://manual.winmerge.org/Configuration.html#d0e5758>.
776

    
777
Documentation:
778
	To generate a Redmine-formatted list of steps for column-based import:
779
		On local machine:
780
		make schemas/public/reinstall
781
		make inputs/ACAD/Specimen/logs/steps.by_col.log.sql
782
	To import and scrub just the test taxonomic names:
783
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
784
		inputs/test_taxonomic_names/test_scrub
785

    
786
General:
787
	To see a program's description, read its top-of-file comment
788
	To see a program's usage, run it without arguments
789
	To remake a directory: make <dir>/remake
790
	To remake a file: make <file>-remake
(6-6/11)