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

83

You can run stolonctl status to detect if it’s been inited.

Something like this would work in bash:

inited=$(stolonctl status || echo "false")
if [[ "$inited" != "false" ]]; then
   stolonctl update ...
fi

This relies on env vars being set, though, I don’t think our start.sh script actually sets those.

84

I’m also seeing a sawtooth pattern in the replication lag graph:

Screen Shot 2021-06-03 at 7.30.48 PM
Screen Shot 2021-06-03 at 7.30.48 PM1764×684 44.1 KB

For fun, I’m running six (one leader + five replicas) DB instances, but that’s wildly aspirational/experimental, since I don’t have any real data yet… but that also makes it weirder that replication would fall so far behind, right?

Checking fly checks list or fly vm status <db instance ID> shows ~0 replication lag, so I’m a little confused about this monotonically increasing >200000 number on the y-axis.

Am I doing/interpreting this all wrong? Should I clone GitHub - fly-apps/postgres-ha: Postgres + Stolon for HA clusters as Fly apps. and deploy it myself, to play with the queries?

85

That might actually be a bug in our replication lag metric. Is it possible those increases happen during periods where there are no writes to the DB?

86

@kurt Yep that was it! Just did a write, and now we’re back to 0:

Screen Shot 2021-06-03 at 7.45.33 PM
Screen Shot 2021-06-03 at 7.45.33 PM1670×684 44.9 KB

87

Queston: it appears that the app name when creating a postgres app via fly postgres create has to be globally unique. Is there a technical reason for this?

88

Postgres runs as a normal fly app, and all fly apps require a globally unique name.

Edit: pulling on that thread a little… we thought about namespacing apps under orgs, but it complicated the UX. We wanted DNS (eg app-name.fly.dev and app-name.internal), flyctl args, and the registry to work out of the box without having to know the org. The other issue is breaking DNS and registry references when an app moves to another org, which happens a lot. We could work around these things, but the time is better spent elsewhere.

89
  1. wondering if there was any movement on the backup and disaster recovery story for this?
  2. backup/restore needs to be “checked off” before we can seriously launch prod apps on fly.
  3. i like the previously suggested idea of backups to S3 (Or equivalent blob storage); even using postgres dumps saved into S3 as a way to bulk migrate an existing database.
  4. looking forward to fully managed postgres; even future managed DBs (cockroachdb? yugabyte?)

Thanks!

90
  1. We have made progress on volume snapshots and exposing backup/recovery tools, but it’s not quite ready yet. Hopefully very soon. This will apply to all apps, not just Postgres, but we’ll cover pg specifics in our docs
  2. I agree!
  3. We’re focusing on PIT snapshots that you can restore to fly volumes first, but you’ll be able to download those snapshots eventually and do whatever you need with the full file system. We haven’t covered migrating to fly yet, but that’s something we’ll document before leaving beta
  4. Fully managed Postgres is next up after we launch lightly managed Postgres. Once those are out the door we can dabble in other databases. We’re excited about that though.

I apologize for so many “soons” – Postgres is getting a bunch of attention right now and a lot is changing, most of it behind the scenes. We’ll post an update… soon :slight_smile:

91

sounds good. excited for the update.

92

A few updates to report:

  1. PG apps are now being provisioned with Postgres 13.

  2. Etcd has been made the new default backend store for Stolon. We found that the switch from Consul to Etcd provided a much more stable connection and results in far fewer interruptions during leadership changes, version upgrades, etc.

PG apps provisioned via flyctl will have Etcd enabled by default. If you are wanting to manually provision using the postgres-ha repo, you can configure Etcd as your backend store by modifying your config file as shown below.

Example:

[env]
  BACKEND_STORE = "etcdv3"  

[experimental]
  enable_etcd   = true
93

Nice! Does this mean there is now a global etcd cluster available along with Consul?

94

@joshu Yep! :slight_smile:

95

How are existing apps going to be upgraded? Would flyctl pg update be a good idea? I’m thinking it would list the changes that you are about to make and ask you if you want to proceed.

96

@rugwiro As of right now, existing apps will not be upgraded. PG upgrades across major versions can be somewhat complicated as the system tables and internal data format can change. Tools like pg_upgrade are available, so long as the internal data format doesn’t change with the new version. Another thing with pg_upgrade that makes it slightly awkward, is that it requires binaries for both the current and target version to be present on the file system…

That all being said though, this is something we plan to streamline in the nearish future. Minor version upgrades thankfully are more straight forward.

97

Timescale being built in to clustered postgres would sell me on fly.io, no question. So… just here to put a big vote in for timescale.

98

Hey everyone,

Few updates to report:

  1. Postgres restores are now available!
    You can checkout the documentation here: Multi-region PostgreSQL

  2. We’ve made some solid improvements to our health checks.

  • Fixed VM pressure check output and false-positive failure condition.
  • Execution times have been added to individual check.
  • Improved replication lag output.
  • Health check related timeouts should now provide more information surrounding what actually caused the timeout, as opposed to just a blanket Context deadline exceeded.
[✓] transactions: readonly (239.05µs)
[✗] replication: Timed out (4.99s)
[-] connections: Not processed

Note:

If you are running an older image and would like to take advantage of the latest improvements, you can upgrade to the latest image by performing the following steps:

  1. Pull down your configuration file if you haven’t already:
 fly config save --app <app-name>
  1. Redeploy your app while specifying the target image.
 fly deploy . --app <app-name> --image flyio/postgres:<major-pg-version>`

For example, if you are running PG12 you would specify flyio/postgres:12 as your target image.

If you have any questions or feedback let us know!

99

Nice. Couple of questions

  • Is there a way to promote a replica to be writable?
  • The external port in [[services.ports]] isn’t really necessary, right? Databases can still be accessed internally on the app.internal:5432 address, right?
  • Is there a way to download a snapshot?
100

@sudhir.j

Is there a way to promote a replica to be writable?

I’m not sure what you mean. Are you asking whether you can promote a replica to primary, or are you asking whether you can reconfigure the replica to accept both reads and writes?

The external port in [[services.ports]] isn’t really necessary, right? Databases can still be accessed internally on the app.internal:5432 address, right?

Yep, that is correct!

Is there a way to download a snapshot?

Not yet, but should be coming soon! That being said though, if you ever need a copy of your data you are always welcome to run a pg_dump against your dataset.

101

Are you asking whether you can promote a replica to primary, or are you asking whether you can reconfigure the replica to accept both reads and writes?

Now that you mention it, both I guess?

  1. Promoting to primary in case I’m planning to migrate an app to a different region, and
  2. reconfigure the replica to take writes in case I want to “fork” the DB a la Heroku Postgres. Forking will cut the connection, though of course, so it’s more for when I want to make a staging environment off prod data.
102

@sudhir.j

  1. Promoting to primary in case I’m planning to migrate an app to a different region

Got it, so you can manage failovers using Stolon’s stolonctl.

You can leverage this by:

  1. Ssh’ing into one of your VM’s:
fly ssh console --app <app-name>
  1. Exporting the necessary environment variables:
  export $(cat /data/.env | xargs)

Now you should be able to use stolonctl failkeeper to handle the failover:

If you are wanting to promote a node outside of your PRIMARY_REGION, then you will need to adjust the value of the PRIMARY_REGION variable within your fly.toml configuration file to match your target region. We restrict leadership eligibility to nodes within the PRIMARY_REGION.

It’s a bit of a process right now, but we do have plans to streamline this in the future!

  1. reconfigure the replica to take writes in case I want to “fork” the DB a la Heroku Postgres. Forking will cut the connection, though of course, so it’s more for when I want to make a staging environment off prod data.

Unfortunately, this is not something we support. There are Postgres forks that support this, but it’s something our current architecture will support.

If you’re staging environment doesn’t require up-to-date data, i’d recommend just using our Restore feature. :slight_smile:

Hope that helps!