TL;DR: PostgreSQL doesn’t ship with a built-in monitoring suite, backup manager, or HA framework — you assemble the stack yourself from specialised tools.
That’s not a weakness: it means you pick what fits your environment instead of paying for features you don’t use.
This list covers one best-in-class pick per category, with brief notes on when to use the alternatives.
Six categories are non-negotiable in production: HA/failover, backup and PITR, connection pooling, monitoring, query analysis, and log analysis.
Everything else is optional.
I’ve set up PostgreSQL in production environments that came after Oracle RAC, after SQL Server AlwaysOn, and after bare MySQL with no HA at all.
The question I get most often is the same one: what tools do I actually need?
Oracle ships with everything built in — RMAN for backup, Enterprise Manager for monitoring, Data Guard for standby, DBMS_SCHEDULER for jobs.
PostgreSQL doesn’t.
You build the stack from components, and each component does one thing well.
These are the tools I install or recommend on every production PostgreSQL deployment.
Table of Contents
What Tools Do You Need to Run PostgreSQL in Production?
Six categories are non-negotiable: high availability and failover, backup with point-in-time recovery, connection pooling, monitoring, query analysis, and log analysis.
Miss any of the first four and you have a gap that will cost you in production.
Everything in the sections below maps to one of these six, with one primary recommendation per category and clear notes on when the alternatives make more sense.
High Availability — Patroni
Patroni is the standard for PostgreSQL high availability in on-premises and cloud environments.
It manages a cluster of PostgreSQL nodes, handles leader election via an external distributed configuration store (etcd, Consul, or ZooKeeper), and performs automatic failover when the primary node fails.
Switchovers are manual and clean — useful for planned maintenance.
The REST API lets you query cluster state, trigger switchovers, and integrate with external load balancers.
For most on-premises deployments, Patroni with etcd is the right choice.
The community is large, documentation is thorough, and it handles the edge cases that simpler tools miss.
When to use the alternatives:
repmgr — simpler to configure, lower operational overhead; good for smaller environments where the full Patroni/etcd stack is more than needed.
pg_auto_failover — single extension, no external dependencies; the easiest path to automated failover for teams who want something that works without managing etcd.
Stolon — designed for Kubernetes; use it when the client is running PostgreSQL on Kubernetes and wants native integration.
If you’re migrating from Oracle RAC, Patroni is the closest functional equivalent for automatic failover.
It is not active-active — PostgreSQL + Patroni is a primary/standby model with automatic promotion.
For workloads that genuinely required RAC’s active-active read/write across nodes, that is a real architectural difference worth evaluating separately.
Backup and Point-in-Time Recovery — pgBackRest
pgBackRest is the first tool I install on every PostgreSQL server.
It handles full, differential, and incremental backups; parallel backup and restore; WAL archiving; and point-in-time recovery.
It compresses and optionally encrypts backup files, supports local and remote storage, and has clear, well-maintained documentation.
For Oracle DBAs, this is the RMAN equivalent.
PITR works by replaying WAL segments from a backup base, which gives you recovery to any point in time between backups — the same model as Oracle’s SCN-based recovery, expressed differently.
When to use the alternatives:
WAL-G — better for cloud-native environments where backups land in S3, GCS, or Azure Blob Storage; lighter weight than pgBackRest, no configuration file.
Barman — strong option for multi-server environments managed centrally; used widely in enterprises that want a dedicated backup server model.
Do not use WAL-E for new deployments — it has been superseded by WAL-G by the same author.
The one rule that matters above everything else: test your restores.
A backup you have never restored is not a backup — it is a file.
Schedule a monthly restore to a separate server and verify the data.
Connection Pooling — PgBouncer
PgBouncer is not optional in production.
PostgreSQL spawns a new operating system process for every client connection.
At low connection counts this is fine.
At 500 concurrent connections it becomes a performance problem.
At 2,000 it brings the server down.
PgBouncer sits between the application and PostgreSQL and multiplexes many client connections onto a small pool of server connections.
For applications migrating from Oracle — where Oracle’s shared server model or connection pooling via UCP or JDBC handled this transparently — adding PgBouncer is a required migration step, not an optional optimisation.
The configuration choice that matters most is pooling mode:
Transaction mode — a server connection is held only for the duration of a transaction; highest efficiency; breaks applications that use session-level features (prepared statements, advisory locks, SET LOCAL).
Session mode — one server connection per client session; lower efficiency; compatible with everything; use this if transaction mode causes application errors.
Start with transaction mode and switch sections of traffic to session mode only where needed.
pgpool-II is the alternative — it adds read query load balancing across replicas on top of pooling.
Use it only when you need that specific feature; it adds significant operational complexity compared to PgBouncer.
Monitoring — postgres_exporter and pgwatch2
No single tool covers every monitoring setup, so I give two recommendations depending on what the client already has.
postgres_exporter — if the client runs Prometheus and Grafana, this is the standard.
It exposes PostgreSQL metrics in Prometheus format, integrates with existing dashboards, and has community-maintained Grafana dashboard templates ready to import.
pgwatch2 — if the client doesn’t have Prometheus and wants a self-contained monitoring stack, pgwatch2 ships with its own storage (TimescaleDB or PostgreSQL) and pre-built Grafana dashboards.
Lower setup friction than building the Prometheus stack from scratch.
What to monitor regardless of which tool you use:
Active connections vs max_connections.
Replication lag on standbys.
Lock waits and deadlocks.
Autovacuum activity and table bloat.
Long-running queries.
Cache hit ratio.
PMM (Percona Monitoring and Management) is the full-stack option — monitoring, query analytics, and alerting in one package.
Recommend it to clients who want everything pre-configured and don’t have existing Prometheus infrastructure.
Running PostgreSQL in production for the first time after an Oracle migration?
I offer a fixed-fee health check covering HA configuration, backup verification, connection pooling, and monitoring — delivered as a written findings report.
See what the health check covers →
Query Analysis — pgBadger and PEV2
pgBadger is the first tool I run when investigating slow query complaints after a migration.
It parses PostgreSQL log files and generates an HTML report showing the slowest queries, most frequent queries, lock waits, connection activity, and error patterns.
Enable log_min_duration_statement in postgresql.conf to capture slow queries, then run pgBadger against the logs.
The report gives a clear picture of where query time is going within minutes.
PEV2 (Postgres EXPLAIN Visualizer 2) is an online tool that takes the output of EXPLAIN (ANALYZE, BUFFERS) and renders it as an annotated, colour-coded plan diagram.
It makes query plans readable for developers and clients who don’t yet think in PostgreSQL execution plan terms.
Useful for showing clients exactly where a query is losing time and why an index change helps.
HypoPG is a PostgreSQL extension worth knowing for post-migration performance tuning.
It lets you create hypothetical indexes — indexes that exist only in the planner’s view — and run EXPLAIN against them to test whether a new index would help before paying the cost of building it on a large table.
This is particularly useful during the first 30–90 days post-migration when query performance is being tuned against real production workloads.
Extensions Every Production DBA Should Know
These six extensions cover the most common gaps that Oracle DBAs notice after migration.
| Extension | Replaces or covers |
|---|---|
| pg_partman | Automates partition maintenance — create, detach, and drop partitions on schedule; replaces manual Oracle interval partitioning management |
| PGAudit | Detailed session and object-level audit logging; the closest equivalent to Oracle Unified Auditing; required for compliance workloads |
| pg_cron | Run scheduled jobs inside PostgreSQL using cron syntax; replaces Oracle DBMS_SCHEDULER for simple recurring tasks |
| pglogical | Logical replication between PostgreSQL instances; the foundation for zero-downtime migration strategies |
| pg_stat_monitor | Drop-in improvement on pg_stat_statements; adds histograms, query plan capture, and client information |
| plpgsql_check | Static analysis for PL/pgSQL code; run it against converted Oracle stored procedures before deployment to catch errors that only surface at runtime |
GUI and CLI — DBeaver and pgcli
DBeaver is the tool I recommend to any DBA who is moving from Oracle to PostgreSQL and needs to work with both databases simultaneously during the migration.
It is free, open-source, and supports Oracle, PostgreSQL, MySQL, SQL Server, and most other databases from a single interface.
During a migration you are often comparing data between Oracle and PostgreSQL in real time — DBeaver handles this without switching tools.
pgcli is the command-line replacement for psql.
It adds autocompletion for table names, column names, and SQL keywords, syntax highlighting, and formatted output.
For Oracle DBAs used to SQL*Plus with a good prompt, pgcli removes most of the friction of the first weeks at the command line.
For client teams post-migration: pgAdmin is the standard free GUI and most teams already know it.
DataGrip (JetBrains) is the preferred choice for developer-heavy teams — full IDE features, excellent query editor, schema comparison.
One Tool Most DBAs Miss — postgres-checkup
postgres-checkup runs a structured health analysis of a PostgreSQL instance and outputs a detailed Markdown report covering bloat, missing indexes, replication slot risk, autovacuum issues, configuration problems, and more.
I run it at the start of every health check engagement and at the end of every migration before sign-off.
It surfaces issues that would otherwise only become visible under production load: tables with significant bloat, indexes that haven’t been used in months, replication slots that are holding back WAL and could fill the disk.
It takes under ten minutes to run and produces a report you can hand directly to a client.
Frequently Asked Questions
What is the best monitoring tool for PostgreSQL?
For teams running Prometheus and Grafana, postgres_exporter with a pre-built dashboard is the standard.
For teams who want a self-contained monitoring stack without building a Prometheus infrastructure, pgwatch2 ships with built-in storage and dashboards.
For teams who want everything pre-packaged, Percona Monitoring and Management (PMM) is the full-stack option.
Do I need PgBouncer if my application already uses a connection pool?
Yes, in most cases.
Application-level connection pools reduce the number of connections from one application service, but in multi-service architectures each service maintains its own pool.
Total server-side connection count still grows with the number of services and instances.
PgBouncer sits at the database level and caps the total regardless of how many application pools exist upstream.
What replaces Oracle RMAN in PostgreSQL?
pgBackRest is the closest equivalent — it handles full, incremental, and differential backups, WAL archiving, and point-in-time recovery.
For cloud environments, WAL-G is a lighter alternative that archives WAL and backups directly to S3, GCS, or Azure Blob Storage.
Both support PITR, which is the PostgreSQL equivalent of Oracle’s SCN-based recovery.
What replaces Oracle Enterprise Manager in PostgreSQL?
There is no single equivalent — PostgreSQL monitoring is assembled from components.
postgres_exporter feeds metrics into Prometheus and Grafana dashboards; pgBadger analyses slow query logs; pgwatch2 provides a self-contained monitoring stack.
For a closer all-in-one experience, Percona Monitoring and Management (PMM) covers metrics, query analytics, and alerting in a single platform.
In Summary
Six categories, six tools you cannot skip: Patroni for HA, pgBackRest for backup and PITR, PgBouncer for connection pooling, postgres_exporter or pgwatch2 for monitoring, pgBadger for log analysis, PEV2 for query plan visualisation.
The extensions and utilities in the sections above fill the gaps that Oracle DBAs most commonly notice in the first months after migration.
The stack is not complicated — it is just assembled differently from what Oracle DBAs are used to.
Each component does one thing well, and together they cover everything that RMAN, Enterprise Manager, RAC, and DBMS_SCHEDULER provided in a single Oracle licence.
If you are setting up PostgreSQL in production for the first time and want a second opinion on your stack, get in touch →