EnterpriseDB builds installers for Windows and desktop versions of Linux. They offer both 32-bit and 64-bit versions for each OS.
The installers are easy to use. They come packaged with PgAdmin (PostgreSQL 9.6+ come with pgAdmin4 while older versions come with pgAdmin3) and a stack builder from which you can install add-ons like JDBC, .NET drivers, Ruby, PostGIS, phpPgAdmin, and pgAgent.
EnterpriseDB has two PostgreSQL offerings: the official, open source edition of PostgreSQL, dubbed the Community Edition; and its proprietary edition, called Advanced Plus. The proprietary fork offers Oracle compatibility and enhanced management features. Don’t get confused between the two when you download installers. In this book, we focused on the official PostgreSQL, not Postgres Plus Advanced Server; however, much of the material applies to Postgres Plus Advanced Server.
BigSQL is an open source PostgreSQL distribution, largely funded by the company OpenSCG. The BigSQL distribution is similar to EnterpriseDB and has installers for 64-bit versions of Windows, Mac, and Linux.
It is newer than the EnterpriseDB distribution and targets interoperability, DevOps, and Big Data. As such, it includes extensions you wouldn’t commonly find in other distributions. It is packaged with pgTSQL, a procedural language that emulates Microsoft SQL Server’s Transact-SQL stored procedure language, and lots of goodies for benchmarking and monitoring like pgBadger.
You’ll also find other enhancements like PostGIS (including ogr_fdw), many other FDWs such as hadoop_fdw, cassandra_fdw, oracle_fdw, and various PLs.
Like EnterpriseDB, BigSQL has its own installer system. The
installer can be triggered via a web interface or via the shell
command-line tool they call pgc, which stands for “pretty good
command-line.” The pgc package management tool follows the same pattern as
Linux yum, apt-get, etc., even on Windows. So to install new packages,
start by opening up a shell prompt and changing the directory to the
folder where you installed BigSQL.
To update your local list of packages and see list of packages:
pgc update pgc list
The output will show something like:
Category | Component | Version | ReleaseDt | Status | Cur? PostgreSQL pg92 9.2.21-1 2017-05-11 1 PostgreSQL pg93 9.3.17-1 2017-05-11 1 PostgreSQL pg94 9.4.12-1 2017-05-11 1 PostgreSQL pg95 9.5.7-1 2017-05-11 1 PostgreSQL pg96 9.6.3-1 2017-05-11 Installed 1 Extensions cassandra_fdw3-pg96 3.0.1-1 2016-11-08 1 Extensions hadoop_fdw2-pg96 2.5.0-1 2016-09-01 1 Extensions oracle_fdw1-pg96 1.5.0-1 2016-09-01 1 Extensions orafce3-pg96 3.3.1-1 2016-09-23 1 Extensions pgaudit11-pg96 1.1.0-2 2017-05-18 1 Extensions pgpartman2-pg96 2.6.4-1 2017-04-15 1 Extensions pldebugger96-pg96 9.6.0-1 2016-12-28 1 Extensions plprofiler3-pg96 3.2-1 2017-04-15 1 Extensions postgis23-pg96 2.3.2-3 2017-05-18 Installed 1 Extensions setuser1-pg96 1.2.0-1 2017-02-23 1 Extensions tds_fdw1-pg96 1.0.8-1 2016-11-23 1 Servers pgdevops 1.4-1 2017-05-18 Installed 1 Applications backrest 1.18 2017-05-18 1 Applications ora2pg 18.1 2017-03-23 1 Applications pgadmin3 1.23.0a 2016-10-20 Installed 1 Applications pgagent 3.4.1-1 2017-02-23 1 Applications pgbadger 9.1 2017-02-09 1 Frameworks java8 8u121 2017-02-09 1 Frameworks perl5 5.20.3.3 2016-03-14 1 Frameworks python2 2.7.12-1 2016-10-20 Installed 0 Frameworks tcl86 8.6.4-1 2016-03-11 1
To install the binaries for a package:
pgc install pgdevops
The pgdevops package is a web-based administration tool that includes pgadmin4 and the ability to install and monitor bigsql packages.
After you install it, you would do:
pgc init pgdevops pgc start pgdevops
The default port it installs on is http://localhost:8051.
To upgrade an existing package, use pgc upgrade instead
of pgc install.
To help you try out different versions of PostgreSQL on the same machine or run it from a USB device, both EnterpriseDB and BigSQL offer standalone setups. Read Starting PostgreSQL in Windows without Install for guidance on EnterpriseDB. For BigSQL, read Installing pgDevOps.
Most Linux/Unix distributions offer PostgreSQL in their main repositories, although the version might be outdated. To compensate, many people use backports, which are alternative package repositories offering newer versions.
For adventurous Linux users, download the latest PostgreSQL, including the developmental versions, by going to the PostgreSQL Yum repository. Not only will you find the core server, but you can also retrieve popular add-ons. PostgreSQL developers maintain this repository and release patches and updates as soon as they are available. The PostgreSQL Yum repository generally maintains updated packages for the newest stable PostgreSQL for 2−4 versions of CentOS, RedHat EL, Fedora, Scientific Linux, Amazon AMI, and Oracle Enterprise. If you have older versions of the OS or still need older PostgreSQL versions that have reached EOL, check the documentation to see what repository still maintains. For detailed installation instructions using YUM, refer to the Yum section of our PostgresOnLine journal site.
You can install the latest stable and development versions of PostgreSQL on both Debian and Ubuntu from the apt-postgresql repository. apt_postgresql is a repository, similar to yum postgresql, that is maintained by the PostgreSQL development group. The latest stable version is generally also available via the default Ubuntu and Debian repos. A typical installation command looks like:
sudo apt-get install postgresql-9.6
If you plan to compile add-ons you don’t find listed in the repo, you need to also install the postgresql-server-dev:
sudo apt-get install postgresql-server-dev-9.6
If your repository doesn’t have the latest version of PostgreSQL, try visiting the Apt PostgreSQL packages for the latest stable and beta releases. It also offers additional packages such as PL/V8 and PostGIS. It generally supports the latest two or three versions of Debian and Ubuntu.
FreeBSD is a popular platform for PostgreSQL. You can find the latest versions of PostgreSQL at FreeBSD and install it via the FreeBSD ports package management system.
We’ve seen a variety of ways to install PostgreSQL on Macs. Both EnterpriseDB and BigSQL offer an installer. The Homebrew package manager is gaining popularity and attracts advanced Mac users. Postgres.app is a variant distributed by Heroku that is very popular with novice users. The long-standing MacPorts and Fink distributions are still around. We do advise against mixing installers for Mac users. For instance, if you installed PostgreSQL using BigSQL, don’t go to EnterpriseDB to get add-ons.
The following list describes each of these options:
EnterpriseDB maintains an easy-to-use, one-step installer for macOS. PgAdmin comes as part of the installer. For add-ons, EnterpriseDB offers a stack builder program, from which you can install popular extensions, drivers, languages, and administration tools.
BigSQL maintains an easy-to-use, one-step installer for macOS 64-bit users. For add-ons, BigSQL offers a command-line tool called pgc and a pgDevops web browser interface, which we covered in “Windows and Desktop Linux” and from which you can install popular extensions, drivers, languages, and administration tools. BigSQL currently includes PL/V8 for non-Windows.
Homebrew is a macOS package manager for many things PostgreSQL. PostgreSQL, Homebrew, and You provides instructions for installing PostgreSQL using Homebrew. You’ll find other articles at the Homebrew PostgreSQL Wiki.
Postgres.app, distributed by Heroku, is a free desktop distribution touted as the easiest way to get started with PostgreSQL on the Mac. It usually maintains the latest version of PostgreSQL bundled with popular extensions such as PostGIS, PL/Python, and PLV8. Postgres.app runs as a standalone application that you can stop and start as needed, making it suitable for development or single users.
MacPorts is a macOS package distribution for compiling, installing, and upgrading many open source packages. It’s the oldest of the macOS distribution systems that carries PostgreSQL.
Fink is a macOS/Darwin packagedistribution based on the Debian apt-get installation framework.