unofficial mirror of guix-patches@gnu.org 
 help / color / mirror / code / Atom feed
From: "Ludovic Courtès" <ludo@gnu.org>
To: Marius Bakke <marius@gnu.org>
Cc: 56045@debbugs.gnu.org
Subject: [bug#56045] [PATCH] Back up and restore PostgreSQL databases with Shepherd
Date: Wed, 22 Jun 2022 22:46:36 +0200	[thread overview]
Message-ID: <87v8ss1l5f.fsf@gnu.org> (raw)
In-Reply-To: <87zgibuh5w.fsf@gnu.org> (Marius Bakke's message of "Fri, 17 Jun 2022 23:14:03 +0200")

Hello!

Marius Bakke <marius@gnu.org> 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=#
>
> 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 <marius@gnu.org>
> Date: Thu, 16 Jun 2022 22:46:01 +0200
> Subject: [PATCH] services: Shepherd can backup and restore PostgreSQL
>  databases.
>
> * gnu/services/databases.scm (<postgresql-configuration>)[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.

Not being a database person, I’ll comment on the code:

>    (match-lambda
>      (($ <postgresql-configuration> postgresql port locale config-file
> -                                   log-directory data-directory
> +                                   log-directory data-directory backup-directory
>                                     extension-packages)

Time to use ‘match-record’!

> +(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 ‘use-modules’ should be avoided; it’s not really supposed
to work.  If you have these three modules in the ‘modules’ field of the
parent <shepherd-service> record, that’s enough (I know, it’s 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 ‘fork+exec-command’ to do all this?  It’d 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-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))

Same here: ‘fork+exec-command’?

Overall I find it nice and convenient, but I wonder how far we should go
with our services.  After all, it’s just one way to make backups, there
are probably other ways, so should we have this particular method
hardwired?

Thanks,
Ludo’.




  reply	other threads:[~2022-06-22 20:47 UTC|newest]

Thread overview: 3+ messages / expand[flat|nested]  mbox.gz  Atom feed  top
2022-06-17 21:14 [bug#56045] [PATCH] Back up and restore PostgreSQL databases with Shepherd Marius Bakke
2022-06-22 20:46 ` Ludovic Courtès [this message]
2024-02-28 12:32   ` Giovanni Biscuolo

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

  Avoid top-posting and favor interleaved quoting:
  https://en.wikipedia.org/wiki/Posting_style#Interleaved_style

  List information: https://guix.gnu.org/

* Reply using the --to, --cc, and --in-reply-to
  switches of git-send-email(1):

  git send-email \
    --in-reply-to=87v8ss1l5f.fsf@gnu.org \
    --to=ludo@gnu.org \
    --cc=56045@debbugs.gnu.org \
    --cc=marius@gnu.org \
    /path/to/YOUR_REPLY

  https://kernel.org/pub/software/scm/git/docs/git-send-email.html

* If your mail client supports setting the In-Reply-To header
  via mailto: links, try the mailto: link
Be sure your reply has a Subject: header at the top and a blank line before the message body.
Code repositories for project(s) associated with this public inbox

	https://git.savannah.gnu.org/cgit/guix.git

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).