deployment

428 notes in this subject, read out of this brain and free to use. This is page 7 of 8.

pg_dump command with parallelization

Create database dump with parallelization: `pg_dump --host=<source_host> --port=<source_port> --username=<source_username> --dbname=<source_database> --jobs=$DUMP_JOBS --format=directory --no-owner --no-privileges --no-subscriptions --verbose --file=./db_dump 2>&1 | ts | tee -a dump.log`. Adjust DUMP_JOBS based on source CPU cores and setup.

pg_dump flags for Supabase migration

Use `--no-owner --no-privileges` flags at dump time to prevent Supabase user management conflicts. Use `--no-subscriptions` because logical replication subscriptions won't work in the target. To migrate single schema add `--schema=PATTERN`, to exclude schema use `--exclude-schema=PATTERN`, to migrate single table use `--table=PATTERN`, to exclude table use `--exclude-table=PATTERN`.

Recommended pg_dump parallelization (-j values)

Parallelization recommendations: <10 GB use 2 (testing) or 4 (production); 10-100 GB use 2-4 (testing) or 8 (production); 100-500 GB use 4 (testing) or 16 (production), limited by Disk IOPS; 500 GB-1 TB use 4-8 (testing) or 16-32 (production), limited by Disk IOPS + CPU. For testing without maintenance window, use lower -j values to avoid impacting production performance.

pg_restore command for Supabase

Restore dump to Supabase with parallelization: `pg_restore --dbname="$SUPABASE_DB_URL" --jobs=$RESTORE_JOBS --format=directory --no-owner --no-privileges --verbose ./db_dump 2>&1 | ts | tee -a restore.log`. Adjust RESTORE_JOBS based on Supabase compute size: Free/Small=2, Medium=4, Large=8, XL=16. Note: -j cannot be used with --single-transaction.

Post-migration tasks after restore

After restore completes, run `VACUUM VERBOSE ANALYZE;` to update statistics for optimal performance. For Postgres 18+, pg_dump includes statistics with `--with-statistics`, but you should still run VACUUM.

Verify migration with row counts

After restore, verify data migration by checking row counts with: `select schemaname, tablename, n_live_tup from pg_stat_user_tables order by n_live_tup desc limit 20;` and run application-specific verification queries.

Re-enable writes on source database after migration

If keeping the source database after migration, re-enable writes with: `ALTER DATABASE your_database_name SET default_transaction_read_only = false;`

Migration time estimates

Migration times vary by database size: 10 GB takes ~5 min dump + ~10 min restore (~15 min total); 100 GB takes ~30 min dump + ~45 min restore (~1.5 hours); 500 GB takes ~2 hours dump + ~3 hours restore (~5 hours); 1 TB takes ~4 hours dump + ~6 hours restore (~10 hours). Times vary based on hardware, network, and parallelization settings.

Logical replication prerequisites

Logical replication requires Postgres 10+ on both source and target. Source must have connection string with rights to CREATE PUBLICATION and read tables. Superuser or replication privileges are recommended.

Required Postgres configuration for logical replication

Source database requires these settings: `wal_level = logical`, `max_wal_senders ≥ 1`, `max_replication_slots ≥ 1`, and sufficient `max_connections` (current + 1 for subscription).

Replica identity requirement for logical replication

Every table receiving UPDATE/DELETE must have a replica identity (typically a PRIMARY KEY). For tables without a primary key, set: `ALTER TABLE schema.table_name REPLICA IDENTITY FULL;`

Items not replicated by logical replication

DDL changes (schema modifications), Sequences (need manual sync), and Large Objects/LOBs (use dump/restore or store in regular bytea columns) are not replicated. Plan a schema freeze, sequence sync before cutover, and handle LOBs separately.

Postgres.conf settings for logical replication

Configure source Postgres.conf with: `wal_level = logical`, `max_replication_slots = 10`, `max_wal_senders = 10`, `max_connections = 200` (adjust based on needs), optionally `ssl = on` for secure replication, and `listen_addresses = '*'` (or specific IP addresses) to allow connections from Supabase.

pg_hba.conf settings for logical replication

Configure source pg_hba.conf to allow replication connections from Supabase: `host replication all <supabase_ip_range> md5` and `host all all <supabase_ip_range> md5`. With SSL: `hostssl replication all <supabase_ip_range> md5` and `hostssl all all <supabase_ip_range> md5`. Replace <supabase_ip_range> with actual Supabase IP range.

Verify logical replication configuration

Verify source configuration with: `SHOW wal_level;` (should return 'logical'), `SHOW max_replication_slots;`, `SHOW max_wal_senders;`, and `SELECT count(*) FROM pg_stat_activity;` to check current connections.

Find tables without primary keys for logical replication

Query to find tables without primary keys: `SELECT n.nspname, c.relname FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_constraint pk ON pk.conrelid = c.oid AND pk.contype = 'p' WHERE c.relkind = 'r' AND pk.oid IS NULL AND n.nspname NOT IN ('pg_catalog','information_schema');` Then set REPLICA IDENTITY FULL for each.

Temporarily increase Supabase compute during restore

You can temporarily increase compute size and/or disk IOPS and throughput via Settings → Compute and Disk if you want faster database restore. You can use larger -j values for pg_restore if you do so.

Export and restore schema for logical replication

Export schema from source with: `pg_dump -h <source_host> -U <source_user> -p <source_port> -d <source_database> --schema-only --no-privileges --no-subscriptions --format=directory -f ./schema_dump`. Restore to Supabase with: `pg_restore --dbname="$SUPABASE_DB_URL" --format=directory --schema-only --no-privileges --single-transaction --verbose ./schema_dump`

Create publication on source database

Create publication for all tables with: `CREATE PUBLICATION supabase_migration FOR ALL TABLES;` Or for specific tables only (doesn't require superuser): `CREATE PUBLICATION supabase_migration FOR TABLE schema1.table1, schema1.table2, public.table3;` Verify with: `SELECT * FROM pg_publication;`

Create subscription on Supabase for logical replication

Create subscription with SSL (recommended): `CREATE SUBSCRIPTION supabase_subscription CONNECTION 'host=<source_host> port=<source_port> user=<source_user> password=<source_password> dbname=<source_database> sslmode=require' PUBLICATION supabase_migration;` Or without SSL: `CREATE SUBSCRIPTION supabase_subscription CONNECTION 'host=<source_host> port=<source_port> user=<source_user> password=<source_password> dbname=<source_database> sslmode=disable' PUBLICATION supabase_migration;`

Monitor logical replication status on Supabase

Check subscription status with: `select * from pg_subscription_rel;` where srsubstate = 'r' means ready (synchronized), 'i' means initializing, 'd' means data is being copied. Check overall status with: `select * from pg_stat_subscription;`

Monitor logical replication on source database

Check replication status on source with: `select * from pg_stat_replication;`. Check replication lag with: `select slot_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as lag_size from pg_replication_slots;`

Synchronize sequences during logical replication

After initial data sync is complete but BEFORE switching to Supabase: set source to read-only with `ALTER DATABASE <source_database> SET default_transaction_read_only = true;`, export sequences with `pg_dump -h <source_host> -U <source_user> -p <source_port> -d <source_database> --data-only --table='*_seq' --table='*_id_seq' > sequences.sql`, then import to Supabase with `psql "$SUPABASE_DB_URL" -f sequences.sql`

Switch to Supabase after logical replication

Steps to switch to Supabase: (1) Ensure replication lag is zero by checking `pg_stat_subscription` on Supabase; (2) Stop writes to source database (if not already read-only); (3) Drop subscription on Supabase with `DROP SUBSCRIPTION supabase_subscription;`; (4) Update application connection strings to point to Supabase; (5) Verify application functionality.

Cleanup after successful logical replication migration

On source database after successful migration: remove publication with `DROP PUBLICATION supabase_migration;`, check and remove any remaining replication slots with `SELECT * FROM pg_replication_slots;` and `DROP REPLICATION SLOT slot_name;` if any remain. The source database should remain read-only or be decommissioned. Do NOT re-enable writes to avoid a split-brain scenario.

Troubleshooting logical replication issues

Common issues and solutions: "could not connect to the publisher" - check network connectivity, firewall rules, pg_hba.conf; "role does not exist" - ensure replication user exists on source with REPLICATION privilege; "publication does not exist" - verify publication name and it was created successfully; Replication lag growing - check network bandwidth, source database load, add more WAL senders; Tables stuck in 'i' state - check for locks on source tables, verify table structure matches; "out of replication slots" - increase max_replication_slots in Postgres.conf.

Logical replication limitations

Logical replication has these limitations: DDL changes (schema modifications) are not replicated - freeze schema during migration; Sequences need manual synchronization before cutover; Large Objects (LOBs) are not replicated - use dump/restore or store in regular bytea columns; Custom types may need special handling; Users and roles must be recreated manually on Supabase. See Postgres Logical Replication Restrictions for detailed information.

When to use dump/restore vs logical replication

Use Dump/Restore when: downtime window is acceptable, source is Postgres < 10, simpler process preferred, or cannot configure logical replication on the source. Use Logical Replication when: minimal downtime is required, Postgres 10+ on both sides, can modify source configuration, or have replication privileges.

Expected errors during backup restore to new project

Errors like 'object already exists' and 'constraint x for relation y already exists' are expected when restoring to a new Supabase project. This occurs because the full backup dump contains CREATE commands for all schemas, and the new project already has these commands applied to schemas like storage and auth. These errors are not problematic because psql skips to the next command. All triggers will run during the restoration process.

Backup file format and preparation

The backup file will be gzipped with a .gz extension. You must unzip the file so it appears as backup_name.backup before restoring.

Dashboard backup availability for logical backups only

Dashboard backups are only available for older Supabase projects that still use logical backups. Projects using physical backups should follow steps in the Backup and Restore using the CLI guide instead.

Data not included in database restore

The following items are not stored directly in the database and must be re-created or set up manually on the new project: Edge Functions, Auth Settings and API keys, Realtime settings, Database extensions and settings, and Read Replicas.

Restore backup using psql command

Run the command: psql -d [CONNECTION_STRING] -f /file/path where [CONNECTION_STRING] is the connection string from the new project and /file/path is the path to the unzipped backup file.

Pre-restore configuration requirements

Before restoring a backup to a new project, configure it by: enabling Database Webhooks if webhooks were used in the original project, enabling Extensions if any were used, and enabling Publication if Replication for Realtime was used.

PrivateLink architecture and organization level configuration

Supabase PrivateLink is an organisation level configuration that works by sharing a VPC Lattice Resource Configuration to any number of AWS Accounts for each of your Supabase projects. Connectivity can be achieved by either associating the Resource Configuration to a PrivateLink endpoint, or a VPC Lattice Service Network. Database traffic flows through private AWS infrastructure only, network isolation provides enhanced security posture, and attack surface is minimized by eliminating public exposure.

PrivateLink supported ports and services

Supabase PrivateLink supports direct database connections on port 5432 and PgBouncer connections on port 6543. It does not support other Supabase services like API, Storage, Auth, or Realtime. These services will continue to operate over public internet connections.

PrivateLink requirements

To use PrivateLink with your Supabase project, you need: Team or Enterprise Supabase subscription, an AWS VPC in the same region as your Supabase project, and appropriate permissions to accept Resource Shares, and create and manage endpoints.

PrivateLink setup step 1: Add AWS account

To add an AWS account for PrivateLink: Go to your Supabase project dashboard, navigate to Settings > Integrations, find the AWS PrivateLink section, click Add Account, enter your AWS Account ID, provide a description for the account (recommended), and click Add Account to submit. Supabase creates a VPC Lattice Resource Configuration for your project and sends an AWS Resource Share to the specified AWS Account ID. Once complete, the account will show a "Ready" status, indicating that the resource share has been sent to your AWS account and is ready to be accepted.

PrivateLink setup step 2: Accept resource share

To accept the AWS Resource Share containing VPC Lattice Resource Configurations: Login to your AWS Management Console in the AWS region where your Supabase project is located, navigate to AWS Resource Access Manager (RAM) console, go to Shared with me > Resource shares, locate the resource share from Supabase (format: sspl-[project_ref]-[random alphanumeric string]), click on the resource share name to view details and verify it only includes vpc-lattice:ResourceConfiguration resources, click Accept resource share, and confirm the acceptance in the dialog box. After accepting, the resource configurations will appear in your Shared with me > Shared resources section of the RAM console and the PrivateLink and Lattice > Resource configurations section of the VPC console.

PrivateLink setup step 3: Configure security groups

To configure security groups for PrivateLink: Navigate to VPC console > Security Groups, create a new security group for the endpoint or service network, give it a descriptive name and select the appropriate VPC, add inbound rules for the connection mode you use (Direct connection requires Postgres TCP port 5432; PgBouncer connection requires Custom TCP port 6543; if using both, add both rules), set the destination appropriate for your network (such as your VPC subnet or application instances' security group), and finish creating the security group.

PrivateLink setup step 4A: Create PrivateLink endpoint

To create a PrivateLink endpoint: Navigate to VPC console, go to Endpoints in the left sidebar, click Create endpoint, give your endpoint a name (e.g., supabase-privatelink-[project name]), under Type select Resources, in the Resource configurations section select the appropriate resource configuration (format: [organisation]-[project-ref]-rc), select your VPC from the dropdown matching the one from security group configuration, enable Enable DNS name option if you want to use a DNS record, choose appropriate subnets for your network (AWS will provision a private ENI in each selected subnet with IPv4 type), choose the security group created earlier, and click Create endpoint. After creation, the endpoint will show in Endpoints section with status Available, with IP addresses listed in the Subnets section of endpoint details and DNS record in the Associations section if enabled.

PrivateLink setup step 4B: Attach to existing VPC Lattice service network

To attach a resource configuration to an existing VPC Lattice Service Network: Navigate to VPC Lattice console, go to Service networks in the left sidebar and select your service network, in the service network details go to the Resource configuration associations tab, click Create associations, select the appropriate Resource configuration from the dropdown, click Save changes. After creation, the resource configuration will appear in the Resource configurations section of your service network with status Active, and the domain name will be listed in the DNS entries section of the association details.

PrivateLink setup step 5: Test connectivity

To verify the private connection is working: Launch an EC2 instance or use an existing instance within your VPC, install a Postgres client (e.g., psql), and test the connection using the private endpoint with the appropriate connection string for either direct connection (port 5432) or PgBouncer connection (port 6543). You should see a successful connection without any public internet traffic.

PrivateLink test connection examples

Example commands to test PrivateLink connectivity: # Direct connection (Postgres) psql "postgresql://[username]:[pa••••••d]@[private-endpoint]:5432/postgres" # PgBouncer connection psql "postgresql://[username]:[pa••••••d]@[private-endpoint]:6543/postgres"

PrivateLink setup step 6: Update applications

To configure applications for private connections: Update database connection strings to use the private endpoint hostname, ensure application instances are in the same VPC or connected VPCs, update any database connection pooling configurations, and test application connectivity thoroughly.

PrivateLink connection string migration examples

Example connection string updates for PrivateLink: # Direct connection (Postgres) # Before (public): postgresql://user:••••@db.[project-ref].supabase.co:5432/postgres # After (private): postgresql://user:••••@your-private-endpoint.vpce.amazonaws.com:5432/postgres # PgBouncer connection # Before (public): postgresql://user:••••@db.[project-ref].supabase.co:6543/postgres # After (private): postgresql://user:••••@your-private-endpoint.vpce.amazonaws.com:6543/postgres

PrivateLink setup step 7: Restrict public database access

For maximum security with PrivateLink, you can restrict public database access: Go to Database > Settings, in Network Restrictions, enable Restrict all access. Ensure all applications, monitoring, and backup tools are using the private endpoint before enabling this setting.

PrivateLink limitations

PrivateLink has the following limitations: Read Replicas require reaching out to your account representative to establish PrivateLink with a Read Replica; the setup process and capabilities may evolve as the offering is refined.

PrivateLink compatibility

The PrivateLink endpoint is a layer 3 solution that behaves like a standard Postgres endpoint, allowing connection using direct Postgres connections with standard tools and third-party database tools and ORMs (with appropriate routing).

PrivateLink availability and customers

PrivateLink is available only to Team and Enterprise Supabase customers. Contact support if you would like to create a PrivateLink connection for a read-only replica.

PrivateLink overview and benefits

PrivateLink provides enterprise-grade private network connectivity between your AWS VPC and your Supabase database using AWS VPC Lattice. It eliminates exposure to the public internet by creating a secure, private connection that keeps database traffic within the AWS network backbone. By enabling PrivateLink, database connections never traverse the public internet, enabling the disablement of public facing connectivity and providing an additional layer of security and compliance for sensitive workloads.

Restore Supabase platform database to self-hosted Docker instance

To restore a Supabase platform project database to a self-hosted Docker instance, follow these steps: (1) Get your platform connection string from the Supabase dashboard Connect button, (2) Back up the platform database using supabase db dump commands to create separate roles.sql, schema.sql, and data.sql files, (3) Prepare the self-hosted instance by enabling necessary non-default extensions, (4) Restore the dump files using psql, and (5) Verify the restore by checking tables, row counts, and extensions. Transferring storage objects or redeploying edge functions is not covered in this process.

supabase db dump command for platform database backup

The supabase db dump command executes pg_dump under the hood but applies Supabase-specific filtering that excludes internal schemas, strips reserved roles, and adds idempotent IF NOT EXISTS clauses. Use three separate commands to export roles, schema, and data: (1) supabase db dump --db-url "[CONNECTION_STRING]" -f roles.sql --role-only, (2) supabase db dump --db-url "[CONNECTION_STRING]" -f schema.sql, (3) supabase db dump --db-url "[CONNECTION_STRING]" -f data.sql --use-copy --data-only. The CLI requires Docker because it runs pg_dump inside a container from the Supabase Postgres image. Using raw pg_dump directly will include Supabase internals and cause permission errors during restore.

Self-hosted Supabase default Postgres connection string

The default connection string for self-hosted Supabase is: postgres://postgres.your-tenant-id:[PO••••••D]@[your-domain]:5432/postgres. The POSTGRES_PASSWORD value comes from the POSTGRES_PASSWORD environment variable in the self-hosted .env file. For [your-domain], use your domain name, server IP, or localhost depending on whether you are running self-hosted Supabase on a VPS or locally.

psql restore command with session_replication_role

To restore dump files to self-hosted Postgres, use this psql command: psql --single-transaction --variable ON_ERROR_STOP=1 --file roles.sql --file schema.sql --command 'SET session_replication_role = replica' --file data.sql --dbname "postgres://postgres.your-tenant-id:[PO••••••D]@[your-domain]:5432/postgres". Setting session_replication_role to replica disables triggers during the data import, preventing issues like double-encryption of columns.

Database dump includes and excludes for platform to self-hosted restore

The database dump includes: schema, data, roles, RLS policies, database functions, triggers, and auth.users table. The database dump does NOT include: JWT secrets and API keys (must generate new ones and update .env), auth provider settings like OAuth (must configure GOTRUE_EXTERNAL_* variables in .env), edge functions (must manually copy), storage objects (must transfer separately), SMTP/email settings (must configure SMTP_* variables in .env), and custom domains and DNS (must point DNS to self-hosted server).

Custom database roles missing passwords after restore

If you created custom database roles with the LOGIN attribute on your platform project, their passwords are not included in the dump. Set them manually after restore using: ALTER ROLE your_custom_role WITH PASSWORD 'new-password';

Restore verification checks for self-hosted database

After restoring to self-hosted, verify the restore by connecting to your self-hosted database and running checks: (1) \dt public.* to check your tables are present, (2) SELECT count(*) FROM auth.users; to verify row counts on key tables, (3) SELECT * FROM pg_extension; to check extensions. Use connection string: psql "postgres://postgres.your-tenant-id:[PO••••••D]@[your-domain]:5432/postgres"

Postgres version compatibility between platform and self-hosted

Managed Supabase may run a newer Postgres version (17) than the self-hosted Docker image (currently Postgres 15 by default). The supabase db dump command produces plain SQL files that work across major Postgres versions. If the managed project runs Postgres 17, consider starting the self-hosted deployment with Postgres 17 as well. Run the restore on a test self-hosted instance first to identify any incompatibilities.

Common version mismatch issues in data.sql during restore

Common issues when restoring data.sql from a newer Postgres version (17) to an older version (15): (1) SET transaction_timeout = 0 is a Postgres 17-only setting that fails on Postgres 15, (2) COPY statements for tables that don't exist on self-hosted such as auth.oauth_clients, storage.buckets_vectors, storage.vector_indexes, (3) COPY statements with columns added in newer Auth versions such as auth.flow_state with oauth_client_state_id or linking_target_id. Workaround: Edit data.sql before restoring by commenting out the problematic lines.

Give your agent this brain