PostgreSQL desde terminal con psql

Standard

Antecedentes:

Hola a todos, en esta publicación les voy a compartir por qué es buena idea utilizar el acceso desde terminal hacia una DB como PostgreSQL.
En este punto debo aclarar que durante todos los proyectos que he participado, estaba acostumbrado a trabajar con algún cliente o editor de SQL para la DB con la que estuviera trabajando. Algunos ejemplos:
– SQL Server: SQL Server Management Studio.
– MySQL: MySQL Workbench, SQL Yog.
– Oracle: Oracle Client, Aptana
– PostgreSQL: pgAdmin.
Con sus diferencias, la experiencia de usuario era más o menos similar, instalas tu cliente, das de alta tu sesión y te pones a trabajar con tu administración de DB y a hacer queries. Hasta aquí todo normal, el problema viene cuando trabajas en ambientes distribuidos, que implica una latencia muy alta en la respuesta de los servidores, en este caso, algunos de los editores de SQL, al tener interfaces más elaboradas, no tienen los mejores tiempos de respuesta.
Un ejemplo claro de este tipo de sistema son los servidores en la nube, como Amazon EC2, donde hay diferentes soluciones para hacer tunneling al servidor o abrir el puerto, en ambos casos nos permite acceder de manera remota, pero la latencia de la red hace muy lenta la operación.
A raíz de que empecé a participar en más proyectos de este tipo, con PostgreSQL sobre Amazon EC2, lo primero que hice fue empezar a usar pgAdmin para administrar las DB, pero a raíz del problema expuesto, empecé a buscar alternativas.
Fue con unos compañeros que estaba cursando su maestría donde vi que todas sus operaciones de DB las hacían a través de Terminal, sin necesidad de ningún cliente.
A pesar de ver algunas ventajas de hacerlo así, me tomó tiempo tomarlo como una opción que me ahorraría tiempo en mis proyectos, ahora es la única manera que utilizo, por la sencilla razón de que ya no tengo problemas de desconexión. Esta opción es sobre la que les voy a dar algunos tips de como sacarle mayor provecho.
PosgreSQL

Sugerencia: Usar pgsql

El cliente psql es un cliente por línea de comandos distribuido con PostgreSQL. Usualmente es conocido como monitor interactivo o terminal interactiva. Con psql, tienes una herramienta sencilla pero poderosa con la que puedes acceder directamente el servidor de PostgreSQL, y por lo tanto explorar SQL.
En Mac hay varias formas de instalar PostgreSQL, la que yo elegí fue usando Homebrew, la ventaja de usar estas herramientas es que hacen las configuraciones necesarias para tener los comandos y variables de ambiente necesarios:

Como conectarse:

La manera de conectarse es abriendo una terminal, y capturando los siguientes comandos:
psql -h  -p  -U
ejemplo:
psql -h xxxx.rds.amazonaws.com -p 5432 -U postgres nombre_db
conectarse-psql

 

Comandos Básicos

El primer paso puede ser conocer los comandos básicos de psql, puedes acceder una sinopsis de los comandos con diagonal invertida mediante \?.
booktown=# \?
 \a             toggle between unaligned and aligned mode
 \c[onnect] [dbname|- [user]]
                connect to new database (currently 'booktown')
 \C     table title
 \copy ...      perform SQL COPY with data stream to the client machine
 \copyright     show PostgreSQL usage and distribution terms
 \d
describe table (or view, index, sequence)  \d{t|i|s|v}    list tables/indices/sequences/views  \d{p|S|l}      list permissions/system tables/lobjects  \da            list aggregates  \dd [object]   list comment for table, type, function, or operator  \df            list functions  \do            list operators  \dT            list data types  \e [file]      edit the current query buffer or [file] with external editor  \echo    write text to stdout  \encoding   set client encoding  \f        change field separator  \g [file]      send query to backend (and results in [file] or |pipe)  \h [cmd]       help on syntax of sql commands, * for all commands  \H             toggle HTML mode (currently off)  \i       read and execute queries from  \l             list all databases  \lo_export, \lo_import, \lo_list, \lo_unlink                 large object operations  \o [file]      send all query results to [file], or |pipe  \p             show the content of the current query buffer  \pset     set table output  = {format|border|expanded|fieldsep|                 null|recordsep|tuples_only|title|tableattr|pager}  \q             quit psql  \qecho   write text to query output stream (see \o)  \r             reset (clear) the query buffer  \s [file]      print history or save it in [file]  \set   set internal variable  \t             show only rows (currently off)  \T       HTML table tags  \unset    unset (delete) internal variable  \w       write current query buffer to a  \x             toggle expanded output (currently off)  \z             list table access permissions  \! [cmd]       shell escape or command
También puedes acceder a los comandos SQL más comunes con \H.
Para salir puedes usar el comando \q.
 

Ejecutando queries en el prompt de psql

Para pasar sentencias de SQL a PostgreSQL, se capturan en el prompt (#). Todo lo que captures se va a ir encolando hasta que termines la sentencia con un punto y coma. (;) Esto te permite distribuir tu query a través de múltiples líneas.
testdb=# SELECT * FROM employees
testdb-#          WHERE firstname = 'Michael';
testdb=# CREATE TABLE employees (
testdb(#
Hasta aquí ya puedes realizar las tareas básicas que normalmente hacemos cuando estamos desarrollando una aplicación, las cuales implica hacer queries básicos como SELECT, CREATE, UPDATE, DELETE, etc.

 

Carga de Archivos Externos

A veces es útil contar con un editor, pero yo opto por usar uno que ya uso para mis otros desarrollos como es Sublime Text para editar mis archivos de Scripts de manera local. Sublime ofrece un package muy bueno para la sintaxis de PostgreSQL, aquí les dejo el link.
Tu editas tu query desde el Sublime y lo mandas ejecutar con la siguiente sentencia:
\i /
\i ~/Desktop/query.sql
sublime-sql
prompt-sql
 

Salida de queries a archivos externos

Si necesitas darle algún manejo a tus result sets de tus queries, puedes ejecutar las siguientes sentencias para guardar el resultado de tus queries en archivos de texto o CSV por ejemplo:
\Copy () To ‘’ [With CSV];
Archivo TXT
\Copy (select * from slot limit 10) To '~/Desktop/query.txt';
Archivo CSV
\Copy (select * from slot limit 10) To '~/Desktop/query.csv' With CSV;

Conclusiones:

Esta publicación es solo una muestra de todo lo que puedes hacer con la herramienta de psql, básicamente puedes hacer todas las funciones que haces con un cliente formal de SQL, pero sin overhead de una herramienta compleja y que depende de una conexión estable hacia el servidor de DB.
Uso mi laptop y puedo continuar mi trabajo en otras partes sin tener que reiniciar la terminal o en el peor de los casos, salir del host con \q y volver a entrar, lo cuál es muy rápido y no se congela mi cliente. 😛
Te recomiendo ampliamente que te des un break de editores SQL convencionales e intentes hacerlo a través de terminal, no es tan complejo como parece y es hasta más divertido. 🙂
Saludos y nos vemos en el próximo post.
 

Agregado el 19/feb/2016:

Después de que publiqué este post, me encontré con el comando \o que básicamente sirve para configurar hacia donde va la salida de los comandos, por lo que es más sencillo el combinar el uso de un archivo de queries y guardar la salida en otro archivo.
La manera de usarlo es usando un parámetro con la ruta del archivo donde se van a guardar las salidas, en el ejemplo query.txt, y si no se envía las salidas se regresan a consola.
\o query.txt
\i query.sql
\i query.sql
\o
query-o
En este ejemplo declaramos que las salidas se van a guardar en el archivo query.txt, se ejecuta un par de veces el archivo query.txt y al final se regresan las salidas a la consola.
Ahora si, se puede ver el ejemplo de como los uso en Sublime Text con un layout de dos columnas. 🙂
sublime-sql

Leave a Reply

Your email address will not be published. Required fields are marked *