Pregunta Convierta el archivo de volcado de SQL SQLITE a POSTGRESQL


He estado desarrollando usando una 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 correr 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 convierto lo anterior en un archivo de volcado compatible con POSTGRESQL que puedo importar a mi servidor de producción?


75
2018-01-03 02:33


origen


Respuestas:


Deberías poder alimentar ese archivo de volcado directamente en psql:

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

Si quieres el id para "autoincrementar" y luego cambiar 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 INSERT con identificadores NULL se le asigne automáticamente el siguiente valor disponible. PostgreSQL tampoco reconocerá AUTOINCREMENT comandos, por lo que estos deben eliminarse.

También querrás verificar datetime columnas en el esquema SQLite y cambiarlos a timestamp para PostgreSQL (gracias a Arcilla para señalar esto).

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

Si tiene BLOB en su SQLite, entonces querrá ajustar el esquema para usar bytea. Es probable que necesites mezclar algunos decode llamadas también. Escribir una copia rápida en tu idioma favorito puede ser más fácil que modificar el SQL si tienes que lidiar con muchos BLOB.

Como es habitual, si tiene claves externas, probablemente querrá examinar set constraints all deferred para evitar problemas de ordenamiento de inserción, colocando el comando dentro del par BEGIN / COMMIT.

Gracias a Nicolas Riley para las notas booleanas, blob y restricciones.

Si usted tiene ` en su código, como generado por algunos clientes SQLite3, necesita eliminarlos.

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

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

Mientras SQLite predetermina valores nulos a '', PostgreSQL requiere que se establezcan como NULL.

La sintaxis en el archivo de volcado SQLite parece ser en su mayoría compatible con PostgreSQL, por lo que puede parchar algunas cosas y alimentarlo a psql. La importación de una gran pila de datos a través de SQL INSERT puede llevar un tiempo, pero funcionará.


81
2018-05-30 09:30



pgloader

Me encontré con esta publicación cuando buscaba una forma de convertir un volcado de SQLite a PostgreSQL. Aunque esta publicación tiene una respuesta aceptada (y una buena en ese +1), creo que agregar esto es importante.

Empecé a buscar soluciones aquí y me di cuenta de que estaba buscando un método más automatizado. Busqué en los documentos de la wiki:

https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL

y descubierto pgloader. Aplicación bastante genial y es relativamente fácil de usar. Puede convertir el archivo SQLite plano en una base de datos PostgreSQL utilizable. Instalé desde el *.deby creó un command archivo 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 documentos estado. Luego 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 por pasos aquí mencionada, habría pasado mucho más tiempo.

Para probar el concepto, descargué esto testdb e importado a un entorno de desarrollo en un servidor de producción y los datos transferidos de una manera agradable.


42
2018-03-30 17:21



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

https://gist.github.com/2253099


15
2017-07-20 16:16



los gema secuela (una biblioteca de Ruby) ofrece copia de datos en diferentes bases de datos: http://sequel.jeremyevans.net/rdoc/files/doc/bin_sequel_rdoc.html#label-Copy+Databases

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


10
2017-09-18 23:30



Puede usar un 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