ESTRUCTURA Y FUNCIONAMIENTO
Postgresql es un sistema gestor de base de datos relacional orientado a objetos, distribuido bajo licencia BSD y de código abierto.
Utiliza un modelo cliente/servidor y usa multiprocesos en vez de multihilos para garantizar la estabilidad del sistema. Un fallo en uno de los procesos no afectará el resto y el sistema continuará funcionando.
A continuación podemos ver un esquema de la estructura básica del sistema PostgreSQL:
- Aplicación cliente: Esta es la aplicación cliente que utiliza PostgreSQL como administrador de bases de datos. La conexión puede ocurrir via TCP/IP ó sockets locales.
- Demonio postmaster: Este es el proceso principal de PostgreSQL. Es el encargado de escuchar por un puerto/socket por conexiones entrantes de clientes. Tambien es el encargado de crear los procesos hijos que se encargaran de autentificar estas peticiones, gestionar las consultas y mandar los resultados a las aplicaciones clientes
- Ficheros de configuracion: Los 3 ficheros principales de configuración utilizados por PostgreSQL, postgresql.conf, pg_hba.conf y pg_ident.conf
- Procesos hijos postgres: Procesos hijos que se encargan de autentificar a los clientes, de gestionar las consultas y mandar los resultados a las aplicaciones clientes
- PostgreSQL share buffer cache: Memoria compartida usada por POstgreSQL para almacenar datos en caché.
- Write-Ahead Log (WAL): Componente del sistema encargado de asegurar la integridad de los datos (recuperación de tipo REDO)
- Kernel disk buffer cache: Caché de disco del sistema operativo
- Disco: Disco físico donde se almacenan los datos y toda la información necesaria para que PostgreSQL funcione
Algunas de sus principales características son:
Alta concurrencia
Permite que mientras un proceso escribe en una tabla, otros accedn a la misma tabla sin necesidad de bloqueos.
Amplia variedad de tipos nativos
Soportando:
- Números de precisión arbitraria.
- Texto de largo ilimitado.
- Figuras geométricas
- Direcciones IP o MAC.
- Arrays
Otras Características:
- Claves ajenas.
- Triggers o disparadores.
- Vistas.
- Integridad transaccional.
- Herencia de tablas.
- Tipos de datos y operaciones geométricas.
- Soporte para transacciones distribuidas.
Funciones en lenguajes:
- PL/PgSQL (Lenguaje propio)
- C/C++
- Java PL/Java web
- PL/Perl
- plPHP
- PL/Python
- PL/Ruby
- PL/sh
- etc.
INSTALACIÓN EN SERVIDOR LINUX
Descargamos el paquete a instalar:
Ahora hay que cambiarle la contraseña al usuario ‘postgres’ que se crea al realizar la instalación
Asimismo, hay que hacerlo mediante la consola de administración de postgresql:
A continuación vamos a configurar el archivo de configuración postgresql.conf para que admita conexiones desde fuera (he puesto ‘*’, ya que estoy manejando máquinas virtuales, en realidad ‘*’ resulta en un fallo de seguridad al permitir todo tipo de conexiones entrantes):
Y luego el archivo pg_hba.conf para especificar si queremos la base de datos, usuario, dirección o direcciones que se va a permitir que se conecten y el método de autenticación:
En mi caso, he puesto la dirección de otro cliente de la red interna, sólo para comprobar el funcionamiento de la conexión remota.
Ahora vamos a crear un usuario para que se pueda conectar a la base de datos:
Con las opciones dadas (-D -S -R -l), al usuario no se le permite crear bases de datos, ni ser superusuario, ni crear roles, pero se le permite iniciar sesión. Luego, se le ha asignado una contraseña.
Se puede comprobar su creación de la siguiente manera:
Tras crear el usuario, lo próximo va a ser crear la base de datos:
Con las opciones, le hemos asignado una plantilla, hemos asignado un usuario propietario de la misma y especificado el esquema de codificación, así como, el nombre de la base de datos.
Tras esto habrá que darle permisos al usuario creado para esta base de datos:
Asimismo, podemos ver como se ha creado la base de datos:
El último comando nos permite corroborar la creación de la base de datos, igual que cuando lo hicimos con el usuario.
Una vez hecho esto, reiniciamos el servicio y desde un cliente (192.168.0.2 en mi caso) probamos a conectarnos a la base de datos:
Con la opción -h especificamos el host al que nos conectamos, con -d el nombre de la base de datos y con -U el usuario con el que nos vamos a conectar.
Como vemos, se nos conecta, por lo cual postgresql ya está instalado y configurado en nuestro Debian.
INSTALACIÓN EN WINDOWS
Para realizar la instalación de Postgresql en Windows nos vamos a bajar la plataforma de desarrollo y administración pgAdmin. Nos vamos a su pagina oficial y nos descargamos la versión más actual:
Una vez instalada (pasos sencillos, dejando las opciones por defecto) nos aparecerá la siguiente interfaz:
Nos conectamos al servidor que hay y nos pedirá contraseña, la cual hemos introducido durante la instalación:
Primero vamos a crear un rol nuevo, para ello nos vamos a la sección login roles y con botón derecho añadimos un nuevo rol:
Nos vamos a la pestaña de privilegios y le marcamos todos los permisos ya que es el que va a crear las bases de datos y demás, si sólo queremos un usuario que se pueda loguear como en la instalación de Linux, sólo dejamos la opción de login marcada:
Asimismo, creamos una base de datos:
CLIENTE WEB
Antes de ponernos a implementar nuestra base de datos, vamos a ver un cliente web que podemos usar para Postgresql llamado PhpPgAdmin, para ello vamos a su página oficial y lo descargamos:
Como podemos observar, da la opción de bajarnos los paquetes para entorno Linux y Windows. Para ver como funciona, yo he descargado la versión Windows.
Una vez descargado el paquete, lo descomprimimos en la carpeta htdocs de nuestro servicio Xampp. Si en vez de Xampp, tenemos Wampp, lo tendremos que descomprimir en su carpeta correspondiente.
Sin embargo, hay que hacer una cosa más antes de poder acceder y es enlazar php con postgresql, para ello nos debemos ir al archivo de configuración php.ini, buscar la linea comentada:
;extension=php_pgsql.dll #Si estamos en Windows
;extension=pgsql.so #En Linux
y descomentarla. Una vez hecho esto, reiniciamos el servicio Apache y ya podemos acceder a través de nuestro navegador poniendo la dirección http://localhost/phpPgAdmin-5.1
DESARROLLO DE LA BBDD
Ahora, vamos a implementar una base de datos sobre un caso específico y que nos servirá a modo de ejemplo para ver el funcionamiento de Postgresql.
Primero vamos a analizar en que consiste el escenario y que es lo queremos hacer exactamente en nuestra base de datos: «Una gestión académica de los alumnos del departamento de informática del IES Gran Capitán.»
Por lo tanto vamos a necesitar tanto información sobre los alumnos, así como de los profesores y de las asignaturas que imparten.
También habrá que distinguir en que cursos se matriculan los alumnos, de que asignaturas y si se trata de los grupos de mañana o tarde en caso de que así esté implementado en el ciclo.
Asimismo, se querrá saber las notas de cada alumno dependiendo de cada asignatura y de cada trimestre, los tutores de cada curso, así como los delegados de cada curso. Esto nos deja un esquema como el siguiente:
El siguiente paso sería pasar el modelo ER al modelo relacional, que quedaría de la siguiente forma tras haber normalizado:
Alumnos( IdAlumno/PK, Nombre, Apellido, NIE, Email)
Grupo( IdGrupo/PK, Nombre, Aula, Horario, idDelegado/FK, idTutor/FK)
RAG(( idAlumno/FK, idGrupo/FK)/PK)
Profesor( IdProfesor/PK, Nombre, Apellidos, Email)
Asignatura( IdAsignatura/PK, Nombre, Código, Horas)
RAA(( IdAlumno/FK, IdAsignatura/FK)/PK, 1ºTrim, 2ºTrim, 3ºTrim, Final)
RGPA(( IdGrupo/FK, IdAsignatura/FK, IdProfesor/FK)/PK, Función)
Vamos a pasar ahora a la implementación del diseño físico:
IMPLEMENTACIÓN EN WINDOWS
Procedemos a crear las distintas tablas de las que va a contar nuestro esquema:
Para la carga de datos podemos entrar los datos manualmente o cargarlos desde archivos de texto plano, csv o binarios en ‘Herramientas>Importar‘:
Una vez introducidos todos los datos en sus correspondientes tablas podemos llevar a cabo las consultas que queramos realizar mediante la herramienta que nos proporciona postgresql, tanto mediante comandos sql como de forma gráfica:
Vamos a realizar algunas consultas de ejemplo para ver si funciona nuestra implementación:
- Quiero que me muestre todos aquellos alumnos que a final de curso han aprobado la asignatura ‘Implantación de Sistemas Operativos’.
- Quiero que me muestre el tutor de 1ºASIR y las asignaturas que da en los distintos grupos y los grupos en el que las da, ordenado por el grupo:
- Quiero saber cuantos alumnos tiene cada grupo:
Antes de pasar a la implementación en Linux, vamos a hacer un backup de nuestra base de datos para cargarla directamente desde el servidor Linux y no tener que volver a hacerlo todo de nuevo.
Para eso nos vamos a ‘Herramientas>backup‘ y lo vamos a guardar como archivo de texto plano:
SERVIDOR DEBIAN
Tenemos que tener cuidado con que el usuario que va a administrar la base de datos y el nombre de la misma son iguales a los del archivo creado en el servidor Windows, si no, va a dar problemas.
Una vez en el servidor escribimos la siguiente linea:
Especificando la base de datos, el usuario propietario y el archivo del que vamos a importar la información de la base de datos.
Al llevar a cabo el comando de arriba, se nos van a ejecutar todos los comandos de CREATE, ALTER e INSERT necesarios.
Para poder comprobar que efectivamente se nos han cargado las tablas, iniciamos sesión en la base de datos y ejecutamos el siguiente comando:
Si no tenemos la suerte de tener la información de la bbdd disponible para cargarla directamente, ya que, no la hemos creado con anterioridad, tendremos que crear las tablas e insertar los datos mediante comandos SQL, ya sea manualmente o mediante scripts.
Tras esto, podemos realizar algunas consultas para comprobar que funciona correctamente:
- Quiero obtener el número de suspensos en la evaluación Final por asignatura ordenados de mayor a menor número:
- Se ha cambiado de delegado en el curso de 2ºDAW:
Como vemos nuestra base de datos funciona como queríamos.
Blog de ASIR 