Setting up PostgreSQL
Installing PostgreSQL
If you’re following along, you’ll need to install PostgreSQL on your computer at this point. The official PostgreSQL documentation contains comprehensive download and installation instructions for all types of operating systems, but if you’re using macOS you should be able to install the latest version with:
$ brew install postgresql@15
Or if you’re using a Linux distribution you should be able to install PostgreSQL via your package manager. For example, if your OS supports the apt package manager (like Debian and Ubuntu do) you can install it with:
$ sudo apt install postgresql
On Windows machines you can install PostgreSQL using the Chocolatey package manager with the command:
> choco install postgresql
Connecting to the PostgreSQL interactive terminal
When PostgreSQL was installed, a psql binary should also have been created on your computer. This contains a terminal-based front-end for working with PostgreSQL.
You can check that this is available by running the psql --version command from your terminal like so:
$ psql --version psql (PostgreSQL) 15.4 (Ubuntu 15.4-1.pgdg22.04+1
If you’re not already familiar with PostgreSQL, the process for connecting to it for the first time using psql can be a bit unintuitive. So let’s take a moment to walk through it.
When PostgreSQL is freshly installed it only has one user account: a superuser called postgres. In the first instance, we need to connect to PostgreSQL as this superuser to do anything — and from there we can perform any setup steps that we need to, like creating a database and creating other users.
During installation, an operating system user named postgres should also have been created on your machine. On Unix-based systems you can check your /etc/passwd file to confirm this, like so:
$ cat /etc/passwd | grep 'postgres' postgres:x:127:134:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash
This is important because, by default, PostgreSQL uses an authentication scheme called peer authentication for any connections from the local machine. Peer authentication means that if the current operating system user’s username matches a valid PostgreSQL user username, they can log in to PostgreSQL as that user with no further authentication. There are no passwords involved.
So it follows that if we switch to the operating system user called postgres, we should be able to connect to PostgreSQL using psql without needing any further authentication. In fact, you can do both these things in one step with the following command:
$ sudo -u postgres psql psql (15.4 (Ubuntu 15.4-1.pgdg22.04+1)) Type "help" for help. postgres=#
So, just to confirm, what we’ve done here is use the sudo command (superuser do) to run the psql command as the operating system user called postgres. That opens a session in the interactive terminal-based front-end, where we’re authenticated as the PostgreSQL superuser postgres.
If you want, you can confirm this by running a "SELECT current_user" query to see which PostgreSQL user you currently are:
postgres=# SELECT current_user; current_user -------------- postgres (1 row)
Creating databases, users, and extensions
While we’re connected as the postgres superuser, let’s create a new database for our project called greenlight, and then connect to it using the \c command like so:
postgres=# CREATE DATABASE greenlight; CREATE DATABASE postgres=# \c greenlight You are now connected to database "greenlight" as user "postgres". greenlight=#
Now that our greenlight database exists and we’re connected to it, there are a couple of tasks we need to complete.
The first task is to create a new greenlight user, without superuser permissions, which we can use to execute SQL migrations and connect to the database from our Go application. We want to set up this new user to use password-based authentication, instead of peer authentication.
PostgreSQL also has the concept of extensions, which add additional features on top of the standard functionality. A list of the extensions that ship with PostgreSQL can be found here, and there are also some others that you can download separately.
In this project we’re going to use the citext extension. This adds a case-insensitive character string type to PostgreSQL, which we will use later in the book to store user email addresses.
Go ahead and run the following commands to create a new greenlight user with a specific password and add the citext extension to our database:
greenlight=# CREATE ROLE greenlight WITH LOGIN PASSWORD 'pa55word'; CREATE ROLE greenlight=# CREATE EXTENSION IF NOT EXISTS citext; CREATE EXTENSION
Once that’s successfully done, you can type exit or \q to close the terminal-based front-end and revert to being your normal operating system user.
greenlight=# exit
Connecting as the new user
Before we go any further, let’s prove to ourselves that everything is set up correctly and try connecting to the greenlight database as the greenlight user. When prompted, enter the password that you set in the step above.
$ psql --host=localhost --dbname=greenlight --username=greenlight Password for user greenlight: psql (15.4 (Ubuntu 15.4-1.pgdg22.04+1)) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) Type "help" for help. greenlight=> SELECT current_user; current_user -------------- greenlight (1 row) greenlight=> exit
Great! That confirms that our database and the new greenlight user with password credentials are working correctly, and that we’re able to execute SQL statements as that user without any issues.
Additional information
Optimizing PostgreSQL settings
The default settings that PostgreSQL ships with are quite conservative, and you can often improve the performance of your database by tweaking the values in your postgresql.conf file.
You can check where your postgresql.conf file lives with the following SQL query:
$ sudo -u postgres psql -c 'SHOW config_file;'
config_file
-----------------------------------------
/etc/postgresql/15/main/postgresql.conf
(1 row)
This article provides a good introduction to some of the most important PostgreSQL settings, and guidance on what values are reasonable to use as a starting point. If you’re interested in optimizing PostgreSQL, I recommend giving this a read.
Alternatively, you can use this web-based tool to generate suggested values based on your available system hardware. A nice feature of this tool is that it also outputs ALTER SYSTEM SQL statements, which you can run against your database to change the settings instead of altering your postgresql.conf file manually.