Migrating and Upgrading Projects
Supabase ships fast and we endeavor to add all new features to existing projects wherever possible. In some cases, access to new features require upgrading or migrating your Supabase project.
Upgrade your project
There are a few methods available to upgrade your project.
pg_upgrade
This upgrade method is currently in Beta.
pg_upgrade performs an in-place upgrade on your database. For projects larger than 1GB, pg_upgrade is generally faster than a pause+restore cycle, and the speed advantage grows with the size of the database.
- Plan for an appropriate downtime window, and ensure you have reviewed the caveats section of this document before executing the upgrade.
- Use the "Upgrade project" button on the Infrastructure section of your dashboard.
Additionally, if a pg_upgrade upgrade should fail, your original DB would be brought back up online and be able to service requests.
As a rough rule of thumb, pg_upgrade operates at ~100mbps (when executing an upgrade on your data). Using the size of your database, you can use this metric to derive an approximate sense of the downtime window necessary for the upgrade. During this window, you should plan for your DB and associated services to be unavailable.
Pause + restore
We recommend using the pg_upgrade method, as it is faster, and more reliable. Additionally, only Free-tier projects are eligible to use the Pause + Restore method.
When you pause and restore a project, the restored database includes the latest features. This method does include downtime, so be aware that your project will be inaccessible for a short period of time.
- On the General Settings page in the Dashboard, click Pause project. You will be redirected to the home screen as your project is pausing. This process can take several minutes.
- After your project is paused, click Restore project. The restoration can take several minutes depending on how much data your database has. You will receive an email once the restoration is complete.
Note that a pause + restore upgrade involves tearing down your project's resources before bringing them back up again. If the restore process should fail, manual intervention from Supabase support will be required to bring your project back online.
Caveats
Regardless of the upgrade method, a few caveats apply:
Logical replication
If you are using logical replication, the replication slots will not be preserved by the upgrade process. You will need to manually recreate them after the upgrade with the method pg_create_logical_replication_slot
. Refer to the Postgres docs on Replication Management Functions for more details about the method.
Breaking changes
Newer versions of services can break functionality or change the performance characteristics you rely on. If your project is eligible for an upgrade, you will be able to find your current service versions from within the Supabase dashboard.
Breaking changes are generally only present in major version upgrades of Postgres and PostgREST. You can find their respective release notes at:
If you are upgrading from a significantly older version, you will need to consider the release notes for any intermediary releases as well.
Time Limits
Starting from 2024-06-24, when a project is paused, users then have a 90-day window to restore the project on the platform from within Supabase Studio.
The 90-day window allows Supabase to introduce platform changes that may not be backwards compatible with older backups. Unlike active projects, static backups can't be updated to accommodate such changes.
During the 90-day restore window a paused project can be restored to the platform with a single button click from Studio's dashboard page.
After the 90-day restore window, you can download your project's backup file from the project dashboard. This backup is compatible with pg_restore. You can extract your data from it, or attempt to manually restore the project.
If you upgrade to a paid plan while your project is paused, any expired one-click restore options are reenabled. Since the backup was taken outside the backwards compatibility window, it may fail to restore. If you have a problem restoring your backup after upgrading, contact Support.
Disk sizing
When upgrading, the Supabase platform will "right-size" your disk based on the current size of the database. For example, if your database is 100GB in size, and you have a 200GB disk, the upgrade will reduce the disk size to 120GB (1.2x the size of your database).
Objects dependent on Postgres extensions
pg_upgrade does not support upgrading of databases containing reg* data types referencing system OIDs. If you have created any objects that depend on the following extensions, you will need to recreate them after the upgrade.
pg_cron records
pg_cron does not automatically clean up historical records. This can lead to extremely large cron.job_run_details
tables if the records are not regularly pruned; you should clean unnecessary records from this table prior to an upgrade.
During the pg_upgrade
process, the pg_cron
extension gets dropped and recreated. Prior to this process, the cron.job_run_details
table is duplicated to avoid losing historical logs. The instantaneous disk pressure created by duplicating an extremely large details table can cause at best unnecessary performance degradation, or at worst, upgrade process failures.
Extensions
pg_upgrade does not currently support upgrading of databases using extensions older than the following versions:
- TimescaleDB 2.16.1
- plv8 3.1.10
To upgrade to a newer version of Postgres, you will need to drop the extensions before the upgrade, and recreate them after the upgrade.
Authentication method changes - deprecating md5 in favor of scram-sha-256
The md5 hashing method has known weaknesses that make it unsuitable for cryptography. As such, we are deprecating md5 in favor of scram-sha-256, which is the default and most secure authentication method used in the latest Postgres versions.
We automatically migrate Supabase-managed roles' passwords to scram-sha-256 during the upgrade process, but you will need to manually migrate the passwords of any custom roles you have created, else you won't be able to connect using them after the upgrade.
To identify roles using the md5 hashing method and migrate their passwords, you can use the following SQL statements after the upgrade:
_10-- List roles using md5 hashing method_10SELECT_10 rolname_10FROM pg_authid_10WHERE rolcanlogin = true_10 AND rolpassword LIKE 'md5%';_10_10-- Migrate a role's password to scram-sha-256_10ALTER ROLE <role_name> WITH PASSWORD '<password>';
Database size reduction
As part of the upgrade process, maintenance operations such as vacuuming are also executed. This can result in a reduction in the reported database size.
Post-upgrade validation
Supabase performs extensive pre- and post-upgrade validations to ensure that the database has been correctly upgraded. However, you should plan for your own application-level validations, as there might be changes you might not have anticipated, and this should be budgeted for when planning your downtime window.
Migrate your project
Migrating projects can be achieved using the Supabase CLI. This is particularly useful for older projects (e.g. to use a newer Postgres version).
Before you begin
- Install Postgres so you can run
psql
andpg_dump
. - Install the latest version of Supabase CLI.
- Create a new Supabase project.
- Install Docker Desktop for your platform.
- Set environment variables for the old project's database URL as
$OLD_DB_URL
and the new project's as$NEW_DB_URL
. To find the database URL for a project, go to the project's dashboard page Project Settings/Database and look underConnection string
. If your network provider supports IPv6, you can disableUse connection pooling
. Otherwise, enableUse connection pooling
. For the pooler mode,Transaction
will work.
Backup your old database
- Run the following command from your terminal:
_10supabase db dump --db-url "$OLD_DB_URL" -f roles.sql --role-only_10supabase db dump --db-url "$OLD_DB_URL" -f schema.sql_10supabase db dump --db-url "$OLD_DB_URL" -f data.sql --use-copy --data-only
Restore to your new project
In your new project:
- Enable Database Webhooks if you enabled them in your old project.
- Enable any extensions that were enabled in your old project.
If you use column encryption, first copy the root encryption key to your new project using your Personal Access Token.
_10export OLD_PROJECT_REF="<old_project_ref>"_10export NEW_PROJECT_REF="<new_project_ref>"_10export SUPABASE_ACCESS_TOKEN="<personal_access_token>"_10_10curl "https://api.supabase.com/v1/projects/$OLD_PROJECT_REF/pgsodium" \_10 -H "Authorization: Bearer $SUPABASE_ACCESS_TOKEN" |_10curl "https://api.supabase.com/v1/projects/$NEW_PROJECT_REF/pgsodium" \_10 -H "Authorization: Bearer $SUPABASE_ACCESS_TOKEN" \_10 -X PUT --json @-
Then run the following command from your terminal:
_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 "$NEW_DB_URL"
Setting the session_replication_role
to replica
disables all triggers so that columns are not double encrypted.
Troubleshooting notes:
- 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 theschema.sql
file 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 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
Enable publication on tables
Replication for Realtime is disabled for all tables in your new project. On the Publications page in the Dashboard, select your new project and enable replication for tables that were enabled in your old project.
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})()
Transfer to a different organization
Note that project migration is for transferring your projects to different regions. If you need to move your project to a different organization without touching the infrastructure, see project transfers.