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:

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
);

This command creates a table named example_table with three columns: id, name, and age.

Backup and Restore

• Backups: As mentioned, daily backups are configured using a cron job. The backups are stored in the /var/lib/postgresql/data/backups directory inside the Docker container.

• Manual Backup: To manually backup the database, you can run:

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

• Restore: To restore from a backup, use:

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

Scaling and Storage Management

1. Adding Storage:

   To add more storage, you can resize the disk attached to your server. This process varies based on the cloud provider and virtualization technology, but generally involves:

   • Increasing disk size in the cloud provider’s console.
   • Resizing the filesystem within the VM.

   Be sure to follow Hetzner’s documentation for the specific steps required.

2. Scaling the Node:

   If you require more compute resources (CPU/RAM), you can create a new, larger instance.

   !!! If you already spun up a VPS previously you need to change the hcloud_token to another project or change the terraform resource name.

    resource "hcloud_server" "postgres_server_larger" { # changed
        name        = "postgres-server-larger" # changed
        image       = "ubuntu-24.04"
        server_type = "cx32" # changed
        location    = "nbg1"
        ssh_keys    = [hcloud_ssh_key.ssh-key.id]
   
        public_net {
            ipv4_enabled = true
            ipv6_enabled = true
        }
    }
   

   To do this:

   • Update the server_type in your main.tf Terraform file to a larger plan, such as cx32 or ccx13.
   • Run terraform apply to create the new server.
   • Migrate your data from the old server to the new server.

Destroying the Setup

To clean up and destroy the setup:

1. Destroy Resources: Run the following command to destroy the infrastructure created by Terraform:

   terraform destroy
   

   This will remove the server and associated resources.

2. Remove Local Files: Delete any sensitive files such as SSH keys, .env files, or backups stored locally to ensure no sensitive information is left behind.

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! 🚀🐘