Thursday, August 12, 2010

PostgreSQL Portable

I just wanted a portable PostgreSQL (on windows) not for development but for distribution. This means that I only care about the data directory being portable.

Making a portable postgreSQL
To make the latest version portably. Heres what I had to do:

  • Just downloaded the latest stable postgreSQL windows zip file (
  • Extracted to a folder.
  • Made a folder called "data" inside the "pgsql" folder and made sure current logged in user (administrator btw) had full rights to the folder.
  • CDed to the pgsql\bin folder
  • ran the command:
    initdb.exe -D "../data" -U postgres -W
  • Type in the password you want for postgres superuser (lets say you put in password) and reenter to verify as prompted. 
  • In the end of the above command I could read the following:
    Success. You can now start the database server using:
        "postgres" -D "../data"
        "pg_ctl" -D "../data" -l logfile start
  • All done. Now you can start the database using the following command (be sure to leave the command prompt open after this command because as soon as you close it the server will terminate):
    pg_ctl -D "../data" start
  • You can shut down the database using the following command:
    pg_ctl -D "../data" stop
You can now connect to the database using pgadmn3 in the bin folder. If you do not need PostGIS support you are all set :) In case you do ... continue reading :)

Adding PostGIS support to the portable postgresql
I downloaded the latest version of postgis from here : PostGIS 1.5.2 release for PostgreSQL 9.0 for 32-bit Windows mentioned on this page.

  • I extracted it so that my directory structure was as follows:

  • Copy all the files from postgis to pgsql 
  • Edit "makepostgisdb.bat"  as follows (the sections in bold italics are the ones I modified / added):
set PGPORT=5432
set PGHOST=localhost
set PGUSER=postgres
set PGPASSWORD=password
set THEDB=optimate
set PGBIN=.\bin\
"./bin/pg_ctl" -D "./data" stop
"./bin/pg_ctl" -D "./data" start
"%PGBIN%\psql"  -d "%THEDB%" -c "CREATE LANGUAGE plpgsql"
"%PGBIN%\psql"  -d "%THEDB%" -f "share\contrib\postgis-1.5\postgis.sql"
"%PGBIN%\psql"  -d "%THEDB%" -f "share\contrib\postgis-1.5\spatial_ref_sys.sql"
"%PGBIN%\psql"  -d "%THEDB%" -f "share\contrib\postgis-1.5\postgis_comments.sql"
"./bin/pg_ctl" -D "./data" stop

REM Uncomment the below line if this is a template database
REM "%PGBIN%\psql" -d "%THEDB%" -c "UPDATE pg_database SET datistemplate = true WHERE datname = '%THEDB%';GRANT ALL ON geometry_columns TO PUBLIC; GRANT ALL ON spatial_ref_sys TO PUBLIC"


  • Execute makepostgisdb.bat by double clicking it. Eventually you should get a screen like this:
  • At this stage you have a new PostGIS enabled database (optimate) available to you!
  • Finally copy the plugins.ini file from the pgsql folder to pgsql\pgAdmin III folder overriting the file already there. Now you have configured a cool Shp2pgsql-gui tool as a plugin for pgAdmin3 :)  You can verify it by running pgadmin3. You will see it in plugins as shown below:

Some useful bat files
Additionally here are some command files I use for postgreSQL (place in the main pgsql folder):

"./bin/pg_ctl" -D "./data" start

"./bin/pg_ctl" -D "./data" stop

Updated on 24th June 2011 adding documentation for postgis + some useful bat files.