From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from mp2.migadu.com ([2001:41d0:403:4876::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by ms8.migadu.com with LMTPS id iOhmMmAo32UhMwEAe85BDQ:P1 (envelope-from ) for ; Wed, 28 Feb 2024 13:34:41 +0100 Received: from aspmx1.migadu.com ([2001:41d0:403:4876::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by mp2.migadu.com with LMTPS id iOhmMmAo32UhMwEAe85BDQ (envelope-from ) for ; Wed, 28 Feb 2024 13:34:40 +0100 X-Envelope-To: larch@yhetil.org Authentication-Results: aspmx1.migadu.com; dkim=none; spf=pass (aspmx1.migadu.com: domain of "guix-patches-bounces+larch=yhetil.org@gnu.org" designates 209.51.188.17 as permitted sender) smtp.mailfrom="guix-patches-bounces+larch=yhetil.org@gnu.org"; dmarc=none ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=yhetil.org; s=key1; t=1709123680; h=from:from:sender:sender:reply-to:subject:subject:date:date: message-id:message-id:to:to:cc:cc:mime-version:mime-version: content-type:content-type:resent-cc:resent-from:resent-sender: resent-message-id:in-reply-to:in-reply-to:references:references: list-id:list-help:list-unsubscribe:list-subscribe:list-post; bh=ZGqms3QEOaP5aZqlT2fQHe/vl0oe9CvU1qTPE7G1BwY=; b=RG98EsYi38nNqWzzMJHb1QB2yadhYEud0gSPwUjc0r+z/1zhzrvJxq+seHbUbukXwsEB8c LG6btQ5Zj4n84E7b7k64R0aDRylLrl6yaO5ZWPssnYR89JTBO6mnxO6DyAvQTdKWXkCVp3 Wdr1uHAORjTzkNwQQ4UYTqnQo+m/nc9+50IAI5EnX3GIa5u1dnndRg1mqC3qgirG5BqeVv ai/KeqCRD5wA8g/AFx829B7D5SblV5bcUmaf++yeByws/IAxiJeWUQ4+7OqkKM5aT4pkYZ gnbEeRUAHGxdw/2zsI4GDwVxYLbTpI1KawIgj/5ZW4rrcKlK6qlQ7gKcP+/PyQ== ARC-Authentication-Results: i=1; aspmx1.migadu.com; dkim=none; spf=pass (aspmx1.migadu.com: domain of "guix-patches-bounces+larch=yhetil.org@gnu.org" designates 209.51.188.17 as permitted sender) smtp.mailfrom="guix-patches-bounces+larch=yhetil.org@gnu.org"; dmarc=none ARC-Seal: i=1; s=key1; d=yhetil.org; t=1709123680; a=rsa-sha256; cv=none; b=FcdZIFaMXGHIMb536nAPCUKys33FFDD3f77t6lmCbIOMHmJQu0ZIuG3yGWmgkQA2gsoRBC lefuWzrAALh0HZED7XGYrgq1KSRVH68JdiAmBY+umoGYnblAGs5dAm5+wxnicYGWltVjeZ rqGEsdk4fVXiYH2yWHHIWVw1IlOR6J0dilapV1Ydpw4ZXSRU1hLYR1xGr1FqZFMalMtMTF +dpvnZkxDilYkMjIriZi6SNuSfbGG531WUGJLleg7u9SBln6NACquGBARKeA+dJ0JqvWue hHiBjOCf3KyC/eKu3LaCqttYuon7FK+5EwMpiCm3UsQsk9K9Rwt16BamQilrqQ== Received: from lists.gnu.org (lists.gnu.org [209.51.188.17]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by aspmx1.migadu.com (Postfix) with ESMTPS id 6E32C71C63 for ; Wed, 28 Feb 2024 13:34:40 +0100 (CET) Received: from localhost ([::1] helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1rfJ8n-0003I6-Dh; Wed, 28 Feb 2024 07:34:29 -0500 Received: from eggs.gnu.org ([2001:470:142:3::10]) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1rfJ7y-0002kA-Jo for guix-patches@gnu.org; Wed, 28 Feb 2024 07:33:52 -0500 Received: from debbugs.gnu.org ([2001:470:142:5::43]) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.90_1) (envelope-from ) id 1rfJ7v-0000G9-UB for guix-patches@gnu.org; Wed, 28 Feb 2024 07:33:38 -0500 Received: from Debian-debbugs by debbugs.gnu.org with local (Exim 4.84_2) (envelope-from ) id 1rfJ8M-0004Ag-3s for guix-patches@gnu.org; Wed, 28 Feb 2024 07:34:02 -0500 X-Loop: help-debbugs@gnu.org Subject: [bug#56045] [PATCH] Back up and restore PostgreSQL databases with Shepherd Resent-From: Giovanni Biscuolo Original-Sender: "Debbugs-submit" Resent-CC: guix-patches@gnu.org Resent-Date: Wed, 28 Feb 2024 12:34:02 +0000 Resent-Message-ID: Resent-Sender: help-debbugs@gnu.org X-GNU-PR-Message: followup 56045 X-GNU-PR-Package: guix-patches X-GNU-PR-Keywords: moreinfo patch To: Marius Bakke , Ludovic =?UTF-8?Q?Court=C3=A8s?= Cc: 56045@debbugs.gnu.org Received: via spool by 56045-submit@debbugs.gnu.org id=B56045.170912361615984 (code B ref 56045); Wed, 28 Feb 2024 12:34:02 +0000 Received: (at 56045) by debbugs.gnu.org; 28 Feb 2024 12:33:36 +0000 Received: from localhost ([127.0.0.1]:36217 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1rfJ7v-00049h-GU for submit@debbugs.gnu.org; Wed, 28 Feb 2024 07:33:36 -0500 Received: from ns13.heimat.it ([46.4.214.66]:59844) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1rfJ7s-00049F-CH for 56045@debbugs.gnu.org; Wed, 28 Feb 2024 07:33:33 -0500 Received: from localhost (ip6-localhost [127.0.0.1]) by ns13.heimat.it (Postfix) with ESMTP id 43AA530081F; Wed, 28 Feb 2024 12:33:00 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at ns13.heimat.it Received: from ns13.heimat.it ([127.0.0.1]) by localhost (ns13.heimat.it [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id HOHo23VQzvtN; Wed, 28 Feb 2024 12:32:58 +0000 (UTC) Received: from bourrache.mug.xelera.it (unknown [93.56.171.217]) (using TLSv1.2 with cipher ECDHE-ECDSA-AES256-GCM-SHA384 (256/256 bits)) (Client did not present a certificate) by ns13.heimat.it (Postfix) with ESMTPSA id 2830830081A; Wed, 28 Feb 2024 12:32:58 +0000 (UTC) Received: from roquette.mug.biscuolo.net (roquette [10.38.2.14]) by bourrache.mug.xelera.it (Postfix) with SMTP id AD3AB2F09E92; Wed, 28 Feb 2024 13:32:57 +0100 (CET) Received: (nullmailer pid 16905 invoked by uid 1000); Wed, 28 Feb 2024 12:32:57 -0000 From: Giovanni Biscuolo In-Reply-To: <87v8ss1l5f.fsf@gnu.org> Organization: Xelera.eu References: <87zgibuh5w.fsf@gnu.org> <87v8ss1l5f.fsf@gnu.org> Date: Wed, 28 Feb 2024 13:32:57 +0100 Message-ID: <87h6hs4vly.fsf@xelera.eu> MIME-Version: 1.0 Content-Type: multipart/signed; boundary="=-=-="; micalg=pgp-sha512; protocol="application/pgp-signature" X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list X-BeenThere: guix-patches@gnu.org List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: guix-patches-bounces+larch=yhetil.org@gnu.org Sender: guix-patches-bounces+larch=yhetil.org@gnu.org X-Migadu-Flow: FLOW_IN X-Migadu-Country: US X-Migadu-Scanner: mx10.migadu.com X-Migadu-Spam-Score: -6.51 X-Spam-Score: -6.51 X-Migadu-Queue-Id: 6E32C71C63 X-TUID: TrvGk0ztarHZ --=-=-= Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable 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=C3=A8s 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=3D# >> >> 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 =2D-file=3Droles.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=E2=80=99ll 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=E2=80=99s 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. =2D-=20 Giovanni Biscuolo Xelera IT Infrastructures --=-=-= Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQJABAEBCgAqFiEERcxjuFJYydVfNLI5030Op87MORIFAmXfJ/kMHGdAeGVsZXJh LmV1AAoJENN9DqfOzDkSnmsP/j7Eym8AGq5mc+D7huJ4bkhgs222WK+zuBbsh0R1 Qi93j668rczE4mg1JruvOrPlD9uFFUvaeriFGB/8r/zO7s3gy5JW854F0vLQfwlC UYP9KuWiQbxhXzM7RfIGjLUIrodBQD8cc1nR6MBhkvMbemjpEdz4dP6LRFc77ANG oXzRLjOpEFvhDRPFCLCClw8OZNPOHSR63MBBF1r/F63to7RZA/11+CqC1b1Ilssy kRrE8bgb5RTBmxJ/153QG9Vs7mJo7gxlhLBlcvzHHkCJ15Dgi1gv5GThWvIx08Zw GTcMAO6NVZGSrP4Ae2OY+pkBnJ3Gt4sk8utRXmaCIpdfMPPeRSWckisNdRkGw6uK isHrmDsyeYI8QnaOZdoKWw+jYEqN03/VirE6C7yr/xEqzn2uGJFCsOpjmRnxmclQ dQNmkB5Re+4IntVy4FtgoD4ZgfaUsPaT6oPYrA30Dzg+o6wYqQNFfx2uL4SyWRGb +uEkQV4gUfY9naN1fcD6TpLDcveddhAv+Gu9rW3yHAmKBFkhTEJy95f86w1hz7jf ET6D9WXTTEzj10XmgaaNq2OiuioN/bqYqAaTHgU8wWJIBH33i/IWImFtNYd/L6gG BYXm3JdYa9Ex7tWIJTl6lPenLNK9dgwnyOMwnF5W+fdUqrKL7id96+F4vJTgpd5y OH2h =GqXh -----END PGP SIGNATURE----- --=-=-=--