Getting my web development stack back in order after upgrading to Leopard has been a little hit-or-miss. Now, in preparation for some geocoding work, I’m adding PostgreSQL and PostGIS to the mix. Here’s what I had to do to get stuff working. I haven’t tested it extensively yet, but I’ll update this post as necessary.

Update: I’m getting some strange bus errors from PostgreSQL (signal 10)… I’d eventually like to get the ports method working, but for now, I’m going to use the prepackaged binaries. Also of note these required ported frameworks.

PostgreSQL

  • Install PostgreSQL
    $ sudo port -k install postgresql82 postgresql82-server
    (‘-k’ doesn’t do cleanup… just in case I want the work files later in the install process)

  • Make the default DB directory, set the owner to the postgres user, and initialize the DB
    $ sudo mkdir -p /opt/local/var/db/postgresql82/defaultdb
    $ sudo chown postgres:postgres /opt/local/var/db/postgresql82/defaultdb
    $ sudo su postgres -c '/opt/local/lib/postgresql82/bin/initdb -D /opt/local/var/db/postgresql82/defaultdb'
    (you could replace ‘postgres’ with something else if you want another user to be the postgres user)

  • Add PostgreSQL server as a startup item
    $ sudo launchctl load -w /Library/LaunchDaemons/org.macports.postgresql82-server.plist
    (add startup info)

  • Add PostgreSQL executables to your path:
    $ PATH=${PATH}:/opt/local/lib/postgresql82/bin

  • Create a DB user
    $ createuser -U postgres -s -P -E <name-of-superuser>
    (If you set the postgres user to something other that ‘postgres’ that should be reflected above.)

PostGIS

  • Update the ports system
    $ sudo port selfupdate

  • Navigate to the location of the PostGIS portfile
    $ /opt/local/var/macports/sources/rsync.macports.org/release/ports/databases/postgis

  • Change the line in the portfile that reads:

    build.args “ICONV_LDFLAGS=\”-L${prefix}/lib -liconv\””

    to

    build.args “ICONV_LDFLAGS=\”-L$/usr/lib -liconv\””

    (OS-X already has the iconv library and I believe this will work fine. Using the ports version breaks.)

  • Run the port installation procedure
    $ sudo port -k install postgis
    (without ‘-k’ in this case, the port will break before finishing instal)

Creating a PostGIS template

The following is adapted from Rob Braswell’s instructions. This allows non-superusers to create spatial databases using a template.

  • Connect to the template database
    $ psql template1

  • Execute the following commands:
    template1=# create database template_postgis with template = template1;
    template1=# UPDATE pg_database SET datistemplate = TRUE where datname = 'template_postgis';

  • Connect to the new template_postgis database:
    template1=# \c template_postgis

  • Add PostGIS extensions and grant access to everyone to spatial tables:
    template_postgis=# CREATE LANGUAGE plpgsql;
    template_postgis=# \i /opt/local/share/postgis/lwpostgis.sql;
    template_postgis=# \i /opt/local/share/postgis/spatial_ref_sys.sql;
    template_postgis=# GRANT ALL ON geometry_columns TO PUBLIC;
    template_postgis=# GRANT ALL ON spatial_ref_sys TO PUBLIC;

  • Prevent further modifications to the template_postgis database:
    template_postgis=# VACUUM FREEZE;

  • Quit out of psql (^D or \q)

  • Create a test database using the new template_postgis template…
    $ createdb test_gis_db -T template_postgis
    …and drop it again.
    $ dropdb test_gis_db

Cleanup PostgreSQL/PostGIS installation files

  • Let’s get rid of the work files associated with the installation above:
    $ sudo port clean postgresql82 postgresql82-server postgis

Ruby and Rails

  • Install Ruby library for PostgreSQL
    $ sudo gem install postgres

  • Install GeoRuby
    $ sudo gem install georuby

Take a look at this GeoRuby and Rails tutorial for more rails integration info. I’ll update this post once i get to that stage.

December 4, 2007 · Uncategorized · · [Print]

9 Comments to “Installing PostgreSQL, PostGIS and more on OS-X Leopard”

  1. flo says:

    i installed postgre the way you did, but when i try to add a user, it always says:

    createuser: could not connect to database postgres: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket “/tmp/.s.PGSQL.5432

    any idea of what i did wrong? thanks

  2. Lincoln says:

    I’m not positive, but I my guess is that the server hasn’t been started. Try: “sudo su postgres -c ‘pg_ctl start -D /usr/local/pgsql/data’”

    However, I would actually follow the other instructions on this blog as noted at the top of this post:

    http://www.lincolnritter.com/blog/2007/12/04/leopard-postgresql-and-postgis-from-kyngchaoscom/

  3. Wow, thanks! Things appear to be working, but I ran into a snag and I thought I’d share the fix :

    If you get an error about ARCHFLAGS not being set when installing the postgres gem, try this :

    sudo env ARCHFLAGS=’-arch i386′ gem install postgres (or -arch ppc if you’re not on an Intel Mac).

    Thanks for the great post.

  4. Stef says:

    Hi there,

    thanks for that. Very helpful. Do you have any idea how I can prevent from installing the functions into schema “public”? I want to have a schema “postgis” where these functions are being stored…

    Thanks for any hints.

  5. Aditya says:

    We were getting Signal 10 errors too – manually updating the portfile to build postgis 1.3.2 as opposed to 1.3.1 fixed that.

  6. CR says:

    Hi, I tried to install both postres and postGIS with the pre-packaged binaries, and the postgres install puts it in an unexpected place (/Library) where as the postGIS looks in /usr/local which seems to be the normal default. Did you have any issue or have any ideas what to do. I’d install postgis as a fink package, but only seems available as source. And I’m pretty new to this stuff. Thanks!

  7. Alex says:

    Hi, good morning to all of you… Nice Guestbook ;-) !!!m

  8. Elvis says:

    Many interesting information on your site – keep up good work

  9. Dughera says:

    The phone works and the texting works, but the internet (email, browsing, etc…) does not work! My buddy and I sit right next to each other, he has edge (the older iphone) and i have the 3g but neither can contact the cellular network when trying to use e-mail or the web browser. Anyone else having this problem? or does anyone got a clue to why this may be? thanks!

    [url=http://www.youtube.com/watch?v=UAnGCJQnPoo]unlock iphone 3gs[/url]

Leave a Reply