1 |
702
|
aaronmk
|
Installation:
|
2 |
14747
|
aaronmk
|
open a terminal window
|
3 |
13764
|
aaronmk
|
Check out svn:
|
4 |
14742
|
aaronmk
|
sudo apt-get --yes install subversion # not preinstalled on Ubuntu
|
5 |
13764
|
aaronmk
|
svn co https://code.nceas.ucsb.edu/code/projects/bien/trunk bien
|
6 |
8458
|
aaronmk
|
cd bien/
|
7 |
14699
|
aaronmk
|
Install:
|
8 |
12226
|
aaronmk
|
**WARNING**: This will delete the public schema of your VegBIEN DB!
|
9 |
14699
|
aaronmk
|
make install
|
10 |
14743
|
aaronmk
|
# at "reload PATH" (if displayed), do what it says
|
11 |
14746
|
aaronmk
|
# at "Are you sure you want to continue connecting", type "yes" and
|
12 |
|
|
press Enter
|
13 |
14756
|
aaronmk
|
# at "aaronmk@jupiter's password", enter the applicable password
|
14 |
14699
|
aaronmk
|
# 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 |
14757
|
aaronmk
|
# 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 |
14758
|
aaronmk
|
# at "aaronmk@jupiter's password", enter the applicable password
|
42 |
8458
|
aaronmk
|
Uninstall: make uninstall
|
43 |
12226
|
aaronmk
|
**WARNING**: This will delete your entire VegBIEN DB!
|
44 |
8458
|
aaronmk
|
This includes all archived imports and staging tables.
|
45 |
554
|
aaronmk
|
|
46 |
11515
|
aaronmk
|
Connecting to vegbiendev:
|
47 |
13284
|
aaronmk
|
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
|
48 |
13763
|
aaronmk
|
cd /home/bien # should happen automatically at login
|
49 |
11515
|
aaronmk
|
|
50 |
14651
|
aaronmk
|
Single datasource refresh:
|
51 |
14653
|
aaronmk
|
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
|
52 |
14651
|
aaronmk
|
# -> Maintenance > to back up the vegbiendev databases
|
53 |
14787
|
aaronmk
|
# place updated extract in inputs/$datasrc/_src/
|
54 |
14654
|
aaronmk
|
# place extracted flat file(s) in the appropriate table subdirs
|
55 |
14651
|
aaronmk
|
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 |
14652
|
aaronmk
|
tail -150 inputs/<datasrc>/*/logs/public.log.sql # view progress
|
59 |
14651
|
aaronmk
|
# -> Full database import > To re-run geoscrubbing
|
60 |
|
|
# -> Full database import > To remake analytical DB
|
61 |
14791
|
aaronmk
|
# -> Full database import > To back up DB
|
62 |
14651
|
aaronmk
|
# -> Maintenance > to back up the vegbiendev databases
|
63 |
|
|
|
64 |
14788
|
aaronmk
|
datasource removal:
|
65 |
|
|
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
|
66 |
14868
|
aaronmk
|
$ make inputs/$datasrc/rm # runtime: see
|
67 |
|
|
# http://vegpath.org/wiki/Individual_datasource_refresh#datasource-removal-runtimes
|
68 |
14788
|
aaronmk
|
|
69 |
13024
|
aaronmk
|
Notes on system stability:
|
70 |
14091
|
aaronmk
|
**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 |
13024
|
aaronmk
|
|
75 |
12011
|
aaronmk
|
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 |
11967
|
aaronmk
|
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 |
7287
|
aaronmk
|
Full database import:
|
87 |
12226
|
aaronmk
|
**WARNING**: You must perform *every single* step listed below, to avoid
|
88 |
9499
|
aaronmk
|
breaking column-based import
|
89 |
12011
|
aaronmk
|
**WARNING**: always start with a clean shell, as described above under
|
90 |
|
|
"Notes on running programs"
|
91 |
13021
|
aaronmk
|
**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 |
13000
|
aaronmk
|
do steps under Maintenance > "to synchronize vegbiendev, jupiter, and
|
96 |
|
|
your local machine"
|
97 |
8458
|
aaronmk
|
On local machine:
|
98 |
|
|
make inputs/upload
|
99 |
10025
|
aaronmk
|
make inputs/upload live=1
|
100 |
14077
|
aaronmk
|
make test by_col=1 # runtime: 1 h ("53m7.383s") @starscream
|
101 |
10549
|
aaronmk
|
if you encounter errors, they are most likely related to the
|
102 |
|
|
PostgreSQL error parsing in /lib/sql.py parse_exception()
|
103 |
8458
|
aaronmk
|
See note under Testing below
|
104 |
14887
|
aaronmk
|
-> Maintenance > to back up the vegbiendev databases > back up Postgres
|
105 |
13284
|
aaronmk
|
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
|
106 |
8458
|
aaronmk
|
Ensure there are no local modifications: svn st
|
107 |
12998
|
aaronmk
|
up
|
108 |
8458
|
aaronmk
|
make inputs/download
|
109 |
10025
|
aaronmk
|
make inputs/download live=1
|
110 |
8458
|
aaronmk
|
For each newly-uploaded datasource above: make inputs/<datasrc>/reinstall
|
111 |
|
|
Update the auxiliary schemas: make schemas/reinstall
|
112 |
12226
|
aaronmk
|
**WARNING**: requires sudo access!
|
113 |
8458
|
aaronmk
|
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 |
13009
|
aaronmk
|
# env var will be inherited by `screen` shell
|
119 |
13016
|
aaronmk
|
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 |
13022
|
aaronmk
|
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 |
13023
|
aaronmk
|
significant temporary space at the beginning of the import.
|
125 |
13028
|
aaronmk
|
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 |
13031
|
aaronmk
|
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 |
8458
|
aaronmk
|
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 |
13423
|
aaronmk
|
for full import:
|
134 |
|
|
screen
|
135 |
|
|
Press ENTER
|
136 |
14087
|
aaronmk
|
$0 # nested shell to prevent errexit from closing the window
|
137 |
13422
|
aaronmk
|
the following must happen within screen to avoid affecting the outer shell:
|
138 |
13428
|
aaronmk
|
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 |
13426
|
aaronmk
|
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 |
13424
|
aaronmk
|
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 |
13119
|
aaronmk
|
to import just a subset of the datasources:
|
152 |
13427
|
aaronmk
|
declare -ax inputs; inputs=(inputs/{src,...}/) # no () in declare on Mac
|
153 |
13119
|
aaronmk
|
# array vars *not* inherited by `screen` shell
|
154 |
|
|
export version=custom_import_name
|
155 |
10579
|
aaronmk
|
Start column-based import: . bin/import_all
|
156 |
|
|
To use row-based import: . bin/import_all by_col=
|
157 |
8458
|
aaronmk
|
To stop all running imports: . bin/stop_imports
|
158 |
12226
|
aaronmk
|
**WARNING**: Do NOT run import_all in the background, or the jobs it
|
159 |
|
|
creates won't be owned by your shell.
|
160 |
8458
|
aaronmk
|
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 |
12026
|
aaronmk
|
To view progress:
|
163 |
14882
|
aaronmk
|
tail inputs/{.[^as.],}*/*/logs/$version.log.sql
|
164 |
13020
|
aaronmk
|
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 |
13425
|
aaronmk
|
for test import, turn off DB backup (also turns off analytical DB creation):
|
168 |
13429
|
aaronmk
|
kill % # cancel after_import()
|
169 |
10850
|
aaronmk
|
Wait (4 days) for the import to finish
|
170 |
14197
|
aaronmk
|
**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 |
8458
|
aaronmk
|
To recover from a closed terminal window: screen -r
|
175 |
10583
|
aaronmk
|
To restart an aborted import for a specific table:
|
176 |
|
|
export version=<version>
|
177 |
11800
|
aaronmk
|
(set -o errexit; make inputs/<datasrc>/<table>/import_scrub by_col=1 continue=1; make inputs/<datasrc>/publish) &
|
178 |
10588
|
aaronmk
|
bin/after_import $! & # $! can also be obtained from `jobs -l`
|
179 |
8458
|
aaronmk
|
Get $version: echo $version
|
180 |
|
|
Set $version in all vegbiendev terminals: export version=<version>
|
181 |
13017
|
aaronmk
|
When there are no more running jobs, exit `screen`: exit # not Ctrl+D
|
182 |
13025
|
aaronmk
|
upload logs: make inputs/upload live=1
|
183 |
10025
|
aaronmk
|
On local machine: make inputs/download-logs live=1
|
184 |
13030
|
aaronmk
|
check for disk space errors:
|
185 |
14882
|
aaronmk
|
grep --files-with-matches -F 'No space left on device' inputs/{.[^as.],}*/*/logs/$version.log.sql
|
186 |
13030
|
aaronmk
|
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 |
14882
|
aaronmk
|
tail inputs/{.[^as.],}*/*/logs/$version.log.sql
|
192 |
13029
|
aaronmk
|
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 |
8458
|
aaronmk
|
In PostgreSQL:
|
198 |
11568
|
aaronmk
|
Go to wiki.vegpath.org/VegBIEN_contents
|
199 |
11728
|
aaronmk
|
Get the # observations
|
200 |
|
|
Get the # datasources
|
201 |
|
|
Get the # datasources with observations
|
202 |
11892
|
aaronmk
|
in the r# schema:
|
203 |
11569
|
aaronmk
|
Check that analytical_stem contains [# observations] rows
|
204 |
12148
|
aaronmk
|
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 |
11568
|
aaronmk
|
Check that provider_count contains [# datasources with observations]
|
209 |
|
|
rows with dataset="(total)" (at the top when the table is unsorted)
|
210 |
9492
|
aaronmk
|
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 |
10784
|
aaronmk
|
Make the changes in the DB's TNRS and public schemas
|
217 |
|
|
rm=1 inputs/.TNRS/schema.sql.run export_
|
218 |
9492
|
aaronmk
|
make schemas/remake
|
219 |
10785
|
aaronmk
|
inputs/test_taxonomic_names/test_scrub # re-run TNRS
|
220 |
10784
|
aaronmk
|
rm=1 inputs/.TNRS/data.sql.run export_
|
221 |
9492
|
aaronmk
|
Commit
|
222 |
13284
|
aaronmk
|
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
|
223 |
9492
|
aaronmk
|
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 |
9498
|
aaronmk
|
Publish the new import:
|
228 |
12226
|
aaronmk
|
**WARNING**: Before proceeding, be sure you have done *every single*
|
229 |
9498
|
aaronmk
|
verification step listed above. Otherwise, a previous valid import
|
230 |
|
|
could incorrectly be overwritten with a broken one.
|
231 |
10864
|
aaronmk
|
make schemas/$version/publish # runtime: 1 min ("real 1m10.451s")
|
232 |
8458
|
aaronmk
|
unset version
|
233 |
10027
|
aaronmk
|
make backups/upload live=1
|
234 |
11897
|
aaronmk
|
on local machine:
|
235 |
|
|
make backups/vegbien.$version.backup/download live=1
|
236 |
|
|
# download backup to local machine
|
237 |
12396
|
aaronmk
|
ssh aaronmk@jupiter.nceas.ucsb.edu
|
238 |
8458
|
aaronmk
|
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 |
11573
|
aaronmk
|
On local machine:
|
244 |
8458
|
aaronmk
|
Open inputs/import.stats.xls
|
245 |
14878
|
aaronmk
|
click the "current" tab
|
246 |
8458
|
aaronmk
|
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 |
14882
|
aaronmk
|
bin/import_date inputs/{.[^as.],}*/*/logs/$version.log.sql
|
253 |
8458
|
aaronmk
|
Update the import date in the upper-right corner
|
254 |
14882
|
aaronmk
|
bin/import_times inputs/{.[^as.],}*/*/logs/$version.log.sql
|
255 |
8458
|
aaronmk
|
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 |
14882
|
aaronmk
|
tail inputs/{.[^as.],}*/*/logs/$version.log.sql
|
260 |
14883
|
aaronmk
|
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 |
11573
|
aaronmk
|
Commit: svn ci -m 'inputs/import.stats.xls: updated import times'
|
267 |
10885
|
aaronmk
|
Running individual steps separately:
|
268 |
9497
|
aaronmk
|
To run TNRS:
|
269 |
9996
|
aaronmk
|
To use an import other than public: export version=<version>
|
270 |
13594
|
aaronmk
|
to rescrub all names:
|
271 |
|
|
make inputs/.TNRS/reinstall
|
272 |
|
|
re-create public-schema views that were cascadingly deleted
|
273 |
9995
|
aaronmk
|
make scrub &
|
274 |
8458
|
aaronmk
|
To view progress:
|
275 |
|
|
tail -100 inputs/.TNRS/tnrs/logs/tnrs.make.log.sql
|
276 |
14447
|
aaronmk
|
To re-run geoscrubbing:
|
277 |
|
|
$ screen
|
278 |
14871
|
aaronmk
|
# press Enter
|
279 |
14802
|
aaronmk
|
$ unset TMOUT # TMOUT causes shell to exit even with background processes
|
280 |
14530
|
aaronmk
|
# to use an import other than public: $ export version=<version>
|
281 |
|
|
$ bin/psql_verbose_vegbien <<<'SELECT geoscrub_input_view_modify();' &
|
282 |
14877
|
aaronmk
|
# runtime: 6 min ("6:02.30") @r14827 @vegbiendev
|
283 |
14531
|
aaronmk
|
# wait until done
|
284 |
14536
|
aaronmk
|
$ rm=1 exports/geoscrub_input.csv.run
|
285 |
14877
|
aaronmk
|
# runtime: 1 min ("1m2.962s") @r14827 @vegbiendev
|
286 |
14792
|
aaronmk
|
$ $0 # subshell to avoid closing screen on errexit
|
287 |
14447
|
aaronmk
|
$ rm=1 inputs/.geoscrub/geoscrub_output/geoscrub.csv.run &
|
288 |
14877
|
aaronmk
|
# runtime: 1.5 h ("84m55.408s") @r14827 @vegbiendev
|
289 |
14447
|
aaronmk
|
# wait until done
|
290 |
14798
|
aaronmk
|
$ rm=1 inputs/.geoscrub/geoscrub_output/run &
|
291 |
14877
|
aaronmk
|
# runtime: 12 min ("11m35.693s") @r14827 @vegbiendev
|
292 |
14447
|
aaronmk
|
# wait until done
|
293 |
14824
|
aaronmk
|
# re-create public-schema views that were cascadingly deleted (currently
|
294 |
|
|
plot.**, view_full_occurrence_individual_view, geoscrub_input_new)
|
295 |
14447
|
aaronmk
|
# press Ctrl+D
|
296 |
|
|
# remake the analytical DB (below)
|
297 |
9497
|
aaronmk
|
To remake analytical DB:
|
298 |
9996
|
aaronmk
|
To use an import other than public: export version=<version>
|
299 |
11089
|
aaronmk
|
bin/make_analytical_db & # runtime: 13 h ("12:43:57elapsed")
|
300 |
8458
|
aaronmk
|
To view progress:
|
301 |
10600
|
aaronmk
|
tail -150 inputs/analytical_db/logs/make_analytical_db.log.sql
|
302 |
8458
|
aaronmk
|
To back up DB (staging tables and last import):
|
303 |
10578
|
aaronmk
|
To use an import *other than public*: export version=<version>
|
304 |
10743
|
aaronmk
|
make backups/TNRS.backup-remake &
|
305 |
10577
|
aaronmk
|
dump_opts=--exclude-schema=public make backups/vegbien.$version.backup/test &
|
306 |
10578
|
aaronmk
|
If after renaming to public, instead set dump_opts='' and replace
|
307 |
|
|
$version with the appropriate revision
|
308 |
10744
|
aaronmk
|
make backups/upload live=1
|
309 |
3381
|
aaronmk
|
|
310 |
1773
|
aaronmk
|
Datasource setup:
|
311 |
11516
|
aaronmk
|
On local machine:
|
312 |
11090
|
aaronmk
|
Example steps for a datasource: wiki.vegpath.org/Import_process_for_Madidi
|
313 |
8469
|
aaronmk
|
umask ug=rwx,o= # prevent files from becoming web-accessible
|
314 |
8458
|
aaronmk
|
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 |
11018
|
aaronmk
|
For a new-style datasource (one containing a ./run runscript):
|
319 |
11019
|
aaronmk
|
"cp" -f inputs/.NCBI/{Makefile,run,table.run} inputs/<datasrc>/
|
320 |
8458
|
aaronmk
|
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 |
13284
|
aaronmk
|
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
|
326 |
8458
|
aaronmk
|
Create a database for the MySQL export in phpMyAdmin
|
327 |
9494
|
aaronmk
|
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 |
8458
|
aaronmk
|
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 |
14661
|
aaronmk
|
Download and install Bullzip's MS Access to PostgreSQL from
|
339 |
|
|
http://bullzip.com/download.php > Access To PostgreSQL > Download
|
340 |
8458
|
aaronmk
|
Use Access To PostgreSQL to export the database:
|
341 |
|
|
Export just the tables/indexes to inputs/<datasrc>/<file>.schema.sql
|
342 |
14662
|
aaronmk
|
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 |
8458
|
aaronmk
|
In <file>.schema.sql, make the following changes:
|
346 |
14813
|
aaronmk
|
Replace text "^CREATE DATABASE .*?;$" with "/*$0*/"
|
347 |
8458
|
aaronmk
|
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 |
8466
|
aaronmk
|
(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 |
8458
|
aaronmk
|
Add <table> to inputs/<datasrc>/import_order.txt before other tables
|
372 |
|
|
that depend on it
|
373 |
11018
|
aaronmk
|
For a new-style datasource:
|
374 |
|
|
"cp" -f inputs/.NCBI/nodes/run inputs/<datasrc>/<table>/
|
375 |
|
|
inputs/<datasrc>/<table>/run
|
376 |
8458
|
aaronmk
|
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 |
11018
|
aaronmk
|
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 |
8458
|
aaronmk
|
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 |
12396
|
aaronmk
|
ssh aaronmk@jupiter.nceas.ucsb.edu
|
431 |
12998
|
aaronmk
|
up
|
432 |
8458
|
aaronmk
|
On local machine:
|
433 |
|
|
./fix_perms
|
434 |
|
|
make inputs/upload
|
435 |
10025
|
aaronmk
|
make inputs/upload live=1
|
436 |
13284
|
aaronmk
|
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
|
437 |
12998
|
aaronmk
|
up
|
438 |
8458
|
aaronmk
|
make inputs/download
|
439 |
10025
|
aaronmk
|
make inputs/download live=1
|
440 |
8458
|
aaronmk
|
Follow the steps under Install the staging tables above
|
441 |
1773
|
aaronmk
|
|
442 |
10884
|
aaronmk
|
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 |
13284
|
aaronmk
|
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
|
453 |
12548
|
aaronmk
|
make phppgadmin-Linux
|
454 |
13027
|
aaronmk
|
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 |
13466
|
aaronmk
|
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 |
14554
|
aaronmk
|
backups/mysql_snapshot
|
460 |
13466
|
aaronmk
|
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 |
14554
|
aaronmk
|
backups/pg_snapshot
|
465 |
10884
|
aaronmk
|
to synchronize vegbiendev, jupiter, and your local machine:
|
466 |
12226
|
aaronmk
|
**WARNING**: pay careful attention to all files that will be deleted or
|
467 |
10884
|
aaronmk
|
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 |
12951
|
aaronmk
|
avoid extraneous diffs when rsyncing:
|
472 |
14670
|
aaronmk
|
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 |
13284
|
aaronmk
|
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
|
479 |
12396
|
aaronmk
|
upload:
|
480 |
11259
|
aaronmk
|
overwrite=1 bin/sync_upload --size-only
|
481 |
13337
|
aaronmk
|
then review diff, and rerun with `l=1` prepended
|
482 |
12396
|
aaronmk
|
on your machine:
|
483 |
|
|
download:
|
484 |
13942
|
aaronmk
|
overwrite=1 swap=1 src=. dest='aaronmk@jupiter.nceas.ucsb.edu:~/bien' put --exclude=.svn web/BIEN3/TWiki
|
485 |
13337
|
aaronmk
|
then review diff, and rerun with `l=1` prepended
|
486 |
12957
|
aaronmk
|
swap=1 bin/sync_upload backups/TNRS.backup
|
487 |
13337
|
aaronmk
|
then review diff, and rerun with `l=1` prepended
|
488 |
11259
|
aaronmk
|
overwrite=1 swap=1 bin/sync_upload --size-only
|
489 |
13337
|
aaronmk
|
then review diff, and rerun with `l=1` prepended
|
490 |
10884
|
aaronmk
|
overwrite=1 sync_remote_url=~/Dropbox/svn/ bin/sync_upload --existing --size-only # just update mtimes/perms
|
491 |
13337
|
aaronmk
|
then review diff, and rerun with `l=1` prepended
|
492 |
12959
|
aaronmk
|
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 |
13333
|
aaronmk
|
# 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 |
13331
|
aaronmk
|
svnsync sync file://"$HOME"/Dropbox/docs/BIEN/svn_repo/ # initial runtime: 1.5 h ("08:21:38" - "06:45:26") @vegbiendev
|
504 |
12959
|
aaronmk
|
(cd ~/Dropbox/docs/BIEN/git/; git svn fetch)
|
505 |
14565
|
aaronmk
|
# use absolute path for vegbiendev commands because the Ubuntu 14.04
|
506 |
|
|
version of rsync doesn't expand ~ properly
|
507 |
13332
|
aaronmk
|
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 |
13337
|
aaronmk
|
then review diff, and rerun with `l=1` prepended
|
509 |
13332
|
aaronmk
|
overwrite=1 src=~ dest='aaronmk@jupiter.nceas.ucsb.edu:/data/dev/aaronmk/' put Dropbox/docs/BIEN/git/
|
510 |
13337
|
aaronmk
|
then review diff, and rerun with `l=1` prepended
|
511 |
14553
|
aaronmk
|
to back up vegbiendev:
|
512 |
14568
|
aaronmk
|
do steps under Maintenance > "to synchronize vegbiendev, jupiter, and
|
513 |
|
|
your local machine"
|
514 |
14553
|
aaronmk
|
on local machine:
|
515 |
14574
|
aaronmk
|
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 |
14601
|
aaronmk
|
# 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 |
14399
|
aaronmk
|
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 |
14549
|
aaronmk
|
sudo find / -name .DS_Store -print -delete
|
528 |
14399
|
aaronmk
|
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 |
14548
|
aaronmk
|
overwrite=1 sync_local_dir=~/Dropbox/svn/ sync_remote_subdir=Dropbox/svn/ bin/sync_upload --size-only # just update mtimes
|
531 |
14399
|
aaronmk
|
then review diff, and rerun with `l=1` prepended
|
532 |
14738
|
aaronmk
|
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 |
14399
|
aaronmk
|
then review diff, and rerun with `l=1` prepended
|
534 |
14738
|
aaronmk
|
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 |
14693
|
aaronmk
|
# sudo -E: needed for Documents/BIEN/vegbiendev*/
|
536 |
14399
|
aaronmk
|
then review diff, and rerun with `l=1` prepended
|
537 |
14692
|
aaronmk
|
pause Dropbox: system tray > Dropbox icon > gear icon > Pause Syncing
|
538 |
14399
|
aaronmk
|
this prevents Dropbox from trying to capture filesystem
|
539 |
|
|
events while syncing
|
540 |
14738
|
aaronmk
|
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 |
14399
|
aaronmk
|
then review diff, and rerun with `l=1` prepended
|
542 |
14692
|
aaronmk
|
resume Dropbox: system tray > Dropbox icon > gear icon > Resume Syncing
|
543 |
10884
|
aaronmk
|
to backup files not in Time Machine:
|
544 |
14667
|
aaronmk
|
**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 |
11516
|
aaronmk
|
On local machine:
|
548 |
14656
|
aaronmk
|
on first run, create parent dirs:
|
549 |
14667
|
aaronmk
|
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 |
14660
|
aaronmk
|
# 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 |
14667
|
aaronmk
|
l=1 overwrite=1 src=/ dest='/Volumes/BIEN3.**SAVE**/' sudo -E put usr/local/var/postgres/
|
558 |
14655
|
aaronmk
|
# this cannot be backed up by Time Machine because it prevents the backup process from ending
|
559 |
13797
|
aaronmk
|
launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist # stop the PostgreSQL server
|
560 |
14667
|
aaronmk
|
l=1 overwrite=1 src=/ dest='/Volumes/BIEN3.**SAVE**/' sudo -E put usr/local/var/postgres/
|
561 |
13797
|
aaronmk
|
launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist # start the PostgreSQL server
|
562 |
14400
|
aaronmk
|
to back up the local machine's hard drive:
|
563 |
|
|
turn on and connect the 2 TB external hard drive
|
564 |
14608
|
aaronmk
|
screen
|
565 |
14623
|
aaronmk
|
# --exclude='/\**': exclude *-files indicating the (differing) retention
|
566 |
|
|
# statuses of the partitions involved
|
567 |
14692
|
aaronmk
|
pause Dropbox: system tray > Dropbox icon > gear icon > Pause Syncing
|
568 |
14550
|
aaronmk
|
otherwise, the backup of ~/.dropbox will be corrupted
|
569 |
14551
|
aaronmk
|
launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist # stop the PostgreSQL server
|
570 |
14627
|
aaronmk
|
l=1 overwrite=1 src=/ dest='/Volumes/BIEN3.**SAVE**/' sudo -E put --exclude='/\**' --exclude=/.fseventsd/ --exclude=/private/var/vm/
|
571 |
14696
|
aaronmk
|
# 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 |
14551
|
aaronmk
|
launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist # start the PostgreSQL server
|
574 |
14692
|
aaronmk
|
resume Dropbox: system tray > Dropbox icon > gear icon > Resume Syncing
|
575 |
14401
|
aaronmk
|
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 |
14607
|
aaronmk
|
$ l=1 swap=1 src=/ dest=/Volumes/Time\ Machine\ Backups/ sudo -E put usr/local/var/postgres/ # runtime: 1 h ("4020.61")
|
583 |
14401
|
aaronmk
|
$ make postgres_restart
|
584 |
10884
|
aaronmk
|
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 |
11516
|
aaronmk
|
On local machine:
|
589 |
10884
|
aaronmk
|
make mappings/VegCore.htm-remake; make mappings/
|
590 |
12716
|
aaronmk
|
apply new data dict mappings to datasource mappings/staging tables:
|
591 |
12883
|
aaronmk
|
inputs/run postprocess # runtime: see inputs/run
|
592 |
12887
|
aaronmk
|
time yes|make inputs/{NVS,SALVIAS,TEAM}/test # old-style import; runtime: 1 min ("0m59.692s") @starscream
|
593 |
10884
|
aaronmk
|
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 |
13284
|
aaronmk
|
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
|
598 |
12717
|
aaronmk
|
perform the steps under "apply new data dict mappings to
|
599 |
|
|
datasource mappings/staging tables" above
|
600 |
10884
|
aaronmk
|
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 |
13284
|
aaronmk
|
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
|
610 |
10884
|
aaronmk
|
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 |
13284
|
aaronmk
|
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
|
619 |
10884
|
aaronmk
|
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 |
13284
|
aaronmk
|
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
|
629 |
10884
|
aaronmk
|
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 |
11516
|
aaronmk
|
On local machine:
|
635 |
10884
|
aaronmk
|
Download: make inputs/download-logs live=1
|
636 |
|
|
|
637 |
6484
|
aaronmk
|
Datasource refreshing:
|
638 |
8458
|
aaronmk
|
VegBank:
|
639 |
13284
|
aaronmk
|
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
|
640 |
8458
|
aaronmk
|
make inputs/VegBank/vegbank.sql-remake
|
641 |
|
|
make inputs/VegBank/reinstall quiet=1 &
|
642 |
6484
|
aaronmk
|
|
643 |
702
|
aaronmk
|
Schema changes:
|
644 |
11516
|
aaronmk
|
On local machine:
|
645 |
8458
|
aaronmk
|
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 |
12226
|
aaronmk
|
**WARNING**: This will delete the public schema of your VegBIEN DB!
|
654 |
12227
|
aaronmk
|
If needed, reinstall staging tables:
|
655 |
8837
|
aaronmk
|
On local machine:
|
656 |
8840
|
aaronmk
|
sudo -E -u postgres psql <<<'ALTER DATABASE vegbien RENAME TO vegbien_prev'
|
657 |
8845
|
aaronmk
|
make db
|
658 |
8837
|
aaronmk
|
. bin/reinstall_all
|
659 |
|
|
Fix any bugs and retry until no errors
|
660 |
8846
|
aaronmk
|
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 |
8842
|
aaronmk
|
sudo -E -u postgres psql <<<'DROP DATABASE vegbien_prev'
|
664 |
13284
|
aaronmk
|
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
|
665 |
12396
|
aaronmk
|
repeat the above steps
|
666 |
12226
|
aaronmk
|
**WARNING**: Do not run this until reinstall_all runs successfully
|
667 |
|
|
on the local machine, or the live DB may be unrestorable!
|
668 |
12927
|
aaronmk
|
update mappings and staging table column names:
|
669 |
12881
|
aaronmk
|
on local machine:
|
670 |
12883
|
aaronmk
|
inputs/run postprocess # runtime: see inputs/run
|
671 |
12887
|
aaronmk
|
time yes|make inputs/{NVS,SALVIAS,TEAM}/test # old-style import; runtime: 1 min ("0m59.692s") @starscream
|
672 |
13284
|
aaronmk
|
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
|
673 |
12928
|
aaronmk
|
manually apply schema changes to the live public schema
|
674 |
12929
|
aaronmk
|
do steps under "on local machine" above
|
675 |
8458
|
aaronmk
|
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 |
203
|
aaronmk
|
|
742 |
1459
|
aaronmk
|
Testing:
|
743 |
8458
|
aaronmk
|
On a development machine, you should put the following in your .profile:
|
744 |
8469
|
aaronmk
|
umask ug=rwx,o= # prevent files from becoming web-accessible
|
745 |
8458
|
aaronmk
|
export log= n=2
|
746 |
11985
|
aaronmk
|
For development machine specs, see /planning/resources/dev_machine.specs/
|
747 |
11516
|
aaronmk
|
On local machine:
|
748 |
8458
|
aaronmk
|
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 |
702
|
aaronmk
|
|
760 |
7183
|
aaronmk
|
Debugging:
|
761 |
8458
|
aaronmk
|
"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 |
12998
|
aaronmk
|
up -r <rev>; make inputs/.TNRS/reinstall; make schemas/public/reinstall; make <failed-test>.xml
|
765 |
8470
|
aaronmk
|
.htaccess:
|
766 |
|
|
mod_rewrite:
|
767 |
12226
|
aaronmk
|
**IMPORTANT**: whenever you change the DirectorySlash setting for a
|
768 |
8471
|
aaronmk
|
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 |
8470
|
aaronmk
|
for Firefox:
|
773 |
|
|
press Cmd+Shift+Delete
|
774 |
|
|
check only Cache
|
775 |
|
|
press Enter or click Clear Now
|
776 |
7183
|
aaronmk
|
|
777 |
3783
|
aaronmk
|
WinMerge setup:
|
778 |
11516
|
aaronmk
|
In a Windows VM:
|
779 |
8458
|
aaronmk
|
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 |
3783
|
aaronmk
|
|
787 |
3133
|
aaronmk
|
Documentation:
|
788 |
8458
|
aaronmk
|
To generate a Redmine-formatted list of steps for column-based import:
|
789 |
11516
|
aaronmk
|
On local machine:
|
790 |
8458
|
aaronmk
|
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 |
13284
|
aaronmk
|
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
|
794 |
8458
|
aaronmk
|
inputs/test_taxonomic_names/test_scrub
|
795 |
3133
|
aaronmk
|
|
796 |
702
|
aaronmk
|
General:
|
797 |
8458
|
aaronmk
|
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
|