This is a quick tutorial for enabling the pgvector extension on your Fly Postgres instance. This extension is useful when adding embedding-related features to your app.

I’m assuming you already have a Fly app running called example-app with a Fly Postgres database app called example-app-db.

If you already have data stored in your Fly Postgres instance, be warned this will delete it all! (Ask me how I know! 🙃)

If you want to maintain your existing data, you could probably use some volume forking, but that’s not included in this post.

Also, once you switch the database app to “self-managed” mode, the flyctl postgres commands won’t work any more.

Finally, be aware this will change your database passwords, so you’ll need to update your app secrets before the new database app will work (Again, ask me how I know 😬)

The gist is that Fly Postgres instances are “just” deployed copies of the open-source postgres-ha app. So at a high level, we need to:

  1. Clone postgres-ha.
  2. Update the Dockerfile to install pgvector.
  3. Deploy the change overtop our existing database app.
  4. Reconfigure our application server to use it.

So, first let’s check it out:

git clone https://github.com/fly-apps/postgres-ha postgres-ha
cd postgres-ha

This comes with a pre-written fly.toml, but we need to update it to our app name. In the following examples, I’ll use example-app as the name of the Fly app that we’re deploying a database for.

flyctl config save -a example-app-db

Then add this to line 45 in the Dockerfile (below the timescaledb-2-postgresql-$PG_MAJOR line):

postgresql-$PG_MAJOR-pgvector \

For an example of what the Dockerfile change looks like in context, see this commit.

Then deploy the app:

flyctl deploy

Enable the extension

To connect to the database and enable the extension, we need to proxy a local connection with flyctl proxy (since flyctl postgres connect doesn’t work anymore):

flyctl proxy 5432

You’ll need to get the database password as well, for example with:

flyctl ssh console

# in db container
echo $OPERATOR_PASSWORD

Then you can connect locally with:

psql postgres://postgres:PASSWORD@localhost:5432

And run the following commands:

-- enable pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- create your app DB if necessary
CREATE DATABASE db_name;

Since the password changed when we deployed over the old app, we’ll also need to remember to update our application secret to use the new one:

flyctl secrets set -a example-app \
DATABASE_URL=postgres://postgres:PASSWORD@example-app-db.flycast:5432/db_name?sslmode=disable

And now we should be all set! Happy embedding!