PostgreSQL management and monitoring

Question

Moving from Oracle to PostgreSQL, what (preferably free/OSS) software/tools are available that replace Oracle Enterprise Manager? I'm aware of pgAdmin, as well as some tools bundled into EnterpriseDB's Postgres Plus AS, but am looking for more context and a better overview of the PostgreSQL ecosystem.

Specifically I'm interested in tools to help:

• Alert db administrators to problems with both configuration and performance
• Monitoring of overall system performance
• General performance tuning
• Hinting support (my understanding is that PostgreSQL typically frowns on hinting)
• Disk management (ease in growth)
• Backup/restore beyond pg_dump and pg_restore

Any thoughts? This seems like something lots of folks may have been through before.

Answer 1

Postgres comes with excellent command-line tools, and they're the focus of the core developer team. I personally leverage those via custom site-specific scripts for db admin, upgrade, replication. For monitoring I use nagios to script psql to query vitals.

That said, there are some other options on the GUI-side beyond pgAdmin:

SQL Maestro

phppgadmin

I don't think any of these have all of the features you listed, but they might be a good jumping off point. I doubt you will find an open source tool that really competes head-to-head with Oracle Enterprise Manager. On the commercial-side, there are also additional options:

Navicat

RazorSQL

MS SQL Manager for PostgreSQL

This wiki page also has links to many other postgres tools:

Community Guide to PostgreSQL GUI Tools

Being a CLI-zealot myself I cannot given any specific recommendations but hopefully this is helpful.

Answer 2

From the management tool perspective caseman has already compiled a nice list, the PostgreSQL wiki also has a few additional ones on their GUI Database Design Tools page.

For Monitoring/Tuning it's a bit more complicated, there are many tools but no thorough solution. I started compiling a list, again in the Postgres Wiki: Monitoring tools.

Being unhappy with the status quo I teamed up with a colleague and we started working on our own solution called pganalyze, trying to help (parttime-)DBAs to understand what's going on in the database. But we also can only focus on a small subset of the problem area for the time being...