Provide embedded postgres+postgis binaries

[Zetten]

Would there be any interest in generating packages with the postgis extensions available?

I started investigating myself, but the current Gradle and Docker patterns doen't seem to lend themselves easily to extension. Ideally I'd expect additional artifacts to be built and extended with the postgis classifier, but as far as I can tell there's no clear way to reach this point without rewriting large swathes of the scripts.

I'd be happy to raise a PR for this if I could get a suggestion as to the desired approach!

[tomix26]

There is definitely interest in it. Any help is welcome.

I think there are two basic approaches. Either it can be solved in the form of additional artifacts that will be part of the regular release and will be included in embedded-postgres-binaries-bom. In that case, all major platforms need to be supported (Linux, Windows, Mac OS). And it brings a problem with combining other extensions that may be supported in the future. Because everyone may want a different combination of extensions.

Or, an easier variant, it can be implemented only in generateCustomXxxPostgresBundle gradle tasks. Anyone can then build the binary package themselves with the parameters they need. In that case, the artifact will not be part of the regular release and only Linux platform may be supported. The problem with combining different extensions also disappears, because the binary package is compiled each time for the specified parameters containing a list of the required extensions.

The second variant should be much easier to implement. Below is an example of how this might be implemented. The example illustrates only the debian variant, it is also necessary to modify the alpine linux variant. Please note that this is just a sample and I have not tested it at all.

Index: repack-postgres-debian.sh
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
--- repack-postgres-debian.sh   (revision 190d84e75ec834b1ad7e3c1456fd647cb2de51b4)
+++ repack-postgres-debian.sh   (date 1554455671000)
@@ -1,13 +1,15 @@
 #!/bin/bash

 DOCKER_OPTS=
+POSTGIS_VERSION=
 LITE_OPT=false

-while getopts "v:i:o:l" opt; do
+while getopts "v:i:o:g:l" opt; do
     case $opt in
     v) VERSION=$OPTARG ;;
     i) IMG_NAME=$OPTARG ;;
     o) DOCKER_OPTS=$OPTARG ;;
+    g) POSTGIS_VERSION=$OPTARG ;;
     l) LITE_OPT=true ;;
     \?) exit 1 ;;
     esac
@@ -84,6 +86,18 @@
     && make install-world \
     && make -C contrib install \
     \
+    && if [ -n "POSTGIS_VERSION" ]; then \
+        wget -O postgis.tar.gz 'https://download.osgeo.org/postgis/source/postgis-$POSTGIS_VERSION.tar.gz' \
+        && mkdir -p /usr/src/postgis \
+        && tar -xf postgis.tar.gz -C /usr/src/postgis --strip-components 1 \
+        && cd /usr/src/postgis \
+        && wget -O config.guess 'https://git.savannah.gnu.org/gitweb/?p=config.git;a=blob_plain;f=config.guess;hb=b8ee5f79949d1d40e8820a774d813660e1be52d3' \
+        && wget -O config.sub 'https://git.savannah.gnu.org/gitweb/?p=config.git;a=blob_plain;f=config.sub;hb=b8ee5f79949d1d40e8820a774d813660e1be52d3' \
+        && ./configure \
+        && make -j\$(nproc) \
+        && make install \
+       fi \
+       \
     && cd /usr/local/pg-build \
     && cp /lib/*/libz.so.1 /lib/*/libuuid.so.1 /lib/*/liblzma.so.5 /usr/lib/*/libxml2.so.2 /usr/lib/*/libxslt.so.1 ./lib \
     && cp /lib/*/libssl.so.1.0.0 /lib/*/libcrypto.so.1.0.0 ./lib || cp /usr/lib/*/libssl.so.1.0.0 /usr/lib/*/libcrypto.so.1.0.0 ./lib \
Index: build.gradle
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
--- build.gradle    (revision 190d84e75ec834b1ad7e3c1456fd647cb2de51b4)
+++ build.gradle    (date 1554456083000)
@@ -14,6 +14,7 @@

     pgVersionParam = project.findProperty('pgVersion') ?: ("${project.version}" - '-SNAPSHOT')
     pgBinVersionParam = project.findProperty('pgBinVersion') ?: "${pgVersionParam}-1"
+    postgisVersion = project.findProperty('postgisVersion') ?: ''
     archNameParam = project.findProperty('archName') ?: ''
     distNameParam = project.findProperty('distName') ?: ''
     dockerImageParam = project.findProperty('dockerImage') ?: ''
@@ -218,22 +219,30 @@
             def dockerImage = { dockerImageParam ?: defaultDebianImage(archName, qemuBindings()) }

             doFirst {
-                println "archName:      $archName"
-                println "distName:      debian-like"
-                println "dockerImage:   ${dockerImage()}"
-                println "qemuBindings:  ${qemuBindings()}"
+                println "archName:       $archName"
+                println "distName:       debian-like"
+                println "dockerImage:    ${dockerImage()}"
+                println "qemuBindings:   ${qemuBindings()}"
                 println ''
+
+                if (postgisVersion) {
+                    println '====== Extensions ======'
+                    println "postgisVersion: $postgisVersion"
+                    println '========================'
+                    println ''
+                }
             }

             inputs.property('pgVersion', pgVersionParam)
             inputs.property('archName', archName)
             inputs.property('dockerImage', dockerImage)
+            inputs.property('postgisVersion', postgisVersion)

             inputs.file("$rootDir/repack-postgres-debian.sh")
             outputs.dir("$temporaryDir/bundle")

             workingDir temporaryDir
-            commandLine "$rootDir/repack-postgres-debian.sh", '-v', "$pgVersionParam", '-i', dockerImage, '-o', qemuBindings
+            commandLine "$rootDir/repack-postgres-debian.sh", '-v', "$pgVersionParam", '-i', dockerImage, '-o', qemuBindings, '-g', postgisVersion
         }

         task customDebianJar(group: 'build (custom)', type: Jar) {
@@ -241,6 +250,10 @@
             include "postgres-linux-debian.txz"
             rename "postgres-linux-debian.txz", "postgres-linux-${normalizeArchName(archName)}.txz"
             appendix = "linux-${archName}"
+
+            if (postgisVersion) {
+                classifier = "postgis"
+            }
         }

         artifacts.add('bundles', tasks.getByName('customDebianJar'))

[Zetten]

Sorry for the late action on this.

I've given it a stab using your suggested starting point. So far I've managed to successfully test the ubuntu-based build in my project, and it all seems fine. The Apline dist has built fine (but I haven't verified the final result). Do you have any CI or automated verification for the other distributions and architectures?

One minor point of interest is that I only verified with the latest postgis (2.5.2), although I did test against various postgres versions (9.5, 10.4 and 10.5). I don't know whether it's worth providing a complete matrix of postgis versions for each postgres version - perhaps an improvement for the future.

[tomix26]

Unfortunately, at this moment, I don't have any automated process to test all distributions and architectures. It is necessary to do it manually by building embedded-database-spring-test project on specific machines. So please create a pull request and I will test it.

As for the second part, what exactly is the problem with the postgis versions? Why is the matrix needed? My idea is that the version should be configurable through postgisVersion property. So that anyone can set the version he wants. There should be no fixed version or a prepared matrix of postgis versions.

Something like this: ./gradlew clean install -Pversion=9.6.12 -PpgVersion=9.6.12 -PpostgisVersion=2.3.8 ./gradlew clean install -Pversion=10.7.0 -PpgVersion=10.7 -PpostgisVersion=2.4.6 ./gradlew clean install -Pversion=11.2.0 -PpgVersion=11.2 -PpostgisVersion=2.5.2

[Zetten]

Good point about the version specification. If anyone can select (and verify) their own versions then there is no substantial issue.

[dlienko]

I've built and tested it with Postgres 12.0 and PostGIS 2.5.2 for Linux, and it works fine. However, from what I can understand in the build.gradle, it's impossible to build it with PostGIS for Windows and MacOS. Is it correct?

[tomix26]

It is correct and it's mentioned in readme:

this option is not (yet) available for Windows and Mac OS platforms

[dlienko]

Thanks for your answer :) It was not clear whether this means a target platform or OS on which Postgis can be built.