Backup and Restore using the CLI
Learn how to backup and restore projects using the Supabase CLI
Backup database using the CLI
Get the new database connection string
Go to the project page and click the "Connect" button at the top of the page for the connection string.
Use the Session pooler connection string by default. If your ISP supports IPv6, use the direct connection string.
Session pooler connection string:
_10 postgresql://postgres.[PROJECT-REF]:[YOUR-PASSWORD]@aws-0-us-east-1.pooler.supabase.com:5432/postgres
Direct connection string:
_10 postgresql://postgres.[PROJECT-REF]:[YOUR-PASSWORD]@db.[PROJECT-REF].supabase.com:5432/postgres
Get the database password
Reset the password in the Database Settings.
Replace [YOUR-PASSWORD] in the connection string with the database password.
Backup database
Run these commands after replacing [CONNECTION_STRING] with your connection string from the previous steps:
_10supabase db dump --db-url [CONNECTION_STRING] -f roles.sql --role-only
_10supabase db dump --db-url [CONNECTION_STRING] -f schema.sql
_10supabase db dump --db-url [CONNECTION_STRING] -f data.sql --use-copy --data-only
Before you begin
Restore backup using CLI
Configure newly created project
In the new project:
- If Webhooks were used in the old database, enable Database Webhooks.
- If any non-default extensions were used in the old database, enable the Extensions.
- If Replication for Realtime was used in the old database, enable Publication on the tables necessary
Get the new database connection string
Go to the project page and click the "Connect" button at the top of the page for the connection string.
Use the Session pooler connection string by default. If your ISP supports IPv6, use the direct connection string.
Session pooler connection string:
_10 postgresql://postgres.[PROJECT-REF]:[YOUR-PASSWORD]@aws-0-us-east-1.pooler.supabase.com:5432/postgres
Direct connection string:
_10 postgresql://postgres.[PROJECT-REF]:[YOUR-PASSWORD]@db.[PROJECT-REF].supabase.com:5432/postgres
Get the database password
Reset the password in the Database Settings.
Replace [YOUR-PASSWORD] in the connection string with the database password.
Restore your Project with the CLI
Run these commands after replacing [CONNECTION_STRING] with your connection string from the previous steps:
_10psql \_10 --single-transaction \_10 --variable ON_ERROR_STOP=1 \_10 --file roles.sql \_10 --file schema.sql \_10 --command 'SET session_replication_role = replica' \_10 --file data.sql \_10 --dbname [CONNECTION_STRING]
Important project restoration notes
Troubleshooting notes
- Setting the
session_replication_roletoreplicadisables all triggers so that columns are not double encrypted. - If you have created any custom roles with
loginattribute, you have to manually set their passwords in the new project. - If you run into any permission errors related to
supabase_adminduring restore, edit theschema.sqlfile and comment out any lines containingALTER ... OWNER TO "supabase_admin".
Preserving migration history
If you were using Supabase CLI for managing migrations on your old database and would like to preserve the migration history in your newly restored project, you need to insert the migration records separately using the following commands.
_10supabase db dump --db-url "$OLD_DB_URL" -f history_schema.sql --schema supabase_migrations_10supabase db dump --db-url "$OLD_DB_URL" -f history_data.sql --use-copy --data-only --schema supabase_migrations_10psql \_10 --single-transaction \_10 --variable ON_ERROR_STOP=1 \_10 --file history_schema.sql \_10 --file history_data.sql \_10 --dbname "$NEW_DB_URL"
Schema changes to auth and storage
If you have modified the auth and storage schemas in your old project, such as adding triggers or Row Level Security(RLS) policies, you have to restore them separately. The Supabase CLI can help you diff the changes to these schemas using the following commands.
_10supabase link --project-ref "$OLD_PROJECT_REF"_10supabase db diff --linked --schema auth,storage > changes.sql
Migrate storage objects
The new project has the old project's Storage buckets, but the Storage objects need to be migrated manually. Use this script to move storage objects from one project to another.
_52// npm install @supabase/supabase-js@1_52const { createClient } = require('@supabase/supabase-js')_52_52const OLD_PROJECT_URL = 'https://xxx.supabase.co'_52const OLD_PROJECT_SERVICE_KEY = 'old-project-service-key-xxx'_52_52const NEW_PROJECT_URL = 'https://yyy.supabase.co'_52const NEW_PROJECT_SERVICE_KEY = 'new-project-service-key-yyy'_52_52;(async () => {_52 const oldSupabaseRestClient = createClient(OLD_PROJECT_URL, OLD_PROJECT_SERVICE_KEY, {_52 db: {_52 schema: 'storage',_52 },_52 })_52 const oldSupabaseClient = createClient(OLD_PROJECT_URL, OLD_PROJECT_SERVICE_KEY)_52 const newSupabaseClient = createClient(NEW_PROJECT_URL, NEW_PROJECT_SERVICE_KEY)_52_52 // make sure you update max_rows in postgrest settings if you have a lot of objects_52 // or paginate here_52 const { data: oldObjects, error } = await oldSupabaseRestClient.from('objects').select()_52 if (error) {_52 console.log('error getting objects from old bucket')_52 throw error_52 }_52_52 for (const objectData of oldObjects) {_52 console.log(`moving ${objectData.id}`)_52 try {_52 const { data, error: downloadObjectError } = await oldSupabaseClient.storage_52 .from(objectData.bucket_id)_52 .download(objectData.name)_52 if (downloadObjectError) {_52 throw downloadObjectError_52 }_52_52 const { _, error: uploadObjectError } = await newSupabaseClient.storage_52 .from(objectData.bucket_id)_52 .upload(objectData.name, data, {_52 upsert: true,_52 contentType: objectData.metadata.mimetype,_52 cacheControl: objectData.metadata.cacheControl,_52 })_52 if (uploadObjectError) {_52 throw uploadObjectError_52 }_52 } catch (err) {_52 console.log('error moving ', objectData)_52 console.log(err)_52 }_52 }_52})()