Migración de Microsoft SQL Server a PostgreSQL por Ian Harding

por Ian A. Harding <ianh@tpchd.org> v1.00, Última actualización 17 de Septiembre del 2001

Cómo mover una base de datos desde uno de las más populares gestores de bases de datos propietarios al gestor de código abierto más poderoso del mundo.

Resumen

El siguiente documento se ofrece de buena fé para la comprensión sólo de la programación segura y los procedimientos. Ninguna responsabilidad es aceptada por el autor por alguna pérdida o daño causado en alguna medida a alguna persona o equipo, como una consecuencia directa o indirecta del seguimiento de estas instrucciones.

Introducción

Microsoft SQL Server es un sistema gestor de bases de datos muy popular con un licenciamiento altamente restrictivo y grandes costos de propiedad si la base de datos tiene un significativo tamaño, o si es usado por un número significativo de clientes. El mismo, sin embargo provee una interfaz de usuario bastante amigable, es muy fácil de usar y aprender, y además las configuraciones de los niveles de entrada son relativamente bajos. Esto ha resultado en una base de datos de usuarios muy grande.

PostgreSQL ahora enfrenta a MS SQL Server en el cambio básico de características, disponibilidad y rendimiento, tiene una licencia mucho menos restrictiva, y es código abierto. Como un atenuante del curso, los usuarios que migran a PostgreSQL desde MS SQL Server tanto el costo de la propiedad se convierte en un aspecto importante a considerar, así como también amplían su conocimiento en cuanto a sistemas de bases de datos relacionales.

Este HOW-TO está desarrollado para el usuario que está lista en este instante para migrar bases de datos a PostgreSQL

Consideraciones

Las características de los RDBMS son implementadas diferentemente y en diferentes niveles por los programadores. Algunas aplicaciones dependen grandemente de los llamados middleware, o la lógica del negocio es manejada desde la aplicación cliente.Otros tratan de poner la lógica de ser posible en la base de datos.La migración será mucho más difícil si la aplicación está en el último grupo. Si bien es una opción de diseño de sonido para poner la lógica en el servidor de base de datos, se requerirá de programación en un proveedor específico Lenguaje de consulta estructurado (SQL) como la extensión de Transact de Microsoft SQL (T-SQL). Éste es también el caso con PostgreSQL. No hay manera fácil de migrar los procedimientos almacenados, disparadores o reglas. En el lado positivo, PostgreSQL proporciona varias opciones de idioma, todos los cuales son más gratos que el T-SQL.

Todos los RDBMS ofrecen funciones integradas. Sin embargo, como las extensiones de procedimiento para SQL, no son portables. Afortunadamente, existe un cierto solapamiento, y la sintaxis simple hace que la migración es relativamente fácil.

Por último, la elección del programador de la sintaxis SQL puede afectar este proceso. La mayoría de los RDBMS se están acercando a las normas aprobadas de SQL. Es decir, se inclina lejos de la sintaxis de proveedores específicos, tales como la sintaxis '*=' para un LEFT OUTER JOIN. Esta sintaxis es aún soportada en MS SQL Server desde la versión 7.0, pero nunca fue soportada en PostgreSQL.

Este proceso requerirá una cantidad de la mano de la edición de guiones y archivos de datos, o uso de un lenguaje de script para programáticamente modificar estos archivos, seguido de una cantidad enorme de algo menos de edición. Yo no soy lo suficientemente inteligente como para identificar todas las opciones posibles para la migración, o para darles cabida en un script. He hecho esta migración de una aplicación de base de datos relativamente compleja en una cantidad razonable de tiempo. Esto, en lugar de un guión técnicamente impecable, debe ser su objetivo.

Usaré la herramienta Command Language (TCL) para casi todo, así que la uso aquí. Usted puede utilizar cualquier idioma que desee.

Tablas

Volcado de las definiciones de las tablas con la herramienta de MS SQL Server Scripting. Desde el Enterprise Manager, haga clic derecho sobre la base de datos y seleccionar "Generar Todas las tareas 'y luego' secuencias de comandos SQL en el menú de contexto. Desactive la opción "Todos los objetos de secuencias de comandos 'y seleccione' Todos los cuadros". En el 'formato' la ficha, de-seleccione "Generar el ...'. En la pestaña "Opciones", seleccione "índices de secuencias de comandos y de secuencias de comandos PRIMARIA LLAVES ...'. Seleccione el formato de archivo de MS-DOS, y asegúrese de que "Crear un archivo" esté marcada. Haga clic en Aceptar, darle un nombre, y ponerlo en algún sitio donde pueda encontrarlo.

Una breve mirada a este archivo le mostrará lo que estamos en contra. MS utiliza corchetes de todos los identificadores, para protegerse de las opciones de diseño pobre como el uso de palabras reservadas para que las cosas locas como:

 CREATE TABLE [dbo]. [Seleccionar] ([Unión Europea] [int])

son posibles. PostgreSQL usa comillas dobles en su lugar. MS utiliza el propietario del objeto de calificación para todos los objetos, 'dbo' en este caso. PostgreSQL no tiene esas reservas en los nombres de objeto.

Otra cosa para notar es que los Estados miembros identificadores SQL no son case en conserva, pero en la práctica mayoría de las instalaciones se instalan como mayúsculas y minúsculas. PostgreSQL es el caso agnóstico en el caso de palabras clave de SQL y los identificadores no cotizadas, obligando a todas las consultas a minúsculas. No es el mismo caso de ser insensible, en que se pueden crear tablas mediante la protección de comillas dobles se mencionó anteriormente, de manera que solamente se puede acceder utilizando el mismo método de cita doble. Me parece que es mejor abandonar el caso en los identificadores de objetos al migrar a PostgreSQL. Además, lo más seguro es evitar que no lo requieren, para evitar problemas en el camino.

Cabe señalar que para las comparaciones de datos, PostgreSQL es sensible a mayúsculas y no hay ninguna opción para cambiar este comportamiento. Tendrá a la fuerza de datos a alta o baja en ambos lados de las comparaciones de texto si el caso no es importante para la operación y existe la posibilidad de que sea diferente. Esta conversión podría ser un buen momento para obligar a los datos utilizados en las comparaciones y se une al caso de los superiores o inferiores. También tendrá que mirar a la solicitud de código que hace la comparación de la información introducida por el usuario aprovechando la insensibilidad caso típico de MS SQL Server.

Otra cuestión que no es inmediatamente evidente es que MS SQL Server es compatible con las instrucciones ALTER TABLE que contienen listas separadas por comas de las alteraciones. PostgreSQL actualmente no lo hace. Esto es importante ya que el programa de MS SQL Server Scripting crea todas las restricciones de ALTER TABLE. Si las tablas tienen más de una restricción, usted tendrá que separar quirúrgicamente en sus propios estados de ALTER TABLE, o moverlos en la sentencia CREATE TABLE.

Los índices son un punto brillante, en su mayoría. La palabra clave de clúster en PostgreSQL no es lo mismo que la palabra clave agrupados en una creación de MS SQL Server índice. PostgreSQL le permitirá «cluster» de una tabla, es decir, reorganizar las tuplas de una tabla para que ese campo. Esto suena bien, excepto que el grupo no se mantiene para las actualizaciones y las inserciones, y el hecho de que se rompa todos los otros índices cada vez que generan la agrupación.

Dicho todo esto, he aquí una lista parcial de las cosas para corregir:

1. Fuerza a minúsculas.
2. Quite todos los corchetes.
3. Quitar todos los prefijos de objeto del propietario (es decir, "dbo").
4. Quitar toda referencia al grupo de archivos (es decir, "EN PRIMARIA")
5. Elimina todas las palabras clave el apoyo opcional (es decir, "CON NOCHECK", "CLUSTERED"),
6. Actualización de todos los no-tipos de datos compatibles (es decir, "DATETIME" se convierte en "timestamp") Además, este es un buen momento para alejarse de MONEY.It está soportado en PostgreSQL, pero está en su out.Use forma numérica (19,4 ).
7. Vuelva a colocar el Terminator T-SQL por lotes "GO" con el terminador de lote PostgreSQL ";"

Coloque este archivo en un lugar seguro, y ahora vamos a obtener los datos.

Datos

Los datos son los datos. Es traído en forma de texto y se moldea en su forma correcta de la base de datos de acuerdo a los tipos de datos que utilizó en la creación de las tablas. Si usted tiene datos binarios, yo soy el tipo equivocado para preguntar.

Hay un par de error aquí también, por supuesto. Desde que utilizamos el comando COPY, y se interpreta como un salto de línea al final de una tupla, es necesario limpiar todos los saltos de línea al acecho en los campos de texto en MS SQL Server. Esto es bastante fácil de hacer. Además, el volcado de datos de MS SQL Server utiliza el estándar CR / LF terminador de línea, que debe ser cambiado a LF o que causará estragos en las comparaciones de cadenas, entre otros problemas. Lo hice el camino más fácil, la descarga de los vertederos a mi máquina con mi favorito de Unix-como sistema operativo a través de FTP, lo que hace la traducción para usted.

El primer paso en el volcado de los datos de MS SQL Server es escribir todos los nombres de sus campos en un archivo de texto en la máquina Win32. Puedes hacer trampa y número:

 select name from sysobjects WHERE type = 'U'

en el Analizador de consultas (ISQL-W) para obtener la lista, y luego guardar los resultados en un archivo. Luego, escribir un pequeño script muy útil para llamar a BCP, el programa de copia masiva. Minas tiene este aspecto:

 conjunto de archivos [Open "C: \ \ inetpub \ \ ftproot \ \ tablelist.txt" r], mientras que ([eof $ file]) (
     cuadro [gets $ file]
     Exec bcp .. mesa $ out $ table-c-k-S192.168.100.1-Usa-Ppassword-R ~
 )
 cerca de $ archivo

Esto volcado todos los cuadros que figuran en los archivos del mismo nombre en el directorio actual. La opción-c medios para utilizar el formato de caracteres sin formato. El flag-k dice bcp para "mantener los nulos". Esto es importante, más adelante, cuando la importación de los datos. El-R es el "terminador de la fila". Para hacer la limpieza de los retornos más fácil el transporte, uso este para indicar el final de una fila. Pongo esta secuencia de comandos en el directorio C: \ Inetpub \ ftproot, así que puedes ir al siguiente paso.

De la máquina Unix, ftp de inicio y obtener el archivo de lista que creó anteriormente. Ponlo en un directorio de trabajo. Cambie al directorio nuevo trabajo y conseguir los archivos:

 ftp> lcd / home / Homer / local workdir ahora / home / Homer / workdir
 ftp> fget tablelist.txt

Esto debe descargar todos los archivos de datos en el directorio de trabajo, mágicamente convertir terminadores de línea a formato compatible con Unix. Si usted no puede usar FTP, hay otras maneras de obtener los archivos de aquí para allá. Sólo tenga en cuenta que es posible que tenga una secuencia de comandos sed poco para solucionar el CR / LF problema.

Ahora, vamos a solucionar el problema incrustado avance de línea.

 #! / usr / pkg / bin / tclsh conjunto de archivos [tblnames abierto r]
 flist conjunto [lectura nonewline $ file]
 cerca de $ archivo
 flist conjunto [$ split flist \ n]
 flist foreach f $ (
     conjunto de archivos [Open $ f r]
     conjunto de datos [read-nonewline $ file]
     cerca de $ archivo
     regsub-all (\ 000) () $ data de datos
     regsub-all (\ n) $ data \ \ \ n datos
     regsub todo ~) ($ datos \ n datos
     conjunto de archivos [Open $ f w]
     pone-nonewline $ file $ datos
     cerca de $ archivo
 )

Las líneas son regsub donde se realiza el trabajo. Que reemplazar todos los valores nulos (\ 000) con una cadena vacía, entonces todos avances de línea con un literal "\ n" que le dirá a copiar lo que hacemos al importar el archivo, entonces mi terminadores de línea se reemplazan con un salto de línea, que es lo COPIA está esperando. Hay formas más limpias y más fácil de hacer esto, pero usted consigue el punto.

Ahora, vuelve a el archivo sql modificó para crear su base de datos de objetos. Supongo que está en el cuadro de Unix en este punto. Debe tener una serie de comandos CREATE TABLE, seguido de ALTER TABLE y CREATE INDEX, etc declaraciones. Lo que tenemos que hacer ahora es decirle que queremos cargar los datos después de que se crean las tablas, pero antes que nada.

Para cada comando CREATE TABLE, siga con una declaración COPY. Algo como:

 COPY FROM TableName "/ home / Homer / workdir / NombreDeTabla con nula como;

Una vez que haya hecho esto, ejecutar en contra de su base de datos PostgreSQL, algo así como

 Newdb $ psql <modifiedscript.sql &> OUTFILE

debería funcionar. El archivo de salida es bueno tener para buscar problemas. Se vuelve tan desordenado:

 OUTFILE $ cat | grep ERROR

puede dar una idea de cómo iban las cosas. Te garantizo que tienen que hacer algunos ajustes.

Vistas == ==

Las opiniones están bastante fácil, mientras que no ha utilizado demasiadas funciones en ellos. Una de mis favoritas es IsNull (). Como la mayoría de las funciones, tiene una contrapartida de PostgreSQL, se unen (). Un número sorprendente de funciones funcionará bien. Por ejemplo, round () es exactamente el mismo. DatePart () se convierte en date_part (), pero los argumentos son los mismos, a pesar de PostgreSQL puede ser más concreto sobre las cadenas de formato. Por ejemplo, SQL Server aceptará DatePart (aaaa, myDateField), así como DatePart (año, myDateField). PostgreSQL quiere ver date_part ( 'año', myDateField) (nótese las comillas simples).

SQL para la generación de puntos de vista es más o menos lo mismo que para las tablas. Desde el Administrador de Empresa, haga clic derecho sobre la base de datos y seleccionar "Generar Todas las tareas 'y luego' secuencias de comandos SQL en el menú de contexto. Desactive la opción "Todos los objetos de secuencias de comandos", y seleccione "Todas las vistas". En el 'formato' la ficha, de-seleccione "Generar el ...'. En la pestaña "Opciones", se comprueba Seleccione el formato de archivo de MS-DOS, y asegúrese de que "Crear un archivo". Haga clic en Aceptar, darle un nombre, y ponerlo en algún sitio donde pueda encontrarlo.

Ejecute este archivo a través del mismo guión que ha creado para limpiar el SQL para sus tablas, y ver si funciona en PostgreSQL. Si no, tendrá que hacer algunas de las funciones de fijación.

Resumen

La conversión de una base de datos de MS SQL Server no es siempre fácil. Es, sin embargo, siempre vale la pena. Va a encontrar PostgreSQL a un producto muy potente y flexible, con el mejor soporte técnico en el mundo, los desarrolladores y los usuarios reales del producto. Si usted pasó días tratando de conseguir xp_sendmail para trabajar en SQL Server versión 7.0, o se preguntaban qué estaba en esos enormes "Service Pack", usted lo apreciará.