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