57 lines
1.4 KiB
Org Mode
57 lines
1.4 KiB
Org Mode
:PROPERTIES:
|
|
:ID: cce/wobserver/postgres
|
|
:ROAM_ALIASES: PostgreSQL Postgres
|
|
:END:
|
|
#+title: PostgreSQL on the Wobserver
|
|
#+FILETAGS: :Wobserver:CCE:
|
|
|
|
#+ARCOLOGY_KEY: cce/postgres
|
|
#+ARCOLOGY_ALLOW_CRAWL: t
|
|
|
|
My go-to multi-write database server is postgres. I like how it has JSON columns and indexes in a (somewhat) ergonomic form. This is a pretty basic setup.
|
|
|
|
#+ARROYO_NIXOS_MODULE: nixos/postgresql.nix
|
|
#+ARROYO_SYSTEM_ROLE: server
|
|
|
|
#+begin_src nix :tangle ~/arroyo-nix/nixos/postgresql.nix
|
|
{ pkgs, ... }:
|
|
|
|
{
|
|
services.postgresql = {
|
|
enable = true;
|
|
package = pkgs.postgresql_14;
|
|
settings = {
|
|
wal_level = "replica";
|
|
archive_mode = "on";
|
|
archive_command = "test ! -f /srv/pgsql/archive/wal/%f && cp %p /srv/pgsql/archive/wal/%f";
|
|
|
|
shared_preload_libraries = "pg_stat_statements";
|
|
# log_min_duration_statement = 10000;
|
|
# statement_timeout = 60*1000;
|
|
|
|
shared_buffers = "4GB";
|
|
effective_cache_size = "12GB";
|
|
maintenance_work_mem = "1GB";
|
|
work_mem = "4GB";
|
|
default_statistics_target = 250;
|
|
|
|
max_worker_processes = "40";
|
|
max_parallel_workers_per_gather = "10";
|
|
max_parallel_workers = "20";
|
|
|
|
autovacuum = true;
|
|
track_counts = true;
|
|
};
|
|
};
|
|
# backup weekly
|
|
services.postgresqlBackup = {
|
|
enable = true;
|
|
backupAll = true;
|
|
location = "/srv/pgsql/backups/";
|
|
startAt = "Mon *-*-* 02:00:00";
|
|
};
|
|
}
|
|
#+end_src
|
|
|
|
* NEXT validate this
|