====== PostgreSQL and PostGIS ======

<note warning>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 [[software:postgresql:pg12|here]]</note>

I have been using PostgreSQL and PostGIS for the past 12+ years on different versions of Mac OS X, for example to generate the [[https://www.basinatlas.org|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 [[http://postgresql.org|PostgreSQL 9.3.x]] on my machines with a the most recent copy of [[http://postgis.net|PostGIS (2.3.2)]].

Instead of compiling the different dependencies I decided to make the jump to [[https://brew.sh/|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:

<code bash>
/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
</code>

Documentation for the brewing process can be found[[http://docs.brew.sh/|here]]. In particular, the [[http://braumeister.org/repos/Homebrew/homebrew-science/browse/a|''homebrew-science'' repository]] contains some of the key components I need for my work:

<code bash>
brew install python2.7
brew install gdal2
brew install proj
brew install docbook # for the PostGIS documentation
brew install docbook-xsl
</code>

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

Additionally, [[https://www.tug.org/mactex/|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 -- [[http://uberspace.de|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.

[[https://github.com/petere/|Peter Eisentraut]] kindly provides [[https://github.com/petere/homebrew-postgresql|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:

<code bash>
brew tap petere/postgresql
</code>

By issuing a:
<code bash>
brew install postgresql@9.3
</code>

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:

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

Success! So the last step is to:

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

Add the version 
<code bash>
echo 'export PATH="/usr/local/opt/postgresql@9.3/bin:$PATH"' >> ~/.bash_profile
</code>

=== 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 [[https://developer.apple.com/legacy/library/documentation/Darwin/Reference/ManPages/man1/dscl.1.html|Directory Service command line utility ''dscl'']]:

<code bash>
dscl locahost read Local/Default/Users/_postgres
</code>

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 [[http://stackoverflow.com/questions/6814127/lion-update-removed-the-postgres-user-how-to-restore-it/6815565#6815565|Stackoverflow]] for more info/discussion.

<note important>
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 ''_''.  
</note>

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: 

<code bash>
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
</code>

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:

<code bash>
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
</code>

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 [[https://developer.apple.com/legacy/library/documentation/Darwin/Reference/ManPages/man8/launchd.8.html|Apple's launchd]] tool and a [[http://www.launchd.info|LaunchAgent]]. [[https://chartio.com/resources/tutorials/how-to-start-postgresql-server-on-mac-os-x/|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.):

<file xml 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>
</file>

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:

<code bash>
sudo launchctl load -w /Library/LaunchDaemons/org.postgresql.postgres.plist 
sudo launchctl start org.postgresql.postgres.plist 
</code>

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

<code log>
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started
</code>

==== PostGIS ====

Like [[http://www.postgresql.org|PostgreSQL]] above, [[http://postgis.net|PostGIS]] can be easily installed with homebrew. At the time of writing, homebrew installs [[http://postgis.net/source/|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: 

<code bash>
brew uninstall postgis && brew install postgis
</code>

While tinkering with the PostGIS formula the [[http://docs.brew.sh/Formula-Cookbook.html|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:

<file ruby 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
</file>

See here [[https://github.com/OSGeo/homebrew-osgeo4mac/issues/185|regarding the use of `gdal2` vs. `gdal`]].

A try with:

<code bash>
brew install  --ignore-dependencies --build-from-source postgis
</code>

resulted in issues with the compilation not being able to find ''libintl.h'' - this [[https://stackoverflow.com/questions/11370684/what-is-libintl-h-and-where-can-i-get-it#11370808|Stackoverflow post worked for me]] to fix:

<code bash>
brew reinstall gettext
brew unlink gettext && brew link gettext --force
</code>

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:

<code bash>
./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
</code>

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

<code sql>
CREATE EXTENSION "postgis";
</code>


==== Restoring data ====

Using the standard utilities that come with PostgreSQL, I have dumped an archive of the earlier database using 
<code bash>
pg_dump -Fc > sqlfile.pgdump
</code> 

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:

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


===== PostgreSQL and PostGIS on Windoze =====

  * [[http://gareth.flowers/postgresql-portable/|Running PostgreSQL on a Stick]] gareth Flower's webpage with links to executables to run PostgreSQL on a stick on Windoze.
  * [[http://security.stackexchange.com/questions/2517/postgresql-security-audit|PostgreSQL security audits]]
  * [[http://www.archaeogeek.com/blog/2016/04/19/portablegis-5-dot-6/|PortableGIS]] Windows-based GIS tools made portable.


{{tag> Windows PostGIS PostgreSQL GIS PortableApps USB databases macOS 10.12}}