GNU bug report logs - #56045
[PATCH] Back up and restore PostgreSQL databases with Shepherd

Previous Next

Full log

Message #5 received at submit <at> debbugs.gnu.org (full text, mbox):

From: Marius Bakke <marius <at> gnu.org>
To: guix-patches <at> gnu.org
Subject: [PATCH] Back up and restore PostgreSQL databases with Shepherd
Date: Fri, 17 Jun 2022 23:14:03 +0200

[Message part 1 (text/plain, inline)]

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 <at> 2022-06-16_21-55-07
mreg <at> 2022-06-16_22-48-59
$ sudo herd restore postgres mreg <at> 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!

[0001-services-Shepherd-can-backup-and-restore-PostgreSQL-.patch (text/x-patch, attachment)]

[signature.asc (application/pgp-signature, inline)]

Previous Next

GNU bug tracking system
Copyright (C) 1999 Darren O. Benham, 1997,2003 nCipher Corporation Ltd, 1994-97 Ian Jackson.