Postgres on Fly
Postgres, or PostgreSQL, is a powerful open-source object relational database system.
About Postgres on Fly
Postgres on Fly is a regular Fly.io app, with an automated creation process and some extensions to simplify management. It relies on building blocks available to all Fly apps, like flyctl
, volumes, private networking, health checks, logs, metrics, and more. The source code is available on GitHub to view and fork.
About Free Postgres on Fly
You can use Fly's free resource allowance in one place, or split it up. The following Postgres configurations fit within the free volume usage limit:
- single node, 3gb volume (single database)
- 2 x 1gb volumes (database in two regions, or a primary and replica in the same region)
- 3 x 1gb volumes (database in three regions)
If you want to keep your whole project free, save some compute allowance for your other apps.
See also How to convert your not-free Postgres to free Postgres.
Creating a Postgres app
To create a Postgres cluster, use the flyctl postgres create
command. The command will walk you through the creation with prompts for name, region, and VM resources.
flyctl postgres create
? App Name: c-pg-test
Automatically selected personal organization: Chris Nicoll
? Select region: [Use arrows to move, type to filter]
> ams (Amsterdam, Netherlands)
cdg (Paris, France)
dfw (Dallas, Texas (US))
ewr (Secaucus, NJ (US))
fra (Frankfurt, Germany)
gru (São Paulo)
? Select region: mia (Miami, Florida (US))
For pricing information visit: https://fly.io/docs/about/pricing/#postgresql-clusters
During this process, you get to choose from several preset resource configurations for the app:
? Select configuration: [Use arrows to move, type to filter]
> Development - Single node, 1x shared CPU, 256MB RAM, 1GB disk
Development - Single node, 1x shared CPU, 512MB RAM, 10GB disk
Production - Highly available, 1x shared CPU, 256MB RAM, 10GB disk
Production - Highly available, 1x Dedicated CPU, 2GB RAM, 50GB disk
Production - Highly available, 2x Dedicated CPU's, 4GB RAM, 100GB disk
Specify custom configuration
The "Production" options give you a two-node cluster in a leader-replica configuration. A single-node "Development" instance can readily be scaled and expanded to more regions.
Creating postgres cluster c-pg-test in organization personal
Postgres cluster c-pg-test created
Username: postgres
Password: 8a93cbc09798f3805056333072bd2b35be7eb634b13a05c3
Hostname: c-pg-test.internal
Proxy Port: 5432
PG Port: 5433
Save your credentials in a secure place, you won't be able to see them again!
Monitoring Deployment
1 desired, 1 placed, 1 healthy, 0 unhealthy [health checks: 3 total, 3 passing]
--> v0 deployed successfully
Connect to postgres
Any app within the personal organization can connect to postgres using the above credentials and the hostname "c-pg-test.internal."
For example: postgres://postgres:8a93cbc09798f3805056333072bd2b35be7eb634b13a05c3@c-pg-test.internal:5432
See the postgres docs for more information on next steps, managing postgres, connecting from outside fly: https://fly.io/docs/reference/postgres/
After answering all the prompts, you'll see a message saying that the cluster is being created, followed by a deployment monitor watching as the app is launched. Take heed of the reminder to save your password in a safe place!
Your new Postgres cluster is ready to use once the deployment is complete.
Before we get any further, note that the automated Postgres creation process doesn't generate a fly.toml
file in the working directory. This means that when you use flyctl
commands with Fly Postgres apps, you'll have to specify the app, like so:
flyctl <command> -a <postgres-app-name>
Connecting to Postgres
How you connect to Postgres depends on the tools you're using. Connection string URIs are a common way to describe a connection to a postgres server.
Connection strings have the following format:
postgres://{username}:{password}@{hostname}:{port}/{database}?options
The output from flyctl postgres create
contains all the values you need to make a connection string to your database.
Connecting to Postgres From Within Fly
As a Fly.io application, your Postgres app is accessible through Fly's private networking. This means applications within the same organization can look up the app at appname.internal
. This name, when looked up, can return one or more IPv6 addresses.
Connecting to Postgres From Outside Fly
On a Machine With flyctl
Installed
To connect to your Postgres database from outside your Fly organization, you need a WireGuard connection. However, flyctl
on your local machine can connect using user-mode WireGuard magic, without you having to set up your own WireGuard tunnel.
For a psql
shell, you can just use the flyctl postgres connect
command:
flyctl postgres connect -a <postgres-app-name>
You can also forward the server port to your local system with flyctl proxy
:
flyctl proxy 5432 -a <postgres-app-name>
Then connect to your Postgres server at localhost:5432. Using psql
again, as a trivial example, it would look like this:
psql postgres://postgres:<password>@localhost:5432
If you already have something else listening on port 5432, you can run this instead:
flyctl proxy 15432:5432 -a <postgres-app-name>
Then connect to localhost:15432.
As with all your Fly.io apps, you can get a root console on your app's VM using flyctl ssh.
With Your Own WireGuard Tunnel
If you have an active WireGuard tunnel to your organization on our private network, you can connect to your Postgres cluster the same way you would from a Fly app within the same organization. For example, the following command would start an interactive terminal session on the cluster leader with psql
:
psql postgres://postgres:secret123@appname.internal:5432
Attaching an App to a Postgres App
Using the superuser credentials, you can create databases, users, and whatever else you need for your apps. But we also have the flyctl postgres attach
shortcut:
flyctl postgres attach --app <app-name> --postgres-app <postgres-app-name>
When you attach an app to Postgres, a number of things happen:
- A database and user are created in the Postgres App. If the attached app in named "myapp", both the database and the user are named "myapp" too.
- The user is allocated a generated password.
When the Attached app starts it will find an environment variable DATABASE_URL
set to a Postgres connection URI with the username, password, host, port and dbname filled in.
Detaching an App From Postgres
Use flyctl postgres detach
to remove postgres from the app.
flyctl postgres detach --app <app-name> --postgres-app <postgres-app-name>
This will revoke access to the attachment's role, remove the role, and remove the DATABASE_URL
secret. The database will not be removed.
High Availability
Fly Postgres uses stolon for leader election and streaming replication between 2+ postgres servers. It provides a number of things, including a “keeper” that controls the postgres process, a "sentinel" that builds the cluster view, and a “proxy” that always routes connections to the current leader.
5433 is the port the keeper tells postgres to listen on. Connecting there goes straight to Postgres, though it might be the leader or the replica. Since clients need writes, the proxy is listening on the default 5432 port so clients are connected to the current leader.
If the leader becomes unhealthy (eg network or hardware issues), the proxy drops all connections until a new leader is elected. Once it’s ready, new connections go to the new leader automatically. The previoius leader's VM will be replaced by another VM which will rejoin the cluster as a replica.
In general, your clients should connect to port 5432.
Users / Roles
A Postgres cluster is configured with three users when created:
postgres
- a role with superuser and login privileges that was created for you along with the cluster. Since thepostgres
role has superuser rights, it's recommended that you only use it for admin tasks and create new users with access restricted to the minimum necessary for applicationsflypgadmin
- this role is used internally by fly to configure and query the postgres clusterrepluser
- this is the user replica servers us for replication from the leader
You can view a list of users using flyctl
flyctl postgres users list c-pg-test
Running flyadmin user-list
USERNAME SUPERUSER DATABASES
flypgadmin true postgres
postgres true postgres
repluser false postgres
Databases
One Postgres cluster can host multiple databases
Listing Databases
You can view a list of databases with flyctl
:
flyctl postgres db list c-pg-test
Running flyadmin database-list
NAME USERS
postgres flypgadmin,postgres,repluser
Connection Examples
Connecting With Ruby (docs)
Ruby apps use the pg
gem to connect to postgres.
require 'pg'
# Output a table of current connections to the DB
conn = PG.connect("postgres://postgres:secret123@postgresapp.internal:5432/yourdb")
conn.exec( "SELECT * FROM pg_stat_activity" ) do |result|
puts " PID | User | Query"
result.each do |row|
puts " %7d | %-16s | %s " %
row.values_at('pid', 'usename', 'query')
end
end
Connecting With Rails (docs)
Rails apps automatically connect to the database specified in the DATABASE_URL
environment variable.
You can set this variable manually with flyctl secrets set
flyctl secrets set DATABASE_URL=postgres://postgres:secret123@postgresapp.internal:5432/yourdb
or by attaching the postgres database to your fly app.
Connecting With Go (docs)
pgx
is the recommended driver for connecting to postgres. It supports the standard database/sql
interface as well as directly exposing low level / high performance APIs.
First, add github.com/jackc/pgx/v4
as a module depepdency.
go get github.com/jackc/pgx/v4
The following program will connect to the database in DATABASE_URL
and run a query.
package main
import (
"database/sql"
"fmt"
"os"
_ "github.com/jackc/pgx/v4/stdlib"
)
func main() {
db, err := sql.Open("pgx", os.Getenv("DATABASE_URL"))
if err != nil {
fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
os.Exit(1)
}
defer db.Close()
var greeting string
err = db.QueryRow("select 'Hello, world!'").Scan(&greeting)
if err != nil {
fmt.Fprintf(os.Stderr, "QueryRow failed: %v\n", err)
os.Exit(1)
}
fmt.Println(greeting)
}
Connecting With Node.js (docs)
You'll use the pg
npm module to connect to postgres from a node.js app.
const { Client } = require('pg')
const client = new Client({connectionString: process.env.DATABASE_URL})
await client.connect()
const res = await client.query('SELECT $1::text as message', ['Hello world!'])
console.log(res.rows[0].message) // Hello world!
await client.end()
Connecting With Prisma – Node.js (docs)
Prisma is an open-source object-relational mapper (ORM) for Node.js and works with both JavaScript and TypeScript. It consists of 3 components:
- Prisma Client - a type-safe query builder
- Prisma Migrate - a data modeling and migration tool
- Prisma Studio - a modern intuitive GUI for interacting with your database
Set up Prisma in your project
Install the Prisma CLI and Prisma Client dependencies in your project
npm i --save-dev prisma
npm i @prisma/client
Initialize Prisma in your project:
npx prisma init
This command does the following:
- Creates a folder called
prisma
at the root of your project - Creates a
.env
file at the root of your project if it doesn't exist - Creates a
schema.prisma
file inside theprisma
folder. This is the file that you will use to model your data
Update the DATABASE_URL
in the .env
to your PostgreSQL database
DATABASE_URL="postgres://postgres:secret123@postgresapp.internal:5432/yourdb"
If you are working in a brownfield project, you can introspect your database to generate the models in your schema.prisma
file:
npx prisma db pull
Assuming you have the following model in your schema.prisma
file:
Add a model to your schema.prisma
file:
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
generator client {
provider = "prisma-client-js"
}
model Post {
id Int @id @default(autoincrement())
title String
content String?
}
You can query your database using Prisma as follows:
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()
async function main() {
const posts = await prisma.post.findMany()
const newPost = await prisma.post.create({
data: {
title: 'PostgreSQL on Fly',
content: 'https://fly.io/docs/reference/postgres'
}
})
}
main()
.catch((e) => {
throw e
})
.finally(async () => {
await prisma.$disconnect()
})
Monitoring
Status
You can use flyctl status
to see a list of VMs and their status. The output for each VM includes it's role within the cluster.
$ flyctl status
App
Name = test-postgres
Owner = superfly
Version = 6
Status = running
Hostname = test.fly.dev
Deployment Status
ID = 044e9269-fabb-27a6-9d53-b25cd2f2e4c2
Version = v6
Status = successful
Description = Deployment completed successfully
Instances = 2 desired, 2 placed, 2 healthy, 0 unhealthy
Instances
ID VERSION REGION DESIRED STATUS HEALTH CHECKS RESTARTS CREATED
6b97fa06 6 iad run running (replica) 3 total, 3 passing 0 2021-02-10T23:31:49Z
da8141e7 6 iad run running (leader) 3 total, 3 passing 0 2021-02-10T23:21:21Z
To view the status of an individual VM:
$ flyctl vm status da8141e7
Instance
ID = da8141e7
Version = 6
Region = iad
Desired = run
Status = running (leader)
Health Checks = 3 total, 2 passing, 1 critical
Restarts = 0
Created = 2021-02-10T23:21:21Z
Recent Events
TIMESTAMP TYPE MESSAGE
2021-02-10T23:21:18Z Received Task received by client
2021-02-10T23:21:48Z Task Setup Building Task Directory
2021-02-10T23:21:49Z Started Task started by client
Checks
ID SERVICE STATE OUTPUT
vm app critical [✗] system spent 31.9 of the last 10 seconds waiting for cpu
[✓] 20.53 GB (83.9%) free space on /data/
[✓] load averages: 0.29 0.35 0.50
[✓] memory: 0.9s waiting over the last 60s
[✓] io: 0.0s waiting over the last 60s
pg app passing [✓] replication: currently leader
[✓] connections: 37 used, 3 reserved, 100 max
role app passing leader
Recent Logs
2021-02-19T22:53:35Z [info] [HEALTHCHECK] [vm: failing] [✗] system spent 6.3 of the last 10 seconds waiting for memory↩︎[✗] system spent 27.5 of the last 10 seconds waiting for cpu↩︎[✓] 20.53 GB (83.9%) free space on /data/↩︎[✓] load averages: 0.29 0.35 0.58↩︎[✓] io: 0.1s waiting over the last 60s↩︎
2021-02-19T22:54:35Z [info] [HEALTHCHECK] [vm: passing] [✓] 20.66 GB (84.4%) free space on /data/↩︎[✓] load averages: 0.28 0.31 0.28↩︎[✓] memory: 0.7s waiting over the last 60s↩︎[✓] cpu: 8.6s waiting over the last 60s↩︎[✓] io: 0.1s waiting over the last 60s↩︎
Checks
To view a list of health checks for a Fly Postgres app, run:
flyctl checks list -a pg-app
Health Checks for pg-app
NAME STATUS ALLOCATION REGION TYPE LAST UPDATED OUTPUT
vm passing 6b97fa06 iad SCRIPT 1m12s ago [✓] 20.68 GB (84.5%) free
space on /data/ [✓] load
averages: 0.00 0.00 0.00 [✓]
memory: 0.0s waiting over the
last 60s [✓] cpu: 0.4s waiting
over the last 60s [✓] io: 0.0s
waiting over the last 60s
pg passing 6b97fa06 iad SCRIPT 5m36s ago [✓] leader check:
[fdaa:0:33:a7b:ab8:0:c24:2]:5433
connected [✓] replication lag:
246µs [✓] connections: 7 used, 3
reserved, 100 max
role passing 6b97fa06 iad SCRIPT 2021-02-15T22:49:36Z replica
vm passing da8141e7 iad SCRIPT 14s ago [✓] 20.66 GB (84.4%) free
space on /data/ [✓] load
averages: 0.31 0.37 0.32 [✓]
memory: 1.1s waiting over the
last 60s [✓] cpu: 9.4s waiting
over the last 60s [✓] io: 0.1s
waiting over the last 60s
pg passing da8141e7 iad SCRIPT 2m53s ago [✓] replication: currently
leader [✓] connections: 31
used, 3 reserved, 100 max
role passing da8141e7 iad SCRIPT 2021-02-15T22:49:38Z leader
Logs
Fly Postgres apps run several processes inside each VM, including postgres, stolon keeper, stolon sentinel, stolon proxy, and postgres_export. Each of those processes redirect STDOUT and STDERR to logs which you can view with flyctl logs
.
Metrics
Fly Postgres apps export metrics to prometheus which can be seen in the Metrics UI or queried from grafana.
The available metrics are
pg_stat_activity_count
pg_stat_activity_max_tx_duration
pg_stat_archiver_archived_count
pg_stat_archiver_failed_count
pg_stat_bgwriter_buffers_alloc
pg_stat_bgwriter_buffers_backend_fsync
pg_stat_bgwriter_buffers_backend
pg_stat_bgwriter_buffers_checkpoint
pg_stat_bgwriter_buffers_clean
pg_stat_bgwriter_checkpoint_sync_time
pg_stat_bgwriter_checkpoint_write_time
pg_stat_bgwriter_checkpoints_req
pg_stat_bgwriter_checkpoints_timed
pg_stat_bgwriter_maxwritten_clean
pg_stat_bgwriter_stats_reset
pg_stat_database_blk_read_time
pg_stat_database_blk_write_time
pg_stat_database_blks_hit
pg_stat_database_blks_read
pg_stat_database_conflicts_confl_bufferpin
pg_stat_database_conflicts_confl_deadlock
pg_stat_database_conflicts_confl_lock
pg_stat_database_conflicts_confl_snapshot
pg_stat_database_conflicts_confl_tablespace
pg_stat_database_conflicts
pg_stat_database_deadlocks
pg_stat_database_numbackends
pg_stat_database_stats_reset
pg_stat_database_tup_deleted
pg_stat_database_tup_fetched
pg_stat_database_tup_inserted
pg_stat_database_tup_returned
pg_stat_database_tup_updated
pg_stat_database_xact_commit
pg_stat_database_xact_rollback
pg_stat_replication_pg_current_wal_lsn_bytes
pg_stat_replication_pg_wal_lsn_diff
pg_stat_replication_reply_time
pg_replication_lag
pg_database_size_bytes
Hardware
Scaling the Postgres Cluster
Scaling Vertically - Adding More VM Resources
You can change VM resources with the flyctl scale vm
command:
$ flyctl scale vm dedicated-cpu-2x
Scaled VM Type to dedicated-cpu-1x
CPU Cores: 1
Memory: 2 GB
See Scaling VM Resources for more.
Scaling Horizontally - Adding More Replicas
flyctl volumes create pg_data --region syd --size 10
flyctl scale count 3
Upgrading
You can update a Postgres cluster, installed with flyctl postgres create
, to the latest release using flyctl image update
.
Check your current image with flyctl image show
:
flyctl image show -a <postgres-app-name>
And upgrade with:
flyctl image update -a <postgres-app-name>