Book Image

PostgreSQL 11 Administration Cookbook

By : Simon Riggs, Gianni Ciolli, Sudheer Kumar Meesala
Book Image

PostgreSQL 11 Administration Cookbook

By: Simon Riggs, Gianni Ciolli, Sudheer Kumar Meesala

Overview of this book

PostgreSQL is a powerful, open source database management system with an enviable reputation for high performance and stability. With many new features in its arsenal, PostgreSQL 11 allows you to scale up your PostgreSQL infrastructure. This book takes a step-by-step, recipe-based approach to effective PostgreSQL administration. The book will introduce you to new features such as logical replication, native table partitioning, additional query parallelism, and much more to help you to understand and control, crash recovery and plan backups. You will learn how to tackle a variety of problems and pain points for any database administrator such as creating tables, managing views, improving performance, and securing your database. As you make steady progress, the book will draw attention to important topics such as monitoring roles, backup, and recovery of your PostgreSQL 11 database to help you understand roles and produce a summary of log files, ensuring high availability, concurrency, and replication. By the end of this book, you will have the necessary knowledge to manage your PostgreSQL 11 database efficiently.
Table of Contents (19 chapters)
Title Page
Copyright and Credits
About Packt
Contributors
Preface
Index

OmniDB


OmniDB is designed to access PostgreSQL, MySQL, MariaDB, and Oracle in one interface, though it makes sure it provides full features for the PostgreSQL database.

OmniDB is developing quickly, with monthly feature releases, so I recommend that you check out the latest information at https://omnidb.org/.

OmniDB provides a very responsive interface and is designed with full security in mind. It can be used as a desktop application and it can also be served using a web server, to be accessed by the web browser of your choice.

How to do it…

OmniDB has the standard tree-view browsing interface, with multi-tab access for each database server you access. It's easy to be connected to multiple PostgreSQL, MySQL, and Oracle database servers at the same time:

OmniDB has a SQL editor that has code completion and debugging. The EXPLAIN ANALYZE output is colored to highlight the areas of the plan that take the most time:

Or, if you prefer the command-line feel, try the Console tab:

You can also visualize the query plan:

Administrators in OmniDB can manage users graphically. The interface gives you the ability to add, edit, and remove users, along with the ability to make someone a superuser. These users can then create connections to PostgreSQL, MySQL, MariaDB, and Oracle—all managed through a unified web page. Connections can also be made via SSH tunnels:

 

In order to ease the process of developing code in PL/pgSQL, OmniDB provides a powerful, full-featured debugger. The debugger works as an inner tab of the SQL Editor and provides insights into parameters, variables, result, messages, and statistics in five tabs:

Another useful feature in OmniDB is the monitoring dashboard. The dashboard gives you real-time statistics of important metrics you might want to monitor, such as system Memory Usage, CPU Usage, and Locks:

OmniDB has been designed to be a flexible and an extensible tool. Though it comes with several default charts, you can use Python and JSON to write new ones or use the existing ones as templates to enhance and expand. OmniDB provides a plugin API, allowing users to write and distribute their own plugins for expanded capabilities.

See also

You may also be interested in commercial tools of various kinds for PostgreSQL. A full listing is given in the PostgreSQL software catalog at http://www.postgresql.org/download/products/1.