Copy another Postgres database into a project: from another hosted Postgres platform or your own server. Live copy with a switch-over in seconds, or a one-time copy; auth users keep their passwords.
Move an app to StitchDB without exporting and importing by hand. Paste the connection string of the database you use today, and StitchDB copies it into a new project: your tables and data, views, functions, triggers, types, policies and sequences, auth users with their password hashes, buckets and stored files, cron jobs and the tables your app listens to with Realtime. It works with another hosted Postgres platform built on the same open-source backend, and with any Postgres 12 or newer for the database part.
#Before you start
- A new, empty project. The import refuses a project that already has tables, users or files. In the dashboard choose Import a project next to New project, or create a project first.
- The connection string of the source, with the password of the database owner (the login that owns your tables). Use the direct connection (usually port 5432), not a connection pooler in transaction mode.
- For a live copy, the source needs logical replication:
show wal_level;returnslogical, and the login has theREPLICATIONattribute (select rolreplication from pg_roles where rolname = current_user;). Managed platforms built on the same backend usually have both. If not, the import does a one-time copy. - For stored files (optional), the source's API URL and its service role key: files are read through the source's storage API.
- For Auth settings (optional), a personal access token of the platform the source runs on. It reads the site URL, redirect URLs, sign-up and session rules, and lists your Edge Functions and secret names so you know what to set again.
#Import in the dashboard
- Open the new project's Studio, Database › Import from Postgres.
- Paste the connection string, pick How to copy (Automatic is fine), and add the optional file and settings details. Choose Check source.
- The check connects to the source and shows what is there: Postgres version, size, schemas, tables and rows, auth users, files, cron jobs, and anything that needs your attention (extensions that are not available, tables without a primary key, roles that are not created).
- Optionally paste your Edge Function secrets as
NAME=valuelines, then choose Start live copy or Start copy.
#Live copy and switch-over
The schema is copied first, then every table with a primary key is copied and followed: inserts, updates and deletes on the source arrive within a second or two. Studio shows how many tables are copied and how far behind the source the copy is. When it says Caught up:
- Point your app at the new project: its API URL and keys (Settings › API) and its connection string.
- Stop writes to the old database, for example with a minute of maintenance mode.
- Choose Switch over. The last changes arrive, sequences are set to the source's values, tables without a primary key are copied, then auth users, buckets and files, cron jobs and realtime tables. Last, every table is checked.
While a live copy runs, the source keeps a replication slot for it, which holds back the source's change log. Switch over or undo within a few days so the source's disk does not fill up.
#One-time copy
Everything is copied once, tables in parallel. Stop writes to the source before you start: changes made during the copy are not included. Use it when the source has no logical replication, or for a test run.
#Import from Supabase
A Supabase project needs only two things from you: a personal access token and the database password. StitchDB lists the projects of the token, and once you pick one it reads the rest through the Supabase Management API: how to connect, the key for the stored files, your Auth settings and sign-in providers, your buckets, your Edge Functions with their code, and the names of your secrets.
- Create a project in StitchDB for the import (it must be empty), and open its Studio at Database › Import from Postgres. Import from Supabase is the first tab.
- Create a personal access token at supabase.com/dashboard/account/tokens, paste it and choose Find projects.
- Pick the project. Studio shows its region, Postgres version, Edge Functions, secrets, buckets and custom domain.
- Enter the project's database password. The Management API cannot read it. If you no longer have it, reset it in the Supabase dashboard under Database › Settings (Studio links to that page), then paste the new one. Resetting it signs out anything that connects with the old one, such as other tools and scripts.
- Choose How to copy and Check project. The check reads the settings and connects to the database. Then you can enter the secrets that cannot be read (see below) and start the copy, as with any other source.
#How StitchDB connects
StitchDB tries the direct connection (db.<ref>.supabase.co, port 5432) first. Only this connection can carry a live copy, because the connection pooler does not pass logical replication. The direct connection uses IPv6 unless the project has the IPv4 add-on. If StitchDB cannot reach it, it falls back to the session pooler (IPv4, port 5432 on the pooler host) and does a one-time copy; the check tells you which one it used. If the project has network restrictions, allow the address the check shows, or turn the restrictions off for the length of the import.
#What is read for you
| What | How it comes over |
|---|---|
| Database | Everything in the table above: your schemas, auth users with their passwords, buckets and files, cron jobs, realtime tables. |
| Stored files | Copied with the project's service role key (or a secret API key), read through the API. You do not paste a key. |
| Storage policies | The policies on storage.objects and storage.buckets are created again with the same roles, command and expressions. A policy for a role that does not exist here is skipped and listed. |
| Triggers on auth users | Your triggers on auth.users and storage.objects, such as the one that creates a profile row for each new user, are created after the users are copied. They do not fire for the copied users, so no profile rows are created twice, and they fire for every new sign-up after that. |
| Grants and RLS | Row level security, table and column grants to anon, authenticated and service_role stay as they were in the source. Default privileges for new tables are set as this platform expects them. |
| Auth settings | Site URL, redirect URLs, sign-up and e-mail confirmation, password rules, JWT expiry, sessions, MFA, sender name, and e-mail subjects and templates you changed. They are applied when the copy is done (you can turn this off before the copy). |
| Sign-in providers | Each enabled provider with its client ID. The client secret cannot be read, so you enter it in the wizard (or later under Authentication › Sign-in methods). |
| SMTP | Your SMTP server, port, user and sender. You enter the password; until then the platform's e-mail sends. |
| Edge Functions | The code of each function (all its files, including shared ones next to it, and its import map), downloaded and deployed here with its JWT setting. A function whose code cannot be read is listed; deploy it with stitchdb functions deploy. |
| Secrets | Only the names: their values cannot be read. The wizard lists them as NAME= lines for you to fill in. The ones it does not get are on the checklist. |
#What is left for you
When the import is done, Studio shows a checklist of what came over and what is left. Depending on the project, that is:
- Secrets you did not fill in: set them under Edge Functions › Secrets.
- Sign-in providers: the client secret of each provider you left empty, and in each provider's console the callback URL of this project (
https://<project-ref>.p.stitchdb.com/auth/v1/callback) next to the old one. - SMTP password, if you left it empty.
- Settings that need you: Auth hooks, phone sign-in with its SMS provider, SSO, CAPTCHA keys and providers not available here are listed one by one.
- Custom domain: point its DNS at this project when you switch over (Settings › Custom domains).
- Your app: use this project's API URL and anon key. The client calls stay the same:
import { createClient } from 'stitchdb-js'instead of the old client, with the same methods (queries, auth, storage, realtime, functions).
#What is copied
| What | How |
|---|---|
| Your schemas | Every schema of yours (public and your own), with tables, data, views, materialized views, functions, triggers, types, policies and sequences. Objects are owned by the project's own role, so they work like anything you create yourself. |
| Extensions | The extensions your schemas use are created first, when they are available here. Objects that need one that is not available are skipped and listed. |
| Auth users | Users with their password hashes, sign-in identities and MFA factors. Users keep their passwords and their user ids, so foreign keys to auth.users and policies with auth.uid() keep working. Open sessions are not copied: signed-in users sign in again once. |
| Buckets and files | Buckets and their settings are copied with the database; files are copied through the storage API (with the source's API URL and service role key), and keep their owners. |
| Cron jobs | Scheduled with the same name, schedule and command, at the switch-over (so they do not run twice while the live copy runs). |
| Realtime | Tables your app listens to with Realtime are added to this project's realtime publication. |
| Auth settings | With the access token: site URL, redirect URLs, sign-up, e-mail confirmation, password, JWT expiry, session and MFA rules. |
| Function secrets | The NAME=value lines you paste are set as Edge Function secrets. |
Not copied: the code of your Edge Functions (deploy it from your repository with stitchdb functions deploy), sign-in provider keys and your SMTP password (set them under Authentication), and roles of your own: grants and policies for them are skipped and listed in the check.
#Checking the copy
At the end every table is counted on both sides (exact counts up to 2 million rows, estimates above), and for tables up to 20,000 rows the rows themselves are compared with a checksum. The result lists each table as Same rows, Same count or Differs, plus the number of auth users and files copied and anything that was skipped.
#Undo
Undo import removes everything the import added: your schemas and their objects, auth users, buckets and files, and cron jobs, so the project is empty again. A running live copy is stopped and its replication slot on the source dropped. The source itself is never changed, apart from the publication and slot a live copy uses, which are removed when it ends.
#From the command line
# link the new project, then
stitchdb import "postgresql://postgres:<password>@<host>:5432/postgres" \
--storage-url https://<old-project-url> --storage-key <service role key> \
--platform-token <access token> --secrets .env
stitchdb import status # progress, live copy lag, result
stitchdb import switch-over # finish a live copy
stitchdb import undo # back to an empty projectThe connection string can also come from STITCHDB_IMPORT_SOURCE, so it stays out of your shell history. Add --mode dump for a one-time copy and --yes to skip the questions. The same steps are in the Management API under /v1/projects/{ref}/import.
#Security
- The copy runs on your project's own server: the database server connects to the source directly, and your data does not pass through anything else.
- The connection string, service key and access token are stored encrypted and only while the import runs; they are deleted when it is done or undone, and never shown again.
- Only the project owner can start, switch over or undo an import. Sources on private or local network addresses are refused.
#Troubleshooting
| Message | What to do |
|---|---|
| password authentication failed | Check the password in the connection string; special characters must be URL-encoded (for example @ as %40). |
| A live copy needs wal_level = logical | Turn on logical replication on the source, or choose One-time copy. |
| cannot read auth.users | Use the connection string of the database owner, not a restricted login. |
| This project is not empty | Import into a new project, or undo the previous import first. |
| The live copy has not caught up yet | Writes are still arriving on the source: stop them and try the switch-over again. |