How We Administer PostgreSQL, MySQL, MongoDB, Redis, Elasticsearch
Imagine: replication is set up, backups are scheduled, monitoring shows green. Then the PostgreSQL primary node goes down at 3 AM, the replica doesn't fail over automatically — the replication slot is stuck, LSN lag is 8 hours. The application degrades, the DBA gets a call. Sound familiar? We solve these problems every day. Over five years we have completed more than 120 projects on database infrastructure—from startups to enterprises with petabytes of data. Our Professional Database Administration Services cover the full stack: PostgreSQL, MySQL, MongoDB, Redis, Elasticsearch.
Most database problems in production are not bugs in the DBMS itself, but misconfigured HA setups, lack of connection pooling under load, and ignoring query planner hints until a query starts taking 30 seconds instead of 300ms. Below we break down typical scenarios and our solutions.
Why Set Up PostgreSQL HA with Patroni?
Patroni is the de facto standard for PostgreSQL HA in Kubernetes and bare metal. It manages leader election via DCS (etcd, Consul, or ZooKeeper). One leader accepts writes, the rest are hot standbys with streaming replication. If the leader does not respond to health checks longer than ttl seconds—Patroni initiates an election and promotes the best replica.
Typical configuration of a three-node cluster:
scope: postgres-ha name: pg-node-1 restapi: listen: 0.0.0.0:8008 connect_address: 10.0.1.1:8008 etcd3: hosts: 10.0.0.10:2379,10.0.0.11:2379,10.0.0.12:2379 bootstrap: dcs: ttl: 30 loop_wait: 10 retry_timeout: 10 maximum_lag_on_failover: 1048576 # 1MB — replica with lag > 1MB will not become leader postgresql: use_pg_rewind: true parameters: max_connections: 200 shared_buffers: 4GB wal_level: replica hot_standby: "on" wal_keep_size: 2GB synchronous_commit: "on" postgresql: listen: 0.0.0.0:5432 connect_address: 10.0.1.1:5432 data_dir: /data/postgresql/14/main pg_hba: - host replication replicator 10.0.0.0/24 md5 - host all all 0.0.0.0/0 md5 The parameter maximum_lag_on_failover: 1048576 equals one megabyte of WAL lag. A replica with lag greater than that will not be promoted to avoid transaction loss. For zero loss, synchronous_standby_names is needed.
Another problem everyone ignores: replication slots. If an application connects to a replica via a logical replication slot and the replica falls behind, the slot on the primary retains WAL files, and the disk fills up. PostgreSQL does not delete WAL until the slot is released. Solution: monitor pg_replication_slots and set max_slot_wal_keep_size in PostgreSQL 13+:
SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag FROM pg_replication_slots ORDER BY lag DESC; SELECT pg_drop_replication_slot('slot_name'); -- caution — data loss for subscriber How to Avoid Connection Exhaustion with PgBouncer?
PostgreSQL creates a separate process for each connection. With 500 concurrent connections and shared_buffers=4GB, about 500MB is spent just on proc structures. Applications using Node.js or Go with connection pools of 20 connections per instance at 30 instances yield 600 connections—and max_connections=200 returns FATAL: sorry, too many clients already.
PgBouncer in transaction pooling mode keeps N connections to PostgreSQL and multiplexes thousands of client sessions. This reduces connection overhead by 10x compared to direct connections.
[databases] mydb = host=10.0.1.1 port=5432 dbname=mydb [pgbouncer] pool_mode = transaction max_client_conn = 2000 default_pool_size = 50 reserve_pool_size = 10 reserve_pool_timeout = 5 server_idle_timeout = 600 log_connections = 0 log_disconnections = 0 Transaction pooling limitation: you cannot use SET LOCAL, LISTEN/NOTIFY, advisory locks, and prepared statements without server_reset_query. This must be considered during migration.
Redis Cluster: Sharding and Gotchas
Redis Cluster divides the keyspace into 16384 hash slots distributed across master nodes. Minimum configuration is 3 masters + 3 replicas. Each key is mapped via CRC16(key) mod 16384.
The most common mistake is CROSSSLOT on multi-key operations. MGET key1 key2 key3 will fail if keys are on different slots. Solution: hash tags—{user:123}:session and {user:123}:cart are guaranteed to be on the same slot because Cluster only looks at the part inside {}.
# Create cluster redis-cli --cluster create \ 10.0.1.1:6379 10.0.1.2:6379 10.0.1.3:6379 \ 10.0.1.4:6379 10.0.1.5:6379 10.0.1.6:6379 \ --cluster-replicas 1 Redis Cluster does not support databases 0-15 (only db 0), Lua scripts with keys on different slots, and SCAN across the whole cluster without iterating nodes. If you need these features, consider KeyDB or Valkey as drop-in replacements with multi-threading. For caching, persistence is often disabled (save "", appendonly no); for session storage—AOF with appendfsync everysec (1 second data loss acceptable); for critical data—AOF with appendfsync always (throughput drops from ~100k to ~10-20k ops/sec).
MongoDB and MySQL: In Brief
MongoDB Replica Set with three nodes is standard. Problem: writeConcern: { w: 1 }—data may be lost if the primary crashes. In production we configure w: "majority" and j: true. Indexes are built in ESR order (Equality → Sort → Range), otherwise an IXSCAN with in-memory sort occurs.
MySQL in Galera Cluster (Percona XtraDB Cluster) provides synchronous multi-master but kills write-heavy loads due to certification conflicts. Alternative: InnoDB ReplicaSet with MySQL Router and Group Replication—fewer guarantees, higher throughput.
Elasticsearch: Index Management
Elasticsearch is not a database. Attempts to use it as such lead to mapping explosions with dynamic: true. We disable dynamic, explicitly map fields, and configure ILM for time-series data (rollover at 50GB/1 day, forcemerge to warm, freeze to cold, delete after 90 days).
How We Configure the Database Layer: Process and Results
Our process includes four stages:
-
Audit — collect
pg_stat_statements,SHOW STATUS LIKE 'Innodb%', check replication, backups, connection pooling, DBMS versions. - Design — select HA topology (Patroni, Redis Cluster, ReplicaSet), configure DCS, monitoring, alerting.
- Implementation — deploy cluster, set up PgBouncer, backups (pgBackRest, WAL-G), test failover.
- Documentation and handover — provide topology diagram, failover runbook, recovery procedure with real RTO/RPO, monitoring access.
What's Included in the Result
- Fully functional PostgreSQL HA cluster with automatic failover
- PgBouncer with transaction pooling tuned for load
- Monitoring of key metrics (lag, connection count, disk usage, WAL generation) via Prometheus + Grafana
- Runbook for typical operations (manual failover, restore from backup, add node)
- Backup scripts with integrity checks (
pg_verifybackup) - Recommendations for replication and sharding for MongoDB/Redis
- Post-setup support: 2 weeks reactive support
- Access to a shared Grafana dashboard with SLO tracking
| Task | Duration |
|---|---|
| PostgreSQL audit + recommendations | 2–3 days |
| Patroni HA setup (3 nodes) | 3–5 days |
| Redis Cluster (6 nodes) | 2–4 days |
| PgBouncer + query optimization | 3–5 days |
| MongoDB Replica Set + monitoring | 2–4 days |
| Comprehensive database layer (multiple DBMS) | 2–4 weeks |
| HA Solution | Failover Time | Data Loss Risk | Complexity |
|---|---|---|---|
| Patroni + synchronous_standby | ~10‑30s | Zero | Medium |
| Repmgr + async replication | ~30‑60s | Up to 1MB WAL | Low |
| Manual streaming replication | Minutes | Hours of data | High |
Pricing is individual after infrastructure audit. A typical PostgreSQL HA project for an e-commerce store with 10 million orders per month helped a client reduce costs by 40% (previously they ran 4 standalone databases for fault tolerance; after migration to Patroni with 3 nodes, costs decreased). In that case the project cost ~$25,000 and saved over $50,000 annually in hosting and DBA overhead.
Contact us for a detailed proposal — we’ll provide a preliminary estimate within two business days. Schedule a discovery call to discuss your database challenges and receive an initial assessment. Order a turnkey database audit: in 2 days you will get a list of issues and a remediation plan.