Use a database on the pool host
Give an app on pooled hosting a Postgres database on its own pool host, back it up, restore it, limit its size, and move it to managed Postgres when it outgrows the host.
Each pool host runs one Postgres, and an app on pooled hosting can have a database and a role of
its own in it, named app_ and the first 12 hex digits of the app's id. Only an app on pooled
hosting has one; an app on its own machines binds a managed Postgres
instead. Replace app-id with the app's id and backup-id with a backup's id.
Warning
The database is a single node on the pool host's own disk, without failover. If the host stays silent for more than 30 minutes, the app moves to another host and the database is created again there from its newest backup (empty if it has none yet): what was written after that backup is lost. For a database with a standby, move it to managed Postgres.
Prerequisites
- An app on pooled hosting that is not being deleted. Adding, backing up, restoring, limiting or
moving the database of any other app is refused with
400"Only an app on pooled hosting has a pool-host database; bind a managed Postgres instead", and of an app being deleted with409"The app is being deleted". Reading it answers the same400for an app on its own machines. - Permission for
deploy:DescribeAppon the app to see the database and its backups. In the console,deploy:DescribeAppalso opens the app's page (without it the page is not found). - Permission for
deploy:ManageHostDatabaseon the app to add the database, back it up, set its size limit or move it. The console shows Add a database and Back up now only with it. - Permission for
deploy:RestoreHostDatabaseon the app to restore a backup. The console shows the Restore buttons only with it.
Add a database
You can add the database when you create the app, or later. Adding it to an app that already has
one answers with the same name and changes nothing. After a
move to managed Postgres, adding it again creates a new,
empty database with the same name, once the app is no longer bound to the managed database
(otherwise 409 "DATABASE_URL comes from a managed Postgres binding; remove the binding first to
use a database on the pool host") and the host has dropped the old copy (otherwise 409 "The pool
host is still removing the previous database; add it again in a minute").
- Open the app's Overview tab. A pooled app without a database shows Postgres database with "No Postgres database on the pool host".
- Choose Add a database. The section then shows the database's Status and its name under Database and user.
With a new app (needs deploy:CreateApp and deploy:ManageHostDatabase on all apps):
dcloud deploy apps create --name my-app --hosting pooled --database postgresFor an existing app:
dcloud deploy host-database create app-idWith a new app:
POST /deploy/api/apps
{ "name": "my-app", "hosting": "pooled", "database": "postgres" }For an existing app:
POST /deploy/api/apps/3f1c2b4a-5d6e-4f70-8a91-b2c3d4e5f601/host-databaseresource "dcloud_app" "web" {
name = "my-app"
hosting = "pooled"
database = "postgres"
}Changing database later replaces the app. Add a database to an existing app with the CLI or API.
When the app is created with a database, deploy:ManageHostDatabase is checked before anything is
made, so without it no app is created either. Asking for a database for an app that is not pooled
is refused with 400 "A database on the pool host is for pooled apps; an app on its own machines
binds a managed Postgres".
What the app gets
Once the database is on the app's host, a running server of the app is stopped, and the next request starts it with these variables:
| Variable | Value |
|---|---|
DATABASE_URL |
postgresql://app_…:…@10.253.0.1:5432/app_…?sslmode=disable |
PGHOST |
10.253.0.1, an address on the pool host itself |
PGPORT |
5432 |
PGDATABASE |
the database's name |
PGUSER |
the same name: the role owns the database |
PGPASSWORD |
the role's password |
PGSSLMODE |
disable: the connection never leaves the host |
Only the apps on that pool host reach that address, and each role connects, with its password, to
its own database only. If
the app's environment already has DATABASE_URL, set by hand or by a
managed Postgres binding without a prefix, the app gets none of these
variables.
Inspect the database
- Open the app's Overview tab. Under Postgres database, Status shows Running on a ready host, Backing up or Restoring while a request is open, Being removed after a move, Host and the host's state while the host is not ready, and Not placed yet before the app has a host. Size shows the last reported size and the limit, Last backup when it was last backed up, and Problem the host's last error with it, if any.
- Under Backups, each finished backup is listed with Taken, Why, Size and Kept until.
dcloud deploy host-database get app-idGET /deploy/api/apps/3f1c2b4a-5d6e-4f70-8a91-b2c3d4e5f601/host-databaseNot available in Terraform. The database's state is not a Terraform resource.
The answer carries database (state, hostState, bytes, lastBackupAt, lastRestoreAt,
backupPending, restorePending, lastError, limitMb, overLimit, limitProblem) and
backups, the finished backups newest first, at most 50. database is null for an app without
one, also for an app on its own machines. The password is not in the answer and not in the
console: only the app's environment has it.
Back up the database
The first backup is taken once the database is more than 15 minutes old, then one a day, while its host is ready. How long a backup is kept depends on why it was taken:
Why (reason) |
Kept |
|---|---|
daily |
7 days |
manual |
30 days |
before-restore |
7 days |
before-delete |
30 days |
move |
30 days |
To take one now:
- Open the app's Overview tab.
- Under Postgres database, choose Back up now. Status shows Backing up until the backup is listed under Backups.
dcloud deploy host-database backup app-idPOST /deploy/api/apps/3f1c2b4a-5d6e-4f70-8a91-b2c3d4e5f601/host-database/backupsNot available in Terraform. A backup is an action, not configuration.
The call returns 202 with a requestId; the pool host takes the backup at its next pass, and it
appears in the backup list when it is done. A new request replaces an earlier one that is still
open. A request the host has not answered in 30 minutes is asked again. Without an active database the
request is refused with 404 "The app has no pool-host database".
Restore a backup
A restore replaces the database with one of its backups. The pool host first backs up what is
there now (before-restore, kept 7 days), so a restore can be undone with that backup. Then it
drops the database, closing every connection, creates it again with the same name and password,
and fills it from the backup.
- Open the app's Overview tab.
- Under Backups, choose Restore on the backup. The Restore this backup? dialog opens.
- Choose Restore. Status shows Restoring until the host has finished.
dcloud deploy host-database restore app-id --backup-id backup-idPOST /deploy/api/apps/3f1c2b4a-5d6e-4f70-8a91-b2c3d4e5f601/host-database/restore
{ "backupId": "7c9e6679-7425-40de-944b-e07fc1f90ae7" }Not available in Terraform. A restore is an action, not configuration.
The call returns 202 with a requestId. When the host has finished, lastRestoreAt (Last
restore) is set. If the backup before the restore fails, the database is not replaced, and
lastError (Problem) says restore failed: the backup before the restore failed: …. If the
restore itself fails after the database was dropped, lastError says restore failed: …, the
database holds only what was restored so far, and the restore is not tried again: restore the
before-restore backup, or the same backup again. The restore is refused with:
404"The app has no pool-host database" without an active database;404"No such backup of this app" for a backup that is not a finished one of this app;409"A restore is already running" while another restore is open.
Set the size limit
A database's size limit is 1 GiB (1024 MiB) until you set it, and can be set from 64 to
20480 MiB (20 GiB).
Not available in Console. The console shows the size and the limit under **Size**, but sets no limit.
dcloud deploy host-database set-limit app-id --limit-mb 2048PATCH /deploy/api/apps/3f1c2b4a-5d6e-4f70-8a91-b2c3d4e5f601/host-database
{ "limitMb": 2048 }Not available in Terraform. The limit is not a Terraform argument.
The call returns 200 with the limit now set; the pool host applies it at its next pass. A limit
outside 64 to 20480 is refused with 400, and an app without an active database with 404 "The
app has no pool-host database".
When the pool host finds the database over its limit, it makes the database read only and ends its
open sessions. Writes then fail with Postgres's cannot execute … in a read-only transaction,
overLimit is true, limitProblem says what happened, and the console shows "The database is
read only: it is over its size limit." The database becomes writable again only once it is below
90 % of the limit. To get there:
- raise the limit to more than the size divided by 0.9; or
- delete data: a session may write after
SET default_transaction_read_only = off.
Move the database to managed Postgres
The move backs the database up (move, kept 30 days), creates the app's role and database on a
managed Postgres, binds the app to it, and restores the backup into it as that role. Then the app's
environment switches to the managed database and the pool-host copy is dropped.
Important
The app keeps running on its pool host, and a pool host reaches a managed Postgres only on its
public endpoint. With instanceClass, the move creates a managed Postgres named <app slug>-db,
single node, publicly accessible, in the app's region, and you pay for it from then on. A database
you give with dbInstanceId must be publicly accessible too (created with --publicly-accessible
in dcloud rds instances create): a private one is refused with 400 "… is private, and an app on
a pool host reaches a managed database only on its public endpoint …", and a database of another
engine with 400 "… is not a Postgres database".
Before you move:
- You need
deploy:ManageHostDatabaseanddeploy:ManageDatabaseBindingon the app, andrds:ModifyDBInstanceon the managed database. To create one withinstanceClass, alsords:CreateDBInstance, andrds:ModifyDBInstanceon all databases (the new one has no id yet); the create is made as you, with the managed Postgres's own checks. Both are checked before anything is created. - The app has no managed Postgres binding without a prefix, otherwise
409"The app is already bound to a managed database without a prefix". - The app's environment does not set
DATABASE_URL, otherwise409"Remove DATABASE_URL from the environment first: the binding generates it". - The managed database's master password is filed in Secrets, so the move can create the role.
- Writes the app makes between the backup and the switch are not carried over: move at a quiet time.
Not available in Console. Under **Move to managed Postgres**, the console shows the CLI command to run, filled in with the app's id.
Create a managed Postgres for it:
dcloud deploy host-database move app-id --instance-class standard-2Or move to one you have:
dcloud deploy host-database move app-id --db-instance-id ewdb-0123456789abPOST /deploy/api/apps/3f1c2b4a-5d6e-4f70-8a91-b2c3d4e5f601/host-database/move
{ "instanceClass": "standard-2" }Not available in Terraform. A move is an action, not configuration.
Give exactly one of dbInstanceId and instanceClass; both or neither is a 400.
The call returns 202
with jobId and database, the managed database's identifier; jobId is null when a move of
this app is already queued, which the new request joins. It is refused with 404 "The app has no
pool-host database" without an active database, 404 "Database not found" for a managed database
that is not yours, and, when the create fails, 400 or 409 "Creating the database failed: …"
with the managed Postgres's reason.
The move runs as a job. It waits up to 15 minutes for the pool host's backup. A managed database still being created is waited for: the job tries again, waiting longer each time, up to 20 times. Each try starts over with a new backup.
The binding is saved only after the backup is restored. When the restore fails, nothing is bound: the app stays on its pool-host database, the job tries again (each try restores into a cleaned database), and if every try fails you can move again. The managed database created for the move stays, and you pay for it until you delete it.
What changes for the app
DATABASE_URLand thePG*variables now come from a binding without a prefix inapp_rolemode, connecting straight to port5432(not through the pooler) withPGSSLMODErequire. The binding is listed in the Database section of the app's Environment tab, shown withdeploy:ManageEnvanddeploy:ManageDatabaseBinding. See Bind a managed database.- The role is
app_<slug>and the database the app's slug with-replaced by_. The role's password is filed in Secrets asAPP_<SLUG>_DB_PASSWORD. - The app's running server is stopped, and the next request starts it with the new variables.
- The pool-host database's
statebecomesdropping, thendroppedonce the host has dropped it.
When the app is deleted
Deleting the app backs the database up first (before-delete), kept 30 days after the app is
gone. The delete waits up to 10 minutes for that backup and does not go on without it. If the
database's host is not
ready, no backup is taken, and the newest existing one is kept 30 days instead. Then the database
is dropped.
Not supported
- A standby or failover for the pool-host database. Move it to managed Postgres for that.
- Connecting from outside the pool host, or with TLS.
- Reaching a private managed Postgres from a pooled app, after a move or otherwise.
- Adding a pool-host database again after a move: the request answers with the old name, and the database stays dropped.
- Setting the size limit, or moving the database, in the console.
Related
Related