meta data for this page
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 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.
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
PostgreSQL and PostGIS on Windoze
- Running PostgreSQL on a Stick gareth Flower's webpage with links to executables to run PostgreSQL on a stick on Windoze.
- PortableGIS Windows-based GIS tools made portable.