Skip to content

Latest commit

 

History

History
87 lines (59 loc) · 3.85 KB

postgres.mdx

File metadata and controls

87 lines (59 loc) · 3.85 KB
Raw

import Layout from '~/layouts/DefaultGuideLayout'

export const meta = { title: 'Migrate from Postgres to Supabase', description: 'Migrate your Postgres database to Supabase.', }

This is a guide for migrating your Postgres database to Supabase. Supabase is a robust and open-source platform. Supabase provide all the backend features developers need to build a product: a Postgres database, authentication, instant APIs, edge functions, realtime subscriptions, and storage. Postgres is the core of Supabase—for example, you can use row-level security and there are more than 40 Postgres extensions available.

This guide demonstrates how to migrate your Postgres database to Supabase to get the most out of Postgres while gaining access to all the features you need to build a project.

Retrieve your Postgres database credentials [#retrieve-credentials]

  1. Log in to your provider to get the connection details for your Postgres database.
  2. Click on PSQL Command and edit it adding the content after PSQL_COMMAND=.

Example:

%env PSQL_COMMAND=PGPASSWORD=RgaMDfTS_password_FTPa7 psql -h dpg-a_server_in.oregon-postgres.provider.com -U my_db_pxl0_user my_db_pxl0

Retrieve your Supabase Host [#retrieve-supabase-host]

  1. If you're new to Supabase, create a project.
  2. Go to the Database settings for your project in the Supabase Dashboard.
  3. Under Connection Info, note your Host ($SUPABASE_HOST).
  4. Save your password or reset it if you forgot it.

Finding Supabase host address

Migrate the database

The fastest way to migrate your database is with the Supabase migration tool on Google Colab. Alternatively, you can use the pg_dump and psql command line tools, which are included in a full PostgreSQL installation.

<Tabs scrollable size="small" type="underlined" defaultActiveId="colab" queryGroup="migrate-method"

  1. Set the environment variables (PSQL_COMMAND, SUPABASE_HOST, SUPABASE_PASSWORD) in the Colab notebook.
  2. Run the first two steps in the notebook in order. The first sets the variables and the second installs PSQL and the migration script.
  3. Run the third step to start the migration. This will take a few minutes.

Export your database to a file in console [#export-database]

Use pg_dump with your Postgres credentials to export your database to a file (e.g., dump.sql).

pg_dump --clean --if-exists --quote-all-identifiers \
 -h $HOST -U $USER -d $DATABASE \
 --no-owner --no-privileges > dump.sql

Import the database to your Supabase project [#import-database-to-supabase]

Use psql to import the Postgres database file to your Supabase project.

psql -h $SUPA_URL -U postgres --file dump.sql -p 6543 -d postgres

Additional options

  • To only migrate a single database schema, add the --schema=PATTERN parameter to your pg_dump command.
  • To exclude a schema: --exclude-schema=PATTERN.
  • To only migrate a single table: --table=PATTERN.
  • To exclude a table: --exclude-table=PATTERN.

Run pg_dump --help for a full list of options.

export const Page = ({ children }) =>

export default Page