Verify postgres role is active in Supabase Studio
After restarting services, verify that Supabase Studio is using the postgres role by running the query `select current_user;` in the Studio SQL Editor. The expected result is postgres.
deployment
428 notes in this subject, read out of this brain and free to use. This is page 6 of 8.
Postgres 17 upgrade from Postgres 15 uses pg_upgrade
Upgrading an existing self-hosted Supabase deployment from Postgres 15 to Postgres 17 uses pg_upgrade to migrate data in place. The included upgrade scripts automate the full process via: sudo bash utils/upgrade-pg17.sh
Resolve leftover db-config volume preventing Postgres 17 start
If starting a fresh Postgres 17 deployment (not using upgrade script) and the container fails to start, a leftover db-config volume from Postgres 15 is likely the cause. Fix by removing the old volume and letting Postgres 17 initialize clean configuration: sh run.sh stop && docker volume rm $(docker volume ls --filter "name=db-config" --format '{{.Name}}') && sh run.sh start. Removing db-config destroys any custom Postgres configuration and pgsodium root key, so only do this for fresh installations with no existing data or vault secrets.
Postgres 17 upgrade disk space staging location
The upgrade script uses /tmp (or TMPDIR if set) for its staging directory, which holds the downloaded tarball and upgrade scripts. If /tmp filesystem is small or limited, point to a different location: sudo TMPDIR=/mnt/my-tmp bash utils/upgrade-pg17.sh. If disk space runs out mid-upgrade, the safest path is to roll back and free up disk space before retrying.
Reconnect services after Postgres 17 upgrade
If services fail to connect after upgrade, restart all services to pick up the new database: sh run.sh recreate
pgsodium and Vault key preservation in Postgres 17 upgrade
The db-config named volume contains the pgsodium root encryption key at /etc/postgresql-custom/pgsodium_root.key. This volume is preserved during the upgrade. Never run docker compose down -v as this destroys named volumes and makes vault secrets unrecoverable.
Fix Postgres 17 upgrade permission denied errors
If permission denied errors occur on the data directory after upgrade, fix file ownership automatically handled by the upgrade script. If errors persist, run: docker compose run --rm db chown -R postgres:postgres /var/lib/postgresql/data. Postgres 15 and 17 use different UIDs.
Resolve pg_upgrade replication slot errors
pg_upgrade cannot proceed if there are active replication slots. Default self-hosted installs don't have any, but if replication slots exist, drop them before upgrading: docker exec supabase-db psql -h localhost -U supabase_admin -d postgres -c "SELECT pg_drop_replication_slot(slot_name) FROM pg_replication_slots;". Replication slots must be manually recreated after the upgrade.
Postgres 17 upgrade Phase 2 finalization details
Phase 2 of the upgrade process (Postgres 17 container): 1) Moves upgraded data directory into place and starts Postgres 17, 2) Applies extension compatibility patches for Wrappers, pg_net, pg_cron, and Vault, 3) Runs SQL scripts generated by pg_upgrade to update system catalogs and extension versions, 4) Re-enables extensions disabled in phase 1, 5) Grants predefined roles (pg_monitor, pg_read_all_data, pg_signal_backend, and on Postgres 16+ also pg_create_subscription) and revokes temporary superuser grant, 6) Restarts Postgres and runs vacuumdb --all --analyze-in-stages to rebuild optimizer statistics.
Postgres 17 upgrade Phase 1 data migration details
Phase 1 of the upgrade process (Postgres 15 container): 1) Disables incompatible extensions (pg_graphql, pg_stat_monitor, pg_backtrace) and generates SQL to re-enable them, 2) Temporarily grants superuser to postgres role (required by pg_upgrade), 3) Extracts Postgres 17 binaries tarball and runs initdb to create empty database, 4) Runs pg_upgrade --check to verify upgrade can succeed before making changes, 5) Stops Postgres 15 and runs pg_upgrade to migrate all data, 6) Copies Postgres configuration and SQL scripts generated by pg_upgrade to staging directory for next phase.
Verify Postgres 17 configuration settings
To verify new Postgres settings are applied, execute: docker compose exec db psql -U postgres -c "SHOW [setting_name];"
Restart Postgres 17 after configuration changes
After adding custom Postgres configuration files to /etc/postgresql-custom/conf.d/, restart the database to apply changes: sh run.sh restart db. Some settings like max_connections require a full restart.
Custom Postgres configuration in Postgres 17
The Postgres 17 image loads any .conf files from /etc/postgresql-custom/conf.d/ on startup. This directory is on the db-config named volume so changes persist across restarts. This is a Supabase Postgres 17 image feature; the Postgres 15 image does not load files from conf.d/. Since conf.d/ is on a Docker named volume (not a bind mount), write through the container: docker exec supabase-db bash -c 'cat > /etc/postgresql-custom/conf.d/custom.conf << EOF [config] EOF'
Rollback from Postgres 17 to Postgres 15
To revert to Postgres 15 (run as root), execute: docker compose down && rm -rf ./volumes/db/data && mv ./volumes/db/data.bak.pg15 ./volumes/db/data && sh run.sh config add pg15 && docker compose run --rm db chown -R postgres:postgres /etc/postgresql-custom/ && sh run.sh start. This restores the original data directory, fixes file ownership (Postgres 15 and 17 use different UIDs), and configures Supabase to use Postgres 15. Rollback is only possible while the backup exists.
Postgres 17 upgrade creates backups
The upgrade script automatically preserves the original Postgres 15 data directory at ./volumes/db/data.bak.pg15 and the pgsodium root key at ./volumes/db/pgsodium_root.key.bak.pg15. The upgrade binaries tarball is cached at ./volumes/db/pg17_upgrade_bin_*.tar.gz. After verifying the upgrade works, these can be deleted to reclaim disk space.
Verify Postgres 17 after upgrade
To verify that Postgres 17 is running after upgrade, execute: docker compose exec db psql -U postgres -c "SELECT version();"
Postgres 17 upgrade requirements
Requirements for upgrading to Postgres 17: at least 2x current database size plus 5 GB free disk space, upgrade script prompts for confirmation at each major step (use --yes to skip prompts), all self-hosted Supabase containers must be running before starting upgrade, requires bash, must be run as root or using sudo.
Optional logical backup before Postgres 17 upgrade
Optionally take a logical backup before upgrading using: docker exec supabase-db pg_dumpall -h localhost -U supabase_admin > ./pg15_dump.sql
Backup pgsodium encryption key before Postgres 17 upgrade
Back up the pgsodium encryption key before upgrading. The key is stored in a Docker named volume and can be backed up with: docker compose run --rm db cat /etc/postgresql-custom/pgsodium_root.key > ./pgsodium_root.key.backup. If this key is lost and vault secrets exist, they become unrecoverable.
Backup database data directory before Postgres 17 upgrade
Before upgrading, create a manual backup of the database data directory: cp -a ./volumes/db/data ./volumes/db/data-manual-backup
Postgres 17 upgrade process steps
The upgrade process: 1) Pulls Postgres 17 image and extracts upgrade binaries, 2) Pulls supplemental upgrade scripts from supabase/postgres repository, 3) Stops all self-hosted Supabase containers, 4) Runs pg_upgrade inside a temporary Postgres 15 container, 5) Runs additional tasks inside a temporary Postgres 17 container (re-enables extensions, applies patches, runs VACUUM ANALYZE), 6) Swaps data directories keeping original as backup, 7) Starts self-hosted Supabase with Postgres 17, 8) Applies post-upgrade migrations and reconciles extension versions.
Postgres 17 upgrade disk space requirements
The Postgres 17 upgrade requires at least 2x your current database size plus 5 GB of free disk space. The pg_upgrade process copies the data directory, and the upgrade tarball is approximately 1.2 GB when compressed.
Self-hosted Supabase Postgres 17 default
Self-hosted Supabase ships with Postgres 17 by default. A new self-hosted instance with no existing data starts on Postgres 17 automatically.
Restore from manual backup after failed Postgres 17 upgrade
If upgrade fails and built-in rollback is insufficient, restore from manual backups. Restore data: docker compose down && rm -rf ./volumes/db/data && cp -a ./volumes/db/data-manual-backup ./volumes/db/data && sh run.sh config add pg15. Restore pgsodium key: docker compose run --rm db sh -c 'cat > /etc/postgresql-custom/pgsodium_root.key' < ./pgsodium_root.key.backup && docker compose run --rm db chown -R postgres:postgres /etc/postgresql-custom/ && docker compose run --rm db chmod 600 /etc/postgresql-custom/pgsodium_root.key. Start with Postgres 15: sh run.sh start
Check deployment status in dashboard
To check deployment status and troubleshoot failures: go to your project dashboard, navigate to 'Manage Branches', click on your branch to view deployment logs, and check the 'View logs' section for detailed error messages.
Schema drift between preview branches
When multiple preview branches exist, each might contain different schema changes, similar to Git branches with different code changes. When a preview branch is merged into the production branch, it creates schema drift between the production branch and preview branches that haven't been merged yet. Resolve conflicts by merging or rebasing from the production Git branch to the preview Git branch. Ensure migration files are timestamped correctly after rebase, with changes that build on earlier changes having later timestamps.
Cannot change production branch
You cannot change which project branch serves as the production branch. The base project that all branches are created from will always remain the production branch. However, you can update which GitHub branch is linked to your production branch by going to the Integrations page and changing the production branch name.
Preview branch auto-pause behavior
Preview branches auto-pause after inactivity. If you can't connect to a preview branch, check if it's paused—it will resume on the first request. First connections after pause may timeout; retry and the branch will wake up after the first request. Convert frequently-used branches to persistent to avoid auto-pause.
Connection troubleshooting for preview branches
If you can't connect to a preview branch: ensure you're using correct branch-specific credentials, check if the branch is paused (it will resume on first request), and check if network restrictions are blocking access.
Config.toml troubleshooting
If configuration changes aren't applying: validate your config.toml syntax, ensure changes are committed and pushed, and try deleting and recreating the branch.
Secrets in preview branches
Secrets are set per branch and not automatically inherited. To use secrets in a branch, use correct syntax: 'env(SECRET_NAME)'. Ensure you're using the latest CLI version.
Preview branch data is temporary
Preview branch data is temporary: data is lost when the branch is deleted, data doesn't move between branches, and deleting and recreating a branch loses all data.
Slow branch creation causes
Branch creation might be slow due to large migrations (many or complex migration files), large seed files that take time to process, or geographic distance from the branch region.
Preview branch query performance characteristics
Preview branches may have different performance characteristics: first queries after auto-pause are slower due to cold starts, preview branches have different resource allocations than production, and proper indexes should exist in migrations for optimal performance.
Monitor branch deployments programmatically
Use the Management API endpoint (https://api.supabase.com/api/v1#tag/environments/post/v1/projects/{ref}/branches) to poll branch status for programmatic monitoring of deployments.
Common deployment failure causes
Deployments might fail for various reasons including invalid SQL statements, schema conflicts in migrations, errors within the config.toml file, or other issues. Check the Supabase workflow run for your branch under the 'View logs' section in the dashboard to see error messages.
Deploy local email template changes
To apply email template changes in local development, stop and restart the Supabase containers using the command: supabase stop && supabase start.
Deploy database migrations to remote project
Use 'supabase db push' to deploy any local database migrations to your remote Supabase project.
Neon to Supabase migration overview
To migrate a Neon database to Supabase, retrieve database credentials from both platforms, set environment variables with connection strings, then use pg_dump to export from Neon and psql to import into Supabase.
Supabase connection string requires password replacement
When setting the NEW_DB_URL environment variable for Supabase, you must replace [YOUR-PASSWORD] in the connection string with your actual database password from your Supabase project.
Export Neon database with pg_dump
Use pg_dump with the OLD_DB_URL environment variable to export a Neon database to a SQL file. The command is: pg_dump "$OLD_DB_URL" --clean --if-exists --quote-all-identifiers --no-owner --no-privileges > dump.sql
Import database to Supabase with psql
Use psql to import a SQL dump file to your Supabase project. The command is: psql -d "$NEW_DB_URL" -f dump.sql
Retrieve Supabase connection string
On your Supabase project dashboard, click Connect, then under the Session pooler section, click the Copy button to the right of the connection string to copy it to the clipboard. The connection string format is: postgresql://postgres.xxxxxxxxxxxxxxxxxxxx:[YO••••••D]@aws-0-us-west-1.pooler.supabase.com:5432/postgres
Retrieve Neon database connection string
Log in to the Neon Console at https://console.neon.tech/login, select Projects on the left, click your project in the list, find your Connection string in the Project Dashboard, and click Copy snippet to copy it to the clipboard without checking 'pooled connection'.
PostgreSQL tools required for database migration
The pg_dump and psql command line tools are required for migrating a database from Neon to Supabase. These tools are included in a full PostgreSQL installation available at https://www.postgresql.org/download.
Requirements for Vercel Postgres to Supabase migration
To migrate from Vercel Postgres to Supabase, you need the pg_dump and psql command-line tools, which are included in a full Postgres installation available from postgresql.org/download.
Migrate Vercel Postgres to Supabase using pg_dump and psql
To migrate a Vercel Postgres database to Supabase, use pg_dump and psql command-line tools. First, retrieve the Vercel Postgres connection string from the Vercel Dashboard by logging in, clicking the Storage tab, selecting your Postgres Database, clicking on the Quickstart section, selecting psql, and clicking Show Secret. Then retrieve your Supabase connection string from your Supabase project dashboard by clicking Connect and copying the Session pooler connection string. Export the Vercel database using pg_dump with the --clean, --if-exists, --quote-all-identifiers, --no-owner, and --no-privileges flags, saving to a file like dump.sql. Finally, import the database to Supabase using psql with the -d flag specifying the NEW_DB_URL and -f flag pointing to the dump.sql file.
pg_dump command for Vercel Postgres migration
Export a Vercel Postgres database using: pg_dump "$OLD_DB_URL" --clean --if-exists --quote-all-identifiers --no-owner --no-privileges > dump.sql. The OLD_DB_URL should be set as an environment variable containing the full Vercel Postgres connection string including username, password, host, port, and database name with sslmode=require.
psql import command for Supabase migration
Import a database dump to Supabase using: psql -d "$NEW_DB_URL" -f dump.sql. The NEW_DB_URL should be set as an environment variable containing the Supabase connection string in the format postgresql://postgres.xxxxxxxxxxxxxxxxxxxx:[YO••••••D]@aws-0-us-west-1.pooler.supabase.com:5432/postgres, with [YOUR-PASSWORD] replaced with the actual Supabase database password.
pg_dump options for selective schema and table migration
pg_dump supports several options for selective migration: --schema=PATTERN migrates only a single database schema, --exclude-schema=PATTERN excludes a schema, --table=PATTERN migrates only a single table, and --exclude-table=PATTERN excludes a table. Run pg_dump --help for a full list of options.
Vercel Postgres credentials retrieval process
To retrieve Vercel Postgres credentials: log in to the Vercel Dashboard, click the Storage tab, click on your Postgres Database, go to the Quickstart section, select psql, and click Show Secret to reveal the database password. Copy the connection string after 'psql ' which includes the format postgres://username:password@host:port/database?sslmode=require.
Supabase connection string retrieval for migration
To retrieve a Supabase connection string for migration: create a Supabase project if needed (noting the database password for later use), go to your project dashboard, click Connect, and under the Session pooler section, click the Copy button to copy the connection string. The connection string follows the format postgresql://postgres.xxxxxxxxxxxxxxxxxxxx:[PA••••••D]@aws-0-us-west-1.pooler.supabase.com:5432/postgres.
Migration support thresholds
For databases > 150 GB, contact Supabase support at /dashboard/support/new before starting migration. Supabase provides support via Dashboard Support, Discord, and documentation guides.
Resource requirements for Postgres migration
Resource requirements by database size: <10 GB needs Default compute with 2 vCPUs/4 GB RAM (no action required); 10-100 GB needs Default-Small with 4 vCPUs/8 GB RAM (consider upgrade); 100-500 GB needs Large compute with 8 vCPUs/16 GB RAM/NVMe (upgrade before restore); 500 GB-1 TB needs XL compute with 16 vCPUs/32 GB RAM/NVMe (upgrade before restore); >1 TB needs Custom (contact support).
Users and RLS status not migrated
User roles/privileges are not migrated during Postgres migration to Supabase and must be recreated manually. Additionally, Row Level Security (RLS) status on tables is not migrated and must be re-enabled after migration.
Ubuntu migration VM setup
Install Postgres client tools on Ubuntu with: `sudo apt update`, `sudo apt install software-properties-common`, add Postgres apt repository, `sudo apt install Postgres-client-17 tmux htop iotop moreutils`. Use tmux for session persistence with `tmux a -t migration || tmux new -s migration`.
Connection modes for migration
Supabase provides three connection modes: Direct connection, Supavisor session mode, and Supavisor transaction mode. Use Supavisor session mode for database migration tasks (pg_dump/restore and logical replication).
Pre-migration SQL checks
Before migrating, check database size with `select pg_size_pretty(pg_database_size(current_database())) as size;`, check Postgres version with `select version();`, list installed extensions with `select * from pg_extension order by extname;`, and check active connections with `select count(*) from pg_stat_activity;`
Postgres migration: three methods available
Supabase provides three methods for migrating a Postgres database: Google Colab (guided notebook with copy-paste workflow), Manual Dump/Restore (CLI approach, works for all versions), and Logical Replication (minimal downtime, requires Postgres 10+).
Set source database to read-only for production migration
For production migrations with a maintenance window, prevent data changes by running on the source database: `ALTER DATABASE your_database_name SET default_transaction_read_only = true;`