Конвертация SQLite в PostgreSQL

Перетащите файл базы данных SQLite (.db, .sqlite, .sqlite3) или SQL, который пишет .dump в sqlite3, и скачайте скрипт PostgreSQL с теми же таблицами, ключами, индексами и данными. Каждый столбец получает тип, в который помещаются его значения, а каждое значение, которое PostgreSQL не может хранить как есть, указано в отчете.

Руководство по миграции SQLite в PostgreSQL Типы данных из SQLite в PostgreSQL

Ваш файл

sqlshop_2024_sqlite.sqlПример файла, 3,4 кБ

18 строк конвертировано, без исправлений, ни одна не отклонена

ТаблицыSQLОтчет

Таблицы в shop_2024_sqlite.sqlДамп SQLite · создан программой sqlite3 · utf-8

customers8 строк
7 столбцов
id bigint not null
name text not null
email text not null
country text not null
is_active boolean not null
signed_up date
created_at timestamp(0) not null

orders10 строк

9 столбцов
id	bigint not null
customer_id	bigint not null
status	text not null
total	numeric(10,2) not null
items	integer not null
ordered_at	timestamp(0) not null
shipped_at	timestamp(0)
note	text
receipt	bytea

Не конвертировано

Представление order_totals
Триггер orders_shipped

Каждая база данных пишет их на своем языке. Перепишите их для PostgreSQL.

-- Written by IntoSQL from a SQLite dump. Load it with: psql -v ON_ERROR_STOP=1 -f <file>
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET synchronous_commit = off;
SET maintenance_work_mem = '512MB';

CREATE TABLE customers (
    id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    name text NOT NULL,
    email text NOT NULL,
    country text DEFAULT 'US' NOT NULL,
    is_active boolean DEFAULT true NOT NULL,
    signed_up date,
    created_at timestamp(0) DEFAULT LOCALTIMESTAMP NOT NULL,
    CONSTRAINT customers_check1 CHECK (char_length(country) = 2)
);

CREATE TABLE orders (
    id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    customer_id bigint NOT NULL,
    status text DEFAULT 'pending' NOT NULL,
    total numeric(10,2) NOT NULL,
    items integer DEFAULT 1 NOT NULL,
    ordered_at timestamp(0) NOT NULL,
    shipped_at timestamp(0),
    note text,
    receipt bytea,
    CONSTRAINT orders_check1 CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled'))
);

COPY customers (id, name, email, country, is_active, signed_up, created_at) FROM stdin;
1	Ana Martínez	ana@example.com	MX	t	2023-01-15	2023-01-15 10:24:00
2	Zoë Müller	zoe@example.de	DE	t	2023-02-03	2023-02-03 08:11:42
3	José Peña	jose@example.es	ES	f	2023-03-21	2023-03-21 19:05:17
4	李雷	lilei@example.cn	CN	t	2023-05-09	2023-05-09 02:40:00
5	Noah O'Brien	noah@example.ie	IE	t	\N	2023-06-30 14:00:00
6	Amélie Laurent	amelie@example.fr	FR	t	2024-01-02	2024-01-02 09:30:00
7	Ravi Patel	ravi@example.in	IN	f	2024-02-29	2024-02-29 23:59:59
8	Sofia Rossi 🌻	sofia@example.it	IT	t	2024-04-12	2024-04-12 12:12:12
\.

COPY orders (id, customer_id, status, total, items, ordered_at, shipped_at, note, receipt) FROM stdin;
1	1	shipped	129.9	3	2024-03-01 10:00:00	2024-03-02 16:30:00	\N	\N
2	2	paid	54	1	2024-03-02 11:15:00	\N	Gift wrap 🎁	\N
3	1	cancelled	12.5	1	2024-03-03 09:45:00	\N	Customer changed their mind	\N
4	4	shipped	980	12	2024-03-05 02:10:00	2024-03-07 08:00:00	请小心轻放	\\x25504446
5	5	pending	45.75	2	2024-03-08 17:20:00	\N	Leave at the door;\nring twice	\N
6	6	shipped	310.4	5	2024-03-10 13:00:00	2024-03-11 09:00:00	Fragile: "glass"	\N
7	8	paid	76.2	2	2024-03-11 18:45:00	\N	\N	\N
8	2	paid	19.99	1	2024-03-12 08:00:00	\N	Paid at the counter	\N
9	3	shipped	64	2	2024-03-14 15:30:00	\N	Shipping date never recorded	\N
10	7	paid	1499	1	2024-03-15 21:05:00	\N	C:\\Users\\ravi\\invoice.pdf	\N
\.


-- Keys, indexes and constraints, after the rows so each builds once.
CREATE UNIQUE INDEX customers_pkey ON customers (id);
CREATE UNIQUE INDEX customers_email ON customers (email);
CREATE UNIQUE INDEX orders_pkey ON orders (id);
CREATE INDEX orders_idx_orders_customer ON orders (customer_id);
CREATE INDEX orders_idx_orders_status_date ON orders (status, ordered_at);
ALTER TABLE customers ADD CONSTRAINT customers_pkey PRIMARY KEY USING INDEX customers_pkey;
ALTER TABLE customers ADD CONSTRAINT customers_email UNIQUE USING INDEX customers_email;
ALTER TABLE orders ADD CONSTRAINT orders_pkey PRIMARY KEY USING INDEX orders_pkey;
ALTER TABLE orders ADD CONSTRAINT orders_fkey FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE NOT VALID;
DO $f$ BEGIN
    ALTER TABLE orders VALIDATE CONSTRAINT orders_fkey;
EXCEPTION WHEN foreign_key_violation THEN
    RAISE WARNING 'foreign key % has rows without a parent: it stays NOT VALID, and new rows are checked', 'orders_fkey';
END $f$;
SELECT setval(pg_get_serial_sequence('customers', 'id'), LEAST(GREATEST(COALESCE((SELECT max(id) FROM customers)::numeric, 0) + 1, 9), 9223372036854775807)::bigint, false);
SELECT setval(pg_get_serial_sequence('orders', 'id'), LEAST(GREATEST(COALESCE((SELECT max(id) FROM orders)::numeric, 0) + 1, 11), 9223372036854775807)::bigint, false);

Исправления

Исправлять ничего не пришлось.

Требует проверки

1 текстовый столбец сравнивает значения без учета регистра в источнике; PostgreSQL сравнивает точно, поэтому уникальные ключи и поиск могут работать иначе. Используйте сопоставление или тип столбца без учета регистра для столбцов, которым это нужно.
Не конвертированы представления, триггеры, процедуры и события (2 объекта); отчет перечисляет каждый. Создайте их заново вручную в PostgreSQL.

Файл конвертируется на этом компьютере и никуда не загружается.

Другие примеры:

Что меняется при переходе с SQLite на PostgreSQL

Каждая строка - это правило, которое применяет конвертер, и строка отчета, которую он пишет, когда правило что-то меняет в ваших данных.

declared types (any text: VARCHAR(255), DATETIME, BOOLEAN, JSON or none)
тип, в который помещается каждое значение столбца
SQLite хранит любое значение в любом столбце, поэтому объявленный тип - лишь подсказка: каждый столбец получает тип, в который помещаются все его значения, а столбец со смешанными значениями сохраняет их текст.
BOOLEAN, BOOL
boolean
Если каждое значение равно 0 или 1; столбец, где есть и другие значения, сохраняет их как числа или текст.
TEXT, VARCHAR(n), CHAR(n), CLOB
varchar(n), если каждое значение помещается в объявленную длину, иначе text
SQLite никогда не ограничивает длину; объявленная длина сохраняется, только если в нее помещается каждое значение.
DECIMAL(p,s), NUMERIC
точный тип, если в него помещается каждое значение, иначе double
SQLite хранит их как целые числа или double; объявленная точность сохраняется, только если значения в нее помещаются.
BLOB, and columns holding bytes
bytea
Байты остаются байтами; текст и числа в том же столбце сохраняются как их байты.
DATE, DATETIME, TIMESTAMP holding ISO 8601 text
date, timestamp
Если каждое значение - дата или дата со временем; числа epoch и другой текст сохраняют свои значения как есть.
JSON holding JSON text
jsonb
Если каждое значение - объект или массив JSON.
Infinity in REAL columns
Infinity, которую double в PostgreSQL сохраняет
double в PostgreSQL вмещает бесконечность.
INTEGER PRIMARY KEY, AUTOINCREMENT
GENERATED BY DEFAULT AS IDENTITY, продолжается после наибольшего id или значения sqlite_sequence
Только столбец, объявленный ровно как INTEGER PRIMARY KEY, является rowid; каждая строка сохраняет свой id, а счетчик начинается после наибольшего когда-либо выданного id.
COLLATE NOCASE
точное сравнение
PostgreSQL сравнивает текст точно, если не указано иное; столбцы NOCASE перестают игнорировать регистр.
Строка отчета: case_insensitive_collation
generated columns
STORED
PostgreSQL с 13 по 17 поддерживает только хранимые генерируемые столбцы; формула та же.
Строка отчета: virtual_as_stored
generated columns with functions IntoSQL doesn't write, and the || operator
обычный столбец
Формула, которую нельзя записать, оставляет обычный столбец и строку в отчете; в дампе нет значений для генерируемых столбцов.
Строка отчета: generated_not_translated
defaults with functions IntoSQL doesn't write (strftime, randomblob)
без значения по умолчанию
Значение по умолчанию, которое нельзя записать, опускается и попадает в отчет; CURRENT_TIMESTAMP и datetime('now') записываются.
Строка отчета: default_not_translated
CHECK constraints
переводится
Те же проверки, применяемые при загрузке каждой строки.
CHECK constraints with functions IntoSQL doesn't write
не создается
Проверка, которую нельзя записать, опускается и попадает в отчет.
Строка отчета: check_skipped
partial and expression indexes
не создается
IntoSQL создает индексы по целым столбцам; остальные создайте заново после загрузки.
Строка отчета: index_skipped
foreign keys (unenforced unless PRAGMA foreign_keys=ON)
внешние ключи, NOT VALID, затем валидация
SQLite проверяет внешние ключи, только если это включено, поэтому строки без родителя встречаются часто: они загружаются, а ключ остается непроверенным с предупреждением, вместо того чтобы остановить загрузку.
foreign keys to tables the dump doesn't create
не создается
Ключ не может ссылаться на таблицу, которой нет.
Строка отчета: foreign_key_skipped
primary keys holding NULL, and NOT NULL columns with values that become NULL
NOT NULL снимается
SQLite разрешает первичному ключу, кроме rowid, содержать NULL; в целевой базе столбец не может быть NOT NULL, иначе загрузка остановится.
Строка отчета: not_null_relaxed
views, triggers and virtual tables (FTS, R*Tree)
перечисляются в отчете
Их код написан на собственном языке SQLite, а виртуальным таблицам нужны модули SQLite; они перечислены, чтобы вы могли создать их заново. Их внутренние таблицы не копируются.
Строка отчета: not_converted_objects
dumps without CREATE TABLE (--data-only)
типы определяются по значениям
Дамп только с данными содержит лишь строки, поэтому типы берутся из значений, а столбцы нумеруются.
Строка отчета: no_schema
expressions in INSERT values other than the text functions dumps write
NULL
unistr(), replace(), char() и || вычисляются в том виде, в каком их пишет оболочка; другие выражения нельзя вычислить без базы данных.
Строка отчета: expression_value
NUL characters in text
удаляется
Оболочка sqlite3 и Python обрезают текст на символе NUL при создании дампа, поэтому в дампе есть только то, что стоит перед ним. text в PostgreSQL тоже не может его содержать.
Строка отчета: nul_removed
text that isn't valid UTF-8
неверные байты заменяются символом замены
Дампы записывают такой текст как сырые байты; скрипт в UTF-8.
Строка отчета: invalid_utf8_replaced
text decoded twice (Ã±)
копируется как есть
Дамп содержит именно эти символы, поэтому они копируются; отчет на них указывает.
Строка отчета: mojibake_suspected

В SQLite	В PostgreSQL	Строка отчета
declared types (any text: VARCHAR(255), DATETIME, BOOLEAN, JSON or none)	тип, в который помещается каждое значение столбцаSQLite хранит любое значение в любом столбце, поэтому объявленный тип - лишь подсказка: каждый столбец получает тип, в который помещаются все его значения, а столбец со смешанными значениями сохраняет их текст.
BOOLEAN, BOOL	booleanЕсли каждое значение равно 0 или 1; столбец, где есть и другие значения, сохраняет их как числа или текст.
TEXT, VARCHAR(n), CHAR(n), CLOB	varchar(n), если каждое значение помещается в объявленную длину, иначе textSQLite никогда не ограничивает длину; объявленная длина сохраняется, только если в нее помещается каждое значение.
DECIMAL(p,s), NUMERIC	точный тип, если в него помещается каждое значение, иначе doubleSQLite хранит их как целые числа или double; объявленная точность сохраняется, только если значения в нее помещаются.
BLOB, and columns holding bytes	byteaБайты остаются байтами; текст и числа в том же столбце сохраняются как их байты.
DATE, DATETIME, TIMESTAMP holding ISO 8601 text	date, timestampЕсли каждое значение - дата или дата со временем; числа epoch и другой текст сохраняют свои значения как есть.
JSON holding JSON text	jsonbЕсли каждое значение - объект или массив JSON.
Infinity in REAL columns	Infinity, которую double в PostgreSQL сохраняетdouble в PostgreSQL вмещает бесконечность.
INTEGER PRIMARY KEY, AUTOINCREMENT	GENERATED BY DEFAULT AS IDENTITY, продолжается после наибольшего id или значения sqlite_sequenceТолько столбец, объявленный ровно как INTEGER PRIMARY KEY, является rowid; каждая строка сохраняет свой id, а счетчик начинается после наибольшего когда-либо выданного id.
COLLATE NOCASE	точное сравнениеPostgreSQL сравнивает текст точно, если не указано иное; столбцы NOCASE перестают игнорировать регистр.	case_insensitive_collation
generated columns	STOREDPostgreSQL с 13 по 17 поддерживает только хранимые генерируемые столбцы; формула та же.	virtual_as_stored
generated columns with functions IntoSQL doesn't write, and the \|\| operator	обычный столбецФормула, которую нельзя записать, оставляет обычный столбец и строку в отчете; в дампе нет значений для генерируемых столбцов.	generated_not_translated
defaults with functions IntoSQL doesn't write (strftime, randomblob)	без значения по умолчаниюЗначение по умолчанию, которое нельзя записать, опускается и попадает в отчет; CURRENT_TIMESTAMP и datetime('now') записываются.	default_not_translated
CHECK constraints	переводитсяТе же проверки, применяемые при загрузке каждой строки.
CHECK constraints with functions IntoSQL doesn't write	не создаетсяПроверка, которую нельзя записать, опускается и попадает в отчет.	check_skipped
partial and expression indexes	не создаетсяIntoSQL создает индексы по целым столбцам; остальные создайте заново после загрузки.	index_skipped
foreign keys (unenforced unless PRAGMA foreign_keys=ON)	внешние ключи, NOT VALID, затем валидацияSQLite проверяет внешние ключи, только если это включено, поэтому строки без родителя встречаются часто: они загружаются, а ключ остается непроверенным с предупреждением, вместо того чтобы остановить загрузку.
foreign keys to tables the dump doesn't create	не создаетсяКлюч не может ссылаться на таблицу, которой нет.	foreign_key_skipped
primary keys holding NULL, and NOT NULL columns with values that become NULL	NOT NULL снимаетсяSQLite разрешает первичному ключу, кроме rowid, содержать NULL; в целевой базе столбец не может быть NOT NULL, иначе загрузка остановится.	not_null_relaxed
views, triggers and virtual tables (FTS, R*Tree)	перечисляются в отчетеИх код написан на собственном языке SQLite, а виртуальным таблицам нужны модули SQLite; они перечислены, чтобы вы могли создать их заново. Их внутренние таблицы не копируются.	not_converted_objects
dumps without CREATE TABLE (--data-only)	типы определяются по значениямДамп только с данными содержит лишь строки, поэтому типы берутся из значений, а столбцы нумеруются.	no_schema
expressions in INSERT values other than the text functions dumps write	NULLunistr(), replace(), char() и \|\| вычисляются в том виде, в каком их пишет оболочка; другие выражения нельзя вычислить без базы данных.	expression_value
NUL characters in text	удаляетсяОболочка sqlite3 и Python обрезают текст на символе NUL при создании дампа, поэтому в дампе есть только то, что стоит перед ним. text в PostgreSQL тоже не может его содержать.	nul_removed
text that isn't valid UTF-8	неверные байты заменяются символом заменыДампы записывают такой текст как сырые байты; скрипт в UTF-8.	invalid_utf8_replaced
text decoded twice (Ã±)	копируется как естьДамп содержит именно эти символы, поэтому они копируются; отчет на них указывает.	mojibake_suspected

Загрузите скрипт в PostgreSQL

Скрипт создает таблицы, копирует строки, затем добавляет ключи, индексы и внешние ключи, поэтому загрузка идет быстро и останавливается на первой ошибке.

$psql -v ON_ERROR_STOP=1 -d mydb -f sqlite.postgresql.sql

Внешние ключи добавляются как NOT VALID, а затем проверяются. Если строка ссылается на несуществующую строку, скрипт сообщает, какое ограничение нарушено, а не прерывается на полпути.

Для большого дампа сначала создайте базу данных и запустите скрипт через psql на той же машине, где работает сервер.

IntoSQL Pro загружает файл прямо в PostgreSQL из приложения для компьютера или командной строки, без написания скрипта.

Как создать дамп SQLite для лучшего результата

Конвертер читает то, что есть в дампе. Эти параметры сохраняют в нем больше ваших данных:

Сам файл базы данных конвертируется как есть и читается в браузере страница за страницей. Сначала закройте приложение, которое в него пишет, чтобы ни одно изменение не осталось в его файле -wal.
sqlite3 app.db .dump > app.sql записывает ту же базу данных в SQL, который тоже конвертируется, в формате .sql или .sql.gz.
DB Browser for SQLite пишет этот SQL через File, Export, Database to SQL file, как и iterdump() в Python.

Что не конвертируется

Представления и триггеры написаны на собственном языке SQLite. Они перечислены в отчете по имени и никогда не удаляются молча, чтобы вы могли переписать их для PostgreSQL. Виртуальные таблицы, такие как FTS5 и R*Tree, тоже перечислены, а их служебные таблицы пропускаются.

Вопросы о SQLite в PostgreSQL

Нет ответа на ваш вопрос? Напишите нам через страницу контактов, и вам ответит человек. Страница контактов

Мой дамп загружается на сервер?

Нет. Конвертер работает в вашем браузере и читает дамп с диска по частям, поэтому он никогда не покидает ваш компьютер. Приложение для компьютера и командная строка тоже конвертируют на вашем компьютере.

Насколько большим может быть дамп?

Конвертер никогда не держит весь дамп в памяти: он читает файл дважды, по диапазонам, и пишет скрипт по ходу чтения. Дампы в несколько гигабайт конвертируются в браузере, в приложении для компьютера и в командной строке.

Можно ли конвертировать сам файл .db?

Да. Перетащите файл .db, .sqlite или .sqlite3: он читается в браузере и никуда не загружается. Текст с символами NUL и все цифры значения double приходят такими, как хранятся, а .dump может их обрезать.

Из каких версий SQLite может быть дамп?

Файлы баз данных SQLite начиная с 3.0, в UTF-8 или UTF-16, и дампы из .dump оболочки sqlite3 или из iterdump() в Python. Каждый тестовый файл и дамп создан в sqlite3 с 3.31 по 3.54, включая таблицы STRICT и WITHOUT ROWID, и сверен с источником ячейка за ячейкой.

Как определяются типы столбцов, если SQLite их не соблюдает?

Объявленный тип - лишь подсказка, а решают значения: столбец становится integer, numeric, double precision, date, timestamp, boolean, bytea или text, если в него помещается каждое значение. Столбец со смешанными значениями сохраняет их текст, а отчет указывает, какие столбцы.

Сохраняются ли INTEGER PRIMARY KEY и AUTOINCREMENT?

Да. INTEGER PRIMARY KEY становится столбцом identity, каждая строка сохраняет свой id, а следующий id идет после наибольшего или, при AUTOINCREMENT, после значения sqlite_sequence.

А представления и триггеры?

Они перечислены в отчете по имени и не конвертируются, потому что каждая база данных пишет их на своем языке.

На каких версиях PostgreSQL загружается скрипт?

PostgreSQL с 13 по 18. Один и тот же скрипт загружается на каждой из них и останавливается на первой ошибке, если что-то пойдет не так.

id	bigint not null
name	text not null
email	text not null
country	text not null
is_active	boolean not null
signed_up	date
created_at	timestamp(0) not null