1069 lines
45 KiB
Nix
1069 lines
45 KiB
Nix
{
|
|
description = "Postgresql";
|
|
|
|
inputs = {
|
|
nixpkgs = {
|
|
url = "github:NixOS/nixpkgs/nixos-23.11";
|
|
};
|
|
# nixpkgs-pgvectors = {
|
|
# url = "github:diogotcorreia/nixpkgs/pgvecto.rs";
|
|
# };
|
|
flake-utils = {
|
|
url = "github:numtide/flake-utils";
|
|
};
|
|
};
|
|
outputs = {
|
|
self,
|
|
nixpkgs,
|
|
# nixpkgs-pgvectors,
|
|
flake-utils,
|
|
...
|
|
} @ inputs:
|
|
(
|
|
flake-utils.lib.eachDefaultSystem
|
|
(
|
|
system: let
|
|
pkgs = import nixpkgs {
|
|
inherit system;
|
|
};
|
|
in {
|
|
packages = {
|
|
scram-sha-256 = let
|
|
pname = "scram-sha-256";
|
|
version = "1.0.3";
|
|
in
|
|
pkgs.buildGoModule {
|
|
inherit pname version;
|
|
src = pkgs.fetchFromGitHub {
|
|
owner = "supercaracal";
|
|
repo = pname;
|
|
rev = "v${version}";
|
|
hash = "sha256-55dEihU9FnKiGt9jRJsY1+NRUgOkwoLF8J60RRYG7yM=";
|
|
};
|
|
vendorHash = "sha256-HjyD30RFf5vnZ8CNU1s3sTTyCof1yD8cdVWC7cLwjic=";
|
|
postInstall = ''
|
|
mv $out/bin/tool $out/bin/scram-sha-256
|
|
'';
|
|
meta = with pkgs.lib; {
|
|
homepage = "https://github.com/supercaracal/scram-sha-256";
|
|
description = "Password-encryption tool for PostgreSQL with scram-sha-256";
|
|
platforms = platforms.all;
|
|
license = licenses.mit;
|
|
};
|
|
};
|
|
# pgvecto-rs = let
|
|
# pname = "pgvecto.rs";
|
|
# version = "0.1.11";
|
|
# hashes = {
|
|
# "15" = "sha256-IVx/LgRnGyvBRYvrrJatd7yboWEoSYSJogLaH5N/wPA=";
|
|
# };
|
|
# major = pkgs.lib.versions.major pkgs.postgresql_15.version;
|
|
# in
|
|
# pkgs.stdenv.mkDerivation {
|
|
# inherit pname version;
|
|
|
|
# buildInputs = [pkgs.dpkg];
|
|
|
|
# src = pkgs.fetchurl {
|
|
# url = "https://github.com/tensorchord/pgvecto.rs/releases/download/v${version}/vectors-pg${major}-v${version}-x86_64-unknown-linux-gnu.deb";
|
|
# hash = hashes."${major}";
|
|
# };
|
|
|
|
# dontUnpack = true;
|
|
# dontBuild = true;
|
|
# dontStrip = true;
|
|
|
|
# installPhase = ''
|
|
# mkdir -p $out
|
|
# dpkg -x $src $out
|
|
# install -D -t $out/lib $out/usr/lib/postgresql/${major}/lib/*.so
|
|
# install -D -t $out/share/postgresql/extension $out/usr/share/postgresql/${major}/extension/*.sql
|
|
# install -D -t $out/share/postgresql/extension $out/usr/share/postgresql/${major}/extension/*.control
|
|
# rm -rf $out/usr
|
|
# '';
|
|
|
|
# meta = {
|
|
# description = "Scalable Vector database plugin for Postgres, written in Rust, specifically designed for LLM";
|
|
# homepage = "https://github.com/tensorchord/pgvecto.rs";
|
|
# license = pkgs.lib.licenses.asl20;
|
|
# platforms = pkgs.postgresql.meta.platforms;
|
|
# };
|
|
# };
|
|
};
|
|
}
|
|
)
|
|
)
|
|
// {
|
|
nixosModules = {
|
|
postgresql = {
|
|
config,
|
|
lib,
|
|
pkgs,
|
|
pkgs-unstable,
|
|
...
|
|
}: let
|
|
cfg = config.jcollie.postgresql;
|
|
in {
|
|
options = {
|
|
jcollie.postgresql = lib.options.mkOption {
|
|
type = lib.types.submodule {
|
|
options = {
|
|
enable = lib.options.mkEnableOption "PostgreSQL";
|
|
port = lib.options.mkOption {
|
|
type = lib.types.port;
|
|
default = 5432;
|
|
};
|
|
maxConnections = lib.options.mkOption {
|
|
type = lib.types.ints.positive;
|
|
default = 100;
|
|
};
|
|
superuser = lib.options.mkOption {
|
|
type = lib.types.submodule {
|
|
options = {
|
|
username = lib.options.mkOption {
|
|
type = lib.types.str;
|
|
default = "postgres";
|
|
};
|
|
password = lib.options.mkOption {
|
|
type = lib.types.addCheck lib.types.str (x: lib.hasPrefix "SCRAM-SHA-256$" x);
|
|
};
|
|
};
|
|
};
|
|
default = {};
|
|
};
|
|
plugins = lib.options.mkOption {
|
|
type = lib.types.submodule {
|
|
options = {
|
|
pgvecto-rs = lib.options.mkOption {
|
|
type = lib.types.submodule {
|
|
options = {
|
|
enable = lib.options.mkOption {
|
|
description = "Enable pgvecto.rs plugin";
|
|
type = lib.types.bool;
|
|
default = true;
|
|
};
|
|
};
|
|
};
|
|
default = {};
|
|
};
|
|
postgis = lib.options.mkOption {
|
|
type = lib.types.submodule {
|
|
options = {
|
|
enable = lib.options.mkOption {
|
|
description = "Enable postgis plugin";
|
|
type = lib.types.bool;
|
|
default = true;
|
|
};
|
|
};
|
|
};
|
|
default = {};
|
|
};
|
|
};
|
|
};
|
|
default = {};
|
|
};
|
|
replication = lib.options.mkOption {
|
|
type = lib.types.submodule {
|
|
options = {
|
|
enable = lib.options.mkEnableOption "Enable PostgreSQL replication";
|
|
role = lib.options.mkOption {
|
|
type = lib.types.enum [
|
|
"primary"
|
|
"replica"
|
|
];
|
|
default = "primary";
|
|
};
|
|
username = lib.options.mkOption {
|
|
type = lib.types.str;
|
|
default = "replication";
|
|
};
|
|
passwordFile = lib.options.mkOption {
|
|
type = lib.types.path;
|
|
};
|
|
primary = lib.options.mkOption {
|
|
type = lib.types.submodule {
|
|
options = {
|
|
hostname = lib.options.mkOption {
|
|
type = lib.types.str;
|
|
};
|
|
port = lib.options.mkOption {
|
|
type = lib.types.port;
|
|
default = 5432;
|
|
};
|
|
};
|
|
};
|
|
default = {};
|
|
};
|
|
};
|
|
};
|
|
};
|
|
backup = lib.options.mkOption {
|
|
type = lib.types.submodule {
|
|
options = {
|
|
enable = lib.options.mkEnableOption "Backup the database with PGBackRest";
|
|
processMax = lib.options.mkOption {
|
|
type = lib.types.ints.positive;
|
|
default = 4;
|
|
};
|
|
log = lib.options.mkOption {
|
|
type = lib.types.submodule {
|
|
options = let
|
|
logLevelType = lib.types.enum [
|
|
"off"
|
|
"error"
|
|
"warn"
|
|
"info"
|
|
"detail"
|
|
"debug"
|
|
"trace"
|
|
];
|
|
in {
|
|
console = lib.options.mkOption {
|
|
type = logLevelType;
|
|
default = "detail";
|
|
};
|
|
file = lib.options.mkOption {
|
|
type = logLevelType;
|
|
default = "off";
|
|
};
|
|
stderr = lib.options.mkOption {
|
|
type = logLevelType;
|
|
default = "off";
|
|
};
|
|
};
|
|
};
|
|
default = {};
|
|
};
|
|
cipher = lib.options.mkOption {
|
|
type = lib.types.submodule {
|
|
options = {
|
|
passwordFile = lib.options.mkOption {
|
|
type = lib.types.path;
|
|
description = "Path to file containing encryption password.";
|
|
};
|
|
type = lib.options.mkOption {
|
|
type = lib.types.enum [
|
|
"aes-256-cbc"
|
|
];
|
|
default = "aes-256-cbc";
|
|
description = "Type of encryption to use.";
|
|
};
|
|
};
|
|
};
|
|
default = {};
|
|
};
|
|
storage = lib.options.mkOption {
|
|
type = lib.types.submodule {
|
|
options = {
|
|
type = lib.options.mkOption {
|
|
type = lib.types.enum [
|
|
"azure"
|
|
"b2"
|
|
];
|
|
description = "The type of storage used for backups.";
|
|
};
|
|
azure = lib.options.mkOption {
|
|
type = lib.types.submodule {
|
|
options = {
|
|
accountName = lib.options.mkOption {
|
|
type = lib.types.str;
|
|
};
|
|
accountKeyFile = lib.options.mkOption {
|
|
type = lib.types.path;
|
|
};
|
|
container = lib.options.mkOption {
|
|
type = lib.types.str;
|
|
};
|
|
path = lib.options.mkOption {
|
|
type = lib.types.str;
|
|
};
|
|
};
|
|
};
|
|
default = {};
|
|
description = "Options for connecting to Azure Blob storage";
|
|
};
|
|
b2 = lib.options.mkOption {
|
|
type = lib.types.submodule {
|
|
options = {
|
|
accountId = lib.options.mkOption {
|
|
type = lib.types.str;
|
|
};
|
|
accountKeyFile = lib.options.mkOption {
|
|
type = lib.types.path;
|
|
};
|
|
endpoint = lib.options.mkOption {
|
|
type = lib.types.str;
|
|
};
|
|
region = lib.options.mkOption {
|
|
type = lib.types.str;
|
|
};
|
|
bucket = lib.options.mkOption {
|
|
type = lib.types.str;
|
|
};
|
|
path = lib.options.mkOption {
|
|
type = lib.types.str;
|
|
};
|
|
};
|
|
};
|
|
default = {};
|
|
description = "Options for connection to BackBlaze B2";
|
|
};
|
|
};
|
|
};
|
|
default = {};
|
|
};
|
|
healthcheck = lib.options.mkOption {
|
|
type = lib.types.submodule {
|
|
options = {
|
|
enable = lib.options.mkEnableOption "use healthchecks";
|
|
fullBackupPingURL = lib.options.mkOption {
|
|
type = lib.types.str;
|
|
};
|
|
differentialBackupPingURL = lib.options.mkOption {
|
|
type = lib.types.str;
|
|
};
|
|
incrementalBackupPingURL = lib.options.mkOption {
|
|
type = lib.types.str;
|
|
};
|
|
};
|
|
};
|
|
default = {};
|
|
};
|
|
};
|
|
};
|
|
default = {};
|
|
};
|
|
|
|
users = lib.options.mkOption {
|
|
type = lib.types.listOf (
|
|
lib.types.submodule {
|
|
options = {
|
|
username = lib.options.mkOption {
|
|
type = lib.types.str;
|
|
};
|
|
password = lib.options.mkOption {
|
|
type = lib.types.addCheck lib.types.str (x: lib.hasPrefix "SCRAM-SHA-256$" x);
|
|
description = "Encrypted password for the database account.";
|
|
};
|
|
};
|
|
}
|
|
);
|
|
default = [];
|
|
};
|
|
|
|
databases = lib.options.mkOption {
|
|
type = lib.types.listOf (
|
|
lib.types.submodule {
|
|
options = {
|
|
name = lib.options.mkOption {
|
|
type = lib.types.str;
|
|
};
|
|
owner = lib.options.mkOption {
|
|
type = lib.types.str;
|
|
};
|
|
extensions = lib.options.mkOption {
|
|
type = lib.types.listOf lib.types.str;
|
|
default = [];
|
|
};
|
|
template = lib.options.mkOption {
|
|
type = lib.types.enum ["template0" "template1"];
|
|
default = "template1";
|
|
};
|
|
encoding = lib.options.mkOption {
|
|
type = lib.types.str;
|
|
default = "UTF8";
|
|
};
|
|
lc_collate = lib.options.mkOption {
|
|
type = lib.types.str;
|
|
default = "en_US.utf8";
|
|
};
|
|
lc_ctype = lib.options.mkOption {
|
|
type = lib.types.str;
|
|
default = "en_US.utf8";
|
|
};
|
|
};
|
|
}
|
|
);
|
|
default = [];
|
|
};
|
|
};
|
|
};
|
|
default = {};
|
|
};
|
|
};
|
|
|
|
config = let
|
|
toStr = value:
|
|
if true == value
|
|
then "yes"
|
|
else if false == value
|
|
then "no"
|
|
else if lib.isString value
|
|
then "'${lib.replaceStrings ["'"] ["''"] value}'"
|
|
else toString value;
|
|
|
|
escapeShell = value: lib.replaceStrings ["$"] ["\\$"] value;
|
|
|
|
postgresql = pkgs-unstable.postgresql_15.withPackages (
|
|
plugins:
|
|
(
|
|
lib.optionals cfg.plugins.postgis.enable [
|
|
plugins.postgis
|
|
]
|
|
)
|
|
++ (
|
|
lib.optionals cfg.plugins.pgvecto-rs.enable [
|
|
plugins.pgvecto-rs
|
|
# nixpkgs-pgvectors.legacyPackages.${pkgs.system}.postgresqlPackages.pgvecto-rs
|
|
# self.packages.${pkgs.system}.pgvecto-rs
|
|
]
|
|
)
|
|
);
|
|
|
|
# rcloneConfig = pkgs.writeTextFile {
|
|
# name = "rclone.conf";
|
|
# text = {
|
|
# "b2" = ''
|
|
# [b2]
|
|
# type = b2
|
|
# account = ${cfg.backup.storage.b2.accountId}
|
|
# key = ${cfg.backup.storage.b2.accountKey}
|
|
# '';
|
|
# "azure" = ''
|
|
# [azure]
|
|
# type = azureblob
|
|
# account = ${cfg.backup.storage.azure.accountName}
|
|
# key = ${cfg.backup.storage.azure.accountKey}
|
|
# '';
|
|
# }.${cfg.backup.storage.type};
|
|
# };
|
|
|
|
rcloneEnvironment =
|
|
{
|
|
RCLONE_CONFIG = "/dev/null";
|
|
}
|
|
// (
|
|
{
|
|
"azure" = {
|
|
RCLONE_CONFIG_AZURE_TYPE = "azureblob";
|
|
RCLONE_CONFIG_AZURE_ACCOUNT = cfg.backup.storage.azure.accountName;
|
|
};
|
|
"b2" = {
|
|
RCLONE_CONFIG_B2_TYPE = "b2";
|
|
RCLONE_CONFIG_B2_ACCOUNT = cfg.backup.storage.b2.accountId;
|
|
};
|
|
}
|
|
.${cfg.backup.storage.type}
|
|
);
|
|
rcloneEnvironmentFiles =
|
|
{
|
|
"azure" = {
|
|
RCLONE_CONFIG_AZURE_KEY = cfg.backup.storage.azure.accountKeyFile;
|
|
};
|
|
"b2" = {
|
|
RCLONE_CONFIG_B2_KEY = cfg.backup.storage.b2.accountKeyFile;
|
|
};
|
|
}
|
|
.${cfg.backup.storage.type};
|
|
|
|
rclone = lib.mkIf (cfg.backup.enable) (
|
|
let
|
|
environment = lib.concatStringsSep "\n" (
|
|
lib.mapAttrsToList
|
|
(
|
|
n: v: ''export ${n}="${builtins.toString v}"''
|
|
)
|
|
rcloneEnvironment
|
|
);
|
|
environmentFiles = lib.concatStringsSep "\n" (
|
|
lib.mapAttrsToList
|
|
(
|
|
n: v: ''export ${n}=''$(<${v})''
|
|
)
|
|
rcloneEnvironmentFiles
|
|
);
|
|
in
|
|
pkgs.writeShellScriptBin "rclone" ''
|
|
${environment}
|
|
${environmentFiles}
|
|
exec ${pkgs.rclone}/bin/rclone "''$@"
|
|
''
|
|
);
|
|
|
|
rootDir = "/var/lib/postgresql";
|
|
dataDir = "${rootDir}/15";
|
|
|
|
pgbackrestEnvironment =
|
|
{
|
|
PGBACKREST_LOG_LEVEL_CONSOLE = cfg.backup.log.console;
|
|
PGBACKREST_LOG_LEVEL_FILE = cfg.backup.log.file;
|
|
PGBACKREST_LOG_LEVEL_STDERR = cfg.backup.log.stderr;
|
|
PGBACKREST_PG1_PATH = dataDir;
|
|
PGBACKREST_PROCESS_MAX = "${builtins.toString cfg.backup.processMax}";
|
|
PGBACKREST_REPO1_CIPHER_TYPE = cfg.backup.cipher.type;
|
|
PGBACKREST_REPO1_RETENTION_FULL = "14";
|
|
PGBACKREST_REPO1_RETENTION_FULL_TYPE = "time";
|
|
PGBACKREST_STANZA = "${config.networking.hostName}.${config.networking.domain}";
|
|
}
|
|
// (
|
|
{
|
|
"azure" = {
|
|
PGBACKREST_REPO1_AZURE_ACCOUNT = cfg.backup.storage.azure.accountName;
|
|
PGBACKREST_REPO1_AZURE_CONTAINER = cfg.backup.storage.azure.container;
|
|
PGBACKREST_REPO1_PATH = cfg.backup.storage.azure.path;
|
|
PGBACKREST_REPO1_TYPE = "azure";
|
|
};
|
|
"b2" = {
|
|
PGBACKREST_REPO1_PATH = cfg.backup.storage.b2.path;
|
|
PGBACKREST_REPO1_S3_BUCKET = cfg.backup.storage.b2.bucket;
|
|
PGBACKREST_REPO1_S3_ENDPOINT = cfg.backup.storage.b2.endpoint;
|
|
PGBACKREST_REPO1_S3_KEY = cfg.backup.storage.b2.accountId;
|
|
PGBACKREST_REPO1_S3_REGION = cfg.backup.storage.b2.region;
|
|
PGBACKREST_REPO1_TYPE = "s3";
|
|
};
|
|
}
|
|
.${cfg.backup.storage.type}
|
|
);
|
|
|
|
pgbackrestEnvironmentFiles =
|
|
{
|
|
PGBACKREST_REPO1_CIPHER_PASS = cfg.backup.cipher.passwordFile;
|
|
}
|
|
// {
|
|
"azure" = {
|
|
PGBACKREST_REPO1_AZURE_KEY = cfg.backup.storage.azure.accountKeyFile;
|
|
};
|
|
"b2" = {
|
|
PGBACKREST_REPO1_S3_KEY_SECRET = cfg.backup.storage.b2.accountKeyFile;
|
|
};
|
|
}
|
|
.${cfg.backup.storage.type};
|
|
|
|
pgbackrest =
|
|
if (cfg.backup.enable)
|
|
then
|
|
(
|
|
let
|
|
environment = lib.concatStringsSep "\n" (
|
|
lib.mapAttrsToList
|
|
(
|
|
n: v: ''export ${n}="${builtins.toString v}"''
|
|
)
|
|
pgbackrestEnvironment
|
|
);
|
|
environmentFiles = lib.concatStringsSep "\n" (
|
|
lib.mapAttrsToList
|
|
(
|
|
n: v: ''export ${n}=''$(<${v})''
|
|
)
|
|
pgbackrestEnvironmentFiles
|
|
);
|
|
in
|
|
pkgs.writeShellScriptBin "pgbackrest" ''
|
|
${environment}
|
|
${environmentFiles}
|
|
exec ${pkgs.pgbackrest}/bin/pgbackrest "''$@"
|
|
''
|
|
)
|
|
else {};
|
|
# pgbackrestnu = lib.mkIf (cfg.backup.enable) (
|
|
# let
|
|
# environment = writeText "pgbackrest-environment.json" (builtins.toJSON pgbackrestEnvironment);
|
|
# in
|
|
# pkgs.writeScriptBin "pgbackrest" ''
|
|
# #!${pkgs.nushell}/bin/nu
|
|
|
|
# def main [...args: string] {
|
|
# load-env (open ${data})
|
|
|
|
# exec ${pkgs.pgbackrest}/bin/pgbackrest $args
|
|
# }
|
|
# ''
|
|
# );
|
|
curl = "${pkgs.curl}/bin/curl --silent --show-error --max-time 10 --retry 5";
|
|
in
|
|
lib.mkIf cfg.enable {
|
|
assertions = [
|
|
# {
|
|
# assertion = !(cfg.backup.storage.b2.accountKey != null && cfg.backup.storage.b2.accountKeyFile != null);
|
|
# message = "cannot set both accountKey and accountKeyFile on B2 storage";
|
|
# }
|
|
];
|
|
|
|
environment.systemPackages =
|
|
[
|
|
postgresql
|
|
self.packages.${pkgs.system}.scram-sha-256
|
|
]
|
|
++ (
|
|
if cfg.backup.enable && (!cfg.replication.enable || cfg.replication.role == "primary")
|
|
then [
|
|
pgbackrest
|
|
rclone
|
|
]
|
|
else []
|
|
);
|
|
|
|
# networking.firewall.allowedTCPPorts = [ cfg.port ];
|
|
|
|
security.acme.certs."${config.networking.hostName}.${config.networking.domain}" = {
|
|
reloadServices = [
|
|
"postgresql"
|
|
];
|
|
};
|
|
|
|
users.groups.postgres.gid = config.ids.gids.postgres;
|
|
users.users.postgres = {
|
|
name = "postgres";
|
|
uid = config.ids.uids.postgres;
|
|
group = "postgres";
|
|
description = "PostgreSQL Server";
|
|
home = dataDir;
|
|
useDefaultShell = true;
|
|
};
|
|
|
|
systemd.tmpfiles.rules = [
|
|
"d ${rootDir} 0750 postgres postgres -"
|
|
"d ${dataDir} 0700 postgres postgres -"
|
|
];
|
|
|
|
systemd.services.postgresql = let
|
|
hbaFile = pkgs.writeTextDir "pg_hba.conf" ''
|
|
local all all ident map=default
|
|
hostnossl all all all reject
|
|
hostssl all all all scram-sha-256
|
|
local replication all ident map=default
|
|
hostnossl replication all all reject
|
|
hostssl replication all all scram-sha-256
|
|
'';
|
|
identFile = pkgs.writeTextDir "pg_ident.conf" (
|
|
''
|
|
default root postgres
|
|
default postgres postgres
|
|
''
|
|
+ (
|
|
lib.strings.concatStringsSep "\n"
|
|
(
|
|
map
|
|
(user: "default root ${user.username}")
|
|
cfg.users
|
|
)
|
|
)
|
|
+ "\n"
|
|
);
|
|
archiveCommand = "${pgbackrest}/bin/pgbackrest archive-push %p";
|
|
settings =
|
|
{
|
|
bgwriter_flush_after = "512kB";
|
|
checkpoint_flush_after = "256kB";
|
|
data_directory = dataDir;
|
|
datestyle = "iso, mdy";
|
|
default_text_search_config = "pg_catalog.english";
|
|
dynamic_shared_memory_type = "posix";
|
|
hba_file = "${dataDir}/pg_hba.conf";
|
|
hot_standby = "on";
|
|
ident_file = "${dataDir}/pg_ident.conf";
|
|
lc_messages = "en_US.utf8";
|
|
lc_monetary = "en_US.utf8";
|
|
lc_numeric = "en_US.utf8";
|
|
lc_time = "en_US.utf8";
|
|
listen_addresses = "*";
|
|
log_destination = "stderr";
|
|
log_connections = "on";
|
|
log_line_prefix = "%m [%l][%p] {%h} :%d:%u:%c: ";
|
|
# log_statement = "all";
|
|
log_timezone = "America/Chicago";
|
|
logging_collector = "off";
|
|
max_connections = cfg.maxConnections;
|
|
max_wal_senders = 3;
|
|
max_wal_size = "1GB";
|
|
min_wal_size = "80MB";
|
|
password_encryption = "scram-sha-256";
|
|
port = cfg.port;
|
|
shared_buffers = "128MB";
|
|
shared_preload_libraries = lib.strings.concatStringsSep "," (lib.optionals cfg.plugins.pgvecto-rs.enable ["vectors.so"]);
|
|
ssl = "on";
|
|
ssl_cert_file = "/run/credentials/postgresql.service/fullchain.pem";
|
|
ssl_key_file = "/run/credentials/postgresql.service/key.pem";
|
|
timezone = "America/Chicago";
|
|
unix_socket_directories = "/run/postgresql";
|
|
wal_level = "replica";
|
|
wal_log_hints = "on";
|
|
}
|
|
// (
|
|
if (cfg.backup.enable)
|
|
then {
|
|
archive_command = archiveCommand;
|
|
archive_mode = "on";
|
|
}
|
|
else {}
|
|
);
|
|
configFile = pkgs.writeTextDir "postgresql.conf" (
|
|
lib.concatStrings (
|
|
lib.mapAttrsToList
|
|
(
|
|
n: v: "${n} = ${toStr v}\n"
|
|
)
|
|
settings
|
|
)
|
|
);
|
|
backupSetup =
|
|
if (cfg.backup.enable)
|
|
then ''
|
|
init=''$(${pgbackrest}/bin/pgbackrest info --output=json | ${pkgs.jq}/bin/jq '.[0].status.code == 0')
|
|
if [ "$init" != "true" ]
|
|
then
|
|
${postgresql}/bin/pg_ctl -o "-c ssl=off -c listen_addresses=''' -p 55432" -w start
|
|
${pgbackrest}/bin/pgbackrest --pg1-port=55432 stanza-create
|
|
${postgresql}/bin/pg_ctl -m fast -w stop
|
|
fi
|
|
''
|
|
else "";
|
|
pgpass =
|
|
if (cfg.replication.enable && cfg.replication.role == "replica")
|
|
then
|
|
lib.concatStringsSep ":" [
|
|
cfg.replication.primary.hostname
|
|
(builtins.toString cfg.replication.primary.port)
|
|
"replication"
|
|
cfg.replication.username
|
|
''''$(<${cfg.replication.passwordFile})''
|
|
]
|
|
else "";
|
|
in {
|
|
description = "PostgreSQL Server";
|
|
wantedBy = ["multi-user.target"];
|
|
after = ["network.target"];
|
|
environment =
|
|
{
|
|
PGDATA = dataDir;
|
|
}
|
|
// (
|
|
if (cfg.replication.enable && cfg.replication.role == "replica")
|
|
then {
|
|
PGPASSFILE = "${rootDir}/.pgpass";
|
|
}
|
|
else {}
|
|
);
|
|
path =
|
|
[
|
|
postgresql
|
|
]
|
|
++ (
|
|
if cfg.backup.enable && (!cfg.replication.enable || cfg.replication.role == "primary")
|
|
then [
|
|
pgbackrest
|
|
]
|
|
else []
|
|
);
|
|
|
|
preStart =
|
|
if (!cfg.replication.enable || cfg.replication.role == "primary")
|
|
then ''
|
|
if [ ! -s "${dataDir}/PG_VERSION" ]
|
|
then
|
|
${postgresql}/bin/initdb \
|
|
--username=${cfg.superuser.username} \
|
|
--pwfile=<(echo -n "${escapeShell cfg.superuser.password}") \
|
|
--data-checksums \
|
|
--locale=en_US.utf8 \
|
|
--auth-host=scram-sha-256 \
|
|
--auth-local=trust
|
|
fi
|
|
|
|
ln -sfn "${configFile}/postgresql.conf" "${dataDir}/postgresql.conf"
|
|
ln -sfn "${hbaFile}/pg_hba.conf" "${dataDir}/pg_hba.conf"
|
|
ln -sfn "${identFile}/pg_ident.conf" "${dataDir}/pg_ident.conf"
|
|
|
|
${backupSetup}
|
|
''
|
|
else ''
|
|
(umask 077; echo "${pgpass}" > ${rootDir}/.pgpass)
|
|
chmod 0600 ${rootDir}/.pgpass
|
|
|
|
if [ ! -s "${dataDir}/PG_VERSION" ]
|
|
then
|
|
${postgresql}/bin/pg_basebackup \
|
|
--verbose \
|
|
--progress \
|
|
--pgdata=${dataDir} \
|
|
--write-recovery-conf \
|
|
--slot=${config.networking.hostName} \
|
|
--create-slot \
|
|
--wal-method=stream \
|
|
--dbname=postgresql://${cfg.replication.username}@${cfg.replication.primary.hostname}:${builtins.toString cfg.replication.primary.port}/postgresql?sslmode=require \
|
|
--no-password
|
|
fi
|
|
|
|
ln -sfn "${configFile}/postgresql.conf" "${dataDir}/postgresql.conf"
|
|
ln -sfn "${hbaFile}/pg_hba.conf" "${dataDir}/pg_hba.conf"
|
|
ln -sfn "${identFile}/pg_ident.conf" "${dataDir}/pg_ident.conf"
|
|
'';
|
|
|
|
unitConfig = {
|
|
RequiresMountsFor = "${dataDir}";
|
|
};
|
|
serviceConfig = {
|
|
ExecReload = "${pkgs.coreutils}/bin/kill -HUP $MAINPID";
|
|
ExecStart = "${postgresql}/bin/postgres";
|
|
Group = "postgres";
|
|
KillMode = "mixed";
|
|
KillSignal = "SIGINT";
|
|
RuntimeDirectory = "postgresql";
|
|
StateDirectory = "postgresql";
|
|
StateDirectoryMode = "0750";
|
|
TimeoutSec = 300;
|
|
Type = "notify";
|
|
User = "postgres";
|
|
LoadCredential = [
|
|
"fullchain.pem:${config.security.acme.certs."${config.networking.hostName}.${config.networking.domain}".directory}/fullchain.pem"
|
|
"key.pem:${config.security.acme.certs."${config.networking.hostName}.${config.networking.domain}".directory}/key.pem"
|
|
];
|
|
};
|
|
};
|
|
|
|
systemd.services.postgresql-setup = lib.mkIf (!cfg.replication.enable || cfg.replication.role == "primary") (
|
|
let
|
|
replicationSetup =
|
|
if (cfg.replication.enable && cfg.replication.role == "primary")
|
|
then ''
|
|
if $PSQL --command "SELECT 1 FROM pg_roles WHERE rolname='${cfg.replication.username}';" | grep -q 1
|
|
then
|
|
echo "alter replication user ${cfg.replication.username}"
|
|
echo "ALTER ROLE ${cfg.replication.username} WITH REPLICATION LOGIN PASSWORD :'v1';" | $PSQL \
|
|
--variable=v1="''$(<''${CREDENTIALS_DIRECTORY}/postgresql-replication-password)"
|
|
else
|
|
echo "create replication user ${cfg.replication.username}"
|
|
echo "CREATE ROLE ${cfg.replication.username} WITH REPLICATION LOGIN PASSWORD :'v1';" | $PSQL \
|
|
--variable=v1="''$(<''${CREDENTIALS_DIRECTORY}/postgresql-replication-password)"
|
|
fi
|
|
''
|
|
else "";
|
|
userSetup =
|
|
lib.strings.concatStringsSep "\n"
|
|
(
|
|
map
|
|
(
|
|
user: ''
|
|
if $PSQL --command "SELECT 1 FROM pg_roles WHERE rolname='${user.username}';" | grep -q 1
|
|
then
|
|
echo "alter user ${user.username}"
|
|
echo "ALTER ROLE :username WITH LOGIN PASSWORD :'password';" | $PSQL --variable username="${user.username}" --variable password="${escapeShell user.password}"
|
|
else
|
|
echo "create user ${user.username}"
|
|
echo "CREATE ROLE :username WITH LOGIN PASSWORD :'password';" | $PSQL --variable username="${user.username}" --variable password="${escapeShell user.password}"
|
|
fi
|
|
''
|
|
)
|
|
cfg.users
|
|
);
|
|
nuShellDatabaseSetup = ''
|
|
|
|
'';
|
|
databaseSetup = lib.strings.concatStringsSep "\n" (
|
|
map
|
|
(
|
|
database:
|
|
''
|
|
if ! ( echo "SELECT 1 FROM pg_database WHERE datname=:'name';" | $PSQL --variable name="${database.name}" | grep -q 1 )
|
|
then
|
|
echo "create database ${database.name}"
|
|
echo "CREATE DATABASE :name WITH OWNER = :'owner' TEMPLATE = :'template' ENCODING = :'encoding' LC_COLLATE = :'lc_collate' LC_CTYPE = :'lc_ctype';" | $PSQL --variable name="${database.name}" --variable owner="${database.owner}" --variable encoding="${database.encoding}" --variable lc_collate="${database.lc_collate}" --variable lc_ctype="${database.lc_ctype}" --variable template="${database.template}"
|
|
fi
|
|
|
|
echo "grant public schema priviliges to user ${database.owner}"
|
|
echo "GRANT ALL PRIVILEGES ON SCHEMA public TO :owner;" | $PSQL --dbname "${database.name}" --variable name="${database.name}" --variable owner="${database.owner}"
|
|
echo "grant priviliges on database ${database.name} to user ${database.owner}"
|
|
echo "GRANT ALL PRIVILEGES ON DATABASE :name TO :owner;" | $PSQL --dbname "${database.name}" --variable name="${database.name}" --variable owner="${database.owner}"
|
|
''
|
|
+ (
|
|
lib.strings.concatStringsSep "\n" (
|
|
map
|
|
(
|
|
extension: ''
|
|
if ! ( $PSQL --dbname ${database.name} --command "SELECT 1 FROM pg_extension WHERE extname='${extension}';" | grep -q 1 )
|
|
then
|
|
echo "adding extention ${extension} to ${database.name}"
|
|
$PSQL --dbname ${database.name} --command "CREATE EXTENSION ${extension};"
|
|
fi
|
|
''
|
|
)
|
|
database.extensions
|
|
)
|
|
)
|
|
)
|
|
cfg.databases
|
|
);
|
|
in {
|
|
description = "PostgreSQL User/Database Setup";
|
|
after = ["postgresql.service"];
|
|
# bindsTo = [ "postgresql.service" ];
|
|
script = ''
|
|
while ! ${postgresql}/bin/psql -d postgres -c "" 2> /dev/null
|
|
do
|
|
sleep 0.1
|
|
done
|
|
|
|
PSQL="${postgresql}/bin/psql --tuples-only --no-align"
|
|
|
|
$PSQL --command "ALTER ROLE ${cfg.superuser.username} WITH SUPERUSER LOGIN PASSWORD '${escapeShell cfg.superuser.password}';"
|
|
|
|
${replicationSetup}
|
|
${userSetup}
|
|
${databaseSetup}
|
|
'';
|
|
serviceConfig = {
|
|
Type = "oneshot";
|
|
User = "postgres";
|
|
Group = "postgres";
|
|
RemainAfterExit = true;
|
|
TimeoutSec = 120;
|
|
LoadCredential = [
|
|
"postgresql-replication-password:${cfg.replication.passwordFile}"
|
|
];
|
|
};
|
|
}
|
|
);
|
|
|
|
systemd.services.postgresql-backup-full = lib.mkIf (cfg.backup.enable && (!cfg.replication.enable || cfg.replication.role == "primary")) (
|
|
let
|
|
hcStart =
|
|
if cfg.backup.healthcheck.enable
|
|
then "${curl} ${cfg.backup.healthcheck.fullBackupPingURL}/start"
|
|
else "";
|
|
hcStop =
|
|
if cfg.backup.healthcheck.enable
|
|
then "${curl} ${cfg.backup.healthcheck.fullBackupPingURL}"
|
|
else "";
|
|
in {
|
|
description = "PostgreSQL Full Backup";
|
|
requires = ["postgresql.service"];
|
|
script = ''
|
|
${hcStart}
|
|
|
|
while ! ${postgresql}/bin/psql -d postgres -c "" 2> /dev/null
|
|
do
|
|
sleep 0.1
|
|
done
|
|
|
|
${pgbackrest}/bin/pgbackrest --type=full --start-fast --stop-auto --delta backup
|
|
|
|
${hcStop}
|
|
'';
|
|
environment = pgbackrestEnvironment;
|
|
serviceConfig = {
|
|
Type = "oneshot";
|
|
User = "postgres";
|
|
Group = "postgres";
|
|
TimeoutSec = "4h";
|
|
};
|
|
}
|
|
);
|
|
|
|
systemd.timers.postgresql-backup-full = lib.mkIf (cfg.backup.enable && (!cfg.replication.enable || cfg.replication.role == "primary")) {
|
|
description = "PostgreSQL Full Backup";
|
|
timerConfig = {
|
|
OnCalendar = "Sun *-*-* 02:00:00";
|
|
RandomizedDelaySec = "5m";
|
|
};
|
|
wantedBy = ["multi-user.target"];
|
|
};
|
|
|
|
systemd.services.postgresql-backup-diff = lib.mkIf (cfg.backup.enable && (!cfg.replication.enable || cfg.replication.role == "primary")) (
|
|
let
|
|
hcStart =
|
|
if cfg.backup.healthcheck.enable
|
|
then "${curl} ${cfg.backup.healthcheck.differentialBackupPingURL}/start"
|
|
else "";
|
|
hcStop =
|
|
if cfg.backup.healthcheck.enable
|
|
then "${curl} ${cfg.backup.healthcheck.differentialBackupPingURL}"
|
|
else "";
|
|
in {
|
|
description = "PostgreSQL Differential Backup";
|
|
requires = ["postgresql.service"];
|
|
script = ''
|
|
${hcStart}
|
|
|
|
while ! ${postgresql}/bin/psql -d postgres -c "" 2> /dev/null
|
|
do
|
|
sleep 0.1
|
|
done
|
|
|
|
${pgbackrest}/bin/pgbackrest --type=diff --start-fast --stop-auto --delta backup
|
|
|
|
${hcStop}
|
|
'';
|
|
environment = pgbackrestEnvironment;
|
|
serviceConfig = {
|
|
Type = "oneshot";
|
|
User = "postgres";
|
|
Group = "postgres";
|
|
TimeoutSec = 3600;
|
|
};
|
|
}
|
|
);
|
|
|
|
systemd.timers.postgresql-backup-diff = lib.mkIf (cfg.backup.enable && (!cfg.replication.enable || cfg.replication.role == "primary")) {
|
|
description = "PostgreSQL Differential Backup";
|
|
timerConfig = {
|
|
OnCalendar = "Mon,Tue,Wed,Thu,Fri,Sat *-*-* 02:00:00";
|
|
RandomizedDelaySec = "5m";
|
|
};
|
|
wantedBy = ["multi-user.target"];
|
|
};
|
|
|
|
systemd.services.postgresql-backup-incr = lib.mkIf (cfg.backup.enable && (!cfg.replication.enable || cfg.replication.role == "primary")) (
|
|
let
|
|
hcStart =
|
|
if cfg.backup.healthcheck.enable
|
|
then "${curl} ${cfg.backup.healthcheck.incrementalBackupPingURL}/start"
|
|
else "";
|
|
hcStop =
|
|
if cfg.backup.healthcheck.enable
|
|
then "${curl} ${cfg.backup.healthcheck.incrementalBackupPingURL}"
|
|
else "";
|
|
in {
|
|
description = "PostgreSQL Incremental Backup";
|
|
requires = ["postgresql.service"];
|
|
script = ''
|
|
${hcStart}
|
|
|
|
while ! ${postgresql}/bin/psql -d postgres -c "" 2> /dev/null
|
|
do
|
|
sleep 0.1
|
|
done
|
|
|
|
${pgbackrest}/bin/pgbackrest --type=incr --start-fast --stop-auto --delta backup
|
|
|
|
${hcStop}
|
|
'';
|
|
environment = pgbackrestEnvironment;
|
|
serviceConfig = {
|
|
Type = "oneshot";
|
|
User = "postgres";
|
|
Group = "postgres";
|
|
TimeoutSec = 3600;
|
|
};
|
|
}
|
|
);
|
|
|
|
systemd.timers.postgresql-backup-incr = lib.mkIf (cfg.backup.enable && (!cfg.replication.enable || cfg.replication.role == "primary")) {
|
|
description = "PostgreSQL Incremental Backup";
|
|
timerConfig = {
|
|
OnCalendar = "*-*-* 00,06,10,14,18,22:00:00";
|
|
RandomizedDelaySec = "5m";
|
|
};
|
|
wantedBy = ["multi-user.target"];
|
|
};
|
|
|
|
services.prometheus.exporters.postgres = {
|
|
enable = true;
|
|
runAsLocalSuperUser = true;
|
|
};
|
|
};
|
|
};
|
|
};
|
|
};
|
|
}
|