diff options
| -rw-r--r-- | BACKLOG.md | 4 | ||||
| -rw-r--r-- | _drafts/temporary-postgresql-server.md | 100 | ||||
| -rw-r--r-- | files/temp-postgres.sh | 87 | ||||
| -rw-r--r-- | posts/temporary-postgresql-server.md | 38 |
4 files changed, 128 insertions, 101 deletions
@@ -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). |