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 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=# >> >> 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