Windows and Desktop Linux

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.

CentOS, Fedora, Red Hat, Scientific Linux

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.

Debian, Ubuntu

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

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.

macOS

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.