Cómo instalar y utilizar PostgreSQL en Ubuntu 18.04

Introducción

Los sistemas de gestión de bases de datos relacionales son un componente clave de muchos sitios web y aplicaciones. Ofrecen una forma estructurada de almacenar, organizar y acceder a la información.

PostgreSQL , o Postgres, es un sistema de gestión de bases de datos relacionales que proporciona una implementación del lenguaje de consulta SQL. Es una opción popular para muchos proyectos pequeños y grandes y tiene la ventaja de cumplir con los estándares y tener muchas funciones avanzadas como transacciones confiables y concurrencia sin bloqueos de lectura.

Esta guía demuestra cómo instalar Postgres en una instancia de VPS de Ubuntu 18.04 y también proporciona instrucciones para la administración básica de la base de datos.

Prerrequisitos

Para seguir este tutorial, necesitará un servidor Ubuntu 18.04 que haya sido configurado siguiendo nuestra guía de configuración inicial del servidor para Ubuntu 18.04 . Después de completar este tutorial de requisitos previos, su servidor debe tener un usuario no root con permisos sudo y un firewall básico.

Paso 1: Instalación de PostgreSQL

Los repositorios predeterminados de Ubuntu contienen paquetes Postgres, por lo que puedes instalarlos usando el aptsistema de empaquetado.

Dado que es la primera vez que utiliza aptesta sesión, actualice el índice de paquetes local. Luego, instale el paquete Postgres junto con un -contribpaquete que agrega algunas utilidades y funciones adicionales:

sudo apt update
sudo apt install postgresql postgresql-contrib

Asegúrese de que el servidor esté ejecutándose mediante el systemctl startcomando:

sudo systemctl start postgresql.service

Ahora que el software está instalado y funcionando, podemos repasar cómo funciona y en qué se diferencia de otros sistemas de gestión de bases de datos similares que haya utilizado.

Paso 2: Uso de roles y bases de datos de PostgreSQL

De forma predeterminada, Postgres utiliza un concepto conocido como roles para gestionar la autenticación y la autorización. En algunos aspectos, son similares a las cuentas normales de estilo Unix, pero Postgres no distingue entre usuarios y grupos y, en su lugar, prefiere el término más flexible “rol”.

Tras la instalación, Postgres se configura para utilizar la autenticación de identidad , lo que significa que asocia los roles de Postgres con una cuenta de sistema Unix/Linux correspondiente. Si existe un rol dentro de Postgres, un nombre de usuario Unix/Linux con el mismo nombre puede iniciar sesión como ese rol.

El procedimiento de instalación creó una cuenta de usuario denominada postgres que está asociada con el rol predeterminado de Postgres. Para usar Postgres, puede iniciar sesión en esa cuenta.

Hay algunas formas de utilizar esta cuenta para acceder a Postgres.

Cambiar a la cuenta de postgres

Cambie a la cuenta postgres en su servidor escribiendo:

sudo -i -u postgres

Ahora puede acceder a un indicador de Postgres inmediatamente escribiendo:

psql

Esto le permitirá iniciar sesión en el indicador de PostgreSQL y desde aquí podrá interactuar con el sistema de administración de base de datos de inmediato.

Salga del indicador de PostgreSQL escribiendo:

q

Esto lo llevará de regreso al postgressímbolo del sistema de Linux.

Cómo acceder a un indicador de Postgres sin cambiar de cuenta

En el último ejemplo, se le indicó que accediera al indicador de Postgres cambiando primero al usuario postgres y luego ejecutando psqlpara abrir el indicador de Postgres. Alternativamente, podría hacer esto en un solo paso ejecutando el comando único psqlcomo usuario postgres con sudo, de esta manera:

sudo -u postgres psql

Esto le permitirá iniciar sesión directamente en Postgres sin el bashshell intermediario.

Nuevamente, puedes salir de la sesión interactiva de Postgres escribiendo:

q

Muchos casos de uso requieren más de una función de Postgres. Continúe leyendo para aprender a configurarlas.

Paso 3: creación de un nuevo rol

Actualmente, solo tiene configurado el rol postgres dentro de la base de datos. Puede crear nuevos roles desde la línea de comandos con el createrolecomando. La --interactivebandera le solicitará el nombre del nuevo rol y también le preguntará si debe tener permisos de superusuario.

Si ha iniciado sesión con la cuenta de postgres , puede crear un nuevo usuario escribiendo:

createuser --interactive

Si, en cambio, prefieres utilizar sudopara cada comando sin cambiar de cuenta normal, escribe:

sudo -u postgres createuser --interactive

El script le solicitará algunas opciones y, en función de sus respuestas, ejecutará los comandos Postgres correctos para crear un usuario que cumpla con sus especificaciones.

En primer lugar, el mensaje le pedirá que especifique un nombre para el nuevo rol. El siguiente ejemplo nombra el rol sammy, pero puede nombrar el suyo como desee:

OutputEnter name of role to add: sammy

A continuación, se le preguntará si el nuevo rol debe ser un superusuario. En PostgreSQL, un rol de superusuario tiene privilegios extremadamente amplios y puede eludir casi todas las comprobaciones de permisos.

El siguiente ejemplo especifica que el rol sammy debe ser un superusuario pero, debido a que los roles de superusuario tienen un gran poder y control sobre una base de datos, no debe otorgar a los nuevos roles el estado de superusuario a la ligera:

OutputEnter name of role to add: sammyShall the new role be a superuser? (y/n) y

Tenga en cuenta que solo puede crear nuevos roles de superusuario si los crea como un rol que ya es de superusuario. De manera predeterminada, el rol de postgres es de superusuario.

Puedes obtener más control si pasas algunas banderas adicionales. Consulta las opciones en la manpágina:

man createuser

Su instalación de Postgres ahora tiene una nueva función, pero aún no ha agregado ninguna base de datos. La siguiente sección describe este proceso.

Paso 4: Creación de una nueva base de datos

Otra suposición que el sistema de autenticación de Postgres hace de forma predeterminada es que para cualquier rol utilizado para iniciar sesión, ese rol tendrá una base de datos con el mismo nombre a la que puede acceder.

Esto significa que, si el usuario que creaste en la última sección se llama sammy , ese rol intentará conectarse a una base de datos que también se llama “sammy” de manera predeterminada. Puedes crear la base de datos adecuada con el createdbcomando.

Si ha iniciado sesión como cuenta de postgres , deberá escribir algo como:

createdb sammy

Si, en cambio, prefieres usar sudofor each command sin cambiar de tu cuenta normal, deberás escribir:

sudo -u postgres createdb sammy

Esta flexibilidad proporciona múltiples rutas para crear bases de datos según sea necesario.

Paso 5: Apertura de un mensaje de Postgres con el nuevo rol

Para iniciar sesión con identautenticación basada en Linux, necesitará un usuario de Linux con el mismo nombre que su rol y base de datos de Postgres.

Si no tienes un usuario Linux disponible, puedes crear uno con el addusercomando. Tendrás que hacerlo desde una cuenta que no sea rootsudo y que tenga privilegios (es decir, que no hayas iniciado sesión como usuario de postgres ):

sudo adduser sammy

Una vez que esta nueva cuenta esté disponible, puedes cambiarla y conectarte a la base de datos escribiendo:

sudo -i -u sammy
psql

O puedes hacer esto en línea:

sudo -u sammy psql

Este comando iniciará sesión automáticamente, asumiendo que todos los componentes se hayan configurado correctamente.

Si desea que su usuario se conecte a una base de datos diferente, puede hacerlo especificando la base de datos de esta manera:

psql -d postgres

Una vez que haya iniciado sesión, puede verificar su información de conexión actual escribiendo:

conninfo

OutputYou are connected to database "sammy" as user "sammy" via socket in "/var/run/postgresql" at port "5432".

Esto es útil si se conecta a bases de datos no predeterminadas o con usuarios no predeterminados.

Paso 6: Creación y eliminación de tablas

Ahora que sabe cómo conectarse al sistema de base de datos PostgreSQL, puede aprender algunas tareas básicas de administración de Postgres.

Primero, crea una tabla para almacenar algunos datos. Por ejemplo, una tabla que describa algunos equipos de juegos.

La sintaxis básica de este comando es la siguiente:

CREATE TABLE table_name (    column_name1 col_type (field_length) column_constraints,    column_name2 col_type (field_length),    column_name3 col_type (field_length));

Estos comandos le dan un nombre a la tabla y luego definen las columnas, así como el tipo de columna y la longitud máxima de los datos del campo. También puede agregar restricciones de tabla para cada columna de manera opcional.

Puede obtener más información sobre cómo crear y administrar tablas en Postgres aquí.

Para fines de demostración, cree una tabla de muestra como ésta:

CREATE TABLE playground (
    equip_id serial PRIMARY KEY,
    type varchar (50) NOT NULL,
    color varchar (25) NOT NULL,
    location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
    install_date date
);

Estos comandos crearán una tabla que realiza un inventario de los equipos de juegos. Comienza con un ID de equipo, que es del serialtipo. Este tipo de datos es un entero de incremento automático. También le ha dado a esta columna la restricción de primary keyque los valores deben ser únicos y no nulos.

Para dos de las columnas ( equip_idy install_date), los comandos no especifican una longitud de campo. Esto se debe a que algunos tipos de columnas no requieren una longitud definida porque la longitud está implícita en el tipo.

Los dos comandos siguientes crean columnas para el equipo typey colorrespectivamente, cada una de las cuales no puede estar vacía (según lo especificado por la NOT NULLrestricción aplicada a cada una). La línea después de estos crea una locationcolumna y agrega una restricción que requiere que los valores de esta columna sean uno de los ocho valores posibles. La última línea dentro de los paréntesis crea una datecolumna que registra la fecha en la que instaló el equipo.

Tenga en cuenta que en SQL, cada declaración debe terminar en punto y coma ( ;).

Si ingresó la CREATE TABLEoperación correctamente, devolverá este resultado:

OutputCREATE TABLE

Puede encontrar una lista de tablas dentro de esta base de datos escribiendo:

d

Output                  List of relations Schema |          Name           |   Type   | Owner--------+-------------------------+----------+------- public | playground              | table    | sammy public | playground_equip_id_seq | sequence | sammy(2 rows)

Aquí está tu tabla de juegos, pero también hay algo llamado playground_equip_id_seqque es del tipo sequence. Esta es una representación del serialtipo que le diste a tu equip_idcolumna. Esto lleva un registro del siguiente número en la secuencia y se crea automáticamente para columnas de este tipo.

Si solo desea que se devuelva la tabla sin la secuencia, puede escribir:

dt

Output          List of relations Schema |    Name    | Type  | Owner--------+------------+-------+------- public | playground | table | sammy(1 row)

Paso 7: agregar, consultar y eliminar datos en una tabla

Ahora que tienes una tabla, puedes insertar algunos datos en ella.

A modo de ejemplo, agregue una diapositiva y un columpio llamando a la tabla que desea agregar, nombrando las columnas y luego proporcionando datos para cada columna, de la siguiente manera:

INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2017-04-28');
INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2018-08-16');

Debe tener cuidado al ingresar los datos para evitar algunos errores comunes. Por un lado, no coloque los nombres de las columnas entre comillas, pero los valores de las columnas que ingrese sí deben estar entre comillas.

Otra cosa que hay que tener en cuenta es que no se debe introducir un valor para la equip_idcolumna, ya que este se genera automáticamente cada vez que se crea una nueva fila en la tabla.

Recupere la información que ha agregado escribiendo:

SELECT * FROM playground;

Output equip_id | type  | color  | location  | install_date----------+-------+--------+-----------+--------------        1 | slide | blue   | south     | 2017-04-28        2 | swing | yellow | northwest | 2018-08-16(2 rows)

Esta salida indica que su equip_idformulario se ha completado correctamente y que todos los demás datos se han organizado correctamente.

Si el tobogán del patio de juegos se rompe y tienes que quitarlo, también puedes eliminar la fila de tu mesa escribiendo:

DELETE FROM playground WHERE type = 'slide';

Consulta la tabla nuevamente:

SELECT * FROM playground;

Output equip_id | type  | color  | location  | install_date----------+-------+--------+-----------+--------------        2 | swing | yellow | northwest | 2018-08-16(1 row)

Tenga en cuenta que su diapositiva ya no es parte de la tabla.

Paso 8: Agregar y eliminar columnas de una tabla

Después de crear una tabla, puede modificarla para agregar o eliminar columnas con relativa facilidad. Agregue una columna para mostrar la última visita de mantenimiento de cada equipo escribiendo lo siguiente:

ALTER TABLE playground ADD last_maint date;

La próxima vez que vuelva a ver la información de su tabla, se habrá agregado la nueva columna (pero no se habrá ingresado ningún dato):

SELECT * FROM playground;

Output equip_id | type  | color  | location  | install_date | last_maint----------+-------+--------+-----------+--------------+------------        2 | swing | yellow | northwest | 2018-08-16   |(1 row)

Para eliminar una columna, puede ingresar una declaración SQL muy similar a la que utilizó para agregar la last_maintcolumna. Si descubre que su equipo de trabajo utiliza una herramienta independiente para realizar un seguimiento del historial de mantenimiento, puede eliminar la columna escribiendo lo siguiente:

ALTER TABLE playground DROP last_maint;

Esto elimina la last_maintcolumna y todos los valores que se encuentran en ella, pero deja todos los demás datos intactos.

Paso 9: Actualizar datos en una tabla

Hasta ahora, ha aprendido cómo agregar registros a una tabla y cómo eliminarlos, pero este tutorial aún no ha cubierto cómo modificar las entradas existentes.

Puede actualizar los valores de una entrada existente consultando el registro que desea y configurando la columna con el valor que desea utilizar. Puede consultar el registro de “columpio” (esto coincidirá con todos los columpios de su tabla) y cambiar su color a “rojo”. Esto podría ser útil si le dio una mano de pintura al columpio:

UPDATE playground SET color = 'red' WHERE type = 'swing';

Puede verificar que la operación fue exitosa consultando los datos nuevamente:

SELECT * FROM playground;

Output equip_id | type  | color | location  | install_date----------+-------+-------+-----------+--------------        2 | swing | red   | northwest | 2010-08-16(1 row)

Como lo indica esta salida, su diapositiva ahora está registrada como roja.

Conclusión

Ya tiene instalado PostgreSQL en su servidor Ubuntu 18.04. Sin embargo, todavía queda mucho por aprender sobre Postgres. Aquí encontrará más guías que explican cómo utilizar Postgres:

• Una comparación de los sistemas de gestión de bases de datos relacionales
• Aprenda a crear y administrar tablas con Postgres
• Mejore la gestión de roles y permisos
• Consultas de Craft con Postgres con Select
• Aprenda a proteger PostgreSQL
• Aprenda a realizar una copia de seguridad de una base de datos Postgres