How to Set Up a PostgreSQL Database on Hetzner in 10 Minutes

Setting up a PostgreSQL database on Hetzner involves creating a Cloud Server, installing the PostgreSQL software, and configuring security settings to allow connections. You can have a fully functional database running on a high-performance VPS (Virtual Private Server) in under 10 minutes for less than €5 per month. This setup provides a dedicated environment for your data, giving you more control and lower costs than managed database providers.

What do you need before starting?

Before you begin the installation, you need a few basic tools and accounts ready. Ensure you have the following items on your checklist:

A Hetzner Cloud Account: Sign up at hetzner.com and add a payment method to activate your account.
An SSH Key: This is a secure digital key used to log into your server without a password.
An SSH Client: If you are on Windows, you can use PowerShell or Terminal; Mac and Linux users can use the built-in Terminal.

We recommend using the latest Ubuntu 24.04 LTS (Long Term Support) image for your server because it offers the best balance of stability and modern features.

How do you create your database server?

Log into your Hetzner Cloud Console and create a new "Project" to keep your resources organized. Click the "Add Server" button to begin the configuration process.

Select a location closest to your users, such as Nuremberg or Helsinki, to reduce latency (the time it takes for data to travel). Choose the "Ubuntu 24.04" image and the "Shared vCPU" plan, specifically the CX22 instance, which provides enough RAM for a starter database.

Under the "Authentication" section, select your SSH key so you can log in securely. Give your server a recognizable name like "my-postgres-db" and click "Create & Buy Now."

How do you install PostgreSQL on Ubuntu?

Once your server status shows "Running," copy its IP address from the dashboard. Open your terminal and type ssh root@your_server_ip to connect to the machine.

First, update the local package index to ensure you get the latest software versions. Run the command sudo apt update followed by sudo apt upgrade -y.

Now you can install the PostgreSQL software along with additional tools. Run sudo apt install postgresql postgresql-contrib -y to finish the installation.

How do you create a database and a user?

By default, PostgreSQL creates a special system user named postgres. You should switch to this user to perform administrative tasks within the database environment.

Type sudo -i -u postgres to enter the postgres user shell. Then, type psql to enter the interactive terminal where you can run database commands.

Create a new database for your application by typing CREATE DATABASE my_app_db;. Make sure to include the semicolon at the end of every command, as this tells the system the instruction is finished.

Next, create a user with a strong password to manage this database. Run CREATE USER my_user WITH PASSWORD 'your_secure_password'; and then grant them permissions with GRANT ALL PRIVILEGES ON DATABASE my_app_db TO my_user;.

How do you allow remote connections safely?

By default, PostgreSQL only listens for connections from the server itself. To connect from your local computer or another server, you must change the listening address.

Open the configuration file using a text editor like Nano by running sudo nano /etc/postgresql/16/main/postgresql.conf. Find the line that says #listen_addresses = 'localhost' and change it to listen_addresses = '*'.

This change tells the database to listen on all available network interfaces. However, you still need to authorize your specific IP address in the access control file.

Open the second configuration file with sudo nano /etc/postgresql/16/main/pg_hba.conf. Add a line at the bottom: host all all your_local_ip/32 md5. Replace "your_local_ip" with your actual home or office IP address to ensure only you can connect.

How do you secure the server with a firewall?

Hetzner provides a free Cloud Firewall that acts as a shield in front of your server. You should never leave your database port (5432) open to the entire internet.

Go to the "Firewalls" tab in the Hetzner Console and create a new firewall template. Add an "Inbound" rule for SSH (Port 22) so you can still manage the server.

Add another "Inbound" rule for PostgreSQL (Port 5432). In the "Source" field, enter your specific IP address instead of "Anywhere."

This setup ensures that even if someone discovers your database port, the firewall will block their connection attempt. Applying these rules protects your data from common automated attacks and unauthorized access.

Common Gotchas and Troubleshooting

If you cannot connect to your database, the most common culprit is a forgotten service restart. Every time you change a .conf file, you must run sudo systemctl restart postgresql for changes to take effect.

Another frequent issue is a mismatch in the pg_hba.conf file. If the authentication method is set to peer, it expects your Linux username to match your database username. Using md5 or scram-sha-256 is usually better for remote connections because it relies on passwords.

Don't worry if you get an "Access Denied" error on your first try. Check your Hetzner Firewall settings first, as it is often the most restrictive layer of security.

Next Steps

Now that your database is running, you can start connecting your applications. You might want to explore tools like pgAdmin or DBeaver, which provide a visual interface for managing your data.

As your project grows, look into setting up automated backups using tools like pg_dump. It is also a good idea to learn about "Connection Pooling" with tools like PgBouncer to help your database handle more users simultaneously.

For detailed guides, visit the official PostgreSQL documentation.