---
title: "Temporary PostgreSQL server"
description: "A simple shell script to run the PostgreSQL server off a temporary directory."
published: 2020-06-02
---

Sometimes I need to spin up a local PostgreSQL server for one-off purposes.
In these cases, I don't particularly like to either configure PostgreSQL manually or use a pre-configured Docker image because I experience this as overkill and inaccessible respectively.
Instead, I use the simple shell script below to run the PostgreSQL server off a temporary directory until I decide to `kill` it.
The script is inspired by a [blog post](https://www.johbo.com/2017/on-demand-postgresql-for-your-development-environment.html) by Johannes Bornhold that reminded me of Unix' simplicity.

The script essentially performs seven steps:

* Create a temporary directory using `mktemp`
* Initialize the directory using `initdb`
* Serve the directory using `postgres`
* Ensure the server is up using `pg_isready`
* Create a database using `createdb`
* Wait for a `SIGINT`
* Remove the temporary directory

Obviously, you still need to install PostgreSQL to use the script.
However, you may use the [Nix package manager](https://nixos.org/nix/) to install PostgreSQL _on-the-fly_ and have it removed too, if you are into this.
Simply put the following shebang in front of the script.

```sh
#! /usr/bin/env nix-shell
#! nix-shell --pure --packages postgresql -i bash
```

Here is the full script with minimal error handling.

```sh
#! /bin/sh

# temp_postgres runs a PostgreSQL server with a temporary data directory until
# it receives a SIGINT.

set -o nounset

# Remove the temporary directory before exiting
trap 'quit' INT
quit() {
  code="${1:-0}"
  trap '' INT TERM
  kill -TERM 0
  wait
  rm -rf "${tmpdir-}" || {
    >&2 printf "temp_postgres: failed to remove temporary directory \"%s\"\\n" "${tmpdir}"
    [ "${code}" -ne 0 ] || code=1
  }
  exit "${code}"
}

# Parse arguments
[ $# -eq 2 ] || {
  >&2 printf "temp_postgres: invalid arguments\\n"
  printf "Usage: temp_postgres <dbname> <username>\\n"
  quit 1
}
dbname="$1"
username="$2"

# Create a temporary directory
tmpdir="$( mktemp --directory )" || {
  >&2 printf "temp_postgres: failed to create temporary directory\\n"
  quit 1
}

# Initialize the directory
initdb --pgdata="${tmpdir}" --username="${username}" || {
  >&2 printf "temp_postgres: failed to initialize database\\n"
  quit 1
}

# Serve the directory
( postgres -k "${tmpdir}" -D "${tmpdir}" </dev/null ) &

# Test the connection
sleep 1
pg_isready --host="${tmpdir}" --dbname="postgres" --username="${username}" --timeout=10 || {
  >&2 printf "temp_postgres: failed to connect to server\\n"
  quit 1
}

# Create a database
createdb --host="${tmpdir}" --username="${username}" --no-password "${dbname}" || {
  >&2 printf "temp_postgres: failed to create database\\n"
  quit 1
}

printf '
Connect with the following command:

\tpsql --host=localhost "%s" "%s"

' "${dbname}" "${username}"

wait
```