From edc8a2e5ae3c89b78fb837d4351f0ddfab8fe474 Mon Sep 17 00:00:00 2001
* gnu/services/databases.scm (<postgresql-configuration>)[backup-directory]:
(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"
gnu/services/databases.scm | 169 ++++++++++++++++++++++++++++++++++++-
gnu/tests/databases.scm | 117 ++++++++++++++++++++++++-
2 files changed, 278 insertions(+), 8 deletions(-)
Toggle diff (370 lines)
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
;;; Copyright © 2018 Clément Lassieur <clement@lassieur.org>
;;; Copyright © 2018 Julien Lepiller <julien@lepiller.eu>
;;; Copyright © 2019 Robert Vollmert <rob@vllmrt.net>
-;;; Copyright © 2020 Marius Bakke <marius@gnu.org>
+;;; Copyright © 2020, 2022 Marius Bakke <marius@gnu.org>
;;; Copyright © 2021 David Larsson <david.larsson@selfhosted.xyz>
;;; This file is part of GNU Guix.
@@ -176,6 +176,8 @@ (define-record-type* <postgresql-configuration>
(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
@@ -213,7 +215,7 @@ (define (final-postgresql postgresql extension-packages)
(define postgresql-activation
(($ <postgresql-configuration> postgresql port locale config-file
- log-directory data-directory
+ log-directory data-directory backup-directory
(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.
@@ -265,10 +272,155 @@ (define postgresql-activation
(pid (waitpid pid))))))))
+(define (postgresql-backup-action postgresql backup-directory)
+ "Back up a database on the running PostgreSQL server.")
+ #~(lambda* (pid #:optional database #:rest rest)
+ (use-modules (guix build utils)
+ (let* ((user (getpwnam "postgres"))
+ (pg_dump #$(file-append postgresql "/bin/pg_dump"))
+ (options '("--create" "--clean" "--if-exists"
+ (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)
+ ;; Exit with a non-zero status code if an exception is thrown.
+ (setgid (passwd:gid user))
+ (setuid (passwd:uid user))
+ (format (current-output-port)
+ "postgres: creating backup ~a.~%"
+ (mkdir-p (dirname file-name))
+ (let* ((result (apply system* pg_dump database
+ (exit-value (status:exit-val result)))
+ (format (current-output-port)
+ "postgres: backup of ~a completed successfully.~%"
+ (format (current-output-port)
+ "postgres: backup of ~a completed with errors.~%"
+ (primitive-exit exit-value)))
+ (format (current-output-port)
+ "postgres: backup of ~a failed.~%")
+ (format #t "usage: herd backup postgres DATABASE~%")
+(define (postgresql-list-backups-action backup-directory)
+ "List available PostgreSQL backups.")
+ #~(lambda* (pid #:optional database #:rest rest)
+ (use-modules (ice-9 ftw)
+ (if (file-exists? #$backup-directory)
+ (for-each (cut format #t "~a~%" <>)
+ (scandir #$backup-directory
+ (cut string-prefix? database <>)
+ (negate (cut member <> '("." ".."))))))
+(define (postgresql-restore-action postgresql backup-directory)
+ "Restore a PostgreSQL backup.")
+ #~(lambda* (pid #:optional file #:rest rest)
+ (use-modules (ice-9 match)
+ ;; 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"))
+ (port (open-input-pipe (string-append psql " -lqtA"
+ (let loop ((line (read-line port)))
+ ((string-prefix? (string-append db separator) line)
+ (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)
+ (setgid (passwd:gid user))
+ (setuid (passwd:uid user))
+ (let* ((backup-file (string-append #$backup-directory
+ (database (match (string-split file #\@)
+ (create? (not (database-exists? database)))
+ (options (list "--clean" "--if-exists"
+ "--single-transaction"))))
+ (format (current-output-port)
+ "postgres: restoring ~a.~%" file)
+ (let* ((result (apply system* pg_restore backup-file
+ "-d" (if create? "postgres" database)
+ (exit-value (status:exit-val result)))
+ (format (current-output-port)
+ "postgres: restore of ~a completed \
+ (format (current-output-port)
+ "postgres: restore of ~a completed \
+ (primitive-exit exit-value))))
+ (format #t "postgres: could not restore ~a.~%" file)
+ (format #t "usage: herd restore postgres BACKUP~%")
+ (format #t "hint: see 'herd list-backups postgres'~%")
(define postgresql-shepherd-service
(($ <postgresql-configuration> postgresql port locale config-file
- log-directory data-directory
+ log-directory data-directory backup-directory
;; Wrapper script that switches to the 'postgres' user before
@@ -309,8 +461,17 @@ (define postgresql-shepherd-service
(documentation "Run the PostgreSQL daemon.")
(requirement '(user-processes loopback syslogd))
- (modules `((ice-9 match)
+ (postgresql-backup-action postgresql backup-directory)
+ (postgresql-list-backups-action backup-directory)
+ (postgresql-restore-action postgresql backup-directory)))
(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
@@ -195,8 +198,6 @@ (define marionette
- (use-modules (ice-9 ftw)
(open-file "/dev/console" "w0"))
@@ -227,8 +228,7 @@ (define marionette
(test-assert "database creation"
- (use-modules (gnu services herd)
+ (use-modules (ice-9 popen))
(open-file "/dev/console" "w0"))
@@ -241,6 +241,115 @@ (define marionette
(string-contains output "1")))
+ (test-eq "insert test data"
+ (open-file "/dev/console" "w0"))
+ #$(file-append postgresql "/bin/psql")
+ "-tA" "-c" "CREATE TABLE test (name VARCHAR,
+INSERT INTO TEST VALUES ('backup', 'pending');"
+ (status:exit-val result)))
+ (test-assert "backup database"
+ '(with-shepherd-action 'postgres ('backup "root")
+ (test-assert "list backups"
+ '(with-shepherd-action 'postgres ('list-backups)
+ (test-eq "drop database"
+ (open-file "/dev/console" "w0"))
+ #$(file-append postgresql "/bin/psql")
+ "-tA" "-c" "DROP DATABASE root"
+ (status:exit-val result)))
+ (test-assert "restore database"
+ (let ((file-name (marionette-eval
+ (use-modules (ice-9 ftw)
+ (car (scandir #$%postgresql-backup-directory
+ `(with-shepherd-action 'postgres ('restore ,file-name)
+ (test-equal "update test data"
+ (use-modules (ice-9 popen))
+ (open-file "/dev/console" "w0"))
+ (let* ((port (open-pipe*
+ #$(file-append postgresql "/bin/psql")
+UPDATE test SET status='completed' WHERE name='backup';
+SELECT status FROM test WHERE name='backup';"
+ (output (get-string-all port)))
+ (string-trim-right output)))
+ (test-assert "restore again"
+ (let ((file-name (marionette-eval
+ (use-modules (ice-9 ftw)
+ (car (scandir #$%postgresql-backup-directory
+ `(with-shepherd-action 'postgres ('restore ,file-name)
+ (test-equal "verify restore"
+ (use-modules (ice-9 popen))
+ (open-file "/dev/console" "w0"))
+ (let* ((port (open-pipe*
+ #$(file-append postgresql "/bin/psql")
+SELECT status FROM test WHERE name='backup'"
+ (output (get-string-all port)))
+ (string-trim-right output)))
(gexp->derivation "postgresql-test" test))