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}'
119 systemCronJobs = lib.flatten (lib.mapAttrsToList (name: hcfg:
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}
128 resume_replication() {
129 ${hcfg.package}/bin/psql -h ${dataDir} -c "SELECT pg_wal_replay_resume();" >/dev/null || echo "impossible to resume replication"
132 trap resume_replication EXIT
134 ${hcfg.package}/bin/psql -h ${dataDir} -c "SELECT pg_wal_replay_pause();" >/dev/null || (echo "impossible to pause replication" && false)
136 ${hcfg.package}/bin/pg_dumpall -h ${dataDir} -f ${backupDir}/$(${pkgs.coreutils}/bin/date -Iminutes).sql
138 u = pkgs.callPackage ./utils.nix {};
139 cleanup_script = pkgs.writeScript "cleanup_postgresql_${name}" (u.keepLastNDumps "sql" backupDir 6);
141 "0 22,4,10,16 * * * postgres ${backup_script}"
142 "0 3 * * * postgres ${cleanup_script}"
146 system.activationScripts = lib.attrsets.mapAttrs' (name: hcfg:
147 lib.attrsets.nameValuePair "psql_replication_${name}" {
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
155 systemd.services = lib.attrsets.mapAttrs' (name: hcfg:
157 dataDir = "${cfg.base}/${name}/postgresql";
159 lib.attrsets.nameValuePair "postgresql_backup_${name}" {
160 description = "Postgresql replication for ${name}";
161 wantedBy = [ "multi-user.target" ];
162 after = [ "network.target" ];
164 environment.PGDATA = dataDir;
165 path = [ hcfg.package ];
168 if ! test -e ${dataDir}/PG_VERSION; then
169 mkdir -m 0700 -p ${dataDir}
170 chown -R postgres:postgres ${dataDir}
174 fp = n: config.secrets.fullPaths."postgresql_replication/${name}/${n}";
176 if ! test -e ${dataDir}/PG_VERSION; then
177 pg_basebackup -d $(cat ${fp "connection_string"}) -D ${dataDir} -S ${hcfg.slot}
179 ln -sfn ${fp "recovery.conf"} ${dataDir}/recovery.conf
180 ln -sfn ${fp "postgresql.conf"} ${dataDir}/postgresql.conf
186 ExecReload = "${pkgs.coreutils}/bin/kill -HUP $MAINPID";
189 PermissionsStartOnly = true;
190 RuntimeDirectory = "postgresql";
193 KillSignal = "SIGINT";
195 # basebackup can take a long time
196 TimeoutStartSec="infinity";
197 TimeoutStopSec = 120;
199 unitConfig.RequiresMountsFor = dataDir;