Heads up: upcoming changes to the PostgreSQL infrastructure and NixOS module

Development
1

Hello *,

I have some changes planned for the past few weeks to the PostgreSQL packaging and NixOS module, and I plan on committing the first revision of them soon. The basics are outlined in issue #38616. The pull request implementing these changes is #38698. Here’s the overview of the changes as they stand.

I’m sending this to spread out the ideas to a wider audience and hopefully field any suggestions – I think these changes are strictly an improvement and will be welcome to anyone running PostgreSQL, but there are certainly things to still improve.

Nixpkgs updates

The main change that motivates all this is due to deficiencies in the Nixpkgs packaging for PostgreSQL, the main two being:

  1. PostgreSQL extensions are not modular. Current extension builds of things like timescaledb are built against against a single version of Postgres – the version that is blessed with the name postgres in all-packages.nix. This has various ramifications:
    • Extensions are only built against one version of PG, not all of them, so you get cache misses if you pick a non-default version
    • There is no way to specify whether an extension builds against a PostgreSQL version or not. If you use something like Postgres 9.3 with timescaledb, you get a build failure, rather than an evaluation failure. This isn’t friendly.
    • Usage of a non-default postgres version in NixOS requires ugly overrides, to make sure extensions built against the version specified. Using PostgreSQL 9.3 with extensions, but without overriding the default version (currently: 9.6) will result in strange runtime errors.
  2. There is no attribute set namespace for PostgreSQL versions. Following from 1), there is no set of namespaces for different versions of postgresql. Much like how we have package sets for major versions of Python or Haskell, we should also have package sets for major versions of PostgreSQL.

These changes introduce these additions. There is now a set of packages for PostgreSQL major versions, each set containing the extensions that are supported. This means top-level.nix now has something more like:

  postgresql93Packages = ... ;
  postgresql94Packages = ... ;
  postgresql95Packages = ... ;
  postgresql96Packages = ... ;
  postgresql10Packages = ... ;
  postgresql11Packages = ... ;

  # default version is 9.6
  postgresqlPackages = postgresql96Packages;

Furthermore, each attribute set contains a .postgres attribute, pointing to the actual server package, as well as an assortment of various extensions you can install – provided they can be built against the actual version of Postgres you want. For example, the following attributes exist:

postgresql96Packages.timescaledb
postgresql10Packages.timescaledb

But postgresql94Packages.timescaledb does not exist – the timescale extension is guarded as only supporting versions of PostgreSQL 9.6 or newer, using a new passthru predicate in the expression

stdenv.mkDerivation rec {
  name = "timescaledb-${version}";
  version = "0.9.1";

  ...

  passthru = {
    versionCheck = builtins.compareVersions postgresql.version "9.6" >= 0;
  };

  meta = with stdenv.lib; {
    ....
  };
};

There are some other knock-on changes, like the fact all the old attribute names have been deprecated for the new attrset-based naming. This means usage of things like plv8 will now throw an evaluation error, telling you to use postgresqlPackages.plv8 or whatnot.

Enhanced NixOS Module

The new NixOS module takes the above Nixpkgs enhancements and extends them to the module interface. In particular, there are two new service configuration options.

  1. The new .packages attribute points to the postgres package set you want to use. It should be set to an attrset and not a derivation itself. For example, if you want to use PostgreSQL 9.4 instead of the default version, you would specify services.postgresql.packages = postgresql94Packages;

  2. The new .plugins attribute specifies a predicate, which returns a list of extensions we should enable for Postgres. This is similar to other functions like ghcWithPackages – the idea is that the function is provided an attrset, and it simply selects out some attributes it wants.

These changes keep backwards compatibility with the old NixOS module interface, so you don’t necessarily need to upgrade immediately. However, it’s unclear if backwards compatibility is so necessary, since it requires keeping old attribute names too.

For example, here was a snippet of my old NixOS module, running on my NixOS Ryzen server:

  services.postgresql =
    let
      mkPlug = p: p.override { postgresql = config.services.postgresql.package; };
    in {
      enable = true;
      package = pkgs.postgresql100;
      dataDir = "/data/pgsql";

      extraConfig = "shared_preload_libraries = 'timescaledb'";
      extraPlugins = with pkgs; map mkPlug
        [ postgis
          timescaledb
          cstore_fdw
          pg_hll
          pg_cron
          pg_topn
        ];
    };

This is the new snippet using this improved module, which I’ve been running nicely for several weeks:

  services.postgresql = {
    enable = true;
    dataDir = "/data/pgsql";

    packages = pkgs.postgresql10Packages;
    plugins = p: with p; # brings the following names into scope
      [ cstore_fdw
        pg_cron
        pg_hll
        pg_jobmon
        pg_repack
        pg_topn
        pgtap
        plv8
        postgis
        timescaledb
      ];

    extraConfig = "shared_preload_libraries = 'timescaledb'";
  };

While the overall LOC isn’t really reduced, the scope for errors is greatly reduced – and the actual logic reads much better without being cluttered by local definitions.

Immediate improvements before pushing

My immediate work before pushing will be to upgrade all the NixOS module tests to the new API, but this is not yet done. This is the major blocker still remaining, IMO.

Future improvements

While I am not yet done, what you see above is basically what’s there right now. I believe this can be committed on its own and is a strict improvement.

But there are three major things I’d like to do still before 18.09:

  1. Extract the logic from the NixOS module that bundles PostgreSQL with its extensions into an environment, necessary for loading extensions. Add it to Nixpkgs – and offer a postgresWithPackages function. For example, it’s completely reasonable to want to start a local database with pg_init myself, with some extensions, for testing things. I don’t need NixOS at all for this, but it’s currently impossible to do this, because there’s no way of building an environment (buildEnv) containing the right filesystem hierarchy.

  2. I’d like to improve the NixOS manual further and include all the documentation that was meant to be written.

  3. I’d like to bump the default PostgreSQL version to PostgreSQL 10 in NixOS 18.09.

After NixOS 18.09 is released, all of the old supporting code for the old NixOS module, attribute names, etc can be deleted.

Please review & Feedback

If you have time to give it a good view, I’d appreciate any reviews on #38698. Even if you don’t have time to review, any feedback or a simple +1 would be much appreciated; I hope these are welcome changes.

5 Likes
2

Can we use the top-level all-packages attrpath naming conventions in An attrpath format policy proposal - #11 by FRidh ?

For example,

postgresql93 -> postgresql_9_3
3 Likes
3

I’d be willing to make this change. If I did, should the old name (postgres93) be deprecated until 18.09, or merely removed?

There is an another way that I can see, too, for the long run. For the most part, the postgresql* derivations are merely used as buildInput dependencies for libpq, so that things can talk to Postgres by way of libpq.so.

In this new setup, every postgres attrset has a single .postgres attribute, pointing to the server package itself. It does not need to be versioned, because the version is implied by the attrset name.

Furthermore, almost all derivations (except for specifically versioned ones) simply ask for libpq. So, why not:

  • Remove all variants of postgresqlXX
  • Create a new, top-level attribute called libpq, reflecting the fact you want libpq the library in your buildInputs (which is almost always the case). It can be defined as:
postgresqlPackages = postgresql96Packages; # postgresql 9.6 is default
libpq = postgresqlPackages.postgres;
  • Change uses of postgresql to libpq (of course)

If a package such as pgpool93 needs a specific libpq version, it can ask for it by having an explicitly named attribute, to make it clear where it came from, as we often do now:

# in some expression
{ stdenv, libpq_9_3, ... }:
...

# in all-packages.nix
  something = callPackage ./pkgs/something/needs/libpq/ {
    libpq_9_3 = postgresql93Packages.postgres;
  };

This change might not be necessary though, it was just something I thought up off hand to optimize for the more common case of needing libpq, and more accurately reflect the real buildInputs a package needs. It also nicely avoids having to litter the top level nameset with libpq_X_Y variants, when they can just get it from the postgresXYPackages instead explicitly if they really want that.

4

That can go in the aliases file.

5

As @thoughtpolice is short on time, I took this into my hands. I plan to change PG to pg11 by Feb 25 (before feature freeze).

So far, this was done:

This is awaiting merge

After all this is merged, PG11 can be set as default. I don’t do any deprecations for NixOS, will only update docs and examples. The example above will look like

  services.postgresql = {
    enable = true;
    dataDir = "/data/pgsql";

    package = pkgs.postgresql_11;
    # this will be supported as well
    # package = pkgs.postgresql_11.withPackages (ps: [ ps.pg_repack ]); 
    extraPlugins = with pkgs.postgresql_11.pkgs;
      [ cstore_fdw
        pg_cron
        pg_hll
        pg_jobmon
        pg_repack
        pg_topn
        pgtap
        plv8
        postgis
        timescaledb
      ];

    extraConfig = "shared_preload_libraries = 'timescaledb'";
  };
2 Likes