Introduction to PostgreSQL, Database Management and ClusterControl

With the latest release of ClusterControl we’ve introduced a range of key new features to automate and manage PostgreSQL (alongside MySQL and MongoDB).

PostgreSQL is not only the world’s most advanced open source database (in its own words), but it is also currently the second most popular one after MySQL and before MongoDB (according to DB-Engines).

We’ve been working closely with a partner from the Netherlands, OptimaData, in the past few months to bring users the good news about open source database automation and management.

They happen to have a particular affection for PostgreSQL, so we’ve asked them to contribute to today’s blog post, which is all about singing the praises of PostgreSQL and recommendations on which systems or tools to use to automate and manage it.

We’ll start with the basics though and with a bit of an introduction to PostgreSQL, which though it is highly popular, somehow seems to be less talked about than its most direct “competitors” MongoDB and MySQL.

Introduction to PostgreSQL and Recommendations – by Martijn Wallet, OptimaData

PostgreSQL is an advanced SQL database server, which is available on a wide range of platforms. It has an enviable reputation for performance, stability, and a wide range of advanced features, which have been developed over the course of more than 20 years. With that, PostgreSQL is one of the oldest open source projects in existence, completely free to use, and developed by a very diverse, worldwide community. And best of all, it just works!

One of its key benefits is that it is open source, meaning that customers and users have a very permissive license to install, use, and distribute PostgreSQL without paying anyone any fees or royalties. On top of that, PostgreSQL is well-known as a database that stays up for long periods, and requires little or no maintenance in many cases. As a result, PostgreSQL provides a very low total cost of ownership.

Originally developed by the Database Research Group at the University of California, Berkeley, PostgreSQL is now developed and maintained by a huge army of developers and contributors. Many of those contributors have full-time jobs related to PostgreSQL, working as designers, developers, database administrators, and trainers. Some, but not many, of those contributors work for companies that specialize in support for PostgreSQL. No single company owns PostgreSQL, nor are you required (or even encouraged) to register your usage. In other words, it is truly an open source project, in the best sense of the term.In the early days, when PostgreSQL was still a research database, the focus was solely on the cool new features. Over the last 15 years, enormous amounts of code have been rewritten and improved, giving us one of the most stable and largest software servers available for operational use.

PostgreSQL is a general-purpose database management system, which offers users many ways to work. You define the database that you would like to manage with it. You can use a normalized database model, augmented with features such as arrays and record subtypes, or use a fully dynamic schema with the help of JSONB and an extension named hstore. PostgreSQL also allows you to create your own server-side functions in any of a dozen different languages.

PostgreSQL is highly extensible, so you can add your own data types, operators, index types, and functional languages. You can even override different parts of the system using plugins to alter the execution of commands or add a new optimizer. All of these features offer a huge range of implementation options to software architects and many ways to keep out of trouble when building applications and maintaining them over long periods of time.

In my opinion for PostgreSQL the following features are key:

• Excellent SQL standards compliance
• Client-server architecture
• Highly concurrent design where readers and writers don’t block each other
• Highly configurable and extensible for many types of applications
• Excellent scalability and performance with extensive tuning features
• Support for many kinds of data models: relational, document (JSON and XML), and key/value

What Makes PostgreSQL Different From Other Databases?

The PostgreSQL project focuses on the following objectives according to its website:

• Robust, high-quality software with maintainable, well-commented code
• Low maintenance administration for both embedded and enterprise use
• Standards-compliant SQL, interoperability, and compatibility
• Performance, security, and high availability

About PostgreSQL and MySQL

What surprises many people is that PostgreSQL’s feature set is more comparable with Oracle or SQL Server than it is with MySQL. The only connection between MySQL and PostgreSQL is that these two projects are open source; apart from that, the features and philosophies are almost totally different.

One of the key features of Oracle, since Oracle 7, has been snapshot isolation, where readers don’t block writers and writers don’t block readers. You may be equally surprised to learn that PostgreSQL was the first database to be designed with this feature, and it offers a complete implementation. In PostgreSQL, this feature is called Multiversion Concurrency Control (MVCC).

Who is Using PostgreSQL?

Prominent users include Adyen, Apple, BASF, Genentech, Heroku, IMDB. com, Skype, McAfee, NTT, The UK Met Office, and The U. S. National Weather Service. 5 years ago, PostgreSQL received well in excess of 1 million downloads per year, according to data submitted to the European Commission, which concluded that, “PostgreSQL is considered by many database users to be a credible alternative.”

Main Advantages of PostgreSQL

PostgreSQL offers many advantages for users and businesses over other database systems.

Immunity to over-deployment

Over-deployment is what some proprietary database vendors regard as their #1 licence compliance problem. With PostgreSQL, no-one can sue our customers for breaking licensing agreements, as there is no associated licensing cost for the software.

This has several additional advantages:

• More profitable business models with wide-scale deployment
• No possibility of being audited for license compliance at any stage
• Flexibility to do concept research and trial deployments without needing to include additional licensing costs

Better support than the proprietary vendors

There is a vibrant community of PostgreSQL professionals and enthusiasts that users can interact with and get support from. OptimaData is also a good option 🙂

Legendary reliability and stability

Unlike many proprietary databases, it is extremely common for companies to report that PostgreSQL has never, ever crashed for them in several years of high activity operation. Not even once. It just works.

Extensible

The source code is available to all at no charge. If users have a need to customise or extend PostgreSQL in any way then they are able to do so with a minimum of effort, and with no attached costs. This is complemented by the community of PostgreSQL professionals and enthusiasts around the globe that also actively extend PostgreSQL on a daily basis.

Cross platform

PostgreSQL is available for almost every brand of Unix (34 platforms with the latest stable release), and Windows compatibility is available via the Cygwin framework. Native Windows compatibility is also available with version 8.0 and above.

Designed for high volume environments

It is possible to make use of a multiple row data storage strategy called MVCC to make PostgreSQL extremely responsive in high volume environments. The leading proprietary database vendors use this technology as well, for the same reasons.

Why Severalnines and ClusterControl for PostgreSQL?

I’ve been following Severalnines a couple of years and along that way I discovered the maturity of their product, ClusterControl. All kinds of companies find their way to OptimaData, for example companies which are operating several database types that all need attending to.

For such companies ClusterControl is a very interesting solution to consider as it automates a lot of important tasks. But also at larger companies like VidaXL with a few dozen database nodes the automatic recovery features for clusters are a great enrichment for their database operations.

The newest additions in release 1.4.2 of ClusterControl for PostgreSQL are a great step forward. The automated failover handling is from my point of view a key feature and for me an important reason to advise ClusterControl to our customers. But also the deployment of an entire replication topology through the new wizard is good consideration. I have been using it a few times already and it’s a real advantage compared to other deployment methods. Also the monitoring has improved compared to earlier versions of ClusterControl.

Of course, there is always something more to wish for and I can’t wait to see what’s in the next release of ClusterControl.

For automation and management therefore, we recommend ClusterControl for PostgreSQL to customers and users for the following features:

• ClusterControl for PostgreSQL
  • Manage All Your Servers From a Single Console
  • Automate Failover Handling
  • Stream Replication
  • Monitor performance
• Streaming Replication
  • Easily deploy and setup master/slave replication setups
• Performance Monitoring
  • Monitor queries and detect anomalies, built-in and custom advisors
• Automated Day to Day Tasks
  • Manage configurations, schedule backups, encrypt client/server traffic
• Configuration Management
  • Automatically manage and provision configurations for your servers
• Automated Failover Handling
  • Detect master failures and automatically promote new master

And in addition to ClusterControl, we also like to recommend the following tools:

• pgAdmin, a database management tool for database administrators;
• pgAgent, job scheduling agent for the planning of complex tasks;
• Repmgr, replication and failover manager for PostgreSQL server clusters;
• pgBouncer, connection pooler for setting up high redundant environments;
• Barman, Backup and Recovery Manager for the planning of backup and disaster recovery;
• pgAudit, detailed session and/or object audit logging via the standard PostgreSQL logging facility;
• PostGIS, provides spatial objects for the PostgreSQL database, allowing storage and query of information about location and mapping;
• PgBadger, a fast PostgreSQL log analysis report;
• PgWatch2, flexible self-contained PostgreSQL metrics monitoring/dashboarding solution.

Additional useful links

GUI database design and administration tools

There are many high-quality GUI Tools available for PostgreSQL from both open source developers and commercial providers. A list is available on the wiki that functions as a community guide to PostgreSQL GUI Tools.

Technical Features

Please see the Feature Matrix for a summary of PostgreSQL’s features

One last tidbit of information I’d like to share: when PostgreSQL was first developed, it was named Postgres, and therefore many aspects of the project still refer to the word “postgres”; for example, the default database is named postgres, and the software is frequently installed using the postgres user ID. As a result, people shorten the name PostgreSQL to simply Postgres, and in many cases use the two names interchangeably. And for those you hesitate:

PostgreSQL is pronounced as “post-grez-q-l”. Postgres is pronounced as “post-grez.”

We hope you enjoyed the read! If you have any questions or comments, do share them in the comments box below. We’ve referred to postgresql.org (thank you) for some of the content in this blog.