Convertir SQLITE SQL dump file a POSTGRESQL

Question

Convertir SQLITE SQL dump file a POSTGRESQL

He estado haciendo desarrollo usando la base de datos SQLITE con producción en POSTGRESQL. Acabo de actualizar mi base de datos local con una gran cantidad de datos y necesito transferir una tabla específica a la base de datos de producción.

Basado en la ejecución de sqlite database .dump > /the/path/to/sqlite-dumpfile.sql, SQLITE genera un volcado de tabla en el siguiente formato:

BEGIN TRANSACTION;
CREATE TABLE "courses_school" ("id" integer PRIMARY KEY, "department_count" integer NOT NULL DEFAULT 0, "the_id" integer UNIQUE, "school_name" varchar(150), "slug" varchar(50));
INSERT INTO "courses_school" VALUES(1,168,213,'TEST Name A',NULL);
INSERT INTO "courses_school" VALUES(2,0,656,'TEST Name B',NULL);
....
COMMIT;

¿Cómo convertir lo anterior en un archivo de volcado compatible con POSTGRESQL que pueda importar a mi servidor de producción?

76

postgresql database sqlite migration

Author: Mike Pennington, 2011-01-03

Source

6 answers

`pgloader`

Me encontré con este post al buscar una manera de convertir un volcado SQLite a PostgreSQL. A pesar de que este post tiene una respuesta aceptada (y una buena en eso +1), creo que agregar esto es importante.

Comencé a buscar soluciones aquí y me di cuenta de que estaba buscando un método más automatizado. Busqué en el wiki docs:

Https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL

Y descubierto pgloader. Genial aplicación y es relativamente fácil de usar. Puede convertir el archivo SQLite plano en una base de datos PostgreSQL utilizable. Instalé desde *.deb y creé un archivo command como este en un directorio de prueba:

load database  
    from 'db.sqlite3'  
    into postgresql:///testdb 

with include drop, create tables, create indexes, reset sequences  

set work_mem to '16MB', maintenance_work_mem to '512 MB';

Como el estado docs. Entonces creé un testdb con createdb:

createdb testdb

Corrí el pgloader comando como este:

pgloader command

Y luego conectado a la nueva base de datos:

psql testdb

Después de algunas consultas para verificar los datos, parece que funcionó bastante bien. Sé que si hubiera intentado ejecutar uno de estos scripts o hacer la conversión paso a paso mencionada aquí, habría pasado mucho más tiempo.

Para probar el concepto, arrojé esto testdb e importé a un entorno de desarrollo en un servidor de producción y los datos se transfirieron muy bien.

46

Author: nicorellius,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-05-15 15:15:14

Escribí un script para hacer la migración de sqlite3 a postgres. No maneja todas las traducciones de esquemas/datos mencionadas en https://stackoverflow.com/a/4581921/1303625 , pero hace lo que necesitaba que hiciera. Esperemos que sea un buen punto de partida para otros.

Https://gist.github.com/2253099

15

Author: Earle Clubb,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-23 12:10:41

La gema secuela (una biblioteca Ruby) ofrece copia de datos a través de diferentes bases de datos: http://sequel.jeremyevans.net/rdoc/files/doc/bin_sequel_rdoc.html#label-Copy + Bases de datos

En el caso de sqlite, sería así: sequel -C sqlite://db/production.sqlite3 postgres://user@localhost/db

10

Author: lulalala,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-10-26 01:08:32

Puede usar un solo trazador de líneas, aquí hay un ejemplo con la ayuda del comando sed:

sqlite3 mjsqlite.db .dump | sed -e 's/INTEGER PRIMARY KEY AUTOINCREMENT/SERIAL PRIMARY KEY/' | sed -e 's/PRAGMA foreign_keys=OFF;//' | sed -e 's/unsigned big int/BIGINT/g' | sed -e 's/UNSIGNED BIG INT/BIGINT/g' | sed -e 's/BIG INT/BIGINT/g' | sed -e 's/UNSIGNED INT(10)/BIGINT/' | sed -e 's/BOOLEAN/SMALLINT/g' | sed -e 's/boolean/SMALLINT/g' | sed -e 's/UNSIGNED BIG INT/INTEGER/g' | sed -e 's/INT(3)/INT2/g' | sed -e 's/DATETIME/TIMESTAMP/g' | psql mypqdb mypguser

8

Author: develCuy,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-05-19 07:14:09

Pgloader hace maravillas al convertir la base de datos en sqlite a postgresql.

Aquí hay un ejemplo sobre la conversión de un sqlitedb local a una base de datos PostgreSQL remota:

Pgloader sqlite.db postgresql://nombre de usuario:contraseña@nombre de host/dbname

0

Author: kouichi,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-09-04 22:00:30

score 82 · Accepted Answer

Debería poder alimentar ese archivo de volcado directamente a psql:

/path/to/psql -d database -U username -W < /the/path/to/sqlite-dumpfile.sql

Si desea que la columna id sea "auto increment", cambie su tipo de "int" a "serial" en la línea de creación de la tabla. PostgreSQL luego adjuntará una secuencia a esa columna para que los insertos con ID NULOS se asignen automáticamente al siguiente valor disponible. PostgreSQL tampoco reconocerá los comandos AUTOINCREMENT, por lo que estos deben eliminarse.

También querrá verificar las columnas datetime en el Esquema SQLite y cambiarlos a timestamp para PostgreSQL (gracias a Clay por señalar esto).

Si tiene booleanos en su SQLite, entonces podría convertir 1 y 0 y 1::boolean y 0::boolean (respectivamente) o podría cambiar la columna booleana a un entero en la sección esquema del volcado y luego arreglarlos a mano dentro de PostgreSQL después de la importación.

Si tiene BLOBs en su SQLite, entonces querrá ajustar el esquema para usar bytea. Probablemente necesidad de mezclar en algunos decode también llama. Escribir una copiadora quick'n'dirty en tu idioma favorito podría ser más fácil que manipular el SQL si tienes que lidiar con muchos BLOBs.

Como de costumbre, si tiene claves foráneas, probablemente querrá buscar en set constraints all deferred para evitar problemas de orden de inserción, coloque el comando dentro del par BEGIN/COMMIT.

Gracias a Nicolas Riley por las notas booleanas, blob y constraints.

Si tenga ` en su código, tal como lo generan algunos clientes SQLite3, debe eliminarlos.

PostgreSQL tampoco reconoce las columnas unsigned, es posible que desee eliminar eso o agregar una restricción personalizada como esta:

CREATE TABLE tablename (
    ...
    unsigned_column_name integer CHECK (unsigned_column_name > 0)
);

Mientras que SQLite establece por defecto valores nulos en '', PostgreSQL requiere que se establezcan como NULL.

La sintaxis en el archivo de volcado SQLite parece ser principalmente compatible con PostgreSQL, por lo que puede parchear algunas cosas y alimentarlo a psql. Importación de un gran pila de datos a través de inserciones SQL podría tomar un tiempo, pero va a funcionar.