This is a supabase unoffical documenation
If something breaks, i wont be held liable for the damages, you will need to document your steps
When trying to restore a Data-Only SQL dump into a database managed by Prisma, you will typically run into a massive wall of errors:
duplicate key value violates unique constraint "_prisma_migrations_pkey"
_prisma_migrations table. Your backup also contains those rows. When the backup tries to insert them, they collide.violates foreign key constraint
Invoice before the User it belongs to, PostgreSQL rejects it.To successfully restore the backup, we must follow a strict 3-step process: Build the structure, empty the tables, and inject the data without constraints.
Because this is a data-only dump, the tables must exist before you import the data. Run your Prisma migration command to build the database schema.
(Run this inside your app container or wherever you normally run Prisma commands):
npx prisma migrate deploy
Note: We use
deployinstead ofdevhere because we just want to apply the existing schema without Prisma prompting to reset the database.
Prisma just created the tables, but it also populated the _prisma_migrations table. We need to empty all tables so the backup can insert the historical data cleanly.
Run this command to TRUNCATE (empty) the database while keeping the table structure intact:
docker compose exec -T db psql -U postgres -c "
DO \$\$
DECLARE
r RECORD;
BEGIN
FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public')
LOOP
EXECUTE 'TRUNCATE TABLE public.' || quote_ident(r.tablename) || ' CASCADE';
END LOOP;
END \$\$;"
Now we restore the backup. We prepend SET session_replication_role = 'replica'; to the SQL file. This tells PostgreSQL to temporarily disable Foreign Key checks for this session, allowing tables to be restored in any order.
Run your restore command:
(echo "SET session_replication_role = 'replica';"; cat ./my-backup-data-only.sql) | docker compose exec -T db psql -U postgres
Once the command finishes, the session ends, and Foreign Key protections are automatically turned back on for your application. Prisma will see the restored _prisma_migrations table and continue to function perfectly.
To avoid needing the echo trick in Step 3 in the future, you can tell pg_dump to automatically disable triggers in the backup file itself.
When taking a data-only backup, use the --disable-triggers flag:
# Example backup command
docker compose exec -T db pg_dump -U postgres -a --disable-triggers -f /path/to/backup.sql
(You will still need to do Steps 1 and 2 to clear Prisma's migration history before restoring).