PostgreSQL

Hatchbox supports two types of PostgreSQL services: managed and unmanaged.

• Managed PostgreSQL runs on a service like DigitalOcean, PlanetScale, Crunchy Data, Amazon RDS, etc.
• Unmanaged PostgreSQL runs on a server inside your Hatchbox cluster (for free!).

Managed PostgreSQL

To use managed PostgreSQL database with Hatchbox:

1. Create the database on your provider of choice
2. Add an environment variable to your app to connect to your database.
   This is typically DATABASE_URL for Ruby applications.

Backups, upgrades, maintenance, etc will all be handled by your PostgreSQL provider.

Unmanaged PostgreSQL

For unmanaged PostgreSQL, choose a server in your cluster and assign it the PostgreSQL role. This will install the postgresql server package on the server and allow you to create databases in Hatchbox.

Connecting to your PostgreSQL databases remotely

If you'd like to connect to your PostgreSQL database from your local machine, you will need to use an SSH tunnel.

Let's use TablePlus for example:

1. Under "Over SSH", add your server's public IP address and the "deploy" user.
2. Check the "Use SSH key" box. Optionally, import your private key if you don't have it in your SSH config.
3. Set the database host to "127.0.0.1" since you'll be connected over SSH.
4. Set the username and password for your Hatchbox database.
5. Test the connection to confirm everything works.

Upgrading PostgreSQL versions

To upgrade your server's PostgreSQL version, you will need the old and new versions of postgresql server and use pg_upgradecluster to upgrade between versions.

https://manpages.ubuntu.com/manpages/trusty/man8/pg_upgradecluster.8.html

For example, to upgrade from PostgreSQL 17 to the latest installed version, you would run:

pg_upgradecluster 17 main