From patchwork Fri Jun 17 21:14:03 2022 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-Patchwork-Submitter: Marius Bakke X-Patchwork-Id: 40099 Return-Path: X-Original-To: patchwork@mira.cbaines.net Delivered-To: patchwork@mira.cbaines.net Received: by mira.cbaines.net (Postfix, from userid 113) id D567727BBEA; Fri, 17 Jun 2022 22:15:11 +0100 (BST) X-Spam-Checker-Version: SpamAssassin 3.4.6 (2021-04-09) on mira.cbaines.net X-Spam-Level: X-Spam-Status: No, score=-3.7 required=5.0 tests=BAYES_00,DKIM_INVALID, DKIM_SIGNED,MAILING_LIST_MULTI,RCVD_IN_MSPIKE_H2,SPF_HELO_PASS, URIBL_BLOCKED autolearn=unavailable autolearn_force=no version=3.4.6 Received: from lists.gnu.org (lists.gnu.org [209.51.188.17]) by mira.cbaines.net (Postfix) with ESMTPS id B935C27BBE9 for ; Fri, 17 Jun 2022 22:15:10 +0100 (BST) Received: from localhost ([::1]:57222 helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1o2JJ7-0000c7-T3 for patchwork@mira.cbaines.net; Fri, 17 Jun 2022 17:15:09 -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 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+patchwork=mira.cbaines.net@gnu.org Sender: "Guix-patches" X-getmail-retrieved-from-mailbox: Patches 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! From edc8a2e5ae3c89b78fb837d4351f0ddfab8fe474 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 restore" tests. --- 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 --- a/gnu/services/databases.scm +++ b/gnu/services/databases.scm @@ -6,7 +6,7 @@ ;;; Copyright © 2018 Clément Lassieur ;;; Copyright © 2018 Julien Lepiller ;;; Copyright © 2019 Robert Vollmert -;;; Copyright © 2020 Marius Bakke +;;; Copyright © 2020, 2022 Marius Bakke ;;; Copyright © 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 '()))) @@ -213,7 +215,7 @@ (define (final-postgresql postgresql extension-packages) (define postgresql-activation (match-lambda (($ postgresql port locale config-file - log-directory data-directory + log-directory data-directory backup-directory 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))) + ;; 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)))))))) +(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=d")) + (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 thrown. + (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 (= 0 exit-value) + (format (current-output-port) + "postgres: backup of ~a completed successfully.~%" + 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 "/" file))) + (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" database) + options)) + (exit-value (status:exit-val result))) + (if (= 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 - log-directory data-directory + log-directory data-directory backup-directory 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)) - (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-directory) + (postgresql-list-backups-action backup-directory) + (postgresql-restore-action postgresql backup-directory))) (start (action "start")) (stop (action "stop")))))))) diff --git a/gnu/tests/databases.scm b/gnu/tests/databases.scm index 296d91d118..4210054d9e 100644 --- a/gnu/tests/databases.scm +++ b/gnu/tests/databases.scm @@ -134,6 +134,9 @@ (define %test-memcached ;;; The PostgreSQL service. ;;; +(define %postgresql-backup-directory + "/var/lib/postgresql/backup") + (define %postgresql-log-directory "/var/log/postgresql") @@ -195,8 +198,6 @@ (define marionette (test-assert "log-file" (marionette-eval '(begin - (use-modules (ice-9 ftw) - (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 - (use-modules (gnu services herd) - (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)) + (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 VARCHAR); +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-directory + (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='completed' WHERE name='backup'; +SELECT status FROM test WHERE name='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-directory + (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='backup'" + "root")) + (output (get-string-all port))) + (close-pipe port) + (string-trim-right output))) + marionette)) + (test-end)))) (gexp->derivation "postgresql-test" test)) -- 2.36.1