installing postgres & postgis

Installing Postgres & Postgis

John Ashmead

Mostly on a Mac, but also on Linux

& Windows

Installing Postgres & PostGIS on a Mac - November 20th, 2012 http://www.ashmeadsoftware.com

Why?

•Building a mapping website!

•Wanted Postgres 9.2 to go pure Javascript: client, server (node.js), & database!!

•And wanted latest & greatest mapping

http://www.timeandquantummechanics.com


Objective

•Postgres 9.2!

•Postgis 2.0 http://www.postgis.org/!

•psql, pgAdmin3, & phpPgAdmin!

•Node.js!

•PL/Javascript


http://www.postgis.org/


Stack Builder



Strategies

•EnterpriseDB - provides Stack Builder not currently current, but good starting point!

•DMGs - not always current, but good intel!

•Open source: fink, homebrew, macports - can be hacked, instructive



EnterpriseDB

% sudo vi /etc/sysctl.conf !On a MacBook Pro with 2GB of RAM, the author's sysctl.conf contains: !kern.sysv.shmmax=1610612736 kern.sysv.shmall=393216 kern.sysv.shmmin=1 kern.sysv.shmmni=32 kern.sysv.shmseg=8 kern.maxprocperuid=512 kern.maxproc=2048



Kyngchaos•Stackbuilder!

•Not quite current: when did first, was Postgres 9.1, PostGIS 1.5. (Now is 9.1, but 2.0.0)!

•But lots of good information, i.e. PostGIS has lots of dependencies: geos, proj4, some others



Xcode



Open source packagers

•Fink - precompiled binaries preferred. Not as up to date. Not current enough.!

•Homebrew - easier to use. On github. Uses ruby. Less intrusive. Assumes single user.!

•Macports - tends to pull a lot of stuff down. Possibly more general. Used here. http://www.macports.org/


http://www.macports.org/


Portfileshttps://trac.macports.org/browser/trunk/dports/databases/postgis2/Portfile variant postgresql92 description {Builds with postgresql 9.2} \ conflicts postgresql90 postgresql91 { depends_lib-append port:postgresql92 archcheck.files-append lib/postgresql92/libpq.dylib configure.args-append --libdir=${prefix}/lib/postgresql92\ --with-pgconfig=${prefix}/lib/postgresql92/bin/pg_config build.args-append PGSQL_DOCDIR=${destroot}${prefix}/share/doc/postgresql91\ PGSQL_MANDIR=${destroot}${prefix}/share/man } !


https://trac.macports.org/browser/trunk/dports/databases/postgis2/Portfile


Port commands

port list > /dev/null Warning: port definitions are more than two weeks old, consider using selfupdate port selfupdate [ port upgrade postgresql92 skipped ] port -v install postgresql91 +perl +tcl +python port -v install postgresql91-doc port install pgAdmin3 +postgresql91 +with_postgresql91 port install postgis2 +postgresql91 +raster +gui +topology



Daemondo

<key>ProgramArguments</key> <array> <string>/opt/local/bin/daemondo</string> <string>--label=vm-pop3d</string> <string>--start-cmd</string> <string>/opt/local/sbin/vm-pop3d</string> <string>-d</string> <string>10</string> <string>-t</string> <string>600</string> <string>;</string> </array>



Launch Daemons



Processes

507 7714 1 0 5:22PM ?? 0:00.04 /Library/PostgreSQL/9.2/bin/postmaster -D/Library/PostgreSQL/9.2/data 507 7715 7714 0 5:22PM ?? 0:00.00 postgres: logger process 507 7717 7714 0 5:22PM ?? 0:00.01 postgres: checkpointer process 507 7718 7714 0 5:22PM ?? 0:00.05 postgres: writer process 507 7719 7714 0 5:22PM ?? 0:00.03 postgres: wal writer process 507 7720 7714 0 5:22PM ?? 0:00.05 postgres: autovacuum launcher process 507 7721 7714 0 5:22PM ?? 0:00.07 postgres: stats collector process ~



Profile variablesvi .profile # to change back to Postgres 9.1, fix path & set PGPORT to 5432, export PATH=".:/opt/local/lib/postgresql92/bin:/Library/PostgreSQL/9.2/bin:$PATH” !export PGDATABASE=talkinvite export PGUSER=talkinvite export PGPORT=5433

vi phpPgAdmin/conf/config.inc.php # to change back to Postgres 9.1, fix path & set PGPORT to 5432, // Database port on server (5432 is the PostgreSQL default) // Using 5433 for Postgres 9.2 $conf['servers'][0]['port'] = 5433;

vi apache/conf/httpd.conf # ServerRoot "/Library/PostgreSQL/EnterpriseDB-ApachePHP/apache" # Listen: Allows you to bind Apache to specific IP addresses and/# Listen 8081



Verify

•Check psql, pgAgent3, pgPhpAdmin (using apache)!

•Bounce box, make sure it comes back!

•Hand save some simple maps, then recover



Linux/Parallels



Pre-Requisites for PostGIS

•Obvious ones (database, compiler, …)!

•PROJ4 - makes round things flat!

•GEOS - “fairly safe to upgrade”!

•LibXML2 - what may have broken!

•JSON-C - JSON support for GEO!

•GDAL - raster support



Of the 36 strategies, running away is the best

•Loaded in Postgres 9.1, PostGIS 1.5!

•Tried upgrade to postGIS 2.0 using various tricks on web. Kept hitting failures, missing bits & so on, dead parrots from previous runs, !

•Ask Bruce & Jim



Windows/VMWare



Download latestFixed www/phppgadmin/conf/config.inc.php to use port 5433

Ran “select version()” to get:

select postgis_full_version() -> POSTGIS="2.0.1 r9979" GEOS="3.3.5-CAPI-1.7.5" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.1, released 2012/05/15" LIBXML="2.7.8" LIBJSON="UNKNOWN" RASTER



Node.js



Fun with maps

•PostGIS

•Javascript & node

•Open source maps - MapServer, PostGIS

•MVC & mapping libraries

•A plan, ney, a map



Checklist

•Software downloads: node, npm, express, … !

•Setup hackable app: my what lovely stack traces we are showing today…!

•Verify hackable’s access to postgres!

•Find & load in initial spatial data!

•Manage spatial data (leaflet)!

•Create MVC wrappers for all this GIS goodness



Recommendations (your mileage may vary)

•Go with packages, defaults when you can!

•If you can’t, build up in layers.!

•Trust but verify!

•Learn a bit more than you need to; ask around!

•Keep a log


installing postgres & postgis

Self Improvement