Guides / PostgreSQL

Run PostgreSQL on a small server

About 10 minutes. For Debian and Ubuntu. We recommend Box or bigger, because databases work best with memory for caching.

1. Install it

apt update
apt install -y postgresql
systemctl status postgresql

2. Create a user and database

Choose your own password:

sudo -u postgres psql -c "CREATE USER app WITH PASSWORD 'choose-a-long-password';"
sudo -u postgres psql -c "CREATE DATABASE appdb OWNER app;"

3. Test the login

psql -h 127.0.0.1 -U app appdb -c "select 1"

Your app connects to the same address:

postgresql://app:choose-a-long-password@127.0.0.1:5432/appdb

4. Keep it private

By default PostgreSQL only listens on your server, which is what you want. Keep your app on the same server, and do not open the database to the internet. If you need to reach it from your computer, use an SSH tunnel, with your own first port:

ssh -p 10000 -L 5432:127.0.0.1:5432 root@natbox.io

While that stays open, connect to 127.0.0.1:5432 on your computer.

5. Back it up

Dump your data to a file, then include that file in your backups:

sudo -u postgres pg_dump appdb > /root/appdb.sql

For a busy database, choose Box Plus. Your plan has a hard memory limit and no swap.