# PostgreSQL

> Managed PostgreSQL on Watasu — creation, plans, followers, backups, and the restore workflow.

PostgreSQL is the default for transactional application data on Watasu. Use it whenever you need relational queries, transactions, migrations, or strong consistency.

## Create a database

```bash
watasu addons:create postgresql --app my-app
watasu addons:wait my-database
```

That's the minimum. Watasu provisions the database, attaches it, and exports connection vars to your app:

| Variable       | Purpose                |
| -------------- | ---------------------- |
| `DATABASE_URL` | Full connection string |
| `PGHOST`       | Hostname               |
| `PGPORT`       | Port                   |
| `PGDATABASE`   | Database name          |
| `PGUSER`       | Username               |
| `PGPASSWORD`   | Password               |

## Pick a plan

Plans range from cheap shared instances for development to dedicated high-availability tiers for production:

* `hobby-0`, `hobby-1` — entry tier, single-node, **network-backed storage**, manual backups. Good for prototyping and review apps.
* `standard-0` … `standard-4` — production tier, single-primary on **local NVMe**, scheduled backups by default. Async followers are available from `standard-1`.
* `premium-1` … `premium-4` — same local-NVMe primary plus a **sync standby** with automatic failover when the primary dies. One stable write endpoint.

The Hobby → Standard step is also a storage backend change (network PVC → local NVMe). Standard plans get materially better I/O latency as a result.

```bash
watasu addons:create postgresql:standard-0 --app my-app
```

See [Add-on Plans](https://docs.watasu.io/reference/addon-plans.md) for the full plan table with sizes, retention, and what's included.

## Open a psql session

```bash
watasu pg:psql DATABASE_URL --app my-app
```

Use the env-var name (`DATABASE_URL`, or an alias like `REPORTING_DATABASE_URL`) to pick which database to connect to when an app has more than one.

## Followers (read replicas)

Followers are streaming replicas, useful for read-heavy reporting queries that you don't want competing with primary traffic.

```bash
watasu pg:follow DATABASE_URL --app my-app --name reporting-db --plan standard-1
```

Once the follower is provisioned and caught up, attach it like any other database:

```bash
watasu addons:attach reporting-db --app my-app --as REPORTING
```

Your app now has both `DATABASE_URL` and `REPORTING_DATABASE_URL`.

## Backups

PostgreSQL supports manual and scheduled backups, plus restore from either a managed backup or an uploaded dump file.

### Capture a manual backup

```bash
watasu pg:backups:capture --app my-app
```

### List backups

```bash
watasu pg:backups --app my-app
```

### Restore

Restores create a **new database** (a replacement add-on), not an in-place overwrite. This is intentionally safer.

From a managed backup:

```bash
watasu pg:backups:restore b101 DATABASE_URL --app my-app --name db-restored
```

From a local dump file:

```bash
watasu pg:backups:restore ./production.dump DATABASE_URL --app my-app --name db-restored
```

### Promote the restored database

When the replacement is ready and you've verified it, promote it into the attachment role:

```bash
watasu pg:promote db-restored --app my-app
```

The app now points at the restored database under `DATABASE_URL`. The original is still around until you destroy it.

The full backup/restore loop is documented as a workflow in [Backups and Restores](https://docs.watasu.io/workflows/backups-and-restores.md).

## Tips

* Take a manual backup before risky schema migrations.
* Use a follower as soon as analytics queries start affecting primary latency.
* Keep your application's connection pool sized to fit the plan, not maxed out — many small pools beat one giant one.
* Run migrations through your deploy or release process, not from a workstation.

[View this page on the web](https://docs.watasu.io/addons/postgresql/)