Using PostgreSQL in a nix-shell

Sometimes (well, quite often of late, at work) I want to load some data into a PostgreSQL database and do some work with it. I use a Mac at work, and used to do this by installing PostgreSQL using Homebrew and running it system-wide. This stung me a couple of times; Homebrew would upgrade the package to a new minor release (9.5 -> 9.6, etc) and create a new data directory with the version number in it, meaning that a brew upgrade would sometimes make the data I loaded previously inaccessible. I never lost anything through this route, but it did slow me down.

Nowadays, I’ve switched to using Nix for everything I can get away with, which means I can install PostgreSQL as my own user. Using Nix shells, I can even have many of them, one for each different project.

In each directory, I have the simplest shell.nix I can get away with:

with import <nixpkgs> {};
mkShell {
  buildInputs = [
    postgresql
  ];
}

Creating this and then running nix-shell means you’ll end up with a new shell with PostgreSQL available, but not actually running. To make it work, you can do this the first time:

# Create a database with the data stored in the current directory
initdb -D .tmp/mydb

# Start PostgreSQL running as the current user
# and with the Unix socket in the current directory
pg_ctl -D .tmp/mydb -l logfile -o "--unix_socket_directories='$PWD'" start

# Create a database
createdb mydb

Then every other time you re-enter that shell, you can just run the part that starts the database. It will keep running until you reboot, or stop it like this:

pg_ctl -D .tmp/mydb stop

Using Nix’s tools you can pin PostgreSQL to a particular version if you like, or use a more specific dependency like postgresql_9_6 if you need it.

Eventually, I will figure out if I can automate a lot of this with shellHook or similar, but it hasn’t annoyed me enough yet to make me look into doing that.