Nix Recipe: Setup Postgresql

2-minute read

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

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

    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.
  • Run nix-shell --pure shell.nix to build and start postgresql_12 in your virtual environment.

    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.