# Move a database here from another provider

Copy a PostgreSQL database from Supabase, Amazon RDS or your own server, and switch your app over without losing writes.

This moves a database with `pg_dump` and `pg_restore`, with a short write stop for the cutover.
For a database too large to stop writes while it copies, use
[logical replication](use-logical-replication.md) into the new database instead.

Replace _ewdb-0123456789ab_ with the new database's id, _orders_admin_ and _orders_ with its master
user and database, and `OLD_URL` with a connection string to the old database.

## Prerequisites

- A new database on the same or a newer PostgreSQL major version; see
  [Create a database](create-a-database.md). Enable the extensions the old database uses
  (`SELECT extname FROM pg_extension;` there); see [Enable an extension](enable-an-extension.md).
- `pg_dump` and `pg_restore` of the new database's major version or newer.
- A list of **every** process that writes to the old database: web processes, workers, job
  schedulers, cron jobs, other apps sharing a table. A writer you miss keeps writing to the old
  database after the switch, and those writes are lost.

## Prepare the app before the cutover

1. **Two connection strings.** Request handling uses the pooled port `6432`; workers, job queues
   and migrations use the direct port `5432`. See [Choose the port](connect.md#choose-the-port).
2. **Timeouts on the role.** If the app sets `statement_timeout` or `lock_timeout` in its client,
   set the same value on the role, so it also applies on the pooled port:
   `ALTER ROLE orders_admin SET statement_timeout = '30s';`
3. **The CA, for Node.js.** Add the CA bundle to the image and set `NODE_EXTRA_CA_CERTS`
   ([From Node.js](connect.md#from-node-js)). Without it, every query of a Node app fails with
   `SELF_SIGNED_CERT_IN_CHAIN`. Ship this change before the cutover: it does no harm while the app
   still talks to the old database.

```sh
dcloud rds ca-bundle download > dcloud-rds-ca.crt
dcloud rds instances get ewdb-0123456789ab
```

## Cut over

1. Stop every writer on your list, or put the app into maintenance mode.
2. Copy the data, to the **direct** port:

   ```sh
   pg_dump --format=custom --no-owner --no-privileges "$OLD_URL" > orders.dump
   pg_restore --no-owner --no-privileges --dbname \
     "postgresql://orders_admin:<password>@<id>.db.apps.<root-domain>:5432/orders?sslmode=require" \
     orders.dump
   ```

3. Change the connection strings of every process on your list at once, and deploy.
4. Start the writers again.

## Check that writes land in the new database

Right after the switch, on the **new** database: rows that the app writes all the time (sessions,
jobs, events) have fresh timestamps, for example `SELECT max(created_at) FROM <a busy table>;`.

On the **old** database, nothing of yours should still be connected:

```sql
SELECT application_name, client_addr, count(*)
FROM pg_stat_activity
WHERE datname = current_database() AND pid <> pg_backend_pid()
GROUP BY 1, 2;
```

A row here is a writer you missed: stop it, then copy whatever it wrote since the cutover. Keep the
old database, read-only, until you are sure.

## Result

The app reads and writes the new database, and the old one gets no connections.

## Related

- [Connect to a database](connect.md)
- [Troubleshooting](troubleshooting.md)
- `rds.ca-bundle.download` · `dcloud rds ca-bundle download`
