Home

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#

note

This upgrade method is currently in Beta, and is being slowly made available to projects.

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.

  1. Plan for an appropriate downtime window, and ensure you have reviewed the caveats section of this document before executing the upgrade.
  2. 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#

note

This method is only available for projects on the Free plan. For projects on the Pro plan, please contact support for assistance with upgrading.

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.

  1. 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.
  2. 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.

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.

Extensions registering system OID-specific reg* data types:

  • plv8
  • plcoffee
  • plls

As these extensions offer support to create functions in different languages, you will only need to check for functions using the languages enabled by the above extensions. Any objects depending on the detected objects (e.g. triggers, views, tables, etc.) will need to be modified to exclude references to them.

The following query returns the function name and their definitions, to help in recreating them post-upgrade.


_10
SELECT
_10
pn.nspname || '.' || objid::regproc AS function_name,
_10
pg_get_functiondef(objid) || ';' AS function_definition
_10
FROM pg_depend d
_10
JOIN pg_language l on (d.refobjid = l.oid)
_10
JOIN pg_proc pp on (d.objid = pp.oid)
_10
JOIN pg_namespace pn on (pp.pronamespace = pn.oid)
_10
WHERE l.lanname in ('plls', 'plcoffee', 'plv8')
_10
AND pn.nspname <> 'pg_catalog';

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
_10
SELECT
_10
rolname
_10
FROM pg_authid
_10
WHERE rolcanlogin = true
_10
AND rolpassword LIKE 'md5%';
_10
_10
-- Migrate a role's password to scram-sha-256
_10
ALTER ROLE <role_name> WITH PASSWORD '<password>';

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 and pg_dump.
  • Install 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 at Connection string / URI. For example, to set the $OLD_DB_URL you would run export OLD_DB_URL=postgresql://postgres:[YOUR-PASSWORD]@db.[YOUR-PROJECT-REF#].supabase.co:5432/postgres.

Backup your old database#

  1. Run the following command from your terminal:

_10
supabase db dump --db-url "$OLD_DB_URL" -f roles.sql --role-only
_10
supabase db dump --db-url "$OLD_DB_URL" -f schema.sql
_10
supabase db dump --db-url "$OLD_DB_URL" -f data.sql --use-copy --data-only

Restore to your new project#

In your new project:

  1. Enable Database Webhooks if you enabled them in your old project.
  2. 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.


_10
export OLD_PROJECT_REF="<old_project_ref>"
_10
export NEW_PROJECT_REF="<new_project_ref>"
_10
export SUPABASE_ACCESS_TOKEN="<personal_access_token>"
_10
_10
curl "https://api.supabase.com/v1/projects/$OLD_PROJECT_REF/pgsodium" \
_10
-H "Authorization: Bearer $SUPABASE_ACCESS_TOKEN" |
_10
curl "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:


_10
psql \
_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 the schema.sql file and comment out any lines containing ALTER ... OWNER TO "supabase_admin".

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.


_10
supabase link --project-ref "$OLD_PROJECT_REF"
_10
supabase 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 Replication 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
_52
const { createClient } = require('@supabase/supabase-js')
_52
_52
const OLD_PROJECT_URL = 'https://xxx.supabase.co'
_52
const OLD_PROJECT_SERVICE_KEY = 'old-project-service-key-xxx'
_52
_52
const NEW_PROJECT_URL = 'https://yyy.supabase.co'
_52
const 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 infrastrusture, see project transfers.