PostgreSQL Tool Triage

[#1]

In the recent times, one of the frequent questions regarding PostgreSQL is which tools are good with PostgreSQL. With the growing functionality of PostgreSQL, the number of vendors are willing to produce tools for PostgreSQL. There are lot of tools for management, development, data visualization and the list if growing. Here, I'm listing a few tools that might be of interest to Thingworx users.

psql terminal:

The psql client is a command-line client distributed with PostgreSQL, often called as interactive terminal. psql is a simple yet powerful tool with which you can directly interface with the PostgreSQL server. The psql client comes default with the PostgreSQL database.

Key features:

• Issue queries either through commands or from a file.
• Provides shell-like features to automate tasks.

For more information, refer http://www.postgresql.org/docs/9.5/static/app-psql.html

pgAdmin III:

pgAdmin III is a GUI based administration and development tool for PostgreSQL database. It delivers the needs of both admin and normal users from writing simple SQL queries to developing complex databases.

Key features:

• Open source and cross-platform support.
• No additional drivers are required.
• Supports more than 30 different languages.

Note: pgAdmin III comes default with postgreSQL9.4 installer. For more information, refer http://www.pgadmin.org/download/

phpPgAdmin:

phpPgAdmin is a web-based client for managing PostgreSQL databases. It provides the user with a convenient way to create databases, create tables, alter tables and query the data using SQL.

Key features:

• Open source and supports PostgreSQL 9.x.
• Requires webserver.
• Administer multiple servers.
• Supports the slony master-slave replication engine.

For phpPgAdmin download: http://phppgadmin.sourceforge.net/doku.php?id=download

TeamPostgreSQL:

TeamPostgreSQL is a browser-based tool for PostgreSQL administration. Using TeamPostgreSQL, database objects can be accessed from anywhere in the web browser.

Key features:

• Open source and cross-platform support.
• Supports SSH for both the web interface and the database connections.
• GUI with tabbed SQL editors.

For TeamPostgreSQL download: http://www.teampostgresql.com/download.jsp

Monitoring Tools

pgBadger:

pgBadger is a PostgreSQL log analyzer for generating reports from the PostgreSQL log files. It is built in Perl language and uses a javascript and bootstrap libraries. Often seen as a replacement for pgfouine log analyzer.

Key features:

• Open source community project.
• Autodetects postgreSQL log file formats (stderr, syslog or csvlog).
• Provides SQL queries related reports and statistics.
• Can also set limits to only report errors.
• Generates Pie charts and Time based charts.

For more information, refer http://dalibo.github.io/pgbadger/.

Git download: https://github.com/dalibo/pgbadger/releases

PostgreStats:

Postgrestats is a software that has automated scripts to easily view statistics such as commits, rollbacks, user inserts, updates and deletes in a time-based intervals. Postgrestats gets installed and executes on the database server, it customizes the main conf file. Postgrestats also provides an enterprise application for Replication mode and High Availability.

Key features:

• Open source and easy-to-setup installation. 
• Take a snapshot report based on time intervals.
• Optional email-on-update.
• Text file Data storage.
• Also provides enterprise application, PostgreStats Enterprise.

For more information, refer: http://www.postgrestats.com/subs/docs.html

Slemma:

Slemma is a collaborative, data visualization tool for PostgreSQL database. Slemma allows database connections with a near to one-click integration and can generate a dashboard from files. Slemma comes with a commercial license with a $29 per user per month pricing.

Key features:

• Create charts and interactive dashboards by selecting tables.
• Non-developers can easily create visualizations (with no coding).
• Email dashboards automatically to clients or your entire team.

For more information, refer https://slemma.com/

Ubiq:

Ubiq is a web-based buisness intelligence and reporting tool for PostgreSQL server. Ubiq creates reports and online dashboards, providing the feature to export in multiple formats. Ubiq is distributed with a commercial license.

Key features:

• Drag & drop interface to create interactive charts, dashboards and reports.
• Apply powerful filters and functions to the data.
• Share your work and schedule email reports.

For more information, refer http://ubiq.co/tour