# Importing a database

> Bring an existing PostgreSQL database into a Fortly project by uploading a SQL dump, which applies in one transaction or not at all.

Source: https://docs.fortly.com/data/import

You bring a database into Fortly as a SQL script. Upload a dump made with `pg_dump`, an export from another Fortly project, or any migration script, and Fortly runs it against your project's database in a single transaction. Either the whole script applies, or nothing does and the reply names the statement that failed.

## Make a dump with pg_dump

Run `pg_dump` against the database you're leaving. Any server that speaks PostgreSQL works: a local install, a container, or a managed provider. Use its connection string:

```bash
pg_dump "$SOURCE_DATABASE_URL" \
  --no-owner \
  --no-privileges \
  --inserts \
  --file=dump.sql
```

| Flag | Why you need it |
| --- | --- |
| `--no-owner` | Leaves out `ALTER … OWNER TO` statements. They name roles from your old server that don't exist here, and the import stops at the first one. Without them, everything you import belongs to your project's own role. |
| `--no-privileges` | Leaves out `GRANT` and `REVOKE` statements, which also name roles from the old server. |
| `--inserts` | Writes rows as `INSERT` statements. By default `pg_dump` writes them as `COPY … FROM stdin` blocks, which the importer can't read. Use `--column-inserts` instead if the column order differs between source and destination. |
| plain format (the default) | Keep the default `--format=plain`. The custom (`-Fc`), directory (`-Fd`) and tar (`-Ft`) formats are binary files, and the import accepts only SQL text. |

Use a `pg_dump` at least as new as the server you dump from, which is `pg_dump`'s own requirement.

> **Big tables:**
>
> Add `--rows-per-insert=500` (PostgreSQL 12 and later) to write 500 rows per `INSERT`. The file is smaller and loads faster than one statement per row.

To take only some tables, add a `--table` flag for each one, such as `--table=customers --table=orders`.

### From a database in Docker

If the source database runs in a local container, run `pg_dump` inside it:

```bash
docker exec my-postgres pg_dump -U postgres --no-owner --no-privileges --inserts mydb > dump.sql
```

### From another platform

The command above works for any PostgreSQL host. Platform-specific steps, such as which schemas to include, are in [Moving from Supabase](https://docs.fortly.com/migrate/supabase). A MySQL database needs converting first. See [Moving from PlanetScale](https://docs.fortly.com/migrate/planetscale).

## Upload it

**API**

```bash
curl -X POST https://api.fortly.com/projects/my-app-ada/import \
  -H "Authorization: Bearer $FORTLY_TOKEN" \
  --data-binary @dump.sql
```

`my-app-ada` is the project's full name: the name you chose plus your account's slug. See [Project names in paths](https://docs.fortly.com/api#project-names-in-paths). Send the file as the raw request body with `--data-binary`. Plain `-d` strips newlines and would merge the dump's comments into its statements.

**Console**

Open the project's **Data** tab, choose **Backups**, then **Restore** › **Restore from a file**, and pick your `.sql` file.

A successful import replies:

```json
{ "imported": "statements", "ran": 214, "skipped": 9, "tables": 6 }
```

`ran` counts the statements that ran, `skipped` counts the lines left out (see below), and `tables` is the number of tables in the database afterwards. The data APIs and the console see the new tables at once.

Only the account owner or an admin can import. Anyone else gets `403` with `only the account owner or an admin can import a database`.

## What runs and what's skipped

The importer splits the script into statements the way PostgreSQL does. Semicolons inside quoted strings, `E'…'` escape strings, quoted identifiers, `$$`-quoted function bodies and nested `/* */` comments don't end a statement. Then it runs each statement in order, inside one transaction.

**Skipped**, because they're about the dump's own session and not your data:

- `SET` lines, such as `SET client_encoding = 'UTF8'` or `SET statement_timeout = 0`
- `SELECT pg_catalog.set_config(…)`
- the dump's own `BEGIN`, `COMMIT`, `END` and `ROLLBACK`, because the import runs inside a transaction of its own
- `psql` backslash commands, such as `\connect`

**Run**, exactly as written: everything else. That includes `CREATE EXTENSION`, `CREATE TYPE`, `COMMENT ON`, `ALTER TABLE`, `GRANT`, `CREATE FUNCTION`, triggers, `SELECT pg_catalog.setval(…)` and `SET CONSTRAINTS ALL DEFERRED`. Nothing is rewritten. A hand-written `integer primary key` stays a column you fill in yourself (see [Primary keys](https://docs.fortly.com/data#primary-keys-fill-themselves-in)).

> **Warning:**
>
> The import runs against your database as it is. Nothing is emptied first. If the script creates a table that already exists, it fails on that statement and nothing is applied. To replace what's there, [export](https://docs.fortly.com/data/backups) the current state first, then drop the old tables with `POST /projects/{name}/sql` before you import.

## When a statement fails

The import stops at the first statement PostgreSQL refuses, rolls back everything before it, and replies `400` naming that statement (its first 160 characters) and PostgreSQL's reason:

```json
{
  "error": "stopped after 41 statements. This one was not accepted: ALTER TABLE public.orders OWNER TO app_user (error returned from database: role \"app_user\" does not exist)"
}
```

Your database is exactly as it was before you uploaded. Fix the dump and upload it again.

| Refused statement | Fix |
| --- | --- |
| `… OWNER TO …`, or `role "…" does not exist` | Dump again with `--no-owner` and `--no-privileges` |
| `COPY … FROM stdin` | Dump again with `--inserts` |
| `CREATE EXTENSION …` | The extension isn't available to your role. See [Extensions](https://docs.fortly.com/data/postgres#extensions). Remove the line, and anything that depends on it, or replace it |
| `relation "…" already exists` | The table is already in your database. Drop it first, or import into a fresh project |
| `canceling statement due to statement timeout` | One statement ran longer than 30 seconds, the [per-statement limit](https://docs.fortly.com/data#limits-on-each-database). Split very large `INSERT`s, or create big indexes in a separate, smaller import |

## Limits and what isn't supported

- **256 MB per upload.** A larger file is refused with `that file is larger than 256 MB`. Split a larger database into several imports, each its own transaction. Import the schema first (`pg_dump --schema-only`), then the data a few tables at a time (`--data-only --table=…`), parent tables before the tables whose foreign keys point at them.
- **UTF-8 SQL text only.** Anything containing a zero byte is refused as binary. That includes a gzipped file, a `pg_dump` custom-format file and a SQLite `.db` file. A Fortly database is a PostgreSQL server, not a file, so there's no database file to upload in place of one.
- **No pulling from a connection string.** Fortly doesn't connect to your old database for you. Run `pg_dump` yourself and upload the file.
- **One transaction.** A script whose statements can't run inside a transaction, such as `CREATE INDEX CONCURRENTLY` or `CREATE DATABASE`, fails. Remove those lines or run them separately.