Exploits in Brute Repetition - RODBC and Postgresql and Postgis and UnixODBC and Ubuntu

Database admin will make me old before my time... It's a wicked-cool toolchain, but byzantine to say the least. This is the third time i've gone through this as a fresh installation, and it sucks every time. Here i'm installing the database engine, setting up the database, adding db user, and restoring a dump. This time, I've documented it... May you never have to do this from scratch through the google-tron! #!/bin/bash ## steps to create frs database from scatch on ubuntu ## with system user as cluser superuser ## postgis setup included for package postgresql-8.3 sudo apt-get install postgresql-8.3 postgresql-8.3-postgis sudo -u postgres createuser --superuser $USER sudo -u postgres psql -c "alter role $USER encrypted password '*****'" createdb $DB createlang plpgsql $DB psql -d $DB -f /usr/share/postgresql-8.3-postgis/lwpostgis.sql psql -d $DB -f /usr/share/postgresql-8.3-postgis/spatial_ref_sys.sql ## http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=441797 ## postgis version issues... # cd /usr/lib/postgresql/ # ln -s 8.3 8.2 # sudo ln -s liblwgeom.so.1.3 liblwgeom.so.1.2 # time psql -d frs -f db.full-2008-06-24 # psql -d frs -f /usr/share/postgresql-8.3-postgis/lwpostgis_upgrade.sql ## odbc sudo apt-get install r-cran-rodbc sudo apt-get install unixodbc psqlodbc sudo odbcinst -i -d -f /usr/share/psqlodbc/odbcinst.ini.template sudo su cat /usr/share/doc/odbc-postgresql/examples/odbc.ini.template >>~/.odbc.ini ### edit the latter - name used to call by, db, passwd, etc. ### unixODBC doesn't do ident authentication??? exit chmod 600 ~/.odbc.ini ### tune the server ### suggestions here: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server ### and here: http://www.postgresql.org/docs/current/static/runtime-config-resource.html ### this machine has 4gb ram, services small number of concurrent connections sudo vi /etc/postgresql/8.3/main/postgresql.conf sudo sysctl -w kernel.shmmax=268435456 sudo sysctl -w kernel.shmall=4194304 sudo vi /etc/sysctl.conf ## set shmmax and shmall across reboots # pulling data from postgres into R using a custom function - not too shabby for a half a million records! # system.time((tmp=query('v_dtw', clause="limit 500000" ))) # user system elapsed # 25.502 18.277 46.285