Importing a database

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:

Terminal

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:

Terminal

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. A MySQL database needs converting first. See Moving from PlanetScale.

Upload it

Terminal

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. 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.

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).

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 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. 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. 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.