Setting up PostgreSQL on a Ubuntu Server

Even though I mostly use docker composes for dev, and managed database solutions for prod, smaller projects might still find it useful to set up PostgreSQL locally. In this article, I'll walk through how to do it on a Ubuntu server specifically (I'll use a EC2 instance running Ubuntu 24.04.1).

1. Installations

Firstly, we'll have to install all the packages we need, which starts with an update to our package list:

sudo apt update

Run the following command to install PostgreSQL:

sudo apt install postgresql postgresql-contrib

2. Setting up a user

Accessing the database is done with a user. In a NodeJS application it might look something like this:

import { Pool } from 'pg';
import dotenv from 'dotenv';
dotenv.config();

export const pool = new Pool({
    user: process.env.POSTGRES_USER,
    host: process.env.POSTGRES_HOST,
    database: process.env.POSTGRES_DATABASE,
    password: process.env.POSTGRES_PASSWORD,
    port: parseInt(process.env.POSTGRES_PORT as string),
});

As such, we'll have to make sure we create a user with the POSTGRES_USER and POSTGRES_PASSWORD we've added to the application's environment variables.

To access the Postgres database, we'll have to switch user to the one created during installation:

sudo -i -u postgres

And then access the shell by using:

psql

In the shell, we'll create our user:

CREATE USER tf2bot WITH PASSWORD '50d56046-0d01-4865-901c-a9b3fac8e1f8';

(you can change the username and password to whatever fits your application best. In this case it will be used for a TF2 Steam bot).

Instead of creating the database with the default user and changing owner, I like to allow the newly created user to create databases:

ALTER USER tf2bot CREATEDB;

(change tf2bot to the username you used in the previous step)

For the next step, we need to locate a configuration file. This is easily done using the following command:

SHOW hba_file;

That should print something like:

              hba_file               
-------------------------------------
 /etc/postgresql/16/main/pg_hba.conf
(1 row)

Let's exit the shell for now:

\q

And then ctrl + D to go back to our root user.

ctrl + D

3. Configuring Postgres to allow sign in with password

Depending on what was printed in the previous step, the following command might look different (depending on postgres version, etc.) Excluding the final file, let's navigate to the directory within which it resides:

cd /etc/postgresql/16/main/

To edit the file of interest, we'll utilize nano:

sudo nano pg_hba.conf

Scroll down to the following line and change "peer" to "scram-sha-256"

# "local" is for Unix domain socket connections only
local   all             all                                peer

It should look like this when you're done:

# "local" is for Unix domain socket connections only
local   all             all                                scram-sha-256

Afterwards, we'll have to restart postgres:

sudo systemctl restart postgresql

If all went well, you should now be able to directly sign in to the user you created:

psql -U tf2bot -d postgres

You'll be prompted for a password, enter the one you added during setup (in my case 50d56046-0d01-4865-901c-a9b3fac8e1f8)

Remain in the shell and move on to step 4.

4. Adding our database

To add a database to postgres, use the following command:

CREATE DATABASE tf2bot;

Afterwards, you can connect to the database using:

\c tf2bot;

In here, paste the database.sql file of your project, and you'll be good to go!

Exit the postgres shell when you are done:

\q