Platform

Backup and Restore using the CLI

Learn how to backup and restore projects using the Supabase CLI

Backup database using the CLI

1

Install the Supabase CLI

Install the Supabase CLI.

2

Install Docker Desktop

Install Docker Desktop for your platform.

3

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.

Session pooler connection string:

1
postgresql://postgres.[PROJECT-REF]:[YOUR-PASSWORD]@aws-0-us-east-1.pooler.supabase.com:5432/postgres

Direct connection string:

1
postgresql://postgres.[PROJECT-REF]:[YOUR-PASSWORD]@db.[PROJECT-REF].supabase.com:5432/postgres
4

Get the database password

Reset the password in the Database Settings.

Replace [YOUR-PASSWORD] in the connection string with the database password.

5

Backup database

Run these commands after replacing [CONNECTION_STRING] with your connection string from the previous steps:

1
supabase db dump --db-url [CONNECTION_STRING] -f roles.sql --role-only
1
supabase db dump --db-url [CONNECTION_STRING] -f schema.sql
1
supabase db dump --db-url [CONNECTION_STRING] -f data.sql --use-copy --data-only

Before you begin

Restore backup using CLI

1

Create project

Create a new project

2

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
3

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.

Session pooler connection string:

1
postgresql://postgres.[PROJECT-REF]:[YOUR-PASSWORD]@aws-0-us-east-1.pooler.supabase.com:5432/postgres

Direct connection string:

1
postgresql://postgres.[PROJECT-REF]:[YOUR-PASSWORD]@db.[PROJECT-REF].supabase.com:5432/postgres
4

Get the database password

Reset the password in the Database Settings.

Replace [YOUR-PASSWORD] in the connection string with the database password.

5

Restore your Project with the CLI

Run these commands after replacing [CONNECTION_STRING] with your connection string from the previous steps:

1
2
3
4
5
6
7
8
psql \  --single-transaction \  --variable ON_ERROR_STOP=1 \  --file roles.sql \  --file schema.sql \  --command 'SET session_replication_role = replica' \  --file data.sql \  --dbname [CONNECTION_STRING]

Important project restoration notes

Troubleshooting notes

  • Setting the session_replication_role to replica disables all triggers so that columns are not double encrypted.
  • If you have created any custom roles with login attribute, you have to manually set their passwords in the new project.
  • If you run into any permission errors related to supabase_admin during restore, edit the schema.sql file and comment out any lines containing ALTER ... 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.

1
2
3
4
5
6
7
8
supabase db dump --db-url "$OLD_DB_URL" -f history_schema.sql --schema supabase_migrationssupabase db dump --db-url "$OLD_DB_URL" -f history_data.sql --use-copy --data-only --schema supabase_migrationspsql \  --single-transaction \  --variable ON_ERROR_STOP=1 \  --file history_schema.sql \  --file history_data.sql \  --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.

1
2
supabase link --project-ref "$OLD_PROJECT_REF"supabase 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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
// npm install @supabase/supabase-js@1const {  } = ('@supabase/supabase-js')const  = 'https://xxx.supabase.co'const  = 'old-project-service-key-xxx'const  = 'https://yyy.supabase.co'const  = 'new-project-service-key-yyy';(async () => {  const  = (, , {    : {      : 'storage',    },  })  const  = (, )  const  = (, )  // make sure you update max_rows in postgrest settings if you have a lot of objects  // or paginate here  const { : ,  } = await .('objects').()  if () {    .('error getting objects from old bucket')    throw   }  for (const  of ) {    .(`moving ${.}`)    try {      const { , :  } = await .        .(.)        .(.)      if () {        throw       }      const { , :  } = await .        .(.)        .(., , {          : true,          : ..,          : ..,        })      if () {        throw       }    } catch () {      .('error moving ', )      .()    }  }})()