Migrating backend-as-a-service to Postgres

You want off your backend-as-a-service platform and onto your own Postgres, maybe for cost, maybe for control, maybe because you outgrew the plan. You dumped the database, it restored fine, and then you hit the wall nobody warned you about: your logins stopped working, your security rules vanished, and you’re not sure how to get back if this goes wrong. The data was the easy part. Here’s the map for the parts that actually decide whether this migration works.

Why the data move is the easy 20%

Copying rows out of your platform and into a Postgres server you control is genuinely simple. The platform runs on real Postgres under the hood. A pg_dump and a pg_restore handle your tables, indexes, and data cleanly, and for a small app the whole thing takes minutes.

The hard 80% is everything the platform did for you that isn’t stored in your tables:

Auth. The platform’s auth layer (built on GoTrue) manages your users, passwords, sessions, and OAuth logins. It lives in the auth schema and is wired into the platform. Move off it and you have to decide who runs auth now.
Row Level Security. Your RLS policies enforce who can see what, right inside the database, using auth.uid(). That function comes from the platform’s auth layer. Move the database and auth.uid() no longer exists, so every policy silently stops working the way it did.
The APIs. The platform auto-generates a REST and realtime API from your schema. Your frontend probably calls it directly. Your own Postgres has none of that.

Nail these three and the migration works. Skip them and you’ll ship an app that “restored successfully” and locks out every user on day one. Let’s take them as decisions, not commands.

Step 1: get the data across (the easy part, done right)

Dump the schema and data from the platform. Use the connection string from your project settings:

# Dump everything except the platform's internal schemas you don't own
pg_dump "postgresql://postgres:[PASSWORD]@[YOUR_PLATFORM_DB_HOST]:5432/postgres" \
  --schema=public \
  --no-owner --no-privileges \
  --format=custom \
  --file=platform_dump.pgdump

Restore into your new Postgres 17 server:

createdb myapp
pg_restore --no-owner --no-privileges --dbname=myapp platform_dump.pgdump

Note the --schema=public. You’re deliberately not dragging the platform’s auth, storage, and realtime schemas along, because those belong to the platform you’re leaving. Your users’ rows live in auth.users, though, so you do need to export those separately if you plan to keep the accounts (more on that below). Verify row counts match before you trust anything:

-- Run on both old and new, compare the numbers
select relname, n_live_tup
from pg_stat_user_tables
order by relname;

Step 2: the auth decision tree

This is the biggest fork in the road. You have three realistic paths. Pick based on how much you want to run yourself.

Option A: keep the platform’s auth service, move only the database.

Some platforms let you use their auth service against an external Postgres, or you keep the platform purely as an auth provider while your data lives elsewhere. You keep the hosted login flow and only migrate data.

Good when: you like the auth experience and just wanted database control or lower data costs.
Trade-off: you’re not fully off the platform. You still depend on it for logins, still pay for that piece, and still have a vendor in your critical path. This is the smallest change and the least clean break.

Option B: switch to a managed auth provider.

Hand auth to a dedicated service. A hosted auth provider gives you a hosted UI and user management out of the box. An open-source auth library you run in your own app is free and gives you more control but more to maintain.

Good when: you want to be fully off the platform but don’t want to run auth infrastructure yourself.
Trade-off: you have to migrate users. Passwords are stored as hashes you usually can’t move directly, so most teams either trigger a password reset for everyone on first login, or import users and force a reset. OAuth users (Google, GitHub) re-link cleanly by email. Budget for the user-communication side of this, not just the code.

Option C: self-host GoTrue (the engine behind the platform’s auth service).

The platform’s auth service is open source. You can run the same GoTrue service on your own server against your own Postgres.

Good when: you want zero vendor lock-in and full control, and you’re comfortable running another service (Docker, config, SMTP for emails, keeping it patched).
Trade-off: it’s the most work and the most ongoing responsibility. You own uptime, security patches, and email deliverability. Because it’s the same engine, your existing auth.users table and password hashes can often move across intact, which makes it the cleanest path for keeping logins working without a mass password reset. That’s the upside that offsets the operational cost.

Decision shortcut: want the smallest change, pick A. Want off the platform with the least ops, pick B. Want full control and no forced password reset, pick C.

Step 3: translate RLS to real authorization

Here’s the quiet failure. Your policies look like this today:

create policy "users read own orders"
  on orders for select
  using (auth.uid() = user_id);

That auth.uid() is a function from the platform’s auth layer that reads the logged-in user from the request’s JWT. On your own Postgres, without that request layer, auth.uid() doesn’t exist, and the policy either errors or, worse, quietly stops filtering. Your security disappears and nothing crashes to warn you.

You have two ways to keep the same protection.

Move authorization into your app layer. Instead of the database deciding who sees what, your backend does. Every query that reads user data adds the where user_id = $currentUser filter in code. This is how most apps outside this kind of platform work. It’s simple and explicit, but the safety now depends on you never forgetting the filter, so centralize it (a single data-access layer, not scattered queries).

Keep RLS, but provide the user yourself. You can keep database-level RLS if your app sets the current user on each connection. Replace auth.uid() with a value your app passes in:

-- Your app runs this at the start of each request's transaction
set local app.current_user_id = 'the-logged-in-user-uuid';

-- Rewrite policies to read that setting instead of auth.uid()
create policy "users read own orders"
  on orders for select
  using (current_setting('app.current_user_id', true)::uuid = user_id);

This keeps your defense inside the database, where a forgotten where clause can’t leak data. It’s more setup but a stronger guarantee. Whichever you choose, test it: log in as user A and confirm you cannot read user B’s rows. Do this before launch, not after.

Step 4: replace the auto-generated API

Your frontend probably calls the platform’s generated REST client directly. On your own stack you need to provide those endpoints. The pragmatic path is a thin backend (a small Node or similar service) that exposes the handful of routes your app actually uses. You don’t need to rebuild every table’s CRUD, just the operations your frontend really calls. Audit your frontend for calls to the platform’s client library to see the real surface area, which is usually smaller than you’d guess.

Step 5: the rollback plan (keep the old platform warm)

This is the step that lets you sleep. Do not delete or pause your old project the moment the new server responds. Run a period where the platform stays live and untouched as your fallback.

The safe cutover:

Stand up the new Postgres and the new auth path in parallel. The old platform keeps serving production.
Do a final data sync right before cutover, so the new database matches.
Flip your app’s config (connection string, auth provider) to the new stack.
Watch logins, reads, and writes closely for at least a few days.
If anything’s wrong, flip the config back to the old platform. Because you never touched it, rollback is instant.
Only after a clean stretch (a week is reasonable) do you decommission the old project.

The mistake that turns a migration into an incident is tearing down the old system before the new one has proven itself under real traffic. Keeping the old platform warm costs you a few more days of one bill. That’s cheap insurance against a lockout you can’t undo.

Or, we do it for you

Moving off a backend-as-a-service platform with auth, RLS, and a tested rollback intact is exactly the kind of job our Rescue service handles end to end, so your users never notice the switch. See what a Rescue engagement covers.

FAQ

Can I just use pg_dump and be done?

For the data, yes. For a working app, no. pg_dump moves your tables and rows perfectly, but it doesn’t move auth, and it doesn’t make your RLS policies work on a database that no longer has auth.uid(). The dump is step one of five, not the whole job.

Will my users have to reset their passwords?

It depends on the auth path. If you self-host GoTrue (the same engine the platform uses), the password hashes usually move across and users notice nothing. If you switch to a different auth provider, the hashes typically don’t transfer, so most teams force a one-time reset. Plan the user communication either way.

What happens to my Row Level Security after the move?

It stops working the way it did, quietly. Your policies rely on auth.uid(), which is a function from the platform’s auth layer that won’t exist on your own Postgres. You either move authorization into your app’s query layer or rewrite the policies to read the current user from a setting your app provides. Test that one user can’t read another’s data before you go live.

How long should I keep the old platform running after cutover?

At least a week of clean production traffic on the new stack. Keep the old project live and untouched the entire time so a rollback is just a config flip. The cost of an extra week is trivial next to the cost of a lockout you can’t reverse because you already tore down the old system.