When considering how to deploy PostgreSQL one must consider the pros and cons of different deployment methods of PostgreSQL. The three most common on-prem/full OS capable deployments are your Linux distribution, packages from postgresql.org, and EnterpriseDB PostgreSQL. We will not be discussing managed cloud options as all popular cloud options are forks, they are not PostgreSQL (though often they are compatible).
Linux Distributions
The majority of people running Postgraduate are running Linux and likely Debian, Ubuntu or some version of RHEL (whether the free versions or not). The good news is that the PostgreSQL community provides direct support for these distributions. The recommended way to running PostgreSQL is from native packages for your operating system. These packages can be found here:
EnterpriseDB
EnterpriseDB is one of the oldest PostgreSQL companies in existence. In fact in North America there is only one that is older, Command Prompt and EnterpriseDB was once upon a time a client of Command Prompt. They have become one of the largest code contributors to PostgreSQL in the world and with the acquisition of 2ndQuadrant they now hold a brain trust of intellectual property that is second to none in product development for PostgreSQL. This includes fascinating tools such as BDR which provides Multi-Master capabilities for PostgreSQL. Unfortunately, they have chosen to keep BDR closed source which denies a wider community the ability to help them increase the value of the software.
Though closed source, they are the preferred providers of the MacOS and Windows installers and are referenced on Postgresql.org. The advantage to these distributions is that like a Linux distribution they contain a vast array of software to allow the lift to use PostgreSQL to be much less than installing and configuring all the components individually.
EnterpriseDB vs PostgreSQL community
The PostgreSQL community is far larger than EnterpriseDB with a worldwide ecosystem that is second to only Linux. EnterpriseDB does offer some fantastic tools that help solve real problems for PostgreSQL, some Open Source, some not.
Connection Pooling
A connection pooler is a piece of software that you place in front of a database to manage connections. This is sometimes necessary due to how resource intensive it is to drop and create database connections. As PostgreSQL is process based, this is even more expensive (though it is true that it has become much more efficient in recent releases). The use of a connection pooler, especially if on a different machine than the database can greatly increase the efficiency of app servers and provide more resources for use on the database server itself.
EDB
- Open Source: Yes
- Pgbouncer
Ecosystem/Community standard
High Availability
There is no question that high availability in today’s world is one of the most important features of a database. The only feature more important is a proper resiliency and data retention capability (backups). The three options offered below are not the only options available (by far). Many middleware stacks offer HA built in for application resilience. There is also proxy software that can provide similar capabilities with the added benefit of features such as Read/Write split.
EDB
- Open Source: No
- Failover Manager
Ecosystem/Community standard
- Microsoft/Citus
- Open Source: Yes
- pg_auto_failover
- Zolando
- Open Source: Yes
- patroni
Replication Management
Replication management is a difficult category to define. On the one hand it means utilities to easily manage the replication features of PostgreSQL. On the other hand, PostgreSQL provides everything needed to manage such features. One could argue that this doesn’t need a separate category outside of perhaps the monitoring of the features which PostgreSQL does not adequately support.
EDB
- Open Source: Yes
- Replication Manager
Ecosystem/Community standard
- EnterpriseDB
- Open Source: Yes
- Replication Manager
Backups
There are few tools in a database suite more important than backups. Backups are the lifeblood of making sure a database is not only available but resilient. The PostgreSQL project provides three utilities for backups: pg_dump, pg_dumpall, and pg_basebackup. They are all good tools but they are limited when discussing larger databases and the need for features such as differential or incremental backups.
EDB
- Open Source: Yes
- Barman
Ecosystem/Community standard
- Open Source: Yes
- PgBackrest
Management and Monitoring
When PostgreSQL it is important to properly manage and monitor the server. There are a number of ways to do this. A lot of experienced DBAs prefer using a direct SQL console such as psql. However, a lot of newer users prefer systems such as PgAdmin for administration, and Zabbix or Nagios for monitoring. There is definitely no right answer on this particular topic as the right tool will integrate into existing workflows or operation centers. Command Prompt utilizes Zabbix to provide enterprise class monitoring and alerting for its clients. Further the majority of Command Prompt operations staff is more comfortable with psql than a graphical tool such as PgAdmin.
EDB
- Open Source: No
- PostgreSQL Enterprise Manager
- Note: It is based on the already extremely capable PgAdminIV
Ecosystem/Community Standard
- Open Source: Yes
- PgAdminIV
Command Prompt understands that sometimes a closed source version of software provides better features than the Open Source counterparts. However, it is Command Prompt’s considerable experience over 25 years of providing expert PostgreSQL professional services and support that there are a minute number of closed source opportunities for PostgreSQL. The ecosystem and community is vibrant and continuing to solve the problems that enterprises face in a collaborative and Open Source way.
