From unknown Fri Aug 08 15:18:08 2025 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-Spam-Score: -0.7 (/) X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -1.7 (-) --==-=-= 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----- --==-=-=-- From unknown Fri Aug 08 15:18:08 2025 X-Loop: help-debbugs@gnu.org Subject: [bug#56045] [PATCH] Back up and restore PostgreSQL databases with Shepherd Resent-From: Ludovic =?UTF-8?Q?Court=C3=A8s?= Original-Sender: "Debbugs-submit" Resent-CC: guix-patches@gnu.org Resent-Date: Wed, 22 Jun 2022 20:47:02 +0000 Resent-Message-ID: Resent-Sender: help-debbugs@gnu.org X-GNU-PR-Message: followup 56045 X-GNU-PR-Package: guix-patches X-GNU-PR-Keywords: patch To: Marius Bakke Cc: 56045@debbugs.gnu.org Received: via spool by 56045-submit@debbugs.gnu.org id=B56045.165593080632686 (code B ref 56045); Wed, 22 Jun 2022 20:47:02 +0000 Received: (at 56045) by debbugs.gnu.org; 22 Jun 2022 20:46:46 +0000 Received: from localhost ([127.0.0.1]:36722 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1o47FO-0008V6-6z for submit@debbugs.gnu.org; Wed, 22 Jun 2022 16:46:46 -0400 Received: from eggs.gnu.org ([209.51.188.92]:35434) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1o47FM-0008Uv-Vc for 56045@debbugs.gnu.org; Wed, 22 Jun 2022 16:46:46 -0400 Received: from fencepost.gnu.org ([2001:470:142:3::e]:52088) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1o47FH-0003yY-Jn for 56045@debbugs.gnu.org; Wed, 22 Jun 2022 16:46:39 -0400 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=gnu.org; s=fencepost-gnu-org; h=MIME-Version:In-Reply-To:Date:References:Subject:To: From; bh=Srl7FLUDCSbmI+hXLg2wC+gEuZjZhZi9YPX4NLJsFqY=; b=eUHAkEu/zGZdWTL3YNpe zd+BV3pPlqwm09RqfTvn8o3c5yMhzgbjcxiaMuDjBEL7wXZsfhAGHpH7cJmkpzwpcG0AMAS6rRKkH qDICzR7v+rFJH7Fx/3pE1CN+7KkffgOu2rPuyaEKJKZomYCXHQCtfDcIAUTeEtqEM3FvbqnNdcZyo 9VHWPo+8nfZp6Tp95HC3hD5ewieh/H3vVmFUUMeEYdRbZubGBEUeEWI+unZPiJPv14XLN+LZGDwNX P+07KSEXAIvk8bE2r5uu2uOESrbDXR4hmJcjjwCL4FNGWWkK/0PVwinisuhdW+fhLkRdq48cOfWQe 7zdTH7IzKM9HmQ==; Received: from 91-160-117-201.subs.proxad.net ([91.160.117.201]:53889 helo=ribbon) by fencepost.gnu.org with esmtpsa (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1o47FG-0004fj-F0; Wed, 22 Jun 2022 16:46:38 -0400 From: Ludovic =?UTF-8?Q?Court=C3=A8s?= References: <87zgibuh5w.fsf@gnu.org> Date: Wed, 22 Jun 2022 22:46:36 +0200 In-Reply-To: <87zgibuh5w.fsf@gnu.org> (Marius Bakke's message of "Fri, 17 Jun 2022 23:14:03 +0200") Message-ID: <87v8ss1l5f.fsf@gnu.org> User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/28.1 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-Spam-Score: -2.3 (--) X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -3.3 (---) Hello! Marius Bakke skribis: > 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=3D# > > Pretty cool, no? :-) Indeed! :-) > 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? No idea, we need input from PG practitioners! > 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-director= y]: > 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. A= dd > "insert test data", "backup database", "list backups", "drop database", > "restore database", "update test data", "restore again", and "verify rest= ore" > tests. Not being a database person, I=E2=80=99ll comment on the code: > (match-lambda > (($ postgresql port locale config-file > - log-directory data-directory > + log-directory data-directory backup-d= irectory > extension-packages) Time to use =E2=80=98match-record=E2=80=99! > +(define (postgresql-backup-action postgresql backup-directory) Please add a docstring (and on other top-level procedures). > + (procedure > + #~(lambda* (pid #:optional database #:rest rest) > + (use-modules (guix build utils) > + (ice-9 match) > + (srfi srfi-19)) Non-top-level =E2=80=98use-modules=E2=80=99 should be avoided; it=E2=80=99s= not really supposed to work. If you have these three modules in the =E2=80=98modules=E2=80=99 = field of the parent record, that=E2=80=99s enough (I know, it=E2=80= =99s not pretty). > + ;; 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))) Would it work to use =E2=80=98fork+exec-command=E2=80=99 to do all this? I= t=E2=80=99d be great if we could avoid the boilerplate. > +(define (postgresql-list-backups-action backup-directory) Docstring. :-) > + (match (primitive-fork) > + (0 > + (dynamic-wind > + (const #t) > + (lambda () > + (setgid (passwd:gid user)) > + (setuid (passwd:uid user)) > + (let* ((backup-file (string-append #$backup-directo= ry > + "/" 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-f= ile > + "-d" (if create? "postgres"= database) > + options)) Same here: =E2=80=98fork+exec-command=E2=80=99? Overall I find it nice and convenient, but I wonder how far we should go with our services. After all, it=E2=80=99s just one way to make backups, t= here are probably other ways, so should we have this particular method hardwired? Thanks, Ludo=E2=80=99. From debbugs-submit-bounces@debbugs.gnu.org Thu Aug 04 05:11:05 2022 Received: (at control) by debbugs.gnu.org; 4 Aug 2022 09:11:05 +0000 Received: from localhost ([127.0.0.1]:51186 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1oJWsj-0002RY-D9 for submit@debbugs.gnu.org; Thu, 04 Aug 2022 05:11:05 -0400 Received: from eggs.gnu.org ([209.51.188.92]:47298) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1oJWsh-0002Qz-GV for control@debbugs.gnu.org; Thu, 04 Aug 2022 05:11:03 -0400 Received: from fencepost.gnu.org ([2001:470:142:3::e]:36926) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1oJWsc-00035K-9U for control@debbugs.gnu.org; Thu, 04 Aug 2022 05:10:58 -0400 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=gnu.org; s=fencepost-gnu-org; h=MIME-version:Subject:From:To:Date:in-reply-to: references; bh=CMO3WpUzLash6u+ukJrs69aAHAMjHCU36ARAdj24HTE=; b=PFb7NBcFLy2bSQ 2gEt1u1IjhBt7T6HUpcQkLxuxly3/hDwMDNp2UMqe2gqXv8JgRdut47eChvW/9xGuX1hkDHO/BVbE kUWX4+eUcqSBOISwB3s4PEhtJHhf9gnNW1EJ2uQi8gMOu2ZV/6IMF8neYLOtDMZIcEFe5pTnSqP2e aC50tXmHZmhwwF9T6HxHLeEnmVTtwlqNQTLvDQht832AGG++EVny/hjtV95mQiESXqoJKwh1VXK0t 00WjgZoiYMpG3dfNeBUrWiWjVxLqJ8FYxzChC7iqvRA6YFcyjdzh5akogF7K8aJm5PcwphK5rV22g YOjY01rUMZour5gbiriQ==; Received: from [2001:660:6102:320:e120:2c8f:8909:cdfe] (port=43050 helo=ribbon) by fencepost.gnu.org with esmtpsa (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1oJWsb-0001JX-MY for control@debbugs.gnu.org; Thu, 04 Aug 2022 05:10:57 -0400 Date: Thu, 04 Aug 2022 11:10:56 +0200 Message-Id: <8735ecpe5b.fsf@gnu.org> To: control@debbugs.gnu.org From: =?utf-8?Q?Ludovic_Court=C3=A8s?= Subject: control message for bug #56045 MIME-version: 1.0 Content-type: text/plain; charset=utf-8 Content-Transfer-Encoding: 8bit X-Spam-Score: -2.3 (--) X-Debbugs-Envelope-To: control X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -3.3 (---) tags 56045 + moreinfo quit From unknown Fri Aug 08 15:18:08 2025 X-Loop: help-debbugs@gnu.org Subject: [bug#56045] [PATCH] Back up and restore PostgreSQL databases with Shepherd Resent-From: Giovanni Biscuolo Original-Sender: "Debbugs-submit" Resent-CC: guix-patches@gnu.org Resent-Date: Wed, 28 Feb 2024 12:34:02 +0000 Resent-Message-ID: Resent-Sender: help-debbugs@gnu.org X-GNU-PR-Message: followup 56045 X-GNU-PR-Package: guix-patches X-GNU-PR-Keywords: moreinfo patch To: Marius Bakke , Ludovic =?UTF-8?Q?Court=C3=A8s?= Cc: 56045@debbugs.gnu.org Received: via spool by 56045-submit@debbugs.gnu.org id=B56045.170912361615984 (code B ref 56045); Wed, 28 Feb 2024 12:34:02 +0000 Received: (at 56045) by debbugs.gnu.org; 28 Feb 2024 12:33:36 +0000 Received: from localhost ([127.0.0.1]:36217 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1rfJ7v-00049h-GU for submit@debbugs.gnu.org; Wed, 28 Feb 2024 07:33:36 -0500 Received: from ns13.heimat.it ([46.4.214.66]:59844) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1rfJ7s-00049F-CH for 56045@debbugs.gnu.org; Wed, 28 Feb 2024 07:33:33 -0500 Received: from localhost (ip6-localhost [127.0.0.1]) by ns13.heimat.it (Postfix) with ESMTP id 43AA530081F; Wed, 28 Feb 2024 12:33:00 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at ns13.heimat.it Received: from ns13.heimat.it ([127.0.0.1]) by localhost (ns13.heimat.it [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id HOHo23VQzvtN; Wed, 28 Feb 2024 12:32:58 +0000 (UTC) Received: from bourrache.mug.xelera.it (unknown [93.56.171.217]) (using TLSv1.2 with cipher ECDHE-ECDSA-AES256-GCM-SHA384 (256/256 bits)) (Client did not present a certificate) by ns13.heimat.it (Postfix) with ESMTPSA id 2830830081A; Wed, 28 Feb 2024 12:32:58 +0000 (UTC) Received: from roquette.mug.biscuolo.net (roquette [10.38.2.14]) by bourrache.mug.xelera.it (Postfix) with SMTP id AD3AB2F09E92; Wed, 28 Feb 2024 13:32:57 +0100 (CET) Received: (nullmailer pid 16905 invoked by uid 1000); Wed, 28 Feb 2024 12:32:57 -0000 From: Giovanni Biscuolo In-Reply-To: <87v8ss1l5f.fsf@gnu.org> Organization: Xelera.eu References: <87zgibuh5w.fsf@gnu.org> <87v8ss1l5f.fsf@gnu.org> Date: Wed, 28 Feb 2024 13:32:57 +0100 Message-ID: <87h6hs4vly.fsf@xelera.eu> MIME-Version: 1.0 Content-Type: multipart/signed; boundary="=-=-="; micalg=pgp-sha512; protocol="application/pgp-signature" X-Spam-Score: -0.0 (/) X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -1.0 (-) --=-=-= Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Hello Marius and Ludovic, maybe I'm late at the party, sorry. I'm interested in this patch and I'd like to test it and help as I can to upstream it: Marius could you please address Ludovic comments and send an updated patch? I also have a few comments/questions of mine... Ludovic Court=C3=A8s writes: > Marius Bakke skribis: > >> 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 backup or... dump? :-) Also: what about a dump/restore of all the databases in a cluster? AFAIU something like this could be easily automated via an mcron job (or extending the service with fully automated dumps management in the future) >> $ 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=3D# >> >> Pretty cool, no? :-) > > Indeed! :-) This would be simply fantastic IMO there should be a way to automatically delete old backups (max-backup-files? max-retention-period?) when starting a new one, in order not fill the entoire disk after some time >> 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 This would be a great workflow for upgrades, the backup/restore of the datadases (the status) could also be automated on the very first start of the service: if PostgreSQL fails due to an incopatible database version, make a backup using the previuos psql version (I guess that can be easily found), restore it with the new version and then start the service (with the new version)... no? >> -- 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? Ideally all roles should be declaratively managed (at least this is the way i like it!) but passwords can be managed only imperatively AFAIU [1] IMO a [dump|restore]-role command is needed, also; something doing: pg_dumpall -U postgres -h localhost -p 5433 --clean --roles-only =2D-file=3Droles.sql "--roles-only" or "--globals-only" (roles and tablespaces)? AFAIU roles.sql restore should be done /before/ the (re)creation of roles declared by postgresql-role-service-type [...] > Not being a database person, I=E2=80=99ll comment on the code: Not being a Guile person, I'll not comment on the code :-) [...] > Overall I find it nice and convenient, but I wonder how far we should go > with our services. After all, it=E2=80=99s just one way to make backups,= there > are probably other ways, so should we have this particular method > hardwired? Yes please :-) Doing a pgSQL database dump (backup?) with pg_dump (that is hardwired ;-) ) is a _prerequisite_ for all other backup tools users may choose to adopt: borgbackup/borgmatic, restic, rdiff-backup and so on. Having an /integrated/ way to *dump* and restore database status is a great functionality for a database service, IMO... now we can do it "by hand" for sure, but doing this semi-declaratively (and one day meybe fully declaratively) would be great. In other words: for database [2] sysadmins, backup (dump) is _part_ of the service :-D Happy hacking! Gio' [1] actyally I'd like to find a way to avoid this and manage roles /only/ declaratively (actually _dropping_ all not declared roles, to avoid "old status stratification" problems)... but this is off-topic here. [2] all databases with a binary on-disk format that cannot me managed like a simple file or directory like pgSQL, MySQL, openLDAP and so on. =2D-=20 Giovanni Biscuolo Xelera IT Infrastructures --=-=-= Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQJABAEBCgAqFiEERcxjuFJYydVfNLI5030Op87MORIFAmXfJ/kMHGdAeGVsZXJh LmV1AAoJENN9DqfOzDkSnmsP/j7Eym8AGq5mc+D7huJ4bkhgs222WK+zuBbsh0R1 Qi93j668rczE4mg1JruvOrPlD9uFFUvaeriFGB/8r/zO7s3gy5JW854F0vLQfwlC UYP9KuWiQbxhXzM7RfIGjLUIrodBQD8cc1nR6MBhkvMbemjpEdz4dP6LRFc77ANG oXzRLjOpEFvhDRPFCLCClw8OZNPOHSR63MBBF1r/F63to7RZA/11+CqC1b1Ilssy kRrE8bgb5RTBmxJ/153QG9Vs7mJo7gxlhLBlcvzHHkCJ15Dgi1gv5GThWvIx08Zw GTcMAO6NVZGSrP4Ae2OY+pkBnJ3Gt4sk8utRXmaCIpdfMPPeRSWckisNdRkGw6uK isHrmDsyeYI8QnaOZdoKWw+jYEqN03/VirE6C7yr/xEqzn2uGJFCsOpjmRnxmclQ dQNmkB5Re+4IntVy4FtgoD4ZgfaUsPaT6oPYrA30Dzg+o6wYqQNFfx2uL4SyWRGb +uEkQV4gUfY9naN1fcD6TpLDcveddhAv+Gu9rW3yHAmKBFkhTEJy95f86w1hz7jf ET6D9WXTTEzj10XmgaaNq2OiuioN/bqYqAaTHgU8wWJIBH33i/IWImFtNYd/L6gG BYXm3JdYa9Ex7tWIJTl6lPenLNK9dgwnyOMwnF5W+fdUqrKL7id96+F4vJTgpd5y OH2h =GqXh -----END PGP SIGNATURE----- --=-=-=--