PostgreSQL y Bash: Cómo Usar la Terminal para Consultar, Insertar y Exportar Datos

En este artículo aprenderás a conectar a bases de datos postgreSQL desde scripts escritos en Bash y poder realizar consultas y/o tareas de mantenimiento en el servidor postgreSQL Doy por hecho que todos tenemos un servidor postgreSQL en algún sitio al que podéis acceder y configurarlo.

Antes de empezar tenéis que tener configurado postgreSQL para que acepte conexiones locales y remotas.

Si no lo tenéis habilitado o no lo tenéis claro mirar este artículo antes:
Permitir Conexiones a postgreSQL desde IP Remota o Local

1) Lanzar consultas y mostrar resultados

Una consulta desde el terminal podemos lanzarla así:

psql -h host -U usuario -d base_datos -c "consulta_sql"

este comando podemos ponerlo en un fichero .sh y crear un programa bash para ejecutar consultas.

Al fichero hay que darle permisos de ejecución

sudo chmod 744 users.sh

Podemos hacer algo como esto:

#!/bin/bash

DB="nenuro"
USER="postgres"
HOST="localhost"

psql -h "$HOST" -U "$USER" -d "$DB" -c "SELECT * FROM users;"

Depende como tengas configurado el usuario que utilizar para lanzar el comando y desde donde pedirá password o no. Revisa el artículo que he mencionado antes sobre permitir conexiones.

Pero queremos mostrar los resultados por pantalla o hacer algo con ellos.
Si asignamos el resultado a una variable lo podremos procesar

#!/bin/bash

DB="nenuro"
USER="postgres"
HOST="localhost"

resultado=$(psql -h "$HOST" -U "$USER" -d "$DB" -t -A -c "SELECT nombre FROM users;")
echo "Raw resultado:"
printf "%q\n" "$resultado"

SI queremos mostrar un poco mas formateado podemos hacerlo asi

psql -h "$HOST" -U "$USER" -d "$DB" -t -A -F"|" -c "SELECT id, name, email, created_at FROM users;" | while IFS='|' read -r id name email created_at; do
    echo "ID: $id"
    echo "Nombre: $name"
    echo "Email: $email"
    echo "Creado en: $created_at"
    echo "-----------------------"
done

2) Lanzar consultas y guardarlas en un fichero

Y como se guarda en un fichero los resultados de una consulta.

psql -h localhost -U postgres -d nenuro -c "\COPY (SELECT * FROM usuarios) TO 'usuarios.csv' CSV HEADER;"

También podríamos ponerlo en un cron y automatizar este proceso para crear backups de tablas por separado añadiendo comandos como estos en un script bash.

3) Insertar, actualizar y borrar registros

Actualizar la base de datos con nuevos registros, modificaciones o borrados es también muy sencillo desde la línea de comandos o utilizando scripts para automatizar los procesos.

Insertar elementos

psql -h localhost -U postgres -d nenuro -c "INSERT INTO users (name, email) VALUES ('Juna', 'juan@dominio.net');"

Actualizar elementos, el mismo que acabamos de insertar le arreglamos el nombre

psql -h localhost -U postgres -d nenuro -c "UPDATE users SET name = 'Juan' Where id = 16;"

Y el borrado de elementos

psql -h localhost -U postgres -d nenuro -c "DELETE FROM Users WHERE id = 16;"

Todos estos comandos los podemos poner en un script como hemos hecho al principio para automatizar tareas de mantenimiento de nuestro sistema pero nos falta una cosa …

4) Uso del password de forma eficiente y segura.

Para poder automatizar todo esto nos falta poder manejar el password de forma automática, eficiente y segura.

Hay varias formas de guardar el password que sea de forma más o menos segura (no hay nada seguro)

Utilizar el archivo .pgpass es la opción más segura que se puede utilizar.

Creamos un fichero llamado ~/.pgpass en el home y le damos permisos solo para el usuario que lo va a ejecutar

chmod 600 ~/.pgpass

Y dentro se almacenamos el password de la siguiente forma

hostname:port:database:username:password

Por ejemplo:

localhost:5432:testdb:postgres:mi_contraseña_secreta

Otra opción interesante para no tener que poner password si trabajas en el mismo servidor y estamos en un entorno seguro es configurar la autenticación con “peer” en el fichero pg_hba.conf



De esta forma al ejecutar la consulta ya no pedirá el password

Después de hacer este cambio reiniciar el servicio

sudo /etc/init.d/postgresql restart

Y se tiene que ejecutar la consulta como el usuario postgres, el usuario que se conecta a la base de datos



Lo que se ve delante de psql "PAGER=cat …" es para que al listar los resultados no se borre la pantalla y los muestre a continuación.

Otra forma de ejecutar sin password y sin necesidad de entrar en el usuario de postgres y ya en un entorno de total confianza es cambiar peer por trust



Esto permite ejecutar sentencias desde el tu usuario

psql -U postgres -d nenuro -c "SELECT * FROM users;"

Y ya por último podríamos utilizar la opcion PGPASSWORD=password delante de la sentencia, de esta forma

PGPASSWORD=root psql -U postgres -d nenuro -c "SELECT * FROM users;"

PGPASSWORD también podemos exportarlo en el entorno

export PGPASSWORD="root”
psql -U postgres -d nenuro -c "SELECT * FROM users;"

Pero esto solo durará para la sesión en curso

5) Conclusión

Como podemos ver ejecutar consultas contra un servidor postgreSQL desde el terminal o desde scripts escritos en bash para automatizar tareas es bastante sencillo.

Hay que tener en cuenta ciertas recomendaciones con la utilización de password que podríamos resumir así:

• La opción más segura es usar .pgpass, ya que guarda las credenciales de manera segura y automatiza el acceso a la base de datos sin necesidad de introducir la contraseña cada vez.
• La opción más rápida es usar la variable de entorno PGPASSWORD, pero no es tan segura a largo plazo.
• La opción interactiva PGPASSWORD es la más sencilla, pero menos conveniente en scripts.
• Si es posible, configurar autenticación sin contraseña en entornos controlados, como con autenticación peer

Y esto es todo, feliz programming!!!
Saludos
Alex
:-)
/

Si te ha gustado el artículo compartelo en:      

---

---

---

---

---