Migrate from PostgreSQL

#1. Prepare

1. 1

   Create the project

   Pick the region closest to your users — it can't be changed later. Sovaria runs PostgreSQL 16; dump with a version 16 pg_dump (it reads older servers).

2. 2

   Check extensions

   Compare what you use with what Sovaria offers; turn the ones you need on first (Schema › Extensions).

   Old database

   select extname, extversion from pg_extension order by 1;

3. 3

   Check the size

   Your plan's database disk must hold the data with room to grow (Plan limits).

4. 4

   Get the connection string

   Connect › Database: host db.<org-id>.sovarias.in, port 5432, sslmode=require (Database: connection, compute, disk and backups). It is a team credential that bypasses your Sovaria rules — keep it out of apps.

#2. Dump and restore

# Schema and data of the public schema, without owners or grants
pg_dump "$OLD_DATABASE_URL" --schema=public --no-owner --no-privileges --format=plain --file=dump.sql

# Into Sovaria; stop at the first error
psql "postgresql://<db-user>:<password>@db.<org-id>.sovarias.in:5432/<database>?sslmode=require" \
  --set ON_ERROR_STOP=1 --single-transaction --file=dump.sql

• --single-transaction makes the restore all or nothing; fix the error and run it again on an empty database.
• Policies written TO <role> for a role Sovaria doesn't have fail to restore. Change them to TO public with a condition on the signed-in user — using (owner_id = auth.uid()) (Auth helpers in SQL).
• If a large table's COPY hits the statement timeout, load it on its own with a higher statement_timeout (up to your plan's maximum), or split the data (Pooling and timeouts).

#3. Record a baseline

Write the restored schema as your first migration, so every later change goes through migrations and your environments stay in step (Migrations):

sov link            # in your app's folder
sov db pull         # writes the current schema as a baseline file in sovaria/migrations/

#4. Decide who may read and write

Restored tables are closed to app users until something allows them: a table needs row-level security that is enabled and forced, or an access policy (How access is decided).

alter table orders enable row level security;
alter table orders force row level security;
create policy "customers see their orders" on orders
  for select using (customer_id = auth.uid());

Then check: the Security advisor lists tables without rules (Security advisor), and sov sim run shows what every kind of user can do (Simulate access before you ship).

#5. Users

• If your app kept its own users table, create the accounts in Sovaria Auth (Authentication › Users, or sov auth-product create --email …) and send each user a password reset link. There is no import of password hashes from an arbitrary system; Supabase and Firebase users can be imported with their passwords (Migrate from Supabase or Firebase).
• Sovaria user ids are UUIDs. Add a mapping table (old_id → new uuid) and update ownership columns, so policies can compare them with auth.uid().

#6. Validate

-- exact row counts per table
select 'orders' as t, count(*) from orders
union all select 'customers', count(*) from customers;

-- spot-check content: a hash of a table's rows in key order
select md5(string_agg(t::text, ',' order by id)) from orders t;

• Counts and hashes match for every table you moved.
• Sequences continue after the highest id (pg_dump sets them): insert a test row.
• Your app's test suite passes against a staging environment (Testing your app).

#7. Cut over

1. 1

   Rehearse

   Do the whole move once on a staging environment and time it.

2. 2

   Stop writes

   Put the app in maintenance mode or make the old database read-only.

3. 3

   Move the final data

   Restore again from a fresh dump (into an empty database), then validate.

4. 4

   Switch the app

   Deploy with the Sovaria project URL and keys (Connect your app), and turn writes back on.

#Rolling back

Keep the old database untouched and read-only until you are confident. To roll back, point the app at it again; anything written to Sovaria after the cutover has to be copied back by hand, so keep the window between cutover and decision short.