Unable to setup postgres in nix-shell

Development
1

Hi, I’m working on a Phoenix/Elixir project which is managed using nix-shell. I’m having issues regarding postgres setup.

I’m starting server using pg_ctl -D $PGDATA start and it outputs as:

pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....2021-09-01 19:19:00.780 PKT [72367] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2021-09-01 19:19:00.784 PKT [72367] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-09-01 19:19:00.806 PKT [72369] LOG:  database system was interrupted; last known up at 2021-09-01 19:14:57 PKT
2021-09-01 19:19:00.848 PKT [72369] LOG:  database system was not properly shut down; automatic recovery in progress
2021-09-01 19:19:00.849 PKT [72369] LOG:  redo starts at 0/16D84A0
2021-09-01 19:19:00.849 PKT [72369] LOG:  invalid record length at 0/16D8580: wanted 24, got 0
2021-09-01 19:19:00.849 PKT [72369] LOG:  redo done at 0/16D8548
2021-09-01 19:19:00.862 PKT [72367] LOG:  database system is ready to accept connections
 done
server started

when I execute createuser postgres --createdb --echo in nix-shell envoirnment it produces following error even though the run directory exists.

createuser: could not connect to database template1: could not connect to server: No such file or directory
	Is the server running locally and accepting
	connections on Unix domain socket "/run/postgresql/.s.PGSQL.5432"?

No other postgres instance is running.
In pg_hba.conf auth method is set to trust.
unix_socket_directories is set to /tmp.

shell.nix looks something like

{ pkgs ? import <nixpkgs> {} }:

with pkgs;

let
  inherit (lib) optional optionals;

  elixir = beam.packages.erlangR22.elixir_1_10;
  postgresql = postgresql_11;
in

mkShell {
  buildInputs = [
    ps
    elixir
    coreutils
    which
    git
    postgresql
    redis
    doxygen
    mongodb-tools
    redis-dump
    (python37.withPackages(ps: with ps; [ credstash awscli ]))
    cmake
    nix-prefetch-git
    zlib
    jq
    teleport
  ]);

  # Fix GLIBC Locale
  LOCALE_ARCHIVE = lib.optionalString stdenv.isLinux
    "${pkgs.glibcLocales}/lib/locale/locale-archive";
  LANG = "en_US.UTF-8";

  # Put the PostgreSQL and Redis databases in the project diretory.
  PGDATA="./.db/postgres";
  RDDATA="./.db/redis";

  shellHook = ''
  ERL_INCLUDE_PATH="${erlangR22}/lib/erlang/usr/include";
}

I totally a noob to Nix and have no idea how to get it working.
Any help would be appreciated, thanks.

1 Like
2

I once had a setup that installed and started a postgresql server as part of the nix-shell.

Though it was in general quite tedious to deal with and broke every now and then.

In the meantime I got back to the following rule of thumb:

  • use nix if it is “just” a program that is run once every now and then
  • use docker if it is some service that needs to be running for the app in dev

I can take a look later this evening, if I can recover what I did in that setup if you still want me to.

3

I’ve been using something like:

_start_pg(){
	pg_ctl -w -l $PGDATA/log start &> /dev/null
}
_stop_pg(){
	pg_ctl stop &> /dev/null
}


export PGDATA="$PWD/.pg"

if [ ! -d $PGDATA ]; then
	initdb &> /dev/null
	CREATE=true
fi

_start_pg
register_cleanup _stop_pg # ~ trapping HUP/EXIT

if [[ $CREATE ]]; then
	createuser -s postgres &> /dev/null
	# set a stable crappy password for local/dev
	# create db
fi

That’s working fine for me with otherwise stock pg configs (i.e., I’m not fiddling with trust or unix_socket_directories), with 3 caveats:

  • I’m using macOS and can’t recall for sure atm if I’ve run this iteration of this dev environment on a Linux/NixOS.
  • I’m still using pg10, so I wouldn’t know yet if 11 somehow disrupts this.
  • It isn’t perfect; if you CTRL+C at the shell, it’ll kill the postgres server. I don’t really recall if I cargo-culted the HUP/EXIT signals. Haven’t tried to debug it yet.

If you cribbed the settings tweaks from an existing example and haven’t tried without them, I’d give that a go. If you’ve already confirmed it doesn’t work, I’d guess that there’s a Linux/macOS difference here, and that these settings are the right place to focus?

1 Like
4

I have a shell.nix for Elixir/Phoenix/PostgreSQL stack (heavily commented); you can try it out with

nix-shell -v -E 'import (builtins.fetchurl "https://raw.githubusercontent.com/toraritte/shell.nixes/main/elixir-phoenix-postgres/shell.nix")'

and the README is full of troubleshooting tips (because every year I spend hours re-acquainting myself with PostgreSQL…).

I would also give the -h | --host= command line switches a try - these were almost always the solution in my case. (As far as I can tell, all PostgreSQL commands will take this; e.g.,

createdb $(whoami) --host=$PGDATA
psql --host=localhost --username=$(whoami) --dbname=$(whoami) --port=5432

where PGDATA is where I put all the PostgreSQL configuration information; see the comments in shell.nix, it’s not long.

1 Like