Grimpi IT Blog

abril 22, 2011

Como migrar una base de datos de SQL Server a PostgreSQL

Filed under: Migrador, PostgreSQL, SQL Server — grimpi @ 11:20 pm

Hace poco tuve que migrar varias bases de datos de SQL Server a PostgreSQL 8.4. Si bien existen varias maneras de hacer esto, no encontré ninguna herramienta fiable y gratis que me permita realizar esta operación. Por supuesto que si estamos dispuestos a pagar, existen utilidades de conversión de bases de datos y algunas muy buenas. Pero gratis, nada que funcione decentemente. Por lo tanto, aprovechando bastante código que ya tenía escrito para otra cosa, decidí hacer mi propio conversor de bases de datos de SQL Server 2005/2008 a PostgreSQL 8.4, que subo para que cualquiera la pueda probar y ahorrarse tiempo y dinero. La aplicación es muy sencilla: ingresamos el connection string de la base de datos de SQL Server y presionamos el botón “Generar Script”. Esto va a generar un codigo SQL en la caja de texto de la aplicación que deberemos copiar y ejecutarlo en algún lugar.

Qué cosas migra este utilitario?
• Schemas
• Tablas
• Constraints (Primary Keys, Unique Keys, Foreign Keys)
• Indices
• Tipos de datos (Domains en PostgreSQL)
• Vistas (Si el código SQL utilizado es compatible con PostgreSQL, no vamos a tener problemas, de lo contrario, tendremos que modificarlo manualmente para hacerlo compatible).
• Comentarios (Extended Properties en SQL Server)

Qué cosas no migra?
• Datos (para eso podemos utilizar algún ETL o SSIS)
• Store procedures/funciones/triggers (La aplicación sin embargo, nos permite generar el script de estos objetos, para que luego los modifiquemos manualmente).

Adicionalmente, este utilitario agrega un par de funciones que existen en SQL Server y no en PostgreSQL como por ejemplo “newid()”, cuando usamos columnas tipo ROWGUID en SQL Server.
También se genera un trigger automáticamente, cuando existe una columna Computed, ya que esta feature no existe en PostgreSQL. Tiene una mínima inteligencia en algunos aspectos, que nos pueden ahorrar mas tiempo todavía. Por ejemplo, cuando encuentra que se llama a la función GetDate(), se reemplaza por su homologa en PostgreSQL que es now(). Lo mismo hago para DatePart y un par de funciones mas.

Esta aplicación es muy básica, pero potente. No garantizo que funcione en todos los escenarios ni que sea infalible. Pero me ha servido para convertir bases de datos muy complejas de manera muy rapida y creo que puede servir a muchos.

La aplicación se puede bajar de esta URL.

Toda sugerencia, queja, critica sera bievenida.

febrero 8, 2011

Control de concurrencia multiversión MVCC

Filed under: Engine, MVCC, PostgreSQL, SQL Server — grimpi @ 8:08 pm

Antes de explicar que es MVCC, hay que aclarar dos conceptos muy importantes: bloqueo pesimista y bloqueo optimista. El bloqueo optimista supone que no se va a hacer nada en el código de la aplicación que imponga explícitamente bloqueos de los recursos cuando se esté trabajando con ellos. Mientras que por otro lado, el bloqueo pesimista supone una intervención por parte de la aplicación como gestor del bloqueo.
Para ser más simples: El enfoque optimista delega en la base de datos el bloqueo y manejo de datos, mientras que el bloqueo pesimista, la aplicación es la encargada de gestionar la concurrencia.
MVCC (Multi version concurrency control) es una técnica de concurrencia optimista en donde ninguna tarea o hilo es bloqueado mientras se realiza una operación en la tabla, porque el otro hilo usa su propia copia (versión) del objeto dentro de una transacción.
Si bien obviamente la implementación interna de este algoritmo es distinta en cada motor de bases de datos, a grandes rasgos se podría decir que el MVCC internamente lo que hace es identificar cada transacción con un numero univoco y a cada registro de la tabla, con un numero de versión. Entonces, cada transacción trabaja con su copia y si se modifica un registro de una tabla, el contador de versión del registro se incrementa. Cuando se comitea, la copia del objeto reemplaza a la que existía en la base de datos. Si 2 transacciones modificaron la misma tabla, se hace un mergue de ambas tablas combinando las últimas versiones de cada registro. Si las 2 transacciones, modificaron exactamente el mismo registro, entonces en ese caso, cuando se commitee, el registro que finalmente queda, corresponde a la ultima transacción en realizar una modificación sobre ese registro.
De esta manera se logra que una lectura no bloquee una transacción de escritura y que una transacción de escritura tampoco bloquee una transacción de lectura. Para ser más claros, cuando hago un SELECT, puedo simultáneamente hacer un UPDATE y cuando hago un UPDATE puedo hacer simultáneamente un SELECT, algo que en el isolation habitual de las bases de datos, no es posible.
Al no existir ningún lockeo ni espera por parte del proceso, en determinadas situaciones concurrentes de fuerte escritura donde la información es modificada frecuentemente y de manera concurrente por muchos usuarios, se logran un gran mejoramiento de la performance del sistema. Es muy habitual usar este tipo modelo de concurrencia para evitar los famosos “deadlocks” que suelen ocurrir en bases de datos con mucha demanda.

Sin embargo, MVCC no debe ser usado en cualquier ocasión, ya que tiene un overhead muy importante. Todo el manejo de versionado tiene un alto costo, ya que las versiones de registros se copian y almacenan en tablas o estructuras físicas temporales que luego se descartan (por ejemplo, para guardar las copias, SQL Server usa la tempdb, mientras que Oracle usa el rollback segment). En SQL Server este overhead es un poco alto, por lo tanto solo en escenarios específicos conviene usar este modelo de concurrencia. A medida que las operaciones de escritura sobre la base de datos cobran relevancia sobre las operaciones de lectura, y por otro lado, nuestras lecturas son largas, los beneficios de usar MVCC aumentan, ya que reducimos los bloqueos.
Es fundamental la existencia de un alto grado de paralelismo en nuestra aplicación. MVCC es por definición, un modelo muy escalable y donde mejor se ven sus ventajas, es en escenarios de alta demanda.

Escenarios típicos:
• Aplicaciones que procesan transacciones en línea en gran escala.
• Aplicaciones mixtas que usan reportes contra datos sobre tablas en línea.
• Aplicaciones donde se ejecutan consultas largas mientras que simultaneamente se realizan operaciones sobre ella.

Escenarios donde no generalmente no debería usarse MVCC:
• Aplicaciones de datawarehousing que extraen información sobre tablas históricas donde no hay escritura.
• Aplicaciones que procesan datos en línea con poca concurrencia.
• Aplicaciones realtime, donde la velocidad es mas importante aun que la consistencia de información.

Todas las bases de datos modernas implementan actualmente este algoritmo de concurrencia optimista. En SQL Server se llama SNAPSHOT ISOLATION, en MySql se llama InnoDB Multi Version, en Firebird MGA y en Oracle y PostgreSQL, simplemente MVCC.

Links:
http://msdn.microsoft.com/en-us/library/ms345124(v=sql.90).aspx
http://www.codinghorror.com/blog/2008/08/deadlocked.html
http://www.rtcmagazine.com/articles/view/101612

enero 8, 2011

Desglosando la metada en PostgreSQL 8.2 o superior

Filed under: Metada, PostgreSQL — grimpi @ 2:59 pm

Después de haber probado como extraer la información del schema de todas las bases de datos del mercado, debo decir que por lejos, SQL Server tiene el método mas sencillo y potente de acceso a su metada y PostgreSQL el método mas entrincado y críptico. No me refiero a traer un simple listado de tablas, sino a cosas más complejas, como por ejemplo traer el detalle de las constraints o de los índices.

Traer todas las tablas de una base de datos:

SELECT c.* FROM pg_class c
INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = ‘r’ and pg_catalog.pg_table_is_visible(c.oid)
and n.nspname NOT IN (‘pg_catalog’, ‘pg_toast’)

Con esto le decimos que nos traiga todos los objetos de la base de tipo tabla que no sean invisibles y que no pertenezcan a los schemas pg_catalog y pg_toast, que son schemas internos de PostgreSQL.
El campo relkind determina qué tipo de objeto queremos buscar. Esta es la tabla de equivalencias:

r Table
S Sequence
i Index
v View
c Composite Type


Traer todas las primary key con sus columnas por tabla de una base de datos:

SELECT cc.relname as tabla, cc.oid as cid, a.attnum, c.conname as Name, a.attname, c.oid as ID FROM pg_constraint c, pg_class cc, pg_attribute a
WHERE contype = ‘p’
and cc.oid = c.conrelid
and a.attrelid = cc.oid
and a.attnum = ANY(c.conkey)
order by cc.oid, c.oid

La table pg_constraint nos trae todas las constraints y el campo contype el tipo de constraint. Esta es la tabla de equivalencias:

p Primary Key
u Unique Key
f Foreign Key
c Check Constraint
t Trigger Constraint
X Exclusive Constraint


Traer el listado de permisos de un objeto:

Dentro de la tabla pg_class, existe un campo vectorial llamado relacl, que contiene una lista de todos los permisos del objeto.

SELECT relacl FROM pg_class

Este campo devuelve un valor similar a este: “{=awdxt/postgres,postgres=awdxt/postgres,crm=arw*d*x*t*/postgres}”. Que significa este resultado?
En esa cadena de string aparentemente inentendible, tenemos toda la información sobre los permisos del objeto.
Recordemos que este campo es un vector, por lo tanto, cada carácter “,” significa un nuevo ítem en el vector. Por lo tanto, este resultado de ejemplo, tiene 3 ítems. Cada posición del vector, corresponde a un usuario/grupo distinto con permisos en el objeto.
Cada ítem del vector, tiene 3 datos: Usuario, Permisos y Owner del objeto.
Ejemplo: “crm=arw*d*x*t*/postgres”
En este caso, el usuario es crm (es el valor que esta antes del “=”), los permisos son “arw*d*x*t*” y el owner es postgres.
Ahora, que significa “arw*d*x*t*” realmente? Simple: cada uno de estos caracteres, es una operación posible (INSERT, DELETE, etc) sobre el objeto.
Esta es la tabla de equivalencias:

“a” INSERT
“r” SELECT
“w” UPDATE
“d” DELETE
“x” REFERENCES
“R” RULE
“t” TRIGGER
“X” EXECUTE
“U” ALTER
“c” CONNECT
“C” CREATE
“*” HABILITA LA OPCION GRAN OPTION A CADA OPERACION

Por lo tanto, este objeto tiene permisos de INSERT, SELECT, UPDATE, DELETE, TRIGGER y REFERENCES, de las cuales para las operaciones UPDATE, DELETE, REFERENCES y TRIGGER, la opción WITH GRAN OPTION esta activada.

=awdxt/postgres GRANT INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLE “Nombre de la tabla” TO public;

(si no aparece un nombre usuario, significa que el permiso pertenece al rol “public”)

postgres=awdxt/postgres GRANT INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLE “Nombre de la tabla” TO postgres;

 

crm=arw*d*x*t*/postgres GRANT INSERT, SELECT ON TABLE “Nombre de la tabla” TO “crm”;

GRANT UPDATE, DELETE, REFERENCES, TRIGGER ON TABLE “Nombre de la tabla” TO “crm” WITH GRANT OPTION;

 



Traer todos los sequences de la base de datos

SELECT * FROM pg_class c where relkind = ‘S’

El problema de esta query, es que nos trae solo el nombre del sequence, pero no el detalle del mismo (valor máximo y mínimo, valor actual, etc.). Si solo queremos el nombre, perfecto, pero si necesitamos mas, la tabla pg_class no nos sirve.
Para traer entonces el detalle de la información de un sequence simplemente tenemos que hacer un SELECT sobre el mismo como si fuera una vista o una tabla.

SELECT * FROM “nombre_del_sequence”

sequence_name last_value start_value increment_by max_value min_value cache_value is_cycled
nombre_del_sequence 1 1 1 922337203685 1 1 f

Dejo en este link algunos scripts de ejemplo de para extraer la metadata de distintos objetos de PostgreSQL.

Blog de WordPress.com.