# 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](bind-a-database.md)
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](#move-the-database-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 with `409`
  "The app is being deleted". Reading it answers the same `400` for an app on its own machines.
- Permission for `deploy:DescribeApp` on the app to see the database and its backups. In the
  console, `deploy:DescribeApp` also opens the app's page (without it the page is not found).
- Permission for `deploy:ManageHostDatabase` on 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:RestoreHostDatabase` on 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](#move-the-database-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").

**Console**
1. Open the app's **Overview** tab. A pooled app without a database shows **Postgres database**
   with "No Postgres database on the pool host".
2. Choose **Add a database**. The section then shows the database's **Status** and its name under
   **Database and user**.

**CLI**
With a new app (needs `deploy:CreateApp` and `deploy:ManageHostDatabase` on all apps):

```sh
dcloud deploy apps create --name my-app --hosting pooled --database postgres
```

For an existing app:

```sh
dcloud deploy host-database create app-id
```

**API**
With a new app:

```http
POST /deploy/api/apps
{ "name": "my-app", "hosting": "pooled", "database": "postgres" }
```

For an existing app:

```http
POST /deploy/api/apps/3f1c2b4a-5d6e-4f70-8a91-b2c3d4e5f601/host-database
```

**Terraform**
```hcl
resource "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](bind-a-database.md) without a prefix, the app gets none of these
variables.

## Inspect the database

**Console**
1. 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.
2. Under **Backups**, each finished backup is listed with **Taken**, **Why**, **Size** and
   **Kept until**.

**CLI**
```sh
dcloud deploy host-database get app-id
```

**API**
```http
GET /deploy/api/apps/3f1c2b4a-5d6e-4f70-8a91-b2c3d4e5f601/host-database
```

**Terraform:** not available. 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:

**Console**
1. Open the app's **Overview** tab.
2. Under **Postgres database**, choose **Back up now**. **Status** shows **Backing up** until the
   backup is listed under **Backups**.

**CLI**
```sh
dcloud deploy host-database backup app-id
```

**API**
```http
POST /deploy/api/apps/3f1c2b4a-5d6e-4f70-8a91-b2c3d4e5f601/host-database/backups
```

**Terraform:** not available. 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.

**Console**
1. Open the app's **Overview** tab.
2. Under **Backups**, choose **Restore** on the backup. The **Restore this backup?** dialog opens.
3. Choose **Restore**. **Status** shows **Restoring** until the host has finished.

**CLI**
```sh
dcloud deploy host-database restore app-id --backup-id backup-id
```

**API**
```http
POST /deploy/api/apps/3f1c2b4a-5d6e-4f70-8a91-b2c3d4e5f601/host-database/restore
{ "backupId": "7c9e6679-7425-40de-944b-e07fc1f90ae7" }
```

**Terraform:** not available. 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).

**Console:** not available. The console shows the size and the limit under **Size**, but sets no limit.
**CLI**
```sh
dcloud deploy host-database set-limit app-id --limit-mb 2048
```

**API**
```http
PATCH /deploy/api/apps/3f1c2b4a-5d6e-4f70-8a91-b2c3d4e5f601/host-database
{ "limitMb": 2048 }
```

**Terraform:** not available. 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:ManageHostDatabase` and `deploy:ManageDatabaseBinding` on the app, and
  `rds:ModifyDBInstance` on the managed database. To create one with `instanceClass`, also
  `rds:CreateDBInstance`, and `rds:ModifyDBInstance` on 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`, otherwise `409` "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.

**Console:** not available. Under **Move to managed Postgres**, the console shows the CLI command to run, filled in with the app's id.
**CLI**
Create a managed Postgres for it:

```sh
dcloud deploy host-database move app-id --instance-class standard-2
```

Or move to one you have:

```sh
dcloud deploy host-database move app-id --db-instance-id ewdb-0123456789ab
```

**API**
```http
POST /deploy/api/apps/3f1c2b4a-5d6e-4f70-8a91-b2c3d4e5f601/host-database/move
{ "instanceClass": "standard-2" }
```

**Terraform:** not available. 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_URL` and the `PG*` variables now come from a binding without a prefix in `app_role`
  mode, connecting straight to port `5432` (not through the pooler) with `PGSSLMODE` `require`.
  The binding is listed in the **Database** section of the app's **Environment** tab, shown with
  `deploy:ManageEnv` and `deploy:ManageDatabaseBinding`. See [Bind a managed database](bind-a-database.md).
- The role is `app_<slug>` and the database the app's slug with `-` replaced by `_`. The role's
  password is filed in Secrets as `APP_<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 `state` becomes `dropping`, then `dropped` once 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

- [Bind a managed database](bind-a-database.md)
- [Set environment variables](set-environment-variables.md)
- [Delete an app](delete-an-app.md)
- `deploy.host-database.create` · `dcloud deploy host-database move` · `dcloud_app`
