Initial version of "ppq" - portable PostgreSQL

This allows running PostgreSQL locally without having to set up a
system-wide service.  It is portable in the sense that you can move
the directory around, and you can run multiple instances side-by-side.

8 files changed, 143 insertions, 0 deletions

diff --git a/.envrc b/.envrc
new file mode 100644
index 0000000..015c979
--- /dev/null
+++ b/.envrc
@@ -0,0 +1,11 @@
+# Settings for ppq configuration
+export PPQ_POSTGRES_VERSION=15
+export PPQ_POSTGRES_DIR=$(expand_path postgres)
+export PPQ_POSTGRES_DATA_DIR=$(expand_path postgres/data)
+
+# Settings for libpq-based clients and postgres itself
+export PGDATA="${PPQ_POSTGRES_DATA_DIR}"
+export PGHOST="${PPQ_POSTGRES_DATA_DIR}"
+export PGUSER=postgres
+
+use nix
diff --git a/.gitignore b/.gitignore
new file mode 100644
index 0000000..fc2d52a
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1,2 @@
+postgres/data
+postgres/postgresql_overrides.conf
\ No newline at end of file
diff --git a/README b/README
new file mode 100644
index 0000000..a4aa7c0
--- /dev/null
+++ b/README
@@ -0,0 +1,22 @@
+ppq - Portable PostgreSQL
+=========================
+
+This allows running PostgreSQL locally without having to set up a
+system-wide service. It is portable in the sense that you can move the
+directory around, and you can run multiple instances side-by-side.
+
+It serves as a good basis to use as a development environment, for
+projects that require a Postgres server.
+
+How to use
+----------
+
+The best way to run this project is with Nix and direnv.  You don't
+need NixOS.
+
+Simply cd into the directory, and direnv should take care of the rest.
+Then you can run a Postgres server with `./ppq start`.
+
+All Postgres client programs (like `psql`, `pg_restore` etc) are
+available and environment variables like `PGUSER` and `PGHOST` are set
+up to ensure it will talk to the local Postgres server automatically.
\ No newline at end of file
diff --git a/nix/default.nix b/nix/default.nix
new file mode 100644
index 0000000..ba8291d
--- /dev/null
+++ b/nix/default.nix
@@ -0,0 +1,15 @@
+{
+  system ? builtins.currentSystem,
+  pkgs ? import ./pkgs.nix { inherit system; }
+}:
+let
+  lib = pkgs.lib;
+  shell = pkgs.mkShell {
+    name = "portable-postgresql";
+    buildInputs = with pkgs; [
+      ppq_postgresql
+    ];
+  };
+in {
+  inherit shell pkgs;
+}
diff --git a/nix/pkgs.nix b/nix/pkgs.nix
new file mode 100644
index 0000000..cd15894
--- /dev/null
+++ b/nix/pkgs.nix
@@ -0,0 +1,31 @@
+{
+  system ? builtins.currentSystem,
+  nixpkgs ? builtins.fetchTarball {
+    url = "https://github.com/NixOS/nixpkgs/archive/22.11.tar.gz";
+    sha256 = "11w3wn2yjhaa5pv20gbfbirvjq6i3m7pqrq2msf0g7cv44vijwgw";
+  }
+}:
+let
+  # use the first version in the list for each component
+  getMajorVersion = builtins.getEnv "PPQ_POSTGRES_VERSION";
+
+  overlay = self: super: {
+    # NOTE: Overlaying `postgresql` is not possible anymore, leads to
+    # error: infinite recursion encountered
+    #
+    # TODO: Make extension packages more easily configurable.  For now
+    # just load whatever we've ever needed.
+    ppq_postgresql = super."postgresql_${getMajorVersion}".withPackages (p: [
+      p.postgis
+    ]);
+  };
+
+  pkgs =
+  import nixpkgs {
+    inherit system;
+    overlays = [
+      overlay
+    ];
+  };
+in
+pkgs
diff --git a/postgres/postgresql.conf b/postgres/postgresql.conf
new file mode 100644
index 0000000..0d16559
--- /dev/null
+++ b/postgres/postgresql.conf
@@ -0,0 +1,11 @@
+# Disable listening on a port to make it more self-contained and allow multiple instances
+listen_addresses=''
+
+# Avoid random shell locale settings from messing with the db settings - default to C locale
+lc_messages='C'
+lc_monetary='C'
+lc_numeric='C'
+lc_time='C'
+
+# Put any overrides into your own config file here:
+include_if_exists 'postgresql_overrides.conf'
\ No newline at end of file
diff --git a/ppq.sh b/ppq.sh
new file mode 100755
index 0000000..29a9d5e
--- /dev/null
+++ b/ppq.sh
@@ -0,0 +1,50 @@
+#!/bin/sh
+set -euo pipefail
+
+kill_jobs() {
+    jobs -p | xargs -rn1 kill || true
+}
+
+trap "kill_jobs" EXIT
+
+POSTGRES="postgres -c unix_socket_directories=${PPQ_POSTGRES_DATA_DIR}"
+
+# NOTE: Environment variables here are set up by .envrc
+
+postgres_init () {
+    local PGDATA_NEW="${PGDATA}.new"
+
+    rm -rf "${PGDATA_NEW}"
+    pg_ctl -s init -D "${PGDATA_NEW}" -o "-E UTF-8 --no-locale -A trust -U postgres"
+    echo "${PGDATA_NEW}"
+
+    # Clobber postgresql.conf
+    ln -fs "${PPQ_POSTGRES_DIR}/postgresql.conf" "${PGDATA_NEW}/"
+    ln -fs "${PPQ_POSTGRES_DIR}/postgresql_overrides.conf" "${PGDATA_NEW}/" || true
+
+    if [ -f "${PPQ_POSTGRES_DIR}/prepare.sql" ]; then
+	$POSTGRES --single -D "${PGDATA_NEW}" postgres < "${PPQ_POSTGRES_DIR}/prepare.sql" >/dev/null
+    fi
+
+    mv "${PGDATA_NEW}" "${PGDATA}"
+    sync
+}
+
+postgres_start () {
+    [ -d "${PGDATA}" ] || postgres_init
+    $POSTGRES
+}
+
+
+COMMAND="${1:-}"
+[ -n "$COMMAND" ] && shift
+
+case "$COMMAND" in
+    start)
+	postgres_start
+	;;
+    *)
+      echo "Available commands:"
+      echo "  start - start the PostgreSQL server"
+      ;;
+esac
diff --git a/shell.nix b/shell.nix
new file mode 100644
index 0000000..53c2c90
--- /dev/null
+++ b/shell.nix
@@ -0,0 +1 @@
+(import ./nix/default.nix {}).shell