Skip to content
Copyright © 2022- basebox GmbH, all rights reserved.
Licensed to be used in conjunction with basebox, only.

PostgreSQL Primer

This document is a quick primer on how to setup a local PostgreSQL server for development and testing under Debian/Ubuntu Linux or Macs. basebox supports PostgreSQL version 10 or later.

Note

We will provide a PostgreSQL setup guide for production later.

If you are curious: we recommend to run PostgreSQL on its dedicated server, together with our database proxy, dbproxy

Linux (Ubuntu or Debian)

Install PostgreSQL with the following command in a terminal window:

sudo apt install postgresql

This command will also create a dedicated PostgreSQL user named postgres that is allowed to issue database commands; your own user account is not. So to create and modify databases, you have to run the required commands as the postgres user like this:

sudo -u postgres <command>

Database Creation

First, create a user for your database. Of course you can also use an existing user, but in general it is a good idea to use a dedicated user; we assume a username bbtest and also name the database bbtest:

sudo -u postgres createuser -DRP bbtest
# Command will prompt you for a password for the new user
The password you enter is up to you, of course.

Next, create the database and set the owner to the new user:

sudo -u postgres createdb -O bbtest bbtest

Configure User Access

You have to explicitly allow the user access to the bbtest database. To do so, edit the pg_hba.conf file located in /etc/postgresql/12/main (12 is your installed PostgreSQL version, so this might differ):

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   bbtest          bbtest                                  md5

The line that starts with local bbtest allows your user access to the bbtest database using a password. Please insert it directly under the line that starts with local all, as PostgreSQL will always use the first line that fits.

After the file has been saved, activate the new configuration with this command:

sudo service postgresql restart

Test access to the database with the following command:

psql -U bbtest bbtest  # will prompt for bbtest's password

You should see a PostgreSQL prompt similar to the following:

psql (12.14 (Ubuntu 12.14-0ubuntu0.20.04.1))
Type "help" for help.

bbtest=>

Press Ctrl+d to exit the PostgreSQL shell. You successfully configured your local PostgreSQL development server!

Mac

We assume macOS version Catalina or later

There are two recommended ways to install PostgreSQL on Macs for development and testing purposes:

  • Homebrew
  • PostgreSQL.app

Homebrew

If you have Homebrew already installed, you can install PostgreSQL by running the following commands in a terminal:

brew update
brew install postgresql@14
brew services start postgresql@14

For further information, please refer to this page.

PostgreSQL.app

PostgreSQL.app is a utility app that simplifies PostgreSQL installation on Macs. It allows you to easily add and switch PostgreSQL versions. So if this sounds interesting to you or if you do not want to install Homebrew, Postgresql.app is for you.

To install it, please follow the instructions on their site, it is really simple. You can use any PostgreSQL version 10 or later; 14 is recommended.

After installation, you probably have to add the PostgreSQL command line utilities to your shell's path. To verify if this is needed, please run the following command in a terminal window:

which psql

If you get a reponse psql not found, you have to modify your shell's path. To do so, first locate the bin directory that contains the PostgreSQL command line utilities; to do so, enter the following in a terminal window:

ls /Applications/Postgres.app/Contents/Versions/latest/bin/

The response should be a list of all the files in the bin directory; if you get an error No such file or directory, then your installation has somehow failed.

If everything's all right, add the directory to your path. First determine what shell you are using:

echo $SHELL

If the output is /bin/zsh, the command to edit your shell config is

open -a textedit ~/.zshrc

If it is /bin/bash, the command is:

open -a textedit ~/.bash_profile

Add the following line to the end of the file:

export PATH=/Applications/Postgres.app/Contents/Versions/latest/bin/:$PATH

Start a new terminal and test if your shell finds PostgreSQL's utilities:

createdb --help

You should see the command line help for createdb. If not... please retry the shell config :-)

Create Database

First, create a user for your database; we assume a username bbtest and also name the database bbtest:

createuser -DRP bbtest
# Command will prompt you for a password for the new user
The password you enter is up to you, of course.

Next, create the database and set the owner to the new user:

createdb -O bbtest bbtest

Test access to the new database, using the new user:

psql -U bbtest bbtest

Your should see an SQL prompt similar to the following:

psql (12.9, server 10.19)
Type "help" for help.

bbtest=#

Congratulations! You successfully installed PostgreSQL and created a test database!

Note

The documentation usually assumes a Linux machine. If you see a command that starts with sudo -u postgres <command>, you can skip the sudo part and use just <command>, as on Mac installations as described above, the postgres user is neither existing nor required.