1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
|
{ lib, pkgs, ... }:
{
imports = [ ];
services.postgresql = {
enable = true;
# At the time of this writing, nixpkgs defaults to postgresql_17, which
# also happens to be the current upstream version. In general, it's fairly
# typical for the stable version of NixOS to lag one major version behind
# upstream.
#
# Specifying this explicitly rather than leaving it at the default does
# also mean that we won't automatically get new major versions as nixpkgs
# rolls them out; that's important for stability, because databases are
# only visible to the version that created them, and need to be migrated
# when there's a new one. At some point, we'll probably need to come up
# with some form of automation to relieve our users from having to
# understand the operational considerations.
package = pkgs.postgresql_17;
# This is the default, but we specify it explicitly so it doesn't become
# a point of confusion.
enableTCPIP = false;
# The mkForce here is so that we can be more restrictive than the nixpkgs
# defaults, which would otherwise be appended to anything specified below.
#
# The use of "peer" as the auth method on the first line means that that
# line only allows logging in as the database user with the same name as
# your Unix user. This allows us to avoid managing passwords for
# individual services.
#
# There are no rows here matching TCP/IP connections, which means it is
# not possible to log in via TCP/IP, even were TCP otherwise enabled.
# This is intentional.
authentication = lib.mkForce ''
local all all peer
'';
# This only ever matters at the very beginning, when PostgreSQL is first
# installed, but we specify it here for the sake of documenting how it was
# done.
initdbArgs = [
# ICU will get us better internationalization defaults than libc will.
# In particular, it makes sure that the default encoding is UTF8, and
# that everything else is set up to work well with that.
"--locale-provider=icu"
"--icu-locale=en"
# It would be possible to configure locale settings in more detail, but
# the vast majority of that stuff can also be specified under the
# "settings" option, which is preferable because, unlike specifying it
# here, changes to it later will actually do something.
];
# Settings configured here will apply to the entire PostgreSQL server and
# all databases within it. There is a whole other family of settings
# which are done inside the database, and managed as mutable state. We
# will preferentially put things here rather than doing it as mutable
# state, whenever possible. It is normal and expected that there are many
# cases where it's not possible; that's the nature of databases.
settings = {
# Nothing here yet, just a placeholder so it's easy to find when we
# need it. :)
};
};
services.postgresqlBackup = {
enable = true;
# Daily backups. Putting this in the middle of the night should hopefully
# avoid it happening in the middle of anyone's manual changes.
startAt = "02:15:00";
# Some services have sizable databases that we don't necessarily want to
# back up, so we turn off the default behavior of backing up everything,
# and instead require all services to explicitly add themselves to
# `services.postgresqlBackup.databases`.
backupAll = false;
compression = "gzip";
compressionLevel = 9;
};
}
|