How do I correctly relocate PostGIS control files?

How do I correctly relocate PostGIS control files?

[Gary Johnson]

Hi Guix,

I use Postgresql with PostGIS extensively for geospatial software
development. While the default set of postgresql (v10.13) and postgis
(v3.0.2) packages in Guix have worked well for me for some time, I am
now in need of upgrading to the latest version in order to utilize newer
functionality in the database software.

To do this, I created a derivative package for postgresql (v13.0) as
well as a derivative postgis package that uses the new postgresql
package as an input. These packages compile and install correctly with
"guix package -i".

Here's my code:

;;=============================================================================================================================

(define-module (my-packages postgresql-13)
  #:use-module ((guix packages)          #:select (package origin base32))
  #:use-module ((guix download)          #:select (url-fetch))
  #:use-module ((gnu packages databases) #:select (postgresql))
  #:use-module ((gnu packages geo)       #:select (postgis gdal geos proj))
  #:use-module ((gnu packages image)     #:select (giflib))
  #:use-module ((gnu packages web)       #:select (json-c))
  #:use-module ((gnu packages image)     #:select (libjpeg-turbo))
  #:use-module ((gnu packages xml)       #:select (libxml2))
  #:use-module ((gnu packages pcre)      #:select (pcre)))

(define-public postgresql-13
  (package
   (inherit postgresql)
   (name "postgresql")
   (version "13.0")
   (source (origin
            (method url-fetch)
            (uri (string-append "https://ftp.postgresql.org/pub/source/v"
                                version "/postgresql-" version ".tar.bz2"))
            (sha256
             (base32
              "15i2b7m9a9430idqdgvrcyx66cpxz0v2d81nfqcm8ss3inz51rw0"))))))

(define-public postgis-for-postgresql-13
  (package
   (inherit postgis)
   (name "postgis")
   (version "3.0.2")
   (inputs
    `(("gdal" ,gdal)
      ("geos" ,geos)
      ("giflib" ,giflib)
      ("json-c" ,json-c)
      ("libjpeg" ,libjpeg-turbo)
      ("libxml2" ,libxml2)
      ("pcre" ,pcre)
      ("postgresql" ,postgresql-13)
      ("proj" ,proj)))))

;;=============================================================================================================================

Next, I moved to my OS config.scm file to set up the Postgresql Shepherd
service and add PostGIS as an extension package for the DB, which should
make its control files available to Postgresql at runtime.

Here are the relevant sections:

;;=============================================================================================================================

(use-modules ...             
             ((gnu services databases)    #:select (postgresql-service-type postgresql-configuration postgresql-config-file))
             ((my-packages postgresql-13) #:select (postgresql-13 postgis-for-postgresql-13)))

(operating-system
 ...
 (packages (cons* ...
                  postgresql-13 postgis-for-postgresql-13 ; psql, raster2pgsql, shp2pgsql, etc.
                  %base-packages))
 (services (cons* ...
                  (service postgresql-service-type (postgresql-configuration
                                                    (postgresql postgresql-13)
                                                    (extension-packages (list postgis-for-postgresql-13))
                                                    (config-file (postgresql-config-file
                                                                  (hba-file my-postgres-hba)
                                                                  (extra-config '(("max_worker_processes" "12")
                                                                                  ("max_parallel_workers" "40")
                                                                                  ("max_parallel_maintenance_workers" "8")
                                                                                  ("max_parallel_workers_per_gather" "4")
                                                                                  ("parallel_leader_participation" "on")))))))
                  %desktop-services)))

;;=============================================================================================================================

This compiles and installs successfully with "guix system reconfigure".

However, when I connect to the Postgresql server with "psql -U postgres"
and attempt to add the PostGIS extension to a database, I get the
dreaded "could not open extension control file" error:

;;=============================================================================================================================

db=# create extension postgis;
ERROR:  could not open extension control file
    "/gnu/store/8m48v5132qpmxim9s4g9vca59qgay2d9-postgresql-13.0/share/extension/postgis.control": No such file or directory

;;=============================================================================================================================

This error does not occur when using the stock postgresql (v10.13) and
postgis (v3.0.2) packages that come with the current Guix, so something
is going wrong with the "extension-packages" code from my config.scm's
"postgresql-configuration" record.

I am very much hoping that one of the Guix maintainers can look into
this and help me resolve the issue as I suspect it may be affecting any
user that wants to use a different version of Postgres.

Thanks in advance,
  Gary

-- 
GPG Key ID: 7BC158ED
Use `gpg --search-keys lambdatronic' to find me
Protect yourself from surveillance: https://emailselfdefense.fsf.org
=======================================================================
()  ascii ribbon campaign - against html e-mail
/\  www.asciiribbon.org   - against proprietary attachments

Please avoid sending me MS-Office attachments.
See http://www.gnu.org/philosophy/no-word-attachments.html

Re: How do I correctly relocate PostGIS control files?

[Julien Lepiller]

The service simply builds a union-build of the postgis and postgresql packages, because postgresql looks for its extensions in the directory it's run from.

It could be that this behavior changed, or that the postgis package doesn't build its extension as expected.

To cgeck these hypothesis: can you check the error message contains the store path of the union (as opposed to only postresql). You should be able to find some of postgis files there in addition to postgresql files. For the second hypothesis, can you find the postgis.control file in the postgis package itself?

Le 2 novembre 2020 17:40:09 GMT-05:00, Gary Johnson <lambdatronic@disroot.org> a écrit :
>Hi Guix,
>
>I use Postgresql with PostGIS extensively for geospatial software
>development. While the default set of postgresql (v10.13) and postgis
>(v3.0.2) packages in Guix have worked well for me for some time, I am
>now in need of upgrading to the latest version in order to utilize
>newer
>functionality in the database software.
>
>To do this, I created a derivative package for postgresql (v13.0) as
>well as a derivative postgis package that uses the new postgresql
>package as an input. These packages compile and install correctly with
>"guix package -i".
>
>Here's my code:
>
>;;=============================================================================================================================
>
>(define-module (my-packages postgresql-13)
>#:use-module ((guix packages)          #:select (package origin
>base32))
>  #:use-module ((guix download)          #:select (url-fetch))
>  #:use-module ((gnu packages databases) #:select (postgresql))
>#:use-module ((gnu packages geo)       #:select (postgis gdal geos
>proj))
>  #:use-module ((gnu packages image)     #:select (giflib))
>  #:use-module ((gnu packages web)       #:select (json-c))
>  #:use-module ((gnu packages image)     #:select (libjpeg-turbo))
>  #:use-module ((gnu packages xml)       #:select (libxml2))
>  #:use-module ((gnu packages pcre)      #:select (pcre)))
>
>(define-public postgresql-13
>  (package
>   (inherit postgresql)
>   (name "postgresql")
>   (version "13.0")
>   (source (origin
>            (method url-fetch)
>          (uri (string-append "https://ftp.postgresql.org/pub/source/v"
>                            version "/postgresql-" version ".tar.bz2"))
>            (sha256
>             (base32
>           "15i2b7m9a9430idqdgvrcyx66cpxz0v2d81nfqcm8ss3inz51rw0"))))))
>
>(define-public postgis-for-postgresql-13
>  (package
>   (inherit postgis)
>   (name "postgis")
>   (version "3.0.2")
>   (inputs
>    `(("gdal" ,gdal)
>      ("geos" ,geos)
>      ("giflib" ,giflib)
>      ("json-c" ,json-c)
>      ("libjpeg" ,libjpeg-turbo)
>      ("libxml2" ,libxml2)
>      ("pcre" ,pcre)
>      ("postgresql" ,postgresql-13)
>      ("proj" ,proj)))))
>
>;;=============================================================================================================================
>
>Next, I moved to my OS config.scm file to set up the Postgresql
>Shepherd
>service and add PostGIS as an extension package for the DB, which
>should
>make its control files available to Postgresql at runtime.
>
>Here are the relevant sections:
>
>;;=============================================================================================================================
>
>(use-modules ...             
>((gnu services databases)    #:select (postgresql-service-type
>postgresql-configuration postgresql-config-file))
>((my-packages postgresql-13) #:select (postgresql-13
>postgis-for-postgresql-13)))
>
>(operating-system
> ...
> (packages (cons* ...
>postgresql-13 postgis-for-postgresql-13 ; psql, raster2pgsql,
>shp2pgsql, etc.
>                  %base-packages))
> (services (cons* ...
>             (service postgresql-service-type (postgresql-configuration
>                                             (postgresql postgresql-13)
>                  (extension-packages (list postgis-for-postgresql-13))
>                                   (config-file (postgresql-config-file
>                                             (hba-file my-postgres-hba)
>                          (extra-config '(("max_worker_processes" "12")
>                                          ("max_parallel_workers" "40")
>                               ("max_parallel_maintenance_workers" "8")
>                                ("max_parallel_workers_per_gather" "4")
>                           ("parallel_leader_participation" "on")))))))
>                  %desktop-services)))
>
>;;=============================================================================================================================
>
>This compiles and installs successfully with "guix system reconfigure".
>
>However, when I connect to the Postgresql server with "psql -U
>postgres"
>and attempt to add the PostGIS extension to a database, I get the
>dreaded "could not open extension control file" error:
>
>;;=============================================================================================================================
>
>db=# create extension postgis;
>ERROR:  could not open extension control file
>"/gnu/store/8m48v5132qpmxim9s4g9vca59qgay2d9-postgresql-13.0/share/extension/postgis.control":
>No such file or directory
>
>;;=============================================================================================================================
>
>This error does not occur when using the stock postgresql (v10.13) and
>postgis (v3.0.2) packages that come with the current Guix, so something
>is going wrong with the "extension-packages" code from my config.scm's
>"postgresql-configuration" record.
>
>I am very much hoping that one of the Guix maintainers can look into
>this and help me resolve the issue as I suspect it may be affecting any
>user that wants to use a different version of Postgres.
>
>Thanks in advance,
>  Gary
>
>-- 
>GPG Key ID: 7BC158ED
>Use `gpg --search-keys lambdatronic' to find me
>Protect yourself from surveillance: https://emailselfdefense.fsf.org
>=======================================================================
>()  ascii ribbon campaign - against html e-mail
>/\  www.asciiribbon.org   - against proprietary attachments
>
>Please avoid sending me MS-Office attachments.
>See http://www.gnu.org/philosophy/no-word-attachments.html

Re: How do I correctly relocate PostGIS control files?

[Gary Johnson]

Julien Lepiller <julien@lepiller.eu> writes:

> The service simply builds a union-build of the postgis and postgresql
> packages, because postgresql looks for its extensions in the directory
> it's run from.
>
> It could be that this behavior changed, or that the postgis package
> doesn't build its extension as expected.
>
> To cgeck these hypothesis: can you check the error message contains
> the store path of the union (as opposed to only postresql). You should
> be able to find some of postgis files there in addition to postgresql
> files.

gjohnson@euclid ~ $ guix package -p /run/current-system/profile -I
...
postgis     3.0.2   out /gnu/store/0cb4sf18w2i9f0b79kyrli7fx3i63c4s-postgis-3.0.2
postgresql  13.0    out /gnu/store/8m48v5132qpmxim9s4g9vca59qgay2d9-postgresql-13.0

gjohnson@euclid ~ $ ls /gnu/store/8m48v5132qpmxim9s4g9vca59qgay2d9-postgresql-13.0/share/extension/
adminpack--1.0--1.1.sql   btree_gist--1.3--1.4.sql  dict_int.control             intagg--1.0--1.1.sql       pageinspect--1.1--1.2.sql      pg_stat_statements--1.0--1.1.sql  pgcrypto--1.1--1.2.sql     seg--1.1--1.2.sql
adminpack--1.0.sql        btree_gist--1.4--1.5.sql  dict_xsyn--1.0.sql           intagg--1.1.sql            pageinspect--1.2--1.3.sql      pg_stat_statements--1.1--1.2.sql  pgcrypto--1.2--1.3.sql     seg--1.1.sql
adminpack--1.1--2.0.sql   btree_gist.control        dict_xsyn.control            intagg.control             pageinspect--1.3--1.4.sql      pg_stat_statements--1.2--1.3.sql  pgcrypto--1.3.sql          seg--1.2--1.3.sql
adminpack--2.0--2.1.sql   citext--1.0--1.1.sql      earthdistance--1.0--1.1.sql  intarray--1.0--1.1.sql     pageinspect--1.4--1.5.sql      pg_stat_statements--1.3--1.4.sql  pgcrypto.control           seg.control
adminpack.control         citext--1.1--1.2.sql      earthdistance--1.1.sql       intarray--1.1--1.2.sql     pageinspect--1.5--1.6.sql      pg_stat_statements--1.4--1.5.sql  pgrowlocks--1.0--1.1.sql   sslinfo--1.0--1.1.sql
amcheck--1.0--1.1.sql     citext--1.2--1.3.sql      earthdistance.control        intarray--1.2--1.3.sql     pageinspect--1.5.sql           pg_stat_statements--1.4.sql       pgrowlocks--1.1--1.2.sql   sslinfo--1.1--1.2.sql
amcheck--1.0.sql          citext--1.3--1.4.sql      file_fdw--1.0.sql            intarray--1.2.sql          pageinspect--1.6--1.7.sql      pg_stat_statements--1.5--1.6.sql  pgrowlocks--1.2.sql        sslinfo--1.2.sql
amcheck--1.1--1.2.sql     citext--1.4--1.5.sql      file_fdw.control             intarray.control           pageinspect--1.7--1.8.sql      pg_stat_statements--1.6--1.7.sql  pgrowlocks.control         sslinfo.control
amcheck.control           citext--1.4.sql           fuzzystrmatch--1.0--1.1.sql  isn--1.0--1.1.sql          pageinspect.control            pg_stat_statements--1.7--1.8.sql  pgstattuple--1.0--1.1.sql  tablefunc--1.0.sql
autoinc--1.0.sql          citext--1.5--1.6.sql      fuzzystrmatch--1.1.sql       isn--1.1--1.2.sql          pg_buffercache--1.0--1.1.sql   pg_stat_statements.control        pgstattuple--1.1--1.2.sql  tablefunc.control
autoinc.control           citext.control            fuzzystrmatch.control        isn--1.1.sql               pg_buffercache--1.1--1.2.sql   pg_trgm--1.0--1.1.sql             pgstattuple--1.2--1.3.sql  tcn--1.0.sql
bloom--1.0.sql            cube--1.0--1.1.sql        hstore--1.0--1.1.sql         isn.control                pg_buffercache--1.2--1.3.sql   pg_trgm--1.1--1.2.sql             pgstattuple--1.3--1.4.sql  tcn.control
bloom.control             cube--1.1--1.2.sql        hstore--1.1--1.2.sql         lo--1.0--1.1.sql           pg_buffercache--1.2.sql        pg_trgm--1.2--1.3.sql             pgstattuple--1.4--1.5.sql  tsm_system_rows--1.0.sql
btree_gin--1.0--1.1.sql   cube--1.2--1.3.sql        hstore--1.2--1.3.sql         lo--1.1.sql                pg_buffercache.control         pg_trgm--1.3--1.4.sql             pgstattuple--1.4.sql       tsm_system_rows.control
btree_gin--1.0.sql        cube--1.2.sql             hstore--1.3--1.4.sql         lo.control                 pg_freespacemap--1.0--1.1.sql  pg_trgm--1.3.sql                  pgstattuple.control        tsm_system_time--1.0.sql
btree_gin--1.1--1.2.sql   cube--1.3--1.4.sql        hstore--1.4--1.5.sql         ltree--1.0--1.1.sql        pg_freespacemap--1.1--1.2.sql  pg_trgm--1.4--1.5.sql             plpgsql--1.0.sql           tsm_system_time.control
btree_gin--1.2--1.3.sql   cube.control              hstore--1.4.sql              ltree--1.1--1.2.sql        pg_freespacemap--1.1.sql       pg_trgm.control                   plpgsql.control            unaccent--1.0--1.1.sql
btree_gin.control         dblink--1.0--1.1.sql      hstore--1.5--1.6.sql         ltree--1.1.sql             pg_freespacemap.control        pg_visibility--1.0--1.1.sql       postgres_fdw--1.0.sql      unaccent--1.1.sql
btree_gist--1.0--1.1.sql  dblink--1.1--1.2.sql      hstore--1.6--1.7.sql         ltree.control              pg_prewarm--1.0--1.1.sql       pg_visibility--1.1--1.2.sql       postgres_fdw.control       unaccent.control
btree_gist--1.1--1.2.sql  dblink--1.2.sql           hstore.control               moddatetime--1.0.sql       pg_prewarm--1.1--1.2.sql       pg_visibility--1.1.sql            refint--1.0.sql            uuid-ossp--1.0--1.1.sql
btree_gist--1.2--1.3.sql  dblink.control            insert_username--1.0.sql     moddatetime.control        pg_prewarm--1.1.sql            pg_visibility.control             refint.control             uuid-ossp--1.1.sql
btree_gist--1.2.sql       dict_int--1.0.sql         insert_username.control      pageinspect--1.0--1.1.sql  pg_prewarm.control             pgcrypto--1.0--1.1.sql            seg--1.0--1.1.sql          uuid-ossp.control

So...unfortunately there are no postgis files in the postgresql directory.

> For the second hypothesis, can you find the postgis.control file in the postgis package itself?

gjohnson@euclid ~ $ tree /gnu/store/0cb4sf18w2i9f0b79kyrli7fx3i63c4s-postgis-3.0.2
/gnu/store/0cb4sf18w2i9f0b79kyrli7fx3i63c4s-postgis-3.0.2
├── bin
│   ├── pgsql2shp
│   ├── raster2pgsql
│   └── shp2pgsql
├── lib
│   ├── address_standardizer-3.so
│   ├── postgis-3.so
│   ├── postgis_raster-3.so
│   └── postgis_topology-3.so
└── share
    ├── contrib
    ├── doc
    └── extension
        ├── address_standardizer.control
        ├── address_standardizer_data_us.control
        ├── postgis.control
        ├── postgis_raster.control
        ├── postgis_tiger_geocoder.control
        ├── postgis_topology.control
        ├── ...

Yes. All of the expected postgis files can be found in the postgis
package.

So it looks like there is an issue with the way postgresql-service-type
installs its extensions. How can we correct this?

Thanks in advance,
  Gary

-- 
GPG Key ID: 7BC158ED
Use `gpg --search-keys lambdatronic' to find me
Protect yourself from surveillance: https://emailselfdefense.fsf.org
=======================================================================
()  ascii ribbon campaign - against html e-mail
/\  www.asciiribbon.org   - against proprietary attachments

Please avoid sending me MS-Office attachments.
See http://www.gnu.org/philosophy/no-word-attachments.html

Re: How do I correctly relocate PostGIS control files?

[Gary Johnson]

Hi again Guix,

It's been a week since my original post requesting assistance with
getting Postgresql and PostGIS to work together correctly, but
unfortunately I still haven't received any help with this issue.

To summarize the conversation thus far:

1. I can build postgresql-13.0 successfully, and all of its files appear
   in its /gnu/store directory as expected.

2. I can build postgis-3.0.2 succesfully, and all of its files appear in
   its /gnu/store directory as expected.

3. None of the postgis-3.0.2 files are being correctly installed into
   postgresql-13.0's /gnu/store directory, which AFAICT indicates that
   the postgresql-service-type code that processes extensions is not
   working correctly here.

Does anyone know how this code works or how to fix this issue?

Thanks in advance,
  Gary

-- 
GPG Key ID: 7BC158ED
Use `gpg --search-keys lambdatronic' to find me
Protect yourself from surveillance: https://emailselfdefense.fsf.org
=======================================================================
()  ascii ribbon campaign - against html e-mail
/\  www.asciiribbon.org   - against proprietary attachments

Why is HTML email a security nightmare? See https://useplaintext.email/

Please avoid sending me MS-Office attachments.
See http://www.gnu.org/philosophy/no-word-attachments.html

Re: How do I correctly relocate PostGIS control files?

[Julien Lepiller]

Le 9 novembre 2020 18:56:56 GMT-05:00, Gary Johnson <lambdatronic@disroot.org> a écrit :
>Hi again Guix,
>
>It's been a week since my original post requesting assistance with
>getting Postgresql and PostGIS to work together correctly, but
>unfortunately I still haven't received any help with this issue.
>
>To summarize the conversation thus far:
>
>1. I can build postgresql-13.0 successfully, and all of its files
>appear
>   in its /gnu/store directory as expected.
>
>2. I can build postgis-3.0.2 succesfully, and all of its files appear
>in
>   its /gnu/store directory as expected.
>
>3. None of the postgis-3.0.2 files are being correctly installed into
>   postgresql-13.0's /gnu/store directory, which AFAICT indicates that
>   the postgresql-service-type code that processes extensions is not
>   working correctly here.

There are actually two postgresql-13.0: the one you build, that does not have postgis (since postgis depends on it), and the one that's created by the service code, as a union of the first one and postgis.

The error message gives you the store path where postgresql tries to find the plugin. Is it the same as the result of "guix build postgresql@13.0"? In that case I think there might be some changes to the way postgresql searches for its plugins. Either it looks for where it's symlinked to, or it embeds its own store path.

In both cases, we need to find another way to make it find its plugins. Do you if there is a way to make it find plugins in non-standard directories (ex /usr/local or /opt in ocher distros)?

>
>Does anyone know how this code works or how to fix this issue?
>
>Thanks in advance,
>  Gary

Re: How do I correctly relocate PostGIS control files?

[Carlo Zancanaro]

[-- Attachment #1: Type: text/plain, Size: 2394 bytes --]

Hi Gary,

On Tue, Nov 10 2020, Gary Johnson wrote:
> It's been a week since my original post requesting assistance 
> with getting Postgresql and PostGIS to work together correctly, 
> but unfortunately I still haven't received any help with this 
> issue.
> ...
> Does anyone know how this code works or how to fix this issue?

I didn't respond initially because I don't have any specialist 
knowledge about this. I had some free time today, so I did a bit 
of an investigation and I think I've figured out what's gone wrong 
for you here.

In your original email you gave this definition for postgresql-13:

    (define-public postgresql-13
      (package
       (inherit postgresql)
       (name "postgresql")
       (version "13.0")
       (source (origin
                (method url-fetch)
                (uri (string-append 
                "https://ftp.postgresql.org/pub/source/v"
                                    version "/postgresql-" version 
                                    ".tar.bz2"))
                (sha256
                 (base32
                  "15i2b7m9a9430idqdgvrcyx66cpxz0v2d81nfqcm8ss3inz51rw0"))))))

However, this is missing one important line from the original 
postgresql definition, which applies a patch to the source:

    (patches (search-patches 
    "postgresql-disable-resolve_symlinks.patch"))

This patch changes how Postgres treats symlinks, which Guix uses 
extensively. If we add this patch the definition of postgresql-13 
we should get:

    (define-public postgresql-13
      (package
       (inherit postgresql)
       (name "postgresql")
       (version "13.0")
       (source (origin
                (method url-fetch)
                (uri (string-append 
                "https://ftp.postgresql.org/pub/source/v"
                                    version "/postgresql-" version 
                                    ".tar.bz2"))
                (sha256
                 (base32
                  "15i2b7m9a9430idqdgvrcyx66cpxz0v2d81nfqcm8ss3inz51rw0"))
                (patches (search-patches 
                "postgresql-disable-resolve_symlinks.patch"))))))

I put this into a system definition (a bit hacky, but attached) 
and built it using "guix system vm helping-gary.scm". Logging into 
the system as root I was able to run this successfully:

    sudo -u postgres psql -c 'create extension postgis'

I hope that helps!

Carlo


[-- Attachment #2: helping-gary.scm --]
[-- Type: application/octet-stream, Size: 1978 bytes --]

(use-modules (gnu)
             (guix modules)
             (guix packages)
             (guix download)
             (gnu system file-systems)
             (gnu packages databases)
             (gnu packages geo)
             (gnu packages image)
             (gnu packages web)
             (gnu packages image)
             (gnu packages xml)
             (gnu packages pcre)
             (gnu services databases)
             (gnu services desktop))

(define-public postgresql-13
  (package
   (inherit postgresql)
   (name "postgresql")
   (version "13.0")
   (source (origin
            (method url-fetch)
            (uri (string-append "https://ftp.postgresql.org/pub/source/v"
                                version "/postgresql-" version ".tar.bz2"))
            (sha256
             (base32
              "15i2b7m9a9430idqdgvrcyx66cpxz0v2d81nfqcm8ss3inz51rw0"))
            (patches (search-patches "postgresql-disable-resolve_symlinks.patch"))))
   (arguments `(#:tests? #f ,@(package-arguments postgresql)))))

(define-public postgis-for-postgresql-13
  (package
   (inherit postgis)
   (name "postgis")
   (version "3.0.2")
   (inputs
    `(("gdal" ,gdal)
      ("geos" ,geos)
      ("giflib" ,giflib)
      ("json-c" ,json-c)
      ("libjpeg" ,libjpeg-turbo)
      ("libxml2" ,libxml2)
      ("pcre" ,pcre)
      ("postgresql" ,postgresql-13)
      ("proj" ,proj)))))

(operating-system
  (bootloader (bootloader-configuration
               (bootloader grub-bootloader)
               (target "/dev/vda")))
  (host-name "??")
  (file-systems %base-file-systems)
  (timezone "UTC")
  (packages (cons* postgresql-13 postgis-for-postgresql-13 %base-packages))
  (services (cons* (service postgresql-service-type (postgresql-configuration
                                                     (postgresql postgresql-13)
                                                     (extension-packages (list postgis-for-postgresql-13))))
                   %base-services)))

Re: How do I correctly relocate PostGIS control files?

[Gary Johnson]

Carlo Zancanaro <carlo@zancanaro.id.au> writes:

> I didn't respond initially because I don't have any specialist
> knowledge about this. I had some free time today, so I did a bit 
> of an investigation and I think I've figured out what's gone wrong for
> you here.
>
> In your original email you gave this definition for postgresql-13:
>
> ...
>
> However, this is missing one important line from the original
> postgresql definition, which applies a patch to the source:
>
>    (patches (search-patches
>    "postgresql-disable-resolve_symlinks.patch"))
>
> ...
>
> I hope that helps!

***************************************************************************

All hail the mighty Carlo Zancanaro!
Applying that patch to my postgresql-13 package does indeed fix the issue!

***************************************************************************

I have also been digging through the code behind
postgresql-service-type, and I worked out what I'm guessing is the
reason that patch actually works.

In gnu/services/databases.scm, postgresql-service-type is declared and
uses the following function to compute the postgresql package to use for
both its database activation (postgresql-activation) and its shepherd
service (postgresql-shepherd-service):

(define (final-postgresql postgresql extension-packages)
  (if (null? extension-packages)
    postgresql
    (package
      (inherit postgresql)
      (source #f)
      (build-system trivial-build-system)
      (arguments
       `(#:modules ((guix build utils) (guix build union))
         #:builder
         (begin
           (use-modules (guix build utils) (guix build union) (srfi srfi-26))
           (union-build (assoc-ref %outputs "out") (map (lambda (input) (cdr input)) %build-inputs))
           #t)))
      (inputs
       `(("postgresql" ,postgresql)
         ,@(map (lambda (extension) (list "extension" extension))
                extension-packages))))))

When extension-packages is (list postgis), then final-postgresql creates
a union-build of both the postgresql and postgis packages, which
essentially just creates a new /gnu/store directory containing the
outputs of both packages.

In exploring my /gnu/store directory, I noticed that even before I
applied Carlo's patch code, there already existed a union-build of my
postgresql-13 and postgis-for-postgresql-13 packages. However, checking
my process table with ps showed that the postgres executable started by
the shepherd service was coming from a /gnu/store directory that ONLY
contained postgresql-13 and NOT from the union-build directory as I
would have expected from the final-postgresql function definition above.

So what gives?

It looks like the individual postgresql-13 and postgis-for-postgresql-13
packages were built correctly and the union-build was also created
correctly (probably either at activation time or at service start time).

HOWEVER, the union-build directory consists primarily of symlinks to the
files in the individual postgresql-13 and postgis-for-postgresql-13
store directories. My guess then is that when the shepherd service fired
up the postgres executable, it was trying to run it from the union-build
directory but after following the symlink, it reported in ps the path to
the target of that symlink rather than the location of the symlink
itself. Then postgres must have taken this symlink target directory as
the actual directory is was being run from, which of course did not
contain any postgis control files.

Long story short, Carlo noticed that the default postgresql package in
gnu/packages/databases.scm, applied this patch to its source code:

postgresql-disable-resolve_symlinks.patch:
***************************************************************************
From 223c82d1d6ed1f29f26307249827ff679e09c780 Mon Sep 17 00:00:00 2001
From: Julien Lepiller <julien@lepiller.eu>
Subject: [PATCH] disable resolve_symlink

---
 src/common/exec.c | 2 ++
 1 file changed, 2 insertions(+)

diff --git a/src/common/exec.c b/src/common/exec.c
index 878fc29..6b3e283 100644
--- a/src/common/exec.c
+++ b/src/common/exec.c
@@ -218,6 +218,8 @@ find_my_exec(const char *argv0, char *retpath)
 static int
 resolve_symlinks(char *path)
 {
+	// On GuixSD we *want* stuff relative to symlinks.
+	return 0;
 #ifdef HAVE_READLINK
 	struct stat buf;
 	char		orig_wd[MAXPGPATH],
--
2.18.0
***************************************************************************

Based on Julien's code comment, I'm guessing this somehow magically
ensures that the postgres executable is run from the within the
union-build directory where its symlink exists rather than from the
postgresql-13 directory where the symlink is pointing.

So now we appear to have a solution to the missing postgis control file
issue. However, I have a few comments still:

1. The postgresql-11 and postgresql-9.6 packages in
   /gnu/packages/databases.scm are also missing Julien's symlink patch
   and thus do not work in combination with postgis currently.

2. The postgis package in /gnu/packages/geo.scm is currently hard-coded
   to compile against postgresql@10.13. We need some way to tie this to
   a dynamically specified postgresql version or we need another postgis
   package for each version of postgresql it is meant to be paired with.

3. It would be good to add a new version of Postgresql to
   gnu/packages/databases.scm as postgresql-11 is quite out of date at
   this point. Here are my package declarations for postgresql-13 and
   postgis-for-postgresql-13 (although I'm not sure if the arguments
   field is shadowing all the arguments from the inherited postgresql
   package from gnu/packages/database.scm):

(define-public postgresql-13
  (package
   (inherit postgresql)
   (name "postgresql")
   (version "13.0")
   (source (origin
            (method url-fetch)
            (uri (string-append "https://ftp.postgresql.org/pub/source/v"
                                version "/postgresql-" version ".tar.bz2"))
            (sha256
             (base32
              "15i2b7m9a9430idqdgvrcyx66cpxz0v2d81nfqcm8ss3inz51rw0"))
            (patches (search-patches "postgresql-disable-resolve_symlinks.patch"))))
   (arguments `(#:tests? #f ,@(package-arguments postgresql)))))

(define-public postgis-for-postgresql-13
  (package
   (inherit postgis)
   (name "postgis")
   (version "3.0.2")
   (inputs
    `(("gdal" ,gdal)
      ("geos" ,geos)
      ("giflib" ,giflib)
      ("json-c" ,json-c)
      ("libjpeg" ,libjpeg-turbo)
      ("libxml2" ,libxml2)
      ("pcre" ,pcre)
      ("postgresql" ,postgresql-13)
      ("proj" ,proj)))))


I'm hoping that Julien or another Guix maintainer can make these changes
to mainline Guix in order to better support geospatial database
programming on our awesome OS.

Happy hacking,
  Gary

-- 
GPG Key ID: 7BC158ED
Use `gpg --search-keys lambdatronic' to find me
Protect yourself from surveillance: https://emailselfdefense.fsf.org
=======================================================================
()  ascii ribbon campaign - against html e-mail
/\  www.asciiribbon.org   - against proprietary attachments

Why is HTML email a security nightmare? See https://useplaintext.email/

Please avoid sending me MS-Office attachments.
See http://www.gnu.org/philosophy/no-word-attachments.html