From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from mp11.migadu.com ([2001:41d0:2:bcc0::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by ms5.migadu.com with LMTPS id iBZmI+DurGLLTAAAbAwnHQ (envelope-from ) for ; Fri, 17 Jun 2022 23:15:12 +0200 Received: from aspmx1.migadu.com ([2001:41d0:2:bcc0::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by mp11.migadu.com with LMTPS id UBRaI+DurGLyyQAA9RJhRA (envelope-from ) for ; Fri, 17 Jun 2022 23:15:12 +0200 Received: from lists.gnu.org (lists.gnu.org [209.51.188.17]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by aspmx1.migadu.com (Postfix) with ESMTPS id E6CA139A6D for ; Fri, 17 Jun 2022 23:15:11 +0200 (CEST) Received: from localhost ([::1]:57222 helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1o2JJ8-0000c7-KA for larch@yhetil.org; Fri, 17 Jun 2022 17:15:10 -0400 Received: from eggs.gnu.org ([2001:470:142:3::10]:39064) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1o2JJ0-0000bt-Uv for guix-patches@gnu.org; Fri, 17 Jun 2022 17:15:02 -0400 Received: from debbugs.gnu.org ([209.51.188.43]:53214) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.90_1) (envelope-from ) id 1o2JJ0-0000X8-Lt for guix-patches@gnu.org; Fri, 17 Jun 2022 17:15:02 -0400 Received: from Debian-debbugs by debbugs.gnu.org with local (Exim 4.84_2) (envelope-from ) id 1o2JJ0-0001Jv-IG for guix-patches@gnu.org; Fri, 17 Jun 2022 17:15:02 -0400 X-Loop: help-debbugs@gnu.org Subject: [bug#56045] [PATCH] Back up and restore PostgreSQL databases with Shepherd Resent-From: Marius Bakke Original-Sender: "Debbugs-submit" Resent-CC: guix-patches@gnu.org Resent-Date: Fri, 17 Jun 2022 21:15:02 +0000 Resent-Message-ID: Resent-Sender: help-debbugs@gnu.org X-GNU-PR-Message: report 56045 X-GNU-PR-Package: guix-patches X-GNU-PR-Keywords: patch To: 56045@debbugs.gnu.org X-Debbugs-Original-To: guix-patches@gnu.org Received: via spool by submit@debbugs.gnu.org id=B.16555004695012 (code B ref -1); Fri, 17 Jun 2022 21:15:02 +0000 Received: (at submit) by debbugs.gnu.org; 17 Jun 2022 21:14:29 +0000 Received: from localhost ([127.0.0.1]:47111 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1o2JIO-0001Ig-Sv for submit@debbugs.gnu.org; Fri, 17 Jun 2022 17:14:29 -0400 Received: from lists.gnu.org ([209.51.188.17]:35798) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1o2JIL-0001IW-5n for submit@debbugs.gnu.org; Fri, 17 Jun 2022 17:14:23 -0400 Received: from eggs.gnu.org ([2001:470:142:3::10]:38888) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1o2JIL-000071-0X for guix-patches@gnu.org; Fri, 17 Jun 2022 17:14:21 -0400 Received: from fencepost.gnu.org ([2001:470:142:3::e]:45324) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1o2JIK-0000JG-Og for guix-patches@gnu.org; Fri, 17 Jun 2022 17:14:20 -0400 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=gnu.org; s=fencepost-gnu-org; h=MIME-Version:Date:Subject:To:From:in-reply-to: references; bh=8x+ls4giJ0GMNhtyAPdvDiRvtzVUpGZn0iGcv9vPvh0=; b=CyQJens+16wgGv PdGErYGtDWR+uQeeijqDOR10l0J95YbCyyUuc3SicjmUdKBohTQWtYnyCNapXCpEUqrNT8DneBz4c LkKkRpaPGe3v8DKFgCripfimz3AIUvnaF4YaNhxdMdHETR3ZHU+BSN6PHhkJUZXTTZE/gOz+LHgCW gJN6roLrBbuVAUEyEsA+9n2OFgize0/ULCP0s5HCgHEZAQmzQySXltTzwe56ChwImvxTdk2s0H5OC fWw+EJpH18pHYkMh+bs3P38eJTAB6PatQ9B7mwSRWh0Oox8AtjTBJ6xAsSM7BQmZSiSKECSLAx7tW TjM18IL+SQ99uKUzZ4fw==; Received: from [2001:4652:9717:0:52eb:71ff:fe49:3a13] (port=54830 helo=localhost) by fencepost.gnu.org with esmtpsa (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1o2JIK-0007a8-87 for guix-patches@gnu.org; Fri, 17 Jun 2022 17:14:20 -0400 From: Marius Bakke Date: Fri, 17 Jun 2022 23:14:03 +0200 Message-ID: <87zgibuh5w.fsf@gnu.org> MIME-Version: 1.0 Content-Type: multipart/signed; boundary="==-=-="; micalg=pgp-sha512; protocol="application/pgp-signature" X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list X-BeenThere: guix-patches@gnu.org List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: guix-patches-bounces+larch=yhetil.org@gnu.org Sender: "Guix-patches" X-Migadu-Flow: FLOW_IN X-Migadu-To: larch@yhetil.org X-Migadu-Country: US ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=yhetil.org; s=key1; t=1655500512; h=from:from:sender:sender:reply-to:subject:subject:date:date: message-id:message-id:to:to:cc:mime-version:mime-version: content-type:content-type:resent-cc:resent-from:resent-sender: resent-message-id:list-id:list-help:list-unsubscribe:list-subscribe: list-post:dkim-signature; bh=8x+ls4giJ0GMNhtyAPdvDiRvtzVUpGZn0iGcv9vPvh0=; b=tJcku9uEt87QqExzDtkgUesb+dwOboG5qY2OAOcKb9hjSGsfkEFsBDgPAIGHp/SRLrwCIf 3/Le/SqFd+jb2GCN8QOrKo5faWvRV+RLpdjH7m/Fx/KOUp+zbS2G294vogfaNFXOye+7OR LiDBLKNhKDTs/4zqs11E3I8HAws16YodsatxXCx/+NToN5buWyZkhesR42s1t4Kp1ioRag aPeoyjwXGlUEXvEWG69k6XmsBqhG0y9IAF8vnl57KQ3L2jcoRL5QUom6DnN20h364MSTZZ IGicwsi3ddrueAOp8knRPML/C+PxTgnf3zijzMXdb+otXWy6R3KsVvzvTWDfIg== ARC-Seal: i=1; s=key1; d=yhetil.org; t=1655500512; a=rsa-sha256; cv=none; b=qgEGAq53UQoASF5mdsnglBXcCxS6kDA1Jo7cKauLz1fXqzrb+7HkcPq42gaK7q83O0502p FBKXXQkAtQIuX4RilZ/WfLYF/nd5/L5K9FfRlbIr6GblXvsAGaFA/K6rKjTj0AB9UnFpL+ uSRo9hXR8u74liyEIs+C8XVnoLcVIzUGsEeqUGmim+QAaxeDuMv/ahoMTXSg9mNtoNpzsX P5xgj1tN4oDeZVfTPzjoKeH3mna9CcDT1xJ1DDsnqHPhs7z9skJi2hH9QNI1YQTLnRnoJs 9cojt86ntF9OeAYmshhI/pMEPiSheMSqjN+zg/csZKAPtmUQq1H9MxWq6HXIcA== ARC-Authentication-Results: i=1; aspmx1.migadu.com; dkim=fail ("headers rsa verify failed") header.d=gnu.org header.s=fencepost-gnu-org header.b=CyQJens+; dmarc=pass (policy=none) header.from=gnu.org; spf=pass (aspmx1.migadu.com: domain of "guix-patches-bounces+larch=yhetil.org@gnu.org" designates 209.51.188.17 as permitted sender) smtp.mailfrom="guix-patches-bounces+larch=yhetil.org@gnu.org" X-Migadu-Spam-Score: -7.99 Authentication-Results: aspmx1.migadu.com; dkim=fail ("headers rsa verify failed") header.d=gnu.org header.s=fencepost-gnu-org header.b=CyQJens+; dmarc=pass (policy=none) header.from=gnu.org; spf=pass (aspmx1.migadu.com: domain of "guix-patches-bounces+larch=yhetil.org@gnu.org" designates 209.51.188.17 as permitted sender) smtp.mailfrom="guix-patches-bounces+larch=yhetil.org@gnu.org" X-Migadu-Queue-Id: E6CA139A6D X-Spam-Score: -7.99 X-Migadu-Scanner: scn0.migadu.com X-TUID: xj/guJZWEeY4 --==-=-= Content-Type: multipart/mixed; boundary="=-=-=" --=-=-= Content-Type: text/plain Hello Guix! The attached patch adds backup and restore mechanisms to the PostgreSQL Shepherd service. It looks like this (here with a db named 'mreg'): $ sudo herd backup postgres mreg $ sudo -u postgres psql -c 'drop database mreg' # whoops ... DROP DATABASE $ sudo herd list-backups postgres mreg mreg@2022-06-16_21-55-07 mreg@2022-06-16_22-48-59 $ sudo herd restore postgres mreg@2022-06-16_22-48-59 $ sudo -u postgres psql mreg mreg=# Pretty cool, no? :-) The restore command is "smart": if the database already exists, it restores in a single transaction; otherwise, it will be created from scratch (these scenarios require mutually exclusive options to 'pg_restore'). With this patch you can 'herd backup' each database, stop postgres, _delete_ /var/lib/postgresql/data, reconfigure with a newer version, and 'herd restore' them again -- but you'll lose any role passwords (and roles not declared by postgresql-role-service-type). Not sure what to about roles, maybe a backup-roles command? There is no Scheme API yet, but it would be nice to define per-database settings (i.e. --jobs or --format) in the configuration. And also a scheduled backup service. These tasks are up for grabs. :-) The quest here is to provide a smooth upgrade path for end users (and eventually bump the old 'postgresql-10' service default). Feedback and/or testing welcome! --=-=-= Content-Type: text/x-patch; charset=utf-8 Content-Disposition: attachment; filename=0001-services-Shepherd-can-backup-and-restore-PostgreSQL-.patch Content-Transfer-Encoding: quoted-printable From=20edc8a2e5ae3c89b78fb837d4351f0ddfab8fe474 Mon Sep 17 00:00:00 2001 From: Marius Bakke Date: Thu, 16 Jun 2022 22:46:01 +0200 Subject: [PATCH] services: Shepherd can backup and restore PostgreSQL databases. * gnu/services/databases.scm ()[backup-directory]: New field. (postgresql-activation): Create it. (postgresql-backup-action, postgresql-list-backups-action, postgresql-restore-action): New variables. (postgresql-shepherd-service)[actions]: Register them. * gnu/tests/databases.scm (%postgresql-backup-directory): New variable. (run-postgresql-test): Trim unused module imports from existing tests. Add "insert test data", "backup database", "list backups", "drop database", "restore database", "update test data", "restore again", and "verify restor= e" tests. =2D-- gnu/services/databases.scm | 169 ++++++++++++++++++++++++++++++++++++- gnu/tests/databases.scm | 117 ++++++++++++++++++++++++- 2 files changed, 278 insertions(+), 8 deletions(-) diff --git a/gnu/services/databases.scm b/gnu/services/databases.scm index fb3cd3c478..e3e8cc724e 100644 =2D-- a/gnu/services/databases.scm +++ b/gnu/services/databases.scm @@ -6,7 +6,7 @@ ;;; Copyright =C2=A9 2018 Cl=C3=A9ment Lassieur ;;; Copyright =C2=A9 2018 Julien Lepiller ;;; Copyright =C2=A9 2019 Robert Vollmert =2D;;; Copyright =C2=A9 2020 Marius Bakke +;;; Copyright =C2=A9 2020, 2022 Marius Bakke ;;; Copyright =C2=A9 2021 David Larsson ;;; ;;; This file is part of GNU Guix. @@ -176,6 +176,8 @@ (define-record-type* (default "/var/log/postgresql")) (data-directory postgresql-configuration-data-directory (default "/var/lib/postgresql/data")) + (backup-directory postgresql-configuration-backup-directory + (default "/var/lib/postgresql/backup")) (extension-packages postgresql-configuration-extension-packages (default '()))) =20 @@ -213,7 +215,7 @@ (define (final-postgresql postgresql extension-packages) (define postgresql-activation (match-lambda (($ postgresql port locale config-file =2D log-directory data-directory + log-directory data-directory backup-dir= ectory extension-packages) #~(begin (use-modules (guix build utils) @@ -245,6 +247,11 @@ (define postgresql-activation (mkdir-p #$log-directory) (chown #$log-directory (passwd:uid user) (passwd:gid user))) =20 + ;; Create the backup directory. + (when (string? #$backup-directory) + (mkdir-p #$backup-directory) + (chown #$backup-directory (passwd:uid user) (passwd:gid user)= )) + ;; Drop privileges and init state directory in a new ;; process. Wait for it to finish before proceeding. (match (primitive-fork) @@ -265,10 +272,155 @@ (define postgresql-activation (primitive-exit 1)))) (pid (waitpid pid)))))))) =20 +(define (postgresql-backup-action postgresql backup-directory) + (shepherd-action + (name 'backup) + (documentation + "Back up a database on the running PostgreSQL server.") + (procedure + #~(lambda* (pid #:optional database #:rest rest) + (use-modules (guix build utils) + (ice-9 match) + (srfi srfi-19)) + (if database + (let* ((user (getpwnam "postgres")) + (pg_dump #$(file-append postgresql "/bin/pg_dump")) + (options '("--create" "--clean" "--if-exists" + "--format=3Dd")) + (start-time (current-time)) + (date (time-utc->date start-time)) + (date-stamp (date->string date "~1_~H-~M-~S")) + (file-name (string-append #$backup-directory "/" + database "@" date-stamp))) + ;; Fork so we can drop privileges. + (match (primitive-fork) + (0 + ;; Exit with a non-zero status code if an exception is th= rown. + (dynamic-wind + (const #t) + (lambda () + (setgid (passwd:gid user)) + (setuid (passwd:uid user)) + (umask #o027) + (format (current-output-port) + "postgres: creating backup ~a.~%" + (basename file-name)) + (mkdir-p (dirname file-name)) + (let* ((result (apply system* pg_dump database + "-f" file-name + options)) + (exit-value (status:exit-val result))) + (if (=3D 0 exit-value) + (format (current-output-port) + "postgres: backup of ~a completed succe= ssfully.~%" + database) + (format (current-output-port) + "postgres: backup of ~a completed with = errors.~%" + database)) + (primitive-exit exit-value))) + (lambda () + (format (current-output-port) + "postgres: backup of ~a failed.~%") + (primitive-exit 1)))) + (pid (waitpid pid)))) + (begin + (format #t "usage: herd backup postgres DATABASE~%") + #f)))))) + +(define (postgresql-list-backups-action backup-directory) + (shepherd-action + (name 'list-backups) + (documentation + "List available PostgreSQL backups.") + (procedure + #~(lambda* (pid #:optional database #:rest rest) + (use-modules (ice-9 ftw) + (srfi srfi-26)) + (if (file-exists? #$backup-directory) + (for-each (cut format #t "~a~%" <>) + (scandir #$backup-directory + (if database + (cut string-prefix? database <>) + (negate (cut member <> '("." "..")))))) + #f))))) + +(define (postgresql-restore-action postgresql backup-directory) + (shepherd-action + (name 'restore) + (documentation + "Restore a PostgreSQL backup.") + (procedure + #~(lambda* (pid #:optional file #:rest rest) + (use-modules (ice-9 match) + (ice-9 popen) + (ice-9 rdelim)) + + ;; The pg_restore arguments varies slightly if the database is + ;; missing vs already present, hence this procedure. + (define (database-exists? db) + (let* ((psql #$(file-append postgresql "/bin/psql")) + (separator "%") + (port (open-input-pipe (string-append psql " -lqtA" + " -F " separator)))) + (let loop ((line (read-line port))) + (cond + ((eof-object? line) + (close-port port) + #f) + ((string-prefix? (string-append db separator) line) + (close-port port) + #t) + (else (loop (read-line port))))))) + + (let ((user (getpwnam "postgres")) + (pg_restore #$(file-append postgresql "/bin/pg_restore"))) + (if (and (string? file) + (file-exists? (string-append #$backup-directory "/" fil= e))) + (match (primitive-fork) + (0 + (dynamic-wind + (const #t) + (lambda () + (setgid (passwd:gid user)) + (setuid (passwd:uid user)) + (let* ((backup-file (string-append #$backup-directory + "/" file)) + (database (match (string-split file #\@) + ((name date) name))) + (create? (not (database-exists? database))) + (options (list "--clean" "--if-exists" + (if create? + "--create" + "--single-transaction")))) + (format (current-output-port) + "postgres: restoring ~a.~%" file) + (let* ((result (apply system* pg_restore backup-file + "-d" (if create? "postgres" d= atabase) + options)) + (exit-value (status:exit-val result))) + (if (=3D 0 exit-value) + (format (current-output-port) + "postgres: restore of ~a completed \ +successfully.~%" + database) + (format (current-output-port) + "postgres: restore of ~a completed \ +with errors.~%" + database)) + (primitive-exit exit-value)))) + (lambda () + (format #t "postgres: could not restore ~a.~%" file) + (primitive-exit 1)))) + (pid (waitpid pid))) + (begin + (format #t "usage: herd restore postgres BACKUP~%") + (format #t "hint: see 'herd list-backups postgres'~%") + #f))))))) + (define postgresql-shepherd-service (match-lambda (($ postgresql port locale config-file =2D log-directory data-directory + log-directory data-directory backup-dir= ectory extension-packages) (let* ((pg_ctl-wrapper ;; Wrapper script that switches to the 'postgres' user before @@ -309,8 +461,17 @@ (define postgresql-shepherd-service (provision '(postgres)) (documentation "Run the PostgreSQL daemon.") (requirement '(user-processes loopback syslogd)) =2D (modules `((ice-9 match) + (modules `((ice-9 ftw) + (ice-9 match) + (ice-9 popen) + (ice-9 rdelim) + (srfi srfi-19) + (srfi srfi-26) ,@%default-modules)) + (actions (list + (postgresql-backup-action postgresql backup-direct= ory) + (postgresql-list-backups-action backup-directory) + (postgresql-restore-action postgresql backup-direc= tory))) (start (action "start")) (stop (action "stop")))))))) =20 diff --git a/gnu/tests/databases.scm b/gnu/tests/databases.scm index 296d91d118..4210054d9e 100644 =2D-- a/gnu/tests/databases.scm +++ b/gnu/tests/databases.scm @@ -134,6 +134,9 @@ (define %test-memcached ;;; The PostgreSQL service. ;;; =20 +(define %postgresql-backup-directory + "/var/lib/postgresql/backup") + (define %postgresql-log-directory "/var/log/postgresql") =20 @@ -195,8 +198,6 @@ (define marionette (test-assert "log-file" (marionette-eval '(begin =2D (use-modules (ice-9 ftw) =2D (ice-9 match)) (current-output-port (open-file "/dev/console" "w0")) (let ((server-log-file @@ -227,8 +228,7 @@ (define marionette (test-assert "database creation" (marionette-eval '(begin =2D (use-modules (gnu services herd) =2D (ice-9 popen)) + (use-modules (ice-9 popen)) (current-output-port (open-file "/dev/console" "w0")) (let* ((port (open-pipe* @@ -241,6 +241,115 @@ (define marionette (string-contains output "1"))) marionette)) =20 + (test-eq "insert test data" + 0 + (marionette-eval + '(begin + (current-output-port + (open-file "/dev/console" "w0")) + (let ((result (system* + #$(file-append postgresql "/bin/psql") + "-tA" "-c" "CREATE TABLE test (name VARCHAR, + status VARCH= AR); +INSERT INTO TEST VALUES ('backup', 'pending');" + "root"))) + (status:exit-val result))) + marionette)) + + (test-assert "backup database" + (marionette-eval + '(with-shepherd-action 'postgres ('backup "root") + result + result) + marionette)) + + (test-assert "list backups" + (marionette-eval + '(with-shepherd-action 'postgres ('list-backups) + result + result) + marionette)) + + (test-eq "drop database" + 0 + (marionette-eval + '(begin + (current-output-port + (open-file "/dev/console" "w0")) + (let ((result (system* + #$(file-append postgresql "/bin/psql") + "-tA" "-c" "DROP DATABASE root" + "postgres"))) + (status:exit-val result))) + marionette)) + + (test-assert "restore database" + (let ((file-name (marionette-eval + '(begin + (use-modules (ice-9 ftw) + (srfi srfi-26)) + (car (scandir #$%postgresql-backup-direct= ory + (negate (cut member <> + '("." ".."))))= )) + marionette))) + (marionette-eval + `(with-shepherd-action 'postgres ('restore ,file-name) + result + result) + marionette))) + + (test-equal "update test data" + "completed" + (marionette-eval + '(begin + (use-modules (ice-9 popen)) + (current-output-port + (open-file "/dev/console" "w0")) + (let* ((port (open-pipe* + OPEN_READ + #$(file-append postgresql "/bin/psql") + "-tA" "-c" " +UPDATE test SET status=3D'completed' WHERE name=3D'backup'; +SELECT status FROM test WHERE name=3D'backup';" + "root")) + (output (get-string-all port))) + (close-pipe port) + (string-trim-right output))) + marionette)) + + (test-assert "restore again" + (let ((file-name (marionette-eval + '(begin + (use-modules (ice-9 ftw) + (srfi srfi-26)) + (car (scandir #$%postgresql-backup-direct= ory + (negate (cut member <> + '("." ".."))))= )) + marionette))) + (marionette-eval + `(with-shepherd-action 'postgres ('restore ,file-name) + result + result) + marionette))) + + (test-equal "verify restore" + "pending" + (marionette-eval + '(begin + (use-modules (ice-9 popen)) + (current-output-port + (open-file "/dev/console" "w0")) + (let* ((port (open-pipe* + OPEN_READ + #$(file-append postgresql "/bin/psql") + "-tA" "-c" " +SELECT status FROM test WHERE name=3D'backup'" + "root")) + (output (get-string-all port))) + (close-pipe port) + (string-trim-right output))) + marionette)) + (test-end)))) =20 (gexp->derivation "postgresql-test" test)) =2D-=20 2.36.1 --=-=-=-- --==-=-= Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iIUEARYKAC0WIQRNTknu3zbaMQ2ddzTocYulkRQQdwUCYqzumw8cbWFyaXVzQGdu dS5vcmcACgkQ6HGLpZEUEHdqGQD/RhKGfvcFZYr4Eo8ico0Y2xt18IUymNgfGq7U oJJsZJgBAM4/gLnUqpPq/NDoTSjmMAE50Ss6rbefVrtGXFX0DMkM =qMDU -----END PGP SIGNATURE----- --==-=-=--