Add Postgres
To create a Postgres cluster, use the fly postgres create command. The command will walk you through the creation with prompts for name, region, and VM resources.
Because we keep adding shorter aliases, you can use any of the following and get the same result: flyctl postgres, fly postgres, flyctl pg, and fly pg.
fly postgres create
? Choose an app name (leave blank to generate one): pg-test
? Select Organization: TestOrg (personal)
Some regions require a paid plan (fra, maa).
See https://fly.io/plans to set up a plan.
? Select region: Toronto, Canada (yyz)
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
Production (High Availability) - 3 nodes, 2x shared CPUs, 4GB RAM, 40GB disk
Production (High Availability) - 3 nodes, 4x shared CPUs, 8GB RAM, 80GB disk
Specify custom configuration
The “Production” options give you a three-node cluster in a leader-replica configuration. A single-node “Development” instance can readily be scaled and expanded to more regions.
If you select the “Development” single-node cluster configuration, then you can choose to scale down to zero if there are no open connections after one hour.
? Scale single node pg to zero after one hour? (y/N)
You might need to configure any apps that connect to your Postgres app to scale to zero as well, otherwise your Postgres database will never have zero connections and will never scale down. Your app might also need to be able to wait for the database to start back up. Learn more about the scale to zero feature, including how to turn it off.
Creating postgres cluster in organization TestOrg
Creating app...
Setting secrets on pg-test...
Provisioning 1 of 1 machines with image flyio/postgres-flex:15.2
Waiting for machine to start...
Machine 3287457df90185 is created
==> Monitoring health checks
Waiting for 3287457df90185 to become healthy (started, 3/3)
Postgres cluster pg-test created
Username: postgres
Password: 45V1YkwVDUzbkHj
Hostname: pg-test.internal
Flycast: fdaa:2:45b:0:1::7
Proxy port: 5432
Postgres port: 5433
Connection string: postgres://postgres:45V1YkwVDUzbkHj@pg-test.flycast:5432
Save your credentials in a secure place -- you won't be able to see them again!
<div class="warning icon">
Warning: Fly Postgres is not managed postgres. Read what that means [here](/docs/postgres/getting-started/what-you-should-know/).
</div>
## Connecting to the Database
To connect to our database, we first have to communicate with our app what the connection string is.
Copy the value after "Connection string" in the output and then set a new secret:
```cmd
fly secrets set DATABASE_URL=postgres://postgres:<password>@<db-name>.flycast:5432
By default your postgres app is only available within your organization. Only apps within the fly organization are able to connect to it via the connection string above.
Now we can access the DATABASE_URL from the environment:
import os
DATABASE_URL = os.getenv("DATABASE_URL")
At this point you can use a database driver to interact with the database. You have some options here:
Let’s create a function to get some metadata about the database using asyncpg:
poetry add asyncpg
import asyncpg
async def get_db_meta() -> list[str]:
conn = await asyncpg.connect(DATABASE_URL)
records = await conn.fetch("select nspname from pg_namespace;")
return [x["nspname"] for x in records]
Then we can add this to our app:
@app.get("/")
async def read_root():
names = await get_db_meta()
return {"names": names}
Finally you can deploy the app to see it in action:
fly deploy
You can check out this gist for the complete example app.
Developing with Postgres Locally
Often you will want to setup a local postgres to run against. One way to do this is to use docker and direnv. With the following command you can spin up a postgres database for your project:
docker run --name <your-db-name> \
-e POSTGRES_DB=<your-db-name> \
-e POSTGRES_USER=postgres \
-e POSTGRES_PASSWORD=postgres \
-p 5432:5432 \
-d postgres
Then in your project you can specify an .envrc file:
export DATABASE_URL="postgres://postgres:postgres@localhost:5432/<your-db-name>"
If you run direnv allow the DATABASE_URL will be available to your app locally.