Primeros pasos con Postgresql

En la entrada de hoy vamos a ver cómo montar un servidor de Postgresql paso a paso para disponer de lo básico para empezar a trabajar.

Indicar que en este ejemplo se usará la distribución Ubuntu 18.04 en su edición server, aunque el proceso no variará demasiado para otras distribuciones.

Vamos a dividir en dos secciones los pasos a realizar: por un lado estará el proceso de instalación y por otro el de realización de configuraciones básicas para poder usar la base de datos.

Instalación de Postgresql

Postgresql se puede instalar desde los repositorios oficiales de Ubuntu o desde los repositorios de Postgresql. Si no se busca una versión específica de Postgresql, bastará con usar los repositorios de Ubuntu por la comodidad e inmediatez. Por contra, si se quiere una versión en particular de Postgresql (por estar a la última o por necesitar compatibilidad con otras aplicaciones), optaremos por la segunda opción: los repositorios de Postgresql.

Instalación desde los repositorios de Ubuntu

Desde la consola, ejecutar:

$ sudo apt install postgresql

Esto instalará la versión disponible en los repositorios de Ubuntu, que para Ubuntu 18.04 es Postgresql 10.6.

Simplemente con esto, ya tendremos funcionando nuestro servicio de Postgresql.

Instalación desde los repositorios de Postgresql

Acceder a la URL de descarga de Postgresql: https://www.postgresql.org/download/. En nuestro caso, escogeremos la sección de instalación sobre Ubuntu:

Tras ello, seleccionar la versión actual de nuestra versión de Ubuntu (18.04):

Con esta selección, las instrucciones de instalación se adaptarán a nuestra versión específica. Según se indica, realizar los siguientes pasos:

1. Crear el fichero /etc/apt/sources.list.d/pgdg.list y dejarlo con el siguiente contenido:

   deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main
   

2. Importar las claves del repositorio y realizar un update de los paquetes disponibles:

   $ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
   $ sudo apt-get update
   

Ahora, si hacemos una búsqueda de las versiones de Postgresql disponibles saldrán bastantes más que antes:

$ apt search postgresql

En este caso, instalaremos la última:

$ sudo apt install postgresql-11

Configuraciones iniciales

Llegados a este punto, disponemos de un servicio de Postgresql operativo. No obstante, este solo acepta conexiones desde localhost. Sin embargo, es muy común que necesitemos que un software que se ejecuta en otro equipo se conecte a la BD para trabajar, o incluso nosotros mismos necesitaremos conectarnos desde nuestro cliente de trabajo para crear tablas, realizar consultas, etc.

Establecer una contraseña para el usuario postgres

La instalación de Postgresql crea un usuario de BD llamado Postgres. Aunque se pueden crear tantos usuarios de BD como se desee, por simplificar vamos a ver cómo establecer una contraseña a este usuario con el fin de podernos conectar por red.

Primero nos convertiremos en el usuario “postgres” del sistema operativo:

Como root, ejecutamos:

# su - postgres

Tras esto, podemos acceder al servicio de Postgresql con el cliente psql:

$ psql
psql (11.1 (Ubuntu 11.1-1.pgdg18.04+1))
Digite «help» para obtener ayuda.

postgres=#

Y establecer una contraseña para el usuario Postgres:

postgres=# alter user postgres with password '1234';
ALTER ROLE
postgres=#

Habilitar la escucha en otras interfaces de red

Por defecto, Postgresql solo escuchará en la interfaz de loopback. Si queremos que se ponga a la escucha en una interfaz concreta, o en todas, habrá que configurar el parámetro “listen_addresses” del fichero de configuración /etc/postgresql/11/main/postgresql.conf:

listen_addresses = '*'

En nuestro caso, descomentamos la linea y ponemos el valor ‘*’ para indicar que escuche en todas las interfaces de red.

Antes de reiniciar el servicio, comprobaremos que solo está escuchando en la interfaz de loopback:

# netstat -anlp | grep 5432
tcp 0 127.0.0.1:5432 0.0.0.0:* LISTEN -

Reiniciamos el servicio:

$ service postgresql restart

Y comprobamos que ahora escucha en todas las interfaces de red:

# netstat -anlp | grep 5432
tcp 0 0.0.0.0:5432 0.0.0.0:* LISTEN -t

Configurar accesos por red

Otra configuración adicional será la de especificar qué equipos podrán conectarse a qué bases de datos y con qué usuarios postgres. Si no, será imposible la conexión por red. Veamos un ejemplo.

Desde otro equipo se intenta acceder a la BD:

$ psql -d postgres -U postgres -h 192.168.1.133
psql: FATAL: no hay una línea en pg_hba.conf para «192.168.1.134», usuario «postgres», base de datos «postgres», SSL activo
FATAL: no hay una línea en pg_hba.conf para «192.168.1.134», usuario «postgres», base de datos «postgres», SSL inactivo

Sin embargo, no se tiene permitido el acceso. Para habilitarlo, accederemos al fichero de configuración /etc/postgresql/11/main/pg_hba.conf del servidor. Añadiremos una linea como la siguiente:

host postgres postgres 192.168.1.134/32 md5

Esto indica que mediante red (host), se permite el acceso del equipo con IP 192.168.1.134 a la BD postgres con usuario postgres.

Se hace un reload del servicio:

$ service postgresql reload

Y se prueba, de forma exitosa, el acceso desde la máquina externa:

$ psql -d postgres -U postgres -h 192.168.1.133
Password for user postgres:
psql (10.6 (Ubuntu 10.6-0ubuntu0.18.04.1), server 11.1 (Ubuntu 11.1-1.pgdg18.04+1))
WARNING: psql major version 10, server major version 11.
Some psql features might not work.
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=#

Indicar que Postgresql tiene muchas opciones de configuración: para realizar tuning, habilitar logs, habilitar replicación, etc. Se recomienda leer sobre estas otras opciones para ajustar de forma más precisa la configuración a nuestras necesidades.