Self-host a Cheap PostgreSQL 16 Server on Hetzner Cloud with Terraform, Ansible, and Docker

TL;DR

• Hetzner available in US, Germany, Finland (+ AND SINGAPORE NOW!).
• Github Repo Link => Github: @s04/automated-hetzner-postgres
• Inspired by: https://pelle.io/posts/hetzner-rds-postgres/

PGBENCH Results:

Instance Information: Hetzner cx22, 2vCPU, 4GB RAM, 40GB SSD.

scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 maximum number of tries: 1 number of transactions per client: 10 number of transactions actually processed: 10/10 number of failed transactions: 0 (0.000%) latency average = 263.958 ms initial connection time = 220.764 ms tps = 3.788476 (without initial connection time)

Introduction

Hey 👋

As I’ve familarised myself with Postgres over the last few months I’ve been testing out things like NeonDB, Supabase, Vercel Postgres.

These are great if you fit into their free tiers but rapidly I’ve found the pricing stops making sense. I needed more than 8GB for my postgres DB which meant that I was going to be paying beyond the $25 in the Supabase Pro plan.

I also didn’t love the way Supabase abstracted away parts of Postgres having worked with a raw PostgresSQL endpoint up until now I found it difficult to transition.

The pricing is insane though:

Service	Plan	Price/Month	Storage	Compute
Neon DB	Free	$0	0.5 GiB	0.25 vCPU, 1 GB RAM, 24/7 for primary compute
Neon DB	Launch	$19	10 GiB, then $3.5 per 2GB	Up to 4 vCPU, 16 GB RAM, 300 compute hours
Supabase	Free	$0	500 MB	Shared resources
Supabase	Pro	$25	8 GB, then $0.125 per GB	Dedicated resources (unspecified)
Supabase	Medium	$75	8 GB, then $0.125 per GB	2-core ARM, 4 GB RAM
Supabase	Large	$125	8 GB, then $0.125 per GB	2-core ARM, 8 GB RAM
Hetzner (DIY)	CX22	~$4,88 (€4.51)	40 GB SSD (+ €5.24 per 100GB)	2 vCPU, 4 GB RAM
Hetzner (DIY)	CX32	~$8,76 (€8.09)	80 GB SSD (+ €5.24 per 100GB)	4 vCPU, 8 GB RAM
Hetzner (DIY)	CCX13	~$16,08 (€14.86)	80 GB SSD (+ €5.24 per 100GB)	2 vCPU, 8 GB RAM (Dedicated)

Notes:

1. Hetzner prices are converted from EUR to USD (approximate).
2. The Hetzner DIY solution provides full control over resources but requires setup and maintenance.
3. Neon DB and Supabase offer managed solutions with additional features beyond just compute and storage.

I’m aware that NeonDB and Supabase make a bunch of optimizations around Postgres which help with performance but nothing is stopping us from doing the same.

This setup will have some basic backups enabled however for things like PgBouncer and other creature comforts as well as performance tweaks you’ll have to setup yourself.

In a future blog post I will write instructions for setting up supabase on Hetzner.

Prerequisites

Before we start, make sure you have the following:

• A Hetzner Cloud account
• Terraform installed on your local machine
• Ansible installed on your local machine
• Basic knowledge of Terraform, Ansible, and Docker

A note on security

This guide is for educational purposes and small projects. It includes basic setup instructions but does not cover comprehensive security measures.

Key Points:

• Secure Your SSH Keys: Keep them safe and private.
• Backup Management: Basic daily backups are included; ensure you have a comprehensive backup strategy.
• Security: Additional hardening beyond UFW and fail2ban is recommended.
• Use this setup at your own risk. The author is not responsible for any data loss or security issues.

Step 1: Setting Up Terraform

First, let’s create our Terraform configuration. We’ll use two files: main.tf and variables.tf.

Here’s our main.tf:

terraform {
  required_providers {
    hcloud = {
      source = "hetznercloud/hcloud"
    }
  }
}

provider "hcloud" {
  token = var.hcloud_token
}

resource "hcloud_server" "postgres_server" {
  name        = "postgres-server"
  image       = "ubuntu-24.04"
  server_type = "cx22"
  location    = "nbg1"
  ssh_keys    = [hcloud_ssh_key.ssh-key.id]

  public_net {
    ipv4_enabled = true
    ipv6_enabled = true
  }
}

resource "hcloud_ssh_key" "ssh-key" {
  name       = "ssh-key"
  public_key = file("~/.ssh/id_rsa.pub")
}

output "server_ip" {
  value = hcloud_server.postgres_server.ipv4_address
}

And here’s our variables.tf:

variable "hcloud_token" {
  description = "Hetzner Cloud API Token"
  type        = string
}

This configuration will create a CX22 (2 vCPU, 4 GB RAM, 40 GB of Storage) server running Ubuntu 24.04 in Nuremberg. It’s a nice balance of performance and cost for our PostgreSQL server.

Step 2: Provisioning the Server

Now, let’s provision our server:

export TF_VAR_hcloud_token=your_hetzner_cloud_api_token
terraform init
terraform plan 

Check your terraform plan and make sure it’s creating the right resources that you expect.

terraform apply

After applying, Terraform will output the IP address of your new server. Make note of this for the next step!

Step 3: Preparing Ansible

Create an inventory.ini file with the following content:

[postgres_servers] 
XXX.XXX.XXX.XXX ansible_user=root

Replace XXX.XXX.XXX.XXX with the IP address of your server.

Step 4: Configuring the Server with Ansible

Now for the fun part! Let’s set up our playbook.yaml to configure the server. This playbook does the following:

1. Updates the system
2. Installs Docker
3. Sets up a PostgreSQL container
4. Configures UFW firewall
5. Installs fail2ban for security
6. Sets up daily backups
7. Disables SSH password authentication

Here’s a breakdown of some key parts:

.env

POSTGRES_DB=mydatabase
POSTGRES_USER=myuser
POSTGRES_PASSWORD=verysafeunhackablepassword777

Use the .env file to set your variables, these are then used in the playbook.yaml. Make sure it’s included in your .gitignore so you don’t accidentally commit it to version control.

- name: Run PostgreSQL container
  docker_container:
    name: postgres
    image: postgres:16
    state: started
    restart_policy: always
    networks:
      - name: postgres_network
    env:
      POSTGRES_DB: "{{ postgres_db }}"
      POSTGRES_USER: "{{ postgres_user }}"
      POSTGRES_PASSWORD: "{{ postgres_password }}"
    ports:
      - "0.0.0.0:{{ postgres_external_port }}:{{ postgres_internal_port }}"
    volumes:
      - "{{ postgres_data_dir }}:/var/lib/postgresql/data"
      - "{{ postgres_init_dir }}:/docker-entrypoint-initdb.d"

This task sets up our PostgreSQL container with the specified environment variables and port mappings.

- name: Set up daily PostgreSQL backups
  cron:
    name: "Daily PostgreSQL backup"
    minute: "0"
    hour: "1"
    user: root
    job: "docker exec postgres pg_dumpall -U {{ postgres_user }} > {{ postgres_data_dir }}/backups/postgres_$(date +\\%Y\\%m\\%d).sql"

This task sets up a daily backup of our PostgreSQL database at 1:00 AM.

Step 5: Running the Ansible Playbook

Now, let’s run our Ansible playbook:

ansible-playbook -i inventory.ini playbook.yaml

This command will configure your server according to the specifications in the playbook.

Usage Guide

Connecting to Your PostgreSQL Server

To connect to your PostgreSQL server, you can use any PostgreSQL client. Here’s a basic example using psql:

1. Install psql (PostgreSQL CLI tool) if you don’t have it installed already. For example, on Ubuntu:

   sudo apt update
   sudo apt install postgresql-client

2. Connect to the database:

   psql -h <YOUR_SERVER_IP> -U myuser -d mydatabase

   Replace <YOUR_SERVER_IP> with the IP address of your server. You will be prompted to enter the password for the myuser role, which you set in the .env file.

Creating a Table

Once connected, you can create a table using SQL commands. Here’s an example:

CREATE TABLE example_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

Backup and Restore

• Backups: Daily backups are configured using a cron job and stored in /var/lib/postgresql/data/backups.

• Manual Backup:

  docker exec postgres pg_dumpall -U myuser > /path/to/backup.sql

• Restore:

  cat /path/to/backup.sql | docker exec -i postgres psql -U myuser

Conclusion

This guide walked you through setting up a cost-effective PostgreSQL server on Hetzner Cloud using Terraform, Ansible, and Docker. The setup provides a robust and secure environment for your databases, perfect for development and small projects.

Remember to always follow best practices for security, backup, and data management, especially in a production environment. If you need more power, you can easily scale your server by upgrading to a larger instance type or adding more storage.

Happy coding, and may your queries be ever optimized! 🚀🐘