Home / Guides
Migration guide

SQLite to PostgreSQL: .dump, a Converter and pgloader

By · November 9, 2026 · 12 min read

The short version

To move SQLite to PostgreSQL you have three routes: pipe sqlite3 .dump into Postgres and fix what breaks, push the file through a converter, or let pgloader read the SQLite file directly. Whichever you pick, the work is the same: SQLite lets any column hold any type, so dates arrive as text, Julian days and Unix seconds mixed together, an INTEGER column can hold the word "two", and orphaned foreign keys pile up because enforcement is off by default. Clean those in SQLite first where you know what a value meant, load, then check that each id column is an identity whose sequence sits past the loaded ids, or the first new row fails. A generated dump also leaves out UNIQUE constraints, defaults and foreign keys, so add those last.

I ran the .dump route and our converter against PostgreSQL 16.14 with a small database built to hold every trap. pgloader is not installed on the laptop I used, so its section is what its documentation says, not something I ran.

The SQLite database, with the traps in it

Two tables, written the way SQLite schemas usually look:

CREATE TABLE customers (
  id          INTEGER PRIMARY KEY,
  email       VARCHAR(20) NOT NULL UNIQUE,
  name        TEXT,
  is_active   BOOLEAN NOT NULL DEFAULT 1,
  signup_date DATE,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE orders (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  qty         INTEGER,
  total       NUMERIC(10,2),
  ordered_at  DATETIME
);

Every line of that is legal SQLite and none of it means what it would in PostgreSQL:

I loaded three customers and four orders, stored one signup date as a Julian day and one as Unix seconds, put 'two' in a quantity, pointed one order at customer 99 (who does not exist), and deleted order 4. SQLite accepted all of it. typeof() shows what is really stored:

$ sqlite3 -header -column shop.db "PRAGMA foreign_keys; SELECT ... FROM customers; SELECT ... FROM orders; SELECT * FROM sqlite_sequence;"
foreign_keys
------------
0
id  email_len  is_active  t_active  signup_date  t_signup
--  ---------  ---------  --------  -----------  --------
1   15         1          integer   2024-03-01   text
2   34         0          integer   2460371.5    real
3   16         1          integer   1709337600   integer
id  customer_id  qty  t_qty    total  t_total  ordered_at           t_at
--  -----------  ---  -------  -----  -------  -------------------  -------
1   1            2    integer  19.99  real     2024-03-05 12:00:00  text
2   2            two  text     5.5    real     2024-03-06 08:00:00  text
3   99           1    integer  100    integer  1709856000           integer
name    seq
------  ---
orders  4

A 34-character email in a VARCHAR(20), a word in an integer column, three storage classes for one date column, and sqlite_sequence remembering 4 while the highest surviving order id is 3. With PRAGMA foreign_keys=ON, an insert pointing at a missing customer fails with FOREIGN KEY constraint failed, but nobody had it on. PRAGMA foreign_key_check lists the orphan after the fact: orders|3|customers|0.

Route 1: sqlite3 .dump straight into PostgreSQL

The SQLite shell's .dump renders the whole database as SQL, and the same page even shows sqlite3 ex1 .dump | psql ex2. With a schema like this one it does not get far. I sent the file to Postgres in one transaction, fixed one error, and ran it again, until it loaded. The errors in the order they came:

ERROR:  syntax error at or near "PRAGMA"
LINE 1: PRAGMA foreign_keys=OFF;

ERROR:  syntax error at or near "AUTOINCREMENT"
LINE 14:   id          INTEGER PRIMARY KEY AUTOINCREMENT,

ERROR:  type "datetime" does not exist
LINE 8:   created_at  DATETIME DEFAULT CURRENT_TIMESTAMP

ERROR:  column "is_active" is of type boolean but default expression is of type integer
HINT:  You will need to rewrite or cast the expression.

ERROR:  column "is_active" is of type boolean but expression is of type integer
LINE 10: ...ustomers VALUES(1,'ana@example.com','Ana O''Neil',1,'2024-03...

ERROR:  column "signup_date" is of type date but expression is of type numeric
LINE 11: ...,'bruno.longname@example-company.com','Bruno','0',2460371.5,...

ERROR:  value too long for type character varying(20)

ERROR:  invalid input syntax for type integer: "two"
LINE 21: INSERT INTO orders VALUES(2,2,'two',5.5,'2024-03-06 08:00:00...

ERROR:  column "ordered_at" is of type timestamp without time zone but expression is of type integer
LINE 22: INSERT INTO orders VALUES(3,99,1,100,1709856000);

ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(99) is not present in table "customers".

ERROR:  relation "sqlite_sequence" does not exist
LINE 22: DELETE FROM sqlite_sequence;

Eleven errors for two tables before it committed. The dump also wrote the REAL 19.99 as 19.98999999999999843; it landed as 19.99 only because the column was NUMERIC(10,2). Once it finally committed, the first insert that let the database pick the id failed:

=> INSERT INTO orders (customer_id, qty, total) VALUES (1, 1, 9.99) RETURNING id
ERROR:  null value in column "id" of relation "orders" violates not-null constraint
DETAIL:  Failing row contains (null, 1, 1, 9.99, null).

SQLite filled the rowid by itself; PostgreSQL has nothing to fill it with. Adding an identity is not the whole fix either, because a new identity starts at 1:

=> ALTER TABLE orders ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY
ALTER TABLE
=> INSERT INTO orders (customer_id, qty, total) VALUES (1, 1, 9.99) RETURNING id
ERROR:  duplicate key value violates unique constraint "orders_pkey"
DETAIL:  Key (id)=(1) already exists.

The .dump route works for a tiny file you are willing to hand-edit. For anything bigger, clean the data at the source and generate the DDL.

Clean the types in SQLite first

Every value error above is easier to fix while the data is still in SQLite, where typeof() tells you exactly which rows are odd. Count the storage classes per column:

col                    t        n
---------------------  -------  -
customers.signup_date  integer  1
customers.signup_date  real     1
customers.signup_date  text     1
orders.qty             integer  2
orders.qty             text     1
orders.ordered_at      integer  1
orders.ordered_at      text     2

Then rewrite them with SQLite's own date functions. A number given to date() is read as a Julian day unless the unixepoch modifier follows it, so the two kinds need two statements:

UPDATE customers SET signup_date = date(signup_date) WHERE typeof(signup_date) = 'real';
UPDATE customers SET signup_date = date(signup_date, 'unixepoch') WHERE typeof(signup_date) = 'integer';
UPDATE orders SET ordered_at = datetime(ordered_at, 'unixepoch') WHERE typeof(ordered_at) = 'integer';
UPDATE orders SET qty = 2 WHERE id = 2 AND qty = 'two';

Afterwards every date is ISO text and every quantity an integer. Do this on a copy. Whether an integer means Unix seconds or something else is a call you make by looking at the values; I checked that 1709337600 and 2460371.5 both came out as 2024-03-02.

Route 2: the converter, loaded into PostgreSQL

Our SQLite to PostgreSQL converter reads the file and writes a dump.sql for Postgres. I ran its reader and writer from Python on the original, uncleaned shop.db. It wrote the dump and listed what it had to decide:

wrote dump.sql, README.txt
SQLITE_NUMERIC_DATE_CONVERTED: customers.signup_date: 1 REAL value read as a Julian day number
SQLITE_NUMERIC_DATE_CONVERTED: customers.signup_date: 1 INTEGER value read as Unix seconds (UTC)
SQLITE_NON_NUMERIC_TO_NULL: orders.qty: 1 non-numeric value written as NULL
SQLITE_NUMERIC_DATE_CONVERTED: orders.ordered_at: 1 INTEGER value read as Unix seconds (UTC)

Those are the same rules SQLite's date functions use: a REAL is a Julian day, and an integer is Unix seconds when you would write the unixepoch modifier yourself. Text that is not a date, or a word in a number column, becomes NULL and is counted, so 'two' is a NULL you can find again, not a failed load. If you know what the value meant, fix it in SQLite first, as above. The orders table, in full:

CREATE TABLE "orders" (
  "id" BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  "customer_id" BIGINT NOT NULL,
  "qty" BIGINT,
  "total" NUMERIC(10,2),
  "ordered_at" TIMESTAMP,
  PRIMARY KEY ("id")
);

INSERT INTO "orders" ("id", "customer_id", "qty", "total", "ordered_at") VALUES
(1, 1, 2, 19.99, TIMESTAMP '2024-03-05 12:00:00'),
(2, 2, NULL, 5.5, TIMESTAMP '2024-03-06 08:00:00'),
(3, 99, 1, 100, TIMESTAMP '2024-03-08 00:00:00');
SELECT setval(pg_get_serial_sequence('"orders"', 'id'), GREATEST(MAX("id"), 4), true) FROM "orders";

The rowid alias became an identity column. The 4 in the setval line is the high-water mark from sqlite_sequence, so the sequence resumes after the largest id SQLite ever handed out, not after the largest one still in the table. That keeps the AUTOINCREMENT promise that an id is never reused. pg_get_serial_sequence finds the sequence behind the identity, and setval with true makes the next value one higher. NUMERIC(10,2) kept its precision and scale. In customers, the Julian day and the Unix seconds both arrived as DATE '2024-03-02', booleans as TRUE/FALSE, and VARCHAR(20) became TEXT, so the 34-character email fits. The file loaded in one transaction, and the insert that broke the .dump route went through:

COMMIT
=> INSERT INTO orders (customer_id, qty, total) VALUES (1, 1, 9.99) RETURNING id
id
5
=> SELECT id, customer_id, qty, total, ordered_at FROM orders ORDER BY id
id  customer_id qty total   ordered_at
1   1   2   19.99   2024-03-05 12:00:00
2   2   NULL    5.50    2024-03-06 08:00:00
3   99  1   100.00  2024-03-08 00:00:00
5   1   1   9.99    NULL

Order 4 was deleted in SQLite, and its id stays retired in Postgres.

What the converter leaves to you

The schema SQLite declared but the dump does not carry: no UNIQUE on email, no column defaults, no foreign key. Without the default, a customer insert that leaves is_active out fails:

=> INSERT INTO customers (email) VALUES ('dee@example.com') RETURNING id
ERROR:  null value in column "is_active" of relation "customers" violates not-null constraint
DETAIL:  Failing row contains (4, dee@example.com, null, null, null, null).

So, on a fresh load of the same dump.sql, before any new rows:

=> ALTER TABLE customers ADD UNIQUE (email)
ALTER TABLE
=> ALTER TABLE customers ALTER COLUMN is_active SET DEFAULT true
ALTER TABLE
=> ALTER TABLE customers ALTER COLUMN created_at SET DEFAULT CURRENT_TIMESTAMP
ALTER TABLE
=> ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers (id)
ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(99) is not present in table "customers".
=> ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID
ALTER TABLE
=> INSERT INTO orders (customer_id, qty, total) VALUES (1, 1, 9.99) RETURNING id
id
5
=> INSERT INTO customers (email) VALUES ('dee@example.com') RETURNING id, is_active
id  is_active
4   True
=> INSERT INTO orders (customer_id, qty, total) VALUES (98, 1, 1) RETURNING id
ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(98) is not present in table "customers".

The foreign key could not be added as written because of the orphan from SQLite. NOT VALID skips the check of existing rows but enforces it on new ones, which the last insert shows. Decide what order 3 belongs to, fix it, then run VALIDATE CONSTRAINT.

If you took the .dump route, the ids have no identity at all. ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY adds one without touching the rows, and it starts at 1, so follow it with SELECT setval(pg_get_serial_sequence('orders','id'), 4) before the first insert, taking the 4 from sqlite_sequence (or from MAX(id) for a table without AUTOINCREMENT).

Route 3: pgloader, from its documentation

pgloader reads a SQLite file directly. Its SQLite reference gives the one-line form:

pgloader sqlite:///path/to/file.db pgsql://pguser@pghost/dbname

By that page, the defaults are create tables, include drop, create indexes, reset sequences, downcase identifiers. include drop drops target tables whose names appear in the SQLite file, with CASCADE, so point it at an empty database. The default casts map integer to bigint, every char and varchar type to text, and datetime and timestamp to timestamptz. reset sequences sets each sequence to the current maximum of its column, which for an AUTOINCREMENT table with deleted rows at the top is lower than sqlite_sequence.

What the page does not say: what happens to 'two' in an integer column, how a REAL Julian day or Unix seconds in a datetime column is converted, or whether foreign keys are created. I did not run pgloader for this guide, so I will not guess. Run it on a copy, then repeat the typeof() counts and the insert test above.

Did everything arrive?

Counts and sums on both sides of the converter load, before the test inserts:

$ sqlite3 shop.db "SELECT 'customers', count(*) FROM customers UNION ALL SELECT 'orders', count(*) FROM orders;"
customers|3
orders|3
$ sqlite3 shop.db "SELECT sum(total), sum(qty), min(ordered_at), max(ordered_at) FROM orders;"
125.49|3.0|1709856000|2024-03-06 08:00:00

=> SELECT 'customers' AS t, count(*) FROM customers WHERE id <= 3 UNION ALL SELECT 'orders', count(*) FROM orders WHERE id <= 3
t   count
customers   3
orders  3
=> SELECT sum(total), sum(qty), min(ordered_at), max(ordered_at) FROM orders WHERE id <= 3
sum sum min max
125.49  3   2024-03-05 12:00:00 2024-03-08 00:00:00

The totals match. sum(qty) is 3 on both sides because SQLite's sum() counts 'two' as 0 and Postgres skips the NULL. The dates do not compare at all in SQLite: an integer sorts before any text, so min() returns the raw Unix seconds and max() misses the latest order. In Postgres they are one type and sort as dates. The WHERE id <= 3 leaves out the rows my insert tests added.

If the SQLite file is a Django project's database, the framework route is different and covered in Django: switching from SQLite to PostgreSQL. If you are still deciding whether to move at all, see SQLite vs PostgreSQL: when it is time to move.

Sources