modules/private/databases/postgresql_replication.nix

   1 { pkgs, config, lib, ... }:
   2 let
   3   cfg = config.myServices.databasesReplication.postgresql;
   4 in
   5 {
   6   options.myServices.databasesReplication.postgresql = {
   7     enable = lib.mkEnableOption "Enable postgresql replication";
   8     base = lib.mkOption {
   9       type = lib.types.path;
  10       description = ''
  11         Base path to put the replications
  12         '';
  13     };
  14     hosts = lib.mkOption {
  15       default = {};
  16       description = ''
  17         Hosts to backup
  18         '';
  19       type = lib.types.attrsOf (lib.types.submodule {
  20         options = {
  21           package = lib.mkOption {
  22             type = lib.types.package;
  23             default = pkgs.postgresql;
  24             description = ''
  25               Postgresql package for this host
  26             '';
  27           };
  28           slot = lib.mkOption {
  29             type = lib.types.str;
  30             description = ''
  31               Slot to use for replication
  32             '';
  33           };
  34           connection = lib.mkOption {
  35             type = lib.types.str;
  36             description = ''
  37               Connection string to access the psql master
  38             '';
  39           };
  40         };
  41       });
  42     };
  43   };
  44
  45   config = lib.mkIf cfg.enable {
  46     nixpkgs.overlays = [ (self: super: {
  47       postgresql = self.postgresql_11_custom;
  48     }) ];
  49
  50     users.users.postgres = {
  51       name = "postgres";
  52       uid = config.ids.uids.postgres;
  53       group = "postgres";
  54       description = "PostgreSQL server user";
  55       home = "/var/lib/postgresql";
  56       useDefaultShell = true;
  57       extraGroups = [ "keys" ];
  58     };
  59     users.groups.postgres.gid = config.ids.gids.postgres;
  60     environment.systemPackages = [ pkgs.postgresql ];
  61
  62     secrets.keys = lib.flatten (lib.mapAttrsToList (name: hcfg: [
  63       {
  64         dest = "postgresql_replication/${name}/recovery.conf";
  65         user = "postgres";
  66         group = "postgres";
  67         permissions = "0400";
  68         text = ''
  69           standby_mode = on
  70           primary_conninfo = '${hcfg.connection}?sslmode=require'
  71           primary_slot_name = '${hcfg.slot}'
  72           '';
  73       }
  74       {
  75         dest = "postgresql_replication/${name}/connection_string";
  76         user = "postgres";
  77         group = "postgres";
  78         permissions = "0400";
  79         text = hcfg.connection;
  80       }
  81       {
  82         dest = "postgresql_replication/${name}/postgresql.conf";
  83         user = "postgres";
  84         group = "postgres";
  85         permissions = "0400";
  86         text = let
  87           dataDir = "${cfg.base}/${name}/postgresql";
  88         in ''
  89           listen_addresses = '''
  90           unix_socket_directories = '${dataDir}'
  91           data_directory = '${dataDir}'
  92           wal_level = logical
  93           '';
  94       }
  95     ]) cfg.hosts);
  96
  97     services.cron = {
  98       enable = true;
  99       systemCronJobs = lib.flatten (lib.mapAttrsToList (name: hcfg:
 100         let
 101           dataDir = "${cfg.base}/${name}/postgresql";
 102           backupDir = "${cfg.base}/${name}/postgresql_backup";
 103           backup_script = pkgs.writeScript "backup_psql_${name}" ''
 104               #!${pkgs.stdenv.shell}
 105
 106               set -euo pipefail
 107
 108               resume_replication() {
 109                 ${hcfg.package}/bin/psql -h ${dataDir} -c "SELECT pg_wal_replay_resume();" >/dev/null || echo "impossible to resume replication"
 110               }
 111
 112               trap resume_replication EXIT
 113
 114               ${hcfg.package}/bin/psql -h ${dataDir} -c "SELECT pg_wal_replay_pause();" >/dev/null || (echo "impossible to pause replication" && false)
 115
 116               ${hcfg.package}/bin/pg_dumpall -h ${dataDir} -f ${backupDir}/$(${pkgs.coreutils}/bin/date -Iseconds).sql
 117             '';
 118         in [
 119           "0 22,4,10,16 * * * postgres ${backup_script}"
 120           "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')"
 121         ]) cfg.hosts);
 122     };
 123
 124     system.activationScripts = lib.attrsets.mapAttrs' (name: hcfg:
 125       lib.attrsets.nameValuePair "psql_replication_${name}" {
 126         deps = [ "users" ];
 127         text = ''
 128           install -m 0700 -o postgres -g postgres -d ${cfg.base}/${name}/postgresql
 129           install -m 0700 -o postgres -g postgres -d ${cfg.base}/${name}/postgresql_backup
 130           '';
 131       }) cfg.hosts;
 132
 133     systemd.services = lib.attrsets.mapAttrs' (name: hcfg:
 134       let
 135         dataDir = "${cfg.base}/${name}/postgresql";
 136       in
 137       lib.attrsets.nameValuePair "postgresql_backup_${name}" {
 138         description = "Postgresql replication for ${name}";
 139         wantedBy = [ "multi-user.target" ];
 140         after = [ "network.target" ];
 141
 142         environment.PGDATA = dataDir;
 143         path = [ hcfg.package ];
 144
 145         preStart = ''
 146           if ! test -e ${dataDir}/PG_VERSION; then
 147             mkdir -m 0700 -p ${dataDir}
 148             chown -R postgres:postgres ${dataDir}
 149           fi
 150           '';
 151         script = let
 152           fp = n: config.secrets.fullPaths."postgresql_replication/${name}/${n}";
 153         in ''
 154           if ! test -e ${dataDir}/PG_VERSION; then
 155             pg_basebackup -d $(cat ${fp "connection_string"}) -D ${dataDir} -S ${hcfg.slot}
 156           fi
 157           ln -sfn ${fp "recovery.conf"} ${dataDir}/recovery.conf
 158           ln -sfn ${fp "postgresql.conf"} ${dataDir}/postgresql.conf
 159
 160           exec postgres
 161           '';
 162
 163         serviceConfig = {
 164           ExecReload = "${pkgs.coreutils}/bin/kill -HUP $MAINPID";
 165           User = "postgres";
 166           Group = "postgres";
 167           PermissionsStartOnly = true;
 168           RuntimeDirectory = "postgresql";
 169           Type = "notify";
 170
 171           KillSignal = "SIGINT";
 172           KillMode = "mixed";
 173           # basebackup can take a long time
 174           TimeoutStartSec="infinity";
 175           TimeoutStopSec = 120;
 176         };
 177         unitConfig.RequiresMountsFor = dataDir;
 178     }) cfg.hosts;
 179   };
 180 }