The previous patch had a small error in it ("psql" in one instance where i'd otherwise settled on "postgres"). I'm attaching a revised patch that doesn't have that problem.
--dkg
>From d88b1d641066aa9299beba5e84c47d22ded5337e Mon Sep 17 00:00:00 2001 From: Daniel Kahn Gillmor <d...@fifthhorseman.net> Date: Tue, 27 Feb 2018 19:26:46 -0500 Subject: [PATCH] Implement easily-managed user clusters under systemd Some user services might want their own local postgresql cluster. This makes it easy for those services to spin up a dedicated cluster + db on demand. --- Makefile | 1 + debian/postgresql-common.install | 3 + debian/postgresql-common.links | 1 + debian/postgresql-common.manpages | 1 + pgcluster@.service | 8 +++ postgres-user-cluster | 59 ++++++++++++++++++ psql-user-cluster | 5 ++ psql-user-cluster.pod | 128 ++++++++++++++++++++++++++++++++++++++ 8 files changed, 206 insertions(+) create mode 100644 pgcluster@.service create mode 100755 postgres-user-cluster create mode 100755 psql-user-cluster create mode 100644 psql-user-cluster.pod diff --git a/Makefile b/Makefile index 6ee9adf..402c2ed 100644 --- a/Makefile +++ b/Makefile @@ -9,6 +9,7 @@ POD1PROGS = pg_conftool.1 \ pg_wrapper.1 POD1PROGS_POD = pg_buildext.1 \ pg_virtualenv.1 \ + psql-user-cluster.1 \ dh_make_pgxs/dh_make_pgxs.1 POD8PROGS = pg_updatedicts.8 diff --git a/debian/postgresql-common.install b/debian/postgresql-common.install index ebd0c50..caacc79 100644 --- a/debian/postgresql-common.install +++ b/debian/postgresql-common.install @@ -12,7 +12,10 @@ pg_renamecluster usr/bin pg_updatedicts usr/sbin pg_upgradecluster usr/bin pg_virtualenv usr/bin +pgcluster@.service usr/lib/systemd/user pgdg usr/share/postgresql-common +postgres-user-cluster usr/share/postgresql-common +psql-user-cluster usr/bin run-upgrade-scripts usr/share/postgresql-common t/* usr/share/postgresql-common/t testsuite usr/share/postgresql-common diff --git a/debian/postgresql-common.links b/debian/postgresql-common.links index 2bf1bda..41d67c8 100644 --- a/debian/postgresql-common.links +++ b/debian/postgresql-common.links @@ -1 +1,2 @@ usr/share/postgresql-common/pg_wrapper usr/bin/pg_archivecleanup +usr/share/man/man1/psql-user-cluster.1 usr/share/man/man1/pgcluster@.service.1 diff --git a/debian/postgresql-common.manpages b/debian/postgresql-common.manpages index 4b21c26..1778484 100644 --- a/debian/postgresql-common.manpages +++ b/debian/postgresql-common.manpages @@ -7,3 +7,4 @@ pg_renamecluster.1 pg_updatedicts.8 pg_upgradecluster.1 pg_virtualenv.1 +psql-user-cluster.1 diff --git a/pgcluster@.service b/pgcluster@.service new file mode 100644 index 0000000..a8f5a3f --- /dev/null +++ b/pgcluster@.service @@ -0,0 +1,8 @@ +[Unit] +Description=User PostgreSQL cluster %I +Documentation=man::postgres(1) +Documentation=man::postgres-user-cluster(1) + +[Service] +Type=notify +ExecStart=/usr/share/postgresql-common/postgres-user-cluster %I %S/%I %t/%I diff --git a/postgres-user-cluster b/postgres-user-cluster new file mode 100755 index 0000000..1e973d6 --- /dev/null +++ b/postgres-user-cluster @@ -0,0 +1,59 @@ +#!/bin/bash + +# Author: Daniel Kahn Gillmor <d...@fifthhorseman.net> +# Date: 2018-02-27 + +# /usr/share/postgresql-common/postgres-user-cluster DBNAME DBPATH SOCKETDIR + +# Launch a private postgresql cluster (setting it up first if +# necessary), which listens solely on a unix-domain socket. It runs +# in the foreground, and is capable of notifying a user service +# manager (e.g. systemd --user) that it is ready for connections. + +# most typically controlled as a user service. See the +# pgcluster@.service(1) manpage. + +dbname="$1" +mkdir -p "$(dirname "$2")" 2>&1 +dir=$(realpath "$2") +socketdir="$3" +shift 3 + +set -e + +latest_pgversion=$(ls /usr/lib/postgresql | sort -n | tail -n1) + +if [ -d "$dir" ]; then + if ! [ -r "$dir/postgres/PG_VERSION" ]; then + printf >&2 "%s does not appear to be a postgresql database\n" "$dir/postgres " + exit 1 + fi +else + "/usr/lib/postgresql/$latest_pgversion/bin/initdb" -D "$dir/postgres" --auth=peer + cat >> "$dir/postgres/postgresql.conf" <<EOF +# no AF_INET or AF_INET6 listeners: +listen_addresses = '' + +# logging is going to be handled by journald; we do not need the date +# (interestingly, journald appears to be confused about the PID, so we +# leave that in): +log_line_prefix = '[%p] %q%u%d' +EOF + # FIXME: can we detect whether this CREATE DATABASE failed or not? Currently this + # can fail without returning an error code (it will log the error, though). + echo create database "$(basename "$dir")" | NOTIFY_SOCKET= "/usr/lib/postgresql/$latest_pgversion/bin/postgres" --single -D "$dir/postgres" postgres +fi +pgversion=$(cat "$dir/postgres/PG_VERSION") + +if [ ! -x "/usr/lib/postgresql/$pgversion/bin/postgres" ]; then + printf >&2 "version %s of postgresql is not installed\n" "$pgversion" + exit 1 +fi + +if [ "$pgversion" != "$latest_pgversion" ]; then + printf >&2 "Database at '%s' is version %s, but version %s is available.\nConsider upgrading!" "$dir/postgres" "$pgversion" "$latest_pgversion" + # FIXME: automate the upgrade for the user? +fi + +mkdir -p "$socketdir" +exec "/usr/lib/postgresql/$pgversion/bin/postgres" -D "$dir/postgres" -c "unix_socket_directories=$socketdir" "$@" diff --git a/psql-user-cluster b/psql-user-cluster new file mode 100755 index 0000000..8df44c8 --- /dev/null +++ b/psql-user-cluster @@ -0,0 +1,5 @@ +#!/bin/bash + +dbname="${@: -1}" +export PGHOST=${XDG_RUNTIME_DIR:-/run/user/"$(id -u)"}/"$dbname" +exec psql "$@" diff --git a/psql-user-cluster.pod b/psql-user-cluster.pod new file mode 100644 index 0000000..4c4d5b8 --- /dev/null +++ b/psql-user-cluster.pod @@ -0,0 +1,128 @@ +=head1 NAME + +pgcluster@.service, psql-user-cluster - PostgreSQL Clusters managed as systemd user services + +=head1 SYNOPSIS + +Ensure that a user-controlled cluster and database exist for another +user service: + + # /usr/lib/systemd/user/example.service + [Unit] + Requires=pgcluster@example.service + After=pgcluster@example.service + ... + +Or, set up and launch a cluster directly from the command-line: + +B<systemctl> B<--user> B<start> B<pgcluster@>I<example>B<.service> + +Connect to such a cluster: + +B<psql-user-cluster> I<example> + +=head1 DESCRIPTION + +Some user accounts might run a daemon (a "user service") that wants +its own postgresql cluster without relying on the system cluster. + +For example, there are daemons that handle sensitive data that they +don't want another system account to access, or that they want to be +able to tightly control the backup schedule for. + +Alternately, the system cluster might be configured and in use for a +different purpose; so the user service might not be able to easily get +access to it. + +For these one-off user-controlled clusters, we can also generally +assume that there will be a single database running. By default, we +can name the databsae the same name as the cluster. + +This framework makes it easier to support these use cases on Debian +systems that run systemd. + +If the local user account wants to start up a cluster named I<foo>, +they just do: + + $ systemctl --user start pgcluster@foo.service + +Then, to access its default I<foo> database from a shell, they can do: + + $ psql-user-cluster foo + +or: + + $ PGHOST=$XDG_RUNTIME_DIR/foo psql foo + +Alternately, consider a user .service named I<exampled> which keeps +its state in B<~/.config/example>. An B<exampled.service> unit might +be configured with: + + # /usr/lib/systemd/user/example.service + [Unit] + Requires=pgcluster@example.service + After=pgcluster@example.service + ... + +So that if the user service is ever started, the cluster will be +created on demand and the I<example> database in that cluster will be +ready for use before `exampled.service` is started. + +=head1 FILES + +B<pgcluster@.service> uses the XDG defaults to figure out where to +place the database server's listening socket (B<XDG_RUNTIME_DIR>) and +database files (B<XDG_CONFIG_HOME>). + +So by default for user 1000, B<pgcluster@foo.service> would open a +listening socket in I</run/user/1000/foo/.s.PSQL.5432>, and would put +its database files in I<~/.config/foo/postgres>. + +Why have an additional B</postgres/> in the path for the database +files? Because a service that needs its own postgresql backend may +have additional configuration or state that it wants to store. It's +cleaner to give the postgresql database a directory to itself. + +=head1 BUGS + +=over 2 + +=item + +B<psql-user-cluster> always assumes the last argument is the +database name. To send a database username, use B<-U> explicitly +(before the database name). + +=item + +This framework assumes that postgresql's multi-version setup is +installed the way it is commonly installed in Debian. If that +changes, it will probably break. + +=item + +B</usr/share/postgresql-common/postgres-user-service> does not upgrade +the user's cluster automatically across postgresql version upgrades. +The user needs to do that manually. + +=item + +If the initial database creation fails, it will be noted in the +journald logs for the service, but it will not cause an explicit error +state. + +=item + +journalctl is apparently confused about which process is emitting +errors or warnings, so we ask postgres to self-report process ID +information in its logs. + +=back + +=head1 SEE ALSO + +postgres(1), initdb(1) + +=head1 AUTHOR + +Daniel Kahn Gillmor L<E<lt>d...@fifthhorseman.nete<gt>> -- 2.16.1