PostgreSQL & PostGIS

Installing/upgrading to PostgreSQL version 12.x and PostGIS version 3.0.1 on MacOS > High Sierra (10.13.x and higher).

The reason for a manual installation is that I want to utilise the GDAL framework utilised by QGIS and GMT. This setup naturally collides with Homebrew's setup to install GDAL as part of the dependency framework when installing postgis.

I also use the Python.org's Python Frameworks.

So I am leaving the dependencies to homebrew apart from GDAL (where I still use William Kyngesbury's formidable GDAL Framework - at v 2.4.1 at time of writing) and Python.org's Python framework distribution - v.3.8.

The documentation to compile from source is available on the Postgresql.org website. Here are the individual commands I use to compile/check:

./configure PYTHON=/Library/Frameworks/Python.framework/Versions/3.x/bin/python3 \\
   --with-perl --with-uuid=e2fs --with-python --with-libxml  --with-readline  --prefix=/usr/local/pgsql/12.2  --with-libraries=/usr/local/Cellar/readline/x.x.x/
make -j24
make check
make docs
sudo make install -j24

The hint with specifying the Python distribution on the ./configure call is from the PostgreSQL mailing list. Once the configure script has completed successfully I run:

After an upgrade of the OS it seems that the LaunchDaemon is broken.

The issues I encounter are related to file permissions for the log file. In this case the user _postgresql does not have permissions to write to /var/log/Postgresql.log as only root as access here. This has likely changed between earlier Mac OS versions and Catalina (which is where the issue popped up).

So there seems to be two options going forward:

1. have pgsql directly log to the syslog
2. use conventional stdout and stderr as log output directions, but change the logfile location and permissions.

On first sight is look as if option 1 is more elegant, but there is very little information as to how this should be set up and I have very little experience with syslog.

Here's a few links to get started:

• man asl.conf - manpage for Apple's system log tool
• Kohei Nozaki's blog entry on Postgresql using syslog
• SO: log level prefs per OS X application
• https://superuser.com/questions/932013/asl-conf-doesnt-take-an-effect-on-my-enviroment-os-x-yosemite
• https://apple.stackexchange.com/questions/6559/setting-a-custom-log
• Linux logging basics
• https://apple.stackexchange.com/questions/338213/how-to-run-a-launchagent-that-runs-a-script-which-causes-failures-because-of-sys
• https://apple.stackexchange.com/questions/387592/how-to-use-launchd
• Getting postgresql write to syslog
• https://apple.stackexchange.com/questions/195997/running-postgres-as-launch-daemon
• https://developer.apple.com/documentation/os/logging
• https://apple.stackexchange.com/questions/357179/unified-logs-macos-high-sierra?rq=1
• https://apple.stackexchange.com/questions/162487/how-can-one-start-a-process-and-monitor-its-syslog-messages?rq=1
• https://apple.stackexchange.com/questions/390986/can-i-have-launchctl-output-stdout-stderr-from-my-application-to-the-system-wide?rq=1
• https://ilostmynotes.blogspot.com/2011/11/os-x-logging-aslconf-syslogconf-and.html
• https://www.unixtutorial.org/syslog-and-asl-in-macos/
• https://www.launchd.info
• https://superuser.com/questions/943983/os-x-launchdaemon-not-running-service-could-not-initialize
• https://stackoverflow.com/questions/27700596/homebrew-postgres-broken
• [https://apple.stackexchange.com/questions/322078/what-are-all-the-available-acl-attributes-in-mac-os-10-13-high-sierra]]
• https://unix.stackexchange.com/questions/479890/permission-to-write-to-log
• https://stackoverflow.com/questions/32160108/os-x-launchd-script-fails-to-run-at-startup?rq=1

On their OpenSource pages Apple provides a template for one of the older included PostgreSQL versions (here: 9.0.4). This deviates a bit from what I used previously for the LaunchDaemon file.

Verbatim copy of [[https://opensource.apple.com/source/PostgreSQL/PostgreSQL-23/org.postgresql.postgres.plist.auto.html|of Apple's org.postgresql.postgres.plist on their opensource site:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
	<key>Disabled</key>
	<true/>
	<key>Label</key>
	<string>org.postgresql.postgres</string>
	<key>UserName</key>
	<string>_postgres</string>
	<key>GroupName</key>
	<string>_postgres</string>
	<key>ProgramArguments</key>
	<array>
		<string>/usr/bin/postgres</string>
		<string>-D</string>
		<string>/var/pgsql</string>
		<string>-c</string>
		<string>unix_socket_directory=/var/pgsql_socket</string>
		<string>-c</string>
		<string>logging_collector=on</string>
		<string>-c</string>
		<string>log_connections=on</string>
		<string>-c</string>
		<string>log_lock_waits=on</string>
		<string>-c</string>
		<string>log_statement=ddl</string>
		<string>-c</string>
		<string>log_line_prefix=%t </string>
		<string>-c</string>
		<string>listen_addresses=</string>
		<string>-c</string>
		<string>log_directory=/Library/Logs</string>
		<string>-c</string>
		<string>log_filename=PostgreSQL.log</string>
		<string>-c</string>
		<string>unix_socket_group=_postgres</string>
		<string>-c</string>
		<string>unix_socket_permissions=0770</string>
	</array>
	<key>OnDemand</key>
	<false/>
</dict>
</plist>

Homebrew's distro uses a slightly different version - here's the excerpt from the postgresql.rb file:

  def plist; <<~EOS
    <?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>KeepAlive</key>
      <true/>
      <key>Label</key>
      <string>#{plist_name}</string>
      <key>ProgramArguments</key>
      <array>
        <string>#{opt_bin}/postgres</string>
        <string>-D</string>
        <string>#{var}/#{name}</string>
      </array>
      <key>RunAtLoad</key>
      <true/>
      <key>WorkingDirectory</key>
      <string>#{HOMEBREW_PREFIX}</string>
      <key>StandardOutPath</key>
      <string>#{var}/log/#{name}.log</string>
      <key>StandardErrorPath</key>
      <string>#{var}/log/#{name}.log</string>
    </dict>
    </plist>
  EOS
  end

Once the

 ./configure --prefix=/usr/local/pgsql/x.x/ --with-projdir=/Library/Frameworks/PROJ.framework/unix/ --with-raster --with-topology --with-pgconfig=/usr/local/pgsql/x.x/bin/pg_config --with-geosconfig=/Library/Frameworks/GEOS.framework/unix/bin/geos-config