PaleoEarthLabs wiki

One bucket of limited wisdom

User Tools

Site Tools


PostgreSQL and PostGIS

I have been using PostgreSQL and PostGIS for the past 12+ years on different versions of Mac OS X, for example to generate the BasinAtlas. This wiki page is my how-to-guide to install/update PostgreSQL/PostGIS. The current description is based on a clean install of Mac OS 10.12 (Sierra).

PostgreSQL and PostGIS on Mac OS X

For various reasons, I am currently running PostgreSQL 9.3.x on my machines with a the most recent copy of PostGIS (2.3.2).

Instead of compiling the different dependencies I decided to make the jump to Homebrew to install and manage software packages. I've previously compiled all the stuff by hand which tends to create a mess over the years, so with a clean install of a new OS, I'm giving the brewery a go. So for the following, I am assuming that homebrew is installed via:

/usr/bin/ruby -e "$(curl -fsSL"

Documentation for the brewing process can be foundhere. In particular, the ''homebrew-science'' repository contains some of the key components I need for my work:

brew install python2.7
brew install gdal2
brew install proj
brew install docbook # for the PostGIS documentation
brew install docbook-xsl

… with brew taking care of the dependencies and related libraries.

Additionally, MacTeX 2017 is installed, independently of homebrew.


Previously, I have been building PostgreSQL from source, largely out of habit but also because I am using an older version (for compatibility reasons – Uberspace's newest version is 9.3.x) and additionally some extensions such as ossp-uuid which (used to not be provided) with other pre-compiled distributions. At the stage of writing, the default available versions for PostgreSQL are 9.5 and higher.

Peter Eisentraut kindly provides PostgreSQL formulae which go back to versions that are no longer contained in the homebrew-core repository. To install an older version of PostgreSQL one needs to install a new tap via:

brew tap petere/postgresql

By issuing a:

brew install postgresql@9.3

PostgreSQL 9.3 get installed the Cellar. The formula for PostgreSQL 9.3 contains build instructions the extensions which I require for some of my projects. After installing dependencies, patching, and a bit of waiting (2mins, 31 secs to be precise) I am greeted with:

==> Summary
🍺  /usr/local/Cellar/postgresql@9.3/9.3.17: 3,200 files, 41.1MB, built in 2 minutes 31 seconds

Success! So the last step is to:

brew unlink postgresql # this removes the pg 9.6.x links in /usr/local subdirs
brew link -f postgresql@9.3

Add the version

echo 'export PATH="/usr/local/opt/postgresql@9.3/bin:$PATH"' >> ~/.bash_profile

Setting up Mac OS X for using PostgreSQL usage

As the database is to be owned by an account different than my user account, macOS comes with a _postgres user that Apple conveniently has set up. The settings — an inparticular the uid and gid — can be interrogated with the Directory Service command line utility ''dscl'':

dscl locahost read Local/Default/Users/_postgres

The output of this command with provide you with a UniqueID and PrimaryGroupID which should then own the data directory of the database cluster.

See also Stackoverflow for more info/discussion.

If you chose to alter the settings using dscl make sure to check existing users/group accounts so that they do not overlap with your new onw. On Mac OS user-ids < 500 indicate non-standard users and daemons are usually prefixed with an underscore _.

Then, generate a new directory to write the PostgreSQL database cluster to. It is a good idea to hide this directory from normal users, so the classic Unix locations of /usr/local or /var/ are quite good choices. I initiate the new db cluster using:

sudo mkdir /path/to/dbcluster/directory
sudo chown uid:gid /path/to/dbcluster/directory
sudo -u <ThePostgresUser> initdb --username=YourSuperuserName  --pwprompt --pgdata=/path/to/dbcluster/directory

where uid and gid are the postgres User UserID and GroupID, respectively. By providing a superuser/password one can save a bit of setup time. The output of the command should result into something like this below:

Success. You can now start the database server using:
    /usr/local/bin/postgres -D /path/to/dbcluster/
    /usr/local/bin/pg_ctl -D /path/to/dbcluster/ -l logfile start

Yo. All set up, ready to roll - with the exception of adding a service to launch and shutdown the postgres daemon when starting up or shutting down the machine. This is done using Apple's launchd tool and a LaunchAgent. Chartio provides a good walkthrough of the process. In my case, I want to create a service which is run as daemon (not agent! - the latter is on a per-user base), system-wide, regardless of the user.

The installation via brew should provide a *.plist file that can be used. In the earlier default postgresql version it was located here: /usr/local/opt/postgresql/homebrew.mxcl.postgresql.plist, I couldn't find the equivalent in the 9.3.x distribution. Anyway, the .plist file can be easily set up manually - here's a template, adjust to your specific settings (logfile locations, postgresql owner, etc.):

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "">
<plist version="1.0">
	<string>PostgreSQL Server</string>

The file is called org.postgresql.postgres.plist, if the db should be started at system boot time, then the .plist file needs to be moved to /Library/LaunchDaemons/, owned by root:wheel and the log file output directories need to have write permissions for the postgresql owner. I made the grave mistake by copying an older .plist file in which I used postmaster as call for the server routine m(, however, this is deprecated and replaced by the postgres utility. Once this is all done the service should be able to be loaded (so that it is kicked off at boot time) and started using the launchd process:

sudo launchctl load -w /Library/LaunchDaemons/org.postgresql.postgres.plist 
sudo launchctl start org.postgresql.postgres.plist 

In the postgresql log file (location as specified in the *.plist file) should show something like this:

LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started


Like PostgreSQL above, PostGIS can be easily installed with homebrew. At the time of writing, homebrew installs PostGIS version 2.3.2. I expected that homebrew would automagically cater for my slightly different setup, however, that was not the case. After tinkering a bit with the set up, I tried altering the formula, but in the end I reverted to compiling PostGIS from source. To have a clean start:

brew uninstall postgis && brew install postgis

While tinkering with the PostGIS formula the cookbook provides invaluable help. Use brew edit postgis to edit the brew formula for postgis (kept in /usr/local/Homebrew/Library/Taps/homebrew/homebrew-core/Formula/), I ended up with the following after a bit of trial-and-error:

class Postgis < Formula
  desc "Adds support for geographic objects to PostgreSQL"
  homepage ""
  url ""
  sha256 "e92e34c18f078a3d1a2503cd870efdc4fa9e134f0bcedbbbdb8b46b0e6af09e4"
  bottle do
    cellar :any
    sha256 "cea4e412efe966694749f6e1feaa11db1dd47970a9f6ac63afd1765b50f56d85" => :sierra
    sha256 "83a1e64c57c69d4e85a1678e772798b2cd04aaba26ab5ce75b678d41d7bc6cf7" => :el_capitan
    sha256 "719efe3d8589e4923ff5a89e542df813053b59695b9d16f1cb2eb88db93e62ce" => :yosemite
  head do
    url ""
    depends_on "autoconf" => :build
    depends_on "automake" => :build
    depends_on "libtool" => :build
  option "with-gui", "Build shp2pgsql-gui in addition to command line tools"
  option "without-gdal", "Disable postgis raster support"
  option "with-html-docs", "Generate multi-file HTML documentation"
  option "with-api-docs", "Generate developer API documentation (long process)"
  depends_on "pkg-config" => :build
  depends_on "gpp" => :build
  depends_on "postgresql\@9.3"
  depends_on "proj"
  depends_on "geos"
  depends_on "gtk+" if build.with? "gui"
  # For GeoJSON and raster handling
  depends_on "json-c"
  depends_on "gdal2" => :recommended
  depends_on "pcre" if build.with? "gdal"
  # For advanced 2D/3D functions
  depends_on "sfcgal" => :recommended
  if build.with? "html-docs"
    depends_on "imagemagick"
    depends_on "docbook-xsl"
  if build.with? "api-docs"
    depends_on "graphviz"
    depends_on "doxygen"
  def install
    args = [
      # Unfortunately, NLS support causes all kinds of headaches because
      # PostGIS gets all of its compiler flags from the PGXS makefiles. This
      # makes it nigh impossible to tell the buildsystem where our keg-only
      # gettext installations are.
    args << "--with-gui" if build.with? "gui"
    args << "--without-raster" if build.without? "gdal"
    args << "--with-xsldir=#{Formula["docbook-xsl"].opt_prefix}/docbook-xsl" if build.with? "html-docs"
    system "./" if build.head?
    system "./configure", *args
    system "make"
    if build.with? "html-docs"
      cd "doc" do
        ENV["XML_CATALOG_FILES"] = "#{etc}/xml/catalog"
        system "make", "chunked-html"
        doc.install "html"
    if build.with? "api-docs"
      cd "doc" do
        system "make", "doxygen"
        doc.install "doxygen/html" => "api"
    mkdir "stage"
    system "make", "install", "DESTDIR=#{buildpath}/stage"
    bin.install Dir["stage/**/bin/*"]
    lib.install Dir["stage/**/lib/*"]
    include.install Dir["stage/**/include/*"]
    ("postgresql\@9.3/9.3.17/share/doc/extension").install Dir["stage/**/share/doc/postgresql/extension/*"]
    (share/"postgresql\@9.3/9.3.17/extension").install Dir["stage/**/share/postgresql/share/extension/*"]
    pkgshare.install Dir["stage/**/contrib/postgis-*/*"]
    (share/"postgis_topology").install Dir["stage/**/contrib/postgis_topology-*/*"]
    # Extension scripts
    bin.install %w[
    man1.install Dir["doc/**/*.1"]
  def caveats
      To create a spatially-enabled database, see the documentation:
      If you are currently using PostGIS 2.0+, you can go the soft upgrade path:
        ALTER EXTENSION postgis UPDATE TO "#{version}";
      Users of 1.5 and below will need to go the hard-upgrade path, see here:
      PostGIS SQL scripts installed to:
      PostGIS plugin libraries installed to:
      PostGIS extension modules installed to:
  test do
    require "base64"
    (testpath/"brew.shp").write ::Base64.decode64 <<-EOS.undent
    (testpath/"brew.dbf").write ::Base64.decode64 <<-EOS.undent
    (testpath/"brew.shx").write ::Base64.decode64 <<-EOS.undent
    result = shell_output("#{bin}/shp2pgsql #{testpath}/brew.shp")
    assert_match /Point/, result
    assert_match /AddGeometryColumn/, result

See here regarding the use of `gdal2` vs. `gdal`.

A try with:

brew install  --ignore-dependencies --build-from-source postgis

resulted in issues with the compilation not being able to find libintl.h - this Stackoverflow post worked for me to fix:

brew reinstall gettext
brew unlink gettext && brew link gettext --force

Then, some other errors popped up and I simply used the PostGIS 2.3.2 source code downloaded via brew (in ~/Library/Caches/Homebrew) to compile from source:

./configure --with-projdir=/usr/local/opt/proj --with-jsondir=/usr/local/opt/json-c --with-pgconfig=/usr/local/opt/postgresql@9.3/bin/pg_config --with-xsldir=/usr/local/Cellar/docbook-xsl/1.79.1/docbook-xsl 
make -j24
make install

The result was a properly installed PostGIS which could be loaded into my database using the extensions:


Restoring data

Using the standard utilities that come with PostgreSQL, I have dumped an archive of the earlier database using

pg_dump -Fc > sqlfile.pgdump

Once the database is up and running, this archive file can be used to generate either a new database or loaded into the spatially enabled database using:

pg_restore -U <user> -d <db> -Fc -v -c -j 24 backupFile.pgdump

PostgreSQL and PostGIS on Windoze

software/postgresql.txt · Last modified: 2017/06/05 21:32 by christian