What Good PostgreSQL Administration Tools Exist?

Question

Does PostgreSQL have good performance or administration tools?

I have been using postgres for quite some time now, but I still do not know a huge amount about ways to optimise and maintain a database, and keep it running well.

I have had a look through the manual, but since it is quite a mature product, I am assuming there are a couple of 'must-have' utilities for it?

Answer 1

I think that PgAdmin is pretty popular. There's also PhpPgAdmin, which is rougly analagous to the very popular phpMyAdmin.

Answer 2

Perhaps not what you're looking for, but plain old psql is excellent. You can tell that the devs really meant for it to be a comfortable place to spend a lot of time.

Answer 3

For general schema navigation DBVisualizer is quite nice.

For performance information there are hardly any complete tools like MySQL Enterprise Monitor; I'd suggest to use pgFouine (already suggested) and any tool that can graph performance data (like Munin for example), collect the relevant information from PostgreSQL (pg_stat_ views/system tables) and the system itself (memory, cpu, disk utiliziation, etc.) and use this as a basis for deducing trends.

This book has a few chapters on PostgreSQL performance; the PostgreSQL Wiki has a list of presentations on this topic.

And for specific questions there's always the pgsql-performance mailinglist and it's comprehensive archive.

Answer 4

this guy has developed quite a few useful tools. take a look at his blog.

Answer 5

For real simple tasks we use phpPgAdmin.

phpPgAdmin is a web-based administration tool for PostgreSQL. It is perfect for PostgreSQL DBAs, newbies and hosting services.

Answer 6

pgFouine

http://pgfouine.projects.postgresql.org/

pgFouine is a great log analyzer that has helped many times in the past. It is really easy to set up too. At one point the project ran on Rails, but I think it's using PHP now.