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 app, just with 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.
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, organization, region, and VM resources.
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. The new cluster is ready to use once the deployment is complete.
$ flyctl postgres create
? App name: md-postgres-500
? Select organization: Michael Dwan (personal)
? Select region: dfw (Dallas 2, Texas (US))
? Select VM size: shared-cpu-1x - 256
? Volume size (GB): **10**
Creating postgres cluster md-postgres-500 in organization personal
Launching...
Postgres cluster md-postgres-500 created
Username: postgres
Password: 5f2496c9161f9aa9d59bc771edab4d1f66ff97fa5236587e
Hostname: md-postgres-500.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
You can detach the terminal anytime without stopping the deployment
2 desired, 2 placed, 2 healthy, 0 unhealthy [health checks: 6 total, 6 passing]
--> v0 deployed successfully
Connect to postgres
Any app within the personal organization can connect to postgres using the above credentials and the hostname "md-postgres-500.internal."
For example: postgres://postgres:5f2496c9161f9aa9d59bc771edab4d1f66ff97fa5236587e@md-postgres-500.internal:5432
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 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
To access Fly's private networking for an organization from outside the Fly platform, you must configure a WireGuard tunnel from whichever system you are using to Fly. See Private Network VPN for more details.
With an active Wireguard tunnel, 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 --postgres-app mypostgres
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.
Note that for the app to be able to attach to the database it must have private_network enabled in fly.toml. This enables it to look up .internal addresses like the database URL. See Using Fly DNS for further details.
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 "sentinal" 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 privilages that was created for you along with the cluster. Since thepostgresrole 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
USERNAME SUPERUSER DATABASES
flypgadmin true postgres,testdb
postgres true postgres,testdb
repluser false postgres,testdb
Creating Users
Dropping Users
Granting / Revoking Access
Databases
One Postgres cluster can host multiple databases
Listing Databases
You can view a list of databases with from flyctl:
flyctl postgres databases list
Creating Databases
Dropping Databases
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.
Conneting 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)
}
Conneting 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()
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
Scenarios to walkthrough
- upgrading
- failovers
- adding read replcas
- global read replicas
Postgres Extensions
FAQ
- can I replicate data out of fly? Yes
- is my data encrypted at rest? yes
TODO
- SSL disable + why
- read-write and read-only mode