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

Another +1 for me if you’re still looking for volunteers to try this out. Would love to get my hands on it!

Yes! Give us a day or two and we’ll post some instructions. :slight_smile: We have a show stopper bug we’re fixing before we get anyone else using it.

1 Like

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

Spun up a test Postgres and it works great!

Any ideas what the backup story will look like once this is out of preview (or even still in preview)?
My ideal would be the ability to backup to an S3 compatible storage on a schedule that is customisable, but has good defaults. For a basic backup, saving to a fly volume would be okay too.

Does stolon support anything by default that would make your lives easier?

1 Like

Backup off-site would be nice. Once a day full backup + streaming updates to allow PITR. Would be nice if you can “clone” a new postgres cluster from a backup timestamp. Or, for staging, fork an existing database by using the last full backup and then streaming the last sub one day.

That said, would be nice if you can support Redis clusters too.

Everything you do with flyctl works as well as through the GraphQL API I guess?

How many IOPS will postgres get? Are there ways to e.g choose SSD as storage?

2 Likes

We’re going to rely on disk snapshots for backups and also ship features for quickly cloning disks (or restoring from backup).

Our goal is to build plumbing to make things like Postgres/MongoDB/other HA work and then make the actual app projects open source.

The Postgres HA cluster app is what we’re running for this, which means two things:

  1. You can make pull requests to enhance functionality
  2. You can fork it and run it without going through our launcher

One (unique) thing we’re doing is giving out replica privileges on Postgres clusters. You can, for example, create a Postgres replica that streams to S3. We’d love to roll all that into our primary Postgres HA project, but you shouldn’t have to wait on us to do it. :slight_smile:

All our persistent volumes are nvme drives, each between 2 and 7TB. You can expect to get a guaranteed proportional amount of IOPs (500GB is about 25% of a 2TB NVMe capacity) with much higher bursts. You will probably be surprised how fast these disks are.

2 Likes

I’m repeatedly getting “Oops, something went wrong! Could you try that again?” when I try to run fly pg create and then complete the questions.

Is this still available to test out in flyctl?


$ fly version  
flyctl 0.0.167

Not sure what was going on with the CLI, but I was able to create one through the API manually. :slight_smile:

Can you try with the CLI again, but with debug logging?

LOG_LEVEL=debug fly pg create

Sure, same error message and nothing too useful in the output.

DEBUG Loaded flyctl config from/Users/scott/.fly/config.yml
DEBUG Working Directory: /Users/scott
DEBUG App Config File: 
? App name: contaim-cli-test
DEBUG --> POST https://api.fly.io/graphql {{"query":"{ organizations { nodes { id slug name type } } }","variables":null}
}
DEBUG <-- 200 https://api.fly.io/graphql (358.69ms)
? Select organization: Contaim (contaim-a9d13f1f-a0b9-44df-8d78-d55fbce237aa)
DEBUG --> POST https://api.fly.io/graphql {{"query":"query { platform { requestRegion regions { name code gatewayAvailable } } }","variables":null}
}
DEBUG <-- 200 https://api.fly.io/graphql (183.45ms)
Oops, something went wrong! Could you try that again?

@Scott could paste the output of https://debug.fly.dev here?

=== Headers ===
X-Forwarded-Ssl: on
Via: 2 fly.io
Accept-Encoding: gzip, deflate, br
Fly-Forwarded-Proto: https
Fly-Forwarded-Ssl: on
Fly-Forwarded-Port: 443
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
Accept-Language: en-us
X-Forwarded-Proto: https
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_6) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/14.0.2 Safari/605.1.15
X-Forwarded-Port: 443
Fly-Region: chi

=== ENV ===
FLY_APP_NAME=debug
FLY_REGION=ord
FLY_VM_MEMORY_MB=128
HOME=/root
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
TERM=linux
WS=this
is
a
test
cgroup_enable=memory

2021-02-02 04:39:55.750442595 +0000 UTC m=+619738.961021422

@michael Can you remove my IP address information once you’ve got all you need?

@Scott thanks for the debug info. Looks like we couldn’t detect your region which caused a panic in flyctl (my fault) – could you update to the latest flyctl version and try again? Thanks!

Yep, looks good! I was able to create/delete it. Thanks for the quick turnaround.

I would love to give this a shot as soon as daily backups are a thing. Without that I would be hesitant to try it. Also is there PGBouncer or something of the sort that will be supported?

Also would be nice to support Redis in a similar fashion (globally shared between the regions as a full blown instance).

Is it possible to connect to it externally without wireguard? For example inside of github actions to run migrations?

We’ll be offering volume snapshots for backups. More on that soon.

We’re planning on baking PGBounder in the postgres app but haven’t gotten to it yet.

We have a prototype of this you can test out now: fly.io/launch/keydb. It’s using KeyDB instead of Redis which supports master-master replication.

Not right now, but you could launch an application that proxies an exposed port to postgres over the private network.

Question on pricing, do you technically pay 2x the vm pricing to cover the replica as well?

It’s just a fly app with sprinkles on top so pricing is the same. You can add more replicas or scale back to one if you want. We’ll have some docs covering scaling scenarios in a few days.

hmmm I guess I am confused, it seems there is always a replica, is it a bad idea to only run with one instance (no replica)?

fly autoscale show outputs Disabled - How would we remove the replica or add more in the future if need be? Sorry If I am completely missing something here haha.

This brings up another points I wanted to ask about, do pg apps scale in a similar way to normal fly apps? If so, how does this work? Is this based on connection counts? How many connections do we get per vm?

Trying to grasp how scaling works when app servers start scaling and eating up pg connections and how we keep the PG scale in sync if that makes sense.

Thanks in advance! SUPER excited to see PG on fly :raised_hands:t2: