Add postgresql replication for backup-2

5 files changed, 217 insertions, 2 deletions

diff --git a/modules/private/databases/default.nix b/modules/private/databases/default.nix
index b2739bf..d1d6a2b 100644
--- a/modules/private/databases/default.nix
+++ b/modules/private/databases/default.nix
@@ -38,8 +38,8 @@ in
         inherit (myconfig.env.databases.postgresql.pam) dn filter password;
       };
       replicationLdapConfig = {
-        inherit (myconfig.env.ldap) host base password;
-        dn = myconfig.env.ldap.host_dn;
+        inherit (myconfig.env.ldap) host base;
+        inherit (myconfig.env.ldap.eldiron) dn password;
       };
       authorizedHosts = {
         immaeEu = [{
diff --git a/modules/private/databases/postgresql.nix b/modules/private/databases/postgresql.nix
index 6d1901d..1932a82 100644
--- a/modules/private/databases/postgresql.nix
+++ b/modules/private/databases/postgresql.nix
@@ -121,6 +121,27 @@ in {
       SupplementaryGroups = "keys";
       RuntimeDirectory = cfg.systemdRuntimeDirectory;
     };
+    systemd.services.postgresql.postStart = lib.mkAfter ''
+      # This line is already defined in 19.09
+      PSQL="${pkgs.sudo}/bin/sudo -u postgres psql --port=5432"
+
+      ${builtins.concatStringsSep "\n" (lib.mapAttrsToList (role: _: ''
+        $PSQL -tAc "SELECT 1 FROM pg_roles WHERE rolname='${role}'" \
+            | grep -q 1 \
+            || $PSQL -tAc 'CREATE USER "${role}" WITH REPLICATION'
+        '') cfg.replicationHosts)}
+
+      ${builtins.concatStringsSep "\n" (lib.mapAttrsToList (role: _:
+        let
+          sname = builtins.replaceStrings ["-"] ["_"] role;
+        in
+        ''
+        $PSQL -tAc "SELECT 1 FROM pg_replication_slots WHERE slot_name='${sname}'" \
+              | grep -q 1 \
+              || $PSQL -tAc "SELECT * FROM pg_create_physical_replication_slot('${sname}')"
+        '') cfg.replicationHosts)}
+      '';
+
     services.postgresql = {
       enable = true;
       package = cfg.package;
diff --git a/modules/private/databases/postgresql_replication.nix b/modules/private/databases/postgresql_replication.nix
new file mode 100644
index 0000000..145fcac
--- /dev/null
+++ b/modules/private/databases/postgresql_replication.nix
@@ -0,0 +1,180 @@
+{ pkgs, config, lib, ... }:
+let
+  cfg = config.myServices.databasesReplication.postgresql;
+in
+{
+  options.myServices.databasesReplication.postgresql = {
+    enable = lib.mkEnableOption "Enable postgresql replication";
+    base = lib.mkOption {
+      type = lib.types.path;
+      description = ''
+        Base path to put the replications
+        '';
+    };
+    hosts = lib.mkOption {
+      default = {};
+      description = ''
+        Hosts to backup
+        '';
+      type = lib.types.attrsOf (lib.types.submodule {
+        options = {
+          package = lib.mkOption {
+            type = lib.types.package;
+            default = pkgs.postgresql;
+            description = ''
+              Postgresql package for this host
+            '';
+          };
+          slot = lib.mkOption {
+            type = lib.types.str;
+            description = ''
+              Slot to use for replication
+            '';
+          };
+          connection = lib.mkOption {
+            type = lib.types.str;
+            description = ''
+              Connection string to access the psql master
+            '';
+          };
+        };
+      });
+    };
+  };
+
+  config = lib.mkIf cfg.enable {
+    nixpkgs.overlays = [ (self: super: {
+      postgresql = self.postgresql_11_custom;
+    }) ];
+
+    users.users.postgres = {
+      name = "postgres";
+      uid = config.ids.uids.postgres;
+      group = "postgres";
+      description = "PostgreSQL server user";
+      home = "/var/lib/postgresql";
+      useDefaultShell = true;
+      extraGroups = [ "keys" ];
+    };
+    users.groups.postgres.gid = config.ids.gids.postgres;
+    environment.systemPackages = [ pkgs.postgresql ];
+
+    secrets.keys = lib.flatten (lib.mapAttrsToList (name: hcfg: [
+      {
+        dest = "postgresql_replication/${name}/recovery.conf";
+        user = "postgres";
+        group = "postgres";
+        permissions = "0400";
+        text = ''
+          standby_mode = on
+          primary_conninfo = '${hcfg.connection}?sslmode=require'
+          primary_slot_name = '${hcfg.slot}'
+          '';
+      }
+      {
+        dest = "postgresql_replication/${name}/connection_string";
+        user = "postgres";
+        group = "postgres";
+        permissions = "0400";
+        text = hcfg.connection;
+      }
+      {
+        dest = "postgresql_replication/${name}/postgresql.conf";
+        user = "postgres";
+        group = "postgres";
+        permissions = "0400";
+        text = let
+          dataDir = "${cfg.base}/${name}/postgresql";
+        in ''
+          listen_addresses = '''
+          unix_socket_directories = '${dataDir}'
+          data_directory = '${dataDir}'
+          wal_level = logical
+          '';
+      }
+    ]) cfg.hosts);
+
+    services.cron = {
+      enable = true;
+      systemCronJobs = lib.flatten (lib.mapAttrsToList (name: hcfg:
+        let
+          dataDir = "${cfg.base}/${name}/postgresql";
+          backupDir = "${cfg.base}/${name}/postgresql_backup";
+          backup_script = pkgs.writeScript "backup_psql_${name}" ''
+              #!${pkgs.stdenv.shell}
+
+              set -euo pipefail
+
+              resume_replication() {
+                ${hcfg.package}/bin/psql -h ${dataDir} -c "SELECT pg_wal_replay_resume();" >/dev/null || echo "impossible to resume replication"
+              }
+
+              trap resume_replication EXIT
+
+              ${hcfg.package}/bin/psql -h ${dataDir} -c "SELECT pg_wal_replay_pause();" >/dev/null || (echo "impossible to pause replication" && false)
+
+              ${hcfg.package}/bin/pg_dumpall -h ${dataDir} -f ${backupDir}/$(${pkgs.coreutils}/bin/date -Iseconds).sql
+            '';
+        in [
+          "0 22,4,10,16 * * * postgres ${backup_script}"
+          "0 3 * * * postgres ${pkgs.coreutils}/bin/rm -f $(${pkgs.coreutils}/bin/ls -1 ${backupDir}/*.sql | ${pkgs.coreutils}/bin/sort -r | ${pkgs.gnused}/bin/sed -e '1,12d')"
+        ]) cfg.hosts);
+    };
+
+    system.activationScripts = lib.attrsets.mapAttrs' (name: hcfg:
+      lib.attrsets.nameValuePair "psql_replication_${name}" {
+        deps = [ "users" ];
+        text = ''
+          install -m 0700 -o postgres -g postgres -d ${cfg.base}/${name}/postgresql
+          install -m 0700 -o postgres -g postgres -d ${cfg.base}/${name}/postgresql_backup
+          '';
+      }) cfg.hosts;
+
+    systemd.services = lib.attrsets.mapAttrs' (name: hcfg:
+      let
+        dataDir = "${cfg.base}/${name}/postgresql";
+      in
+      lib.attrsets.nameValuePair "postgresql_backup_${name}" {
+        description = "Postgresql replication for ${name}";
+        wantedBy = [ "multi-user.target" ];
+        after = [ "network.target" ];
+
+        environment.PGDATA = dataDir;
+        path = [ hcfg.package ];
+
+        preStart = ''
+          if ! test -e ${dataDir}/PG_VERSION; then
+            mkdir -m 0700 -p ${dataDir}
+            chown -R postgres:postgres ${dataDir}
+          fi
+          '';
+        script = let
+          fp = n: config.secrets.fullPaths."postgresql_replication/${name}/${n}";
+        in ''
+          if ! test -e ${dataDir}/PG_VERSION; then
+            pg_basebackup -d $(cat ${fp "connection_string"}) -D ${dataDir} -S ${hcfg.slot}
+          fi
+          ln -sfn ${fp "recovery.conf"} ${dataDir}/recovery.conf
+          ln -sfn ${fp "postgresql.conf"} ${dataDir}/postgresql.conf
+
+          exec postgres
+          '';
+
+        serviceConfig = {
+          ExecReload = "${pkgs.coreutils}/bin/kill -HUP $MAINPID";
+          User = "postgres";
+          Group = "postgres";
+          PermissionsStartOnly = true;
+          RuntimeDirectory = "postgresql";
+          Type = "notify";
+
+          KillSignal = "SIGINT";
+          KillMode = "mixed";
+          # basebackup can take a long time
+          TimeoutStartSec="infinity";
+          TimeoutStopSec = 120;
+        };
+        unitConfig.RequiresMountsFor = dataDir;
+    }) cfg.hosts;
+  };
+}
diff --git a/modules/private/default.nix b/modules/private/default.nix
index c418795..b4b8c4f 100644
--- a/modules/private/default.nix
+++ b/modules/private/default.nix
@@ -10,6 +10,7 @@ set = {
   openldap   = ./databases/openldap;
   postgresql = ./databases/postgresql.nix;
   redis      = ./databases/redis.nix;
+  postgresqlReplication = ./databases/postgresql_replication.nix;
 
   websites = ./websites;
   atenInte = ./websites/aten/integration.nix;
diff --git a/modules/private/system/backup-2.nix b/modules/private/system/backup-2.nix
index 6151671..1c5b7d8 100644
--- a/modules/private/system/backup-2.nix
+++ b/modules/private/system/backup-2.nix
@@ -48,6 +48,19 @@
     ssh_key_private = myconfig.env.rsync_backup.ssh_key.private;
   };
 
+  myServices.databasesReplication = {
+    postgresql = {
+      enable = true;
+      base = "/backup2";
+      hosts = {
+        eldiron = {
+          slot = "backup_2";
+          connection = "postgresql://backup-2:${myconfig.env.ldap.backup-2.password}@eldiron.immae.eu";
+        };
+      };
+    };
+  };
+
   # This value determines the NixOS release with which your system is
   # to be compatible, in order to avoid breaking some software such as
   # database servers. You should change this only after NixOS release