Migration and hardening of a PostgreSQL + Patroni production cluster to Ubuntu with replication restoration and backup setup

Initial situation
Currently using PostgreSQL 14 in production for SaaS (multi-tenancy, up to 1200 rps at peak, ~900 GB of data). Cluster 3 nodes (2x primary/standby + witness) on Ubuntu 20.04 in Hetzner Cloud, managed by Patroni+etcd. After a series of incidents, we noticed: periodic failovers with split-brain, replicas lagging behind for up to 20-40 minutes, barman backups were sometimes broken, and OS and Postgres updates were delayed. The plan is to migrate to Ubuntu 22.04 and PostgreSQL 16 with minimal downtime, plus bringing order to observability and safe operation.

Technical environment
- 3 VM (Hetzner Cloud), NVMe, 1 Gbps network
- Ubuntu 20.04 -> 22.04
- PostgreSQL 14 -> 16
- Patroni, etcd, HAProxy
- barman for PITR
- Prometheus + node_exporter is already there, postgres_exporter is configured in some places
- Access: SSH via key, idle time limit - no more than 10 minutes at the final switchover

Task and expected result
1) Analyze the current HA configuration (Patroni/etcd/HAProxy/Postgres) and identify the causes of split-brain and replication lags. Record the problems found and proposed changes in a short report.
2) Prepare a plan and perform a migration to PostgreSQL 16 and Ubuntu 22.04, preserving the Patroni cluster and transparent switching for applications via HAProxy.
3) Configure backups and recovery verification so that PITR and regular validation of backups are guaranteed.
4) Strengthen security and operational reliability: limiting network access, correct kernel/limits parameters, log rotation, basic hardening measures without fanaticism.
5) Bring monitoring to a state where you can measurably see replication lag, failover status, disk fullness, long-running transactions and WAL growth.

Restrictions
- You cannot change the application and database schema (only the server part and Postgres/Patroni/HAProxy parameters).
- You cannot use managed DB, everything remains on the current provider.
- Access to production is limited: work in prime time is only reading and diagnostics, changes and switching - according to an agreed window.
- The documentation must be understandable to the engineer who will maintain the system after completion of the work.

Acceptance criteria (measurable)
- The final switch to a new primary takes 10 minutes of total impact on the service.
- Replication in normal mode: lag standby < 5 seconds at average load (according to metrics) and without WAL accumulation.
- Split-brain has been eliminated: correct quorum/ttl/loop_wait/retry_timeout settings and checking the network/node loss scenario on the stand or in an agreed window.
- Backups: daily full or basebackup and continuous archiving WAL, successful PITR test procedure with restoration to a separate VM before a specified time.
- Monitoring: Postgres metrics are available in Prometheus (including replication lag, WAL, long running queries), there are at least 5 alerts (disk, lag, node unavailability, increasing errors, stopping backups).
- Transfer of artifacts: current Patroni/HAProxy/Postgres configs, instructions for updating and disaster recovery, list of changes made.

Execution steps
- Audit and recording of the current state, collecting metrics and logs, diagnosing incidents.
- Design of target scheme and migration plan with rollback.
- Deployment of new VMs, transfer of configurations, configuration of Patroni/etcd/HAProxy.
- Data migration (pg_basebackup/pg_upgrade or agreed upon method), running checks.
- Barman setup and PITR test.
- Setting up monitoring and alerts, final verification and transfer of documentation.

Professional tools you'll need
- patronictl, etcdctl, psql, pgBackRest or barman, pgbench (or an analogue for running), Prometheus exporters, tcpdump/ss, journald/rsyslog, Ansible (preferably) for reproducibility.

Important
What is needed is hands-on work in the infrastructure, with analysis and careful migration, and not a general consultation.