How to develop a PostgreSQL extension on NixOS?

Help
1

I’m developing a PostgreSQL extension and I want to be able to test it. I’ve managed to get it compiled, so I have the necessary .so, .control, .sql files and all that. They just need to be moved into the right directories in order to be loadable by the CREATE EXTENSION command. This would normally be automatically done by the Makefile (like this one) using make install. The problem is that those directories are immutable (/run/current-system/sw/share/postgresql aka /nix/store/ygqkd75dny3rljgrnrv6zdqgmwnh679b-postgresql-and-plugins-14.10/share/postgresql/ and others).

My thinking is that I’ll need to build a custom postgresql server preloaded with my extension every time I make a change. Unfortunately my nix-fu isn’t strong and looking at package sources for postgresql, postgis, isn’t helping. I would greatly appreciate some guidance here.

2

Update: I’ve gotten it mostly working with this shell.nix:

{ pkgs ? import <nixpkgs> {} }:

let 
  myExtension = pkgs.stdenv.mkDerivation rec {
    pname = "myExtension";
    version = "0.1.0";

    src = ./.;

    buildInputs = [ pkgs.postgresql ];

    makeFlags = [
      "USE_PGXS=1"
    ];

    installPhase = ''
      mkdir -p $out/lib
      mkdir -p $out/share/postgresql/extension
      install -D ${pname}.so -t $out/lib
      install -D ${pname}.control -t $out/share/postgresql/extension
      install -D ${pname}--${version}.sql -t $out/share/postgresql/extension
    '';
  };

  postgresqlSrc = pkgs.fetchurl {
    url = "https://ftp.postgresql.org/pub/source/v${pkgs.postgresql.version}/postgresql-${pkgs.postgresql.version}.tar.bz2";
    sha256 = "sha256-j1OqldeOuOglNupGtoGHeTtCu6O09lqjQvVAsjybEKY=";
  };

  modifiedPostgresql = pkgs.postgresql.overrideAttrs (old: {
    src = postgresqlSrc;

    configureFlags = old.configureFlags ++ [
      "--with-includes=${myExtension}/include"
      "--with-libraries=${myExtension}/lib"
    ];

    postInstall = ''
      ${old.postInstall or ""}
      # Copy over the extension files
      mkdir -p $out/share/postgresql/extension
      mkdir -p $lib/lib
      cp -r ${myExtension}/share/postgresql/extension/* $out/share/postgresql/extension
      cp -r ${myExtension}/lib/* $lib/lib
    '';
    # postInstall = ''
    #   ${old.postInstall or ""}
    #   # Copy over the extension files
    #   mkdir -p $out/share/postgresql/extension
    #   cp -r ${myExtension}/share/postgresql/extension/* $out/share/postgresql/extension
    # '';
  });
in
pkgs.mkShell {
  buildInputs = [ modifiedPostgresql ];
  shellHook = ''
    export PATH=${modifiedPostgresql}/bin:$PATH
  '';
}

Makefile:

EXTENSION = myExtension
DATA = myExtension--0.1.0.sql
# Check if nix exists, and set PG_CONFIG accordingly
IS_NIX := $(shell which nix > /dev/null 2>&1 && echo yes || echo no)
ifeq ($(IS_NIX),yes)
   PG_CONFIG = ./pg_config_nix
else
   PG_CONFIG = pg_config
endif
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

pg_config_nix:

#!/usr/bin/env bash

# pg_config on NixOS returns some wrong outputs
# this wrapper script aims to correct that

set -euo pipefail

INCLUDEDIR=$(nix-store -r $(which postgres) 2>/dev/null)/include

if [[ $# -eq 0 ]]; then
    pg_config | grep -v INCLUDEDIR
    echo "INCLUDEDIR = $INCLUDEDIR"
    echo "PKGINCLUDEDIR = $INCLUDEDIR"
    echo "INCLUDEDIR-SERVER = $INCLUDEDIR/server"
    exit 0
fi

# Handle specific arguments
for arg in "$@"
do
    case $arg in
        --includedir) echo $INCLUDEDIR; break;;
        --pkgincludedir) echo $INCLUDEDIR; break;;
        --includedir-server) echo "$INCLUDEDIR/server"; break;;
        *) pg_config $arg; break;;
    esac
done

However, every time I want to test the extension again the entirety of postgresql needs to be rebuilt. Still working on a solution for that. Would appreciate any help!

3

Here’s what I’ve settled on for anyone who runs into the same problem. It seems hacky, unfortunately.


let 
  postgres = pkgs.postgresql_16;

  postgresqlSrc = pkgs.fetchurl {
    url = "https://ftp.postgresql.org/pub/source/v${postgres.version}/postgresql-${postgres.version}.tar.bz2";
    sha256 = "sha256-zjxNhdGbASH+DT+O8fpgH3GYnob4pm99w61UbdVWT+w=";
  };

  pwd = builtins.getEnv "PWD";
  modifiedPostgresql = postgres.overrideAttrs (old: {
    src = postgresqlSrc;

    postInstall = ''
      ${old.postInstall or ""}
      # Copy over the extension files
      mkdir -p $out/share/postgresql/extension
      mkdir -p $lib/lib
      ln -s ${pwd}/myExtension.so $lib/lib
      ln -s ${pwd}/myExtension.control $out/share/postgresql/extension
      ln -s ${pwd}/myExtension--0.1.0.sql $out/share/postgresql/extension
    '';
  });
in
pkgs.mkShell {
  buildInputs = [ modifiedPostgresql ];
  shellHook = ''
    export PATH=${modifiedPostgresql}/bin:$PATH
  '';
}