commit e0a155d9c571ed990f056a3e2074253301b7292c
parent 9f144556c60f0450aba037ceaea9d49c43ef8fef
Author: MTRNord <mtrnord1@gmail.com>
Date: Tue, 5 Sep 2023 19:09:11 +0200
Add pgbouncer
Diffstat:
4 files changed, 204 insertions(+), 142 deletions(-)
diff --git a/nixos/common/lib/envoy.nix b/nixos/common/lib/envoy.nix
@@ -2,7 +2,7 @@
{
services = {
envoy = {
- enable = true;
+ enable = false;
settings = {
admin = {
access_log_path = "/dev/null";
@@ -111,8 +111,9 @@
{
name = "postgres_cluster";
connect_timeout = "0.25s";
- type = "STRICT_DNS";
+ type = "STATIC";
lb_policy = "LEAST_REQUEST";
+ per_connection_buffer_limit_bytes = 16777216;
load_assignment = {
cluster_name = "postgres_cluster";
endpoints = [
diff --git a/nixos/common/lib/patroni.nix b/nixos/common/lib/patroni.nix
@@ -0,0 +1,146 @@
+{ lib, pkgs, config, ... }:
+{
+ environment.systemPackages = with pkgs; [
+ patroni
+ etcd_3_4
+ ];
+
+ services = {
+ postgresql = {
+ enableJIT = true;
+ enable = false;
+ enableTCPIP = true;
+ settings = {
+ listen_addresses = "100.64.0.1";
+ };
+ authentication = ''
+ host all all 10.100.12.1/32 md5
+ host replication all 10.100.12.1/32 md5
+ host all all 10.100.0.0/10 md5
+ host replication all 10.100.0.0/10 md5
+ host all all 10.244.0.0/10 md5
+ '';
+ };
+
+ etcd = {
+ enable = true;
+ initialClusterState = "existing";
+ listenClientUrls = [ "http://100.64.0.1:2379" ];
+ listenPeerUrls = [ "http://100.64.0.1:2380" ];
+ initialCluster = [
+ "worker-1=http://100.64.0.1:2380"
+ "nordgedanken=http://100.64.0.3:2380"
+ ];
+ extraConf = {
+ "UNSUPPORTED_ARCH" = "arm64";
+ "ENABLE_V2" = "true";
+ };
+ };
+ patroni = {
+ enable = true;
+ nodeIp = "10.100.0.1";
+ name = "worker-1";
+ scope = "cluster-1";
+ postgresqlPackage = pkgs.postgresql_14;
+
+ settings = {
+ postgresql = {
+ listen = lib.mkForce "127.0.0.1,10.100.0.1:5432";
+ parameters = {
+ max_connections = "160";
+ superuser_reserved_connections = "3";
+
+ shared_buffers = "4096 MB";
+ work_mem = "32 MB";
+ maintenance_work_mem = "320 MB";
+ huge_pages = "off";
+ effective_cache_size = "11 GB";
+ effective_io_concurrency = "200"; # concurrent IO only really activated if OS supports posix_fadvise function
+ random_page_cost = "1.25"; # speed of random disk access relative to sequential access (1.0)
+
+ # Monitoring
+ shared_preload_libraries = "pg_stat_statements"; # per statement resource usage stats
+ track_io_timing = "on"; # measure exact block IO times
+ track_functions = "pl"; # track execution times of pl-language procedures if any
+
+ # Replication
+ wal_level = "replica"; # consider using at least "replica"
+ max_wal_senders = "10";
+ #synchronous_commit = "on";
+
+ # Checkpointing:
+ checkpoint_timeout = "15 min";
+ checkpoint_completion_target = "0.9";
+ max_wal_size = "1024 MB";
+ min_wal_size = "512 MB";
+
+ # WAL archiving
+ archive_mode = "on"; # having it on enables activating P.I.T.R. at a later time without restart›
+ archive_command = "/bin/true"; # not doing anything yet with WAL-s
+
+ # WAL writing
+ wal_compression = "on";
+ wal_buffers = "-1"; # auto-tuned by Postgres till maximum of segment size (16MB by default)
+ wal_writer_delay = "200ms";
+ wal_writer_flush_after = "1MB";
+ wal_keep_size = "3650 MB";
+
+
+ # Background writer
+ bgwriter_delay = "200ms";
+ bgwriter_lru_maxpages = "100";
+ bgwriter_lru_multiplier = "2.0";
+ bgwriter_flush_after = "0";
+
+ # Parallel queries:
+ max_worker_processes = "12";
+ max_parallel_workers_per_gather = "6";
+ max_parallel_maintenance_workers = "6";
+ max_parallel_workers = "12";
+ parallel_leader_participation = "on";
+
+ # Advanced features
+ enable_partitionwise_join = "on";
+ enable_partitionwise_aggregate = "on";
+ jit = "on";
+ max_slot_wal_keep_size = "1000 MB";
+ track_wal_io_timing = "on";
+ };
+ };
+ etcd = {
+ hosts = [
+ "100.64.0.3:2379"
+ "100.64.0.1:2379"
+ ];
+ };
+ tags = {
+ nofailover = false;
+ noloadbalance = false;
+ clonefrom = false;
+ nosync = false;
+ };
+ };
+
+ otherNodesIps = [
+ "100.64.0.3"
+ ];
+
+ environmentFiles = {
+ PATRONI_REPLICATION_USERNAME = config.sops.secrets."patroni/replication_username".path;
+ PATRONI_REPLICATION_PASSWORD = config.sops.secrets."patroni/replication_password".path;
+ PATRONI_SUPERUSER_USERNAME = config.sops.secrets."patroni/replication_superuser_username".path;
+ PATRONI_SUPERUSER_PASSWORD = config.sops.secrets."patroni/replication_superuser_password".path;
+ };
+ };
+ };
+
+ # Ensure postgres can create a lockfile where it expects
+ system.activationScripts = {
+ postgresqlMkdir = {
+ text = "mkdir -p /run/postgresql && chmod o+w /run/postgresql";
+ deps = [ ];
+ };
+ };
+
+ systemd.services.etcd.serviceConfig.ExecStart = lib.mkForce "${pkgs.etcd_3_4}/bin/etcd";
+}
diff --git a/nixos/common/lib/pgbouncer.nix b/nixos/common/lib/pgbouncer.nix
@@ -0,0 +1,52 @@
+{ lib, pkgs, config, ... }:
+{
+ environment.systemPackages = with pkgs; [
+ pgbouncer
+ ];
+ users.users = {
+ pgbouncer = {
+ isSystemUser = true;
+ description = "PgBouncer User";
+ };
+ };
+
+ systemd.services.pgbouncer = {
+ enable = true;
+ after = [ "network-online.target" ];
+ requires = [ "network-online.target" ];
+ description = "PgBouncer - PostgreSQL connection pooler";
+ wantedBy = [ "multi-user.target" ];
+ serviceConfig = {
+ ExecStart = "${pkgs.pgbouncer}/bin/pgbouncer /etc/pgbouncer/pgbouncer.ini";
+ Restart = "always";
+ RestartSec = 5;
+ TimeoutStopSec = 5;
+ User = config.users.users.pgbouncer.name; # Set the user under which PgBouncer should run
+ Group = config.users.users.pgbouncer.group; # Set the group under which PgBouncer should run
+ };
+ };
+
+ environment.etc."pgbouncer/pgbouncer.ini" = {
+ owner = config.users.users.pgbouncer.name;
+ group = config.users.users.pgbouncer.name.group;
+ text = ''
+ [databases]
+ db2 = host=10.100.0.2 port=5432
+ db = host=10.100.0.1 port=5432
+
+ [pgbouncer]
+ listen_addr = ::
+ listen_port = 5000
+
+ ; Define your PgBouncer user and password here (replace with your actual values)
+ auth_type = hba
+ auth_hba_file = ${services.patroni.postgresqlDataDir}/pg_hba.conf
+
+ ; Connection Pooling Settings
+ pool_mode = transaction
+ max_client_conn = 200
+ min_pool_size = 5
+ reserve_pool_size = 5
+ '';
+ };
+}
diff --git a/nixos/worker-1/configuration.nix b/nixos/worker-1/configuration.nix
@@ -26,11 +26,13 @@
./boot.nix
../common/lib/shell.nix
+ ../common/lib/patroni.nix
+ ../common/lib/pgadmin.nix
+ ../common/lib/pgbouncer.nix
../common/lib/envoy.nix
../common/lib/fail2ban.nix
../common/lib/podman.nix
../common/lib/asterisk.nix
- ../common/lib/pgadmin.nix
];
nixpkgs = {
@@ -241,8 +243,6 @@
environment.systemPackages = with pkgs; [
unstable.forgejo-actions-runner
config.services.headscale.package
- patroni
- etcd_3_4
];
# Configure your system-wide user settings (groups, etc), add more users as needed.
@@ -567,145 +567,8 @@
};
};
};
-
- postgresql = {
- enableJIT = true;
- enable = false;
- enableTCPIP = true;
- settings = {
- listen_addresses = "100.64.0.1";
- };
- authentication = ''
- host all all 10.100.12.1/32 md5
- host replication all 10.100.12.1/32 md5
- host all all 10.100.0.0/10 md5
- host replication all 10.100.0.0/10 md5
- host all all 10.244.0.0/10 md5
- '';
- };
-
- etcd = {
- enable = true;
- initialClusterState = "existing";
- listenClientUrls = [ "http://100.64.0.1:2379" ];
- listenPeerUrls = [ "http://100.64.0.1:2380" ];
- initialCluster = [
- "worker-1=http://100.64.0.1:2380"
- "nordgedanken=http://100.64.0.3:2380"
- ];
- extraConf = {
- "UNSUPPORTED_ARCH" = "arm64";
- "ENABLE_V2" = "true";
- };
- };
- patroni = {
- enable = true;
- nodeIp = "10.100.0.1";
- name = "worker-1";
- scope = "cluster-1";
- postgresqlPackage = pkgs.postgresql_14;
-
- settings = {
- postgresql = {
- listen = lib.mkForce "127.0.0.1,10.100.0.1:5432";
- parameters = {
- max_connections = "160";
- superuser_reserved_connections = "3";
-
- shared_buffers = "4096 MB";
- work_mem = "32 MB";
- maintenance_work_mem = "320 MB";
- huge_pages = "off";
- effective_cache_size = "11 GB";
- effective_io_concurrency = "200"; # concurrent IO only really activated if OS supports posix_fadvise function
- random_page_cost = "1.25"; # speed of random disk access relative to sequential access (1.0)
-
- # Monitoring
- shared_preload_libraries = "pg_stat_statements"; # per statement resource usage stats
- track_io_timing = "on"; # measure exact block IO times
- track_functions = "pl"; # track execution times of pl-language procedures if any
-
- # Replication
- wal_level = "replica"; # consider using at least "replica"
- max_wal_senders = "10";
- #synchronous_commit = "on";
-
- # Checkpointing:
- checkpoint_timeout = "15 min";
- checkpoint_completion_target = "0.9";
- max_wal_size = "1024 MB";
- min_wal_size = "512 MB";
-
- # WAL archiving
- archive_mode = "on"; # having it on enables activating P.I.T.R. at a later time without restart›
- archive_command = "/bin/true"; # not doing anything yet with WAL-s
-
- # WAL writing
- wal_compression = "on";
- wal_buffers = "-1"; # auto-tuned by Postgres till maximum of segment size (16MB by default)
- wal_writer_delay = "200ms";
- wal_writer_flush_after = "1MB";
- wal_keep_size = "3650 MB";
-
-
- # Background writer
- bgwriter_delay = "200ms";
- bgwriter_lru_maxpages = "100";
- bgwriter_lru_multiplier = "2.0";
- bgwriter_flush_after = "0";
-
- # Parallel queries:
- max_worker_processes = "12";
- max_parallel_workers_per_gather = "6";
- max_parallel_maintenance_workers = "6";
- max_parallel_workers = "12";
- parallel_leader_participation = "on";
-
- # Advanced features
- enable_partitionwise_join = "on";
- enable_partitionwise_aggregate = "on";
- jit = "on";
- max_slot_wal_keep_size = "1000 MB";
- track_wal_io_timing = "on";
- };
- };
- etcd = {
- hosts = [
- "100.64.0.3:2379"
- "100.64.0.1:2379"
- ];
- };
- tags = {
- nofailover = false;
- noloadbalance = false;
- clonefrom = false;
- nosync = false;
- };
- };
-
- otherNodesIps = [
- "100.64.0.3"
- ];
-
- environmentFiles = {
- PATRONI_REPLICATION_USERNAME = config.sops.secrets."patroni/replication_username".path;
- PATRONI_REPLICATION_PASSWORD = config.sops.secrets."patroni/replication_password".path;
- PATRONI_SUPERUSER_USERNAME = config.sops.secrets."patroni/replication_superuser_username".path;
- PATRONI_SUPERUSER_PASSWORD = config.sops.secrets."patroni/replication_superuser_password".path;
- };
- };
};
- # Ensure postgres can create a lockfile where it expects
- system.activationScripts = {
- postgresqlMkdir = {
- text = "mkdir -p /run/postgresql && chmod o+w /run/postgresql";
- deps = [ ];
- };
- };
-
- systemd.services.etcd.serviceConfig.ExecStart = lib.mkForce "${pkgs.etcd_3_4}/bin/etcd";
-
# https://nixos.wiki/wiki/FAQ/When_do_I_update_stateVersion
system.stateVersion = "23.05";
}
