Post Temporary PostgreSQL server

4 files changed, 128 insertions, 101 deletions

diff --git a/BACKLOG.md b/BACKLOG.md
index 4cc59da..efe0958 100644
--- a/BACKLOG.md
+++ b/BACKLOG.md
@@ -42,7 +42,6 @@
 * Dotfiles under revision control: How I manage my personal configuration files with Git
 * My preferred tools: A whirlwind tour of my preferred command-line tools
 * SSH proxy: How to establish a secure tunnel between two firewalled machines
-* Temporary PostgreSQL server: A shell script to run the PostgreSQL server off a temporary directory
 * Your favorite Newsletter may track you
 * Redirect and kill non-interactive subshells (in a POSIX shell script)
 * HTTP health check using curl(1) and cron(8)
@@ -55,3 +54,6 @@
 * SQLite hexastore vs. Neo4j
 * Setup a FreeBSD sftp server with basic email notifications from the command-line using bash, ssh, and the DigitalOcean command-line application
 * Cloud/VPS provider comparison
+* goaccess log format for OpenBSD httpd
+* Practical, cross-platform, append-only backups using restic, rclone, and ssh: ready to use scripts/shortcuts for OpenBSD, Linux, macOS, Windows 10
+* nix vs. guix
diff --git a/_drafts/temporary-postgresql-server.md b/_drafts/temporary-postgresql-server.md
deleted file mode 100644
index a46cd86..0000000
--- a/_drafts/temporary-postgresql-server.md
+++ /dev/null
@@ -1,100 +0,0 @@
----
-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
-```
diff --git a/files/temp-postgres.sh b/files/temp-postgres.sh
new file mode 100644
index 0000000..aceef4f
--- /dev/null
+++ b/files/temp-postgres.sh
@@ -0,0 +1,87 @@
+#! /bin/sh
+
+# Copyright (c) 2019-2020 Stefan Kreutz <mail@skreutz.com>
+#
+# Permission to use, copy, modify, and distribute this software for any purpose
+# with or without fee is hereby granted, provided that the above copyright
+# notice and this permission notice appear in all copies.
+#
+# THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES WITH
+# REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY
+# AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY SPECIAL, DIRECT,
+# INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM
+# LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR
+# OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR
+# PERFORMANCE OF THIS SOFTWARE.
+
+# Run the PostgreSQL server off a temporary data directory.
+#
+# Expects exactly two arguments: database name and user name. You may also set
+# the port by setting the environment variable PGPORT, see postgres(1).
+#
+# Exits 0 on SIGINT, and >0 if an error occurs.
+#
+# First published at https://www.skreutz.com/posts/temporary-postgresql-server/
+# on 14 October 2020.
+
+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 -d )" || {
+  ( >&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 the 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
diff --git a/posts/temporary-postgresql-server.md b/posts/temporary-postgresql-server.md
new file mode 100644
index 0000000..ba3c573
--- /dev/null
+++ b/posts/temporary-postgresql-server.md
@@ -0,0 +1,38 @@
+---
+title: "Temporary PostgreSQL server"
+description: "A shell script to run the PostgreSQL server off a temporary directory."
+published: 2020-10-14
+---
+
+Today I want to share a simple shell script to spin up a temporary PostgreSQL database.
+I've been using this script for two years and found it useful enough to publish it now.
+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.
+You can download it [here](/files/temp-postgres.sh).
+
+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 your favorite functional package manager to install PostgreSQL on-the-fly if you like.
+In case of [Nix](https://nixos.org/), simply replace the shebang on the first line of the script with the following two lines.
+
+```sh
+#! /usr/bin/env nix-shell
+#! nix-shell --pure --packages postgresql -i bash
+```
+
+In case of [GNU Guix](https://guix.gnu.org/), use the following shebang.
+Please note, however, that the `env` command on your OS might not support the used `-S` argument.
+
+```sh
+#! /usr/bin/env -S guix environment --pure --ad-hoc postgresql bash coreutils -- bash
+```
+
+You can find the full shell script [here](/files/temp-postgres.sh).