From 6d4ad63f9fc7720d38981681632de6359e62c666 Mon Sep 17 00:00:00 2001
From: Isabella Bastaja <isabella@sorre.xyz>
Date: Fri, 28 Aug 2026 22:21:51 +0200
Subject: tower: implement postgres backup service

---
 hosts/tower/postgres.nix | 36 ++++++++++++++++++++++++++++++++++++
 1 file changed, 36 insertions(+)

(limited to 'hosts')

diff --git a/hosts/tower/postgres.nix b/hosts/tower/postgres.nix
index 67c9fdd..31fab49 100644
--- a/hosts/tower/postgres.nix
+++ b/hosts/tower/postgres.nix
@@ -47,4 +47,40 @@ in
   }; # end of services.postgreslq
 
   environment.systemPackages = [ upgrade-pg-cluster ];
+
+  systemd.services.backup-postgres = lib.mkIf cfg.enable {
+    serviceConfig = {
+      Type = "exec";
+      User = "postgres";
+      Group = "postgres";
+      StateDirectory = "backups/postgres";
+      StateDirectoryMode = "0700";
+      Umask = "077";
+    };
+    description = "Back up postgres data";
+    path = [ cfg.finalPackage ];
+    enableStrictShellChecks = true;
+    script = ''
+      #!/usr/bin/env bash
+      set -euo pipefail
+
+      TIMESTAMP="$(date +%Y%m%d%H%M%S)"
+      BACKUP_PATH="$STATE_DIRECTORY/$TIMESTAMP.sql"
+      echo "Backing up postgresql to $BACKUP_PATH" >&2
+
+      pg_dumpall --file="$BACKUP_PATH" --verbose
+      chmod u=r,go-rwx "$BACKUP_PATH"
+
+      find "$STATE_DIRECTORY" -type f -name '*.sql' -print0 |
+        sort --zero-terminated --reverse |
+        tail --zero-terminated --lines=+6 |
+        xargs --null --no-run-if-empty rm --
+    '';
+  };
+
+  systemd.timers.backup-postgres = lib.mkIf cfg.enable {
+    description = "Back up postgres data every morning";
+    timerConfig.OnCalendar = "*-*-* 05:00:00";
+    wantedBy = [ "timers.target" ];
+  };
 }
-- 
cgit v1.3.1