PostgreSQL and PostGIS

This guide is for installing PostgreSQL 9.3.x and PostGIS 2.3.2. and superseded by one for Postgresql 11.3 and PostGIS 2.5.2. which can be accessed here

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 https://raw.githubusercontent.com/Homebrew/install/master/install)"

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.

PostgreSQL

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/
or
    /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.):

org.postgresql.postgres.plist

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
	<key>GroupName</key>
	<string>YourPostgresOwner</string>
	<key>Label</key>
	<string>org.postgresql.postgres</string>
	<key>ProgramArguments</key>
	<array>
		<string>/usr/local/opt/postgresql@9.3/bin/postgres</string>
		<string>-D</string>
		<string>/path/to/your/dbcluster</string>
	</array>
	<key>RunAtLoad</key>
	<true/>
	<key>ServiceDescription</key>
	<string>PostgreSQL Server</string>
	<key>StandardErrorPath</key>
	<string>/var/log/postgresql/postgresql.errors.log</string>
	<key>StandardOutPath</key>
	<string>/var/log/postgresql/postgresql.log</string>
	<key>UserName</key>
	<string>YourPostgresqUser</string>
</dict>
</plist>

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 , 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 Console.app 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

PostGIS

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:

postgis.rb

class Postgis < Formula
  desc "Adds support for geographic objects to PostgreSQL"
  homepage "https://postgis.net/"
  url "http://download.osgeo.org/postgis/source/postgis-2.3.2.tar.gz"
  sha256 "e92e34c18f078a3d1a2503cd870efdc4fa9e134f0bcedbbbdb8b46b0e6af09e4"
 
  bottle do
    cellar :any
    sha256 "cea4e412efe966694749f6e1feaa11db1dd47970a9f6ac63afd1765b50f56d85" => :sierra
    sha256 "83a1e64c57c69d4e85a1678e772798b2cd04aaba26ab5ce75b678d41d7bc6cf7" => :el_capitan
    sha256 "719efe3d8589e4923ff5a89e542df813053b59695b9d16f1cb2eb88db93e62ce" => :yosemite
  end
 
  head do
    url "https://svn.osgeo.org/postgis/trunk/"
 
    depends_on "autoconf" => :build
    depends_on "automake" => :build
    depends_on "libtool" => :build
  end
 
  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"
  end
 
  if build.with? "api-docs"
    depends_on "graphviz"
    depends_on "doxygen"
  end
 
  def install
    ENV.deparallelize
 
    args = [
      "--with-projdir=#{Formula["proj"].opt_prefix}",
      "--with-jsondir=#{Formula["json-c"].opt_prefix}",
      "--with-pgconfig=#{Formula["postgresql@9.3"].opt_bin}/pg_config",
      "--with-geosconfig=#{Formula["geos"].opt_bin}/geos-config",
      "--with-gdalconfig=#{Formula["gdal2"].opt_bin}/gdal-config",
      # 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.
      "--disable-nls",
    ]
 
    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 "./autogen.sh" 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"
      end
    end
 
    if build.with? "api-docs"
      cd "doc" do
        system "make", "doxygen"
        doc.install "doxygen/html" => "api"
      end
    end
 
    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[
      utils/create_undef.pl
      utils/postgis_proc_upgrade.pl
      utils/postgis_restore.pl
      utils/profile_intersects.pl
      utils/test_estimation.pl
      utils/test_geography_estimation.pl
      utils/test_geography_joinestimation.pl
      utils/test_joinestimation.pl
    ]
 
    man1.install Dir["doc/**/*.1"]
  end
 
  def caveats
    <<-EOS.undent
      To create a spatially-enabled database, see the documentation:
        https://postgis.net/docs/manual-2.2/postgis_installation.html#create_new_db_extensions
      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:
        https://postgis.net/docs/manual-2.2/postgis_installation.html#upgrading
 
      PostGIS SQL scripts installed to:
        #{opt_pkgshare}
      PostGIS plugin libraries installed to:
        #{HOMEBREW_PREFIX}/lib
      PostGIS extension modules installed to:
        #{HOMEBREW_PREFIX}/share/postgresql/extension
      EOS
  end
 
  test do
    require "base64"
    (testpath/"brew.shp").write ::Base64.decode64 <<-EOS.undent
      AAAnCgAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAoOgDAAALAAAAAAAAAAAAAAAA
      AAAAAADwPwAAAAAAABBAAAAAAAAAFEAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
      AAAAAAAAAAAAAAAAAAEAAAASCwAAAAAAAAAAAPA/AAAAAAAA8D8AAAAAAAAA
      AAAAAAAAAAAAAAAAAgAAABILAAAAAAAAAAAACEAAAAAAAADwPwAAAAAAAAAA
      AAAAAAAAAAAAAAADAAAAEgsAAAAAAAAAAAAQQAAAAAAAAAhAAAAAAAAAAAAA
      AAAAAAAAAAAAAAQAAAASCwAAAAAAAAAAAABAAAAAAAAAAEAAAAAAAAAAAAAA
      AAAAAAAAAAAABQAAABILAAAAAAAAAAAAAAAAAAAAAAAUQAAAAAAAACJAAAAA
      AAAAAEA=
    EOS
    (testpath/"brew.dbf").write ::Base64.decode64 <<-EOS.undent
      A3IJGgUAAABhAFsAAAAAAAAAAAAAAAAAAAAAAAAAAABGSVJTVF9GTEQAAEMA
      AAAAMgAAAAAAAAAAAAAAAAAAAFNFQ09ORF9GTEQAQwAAAAAoAAAAAAAAAAAA
      AAAAAAAADSBGaXJzdCAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
      ICAgICAgICAgICAgIFBvaW50ICAgICAgICAgICAgICAgICAgICAgICAgICAg
      ICAgICAgICAgU2Vjb25kICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
      ICAgICAgICAgICAgICBQb2ludCAgICAgICAgICAgICAgICAgICAgICAgICAg
      ICAgICAgICAgIFRoaXJkICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
      ICAgICAgICAgICAgICAgUG9pbnQgICAgICAgICAgICAgICAgICAgICAgICAg
      ICAgICAgICAgICBGb3VydGggICAgICAgICAgICAgICAgICAgICAgICAgICAg
      ICAgICAgICAgICAgICAgIFBvaW50ICAgICAgICAgICAgICAgICAgICAgICAg
      ICAgICAgICAgICAgQXBwZW5kZWQgICAgICAgICAgICAgICAgICAgICAgICAg
      ICAgICAgICAgICAgICAgICBQb2ludCAgICAgICAgICAgICAgICAgICAgICAg
      ICAgICAgICAgICAg
    EOS
    (testpath/"brew.shx").write ::Base64.decode64 <<-EOS.undent
      AAAnCgAAAAAAAAAAAAAAAAAAAAAAAAAAAAAARugDAAALAAAAAAAAAAAAAAAA
      AAAAAADwPwAAAAAAABBAAAAAAAAAFEAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
      AAAAAAAAAAAAAAAAADIAAAASAAAASAAAABIAAABeAAAAEgAAAHQAAAASAAAA
      igAAABI=
    EOS
    result = shell_output("#{bin}/shp2pgsql #{testpath}/brew.shp")
    assert_match /Point/, result
    assert_match /AddGeometryColumn/, result
  end
end

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:

CREATE EXTENSION "postgis";

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