De nuevo con postgres

Hace tanto que no usaba postgres que ya había olvidado ciertos detalles, los documentare aqui para futuras referencias, estamos usando un postgresql 8.4.5 sobre un ubuntu server 9.10, tambien tuve otras bronquillas interesantes con los accesos locales y remotos.

1. Despues de instalar nuestro postgres con apt-get nos disponemos a accesar con el ya tradicional:

$sudo su postgres

2. Una vez adentro accesamos directamente a pqsl

#psql

3. Digamos que quiero hacer una base de datos llamada pepito y que el usuario sea pepito_user con un password, primero crearemos el usuario, despues la db y al final nos salimos

CREATE USER pepito_user WITH PASSWORD 'n0m4nch35';

postgres=# CREATE DATABASE pepito WITH OWNER pepito_user;

postgres=# \q

4. Ahora accesamos como pepito_user

# psql -d pepito -U pepito_user --password

Aqui lo mas probable es psql te raye la mother, que no te permita accesar, esto es porque no está habilitado el acceso de este tipo para postgres, para hacer esto modificamos nuestro querido archivo pg_hba.conf, en cada maquina puede variar pero en la mia estaba en

/etc/postgresql/8.4/main/pg_hba.conf

Lo editamos como root, te encontrarás esta linea la cual vamos a borrar o comentar

local all all ident

y escribiremos la siguiente

local all all md5

Explicacion: el método de autenticación ident se refiere a que el usuario que se validará para el acceso sera el que esta en sesión y como mi usuario es postgres aunque escriba yo --password en psql le valdrá gorro, por lo cual preferimos md5 que nos permite mandar un user y password encriptado por seguridad en md5, esto también es útil para accesar remotamente ya que remotamente no siempre estas logueado como un usuario ident.

Una vez que guardemos los cambios debemos reiniciar el demonio de postgresql

# sudo /etc/init.d/postgresql stop

# sudo /etc/init.d/postgresql start

ó

# sudo /etc/init.d/postgresql restart

5. Ahora que ya estamos dentro ya podemos crear la estructura de nuestra db con CREATE table... cosa que no especificare aqui. Despues de eso nos vamos a otra maquina que tenga instalado un cliente psql e intentamos accesar remotamente:

# psql pepito -h pepito.com.mx -U pepito_user --password

pepito.com.mx puede ser la ip de tu server postgres también... sin embargo lo mas probable es que te rechace la petición... ¿que pasa? que nuestro archivo pg_hba.conf no esta configurado para recibir peticiones externas, por lo cual nos regresamos a nuestro server de postgresql y lo editamos de nuevo, ahora agregaremos la siguiente linea:

host all all 0.0.0.0 0.0.0.0 md5

Y reiniciamos postgres. Ahora ya podrán acceder remotamente y hacer las tablas que quieran a menos que suceda algo como lo del siguiente punto.

6. Mi server tiene 6 direcciones ip para una misma tarjeta por lo cual postgres no mostraba el abierto el puerto mas que en localhost y en la ip principal, para que postgres se escuche en todas las direcciones ip o incluso en las direcciones que tu elijas debemos modificar el archivo

sudo vi /etc/postgresql/8.4/main/postgresql.conf

Buscamos una linea que diga listen_addresses que esta comentada, ahi vienen las instrucciones para configurarla, yo lo único que hice fue escribir una nueva linea de la siguiente manera:

listen_addresses = '*'

reiniciando el servidor ya se podia ver el puerto 5432 de postgresql en todas las ip de forma remota... como supe esto? pues monitoreando los puertos con ayuda del comando "nmap" y con el comando "netstat -putan" con los cuales puedes escanear los puertos abiertos tanto de tu misma maquina como el de otras maquinas remotas, por ejemplo, al ejecutar nmap dentro de mi server podia ver el puerto 5432 abierto pero si ejecutaba nmap desde otra maquina hacia mi server no lo podia ver, por cierto, recomiendo ejecutar nmap de la siguiente manera, tarda un poco mas pero podemos ver mas puertos abiertos.

#nmap -p1-65535 pepito.com.mx

FIN

RAZONES POR LAS CUALES ES MEJOR EL SQL MANAGENENT STUDIO DE SQL SERVER 2005(SMSSQL) QUE EL ADMINISTRADOR CORPORATIVO DE SQL 2000(ACSQL)

PROS
1. Te puedes conectar a servidores sql 2005 con SMSSQL y en ACSQL no
2. La edición de procedimientos almacenados es tipo texto por lo que se facilita la substitución de variables en todo el procedimiento sin necesidad de copiar y pegar y otros malabares
3. El texto del editor es monoespacio. Nota: no se si el texto sea configurable tanto en SMSSQL como en ACSQL pero SMSSQL lo trae por default por lo tanto le doy el triunfo.
4. Aparte de que en general la programación T-SQL para SQL Server 2005 es mucho mejor que la de su antecesor sobre todo en el manejo de XML y paginación y como en ACSQL no puedes ni accesar pues ya perdió mas puntos.

CONTRAS
1. Los wizards de SMSSQL me han fallado generalmente y los de ACSQL no, como por ejemplo para una simple importación de datos desde excel o incluso de una tabla a otra.
2. Es algo pesado y necesita obviamente una buena cantidad de memoria para trabajar agusto.

SQL SERVER EN LINUX

En mi busqueda existencial por migrar mi trabajo a linux he buscado algún administrador de bases de datos desarrolado en java (por eso de la portabilidad) y son útiles hasta que llegas al punto de tener que trabajar con procedimientos almacenados, ni si quiera con el querido druid ha podido decodificarlos -puede ser problema del jdbc no de druid exactamente- y he llegado al grado de pensar seriamente en desarrollar mi propio IDE linuxero para sql server, sin embargo esto seria dar cierta ayuda microsoft, aunque tambien el objetivo es convencer a la gente -sobre todo de escritorios aqui cercanos- que el software libre es tan útil como el propietario, asi que por ese lado valdria la pena. En fin, rindiendome ante la supuesta portabilidad javosa me encontre una pequeña herramienta llamada sqsh que trabaja bajo la amada consola y con freetds. No me he metido mucho, pero si me he conectado ejecutado algunos selects y además de que si me trae el contenido de los stored procedures decodificado, acabo de imprimir el manual para leerlo y recomiendo hagais lo mismo. A veces lo mas sencillo es lo más útil.

Algunas páginillas:

http://infolab.stanford.edu/~ullman/fcdb/aut96/sy-intro.html <--introduccion a sqsh


http://misdocumentos.net/wiki/linux/odbc <--Acceso a sql desde ubuntu... buenisima pero lentisima

Tablas temporales en el Transa-SQL

Podemos usar dos trucos para programar en procedimientos almacenados en T-SQL:

1. Usar tablas temporales en disco:

Estas se crean como cualquier tabla:

'Create table #MiTabla(valor1 tipoDeDato1, ...)'

Asi que la unica diferencia con crear una tabla realmente es el signo # y podemos hacer inserciones y todo lo que querramos. Se supone que esta se elimina al terminar el procedimiento o la sesión del usuario, sin embargo para fines de efectividad se recomienda hacerle un 'delete table #MiTabla' en cuanto esta ya no se necesite ya que estas tablas se crean en disco duro. Lo que todavia no he comprobado es si se crea una tabla temporal por sesión o todos los usuarios acceden a la misma tabla, lo cual nos podria traer algunos problemas de concurrencia...

2. Usar tablas temporales en memoria:

Estas estan un poco mejor y quiero suponer que no tendremos problemas de concurrencia con ellas ya que son declaradas como una variable:

'declare @MiTabla TABLE(valor1 tipoDeDato1, ...)'

para despues poder utilizarse como cualquier tabla comun y corriente.

Asi que el top-ten de uso en efectividad es el siguiente:

1. Tablas.
2. Tablas temporales en memoria.
3. Tablas temporales en disco.
4. Cursores.

Curiosamente ocupa menos procesamiento accesar el disco que declarar un cursor en memoria, los cursores pueden ser utiles en registros no mayores a 300, para el resto se recomienda el uso de tablas temporales, claro que todo depende de los requerimientos de tu sistema.

Referencias: http://sql.manivesa.com/Tutoriales+SQL/SQL+Server/550.aspx

SQL

1. Copiar una tabla. agregado el 17-05-2007:

select tabla1.* into tabla2 from tabla1 where 1=2

Lo que hace es copiar la toda la tabla uno en una tabla2 creando la tabla 2 y la comparación falsa 1=2 es que la copia vacía, solo su estructura, digamos que es como un Save As. Ahora, si una vez que la creamos nos arrepentimos de nuestros pecados y queremos meter datos en la tabla 2 tenemos que hacer algo similar al siguiente punto.

2. Insertar de una tabla a otra. 18-05-2007

insert into tabla2 select * from tabla1 where 1=1

Esto nos copiara todo el contenido, claro que podemos definir el filtro como nosotros queramos.

3. Vaciar una tabla

delete tabla

Hay que tener cuidado con esta aparentemente inocente instrucción, puede causar daños irreparables si no se tiene un respaldo de los datos.

Receta de Cocina Postgresql.

Para esta receta utilizaremos un ubuntu feisty montado en una dell inspiron E1505:

1. Dadas estas caracteristicas bajaremos nuestro gestor de bases de datos postgresql mediante el maravilloso apt-get (o si prefieren usar Synaptics allá ustedes):
$sudo apt-get install postgresql-8.2

2. Una vez instalado nos disponemos a entrar con nuestro usuario postgres a nuestra sesion postgres:
$sudo su postgresql
Deberemos ver en nuestro shell como algo similar a esto:
postgres@micompu: /$

3. Ahora, podemos intentar entrar al administrador psql:
postgres@micompu: /$ psql

Esto nos dará acceso a todas las bases de datos como administrador, como ven, esto es poco seguro, además de que no podemos hacer muchas cosas, por eso vamos a colocar la contraseña para administrador y vamos a realizar un pequeño cambio a la configuración:

3a. Para cambiar la contraseña, ya estando dentro del editor psql escribimos las siguientes lineas

=# ALTER USER postgres
=# WITH PASSWORD '12345';

Si todo salio bien nos salimos con el comando \q
=#\q

4a. Luego un exit para volver a nuestro usuario. Entraremos a nuestro archivo de configuracion de conexiónes pg_hba.conf de nuestro postgresql.
$sudo vi /etc/postgresql/8.2/main/hba.conf

y cambiamos la linea
local all all ident sameuser
por
local all all password

y hasta donde tengo entendido esto permitira accesos desde cualquier lugar por medio de un password, de otra manera nadie podrá accesar aunque le asignes un password.

Ahora reiniciaremos nuestro servidor de bases de datos:
$sudo /etc/init.d/postgresql-8.2 restart

Ahora entraremos tal como lo hicimos hasta el paso 3.

4. Una vez en en psql, nos dispongremos a crear un usuario, de hecho hay dos maneras de hacerlo:

4a.la primera mediante el comando createuser pero no en psql sino como usuario general de postgres, asi que tendriamos que salir de psql para hacerlo. Te preguntara algunas cosas como si el usuario creado va a ser

4b. la segunda es ejecutando una sentencia SQL dentro de psql, la cual sería similar a esta:
=#CREATE USER nombre_del_usuario WITH PASSWORD 'supass';

5. Ahora crearemos una base de datos que pertenezca a ese Usuario
=#CREATE DATABASE mi_db WITH OWNER = "nombre_del_usuario";


6. Ahora podemos entrar desde usuario postgres
postgres@micompu: /$ psql -d mi_db -U nombre_de_usuario
y veremos algo similar a esto:
mi_db=>

Ahora podremos meter las sentencias SQL para crear tablas, etc, etc... pero esto no es lo importante, ya que podemos acceder incluso como administrador psql y realizar esto, lo que logramos aqui es utilizar el acceso a un usuario especial al cual podemos asignarle ciertos privilegios por seguridad.

7. Como plus, instalaremos mediante apt-get admin3.
$sudo apt-get install admin3

el cual se agregará a nuestro menú de aplicaciones y herramientas del sistema. Para poder acceder en conexión pondremos los siguientes parámetros(los cuales configuramos en los pasos anteriores):

Address: localhost
MaintenanceDB: postgres
Username: postgres
Port: 5432
SSL: --- lo dejamos en blanco ---
Password: 12345

Algunos otros comandos utiles como psql:
\? - ver ayuda
\q - salir
\dt - ver tus tablas(siempre y cuando hayas entrado como en el paso 6, incluso sin User)

... los que vayan saliendo