From ec9b656476d4af2784aea29b846bead85dc46e16 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Isma=C3=ABl=20Bouya?= Date: Mon, 11 Nov 2019 10:08:20 +0100 Subject: [PATCH] Add postgresql replication for backup-2 --- modules/private/databases/default.nix | 4 +- modules/private/databases/postgresql.nix | 21 ++ .../databases/postgresql_replication.nix | 180 ++++++++++++++++++ modules/private/default.nix | 1 + modules/private/system/backup-2.nix | 13 ++ 5 files changed, 217 insertions(+), 2 deletions(-) create mode 100644 modules/private/databases/postgresql_replication.nix 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 -- 2.41.0