963 lines
20 KiB
Plaintext
963 lines
20 KiB
Plaintext
Yes. At this point, the next phase should be **systematic failure testing**. A multi-master cluster is not production-ready just because `INSERT` on Node1 appears on Node2 and Node3.
|
||
|
||
One important thing first: your setup has been using **Spock 5.0.1**. pgEdge's current v5 release notes list **5.0.12**, with fixes around connection-loss recovery, exception handling, apply workers, memory growth and newer PostgreSQL security changes. Before production, I would include a controlled upgrade rehearsal to a current supported 5.x patch rather than certify 5.0.1 as-is. [pgEdge Documentation](https://docs.pgedge.com/spock-v5/development/spock_release_notes/?utm_source=chatgpt.com)
|
||
|
||
Do the destructive tests below on a staging copy of the cluster, not on the only copy of important data.
|
||
|
||
## 1. Establish your normal baseline first
|
||
|
||
Before deliberately breaking anything, all three nodes should show healthy subscriptions and essentially zero lag.
|
||
|
||
Run on every node:
|
||
|
||
```sql
|
||
SELECT * FROM spock.sub_show_status();
|
||
```
|
||
|
||
```sql
|
||
SELECT * FROM spock.lag_tracker;
|
||
```
|
||
|
||
```sql
|
||
SELECT
|
||
slot_name,
|
||
active,
|
||
wal_status,
|
||
restart_lsn,
|
||
confirmed_flush_lsn
|
||
FROM pg_replication_slots;
|
||
```
|
||
|
||
Check:
|
||
|
||
```sql
|
||
SHOW track_commit_timestamp;
|
||
SHOW spock.conflict_resolution;
|
||
SHOW spock.exception_behaviour;
|
||
```
|
||
|
||
And:
|
||
|
||
```sql
|
||
SELECT *
|
||
FROM spock.exception_log
|
||
ORDER BY timestamp DESC
|
||
LIMIT 50;
|
||
```
|
||
|
||
`track_commit_timestamp` needs to be enabled for timestamp-based Spock conflict resolution such as `last_update_wins`. [pgEdge Documentation](https://docs.pgedge.com/spock-v5/development/configuring/?utm_source=chatgpt.com)
|
||
|
||
Your baseline should be:
|
||
|
||
```text
|
||
Node1 → Node2 ✓
|
||
Node1 → Node3 ✓
|
||
|
||
Node2 → Node1 ✓
|
||
Node2 → Node3 ✓
|
||
|
||
Node3 → Node1 ✓
|
||
Node3 → Node2 ✓
|
||
|
||
Lag → approximately 0
|
||
Exceptions → none unexplained
|
||
```
|
||
|
||
---
|
||
|
||
# Production test matrix
|
||
|
||
| ID | Scenario | Test | Production PASS condition |
|
||
| --- |--------------------------|-------------------------------------------------------|---------------------------|
|
||
| P01 | Node1 INSERT | Insert unique rows on Node1 | Node2 + Node3 receive exact rows |(pass)
|
||
| P02 | Node2 INSERT | Write only Node2 | Node1 + Node3 receive them |(pass)
|
||
| P03 | Node3 INSERT | Write only Node3 | Node1 + Node2 receive them |(pass)
|
||
| P04 | UPDATE/DELETE | Perform updates/deletes from every node | All 3 converge |(pass)
|
||
| P05 | PostgreSQL restart | Restart one PostgreSQL container | Replication reconnects automatically | (pass)
|
||
| P06 | VM reboot | Reboot one VM completely | DB + Spock recover and catch up |(pass)
|
||
| P07 | Short outage | Node offline 5–30 min while others write | All backlog catches up |(inprogress)
|
||
| P08 | Long backlog | Generate hours/days-equivalent WAL while node offline | Catch-up succeeds without WAL loss/disk exhaustion |
|
||
| P09 | Network partition | Node stays writable but cannot replicate | Reconnect works and conflicts behave as designed |
|
||
| P10 | Same-row UPDATE | Modify same PK concurrently on 2 nodes | Expected conflict policy wins |
|
||
| P11 | Duplicate PK | Insert same PK independently on 2 nodes | Conflict detected/resolved as expected |
|
||
| P12 | Unique constraint | Different PKs but same UNIQUE value | Failure/conflict is handled and alerted |
|
||
| P13 | DELETE vs UPDATE | Delete a row on one node, update on another | Expected result + exception handling verified |
|
||
| P14 | Business conflict | Two nodes decrement same inventory/balance | Business invariant remains correct or architecture changed |
|
||
| P15 | DDL | CREATE/ALTER during maintenance procedure | Identical schema across all nodes |
|
||
| P16 | Node-down DDL | One node unavailable during schema migration | Recovery procedure proven |
|
||
| P17 | Schema mismatch | Deliberately introduce mismatch in staging | Monitoring detects it; recovery works |
|
||
| P18 | Disk pressure | Drive disk toward threshold in staging | Alerts fire before PostgreSQL becomes unsafe |
|
||
| P19 | Replication-slot backlog | Subscriber offline while WAL grows | Retention monitored and bounded |
|
||
| P20 | Lost slot/WAL | Simulate unrecoverable replication state | Documented resync procedure succeeds |
|
||
| P21 | High write load | Concurrent writes across all nodes | Lag remains inside target |
|
||
| P22 | Long soak | 24–72 h realistic traffic | No growing lag/memory/disk/errors |
|
||
| P23 | ACE comparison | Run table-diff after every failure test | Tables match |
|
||
| P24 | Backup restore | Restore pgBackRest backup | Database boots and data verified |
|
||
| P25 | PITR | Recover to timestamp before accidental change | Recovery reaches correct point |
|
||
| P26 | Entire-site loss | Lose one VM/site completely | Remaining service meets RTO/RPO |
|
||
| P27 | Credentials/TLS | Invalid clients attempt connection | Unauthorized traffic rejected |
|
||
| P28 | Upgrade | PostgreSQL/Spock patch upgrade rehearsal | Cluster remains consistent |
|
||
| P29 | Monitoring failure | Kill exporter/agent | Alert tells you monitoring itself failed |
|
||
| P30 | Control-plane failure | Stop your FastAPI management service | Database replication continues independently |
|
||
|
||
That is the set I would use as your production qualification.
|
||
|
||
---
|
||
|
||
## 2. The first conflict test: same row, two nodes
|
||
|
||
Create:
|
||
|
||
```sql
|
||
CREATE TABLE conflict_test (
|
||
id UUID PRIMARY KEY,
|
||
value TEXT,
|
||
updated_at TIMESTAMPTZ DEFAULT now()
|
||
);
|
||
```
|
||
|
||
Insert one row and allow it to synchronize.
|
||
|
||
Then disconnect replication temporarily in your staging test and make:
|
||
|
||
Node1:
|
||
|
||
```sql
|
||
UPDATE conflict_test
|
||
SET value = 'NODE1'
|
||
WHERE id = '<same-uuid>';
|
||
```
|
||
|
||
Node2:
|
||
|
||
```sql
|
||
UPDATE conflict_test
|
||
SET value = 'NODE2'
|
||
WHERE id = '<same-uuid>';
|
||
```
|
||
|
||
Restore connectivity.
|
||
|
||
Eventually:
|
||
|
||
```sql
|
||
SELECT * FROM conflict_test;
|
||
```
|
||
|
||
should give the **same value on all three nodes**.
|
||
|
||
But the exact winner needs to match your chosen conflict policy.
|
||
|
||
Spock 5.x supports policies including:
|
||
|
||
```text
|
||
error
|
||
apply_remote
|
||
keep_local
|
||
last_update_wins
|
||
```
|
||
|
||
with `last_update_wins` using commit timestamps. [pgEdge Documentation](https://docs.pgedge.com/spock-v5/development/configuring/?utm_source=chatgpt.com)
|
||
|
||
This test answers:
|
||
|
||
> "Does replication converge when two masters genuinely disagree?"
|
||
|
||
---
|
||
|
||
## 3. Duplicate primary-key test
|
||
|
||
This is extremely important.
|
||
|
||
While two nodes are isolated:
|
||
|
||
Node1:
|
||
|
||
```sql
|
||
INSERT INTO conflict_test(id, value)
|
||
VALUES ('same-uuid', 'NODE1');
|
||
```
|
||
|
||
Node2:
|
||
|
||
```sql
|
||
INSERT INTO conflict_test(id, value)
|
||
VALUES ('same-uuid', 'NODE2');
|
||
```
|
||
|
||
Reconnect.
|
||
|
||
All nodes should eventually converge according to the configured conflict-resolution policy.
|
||
|
||
If you're using generated integer IDs, test those particularly aggressively.
|
||
|
||
For active-active, UUID/Snowflake-style distributed IDs are much safer than independent ordinary sequences.
|
||
|
||
---
|
||
|
||
## 4. Test a second UNIQUE constraint
|
||
|
||
This is one of the places active-active systems get interesting.
|
||
|
||
Example:
|
||
|
||
```sql
|
||
CREATE TABLE user_test (
|
||
id UUID PRIMARY KEY,
|
||
email TEXT UNIQUE
|
||
);
|
||
```
|
||
|
||
Node1:
|
||
|
||
```sql
|
||
INSERT INTO user_test
|
||
VALUES (
|
||
gen_random_uuid(),
|
||
'test@example.com'
|
||
);
|
||
```
|
||
|
||
Node2 simultaneously:
|
||
|
||
```sql
|
||
INSERT INTO user_test
|
||
VALUES (
|
||
gen_random_uuid(),
|
||
'test@example.com'
|
||
);
|
||
```
|
||
|
||
Different primary keys:
|
||
|
||
```text
|
||
UUID A ≠ UUID B
|
||
```
|
||
|
||
but identical:
|
||
|
||
```text
|
||
email
|
||
```
|
||
|
||
Spock 5 documentation specifically warns about multiple unique indexes/constraints in multi-master environments because conflict handling can encounter downstream constraint violations. [pgEdge Documentation](https://docs.pgedge.com/spock-v5/v5-0-10/limitations/?utm_source=chatgpt.com)
|
||
|
||
You need to know exactly how your application's tables behave here.
|
||
|
||
---
|
||
|
||
# 5. Network partition — one of your most important tests
|
||
|
||
This is different from shutting Node1 down.
|
||
|
||
### Clean outage
|
||
|
||
```text
|
||
Node1
|
||
OFF
|
||
|
||
Node2 ←→ Node3
|
||
```
|
||
|
||
Generally easy:
|
||
|
||
```text
|
||
Node2/3 accumulate changes
|
||
↓
|
||
Node1 returns
|
||
↓
|
||
backlog catches up
|
||
```
|
||
|
||
### Network partition
|
||
|
||
Much more dangerous:
|
||
|
||
```text
|
||
Node1 Node2/Node3
|
||
|
||
ONLINE ONLINE
|
||
writes writes
|
||
|
||
X network connection X
|
||
```
|
||
|
||
Now both sides are independently accepting transactions.
|
||
|
||
Suppose:
|
||
|
||
```text
|
||
Node1:
|
||
customer balance = 50
|
||
|
||
Node2:
|
||
customer balance = 75
|
||
```
|
||
|
||
After reconnection, Spock has to reconcile those histories.
|
||
|
||
Your PASS condition isn't merely:
|
||
|
||
```text
|
||
replication resumed
|
||
```
|
||
|
||
It is:
|
||
|
||
```text
|
||
✓ replication resumed
|
||
✓ conflict policy executed correctly
|
||
✓ no subscription disabled
|
||
✓ exception log understood
|
||
✓ lag returned to zero
|
||
✓ ACE says tables agree
|
||
✓ application business rules still hold
|
||
```
|
||
|
||
---
|
||
|
||
# 6. Test business conflicts, not just database conflicts
|
||
|
||
This is possibly **the most important production test**.
|
||
|
||
Start with:
|
||
|
||
```text
|
||
inventory = 1
|
||
```
|
||
|
||
During a partition:
|
||
|
||
Node1:
|
||
|
||
```sql
|
||
UPDATE products
|
||
SET inventory = inventory - 1
|
||
WHERE id = 10;
|
||
```
|
||
|
||
Node2:
|
||
|
||
```sql
|
||
UPDATE products
|
||
SET inventory = inventory - 1
|
||
WHERE id = 10;
|
||
```
|
||
|
||
Both transactions locally see:
|
||
|
||
```text
|
||
1 → 0
|
||
```
|
||
|
||
Eventually the database might converge to:
|
||
|
||
```text
|
||
inventory = 0
|
||
```
|
||
|
||
Yet:
|
||
|
||
```text
|
||
two customers bought one item
|
||
```
|
||
|
||
That's a failure at the **business layer**, even though replication technically worked.
|
||
|
||
Spock also provides delta-apply mechanisms specifically because counters/running totals are a special problem in active-active designs. [pgEdge Documentation](https://docs.pgedge.com/spock-v6/v6-0-0-beta/conflicts/?utm_source=chatgpt.com)
|
||
|
||
For things such as:
|
||
|
||
```text
|
||
bank balances
|
||
inventory
|
||
seat availability
|
||
quotas
|
||
ticket allocation
|
||
counters
|
||
```
|
||
|
||
you must test the actual business invariant.
|
||
|
||
---
|
||
|
||
# 7. Ten-day outage / WAL-retention test
|
||
|
||
You don't necessarily have to wait ten real days.
|
||
|
||
Generate a representative amount of WAL while one node is offline.
|
||
|
||
Monitor:
|
||
|
||
```sql
|
||
SELECT
|
||
slot_name,
|
||
active,
|
||
wal_status,
|
||
pg_size_pretty(
|
||
pg_wal_lsn_diff(
|
||
pg_current_wal_lsn(),
|
||
restart_lsn
|
||
)
|
||
) AS retained_wal
|
||
FROM pg_replication_slots;
|
||
```
|
||
|
||
You want to know:
|
||
|
||
```text
|
||
100 MB backlog?
|
||
1 GB?
|
||
20 GB?
|
||
100 GB?
|
||
```
|
||
|
||
Then calculate how much storage you'd need for your actual outage window.
|
||
|
||
Pass condition:
|
||
|
||
```text
|
||
Node offline
|
||
↓
|
||
WAL grows
|
||
↓
|
||
disk remains safe
|
||
↓
|
||
node returns
|
||
↓
|
||
lag decreases steadily
|
||
↓
|
||
0 lag
|
||
↓
|
||
ACE passes
|
||
```
|
||
|
||
---
|
||
|
||
# 8. Disk-full test
|
||
|
||
You already discovered how unpleasant a `100% /` filesystem can be.
|
||
|
||
For production, don't wait until:
|
||
|
||
```text
|
||
100%
|
||
```
|
||
|
||
Alert at something like:
|
||
|
||
```text
|
||
70% warning
|
||
80% serious
|
||
90% critical
|
||
```
|
||
|
||
based on your actual capacity model.
|
||
|
||
Especially monitor:
|
||
|
||
```text
|
||
/
|
||
PGDATA
|
||
pg_wal
|
||
Docker storage
|
||
backup repository
|
||
```
|
||
|
||
A lagging replication slot can cause WAL retention to grow dramatically.
|
||
|
||
Do the actual "nearly fill the disk" experiment only on staging.
|
||
|
||
---
|
||
|
||
# 9. DDL testing
|
||
|
||
This deserves an entire test category.
|
||
|
||
Test:
|
||
|
||
```sql
|
||
CREATE TABLE
|
||
ALTER TABLE ADD COLUMN
|
||
ALTER TABLE DROP COLUMN
|
||
CREATE INDEX
|
||
DROP INDEX
|
||
ALTER COLUMN TYPE
|
||
DROP TABLE
|
||
```
|
||
|
||
But be careful.
|
||
|
||
Spock's own v5 documentation says multi-master schema changes require special care because there's no generic "freeze replication queue and wait until every transaction is applied" mechanism. For multi-master, writes should be stopped on all nodes and slots allowed to catch up before incompatible schema changes. [pgEdge Documentation](https://docs.pgedge.com/spock-v5/v5-0-10/limitations/?utm_source=chatgpt.com)
|
||
|
||
It also specifically warns about operations including:
|
||
|
||
```text
|
||
CREATE TABLE AS
|
||
DROP TABLE in 3+ node clusters
|
||
```
|
||
|
||
and recommends maintenance windows for potentially dangerous DDL. [pgEdge Documentation](https://docs.pgedge.com/spock-v5/development/managing/spock_autoddl/?utm_source=chatgpt.com)
|
||
|
||
So test your real production migration procedure:
|
||
|
||
```text
|
||
Application maintenance/read-only
|
||
↓
|
||
stop writes
|
||
↓
|
||
lag → zero
|
||
↓
|
||
perform DDL
|
||
↓
|
||
verify schema all nodes
|
||
↓
|
||
ACE
|
||
↓
|
||
resume traffic
|
||
```
|
||
|
||
That procedure itself should be documented.
|
||
|
||
---
|
||
|
||
# 10. Kill PostgreSQL brutally
|
||
|
||
Not just:
|
||
|
||
```bash
|
||
docker stop
|
||
```
|
||
|
||
which is relatively graceful.
|
||
|
||
On a disposable/staging node, simulate:
|
||
|
||
```text
|
||
process crash
|
||
container crash
|
||
VM crash
|
||
unexpected host reboot
|
||
```
|
||
|
||
Then verify:
|
||
|
||
```text
|
||
PostgreSQL crash recovery
|
||
↓
|
||
Spock starts
|
||
↓
|
||
subscriptions reconnect
|
||
↓
|
||
backlog catches up
|
||
↓
|
||
ACE matches
|
||
```
|
||
|
||
You want proof that an administrator doesn't need to manually rebuild replication after an ordinary crash.
|
||
|
||
---
|
||
|
||
# 11. Restart nodes repeatedly
|
||
|
||
Try:
|
||
|
||
```text
|
||
Restart Node1
|
||
Restart Node2
|
||
Restart Node3
|
||
|
||
Restart Node1 + Node2
|
||
Restart Node2 + Node3
|
||
|
||
Restart all three
|
||
```
|
||
|
||
Then vary the order in which they come back.
|
||
|
||
Example:
|
||
|
||
```text
|
||
Node3
|
||
↓
|
||
Node1
|
||
↓
|
||
Node2
|
||
```
|
||
|
||
The cluster should recover regardless of reasonable startup ordering.
|
||
|
||
---
|
||
|
||
# 12. Test an actual replication exception
|
||
|
||
You should deliberately create something that lands in:
|
||
|
||
```sql
|
||
spock.exception_log
|
||
```
|
||
|
||
Then prove your operational process.
|
||
|
||
Monitor:
|
||
|
||
```sql
|
||
SELECT *
|
||
FROM spock.exception_log
|
||
ORDER BY timestamp DESC
|
||
LIMIT 50;
|
||
```
|
||
|
||
Spock's exception behavior can be configured to discard an offending statement/transaction or disable a subscription, so your team needs to know exactly which policy you're using and what happens afterward. [pgEdge Documentation](https://docs.pgedge.com/spock-v5/development/troubleshooting/?utm_source=chatgpt.com)
|
||
|
||
The test isn't complete until you demonstrate:
|
||
|
||
```text
|
||
alert
|
||
↓
|
||
diagnosis
|
||
↓
|
||
repair
|
||
↓
|
||
subscription healthy
|
||
↓
|
||
ACE validation
|
||
```
|
||
|
||
---
|
||
|
||
# 13. ACE after EVERY major failure test
|
||
|
||
This is important.
|
||
|
||
Replication saying:
|
||
|
||
```text
|
||
replicating
|
||
```
|
||
|
||
doesn't prove:
|
||
|
||
```text
|
||
data identical
|
||
```
|
||
|
||
Run:
|
||
|
||
```bash
|
||
./ace table-diff cluster1 public.your_table
|
||
```
|
||
|
||
ACE compares table contents across nodes and reports actual differences. [pgEdge Documentation](https://docs.pgedge.com/ace/v2-1-1/commands/diff/table-diff/?utm_source=chatgpt.com)
|
||
|
||
For each critical table, after:
|
||
|
||
```text
|
||
partition
|
||
restart
|
||
conflict
|
||
DDL
|
||
recovery
|
||
upgrade
|
||
```
|
||
|
||
you should end with:
|
||
|
||
```text
|
||
TABLES MATCH
|
||
```
|
||
|
||
---
|
||
|
||
# 14. Heavy-load test
|
||
|
||
Your system may work perfectly at:
|
||
|
||
```text
|
||
10 transactions/sec
|
||
```
|
||
|
||
and fall apart at:
|
||
|
||
```text
|
||
2,000 transactions/sec
|
||
```
|
||
|
||
Generate simultaneous:
|
||
|
||
```text
|
||
INSERT
|
||
UPDATE
|
||
DELETE
|
||
SELECT
|
||
```
|
||
|
||
across all three nodes.
|
||
|
||
Monitor:
|
||
|
||
```sql
|
||
SELECT * FROM spock.lag_tracker;
|
||
```
|
||
|
||
`spock.lag_tracker` provides replication lag in both bytes and time. [pgEdge Documentation](https://docs.pgedge.com/spock-v5/v5-0-9/monitoring/lag_tracking/?utm_source=chatgpt.com)
|
||
|
||
Monitor additionally:
|
||
|
||
```text
|
||
CPU
|
||
memory
|
||
disk IOPS
|
||
network bandwidth
|
||
connections
|
||
TPS
|
||
locks
|
||
deadlocks
|
||
WAL/sec
|
||
replication lag
|
||
slot backlog
|
||
```
|
||
|
||
You already have much of this in your monitoring/FastAPI work.
|
||
|
||
---
|
||
|
||
# 15. Resource-exhaustion test
|
||
|
||
Check:
|
||
|
||
```sql
|
||
SHOW max_worker_processes;
|
||
SHOW max_replication_slots;
|
||
SHOW max_wal_senders;
|
||
SHOW max_connections;
|
||
```
|
||
|
||
Don't assume:
|
||
|
||
```conf
|
||
max_replication_slots = 10
|
||
max_wal_senders = 10
|
||
```
|
||
|
||
is automatically a production number just because the lab works.
|
||
|
||
Spock uses workers, replication slots, walsenders and replication origins, and insufficient capacity can prevent replication workers from starting. Current pgEdge sizing guidance explicitly warns that tutorial/default values should not be treated as universal production sizing. [pgEdge Documentation](https://docs.pgedge.com/spock-v6/development/sizing/?utm_source=chatgpt.com)
|
||
|
||
Run load tests until you know your headroom.
|
||
|
||
---
|
||
|
||
# 16. Backup is not tested until restore works
|
||
|
||
Install/configure pgBackRest and test:
|
||
|
||
```text
|
||
Full backup
|
||
↓
|
||
Incremental backup
|
||
↓
|
||
WAL archive
|
||
↓
|
||
restore
|
||
```
|
||
|
||
Then verify the restored database.
|
||
|
||
pgEdge recommends monitoring pgBackRest backups and explicitly emphasizes that disaster-recovery strategy needs to be tested and customized for the database. [pgEdge Documentation](https://docs.pgedge.com/platform/managing/pgbackrest/?utm_source=chatgpt.com)
|
||
|
||
---
|
||
|
||
# 17. PITR test
|
||
|
||
Simulate:
|
||
|
||
```text
|
||
10:00 good data
|
||
|
||
10:15 good data
|
||
|
||
10:20 accidental DELETE
|
||
|
||
10:25 users notice
|
||
```
|
||
|
||
You need to demonstrate:
|
||
|
||
```text
|
||
restore → 10:19
|
||
```
|
||
|
||
pgBackRest supports recovery to a specific timestamp/LSN/XID/recovery point. [pgEdge Documentation](https://docs.pgedge.com/pgbackrest/development/user-guide/pitr/?utm_source=chatgpt.com)
|
||
|
||
Measure:
|
||
|
||
```text
|
||
RPO = how much data you lost
|
||
|
||
RTO = how long until service returned
|
||
```
|
||
|
||
Don't estimate those numbers.
|
||
|
||
Actually measure them.
|
||
|
||
---
|
||
|
||
# 18. Upgrade test
|
||
|
||
Given your older Spock patch level, this is especially important.
|
||
|
||
Clone production-like data and perform:
|
||
|
||
```text
|
||
PostgreSQL minor update
|
||
Spock patch update
|
||
Docker image update
|
||
OS security update
|
||
```
|
||
|
||
Then:
|
||
|
||
```text
|
||
restart
|
||
↓
|
||
subscriptions healthy
|
||
↓
|
||
load test
|
||
↓
|
||
conflict test
|
||
↓
|
||
ACE
|
||
```
|
||
|
||
Current Spock 5.x patch releases contain fixes specifically involving reconnect/recovery and replication behavior, which are exactly the areas you're testing. [pgEdge Documentation](https://docs.pgedge.com/spock-v5/development/spock_release_notes/?utm_source=chatgpt.com)
|
||
|
||
---
|
||
|
||
# 19. Security tests
|
||
|
||
For production, verify:
|
||
|
||
```text
|
||
✓ PostgreSQL replication traffic encrypted
|
||
✓ application DB connections encrypted
|
||
✓ SCRAM/cert authentication
|
||
✓ pg_hba restricts source networks
|
||
✓ replication account permissions controlled
|
||
✓ database superuser credentials aren't in frontend
|
||
✓ FastAPI secrets aren't hard-coded
|
||
✓ JWT expires/validates correctly
|
||
✓ Docker API isn't publicly exposed
|
||
✓ FastAPI Docker-control endpoints require authorization
|
||
✓ logs don't leak passwords
|
||
```
|
||
|
||
Your Docker-management API is particularly sensitive because Docker daemon access is effectively host-administrative access.
|
||
|
||
---
|
||
|
||
# 20. Monitoring failure itself
|
||
|
||
People often forget this one.
|
||
|
||
Kill:
|
||
|
||
```text
|
||
postgres_exporter
|
||
Prometheus
|
||
FastAPI monitoring agent
|
||
```
|
||
|
||
Your system should tell you:
|
||
|
||
> Monitoring is unavailable.
|
||
|
||
Otherwise this can happen:
|
||
|
||
```text
|
||
Monitoring dies
|
||
↓
|
||
everything looks green
|
||
↓
|
||
database fails
|
||
↓
|
||
nobody knew
|
||
```
|
||
|
||
---
|
||
|
||
## Build these six dashboards
|
||
|
||
For production I would make your FastAPI/React monitoring page show:
|
||
|
||
```text
|
||
CLUSTER HEALTH
|
||
|
||
Node1 ONLINE
|
||
Node2 ONLINE
|
||
Node3 ONLINE
|
||
|
||
REPLICATION
|
||
|
||
Node1 → Node2 0 MB / 20 ms
|
||
Node1 → Node3 0 MB / 25 ms
|
||
Node2 → Node1 0 MB / 18 ms
|
||
Node2 → Node3 0 MB / 21 ms
|
||
Node3 → Node1 0 MB / 23 ms
|
||
Node3 → Node2 0 MB / 19 ms
|
||
|
||
SLOTS
|
||
|
||
slot active retained WAL
|
||
node1_from_node2 yes 0 MB
|
||
node1_from_node3 yes 0 MB
|
||
|
||
CONFLICTS
|
||
|
||
last hour: 0
|
||
|
||
EXCEPTIONS
|
||
|
||
unresolved: 0
|
||
|
||
SYSTEM
|
||
|
||
CPU 28%
|
||
RAM 61%
|
||
Disk 52%
|
||
Connections 42 / 200
|
||
WAL 3.1 GB
|
||
|
||
ACE
|
||
|
||
Last verification:
|
||
✓ TABLES MATCH
|
||
```
|
||
|
||
That would turn your FastAPI project into a genuinely useful management plane rather than just an API collection.
|
||
|
||
---
|
||
|
||
# Your final go-live gate
|
||
|
||
I would **not call the system production-ready** until you can demonstrate all of these:
|
||
|
||
1. Every node can write and replicate to every other node.
|
||
2. A node can disappear and automatically catch up.
|
||
3. A writable network partition has been tested.
|
||
4. Concurrent-write conflicts behave predictably.
|
||
5. Application business invariants survive concurrent writes.
|
||
6. WAL backlog cannot silently fill the disk.
|
||
7. Replication errors generate alerts.
|
||
8. DDL has a documented maintenance procedure.
|
||
9. ACE confirms convergence after failures.
|
||
10. Backup restore actually works.
|
||
11. PITR actually works.
|
||
12. RPO and RTO have been measured.
|
||
13. High-load and long-duration tests pass.
|
||
14. Security/TLS/authentication have been verified.
|
||
15. Upgrade and rollback procedures have been rehearsed.
|
||
16. Every failure has a written recovery runbook.
|
||
|
||
If those pass repeatedly, then you're getting into **actual production-grade active-active territory**, rather than simply having a working three-node Spock cluster. |