database - full - Convierta el archivo de volcado de SQL SQLITE a POSTGRESQL

sql_mode postgresql

Question

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 ejecutar la 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?

Answer 1

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 descubrió 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 *.deb y creé un archivo de 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 de los docs . Luego creé un testdb con createdb :

createdb testdb

pgloader comando pgloader así:

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, testdb este testdb e testdb en un entorno de desarrollo en un servidor de producción y los datos se transfirieron muy bien.

Answer 2

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

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

Si desea que la columna de id "aumente automáticamente", cambie su tipo de "int" a "serie" 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á los comandos AUTOINCREMENT , por lo que estos deben eliminarse.

También querrá comprobar las columnas de datetime y datetime en el esquema de SQLite y cambiarlas por timestamp para PostgreSQL (gracias a Clay por señalar esto).

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

Si tiene BLOB en su SQLite, querrá ajustar el esquema para usar bytea . Probablemente necesites mezclar algunas llamadas decode 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 las set constraints all deferred para evitar problemas de ordenamiento de inserción, colocando el comando dentro del par COMIENZO / COMPROMISO.

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

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

PostGRESQL tampoco reconoce columnas unsigned , 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 los 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á.

Answer 3

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

https://gist.github.com/2253099

Answer 4

La gema de la 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

Answer 5

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