1 { pkgs, config, lib, ... }:
3 cfg = config.myServices.databasesReplication.postgresql;
6 options.myServices.databasesReplication.postgresql = {
7 enable = lib.mkEnableOption "Enable postgresql replication";
11 Base path to put the replications
14 mainPackage = lib.mkOption {
15 type = lib.types.package;
16 default = pkgs.postgresql;
18 Postgresql package available in shell
21 hosts = lib.mkOption {
26 type = lib.types.attrsOf (lib.types.submodule {
28 package = lib.mkOption {
29 type = lib.types.package;
30 default = pkgs.postgresql;
32 Postgresql package for this host
38 Slot to use for replication
41 connection = lib.mkOption {
44 Connection string to access the psql master
52 config = lib.mkIf cfg.enable {
53 myServices.chatonsProperties.hostings.postgresql-replication = {
54 file.datetime = "2022-08-27T15:00:00";
56 name = "PostgreSQL replication";
57 description = "Replication of PostgreSQL database";
58 website = "db-1.immae.eu";
60 status.description = "OK";
61 registration.load = "OPEN";
62 install.type = "PACKAGE";
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";
73 users.users.postgres = {
75 uid = config.ids.uids.postgres;
77 description = "PostgreSQL server user";
78 home = "/var/lib/postgresql";
79 useDefaultShell = true;
80 extraGroups = [ "keys" ];
82 users.groups.postgres.gid = config.ids.gids.postgres;
83 environment.systemPackages = [ cfg.mainPackage ];
85 secrets.keys = lib.listToAttrs (lib.flatten (lib.mapAttrsToList (name: hcfg: [
86 (lib.nameValuePair "postgresql_replication/${name}/recovery.conf" {
92 primary_conninfo = '${hcfg.connection}?sslmode=require'
93 primary_slot_name = '${hcfg.slot}'
96 (lib.nameValuePair "postgresql_replication/${name}/connection_string" {
100 text = hcfg.connection;
102 (lib.nameValuePair "postgresql_replication/${name}/postgresql.conf" {
105 permissions = "0400";
107 dataDir = "${cfg.base}/${name}/postgresql";
109 listen_addresses = '''
110 unix_socket_directories = '${dataDir}'
111 data_directory = '${dataDir}'
113 max_connections = 300
120 systemCronJobs = lib.flatten (lib.mapAttrsToList (name: hcfg:
122 dataDir = "${cfg.base}/${name}/postgresql";
123 backupDir = "${cfg.base}/${name}/postgresql_backup";
124 backup_script = pkgs.writeScript "backup_psql_${name}" ''
125 #!${pkgs.stdenv.shell}
129 resume_replication() {
130 ${hcfg.package}/bin/psql -h ${dataDir} -c "SELECT pg_wal_replay_resume();" >/dev/null || echo "impossible to resume replication"
133 trap resume_replication EXIT
135 ${hcfg.package}/bin/psql -h ${dataDir} -c "SELECT pg_wal_replay_pause();" >/dev/null || (echo "impossible to pause replication" && false)
137 ${hcfg.package}/bin/pg_dumpall -h ${dataDir} -f ${backupDir}/$(${pkgs.coreutils}/bin/date -Iminutes).sql
139 u = pkgs.callPackage ./utils.nix {};
140 cleanup_script = pkgs.writeScript "cleanup_postgresql_${name}" (u.keepLastNDumps "sql" backupDir 6);
142 "0 22,4,10,16 * * * postgres ${backup_script}"
143 "0 3 * * * postgres ${cleanup_script}"
147 system.activationScripts = lib.attrsets.mapAttrs' (name: hcfg:
148 lib.attrsets.nameValuePair "psql_replication_${name}" {
151 install -m 0700 -o postgres -g postgres -d ${cfg.base}/${name}/postgresql
152 install -m 0700 -o postgres -g postgres -d ${cfg.base}/${name}/postgresql_backup
156 systemd.services = lib.attrsets.mapAttrs' (name: hcfg:
158 dataDir = "${cfg.base}/${name}/postgresql";
160 lib.attrsets.nameValuePair "postgresql_backup_${name}" {
161 description = "Postgresql replication for ${name}";
162 wantedBy = [ "multi-user.target" ];
163 after = [ "network.target" ];
165 environment.PGDATA = dataDir;
166 path = [ hcfg.package ];
169 if ! test -e ${dataDir}/PG_VERSION; then
170 mkdir -m 0700 -p ${dataDir}
171 chown -R postgres:postgres ${dataDir}
175 fp = n: config.secrets.fullPaths."postgresql_replication/${name}/${n}";
177 if ! test -e ${dataDir}/PG_VERSION; then
178 pg_basebackup -d $(cat ${fp "connection_string"}) -D ${dataDir} -S ${hcfg.slot}
180 ln -sfn ${fp "recovery.conf"} ${dataDir}/recovery.conf
181 ln -sfn ${fp "postgresql.conf"} ${dataDir}/postgresql.conf
187 ExecReload = "${pkgs.coreutils}/bin/kill -HUP $MAINPID";
190 PermissionsStartOnly = true;
191 RuntimeDirectory = "postgresql";
194 KillSignal = "SIGINT";
196 # basebackup can take a long time
197 TimeoutStartSec="infinity";
198 TimeoutStopSec = 120;
200 unitConfig.RequiresMountsFor = dataDir;