Top Tools to Manage Postgres in an Enterprise: Administration, Performance, High Availability, and Migration

November 15, 2024

This blog post lists tools for managing Postgres in the enterprise. While this is a big topic, there are common denominators that we have seen in many Postgres projects.

Our list is not exhaustive, but it provides a starting point.

PostgreSQL Tools

We list PostgreSQL tools for development and administration, performance tuning and monitoring, high availability and disaster recovery, connection pooling and query routing, and data migration.

Not all the tools listed below are part of the EDB support scope. Many of them are open source tools with varying degrees of support and maintenance. (**) identifies tools that are part of EDB’s support scope.

Development and Administration

Tool Description License
psql (**) Postgres core client PostgreSQL
pgAdmin (**) Desktop or web-based; browse and modify a database schema, run queries, debug stored procedures, and more PostgreSQL
Toad Edge for Postgres A new version of the classic DBA tool for Oracle Quest proprietary license
sqlSmith A fuzz testing tool for developers, sqlSmith generates random queries GPL V3

Performance Tuning and Monitoring

Tool Description License
Postgres Enterprise Manager (**) All the functionality of pgAdmin, plus 24x7 monitoring and alerting and various enterprise management tools. Includes Tuning Wizard, Wait State Analyser for EDBAS, a SQL Profiler, and monitoring dashboards. EnterpriseDB proprietary license
Pg_stat_statements (**) A Postgres extension that tracks execution statistics of all SQL statements executed by a server. PostgreSQL
Auto_explain (**) A Postgres extension that logs execution plans of slow queries automatically. PostgreSQL
pgBadger Very popular Postgres log file analyzer with a graphical output. Helps find long-running queries, demanding workloads, etc. PostgreSQL

Disaster Recovery and High Availability

Tool Description License
EDB Postgres Distributed (**) Achieve up to five 9s of Postgres extreme high availability – whether on-premises, in the cloud, or in a hybrid architecture. EnterpriseDB proprietary license
EDB Postgres Failover Manager (**) Cluster-aware database availability monitoring, automatic failover, and support of manual switchover in support of maintenance activities. EnterpriseDB proprietary license
EDB Backup And Recovery Tool (**) Block-level incremental backup for Postgres. EnterpriseDB proprietary license
RepMgr Replication Manager for Postgres streaming replication and failover GPL V3
pgBackRest Advanced backup and recovery tool for Postgres MIT license
Barman Backup and recovery tool for Postgres GPL V3
Patroni High availability tool for Postgres, mostly used in containerized deployments MIT license

Connection Management/Connection Pooling

Tool Description License
pgPool-II (**) Connection pooler and query router for Postgres BSD license
pgBouncer (**) Lightweight connection pooler for Postgres BSD license

Migration from Commercial Databases

Tool Description License
EDB Migration Portal (**) Web-based Oracle to Postgres migration tool. Maps DDL, DML, packages, stored procedures, and other proprietary extensions to the SQL standard from Oracle to Postgres. EnterpriseDB proprietary license
EDB Migration Toolkit (**) Command-line tool for migration of DDL, DML, and data from Oracle, SQL Server, Sybase, and MySQL to Postgres EnterpriseDB proprietary license
ora2PG Maps Oracle schemas and data types to Postgres; provides some DML transformation GPL V3
Cybertech migrator Maps Oracle schemas and data types to Postgres, provides DML transformation, provides some PL/SQL transformation and an interface to debug it Cybertech proprietary license

One of the most important lessons learned is: “Don’t go it alone!” Postgres is mature, and in many regards, it behaves like Oracle and SQL Server, but there are significant differences in tooling, management practices, and performance behavior.

The ROI of Postgres projects is very high. Project delays, partial implementations, and substandard performance are the enemies of a timely move to production.

Supported Postgres

We highly suggest that users work with a well-established Postgres company with a staffed 24x7 support team with commercial-grade support SLAs. That support team must be backed by software engineers who are involved in the development of the Postgres database (check the list of committers and contributors at PostgreSQL.org – don’t work with “Postgres companies” that don’t have several full-time team members who are on that list. They will not be able to help you in a timely manner on the rare occasion where Postgres has issues).

EDB Postgres Advanced Server is a managed fork of Postgres. This allows EDB to provide features for customers (Oracle compatibility, Resource Management, Query Hints, PCI-compliant password management, etc.) that may otherwise not be possible in the community process.

Technical Account Management

Technical Account Managers (TAMs) are a staple of the commercial software world, especially for mission-critical infrastructure software. TAMs provide an intimate and knowledgeable link between the customer’s needs and the software provider’s engineering team. They greatly improve communication and help make projects successful, and also act like the customer’s advocate and influence the product roadmap.

DBA Services

Specialized Postgres DBAs monitor and manage Postgres databases in the customer’s data center, in cloud IaaS deployments, or in cloud-based DBaaS projects. Postgres is close to Oracle and SQL Server, but some key management processes, such as bloat and vacuum, or backup/HA, are different. Query tuning and performance management practices also differ significantly – especially when considering the requirements of Tier 1 99.99%+ SLA solutions.

Many customers use DBA services as “training wheels” for their first project; other customers focus their in-house DBAs on innovation and data management and use EDB’s DBAs to keep the lights on.

Consulting and Architecture Advice

Highly performing and reliable Postgres architectures are foundational building blocks if an enterprise wants to transition a significant part of their database estate from commercial databases to open source based ones. Understanding how to use Postgres and how to take advantage of its unparalleled innovations, its flexible data model, extensions, GIS, and document capabilities, is important to achieve the ROI and get value from open source.

The Need for an Integrated Platform

The open source community has provided a plethora of tools and capabilities around Postgres – almost everything from HA to DR and log analyzers, are available as “free” building blocks. However, none of the blocks fit together seamlessly, none of them are on the same release schedule, and they are not covered by the same support SLA. None of that matters if an enterprise can invest enough into many in-house Postgres knowledgeable resources, has plenty of time to mature its Postgres projects, and does not plan to use Postgres for Tier 1 mission-critical applications.

However, that is not acceptable for enterprises that want to take advantage of open source-based technology to reduce cost, drive innovation, and support digital transformation. Those users require:

  • An integrated platform that includes management, monitoring, tuning, HA, and DR tools that fit together seamlessly and create a robust management platform.
  • One integrated release schedule to make sure that the tools work together.
  • An integrated SLA and support structure for the tools and the database. A whole is only as strong as its weakest part.

Conclusion

The enterprise demands imposed on an open source platform, such as Postgres, are in no way different from the demands imposed on closed source software. Increasing use of Postgres for mission-critical apps means that the tooling and the best practices align increasingly with traditional soft practices. Integrated platforms, single vendor support solutions, and the need for an agile roadmap are obvious requirements when enterprises start betting on Postgres.

Share this
What is pgAdmin? chevron_right

pgAdmin is an open source management tool for PostgreSQL, designed to simplify database management tasks like writing SQL queries, managing database objects, and monitoring performance through a web or desktop interface.

How do I connect to a PostgreSQL server using pgAdmin? chevron_right

To connect to a PostgreSQL server in pgAdmin, you must specify the server's IP address or hostname, port number (default is 5432), and your database credentials (username and password) in the server dialog.

What backups tools are available for PostgreSQL? chevron_right

PostgreSQL offers several backup tools, including:

  • pg_dump: A command-line utility for backing up database objects in a variety of formats.
  • pg_restore: Used to restore a PostgreSQL database from an archive created by pg_dump.
  • pgBackRest: A backup and restore tool designed for high performance and reliability.
How does physical backup differ from logical backup in PostgreSQL? chevron_right

Physical backups involve copying the actual database files at the file system level, while logical backups export specific database objects or entire databases into a readable format, such as SQL statements. Each has different use cases – physical backups are more efficient for disaster recovery, whereas logical backups are better for data migrations.

What is the role of psql in PostgreSQL? chevron_right

psql is a terminal-based front-end to PostgreSQL that allows users to interactively type SQL commands and queries, see outputs, and run administrative commands.

What are some common performance tuning tools in PostgreSQL? chevron_right

Performance tuning tools in PostgreSQL include:

  • pg_stat_statements: Captures and reports statistics about all SQL statements executed by a server.
  • pg_stat_activity: Displays information about the current state of all server processes, including currently running queries.
What is the purpose of PostgreSQL extensions? chevron_right

Extensions in PostgreSQL enhance the functionality of the database. Popular extensions include PostGIS for geographic information systems and pgcrypto for cryptographic functions. They allow custom features to be added to PostgreSQL, making it more versatile.

How can I monitor PostgreSQL performance? chevron_right

Monitoring tools include:

  • pgAdmin: Offers dashboards for viewing database metrics.
  • pgBadger: A log analyzer that generates detailed reports based on PostgreSQL log files.
  • pg_stat_user_tables: Provides statistics on user tables, helping to analyze performance.
What is the purpose of WAL (Write-Ahead Logging)? chevron_right

WAL is a PostgreSQL feature that improves reliability by logging changes before they are written to the database files. This ensures that data can be restored in case of a crash, maintaining data integrity and durability.

Can I use PostgreSQL with Docker? chevron_right

Yes, PostgreSQL can be run within Docker containers, allowing for isolated environments and easy deployment. PostgreSQL images are available on Docker Hub, and you can run a SQL client like pgAdmin in a separate container for management tasks.

Optimize Your Database with EDB’s Integrated Tooling and Services

Address a wide variety of needs through enterprise-grade solutions

More Blogs

Explaining ABI Breakage in PostgreSQL 17.1

PostgreSQL comes out with a scheduled major release every year and scheduled minor releases for all supported versions every quarter. But in the November minor releases, two issues caused the...
December 06, 2024