[PR #1648] [MERGED] Add PostgreSQL read-only replica support #1677

New issue

Closed

opened 2026-05-07 01:03:12 +02:00 by BreizhHardware · 0 comments

BreizhHardware commented

2026-05-07 01:03:12 +02:00

Owner

📋 Pull Request Information

Original PR: https://github.com/binwiederhier/ntfy/pull/1648
Author: @binwiederhier
Created: 3/11/2026
Status: ✅ Merged
Merged: 3/13/2026
Merged by: @binwiederhier

Base: main ← Head: postgres-replica

📝 Commits (8)

f186574 WIP: Postgres read-only replica
ab33ac7 Refine
ac65df1 Move auth queries to primary, redo health check loop
85bdfc6 Refine, log unhealthy replica
1f483dc Remove consts
9eaadd7 Log
270fec5 Bump
8a34dfe Move things, rename things

📊 Changes

26 files changed (+462 additions, -227 deletions)

View changed files

📝 cmd/serve.go (+6 -1)
📝 cmd/user.go (+3 -2)
📝 db/db.go (+123 -24)
📝 db/pg/pg.go (+34 -22)
📝 db/test/test.go (+13 -12)
➕ db/types.go (+19 -0)
➕ db/util.go (+36 -0)
📝 docs/config.md (+36 -11)
📝 docs/releases.md (+4 -0)
📝 go.mod (+24 -24)
📝 go.sum (+50 -50)
📝 message/cache.go (+15 -13)
📝 message/cache_postgres.go (+5 -4)
📝 message/cache_sqlite.go (+4 -3)
📝 server/config.go (+2 -1)
📝 server/server.go (+19 -6)
📝 test/server.go (+13 -2)
📝 tools/pgimport/main.go (+2 -1)
📝 user/manager.go (+17 -17)
📝 user/manager_postgres.go (+4 -4)

...and 6 more files

📄 Description

Summary

Add db.DB wrapper that supports routing read queries to PostgreSQL read replicas via round-robin with automatic health checking and fallback to primary
Introduce Beginner interface so ExecTx/QueryTx work with both *sql.DB and *db.DB
Route read-heavy queries in message cache, user manager, and web push store to replicas via ReadOnly()
New --database-replica-urls flag (opt-in, no behavior change when unconfigured)

Configuration

database-replica-urls:
  - postgres://user:pass@replica1:5432/ntfy
  - postgres://user:pass@replica2:5432/ntfy

Design

db.DB wraps a primary *sql.DB and optional replicas
ReadOnly() returns a *sql.DB from a healthy replica (round-robin) or falls back to primary
Replicas are health-checked via ping every 5 seconds (cached with atomic CAS)
All writes, transactions, and correctness-critical reads stay on primary
SQLite stores are unaffected (wrapped with db.NewDB(sqlDB, nil) for uniform typing)

Test plan

go test ./... passes (all existing tests, no replicas = ReadOnly returns primary)
Manual test with PG primary + streaming replica
Verify writes never route to replicas
Verify automatic fallback when replica is down

_{🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.}

## 📋 Pull Request Information **Original PR:** https://github.com/binwiederhier/ntfy/pull/1648 **Author:** [@binwiederhier](https://github.com/binwiederhier) **Created:** 3/11/2026 **Status:** ✅ Merged **Merged:** 3/13/2026 **Merged by:** [@binwiederhier](https://github.com/binwiederhier) **Base:** `main` ← **Head:** `postgres-replica` --- ### 📝 Commits (8) - [`f186574`](https://github.com/binwiederhier/ntfy/commit/f1865749d72dcc02b1038dc45d01a9e594307e6a) WIP: Postgres read-only replica - [`ab33ac7`](https://github.com/binwiederhier/ntfy/commit/ab33ac7ae5c8036e1fff3afb4d68ad244ab49bd2) Refine - [`ac65df1`](https://github.com/binwiederhier/ntfy/commit/ac65df1e83ba693a1746dd310c44f913373189a5) Move auth queries to primary, redo health check loop - [`85bdfc6`](https://github.com/binwiederhier/ntfy/commit/85bdfc61ceae999b836f2a19ffb1b8474ff2c5a3) Refine, log unhealthy replica - [`1f483dc`](https://github.com/binwiederhier/ntfy/commit/1f483dcbd39f5b5608a6ce5713fda3975177eff2) Remove consts - [`9eaadd7`](https://github.com/binwiederhier/ntfy/commit/9eaadd74cf4c04abbc091bb510f966a9a3461ca4) Log - [`270fec5`](https://github.com/binwiederhier/ntfy/commit/270fec51a66554c3d580e3619660980f517dde6b) Bump - [`8a34dfe`](https://github.com/binwiederhier/ntfy/commit/8a34dfe3f88439e3ddf3c68888d18f41abb073a2) Move things, rename things ### 📊 Changes **26 files changed** (+462 additions, -227 deletions) <details> <summary>View changed files</summary> 📝 `cmd/serve.go` (+6 -1) 📝 `cmd/user.go` (+3 -2) 📝 `db/db.go` (+123 -24) 📝 `db/pg/pg.go` (+34 -22) 📝 `db/test/test.go` (+13 -12) ➕ `db/types.go` (+19 -0) ➕ `db/util.go` (+36 -0) 📝 `docs/config.md` (+36 -11) 📝 `docs/releases.md` (+4 -0) 📝 `go.mod` (+24 -24) 📝 `go.sum` (+50 -50) 📝 `message/cache.go` (+15 -13) 📝 `message/cache_postgres.go` (+5 -4) 📝 `message/cache_sqlite.go` (+4 -3) 📝 `server/config.go` (+2 -1) 📝 `server/server.go` (+19 -6) 📝 `test/server.go` (+13 -2) 📝 `tools/pgimport/main.go` (+2 -1) 📝 `user/manager.go` (+17 -17) 📝 `user/manager_postgres.go` (+4 -4) _...and 6 more files_ </details> ### 📄 Description ## Summary - Add `db.DB` wrapper that supports routing read queries to PostgreSQL read replicas via round-robin with automatic health checking and fallback to primary - Introduce `Beginner` interface so `ExecTx`/`QueryTx` work with both `*sql.DB` and `*db.DB` - Route read-heavy queries in message cache, user manager, and web push store to replicas via `ReadOnly()` - New `--database-replica-urls` flag (opt-in, no behavior change when unconfigured) ## Configuration ```yaml database-replica-urls: - postgres://user:pass@replica1:5432/ntfy - postgres://user:pass@replica2:5432/ntfy ``` ## Design - `db.DB` wraps a primary `*sql.DB` and optional replicas - `ReadOnly()` returns a `*sql.DB` from a healthy replica (round-robin) or falls back to primary - Replicas are health-checked via ping every 5 seconds (cached with atomic CAS) - All writes, transactions, and correctness-critical reads stay on primary - SQLite stores are unaffected (wrapped with `db.NewDB(sqlDB, nil)` for uniform typing) ## Test plan - [ ] `go test ./...` passes (all existing tests, no replicas = ReadOnly returns primary) - [ ] Manual test with PG primary + streaming replica - [ ] Verify writes never route to replicas - [ ] Verify automatic fallback when replica is down --- <sub>🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.</sub>