Adding upstream version 248.upstream/248upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

11 files changed, 245 insertions, 0 deletions

diff --git a/systemd/README.systemd b/systemd/README.systemd
new file mode 100644
index 0000000..c818ad3
--- /dev/null
+++ b/systemd/README.systemd
@@ -0,0 +1,55 @@
+systemd unit files for PostgreSQL on Debian/Ubuntu
+--------------------------------------------------
+
+Each cluster is run as a separate service, called postgresql@version-cluster.
+pg_ctlcluster is invoked with --skip-systemctl-redirect. Logging still goes to
+/var/log/postgresql.
+
+There is a parent service called postgresql.service, that starts/stops/restarts/
+reloads all individual services that are configured as "auto" in
+/etc/postgresql/*/*/start.conf.
+
+The link between start.conf and postgresql.service is established by
+postgresql-generator, which creates symlinks in
+/run/systemd/generator/postgresql.service.wants/.
+
+Backup
+------
+
+Two backup mechanisms are being offered as systemd services: basebackups
+capable of point in time recovery (PITR, the preferred method), and SQL-level
+dumps.
+
+pg_basebackup@version-cluster.service
+pg_basebackup@version-cluster.timer
+
+  Weekly basebackup in /var/backups/postgresql/version/cluster.
+  By default, 3 copies are being kept.
+
+  To enable, run
+  systemctl enable --now pg_basebackup@version-cluster.timer
+  systemctl start pg_basebackup@version-cluster.service
+
+pg_receivewal@version-cluster.service
+
+  WAL archival to be used with pg_basebackup@.service for PITR.
+
+  To enable, run
+  systemctl enable --now pg_basebackup@version-cluster.timer pg_receivewal@version-cluster.service
+  systemctl start pg_basebackup@version-cluster.service
+
+pg_dump@version-cluster.service
+pg_dump@version-cluster.timer
+
+  Weekly SQL dump in /var/backups/postgresql/version/cluster.
+  By default, 3 copies are being kept.
+
+  To enable, run
+  systemctl enable --now pg_dump@version-cluster.timer
+  systemctl start pg_dump@version-cluster.service
+
+The mechanisms provided are meant to be used with low to medium size databases.
+For larger databases, or databases with high write volume, we advise to use a
+full-size backup solution such as pgbackrest or barman.
+
+ -- Christoph Berg <myon@debian.org>  Mon, 08 Mar 2021 13:45:26 +0100
diff --git a/systemd/system-generators/postgresql-generator b/systemd/system-generators/postgresql-generator
new file mode 100755
index 0000000..12e8102
--- /dev/null
+++ b/systemd/system-generators/postgresql-generator
@@ -0,0 +1,38 @@
+#!/bin/sh
+
+# This systemd generator creates dependency symlinks that make all PostgreSQL
+# clusters with "auto" in their start.conf file be started/stopped/reloaded
+# when postgresql.service is started/stopped/reloaded.
+
+set -eu
+
+gendir="$1"
+wantdir="$1/postgresql.service.wants"
+bindir="/usr/lib/postgresql/"
+#redhat# bindir="/usr/pgsql-"
+pgservice="/lib/systemd/system/postgresql@.service"
+
+mkdir -p "$wantdir"
+
+for conf in /etc/postgresql/*/*/postgresql.conf; do
+	# abort loop if glob was not expanded (but accept dead symlinks)
+	if ! test -e "$conf" && ! test -L "$conf"; then continue; fi
+
+	dir="${conf%/*}"
+
+	# evaluate start.conf
+	if [ -e "$dir/start.conf" ]; then
+		start=$(sed 's/#.*$//; /^[[:space:]]*$/d; s/^\s*//; s/\s*$//' "$dir/start.conf")
+	else
+		start=auto
+	fi
+	[ "$start" = "auto" ] || continue
+
+	verdir="${dir%/*}"
+	version="${verdir##*/}"
+	test -x "$bindir$version/bin/postgres" || continue # package got removed
+	cluster="${dir##*/}"
+	ln -s "$pgservice" "$wantdir/postgresql@$version-$cluster.service"
+done
+
+exit 0
diff --git a/systemd/system/pg_basebackup@.service b/systemd/system/pg_basebackup@.service
new file mode 100644
index 0000000..011c11e
--- /dev/null
+++ b/systemd/system/pg_basebackup@.service
@@ -0,0 +1,14 @@
+[Unit]
+Description=Basebackup of PostgreSQL Cluster %i
+AssertPathExists=/etc/postgresql/%I/postgresql.conf
+Wants=postgresql@%i.service
+After=postgresql@%i.service
+RequiresMountsFor=/var/backups/postgresql
+
+[Service]
+Type=oneshot
+User=postgres
+Environment="KEEP=3"
+ExecStartPre=+/usr/bin/pg_backupcluster %i createdirectory
+ExecStart=/usr/bin/pg_backupcluster %i basebackup
+ExecStart=/usr/bin/pg_backupcluster %i expirebasebackups $KEEP
diff --git a/systemd/system/pg_basebackup@.timer b/systemd/system/pg_basebackup@.timer
new file mode 100644
index 0000000..da0bb3f
--- /dev/null
+++ b/systemd/system/pg_basebackup@.timer
@@ -0,0 +1,12 @@
+[Unit]
+Description=Weekly Basebackup of PostgreSQL Cluster %i
+AssertPathExists=/etc/postgresql/%I/postgresql.conf
+
+[Timer]
+OnCalendar=weekly
+RandomizedDelaySec=1h
+FixedRandomDelay=true
+
+[Install]
+# when enabled, start along with postgresql@%i
+WantedBy=postgresql@%i.service
diff --git a/systemd/system/pg_compresswal@.service b/systemd/system/pg_compresswal@.service
new file mode 100644
index 0000000..e5eae6b
--- /dev/null
+++ b/systemd/system/pg_compresswal@.service
@@ -0,0 +1,9 @@
+[Unit]
+Description=Compress WAL of PostgreSQL Cluster %i
+AssertPathExists=/etc/postgresql/%I/postgresql.conf
+RequiresMountsFor=/var/backups/postgresql
+
+[Service]
+Type=oneshot
+User=postgres
+ExecStart=/usr/bin/pg_backupcluster %i compresswal
diff --git a/systemd/system/pg_compresswal@.timer b/systemd/system/pg_compresswal@.timer
new file mode 100644
index 0000000..6dddbb9
--- /dev/null
+++ b/systemd/system/pg_compresswal@.timer
@@ -0,0 +1,12 @@
+[Unit]
+Description=Daily Compress WAL of PostgreSQL Cluster %i
+AssertPathExists=/etc/postgresql/%I/postgresql.conf
+
+[Timer]
+OnCalendar=daily
+RandomizedDelaySec=1h
+FixedRandomDelay=true
+
+[Install]
+# when enabled, start along with pg_receivewal@%i
+WantedBy=pg_receivewal@%i.service
diff --git a/systemd/system/pg_dump@.service b/systemd/system/pg_dump@.service
new file mode 100644
index 0000000..a7f7f3d
--- /dev/null
+++ b/systemd/system/pg_dump@.service
@@ -0,0 +1,14 @@
+[Unit]
+Description=Dump of PostgreSQL Cluster %i
+AssertPathExists=/etc/postgresql/%I/postgresql.conf
+Wants=postgresql@%i.service
+After=postgresql@%i.service
+RequiresMountsFor=/var/backups/postgresql
+
+[Service]
+Type=oneshot
+User=postgres
+Environment="KEEP=3"
+ExecStartPre=+/usr/bin/pg_backupcluster %i createdirectory
+ExecStart=/usr/bin/pg_backupcluster %i dump
+ExecStart=/usr/bin/pg_backupcluster %i expiredumps $KEEP
diff --git a/systemd/system/pg_dump@.timer b/systemd/system/pg_dump@.timer
new file mode 100644
index 0000000..a1d2799
--- /dev/null
+++ b/systemd/system/pg_dump@.timer
@@ -0,0 +1,12 @@
+[Unit]
+Description=Weekly Dump of PostgreSQL Cluster %i
+AssertPathExists=/etc/postgresql/%I/postgresql.conf
+
+[Timer]
+OnCalendar=weekly
+RandomizedDelaySec=1h
+FixedRandomDelay=true
+
+[Install]
+# when enabled, start along with postgresql@%i
+WantedBy=postgresql@%i.service
diff --git a/systemd/system/pg_receivewal@.service b/systemd/system/pg_receivewal@.service
new file mode 100644
index 0000000..a15b432
--- /dev/null
+++ b/systemd/system/pg_receivewal@.service
@@ -0,0 +1,21 @@
+[Unit]
+Description=WAL archival of PostgreSQL Cluster %i
+AssertPathExists=/etc/postgresql/%I/postgresql.conf
+Wants=postgresql@%i.service
+After=postgresql@%i.service
+RequiresMountsFor=/var/backups/postgresql
+
+[Service]
+Type=simple
+User=postgres
+ExecStartPre=+/usr/bin/pg_backupcluster %i createdirectory
+ExecStart=/usr/bin/pg_backupcluster %i receivewal
+Restart=on-failure
+RestartSec=1min
+# pg_receivewal only flushes compressed output on SIGINT
+# (https://www.postgresql.org/message-id/flat/Yvo/5No5S0c4EFMj%40msg.df7cb.de)
+KillSignal=SIGINT
+
+[Install]
+# when enabled, start along with postgresql@%i
+WantedBy=postgresql@%i.service
diff --git a/systemd/system/postgresql.service b/systemd/system/postgresql.service
new file mode 100644
index 0000000..f53834e
--- /dev/null
+++ b/systemd/system/postgresql.service
@@ -0,0 +1,18 @@
+# postgresql.service is the meta unit for managing all PostgreSQL clusters on
+# the system at once. Conceptually, this unit is more like a systemd target,
+# but we are using a service since targets cannot be reloaded.
+#
+# The unit actually managing PostgreSQL clusters is postgresql@.service,
+# instantiated as postgresql@15-main.service for individual clusters.
+
+[Unit]
+Description=PostgreSQL RDBMS
+
+[Service]
+Type=oneshot
+ExecStart=/bin/true
+ExecReload=/bin/true
+RemainAfterExit=on
+
+[Install]
+WantedBy=multi-user.target
diff --git a/systemd/system/postgresql@.service b/systemd/system/postgresql@.service
new file mode 100644
index 0000000..8eed65c
--- /dev/null
+++ b/systemd/system/postgresql@.service
@@ -0,0 +1,40 @@
+# systemd service template for PostgreSQL clusters. The actual instances will
+# be called "postgresql@version-cluster", e.g. "postgresql@9.3-main". The
+# variable %i expands to "version-cluster", %I expands to "version/cluster".
+# (%I breaks for cluster names containing dashes.)
+
+[Unit]
+Description=PostgreSQL Cluster %i
+AssertPathExists=/etc/postgresql/%I/postgresql.conf
+RequiresMountsFor=/etc/postgresql/%I /var/lib/postgresql/%I
+PartOf=postgresql.service
+ReloadPropagatedFrom=postgresql.service
+Before=postgresql.service
+# stop server before networking goes down on shutdown
+After=network.target
+
+[Service]
+Type=forking
+# -: ignore startup failure (recovery might take arbitrarily long)
+# the actual pg_ctl timeout is configured in pg_ctl.conf
+ExecStart=-/usr/bin/pg_ctlcluster --skip-systemctl-redirect %i start
+# 0 is the same as infinity, but "infinity" needs systemd 229
+TimeoutStartSec=0
+ExecStop=/usr/bin/pg_ctlcluster --skip-systemctl-redirect -m fast %i stop
+TimeoutStopSec=1h
+ExecReload=/usr/bin/pg_ctlcluster --skip-systemctl-redirect %i reload
+PIDFile=/run/postgresql/%i.pid
+SyslogIdentifier=postgresql@%i
+# prevent OOM killer from choosing the postmaster (individual backends will
+# reset the score to 0)
+OOMScoreAdjust=-900
+# restarting automatically will prevent "pg_ctlcluster ... stop" from working,
+# so we disable it here. Also, the postmaster will restart by itself on most
+# problems anyway, so it is questionable if one wants to enable external
+# automatic restarts.
+#Restart=on-failure
+# (This should make pg_ctlcluster stop work, but doesn't:)
+#RestartPreventExitStatus=SIGINT SIGTERM
+
+[Install]
+WantedBy=multi-user.target