Administrando PostgreSQL

Hola con tod@s otra vez; como ya les conté estaba migrando una base de datos MSSQL2000 a PostreSQL 8,3, bueno la tarea aun sigue, pero esta vez les contare sobre algunas herramientas que son fundamentales en el momento de administrar nuestra base de datos PostgreSQL.

1. psql (http://psql.sourceforge.net). Esta herramienta en modo texto o consola se instala predeterminadamente cuando instalamos el servidor PostgreSQL, es básicamente un programa interactivo para ejecutar comandos SQL en nuestro servidor, aunque no tiene un interfaz gráfico amigable es una poderosa herramienta par administrar de forma interactiva nuestro servidor de base de datos.

2. pgAdmin III (http://www.pgadmin.org/ ) El administrador gráfico por defecto para PostgreSQL, es bueno y nos permite hacer muchas de las tareas de administración y consulta a nuestra base de datos de forma gráfica, es totalmente gratuita y se instala siempre que instalamos PostgreSQL en un entorno Windows. Algunas cosas que no tiene y con lo que seria en si una poderosa herramienta son: un diseñador de consultas al estilo E/R, un depurador de funciones , aunque en este punto encontré que si es posible hacerlo pero hay que recompilar el servidor y la herramienta con algunos parches para que se pueda realizar dicha función, mas información al respecto en http://www.pgadmin.org/docs/1.8/debugger.html. También sería fenomenal si se pudiera exportar a otros formatos como html, xml, xls, etc. ya que solo soporta exportación de los datos consultados a CSV (texto separado por comas).

3. pg_dump. La herramienta predeterminada para hacer copias de seguridad de nuestras bases de datos en PostreSQL, lo malo ( si se le puede llamar asi) es que es en modo texto o consola y es totalmente interactiva, no pudiéndose programar tareas, pero usándolo en combinación con algún otro programa adicional como cron en unix/linux/freebsd se puede hacer muchas cosas interesantes. Pero en definitiva es imprescindible para cualquier administrador de base de datos PostgreSQL.

4. pg_top (http://ptop.projects.postgresql.org/). Interesante programa para poder monitorear el estado de las conexiones, que esta haciendo cada una y algunas cosas mas de nuestro servidor PostgreSQL en ambientes linux. Podríamos compararlo en funcionalidad con el SQLProfiler de MSSQL. Esta herramienta esta hecha al estilo de la interfaz del comando top de Linux. PgAdminIII puede también darnos este tipo de información pero solo si tenemos instalado PGAdmin en el mismo equipo que el servidor. Pero en definitiva la considero también como una herramienta imprescindible para un DBA de PostgreSQL que se respete :o) .

5, SQL Manager for PostgreSQL (http://sqlmanager.net/en/products/postgresql/manager). Poderosa herramienta de administración, consulta y manipulación de datos para PostgreSQL. Permite consultar, modificar, eliminar datos, administrar usuarios y permisos, depurar funciones, exportar a muchos formatos, diseñador de consultas, etc, etc, etc. Todo con excelentes interfaces y asistentes gráficos que hacen que la tarea de administración del servidor sea realmente un trabajo mucho mas sencillo. Yo he probado algunos otros mas y siempre llego a la conclusión de que esta herramienta es la mejor. Lo malo, es que es de pago, pero aun así podemos usarla con dos opciones una es un demo por 30 días con todas las opciones y la otra es una versión “Lite” libre de pago pero con limitaciones en algunas funcionalidades. Aun así si se tiene la posibilidad de comprarla sera una excelente compra.

6, Arinet Automatic Postgresql BackupScript (http://autopgsqlbakup.sourceforge.net/) Mas que un programa es un script basado en pg_dump para poder realizar las tareas de backup de nuestra base de datos de una forma mucho mas sencilla y con mejores opciones. Se debe trabajar también con cron en Unix/Linux/FreeBSD ya que el script esta hecho en bash. Pero para mi caso particular me ha servido bastante en el momento de administrar el backup de mi información. En el sig. link hay un pequeño manual de como ponerlo a trabajar en Linux: http://linux2.arinet.org/index.php?option=com_content&task=view&id=125&Itemid=35

Bueno es todo por ahora, si se de alguna otra herramienta por ahí se los haré saber en un próximo post. Bye

Frase 8

“La única diferencia entre un sueño y un objetivo es una fecha.”
Edmundo Hoffens

Migrando de MSSQL2000 a PostgreSQL8.3

Hola, estoy ahora migrando una BD MicroSoft SQL 2000(MSSQL) a PostgreSQL 8.3 (PGSQL)..., la BD MSSQL tiene como principal caracteristica y que ha dado todo el trabajo, muchos Stores Procedures (SPs) asi que la dificl tarea de hacer esa migracion empezo ya.
Los sistemas que actualmente uso y se conectan al MSSQL2000 estan hechos en VisualBasic 6 y en Delphi 5, ya hice pruebas de conectividad y uso de PGSQL con estos lenguajes y todo bien usando el driver OleDB de Postgres (http://pgfoundry.org/projects/oledb/). Generar la cadena de conexion es muy sencillo, en todo caso un ejemplo seria:

CadConn = "Provider=PostgreSQL OLE DB Provider;" & _
     "Password=mipassword;User ID=miusuario;" & _
    "Data Source=localhost;Location=mibasededatos;Extended Properties='';"

El Unico detalle aqui es un problema del driver cuando uno lanza SQLs que devuelven registros, provoca un error de retorno, pero esto no sucede si se hace a traves de SPs.

Ok ahora si a las bases de datos; primero unos alcances: MSSQL usa como lenguaje de programacion Transact-SQL(T-SQL) y PostgreSQL PlPgSQL, para postgres no es el unico, se pueden instalar otros como java, c, etc ,etc. Pero el mas usado y casi por default es PLPGSQL. Asi que dare alguno tips para hacer esta tarea un poco mas sencilla.

Existe una herramienta que se llama SQLWays(http://www.ispirer.com/products) que ofrece hacer dicha migracion, es realmente util, pero siempre hay que revisar lo que ha migrado, ademas al hacer esta tarea de manera automatizada hay muchos SPs que no son optimos o que no se ejecutan.

Ok entonces vamos a los tips especificos

1. En PGSQL no existen "Procedimientos Almacenados" como tales, sino mas bien "Funciones Almacenadas", eso quiere decir que siempre estaremos obligados a devolver algo desde nuestros "SPs".

2. Declaracion de variables:

MSSQL: se usa la palabra DECLARE y el nombre debe empezar con el simbolo @ , por ejem:

           DECLARE @mivar INT

PGSQL: se hace el estilo de C o de java, es decir identificador seguido del tipo de dato, por ejem:

           mivar int;

3. Las asignaciones de valores a variables:

MSSQL: se usa SET , ejem:

            SET @mivar=1.12

PGSQL: se usa el simbolo := , ejem:

            mivar:=1.12;

4. Devolucion de registros o filas, uno de los mas importantes tips creo yo.

MSSQL: se hace el select directamente y punto, por ejem: :o)

create procedure consulta as
    select * from mitabla

PGSQL: como dije anteriormente en PGSQL se usan "funciones Almacenadas" por lo que debemos indicar a nuestra funcion el tipo a devolver. Lo mejor es usar el tipo pg_catalog.refcursor, que creo que esta disponible recien desde la version 8.x de PGSQL.

CREATE OR REPLACE FUNCTION consulta() RETURNS "pg_catalog"."refcursor" AS
   declare data refcursor;
begin
   open data for (
     Select * from mitabla
   );
   return data;
END;

5. Recorrer cursores dentro de los SPs.

MSSQL:

declare @v_1 varchar(10)
declare @v_2 varchar(10)
declare cur1 cursor for
select * from mitabla
OPEN cur1
FETCH NEXT FROM cur1
INTO @v_1, @v_2
WHILE @@FETCH_STATUS = 0 BEGIN
print 'Valor 1 '+v_1 
print 'Valor 2 '+v_2 
FETCH NEXT FROM cur1
  INTO @v_1, @v_2
END
CLOSE cur1

PGSQL:

declare
cur1 refcursor;
v_1 varchar (10) ;
v_2 varchar (10) ;
begin
OPEN cur1 FOR execute('select * from mitabla');
loop
 fetch cur1 into v_1, v_2;

 if not found then
    exit ;
 end if;

 RAISE NOTICE 'Valor 1(%)', v_1;
 RAISE NOTICE 'Valor 2(%)', v_2;

end loop;
close cur1;
end;

* Existe otra forma de recorrer registros en PGSQL, pueden ver mas detalles en http://www.postgresql.org/docs/8.3/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING.

6. Bueno este tip mas que de SPs, es para la carga de data, mis tablas en MSSQL son de millones de registros asi que la mejor manera de pasar la data del MSSQL a PGSQL es:

1ero. Bajar la data del MSSQL a texto plano, tipo csv, es decir valores separados por comas.
2do. Usar el comando COPY.. FROM de PGSSQL para cargar a data desde los arhivos de texto a las tablas de PGSQL.

Ok, es todo hasta ahora, si veo algo mas lo publicare o si alguien mas puede dar un aporte bienvenido.