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
