Igor Khromov blog

Install PostgreSQL Server on macOS with brew

By igor.khromov, April 4, 2019

1. Install latest version

1.1. Check what is the latest version available

To check the version, we just need to run:

brew info postgresql

You will get output like this (valid for April 2019):

postgresql: stable 11.2 (bottled), HEAD
Object-relational database system
https://www.postgresql.org/
Conflicts with:
  postgres-xc (because postgresql and postgres-xc install the same binaries.)
/usr/local/Cellar/postgresql/11.1_1 (3,548 files, 40.3MB) *
  Poured from bottle on 2019-01-22 at 15:23:46
From: https://github.com/Homebrew/homebrew-core/blob/master/Formula/postgresql.rb
==> Dependencies
Build: pkg-config ✘
Required: icu4c ✔, openssl ✔, readline ✔
==> Options
--HEAD
	Install HEAD version
==> Caveats
To migrate existing data from a previous major version of PostgreSQL run:
  brew postgresql-upgrade-database

To have launchd start postgresql now and restart at login:
  brew services start postgresql
Or, if you don't want/need a background service you can just run:
  pg_ctl -D /usr/local/var/postgres start
==> Analytics
install: 74,356 (30 days), 241,172 (90 days), 763,897 (365 days)
install_on_request: 68,356 (30 days), 216,677 (90 days), 669,943 (365 days)
build_error: 0 (30 days)

In line #1 you can see version number: stable 11.2 and this is what we looked for.

1.2. Install formula (packet)

If you ok with with that version than can install it with command:

brew install postgresql

You will get output like this:

==> Downloading https://homebrew.bintray.com/bottles/postgresql-11.2.mojave.bottle.tar.gz
==> Downloading from https://akamai.bintray.com/8f/8fd1cacd7b9aa9325c9ddcc1c3fabbc0f76d70898cb45aa4
######################################################################## 100.0%
==> Pouring postgresql-11.2.mojave.bottle.tar.gz
==> Caveats
To migrate existing data from a previous major version of PostgreSQL run:
  brew postgresql-upgrade-database

To have launchd start postgresql now and restart at login:
  brew services start postgresql
Or, if you don't want/need a background service you can just run:
  pg_ctl -D /usr/local/var/postgres start
==> Summary
🍺  /usr/local/Cellar/postgresql/11.2: 3,186 files, 35.4MB

We can avoid using long commands to start, stop or restart postgresql service can create aliases in our user’s .bash_profile.

Open current user’s bash profile:

nano ~/.bash_profile

Add following line to the end of the file:

alias pg-start='brew services start postgresql'
alias pg-stop='brew services stop postgresql'
alias pg-restart='brew services restart postgresql'
alias pg-status='pg_ctl -D /usr/local/var/postgres status'

To reload .bash_profile run:

source ~/.bash_profile

Now you can start PostgreSQL Server with command:

pg-start
waiting for server to start....2019-04-10 22:55:06.607 EEST [57850] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2019-04-10 22:55:06.607 EEST [57850] LOG:  listening on IPv6 address "::1", port 5432
2019-04-10 22:55:06.613 EEST [57850] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-04-10 22:55:06.649 EEST [57851] LOG:  database system was shut down at 2019-04-10 22:55:02 EEST
2019-04-10 22:55:06.655 EEST [57850] LOG:  database system is ready to accept connections
 done
server started

Done, server is up running.

To connect to PostgreSQL using command line, just type:

psql postgres

1.4. Get full config data

PostgreSQL has specific command to get config details:

pg_config

You will get output like this:

BINDIR = /usr/local/Cellar/postgresql/11.2/bin
DOCDIR = /usr/local/Cellar/postgresql/11.2/share/doc/postgresql
HTMLDIR = /usr/local/Cellar/postgresql/11.2/share/doc/postgresql
INCLUDEDIR = /usr/local/Cellar/postgresql/11.2/include
PKGINCLUDEDIR = /usr/local/Cellar/postgresql/11.2/include
INCLUDEDIR-SERVER = /usr/local/Cellar/postgresql/11.2/include/server
LIBDIR = /usr/local/lib
PKGLIBDIR = /usr/local/lib/postgresql
LOCALEDIR = /usr/local/Cellar/postgresql/11.2/share/locale
MANDIR = /usr/local/Cellar/postgresql/11.2/share/man
SHAREDIR = /usr/local/share/postgresql
SYSCONFDIR = /usr/local/etc/postgresql
PGXS = /usr/local/lib/postgresql/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--disable-debug' '--prefix=/usr/local/Cellar/postgresql/11.2' '--datadir=/usr/local/share/postgresql' '--libdir=/usr/local/lib' '--sysconfdir=/usr/local/etc' '--docdir=/usr/local/Cellar/postgresql/11.2/share/doc/postgresql' '--enable-thread-safety' '--with-bonjour' '--with-gssapi' '--with-icu' '--with-ldap' '--with-libxml' '--with-libxslt' '--with-openssl' '--with-pam' '--with-perl' '--with-uuid=e2fs' '--with-tcl' '--with-tclconfig=/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.14.sdk/System/Library/Frameworks/Tcl.framework' 'CC=clang' 'LDFLAGS=-L/usr/local/opt/openssl/lib -L/usr/local/opt/readline/lib' 'CPPFLAGS=-I/usr/local/opt/openssl/include -I/usr/local/opt/readline/include' 'CXX=clang++' 'PKG_CONFIG_PATH=/usr/local/opt/icu4c/lib/pkgconfig:/usr/local/opt/openssl/lib/pkgconfig:/usr/local/opt/readline/lib/pkgconfig' 'PKG_CONFIG_LIBDIR=/usr/lib/pkgconfig:/usr/local/Homebrew/Library/Homebrew/os/mac/pkgconfig/10.14'
CC = clang
CPPFLAGS = -I/usr/local/Cellar/icu4c/63.1/include -isysroot /Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.14.sdk -I/usr/local/opt/openssl/include -I/usr/local/opt/readline/include -I/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.14.sdk/usr/include/libxml2
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2
CFLAGS_SL = 
LDFLAGS = -L/usr/local/opt/openssl/lib -L/usr/local/opt/readline/lib -Wl,-dead_strip_dylibs
LDFLAGS_EX = 
LDFLAGS_SL = 
LIBS = -lpgcommon -lpgport -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz -lreadline -lm 
VERSION = PostgreSQL 11.2

2. Install specific version

2.1. Check what versions are available

To get the list of available versions of PostgreSQL in a brew, we can simply run:

brew search postgresql

You will see the output like this:

==> Formulae
postgresql ✔         postgresql@10       postgresql@9.4       postgresql@9.5       postgresql@9.6

==> Casks
homebrew/cask/navicat-for-postgresql                 homebrew/cask/photo-supreme-postgresql

2.2. Install specific version

To install version we need we can just run install command (in our case for version postgresql@10):

brew install postgresql@10

You will see the output like this:

==> Downloading https://homebrew.bintray.com/bottles/postgresql@10-10.6_1.mojave.bottle.1.tar.gz
Already downloaded: /Users/username/Library/Caches/Homebrew/downloads/e7347cb962dec34eb87514ad87bf0e83d2a60fd383a88b1d9defb39118359aaa--postgresql@10-10.6_1.mojave.bottle.1.tar.gz
==> Pouring postgresql@10-10.6_1.mojave.bottle.1.tar.gz
==> Caveats
To migrate existing data from a previous major version of PostgreSQL run:
  brew postgresql-upgrade-database

postgresql@10 is keg-only, which means it was not symlinked into /usr/local,
because this is an alternate version of another formula.

If you need to have postgresql@10 first in your PATH run:
  echo 'export PATH="/usr/local/opt/postgresql@10/bin:$PATH"' >> ~/.bash_profile

For compilers to find postgresql@10 you may need to set:
  export LDFLAGS="-L/usr/local/opt/postgresql@10/lib"
  export CPPFLAGS="-I/usr/local/opt/postgresql@10/include"


To have launchd start postgresql@10 now and restart at login:
  brew services start postgresql@10
Or, if you don't want/need a background service you can just run:
  pg_ctl -D /usr/local/var/postgresql@10 start
==> Summary
🍺  /usr/local/Cellar/postgresql@10/10.6_1: 1,706 files, 20.8MB

Now we can add aliases to .bash_profile.

2.2.1. We have only one version of PostgreSQL installed

We can simply open our user’s .bash_profile:

nano ~/.bash_profile

And add in the end of file:

alias pg-start='brew services start postgresql@10'
alias pg-stop='brew services stop postgresql@10'
alias pg-restart='brew services restart postgresql@10'
alias pg-status='pg_ctl -D /usr/local/var/postgresql@10 status'

To close nano, press Ctrl+O.

2.2.2. We have two versions of PostgreSQL installed

If we have two versions of PostgreSQL installed, we need to stop one, that currently running:

pg-stop

Then we ask brew to make version 10 default one.

Unlink old version firstly:

brew unlink postgresql

Link required version:

brew link postgresql@10 --force

You will see the output like this:

Linking /usr/local/Cellar/postgresql@10/10.6_1... 167 symlinks created

If you need to have this software first in your PATH instead consider running:
  echo 'export PATH="/usr/local/opt/postgresql@10/bin:$PATH"' >> ~/.bash_profile

Let’s check if we did all correctly:

pg_config

If everything is OK, we need to see config related to version 10.6:

BINDIR = /usr/local/Cellar/postgresql@10/10.6_1/bin
DOCDIR = /usr/local/Cellar/postgresql@10/10.6_1/share/doc/postgresql@10
HTMLDIR = /usr/local/Cellar/postgresql@10/10.6_1/share/doc/postgresql@10
INCLUDEDIR = /usr/local/Cellar/postgresql@10/10.6_1/include
PKGINCLUDEDIR = /usr/local/Cellar/postgresql@10/10.6_1/include
INCLUDEDIR-SERVER = /usr/local/Cellar/postgresql@10/10.6_1/include/server
LIBDIR = /usr/local/Cellar/postgresql@10/10.6_1/lib
PKGLIBDIR = /usr/local/Cellar/postgresql@10/10.6_1/lib
LOCALEDIR = /usr/local/Cellar/postgresql@10/10.6_1/share/locale
MANDIR = /usr/local/Cellar/postgresql@10/10.6_1/share/man
SHAREDIR = /usr/local/Cellar/postgresql@10/10.6_1/share/postgresql@10
SYSCONFDIR = /usr/local/etc/postgresql
PGXS = /usr/local/Cellar/postgresql@10/10.6_1/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--disable-debug' '--prefix=/usr/local/Cellar/postgresql@10/10.6_1' '--datadir=/usr/local/Cellar/postgresql@10/10.6_1/share/postgresql@10' '--libdir=/usr/local/Cellar/postgresql@10/10.6_1/lib' '--sysconfdir=/usr/local/etc' '--docdir=/usr/local/Cellar/postgresql@10/10.6_1/share/doc/postgresql@10' '--enable-thread-safety' '--with-bonjour' '--with-gssapi' '--with-icu' '--with-ldap' '--with-libxml' '--with-libxslt' '--with-openssl' '--with-pam' '--with-perl' '--with-uuid=e2fs' '--with-tcl' '--with-tclconfig=/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.14.sdk/System/Library/Frameworks/Tcl.framework' 'CC=clang' 'LDFLAGS=-L/usr/local/opt/openssl/lib -L/usr/local/opt/readline/lib -R/usr/local/Cellar/postgresql@10/10.6_1/lib/postgresql -L/usr/local/opt/openssl/lib -L/usr/local/opt/readline/lib' 'CPPFLAGS=-I/usr/local/opt/openssl/include -I/usr/local/opt/readline/include' 'PKG_CONFIG_PATH=/usr/local/opt/icu4c/lib/pkgconfig:/usr/local/opt/openssl/lib/pkgconfig:/usr/local/opt/readline/lib/pkgconfig' 'PKG_CONFIG_LIBDIR=/usr/lib/pkgconfig:/usr/local/Homebrew/Library/Homebrew/os/mac/pkgconfig/10.14'
CC = clang
CPPFLAGS = -I/usr/local/Cellar/icu4c/63.1/include -isysroot /Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.14.sdk -I/usr/local/opt/openssl/include -I/usr/local/opt/readline/include -I/Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.14.sdk/usr/include/libxml2
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2
CFLAGS_SL = 
LDFLAGS = -L/usr/local/opt/openssl/lib -L/usr/local/opt/readline/lib -R/usr/local/Cellar/postgresql@10/10.6_1/lib/postgresql -L/usr/local/opt/openssl/lib -L/usr/local/opt/readline/lib -Wl,-dead_strip_dylibs
LDFLAGS_EX = 
LDFLAGS_SL = 
LIBS = -lpgcommon -lpgport -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz -lreadline -lm 
VERSION = PostgreSQL 10.6

Done.

3. PostgreSQL file and database locations

  • config file: /usr/local/var/postgres/postgresql.conf;
  • client authentication config: /usr/local/var/postgres/pg_hba.conf;
  • config folder: /usr/local/var/postgres;
  • bin folder: /usr/local/Cellar/postgresql/11.2/bin;
  • database location: /usr/local/var/postgres.
Tags:
brew
datababe
install
mac
macos
postgres
postgresql