Nix Recipe: Setup Postgresql

This is a nix recipe for building and running postgresql-12 in a virtual nix environment.

Step 1: Create shell.nix

Create your project folder and save the following file as shell.nix

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
let
nixpkgs = import (builtins.fetchTarball https://github.com/NixOS/nixpkgs/archive/20.03.tar.gz) {
  overlays = [];
  config = {};
};

in
with nixpkgs;

stdenv.mkDerivation {
  name = "postgres-env";
  buildInputs = [];

  nativeBuildInputs = [
    zsh
    vim
    geos
    gdal
    nixpkgs-fmt

    # postgres-12 with postgis support
    (postgresql_12.withPackages (p: [ p.postgis ]))
  ];

  postgresConf =
    writeText "postgresql.conf"
      ''
        # Add Custom Settings
        log_min_messages = warning
        log_min_error_statement = error
        log_min_duration_statement = 100  # ms
        log_connections = on
        log_disconnections = on
        log_duration = on
        #log_line_prefix = '[] '
        log_timezone = 'UTC'
        log_statement = 'all'
        log_directory = 'pg_log'
        log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
        logging_collector = on
        log_min_error_statement = error
      '';


  # ENV Variables
  LD_LIBRARY_PATH = "${geos}/lib:${gdal}/lib";
  PGDATA = "${toString ./.}/.pg";

  # Post Shell Hook
  shellHook = ''
    echo "Using ${postgresql_12.name}."

    # Setup: other env variables
    export PGHOST="$PGDATA"
    # Setup: DB
    [ ! -d $PGDATA ] && pg_ctl initdb -o "-U postgres" && cat "$postgresConf" >> $PGDATA/postgresql.conf
    pg_ctl -o "-p 5555 -k $PGDATA" start
    alias fin="pg_ctl stop && exit"
    alias pg="psql -p 5555 -U postgres"
  '';
}

Note:

Here, the nix packages are installed from 20.03 release.
Postgres is installed with PostGIS extension.
Above postgres server runs with local configuration specified in postgresConf variable, use this to modify/add config for postgres.
All data is stored in project folder’s .pg directory, configured by $PGDATA variable.
$PGHOST is configured to be same as $PGDATA.
geos and gdal libraries are also installed.

Step 2: Run nix shell

Run below command to build and start postgresql_12 in your virtual environment.

1
nix-shell --pure shell.nix

Note:

Postgres is running here on port: 5555, so it won’t conflict with other Postgres server, if running.
Running this command multiple times won’t start multiple postgres server, since it is controlled by the lock file: ./.pg/postmaster.pid
To exit the nix shell, run fin, this will stop the postgres server.