[bug#56045] [PATCH] Back up and restore PostgreSQL databases with Shepherd

[Giovanni Biscuolo]

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ès <ludo@gnu.org> writes:

> 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

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=#
>>
>> 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
--file=roles.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’ll 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’s 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.

-- 
Giovanni Biscuolo

Xelera IT Infrastructures

signature.asc
Description: PGP signature