(i recently had to piece this together for Linux Mint 12 KDE, i expect ubuntu should be identical. You can also refer to http://postgis.refractions.net/documentation/manual-1.4/ch02.html and http://postgis.17.n6.nabble.com/Installing-a-fully-functional-PostGIS-2-0-on-Ubuntu-Linux-GEOS-GDAL-issues-td3566227.html)
Install and configure PostgreSQL (v9.1)
install via Synaptic or
sudo apt-get update
sudo apt-get install build-essential
sudo apt-get install python-dev libreadline-dev
sudo apt-get install postgresql-9.1 postgresql-server-dev-9.1 postgresql-contrib-9.1 postgresql-plpython-9.1
change password method 1
sudo passwd postgres
sudo -u postgres psql postgres
enter password twice on prompts
at psql prompt
postgres # \password postgres
enter password twice on prompts
OR
change password method 2
sudo -u postgres psql
postgres # ALTER ROLE postgres WITH ENCRYPTED PASSWORD 'newpassword';
postgres # \q
Install and configure pgAdmin3 (v1.14)
default distro repo only has v1.12 which lacks support for the Postgres 9.1 maintenance features, so instead, do the following:
add apt keyring using standard port (firewall workaround)
gpg --keyserver hkp://keyserver.ubuntu.com:80 --recv 1F9ADD375CA44993
gpg --export --armor 1F9ADD375CA44993 | sudo apt-key add -
add ppa
sudo add-apt-repository ppa:rhonda/pgadmin3
using nano, open ppa list and replace oneiric with natty (this is cheating, but it works), then apt-get update:
sudo nano /etc/apt/sources.list.d/rhonda-pgadmin3-oneiric.list
sudo apt-get update
pgAdmin3 v1.14 will now be available to Synaptic, or
sudo apt-get install pgadmin3=1.14.0~beta1-1~ppa1~natty1
Setup pgAdmin3 bin location. Launch pgAdmin3, File > Options > PG bin: /usr/lib/postgresql/9.1/bin/
Install and configure PostGIS (v1.5)
install via Synaptic or
sudo apt-get install postgresql-9.1-postgis
then
createdb tmplt_postgis
psql -d tmplt_postgis -f /usr/share/postgresql/9.1/contrib/postgis-1.5/postgis.sql
psql -d tmplt_postgis -f /usr/share/postgresql/9.1/contrib/postgis-1.5/spatial_ref_sys.sql
psql -d tmplt_postgis -f /usr/share/postgresql/9.1/contrib/postgis_comments.sql
(if these paths are incorrect, check Synaptic > postgresql-9.1-postgis > rclick > properties > installed files)
If postgis was compiled with shp2pgsql-gui then add the plugin to pgadmin3. (i think this method is hamfisted, oh well. All you need is a text ini file as shown below)
sudo mkdir /usr/share/pgadmin3/plugins.d
su
cat > /usr/share/pgadmin3/plugins.d/postgis.shp2pgsql-gui.ini
;
; pgShapeLoader (Linux):
;
Title=PostGIS Shapefile and DBF loader
Command=$$PGBINDIR/shp2pgsql-gui -U $$USERNAME -d $$DATABASE -p $$PORT -h $$HOSTNAME
Description=Open a PostGIS ESRI Shapefile or Plain dbf loader console to the current database.
KeyFile=$$PGBINDIR/shp2pgsql-gui
Platform=unix
ServerType=postgresql
Database=Yes
SetPassword=No
[Ctrl-D] to exit input mode
# exit
to exit su
Done.
See this help document for creating multiple connections.
This simple code will create multiple connections:
EXEC SQL CONNECT TO testdb1 AS con1 USER testuser;
EXEC SQL CONNECT TO testdb2 AS con2 USER testuser;
EXEC SQL CONNECT TO testdb3 AS con3 USER testuser;
You can then choose a connection to use:
EXEC SQL AT connection-name SELECT ...;
Finally you need to disconnect all of your connections:
EXEC SQL DISCONNECT ALL;
This code will automatically make use of multiple threads and doesn't require you to create redundancies, such as duplicate tables.
Best Answer
Let me guess: Your cpu has 4 cores, so 25% cpu usage, is 100% usage of one core, and 3 idle cores.
So only solution is to make the code multi threaded, but that is no simple task.