[Pkg-postgresql-public] Bug#891687: postgresql-common: dsfwew

[Daniel Kahn Gillmor]

Hi Christoph--

On Fri 2018-03-02 14:00:52 +0100, Christoph Berg wrote:
> this is very interesting, especially Type=notify.

thanks for taking a look!

> my first question would be if we could integrate the idea more tightly
> with the existing postgresql@ services which are already capable of
> launching clusters for different users (though currently hardcoded to
> /etc/postgresql/*). The cluster machinery itself (pg_ctlcluster and
> friends) can operate on different roots if PG_CLUSTER_CONF_ROOT is
> set. Maybe that can be incorporated to work better together.

i confess i don't understand the existing cluster machinery in debian as
well as i understand the upstream binaries -- so my changes were focused
specifically on setting up a simple per-user arrangement which:

 a) doesn't require any superuser interaction, and

 b) doesn't require us to think about any upgrade paths from previous
versions ;)

> One thing I have been thinking about was to replace pg_ctlcluster with
> something more systemd-specific, including Type=notify operation. This
> looks about what you have here.

yep, Type=notify Just Works™ afaict with the currently-shipped postgres.
it'd be great to have that used for the system stuff as well, but i
haven't thought through the upgrade paths clearly yet.

one additional fix to make things even nicer would be to make the
cluster daemon fully socket-activated, but that's likely to be work for
upstream, so we don't need to discuss it here.

anyway, let me know if there are things i can do that would make you
feel more comfortable adopting this user-specific cluster management
idea.  users can already do it, but it'd be nice to smooth out bumps in
the road for them a little bit with a sensibly-maintained minimal
interface for the simple use case.

--dkg

___
Pkg-postgresql-public mailing list
Pkg-postgresql-public@lists.alioth.debian.org
http://lists.alioth.debian.org/cgi-bin/mailman/listinfo/pkg-postgresql-public

[Pkg-postgresql-public] Bug#891687: postgresql-common: dsfwew

[Christoph Berg]

Re: Daniel Kahn Gillmor 2018-02-28 
<151977883437.31585.3826803256327598913.report...@alice.fifthhorseman.net>
> In particular, a user .service named "exampled" which keeps its
> state in `~/.config/example`.  An `exampled.service` unit might be
> configured with:
> 
> Requires=pgcluster@example.service
> After=pgcluster@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.

Hi Daniel,

this is very interesting, especially Type=notify. I haven't yet had
time to look closely at the patches - my first question would be if we
could integrate the idea more tightly with the existing postgresql@
services which are already capable of launching clusters for different
users (though currently hardcoded to /etc/postgresql/*). The cluster
machinery itself (pg_ctlcluster and friends) can operate on different
roots if PG_CLUSTER_CONF_ROOT is set. Maybe that can be incorporated
to work better together.

One thing I have been thinking about was to replace pg_ctlcluster with
something more systemd-specific, including Type=notify operation. This
looks about what you have here.

Christoph

___
Pkg-postgresql-public mailing list
Pkg-postgresql-public@lists.alioth.debian.org
http://lists.alioth.debian.org/cgi-bin/mailman/listinfo/pkg-postgresql-public

[Pkg-postgresql-public] Bug#891687: postgresql-common: dsfwew

[Daniel Kahn Gillmor]

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@example.service
After=pgcluster@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  procps2: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
>From 6db58c09704dc61310e4dda9607afcfc9f1dab65 Mon Sep 17 00:00:00 2001
From: Daniel Kahn Gillmor 
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 000..a8f5a3f
--- /dev/null
+++ b/pgcluster@.service