From 9d05c9ca73c2b501744d528475532ab728318bde Mon Sep 17 00:00:00 2001 From: Peter Bex Date: Sun, 19 Mar 2023 10:06:45 +0100 Subject: 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. --- .envrc | 11 +++++++++++ .gitignore | 2 ++ README | 22 +++++++++++++++++++++ nix/default.nix | 15 +++++++++++++++ nix/pkgs.nix | 31 ++++++++++++++++++++++++++++++ postgres/postgresql.conf | 11 +++++++++++ ppq.sh | 50 ++++++++++++++++++++++++++++++++++++++++++++++++ shell.nix | 1 + 8 files changed, 143 insertions(+) create mode 100644 .envrc create mode 100644 .gitignore create mode 100644 README create mode 100644 nix/default.nix create mode 100644 nix/pkgs.nix create mode 100644 postgres/postgresql.conf create mode 100755 ppq.sh create mode 100644 shell.nix 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 -- cgit v1.2.3