Getting fancy with PostgreSQL and Homebrew

Hunleyd, Nov 10, 2016, Osx Postgresql Howto

In our previous post, we installed PostgreSQL via Homebrew and got our own little cluster up and running under our userid. That is probably good enough for 90% of the users out there who just want to play with or devel on PostgreSQL, but for those of us who need a little more flexibility in our installs, we’re going to take the next step.

First, if you have any data in your existing cluster that you want to preserve, let’s backup it up. We’re just going to use pg_dumpall here since it’s quick-n-dirty:

$ pg_dumpall -g > my_roles.sql
$ pg_dumpall > my_data.sql

Now, we can stop our cluster:

$ brew services stop postgresql
Stopping `postgresql`... (might take a while)
==> Successfully stopped `postgresql` (label: homebrew.mxcl.postgresql)

And uninstall the current PostgreSQL:

$ brew uninstall postgresql
Uninstalling /usr/local/Cellar/postgresql/9.5.5... (3,154 files, 35.1M)

And remove the remnants of our cluster:

$ rm -rf /usr/local/var/postgres

Now we’ve got a clean slate. So, let’s tell Homebrew to stop using the default PostgreSQL and use Peter Eisentraut’s version (if you don’t know, Peter actually contributes to PostgreSQL):

$ brew tap petere/postgresql
Updating Homebrew...
==> Auto-updated Homebrew!
Updated 1 tap (homebrew/core).
==> New Formulae
[snip]
==> Updated Formulae
[snip]
==> Deleted Formulae
[snip]

==> Tapping petere/postgresql
Cloning into '/usr/local/Homebrew/Library/Taps/petere/homebrew-postgresql'...
remote: Counting objects: 15, done.
remote: Compressing objects: 100% (11/11), done.
remote: Total 15 (delta 9), reused 5 (delta 4), pack-reused 0
Unpacking objects: 100% (15/15), done.
Checking connectivity... done.
Tapped 11 formulae (49 files, 71.7K)

As of this writing, Peter makes PostgreSQL 8.3 and above available. However, only PostgreSQL 9.2 and above are officially supported by the PostgreSQL community. So let’s install the supported versions:

$ for i in $(seq 2 6)
\ do
\ brew install postgresql-9.${i}
\ done
==> Installing postgresql-9.2 from petere/postgresql
==> Tapping homebrew/dupes
Cloning into '/usr/local/Homebrew/Library/Taps/homebrew/homebrew-dupes'...
remote: Counting objects: 42, done.
remote: Compressing objects: 100% (42/42), done.
remote: Total 42 (delta 0), reused 4 (delta 0), pack-reused 0
Unpacking objects: 100% (42/42), done.
Checking connectivity... done.
Tapped 38 formulae (103 files, 121.4K)
==> Installing dependencies for petere/postgresql/postgresql-9.2: gettext, homebrew/dupes/openldap, ossp-uuid, homebrew/dupes/tcl-tk
==> Installing petere/postgresql/postgresql-9.2 dependency: get
==> Downloading https://homebrew.bintray.com/bottles/gettext-0.19.8.1.sierra.bot
######################################################################## 100.0%
==> Pouring gettext-0.19.8.1.sierra.bottle.tar.gz
==> Caveats
This formula is keg-only, which means it was not symlinked into /usr/local.

macOS provides the BSD gettext library and some software gets confused if both are in the library path.

Generally there are no consequences of this for you. If you build your
own software and it requires this formula, you will need to add to your
build variables:

    LDFLAGS:  -L/usr/local/opt/gettext/lib
    CPPFLAGS: -I/usr/local/opt/gettext/include

==> Summary
🍺   /usr/local/Cellar/gettext/0.19.8.1: 1,934 files, 16.9M
==> Installing petere/postgresql/postgresql-9.2 dependency: homebrew/dupes/
==> Downloading https://homebrew.bintray.com/bottles-dupes/openldap-2.4.44.sierr
######################################################################## 100.0%
==> Pouring openldap-2.4.44.sierra.bottle.tar.gz
==> Caveats
This formula is keg-only, which means it was not symlinked into /usr/local.

macOS already provides this software and installing another version in
parallel can cause all kinds of trouble.

Generally there are no consequences of this for you. If you build your
own software and it requires this formula, you will need to add to your
build variables:

    LDFLAGS:  -L/usr/local/opt/openldap/lib
    CPPFLAGS: -I/usr/local/opt/openldap/include

==> Summary
🍺   /usr/local/Cellar/openldap/2.4.44: 295 files, 6.3M
==> Installing petere/postgresql/postgresql-9.2 dependency: ossp-uuid
==> Downloading https://homebrew.bintray.com/bottles/ossp-uuid-1.6.2_2.sierra.bo
######################################################################## 100.0%
==> Pouring ossp-uuid-1.6.2_2.sierra.bottle.tar.gz
🍺   /usr/local/Cellar/ossp-uuid/1.6.2_2: 17 files, 206K
==> Installing petere/postgresql/postgresql-9.2 dependency: homebrew/dupes/
==> Using the sandbox
==> Downloading https://downloads.sourceforge.net/project/tcl/Tcl/8.6.6/tcl8.6.6
==> Downloading from http://iweb.dl.sourceforge.net/project/tcl/Tcl/8.6.6/tcl8.6
######################################################################## 100.0%
==> ./configure --prefix=/usr/local/Cellar/tcl-tk/8.6.6 --mandir=/usr/local/Cell
==> make
==> make install
==> make install-private-headers
==> Downloading https://downloads.sourceforge.net/project/tcl/Tcl/8.6.6/tk8.6.6-
==> Downloading from http://heanet.dl.sourceforge.net/project/tcl/Tcl/8.6.6/tk8.
######################################################################## 100.0%
==> ./configure --prefix=/usr/local/Cellar/tcl-tk/8.6.6 --mandir=/usr/local/Cell
==> make TK_LIBRARY=/usr/local/Cellar/tcl-tk/8.6.6/lib
==> make install
==> make install-private-headers
==> Downloading https://github.com/tcltk/tcllib/archive/tcllib_1_18.tar.gz
==> Downloading from https://codeload.github.com/tcltk/tcllib/tar.gz/tcllib_1_18
######################################################################## 100.0%
==> ./configure --prefix=/usr/local/Cellar/tcl-tk/8.6.6 --mandir=/usr/local/Cell
==> make install
==> Caveats
This formula is keg-only, which means it was not symlinked into /usr/local.

Tk installs some X11 headers and OS X provides an (older) Tcl/Tk.

Generally there are no consequences of this for you. If you build your
own software and it requires this formula, you will need to add to your
build variables:

    LDFLAGS:  -L/usr/local/opt/tcl-tk/lib
    CPPFLAGS: -I/usr/local/opt/tcl-tk/include
    PKG_CONFIG_PATH: /usr/local/opt/tcl-tk/lib/pkgconfig
==> Summary
🍺   /usr/local/Cellar/tcl-tk/8.6.6: 2,847 files, 29.2M, built in 5 minutes 31 seconds
==> Installing petere/postgresql/postgresql-9.2
==> Downloading https://ftp.postgresql.org/pub/source/v9.2.19/postgresql-9.2.19.
######################################################################## 100.0%
==> Patching
patching file contrib/uuid-ossp/uuid-ossp.c
==> ./configure --prefix=/usr/local/Cellar/postgresql-9.2/9.2.19 --enable-dtrace
==> make install-world
==> Caveats
To use this PostgreSQL installation, do one or more of the following:

- Call all programs explicitly with /usr/local/opt/postgresql-9.2/bin/...
- Add /usr/local/opt/postgresql-9.2/bin to your PATH
- brew link -f postgresql-9.2
- Install the postgresql-common package

To access the man pages, do one or more of the following:
- Refer to them by their full path, like `man /usr/local/opt/postgresql-9.2/share/man/man1/psql.1`
- Add /usr/local/opt/postgresql-9.2/share/man to your MANPATH
- brew link -f postgresql-9.2

This formula is keg-only, which means it was not symlinked into /usr/local.

The different provided versions of PostgreSQL conflict with each other.

Generally there are no consequences of this for you. If you build your
own software and it requires this formula, you will need to add to your
build variables:

    LDFLAGS:  -L/usr/local/opt/postgresql-9.2/lib
    CPPFLAGS: -I/usr/local/opt/postgresql-9.2/include

==> Summary
🍺   /usr/local/Cellar/postgresql-9.2/9.2.19: 3,120 files, 40M, built in 3 minutes 28 seconds
Updating Homebrew...
==> Auto-updated Homebrew!
Updated 1 tap (homebrew/core).
==> Updated Formulae
[snip]

==> Installing postgresql-9.3 from petere/postgresql
==> Downloading https://ftp.postgresql.org/pub/source/v9.3.15/postgresql-9.3.15.
######################################################################## 100.0%
==> Patching
patching file contrib/uuid-ossp/uuid-ossp.c
==> ./configure --prefix=/usr/local/Cellar/postgresql-9.3/9.3.15 --enable-dtrace
==> make install-world
==> Caveats
To use this PostgreSQL installation, do one or more of the following:

- Call all programs explicitly with /usr/local/opt/postgresql-9.3/bin/...
- Add /usr/local/opt/postgresql-9.3/bin to your PATH
- brew link -f postgresql-9.3
- Install the postgresql-common package

To access the man pages, do one or more of the following:
- Refer to them by their full path, like `man /usr/local/opt/postgresql-9.3/share/man/man1/psql.1`
- Add /usr/local/opt/postgresql-9.3/share/man to your MANPATH
- brew link -f postgresql-9.3

This formula is keg-only, which means it was not symlinked into /usr/local.

The different provided versions of PostgreSQL conflict with each other.

Generally there are no consequences of this for you. If you build your
own software and it requires this formula, you will need to add to your
build variables:

    LDFLAGS:  -L/usr/local/opt/postgresql-9.3/lib
    CPPFLAGS: -I/usr/local/opt/postgresql-9.3/include
    PKG_CONFIG_PATH: /usr/local/opt/postgresql-9.3/lib/pkgconfig

==> Summary
🍺   /usr/local/Cellar/postgresql-9.3/9.3.15: 3,190 files, 41M, built in 3 minutes 17 seconds
==> Installing postgresql-9.4 from petere/postgresql
==> Installing dependencies for petere/postgresql/postgresql-9.4: e2fsprogs
==> Installing petere/postgresql/postgresql-9.4 dependency: e2fsprogs
==> Downloading https://homebrew.bintray.com/bottles/e2fsprogs-1.42.13.sierra.bo
######################################################################## 100.0%
==> Pouring e2fsprogs-1.42.13.sierra.bottle.tar.gz
==> Caveats
This formula is keg-only, which means it was not symlinked into /usr/local.

This brew installs several commands which override macOS-provided file system commands.

Generally there are no consequences of this for you. If you build your
own software and it requires this formula, you will need to add to your
build variables:

    LDFLAGS:  -L/usr/local/opt/e2fsprogs/lib
    CPPFLAGS: -I/usr/local/opt/e2fsprogs/include
    PKG_CONFIG_PATH: /usr/local/opt/e2fsprogs/lib/pkgconfig

==> Summary
🍺   /usr/local/Cellar/e2fsprogs/1.42.13: 135 files, 5.0M
==> Installing petere/postgresql/postgresql-9.4
==> Downloading https://ftp.postgresql.org/pub/source/v9.4.10/postgresql-9.4.10.
######################################################################## 100.0%
==> ./configure --prefix=/usr/local/Cellar/postgresql-9.4/9.4.10 --enable-dtrace
==> make install-world
==> Caveats
To use this PostgreSQL installation, do one or more of the following:

- Call all programs explicitly with /usr/local/opt/postgresql-9.4/bin/...
- Add /usr/local/opt/postgresql-9.4/bin to your PATH
- brew link -f postgresql-9.4
- Install the postgresql-common package

To access the man pages, do one or more of the following:
- Refer to them by their full path, like `man /usr/local/opt/postgresql-9.4/share/man/man1/psql.1`
- Add /usr/local/opt/postgresql-9.4/share/man to your MANPATH
- brew link -f postgresql-9.4

This formula is keg-only, which means it was not symlinked into /usr/local.

The different provided versions of PostgreSQL conflict with each other.

Generally there are no consequences of this for you. If you build your
own software and it requires this formula, you will need to add to your
build variables:

    LDFLAGS:  -L/usr/local/opt/postgresql-9.4/lib
    CPPFLAGS: -I/usr/local/opt/postgresql-9.4/include
    PKG_CONFIG_PATH: /usr/local/opt/postgresql-9.4/lib/pkgconfig

==> Summary
🍺   /usr/local/Cellar/postgresql-9.4/9.4.10: 3,261 files, 42.9M, built in 3 minutes 17 seconds
==> Installing postgresql-9.5 from petere/postgresql
==> Downloading https://ftp.postgresql.org/pub/source/v9.5.5/postgresql-9.5.5.ta
######################################################################## 100.0%
==> ./configure --prefix=/usr/local/Cellar/postgresql-9.5/9.5.5 --enable-dtrace
==> make install-world
==> Caveats
To use this PostgreSQL installation, do one or more of the following:

- Call all programs explicitly with /usr/local/opt/postgresql-9.5/bin/...
- Add /usr/local/opt/postgresql-9.5/bin to your PATH
- brew link -f postgresql-9.5
- Install the postgresql-common package

To access the man pages, do one or more of the following:
- Refer to them by their full path, like `man /usr/local/opt/postgresql-9.5/share/man/man1/psql.1`
- Add /usr/local/opt/postgresql-9.5/share/man to your MANPATH
- brew link -f postgresql-9.5

This formula is keg-only, which means it was not symlinked into /usr/local.

The different provided versions of PostgreSQL conflict with each other.

Generally there are no consequences of this for you. If you build your
own software and it requires this formula, you will need to add to your
build variables:

    LDFLAGS:  -L/usr/local/opt/postgresql-9.5/lib
    CPPFLAGS: -I/usr/local/opt/postgresql-9.5/include
    PKG_CONFIG_PATH: /usr/local/opt/postgresql-9.5/lib/pkgconfig

==> Summary
🍺   /usr/local/Cellar/postgresql-9.5/9.5.5: 3,395 files, 45M, built in 3 minutes 30 seconds
==> Installing postgresql-9.6 from petere/postgresql
==> Downloading https://ftp.postgresql.org/pub/source/v9.6.1/postgresql-9.6.1.ta
######################################################################## 100.0%
==> ./configure --prefix=/usr/local/Cellar/postgresql-9.6/9.6.1 --enable-dtrace
==> make install-world
==> Caveats
To use this PostgreSQL installation, do one or more of the following:

- Call all programs explicitly with /usr/local/opt/postgresql-9.6/bin/...
- Add /usr/local/opt/postgresql-9.6/bin to your PATH
- brew link -f postgresql-9.6
- Install the postgresql-common package

To access the man pages, do one or more of the following:
- Refer to them by their full path, like `man /usr/local/opt/postgresql-9.6/share/man/man1/psql.1`
- Add /usr/local/opt/postgresql-9.6/share/man to your MANPATH
- brew link -f postgresql-9.6

This formula is keg-only, which means it was not symlinked into /usr/local.

The different provided versions of PostgreSQL conflict with each other.

Generally there are no consequences of this for you. If you build your
own software and it requires this formula, you will need to add to your
build variables:

    LDFLAGS:  -L/usr/local/opt/postgresql-9.6/lib
    CPPFLAGS: -I/usr/local/opt/postgresql-9.6/include
    PKG_CONFIG_PATH: /usr/local/opt/postgresql-9.6/lib/pkgconfig

==> Summary
🍺   /usr/local/Cellar/postgresql-9.6/9.6.1: 3,485 files, 46.6M, built in 3 minutes 38 seconds

As it says in the output, these are ‘keg-only’, which has the nice side effect that they are automatically installed in side-by-side directories /usr/local/opt/postgresql-9.1/ etc.

Peter configures PostgreSQL with just about all the options turned on:

$ pg_config |grep ^CONF
CONFIGURE = '--prefix=/usr/local/Cellar/postgresql-9.6/9.6.1' '--enable-dtrace' '--enable-nls' '--with-bonjour' '--with-gssapi' '--with-ldap' '--with-libxml' '--with-libxslt' '--with-openssl' '--with-uuid=e2fs' '--with-pam' '--with-perl' '--with-python' '--with-tcl' '--with-includes=/usr/local/opt/gettext/include:/usr/local/opt/openldap/include:/usr/local/opt/openssl/include:/usr/local/opt/readline/include:/usr/local/opt/tcl-tk/include' '--with-libraries=/usr/local/opt/gettext/lib:/usr/local/opt/openldap/lib:/usr/local/opt/openssl/lib:/usr/local/opt/readline/lib:/usr/local/opt/tcl-tk/lib' 'CC=clang'

and you get all the extensions that are in ‘contrib’ as well. However, if you find that you need to install another extension, I advocate using pex. You would install it like this:

$ brew install pex
==> Installing dependencies for pex: postgresql
==> Installing pex dependency: postgresql
==> Downloading https://homebrew.bintray.com/bottles/postgresql-9.5.5.sierra.bot
Already downloaded: /Users/doug/Library/Caches/Homebrew/postgresql-9.5.5.sierra.bottle.tar.gz
==> Pouring postgresql-9.5.5.sierra.bottle.tar.gz
==> Using the sandbox
==> /usr/local/Cellar/postgresql/9.5.5/bin/initdb /usr/local/var/postgres
==> Caveats
If builds of PostgreSQL 9 are failing and you have version 8.x installed,
you may need to remove the previous version first. See:
  https://github.com/Homebrew/homebrew/issues/2510

To migrate existing data from a previous major version (pre-9.0) of PostgreSQL, see:
  https://www.postgresql.org/docs/9.5/static/upgrading.html

To migrate existing data from a previous minor version (9.0-9.4) of PostgreSQL, see:
  https://www.postgresql.org/docs/9.5/static/pgupgrade.html

   You will need your previous PostgreSQL installation from brew to perform `pg_upgrade`.
   Do not run `brew cleanup postgresql` until you have performed the migration.                                                                  1
To have launchd start postgresql now and restart at login:
  brew services start postgresql
Or, if you do not want/need a background service you can just run:
  pg_ctl -D /usr/local/var/postgres start
==> Summary
🍺   /usr/local/Cellar/postgresql/9.5.5: 3,154 files, 35.1M
==> Installing pex
==> Downloading https://homebrew.bintray.com/bottles/pex-1.20140409.sierra.bottl
######################################################################## 100.0%
==> Pouring pex-1.20140409.sierra.bottle.tar.gz
==> Caveats
If installing for the first time, perform the following in order to setup the necessary directory structure:
  pex init
==> Summary
🍺   /usr/local/Cellar/pex/1.20140409: 5 files, 31.6K
$
$ pex init

Sadly, it will reinstall the default PostgreSQL brew recipe, but we’ll simply pretend that it’s not there going forward. Once installed, use pex like so:

$ pex -g /usr/local/opt/postgresql-9.4 install ip4r
==> Downloading ip4r from https://github.com/RhodiumToad/ip4r/archive/2.0.3.tar.gz
######################################################################## 100.0%
==> Unpacking ip4r
[snip]
==> Building ip4r
[snip]
==> Installing ip4r
[snip]
/usr/bin/install -c -m 755  ip4r.so '/usr/local/Cellar/postgresql-9.4/9.4.10/lib/ip4r.so'
/usr/bin/install -c -m 644 ip4r.control '/usr/local/Cellar/postgresql-9.4/9.4.10/share/extension/'
/usr/bin/install -c -m 644 ip4r--2.0.sql ip4r--unpackaged2.0--2.0.sql ip4r--unpackaged1--2.0.sql                                                  ↪\  '/usr/local/Cellar/postgresql-9.4/9.4.10/share/extension/'
[snip]
==> Package ip4r installed successfully

As you can see, the ip4r extension was installed into my PostgreSQL 9.4 instance.

Now, you might be wondering how to deal with all these concurrent versions. If you type pg_dump which one will you get? How can you force it to use a specific version? Right now, you won’t get any version, since it’s not in your path. You will need to specify exactly what you want:

$ /usr/local/opt/postgresql-9.6/bin/psql --version
psql (PostgreSQL) 9.6.1

which is a huge pita. So, let’s make a little convenience function in our shell. Edit your .zshrc or your .bashrc (or whatever) and add a function like this:

function pg()
    {
      ver=$1

      for i in /usr/local/var/postgres/*
      do
        running_ver=$(basename ${i})
        is_running=$(ps -few|egrep -- "[p]ostgres.*-D.*${running_ver}")
        if [[ ! -z ${is_running} ]] ; then
          echo -n "Stopping PostgreSQL ${running_ver}... "
          /usr/local/opt/postgresql-${running_ver}/bin/pg_ctl \
            -D $i stop -mf &>/dev/null
          echo "done!"
        fi
      done

      echo -n "Activating PostgreSQL ${ver}... "
      brew unlink --force --overwrite postgresql-${ver} &>/dev/null
      brew link --force --overwrite postgresql-${ver} &>/dev/null
      echo "done!"

      export PGDATA=/usr/local/var/postgres/${ver}

      if [[ ! -d ${PGDATA} ]] ; then
        echo -n "Initializing PostgreSQL ${ver} cluster... "
        mkdir ${PGDATA}
        initdb -k ${PGDATA} &>/dev/null || initdb ${PGDATA} &>/dev/null
        echo "done!"
      fi
      echo -n "Starting PostgreSQL ${ver}... "
      pg_ctl -D ${PGDATA} start &>/dev/null
      echo "done!"
    }

and then simply call pg 9.5 to set PostgreSQL 9.5 as your ‘active’ instance:

$ pg 9.5
Stopping PostgreSQL 9.4... done!
Activating PostgreSQL 9.5... done!
Initializing PostgreSQL 9.5 cluster... done!
Starting PostgreSQL 9.5... done!

Restore your data:

$ psql -d template1 -f my_roles.sql
$ psql -d template1 -f my_data.sql

and there you be. Ain’t it beautiful? :)

#Osx, #Postgresql, #Howto,

See Also