--- /dev/null
+{ 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
+ '';
+ };
+ mainPackage = lib.mkOption {
+ type = lib.types.package;
+ default = pkgs.postgresql;
+ description = ''
+ Postgresql package available in shell
+ '';
+ };
+ 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 {
+ myServices.chatonsProperties.hostings.postgresql-replication = {
+ file.datetime = "2022-08-27T15:00:00";
+ hosting = {
+ name = "PostgreSQL replication";
+ description = "Replication of PostgreSQL database";
+ website = "db-1.immae.eu";
+ status.level = "OK";
+ status.description = "OK";
+ registration.load = "OPEN";
+ install.type = "PACKAGE";
+ };
+ software = {
+ name = "PostgreSQL";
+ website = "https://www.postgresql.org/";
+ license.url = "https://www.postgresql.org/about/licence/";
+ license.name = "The PostgreSQL Licence";
+ version = pkgs.postgresql.version;
+ source.url = "https://git.postgresql.org/gitweb/?p=postgresql.git;a=summary";
+ };
+ };
+ 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 = [ cfg.mainPackage ];
+
+ secrets.keys = lib.listToAttrs (lib.flatten (lib.mapAttrsToList (name: hcfg: [
+ (lib.nameValuePair "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}'
+ '';
+ })
+ (lib.nameValuePair "postgresql_replication/${name}/connection_string" {
+ user = "postgres";
+ group = "postgres";
+ permissions = "0400";
+ text = hcfg.connection;
+ })
+ (lib.nameValuePair "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
+ max_connections = 300
+ '';
+ })
+ ]) 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 -Iminutes).sql
+ '';
+ u = pkgs.callPackage ./utils.nix {};
+ cleanup_script = pkgs.writeScript "cleanup_postgresql_${name}" (u.keepLastNDumps "sql" backupDir 6);
+ in [
+ "0 22,4,10,16 * * * postgres ${backup_script}"
+ "0 3 * * * postgres ${cleanup_script}"
+ ]) 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;
+ };
+}