Setting Up PostgreSQL on Ubuntu VPS

Before the before you start

I've tried my best to detail everything here. If you notice any issues or things that are wrong, please reach out on X @ldo_dev or by email and let me know and I'll get them sorted asap.

If you think I can improve this in anyway, please let me know also!

Before you start

Buy a vps and figure out your ssh credentials

ssh into your new setup

ssh root@domain
 
# or
 
ssh root@ip

Update everything

sudo apt update && sudo apt upgrade -y

Set the timezone (optional/ depends where you're hosing)

timedatectl set-timezone Europe/London # Change for yours?

Create a non-root user (first time setup only)

adduser luke

usermod -aG sudo luke

From now on we use the new user

su - luke

Install Postgres

🤷🏼‍♀️

You can change the version to what you like. I think 18 is the latest, but all my apps are tailored for 17, so that's what I'll use.

Ubuntu repositories can lag - so best to use the official PostgreSQL repo

sudo apt install -y curl ca-certificates gnupg
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc \
 | sudo gpg --dearmor -o /usr/share/keyrings/postgres.gpg
 
echo "deb [signed-by=/usr/share/keyrings/postgres.gpg] \
http://apt.postgresql.org/pub/repos/apt \
$(lsb_release -cs)-pgdg main" \
 | sudo tee /etc/apt/sources.list.d/postgres.list

Install updates and PostgreSQL

sudo apt update && sudo apt install -y postgres-17 postgres-client-17

Verify installation

psql --version

Sanity check it actually installed and is running

sudo systemctl status postgresql

Switch to the postgres user:

sudo -i -u postgres

psql

Exit postgres editor

\q # or "exit" if that doesn't work

Create production and dev roles and databases

🤷🏼‍♀️

Again this is just something I like to do. Production users can only access the production databases and vice versa. Helps to prevent any fuckups in dev lol

Re-enter postgres account

sudo -i -u postgres

psql

You're now in the postgreSQL editor.

Create Roles (users)

CREATE ROLE prod_user LOGIN PASSWORD 'STRONG_PROD_PASSWORD';
CREATE ROLE dev_user  LOGIN PASSWORD 'STRONG_DEV_PASSWORD';

Create databases

CREATE DATABASE prod_db OWNER prod_user;
CREATE DATABASE dev_db  OWNER dev_user;

Lock users to their db only

REVOKE ALL ON DATABASE prod_db FROM PUBLIC;
REVOKE ALL ON DATABASE dev_db  FROM PUBLIC;

Give users permission to see their db

ALTER DATABASE prod_db OWNER TO prod_user;

GRANT ALL PRIVILEGES ON DATABASE prod_db TO prod_user;

Exit Postgres Editor

\q # or "exit" if that doesn't work

Configure Postgres to accept remote connections

Edit `postgresql.conf`

sudo nano /etc/postgresql/17/main/postgresql.conf

Scroll through and find the following line, uncomment and change it

listen_address = '*'

Edit `pg_hba.conf`

🤷🏼‍♀️

This is your holy grail - treat it with care and don't tamper with it

sudo nano /etc/postgresql/17/main/pg_hba.conf

Add at the bottom

# PROD
hostssl prod_db prod_user 0.0.0.0/0 scram-sha-256
 
# DEV
hostssl dev_db dev_user  0.0.0.0/0 scram-sha-256

Notes:

hostssl forces TLS
scram-sha-256 is modern and good
Firewall will do the real IP locking
0.0.0.0/0 allows any connection from any IP range. Ideally, once your main hosting is configured (if using a static IP), you'll only allow traffic from there to prevent attacks.

Every time you change pg_hba.conf you will need to restart postgres server

sudo systemctl restart postgresql

Enable TLS

Create the certificate directory

sudo mkdir /etc/postgresql/ssl
sudo chown postgres:postgres /etc/postgresql/ssl
sudo chmod 700 /etc/postgresql/ssl

Generate Self Signed Cert (fine for now)

sudo -i -u postgres

openssl req -new -x509 -days 365 \
 -nodes -text \
 -out /etc/postgresql/ssl/server.crt \
 -keyout /etc/postgresql/ssl/server.key

chmod 600 /etc/postgresql/ssl/server.key

Exit postgres

\q # or "exit" if that doesn't work

Tell postgres to use the cert

sudo nano /etc/postgresql/17/main/postgresql.conf

Set:

ssl = on
ssl_cert_file = '/etc/postgresql/ssl/server.crt'
ssl_key_file  = '/etc/postgresql/ssl/server.key'

Restart service again

sudo systemctl restart postgres

Enable Firewall

We'll use UFW (Universal FW)

# If you're allowing all IPs through from previous step, use:
sudo ufw default allow incoming
 
# Otherwise deny it and follow next steps
sudo ufw default deny incoming

Allow SSH

sudo ufw allow from [your_ip] to any port 22

Allow Web hosting thorough (my example)

sudo ufw allow from VERSEL_IP_RANGE to any port 5432
sudo ufw allow from SEVALLA_IP_RANGE to any port 5432

Regardless of which above option you selected, set outgoing to open

sudo ufw default allow outgoing

Enable ufw:

sudo ufw enable

sudo ufw status

Test connection locally

psql "postgresql://prod_user@localhost/prod_db?sslmode=require"

Connection string example

DATABASE_URL="postgresql://prod_user:PASS@prod.pg.your-infra.com:5432/prod_db?sslmode=require"

🤷🏼‍♀️

Note: you might have some issues with url encoding the password (i did). Should be fine, just generate a password that doesn't need encoding - your fav chat bot will do that.

Setup Backups

Install

sudo apt install -y postgresql-client

Setup cron job to run daily

crontab -e

0 2 * * * pg_dump prod_db | gzip > /var/backups/prod_db_$(date +\%F).sql.gz

Before the before you start

Before you start

First Login

Create a non-root user (first time setup only)

Install Postgres

Sanity check it actually installed and is running

Create production and dev roles and databases

Create Roles (users)

Create databases

Lock users to their db only

Give users permission to see their db

Configure Postgres to accept remote connections

Edit postgresql.conf

Edit pg_hba.conf

Enable TLS

Generate Self Signed Cert (fine for now)

Tell postgres to use the cert

Enable Firewall

Test connection locally

Connection string example

Setup Backups

Later if you run out of storage you can push this to another server, but for now just leave it in one place, it's easier.

Edit `postgresql.conf`

Edit `pg_hba.conf`