Early look: PostgreSQL on Fly. We want your opinions.

18

Howdy folks. After you update flyctl to the latest version (0.0.166+) you’ll find some handy new commands to launch and manage a 2 node HA postgres cluster.

This is a preview, you probably don’t want to use this in production yet. :wink:

Launch

$ flyctl pg create

You’ll be prompted for name, organization, region, vm size, and volume size. After a few moments you’ll have a shiney new pg cluster and be presented with some help text like this:

Creating postgres cluster pg-demo in organization personal
Postgres cluster pg-demo created
  Username:    postgres
  Password:    f02313012f9ab47297651bd12b294623b780ee
  Hostname:    pg-demo.internal
  Proxy Port:  5432
  Leader Port: 5433
Save your credentials in a secure place, you won't be able to see them again!

Connect to postgres
Any app within the personal organization can connect to postgres using the above credentials and the hostname "pg-demo.internal."
For example: postgres://postgres:f02313012f9ab47297651bd12b294623b780ee@pg-demo.internal:5432

See the postgres docs for more information on next steps, managing postgres, connecting from outside fly:  https://fly.io/docs/reference/postgres/

It’ll take about 1-2 minutes for the cluster to be ready for connections.

Connecting

Once the cluster is up, you can connect to it however you want. Fly apps within the same organization or a wireguard peer from outside fly can use the app-name.internal hostname to find an instance over the private network.

For example, from my mac with wireguard, I can use psql to connect as a superuser:

psql postgres://postgres:f02313012f9ab47297651bd12b294623b780ee@pg-demo.internal:5432

Using the superuser credentials, you can create databases, users, and whatever else you need to configure for your apps.

We also have a shortcut for attaching postgres to another fly app:

flyctl postgres attach -a another-app --postgres-app pg-demo

This will create a user for the app, optionally create a database, grant permissions, and set a DATABASE_URL secret.

Here’s a few other helpful commands:

# list users
flyctl postgres users list pg-demo

# list databases
flyctl postgres databases list pg-demo

# detach an app
flyctl postgres detach -a another-app --postgres-app pg-demo

Monitoring

You can keep an eye on your postgres app just like any other fly app since these are normal fly apps, just with extra functionality sprinked on top…

flyctl status shows which VMs are replicas and leader.

$ flyctl status -a pg-demo
Instances
ID       VERSION REGION DESIRED STATUS            HEALTH CHECKS      RESTARTS CREATED
c66364b4 206     ord    run     running (replica) 3 total, 3 passing 0        7m31s ago
dba8a5cf 206     ord    run     running (leader)  3 total, 3 passing 0        8m50s ago

Other commands like flyctl status instance <vm>, flyctl checks list, flyctl logs etc all work as you’d expect.

Source code for the postgres fly app is available here: GitHub - fly-apps/postgres-ha: Postgres + Stolon for HA clusters.. We’ll share more technical bits on how it works soon along with documentation soon.

If you run into any issues or have feedback, let us know with a reply here. Or if it’s working flawlessly, you can let us know that too :smiley:

4 Likes