How to Move Data Between SQLite MySQL and PostgreSQL

Moving a small application from SQLite to PostgreSQL, or a MySQL database into Postgres, is a regular chore. The tempting path is to take a dump and feed it to the other engine, and it fails on the first quoted identifier. Going through a neutral format and recreating the schema deliberately is slower to start and reliable. It builds on exporting query results to CSV and importing CSV. The examples assume SQLite 3.46, PostgreSQL 17 and MySQL 8.4.

Why a native dump does not load

sqlite3 app.db .dump | head -4
mysqldump --no-data appdb | grep -m2 -E 'ENGINE|AUTO_INCREMENT'

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, created_at DATETIME);
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8mb4;

Each engine's dump uses its own syntax: SQLite's PRAGMA and AUTOINCREMENT, MySQL's backticks and ENGINE=, PostgreSQL's COPY blocks and SERIAL or identity columns. Types differ too (SQLite's DATETIME is not a PostgreSQL type) and boolean, date and blob values are written differently. Fixing a dump with sed works for tiny schemas and breaks on anything with defaults or constraints.

Step 1: create the target schema by hand

CREATE TABLE users (
  id          bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name        text NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE orders (
  id        bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  user_id   bigint NOT NULL REFERENCES users(id),
  total     numeric(10,2) NOT NULL,
  paid      boolean NOT NULL DEFAULT false
);

Write the schema for the target engine, mapping types deliberately: SQLite INTEGER to bigint, TEXT to text, a 0/1 flag to boolean, a date stored as text to timestamptz (decide the time zone). MySQL TINYINT(1) is usually a boolean, DATETIME has no zone, and utf8mb4 maps to PostgreSQL's UTF-8. Doing this by hand is the chance to fix the old design.

Step 2: export and import CSV

sqlite3 -header -csv app.db "SELECT id, name, created_at FROM users" > users.csv
psql -d appdb -c "\copy users (id, name, created_at) FROM 'users.csv' WITH (FORMAT csv, HEADER true)"
mysql -e "SELECT id, name, created_at FROM users INTO OUTFILE '/var/lib/mysql-files/users.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n'" appdb
mysql --local-infile=1 -e "LOAD DATA LOCAL INFILE 'users.csv' INTO TABLE users FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' IGNORE 1 LINES" appdb

CSV preserves values and handles quoting of commas, quotes and newlines when exported by the engine. \copy in psql reads a client-side file; server-side COPY needs file access on the database host. MySQL's INTO OUTFILE writes on the server, restricted to the secure_file_priv directory, and LOCAL INFILE must be enabled on both sides. Check NULLs: empty field versus the string NULL differ by engine, and CSV export options control it (NULL '' in COPY).

Step 3: fix identifiers and constraints

SELECT setval(pg_get_serial_sequence('users', 'id'), (SELECT max(id) FROM users));
SELECT setval(pg_get_serial_sequence('orders', 'id'), (SELECT max(id) FROM orders));

Loading explicit IDs does not advance the sequence, so the next insert collides with an existing row. Reset every sequence after loading. Load tables in dependency order (parents first) or defer constraints, and re-enable checks afterwards. Create indexes after the bulk load; it is faster than loading into indexed tables.

Step 4: verify

sqlite3 app.db "SELECT count(*), sum(total) FROM orders"
psql -d appdb -Atc "SELECT count(*), sum(total) FROM orders"
sqlite3 app.db "SELECT id, name FROM users ORDER BY id LIMIT 3" | md5sum
psql -d appdb -Atc "SELECT id || '|' || name FROM users ORDER BY id LIMIT 3" | md5sum

Compare counts and sums for each table, then sample actual rows. Watch for silent changes: trimmed strings, truncated text, timestamps shifted by a zone, floats rounded, boolean values swapped. Run the application's tests against the new database before switching, and keep the old one read-only until the new one has run for a while.

Automating with a tool

pgloader sqlite:///app.db postgresql:///appdb
pgloader mysql://root@localhost/appdb postgresql:///appdb

pgloader reads SQLite, MySQL and others, converts types, creates tables, loads data in parallel and resets sequences. Review what it created, since its default type mapping may not be what you would choose. A tool is the right choice for large databases or many tables; for three small tables the manual way is more transparent.

Planning the move before touching data

A migration is a project with a plan, and the plan starts with an inventory. List the tables, their row counts and sizes, the constraints, the indexes, the views, the triggers and the stored routines, and mark which of them use features the target engine lacks. Note the data types that map imperfectly: unsigned integers, enumerations, unusually precise decimals, binary columns, date values outside the target's range and text in encodings other than UTF-8. The inventory tells you whether you are moving rows, which is simple, or porting an application, which is not. It also gives you the checklist for verification afterwards.

Decide the downtime budget now. A copy of a small database during a quiet evening takes minutes, and the application can be stopped for it. A database that serves traffic continuously and holds hundreds of gigabytes needs a different method, such as replicating changes while the bulk copy runs and switching over once the two agree. That is a larger undertaking with its own tools, and choosing the simple method for a database that needs the complex one results in an outage nobody planned.

Character sets, collations and time

Text and time cause the quietest damage. If the source uses a legacy encoding or a collation with different sorting and case rules, a unique constraint that held in the source can fail or, worse, silently allow duplicates in the target. Convert to UTF-8 on export, and choose a collation in the target on purpose, testing the queries that depend on ordering. Timestamps without zones are ambiguous: a value stored as local time in the source needs the right zone applied when loaded into a zone-aware column, or every row shifts by hours. Pick one rule, apply it in the export query and check a handful of known rows by eye.

Check the edges: empty strings versus nulls, which some engines treat as the same, zero dates such as 0000-00-00 that MySQL can store and PostgreSQL rejects, and extremely long text values. Find them with queries against the source before the load and decide what each becomes.

Performance of the load itself

Bulk loading is fast when indexes and constraints are applied after the data. Create the tables, load with the bulk path, then build indexes and add foreign keys. For PostgreSQL, increase maintenance_work_mem for the index builds, and run ANALYZE afterwards so the planner has statistics. Disable triggers during the load if they have side effects such as writing audit rows. For a large table, load in chunks by key range so a failure does not force a restart from the beginning, and log the last completed range.

Rehearse and keep the way back

Do the whole move on a copy first, time it, and run the application's tests against the result. Fix what surfaces, and run it again until it is boring. Keep the source database untouched and read-only until the new one has been in service long enough to trust, and write down the rollback: how to point the application back and what to do about writes made to the new system in the meantime. A migration with a rehearsed rollback is a routine change; one without is a gamble taken at the worst time.

Where this does not apply

Stored procedures, triggers, views with engine-specific SQL and application code that uses dialect features need a rewrite, not just a data move. For a live system, a migration needs a cut-over plan, such as dual writes or a short freeze, see copying a database between servers. See the PostgreSQL COPY documentation and the pgloader documentation.