Consola de postgresql

Acerca del proyecto

En este post vamos a explicar como ingresar a la consola o terminal de comandos de postresql, aunque hoy en día contamos con aplicaciones wed y de escritorio que nos permiten manipular las bases de datos, tablas y registros, es bueno conocer este tipo de herramientas básicas, por si en algún momento necesitamos entrar a manipular o configurar el servidor.

Vamos a explicar como ingresar a la consola desde windows y linux, con el objetivo de entender como se hace en los distintos sistemas operativos.

Conocimiento previo

Para realizar este proyecto se requiere conocimiento previo de los siguientes temas, si aun no has trabajado con lo que se menciona en la siguiente tabla, allí están los links a los post para que puedas dar un repaso adicional o puedas recordar el conocimiento necesario para poder realizar este proyecto.

Acceder a la consola psql

En esta primera parte lo que necesitamos es que reconozca el comando psql para poder ingresar a la consola de postgres

Windows

Para ingresar a la consola de postgresql tenemos varias opciones, las cuales son las siguientes:

SQL shell

Si realizamos los pasos como nos indica la instalación que explico en el post mencionados en conocimiento previo, debemos tener un programa en el menú de inicio que es sql shell, al ingresar nos mostrará una ventana donde podemos interactuar con postgres:

Archivo bin de psql

Esta opción la usamos si no tenemos instalado el SQL shell, lo que hacemos es buscar el archivo psql.bat para poder ejecutarlo desde la consola y tener los accesos y comandos para usar la consola de postgresql.

Vamos a buscar la ruta de instalación del postgresql, en mi caso lo deje en esta carpeta que es la por defecto "C:\Program Files\PostgreSQL\13\bin" donde podemos ver todos los archivos binarios, abrimos una terminal cmd y vamos a navegar hasta llegar a esta carpeta, o simplemente con el comando:

cd C:\Program Files\PostgreSQL\13\bin

En este directorio podemos usar directamente el comando psql dando doble click.

Variable de entorno de psql

En la instalación de postgres no se realiza la actualización de las variables de entorno para que reconozca a psql, así que vamos a realizar ese paso adicional, para ello y poder probar abrimos una consola de comandos o cmd y ejecutamos el siguiente comando:

C:\Users\codigoelectronica>psql
"psql" no se reconoce como un comando interno o externo,
programa o archivo por lotes ejecutable.

Con esto comprobamos que no se reconoce el comando.

Ahora vamos a buscar la ruta de instalación del postgres en mi caso lo deje en esta carpeta que es la por defecto "C:\Program Files\PostgreSQL\13\bin"

Ubicado los archivos procedemos a realizar la actualización en las variables de entorno, para ello buscamos en windows el "editor de las variables de entorno del sistema" donde procedemos a desplegar las ventanas como se muestra en la siguiente imagen:

Editamos la variable path donde agregamos la ruta, donde están ubicados los archivos bin de postgresql.

Linux

Si realizamos la instalación correcta y no se ha presentado problemas, desde la consola o terminal podemos ingresar directamente, ya que en las variables se reconoce automáticamente, no es necesario hacer la configuración anterior.

Iniciar sesión

Vamos a iniciar sesión en postgresql, pero como vimos que al ingresar a la consola es diferente entre sistemas operativos, iniciar sesión también es diferente, a continuación paso a paso:

Windows

Para windows tenemos dos opciones dependiendo de si ingresamos a la consola por el shell o configurando la variable de entorno, veamos cómo es en cada uno de estos:

Shell

Al ingresar por la shell nos pedirá los siguientes datos:

• server [localhost]
• database [postgres]
• port [5432]
• username [postgres]

Por ultimo nos pedirá la contraseña que se ha configurado al momento de instalar postgresql.

cmd windows

Para ingresar a la consola desde la terminal cmd, debemos realizar la configuración de las variables de entorno previamente, cómo menciono en el apartado anterior, con esto reconocerá la instrucción cmd psql.

Abrimos una terminal cmd de windows y vamos a indicar los siguientes parámetros a la instrucción psql para entrar a la consola:

C:\Users\ce>psql -U postgres -W -h localhost postgres-db
Contraseña:
psql (13.2)

postgres=#

Donde:

• -U es el usuario de la base
• -W mostrará el prompt de solicitud de password
• -h IP del servidor de la base de datos en caso nos conectemos remotamente sino bastaría con poner localhost
• postgres-db: Por ultimo especificamos la base de datos sin una bandera

Linux

Una ves instalado postgres en linux, debemos hacer un par de configuraciones para acceder a la consola del mismo, primero vamos a validar si podemos entrar desde el usuario root.[1]

Ingresar a la consola desde root

Debemos iniciar con el usuario postgres que se ha creado, ya que es el único al que se le asigno el rol para ingresar por consola, para ello accedemos de la siguiente forma:

sudo -i -u postgres

Luego con escribir el comando psql, ingresaremos a la consola de postgres.

vagrant@ubuntu-focal:~$ sudo -i -u postgres
postgres@ubuntu-focal:~$ psql
psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1))
Type "help" for help.

postgres=#

Los comandos y acciones que podemos hacer las explico adelante en el siguiente apartado. Por el momento con \q salimos de la consola de postgres.

postgres=# \q

Acceder a consola sin cambiar de usuario

Podemos acceder directamente a la consola sin tener que cambiar de usuario de la siguiente forma:

vagrant@ubuntu-focal:~$ sudo -u postgres psql
psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1))
Type "help" for help.

postgres=#

Crear nuevo rol

Podemos crear un nuevo rol para darle acceso a un nuevo usuario o aplicación, para ellos podemos seguir los siguientes pasos.

Vamos a indicar que creara un usuario nuevo con la herramienta que nos proporciona postgres para ello escribimos lo siguiente:

vagrant@ubuntu-focal:~$ sudo -u postgres createuser --interactive
Enter name of role to add: oscar
Shall the new role be a superuser? (y/n) y

Esto nos pedirá si queremos crear un super usuario o no, pero el paso anterior crea el usuario en postgres, ahora lo tenemos que crear desde los usuarios de ubunti/linux, para ello hacemos lo siguiente:

vagrant@ubuntu-focal:~$ sudo adduser oscar
Adding user `oscar' ...
Adding new group `oscar' (1002) ...
Adding new user `oscar' (1002) with group `oscar' ...
Creating home directory `/home/oscar' ...
Copying files from `/etc/skel' ...
New password:
Retype new password:
passwd: password updated successfully
Changing the user information for oscar
Enter the new value, or press ENTER for the default
        Full Name []:
        Room Number []:
        Work Phone []:
        Home Phone []:
        Other []:
Is the information correct? [Y/n] y

Por ultimo creamos una base de datos para este usuario, con el mismo nombre:

vagrant@ubuntu-focal:~$ sudo -u postgres createdb oscar

Con esto logramos crear un usuario nuevo en postgres y en los usuarios de linux, solo nos queda ingresar y probar su funcionamiento.

vagrant@ubuntu-focal:~$ sudo -u oscar psql
psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1))
Type "help" for help.

oscar=#

Operaciones básicos de psql

En la consola de postgres psql podemos realizar varias acciones, en este apartado no va a ser tan detallado, pero mencionaremos los comandos básicos que podemos ejecutar:

• \l Te muestra las bases de datos existentes.
• \d Te muestra las relaciones (tablas, secuencias, etc.) existentes en la base de datos.
• \d [nombre_tabla] Para ver la descripción (nombre de columnas, tipo de datos, etc.) de una tabla.
• \c [nombre_bd] Para conectarte a otra base de datos.
• SHOW search_path; Para ver la ruta de búsqueda actual.
• SET search_path TO [nombre_esquema]; Para actualizar la ruta de búsqueda.
• \q Para salir de psql

Referencias

[1] digitalocean https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-20-04-es, Consultado abril 2022