[Pkg-postgresql-public] Bug#891687: postgresql-common: dsfwew
Daniel Kahn Gillmor
dkg at fifthhorseman.net
Wed Feb 28 00:47:14 UTC 2018
Package: postgresql-common
Version: 190
Severity: wishlist
Tags: patch
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.
The attached patch makes it easier to support these use cases on
Debian systems that run systemd.
In particular, a user .service named "exampled" which keeps its
state in `~/.config/example`. An `exampled.service` unit might be
configured with:
Requires=pgcluster at example.service
After=pgcluster at example.service
So that if the user service is ever started, the cluster will be
created on demand (in `~/.config/example/data`) and the "example"
database will be ready for use (via unix-domain sockets in
`$XDG_RUNTIME_DIR/example/`) before the user service is started.
Regards,
--dkg
-- System Information:
Debian Release: buster/sid
APT prefers testing-debug
APT policy: (500, 'testing-debug'), (500, 'testing'), (500, 'oldstable'), (200, 'unstable-debug'), (200, 'unstable'), (1, 'experimental-debug'), (1, 'experimental')
Architecture: amd64 (x86_64)
Foreign Architectures: i386
Kernel: Linux 4.14.0-3-amd64 (SMP w/4 CPU cores)
Locale: LANG=en_US.UTF-8, LC_CTYPE=en_US.UTF-8 (charmap=UTF-8), LANGUAGE=en_US.UTF-8 (charmap=UTF-8)
Shell: /bin/sh linked to /bin/dash
Init: systemd (via /run/systemd/system)
Versions of packages postgresql-common depends on:
ii adduser 3.117
ii debconf [debconf-2.0] 1.5.65
ii lsb-base 9.20170808
ii postgresql-client-common 190
ii procps 2:3.3.12-4
ii ssl-cert 1.0.39
ii ucf 3.0037
Versions of packages postgresql-common recommends:
ii e2fsprogs 1.43.9-1
ii logrotate 3.11.0-0.1
Versions of packages postgresql-common suggests:
ii libjson-perl 2.97001-1
-- Configuration Files:
/etc/postgresql-common/createcluster.conf changed [not included]
-- debconf information excluded
-------------- next part --------------
>From 6db58c09704dc61310e4dda9607afcfc9f1dab65 Mon Sep 17 00:00:00 2001
From: Daniel Kahn Gillmor <dkg at 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 at .service | 8 +++
postgres-user-cluster | 59 ++++++++++++++++++
psql-user-cluster | 5 ++
psql-user-cluster.pod | 128 ++++++++++++++++++++++++++++++++++++++
8 files changed, 206 insertions(+)
create mode 100644 pgcluster at .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 at .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 at .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 at .service b/pgcluster at .service
new file mode 100644
index 0000000..a8f5a3f
--- /dev/null
+++ b/pgcluster at .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..f89666e
--- /dev/null
+++ b/postgres-user-cluster
@@ -0,0 +1,59 @@
+#!/bin/bash
+
+# Author: Daniel Kahn Gillmor <dkg at 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 at .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/psql/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 at .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 at example.service
+ After=pgcluster at 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 at 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 at example.service
+ After=pgcluster at 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 at .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 at 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>dkg at fifthhorseman.netE<gt>>
--
2.16.1
More information about the Pkg-postgresql-public
mailing list