Learning Center

Migrate a Self-Hosted App from SQLite to Postgres Without Losing Data

Outgrow SQLite deliberately: rehearse the migration, prove the new database, and keep rollback realistic until the app survives real validation.

SQLite to PostgresCutover planningRollback discipline

What you learn

How to evaluate app support, back up the SQLite source, rehearse a conversion, point the app at Postgres, and verify that the new database behaves correctly before you retire the old file.

Best for

Apps that started simple with SQLite but now need better concurrency, external backups, richer tooling, or a cleaner production database path.

Risk to watch

The dangerous part is not copying rows. It is missing app-specific assumptions, background writers, or schema mismatches during cutover.

Before you begin

Documentation from the application itself confirming PostgreSQL support.
A fresh backup of the SQLite database file and related app configuration.
A maintenance window or write-freeze plan if the app is already in use.
A PostgreSQL target database you can destroy and recreate during rehearsal.

SQLite is excellent for small, low-concurrency apps, but growth eventually exposes its limits. The safe move is to treat migration as an application change, not just a database format change.

Reference note: pgloader’s SQLite support can automatically discover schema and load data into PostgreSQL, which makes it a strong open-source option for rehearsal and one-time migrations when the application supports a direct database switch.

Step 1: Confirm the app really supports PostgreSQL

Before touching data, verify the application’s own migration path. Some apps provide:

a built-in upgrade command
a documented database switch procedure
automatic schema creation on startup
special handling for jobs, extensions, or search indexes

If the app documentation says “supported” but does not document migration, inspect how it expects database URLs, whether it needs migrations rerun, and whether background workers must be stopped during the change.

Warning: If the app is not designed for PostgreSQL, do not improvise a production cutover just because tables can be copied.

Step 2: Back up and freeze the SQLite source

Make a copy of the SQLite file before any experiment. If the app is live, stop writes first so you do not capture a half-changing state.

cp app.db app.db.pre-migration-20260610
sqlite3 app.db ".backup 'app.db.backup'"

If the application uses WAL mode, confirm the related files are handled correctly or stop the app first and copy the whole database state together. Pair the data copy with the app’s config, media files, and anything else required for a complete rollback.

For the PostgreSQL side, create a dedicated user and database:

createuser --pwprompt myapp
createdb --owner=myapp myapp

Step 3: Rehearse the conversion on a disposable target

Do not make production your first attempt. Spin up a throwaway PostgreSQL database and practice the move there.

With pgloader, the simplest path looks like this:

pgloader sqlite:///path/to/app.db   postgresql://myapp:YOURPASSWORD@127.0.0.1/myapp_rehearsal

After loading, point a non-production copy of the application at the rehearsal database and run its own migrations or upgrade commands if required. This is where you learn whether booleans, timestamps, unique constraints, or application-generated indexes need attention.

Document every adjustment you needed during rehearsal. The real migration should be boring because the surprises already happened on the practice run.

Step 4: Cut over the app during a quiet window

When rehearsal succeeds, plan the real cutover:

Put the app into maintenance mode or stop writes.
Take a final SQLite backup.
Run the same validated conversion procedure against the real PostgreSQL target.
Change the application database settings.
Start only the app components you need for validation first.

# Example environment switch
DATABASE_URL=postgresql://myapp:YOURPASSWORD@postgres:5432/myapp

Keep the SQLite file untouched after cutover. It is part of your rollback plan until the new database proves itself under real reads and writes.

Step 5: Validate before calling the migration complete

At minimum, verify:

users can log in
new records can be created
old records still appear correctly
background workers reconnect and write successfully
the app’s own migrations report a clean state

psql postgresql://myapp@127.0.0.1/myapp -c '\dt'
psql postgresql://myapp@127.0.0.1/myapp -c 'select count(*) from your_key_table;'

PostgreSQL’s documentation notes that pg_dump produces a consistent export snapshot. Once the new system is stable, add a proper PostgreSQL backup routine instead of assuming the old SQLite file backup habits still fit.

Expected outcome: The application is now using PostgreSQL, critical data matches expectations, and rollback is still possible because the original SQLite state was preserved.

Troubleshooting common migration problems

The app starts but behaves strangely.
Check whether the app expected its own migrations to run after the database switch. Schema presence alone may not equal application readiness.

Boolean or timestamp data looks wrong.
Inspect how the application and migration tool mapped SQLite types. Rehearsal is where these mismatches should have been caught.

Background workers fail after cutover.
Verify that every service received the new database URL and that queues or task tables were migrated as expected.

You are not sure whether to roll back.
If validation of core user actions fails, stop and restore the known-good SQLite-based deployment. Do not keep layering fixes on a half-validated production cutover.

What to do next

Continue with How to Run Postgres in Docker Compose With Safe Persistence.