PostgreSQL-AWS: La unión hace la fuerza

Si has hecho clic en este artículo es porque según en lo que estés metido, te interesa el tipo de base de datos PostgreSQL debido a que:

  • Es software libre
  • Hace copias de seguridad en caliente (Online/hot backups)
  • Tiene acceso encriptado via SSL
  • Está disponible para Linux y UNIX en todas sus variantes
  • Posee Sistema MVCC (Multiversion Concurrency Control), que pueden interactuar varios procesos en una tabla sin que se bloquee
  • Permite gestionar datos de geolocalización y posicionamiento con la herramienta PostGIS.
  • Puede gestionar de forma combinada desde la versión 9.4 , un tipo de base de datos SQL (relacional) con No-SQL de forma nativa JSON (clave-valor usada por Mongodb ).

Sin importarte que:

  • Consume más recursos que MySQL, será por dinero…

Pues si tras estas breves aclaraciones decides continuar, ¡adelante!

¿Qué vamos a hacer?

Realizaremos el montaje de un servicio PostgreSQL, tanto en local, como en AWS (Amazon Web Services) con RDS (Relational Database Service), al que atacaremos en remoto.

 

EN LOCAL

Usaremos una máquina con sistema operativo Debian GNU/Linux 8.9, en mi caso usé este montaje para un entorno de staging.

 

Instalaremos la versión que viene por defecto, comprobamos la versión y vemos que el proceso está levantado en el puerto 5432:

 

Aquí nuestro amado log:

 

Una vez tenemos el servicio levantado y antes de meterle mano a la gestión de usuarios y bases de datos, tenemos todavía un asunto pendiente con PostgreSQL…¡atento a la jugada!

 

Por defecto PostgreSQL en Debian GNU/Linux deja habilitado el sistema de autentificación PEER para conexiones locales, por lo que si quisiéramos conectarnos a una base de datos con un usuario del sistema que no hemos creado en base de datos , nos da error y mucha guerra:

 

Veamos nuestro log:

 

Para solventarlo, vamos a cambiar el tipo de autenticación por MD5 (autenticación basada en contraseña).

 

Para ello, copiamos y editamos el fichero pg_hba.conf con el usuario postgres:

 

 A continuación, comprobamos el cambio y reiniciamos el servicio:

 

¡Ahora sí!

 

Otra cosa a tener en cuenta de la shell de PostgreSQL es que los comandos se finalizan con punto y coma,  si no lo hacemos, no nos reporta ningún error y se queda el prompt con postgres-#, dándonos algún que otro dolor de cabeza:

 

Esta es la ayuda y salida de la shell de PostgreSQL:

 

Podemos lanzar comandos tanto desde la shell del sistema como la shell de PostgreSQL, veamos algunos sencillos ejemplos:

 

Creación de base de datos y tabla de ejemplo:

 

Listado de la tabla y campos creados (debes de prestar mucha atención a los signos de puntuación):

 

Con un usuario postgres podremos realizar un listado general de usuarios y base de datos:

 

Cambio de contraseña de usuario:

 

Cambio de propietario de base de datos:

 

Dump y restore de base de datos (con el usuario postgres podremos interactuar , sin necesidad de hacer referencia al usuario propietario de la base de datos):

 

Borrado de base de datos y usuario:

 

Bien …¿no? , pues ahora toca abrir un navegador que nos vamos a…

AWS (Amazon Web Services) -> RDS (Relational Database Service)

 

En este apartado es necesario algo de experiencia en la administración AWS, por lo que algunos conceptos se darán por sabidos.

Ingresamos en nuestra cuenta de AWS > Services > RDS > Instances > Launch DB Instance:

 

AWS - Gigigo

 

PostgreSQL - Gigigo

 

Para nuestra prueba nos vale una instancia sencilla:

 

Cogemos la versión de PostgreSQL más parecida a la que hemos instalado en local, las demás características las he elegido acorde a una instancia de pruebas.

Elegimos nuestro nombre de base de datos usuario Master (mas adelante nos detendremos en los permisos que nos asigna AWS respecto a este usuario) y password:

especificaciones de instancias - Gigigo

 

 

Seguimos con más configuraciones, adáptalas a tus necesidades:

advanced settings - Postgre - Gigigo

 

Recuerda que en la VPC Security Group le damos acceso al puerto 5432 solo a la ip del host donde está el cliente de PostgreSQL:

 

 

 

postgresql_up - Gigigo

 

Atención: Los snapshots automáticos desaparecen cuando borramos la instancia, por lo que:

  • Crea una manual antes de eliminarla.
  • Si necesitas levantar una de días anteriores, levántala con otro nombre etc… y cuando esté ok, ya puedes eliminar la actual.

 

Una vez la tengamos disponible podremos gestionarla a través del menú Instance Actions:

 

Copiamos el endpoint que ha generado y accedemos:

 

Ya podemos empezar a trabajar con la ventaja de usar las posibilidades que nos ofrece una instancia RDS con PostgreSQL en AWS (https://aws.amazon.com/es/rds/postgresql/).

 

¿Quieres más?

Pues presta atención, ya que esto puede serte útil…

Un día necesitábamos importar un fichero csv dentro de una tabla de base de datos , pero con el usuario creado por defecto daba un error, porque no tenía suficiente power.

Veamos que ocurrió tanto en el entorno de staging donde teníamos montado PostgreSQL en local, como en remoto con RDS.

LOCAL

Éste es el error y su solución (no había propiedades que habilitaran la importación, debía ser superusuario):

 

AWS

Aquí ya cambian las cosas, ya que las propiedades del usuario Master Username son:

 

Vemos que NO es un usuario con atributos de superuser , si no que es miembro de rds_superuser, por lo que el COPY  “a secas” del anterior caso con PostgreSQL en local no vale.

 

Por último, tras realizar varias pruebas de todo tipo al final usamos:

\COPY, ya que era necesario que estuviera embebido dentro de la shell del sistema con el cliente psql (de nuevo, debes estar muy atento a los signos de puntuación):

 

Este post no hubiera sido posible sin la colaboración de :

  • Mónica González – Communications Department
  • Belén De Pablo – UX/UI Department

Infrastructure Department.