Skip to content
ReviveDB
Blog

pg_restore: "input file appears to be a text format dump" with a Supabase backup

By ReviveDB

pg_restore: error: input file appears to be a text format dump. Please use psql.
pg_restore: error: could not open input file "db_cluster-...backup/toc.dat": No such file or directory

Both come from pointing pg_restore at the backup you downloaded from the Supabase dashboard. That file is a plain SQL dump. pg_restore only reads the custom, directory and tar formats, so it refuses the file itself, and when it is given a folder it looks for the toc.dat that a directory-format dump would have.

The folder with the same name

The download is a single gzipped file. Some unzip tools extract it into a folder with the same name as the archive, and that folder ends up as the argument to pg_restore. The SQL file inside it is what you want.

Restore it with psql

gunzip db_cluster-XX-XX-XXXX@XX-XX-XX.backup.gz
psql -d "<connection string>" -f db_cluster-XX-XX-XXXX@XX-XX-XX.backup

This is the command in Supabase's dashboard restore guide. Take the connection string from Connect in the target project. From a network without IPv6, use the Session pooler string.

Expected errors

Into a new Supabase project, psql prints errors such as "already exists" for schemas and constraints. The file contains the CREATE statements for everything, including the auth and storage schemas the new project already has, and Supabase documents these errors as expected. That also means you should not add ON_ERROR_STOP=1 or --single-transaction for this file: the first expected error would stop or roll back the whole restore.

Because real errors scroll past in the same stream, count the rows in your main tables afterwards and compare them with the old project.

Storage files are not in it

Your Storage files are not in the dump. The buckets and the storage.objects rows come back, so the dashboard looks complete, but the files themselves need their own copy. See do Supabase backups include Storage and restoring a backup into a new project.