Cómo instalar PostgreSQL en Ubuntu 20.04 LTS

Introducción

PostgreSQL, o Postgres, es un sistema de gestión de bases de datos relacionales. Tiene la ventaja de ser compatible con los estándares y con muchas funciones avanzadas.

En este tutorial explicaremos cómo instalar PostgreSQL en un VPS con Ubuntu 20.04 LTS Focal Fossa.

Si desea instalar PostgreSQL en un servidor remoto, continúe leyendo; de lo contrario, omita el primer párrafo "Conectando al servidor" y lea el siguiente.

Conexión al servidor

Para acceder al servidor, necesita conocer la dirección IP. También necesitará su nombre de usuario y contraseña para la autenticación. Para conectarse al servidor como root, escriba el siguiente comando:

ssh root@IP_DEL_SERVER

A continuación, deberá ingresar la contraseña del usuario root.

Si no está utilizando el usuario root, puede iniciar sesión con otro nombre de usuario utilizando el mismo comando, luego cambie root a su nombre de usuario:

ssh nome_utente@IP_DEL_SERVER

Luego se le pedirá que ingrese su contraseña de usuario.

El puerto estándar para conectarse a través de ssh es 22, si su servidor usa un puerto diferente, deberá especificarlo usando el parámetro -p, luego escriba el siguiente comando:

ssh nome_utente@IP_DEL_SERVER -p PORTA

Ahora está conectado a su servidor, está listo para comenzar a instalar Postgres.

Instalar PostgresSQL

Puede instalar PostgresSQL usando el repositorio oficial de Ubuntu con apt.

Actualice la lista de paquetes:

sudo apt update

Instalar PostgresSQL

sudo apt install postgresql postgresql-contrib

Utilice PostgresSQL

Por defecto, Postgres usa un concepto llamado roles para manejar la autenticación y autorización, similar a las cuentas normales de estilo Unix.

En la instalación, Postgres está configurado para usar la autenticación de identificación, lo que significa que asigna los roles de Postgres al usuario del sistema Linux correspondiente. Si existe un rol dentro de Postgres, con el mismo nombre que un usuario de Linux, podrán iniciar sesión como ese rol.

El procedimiento de instalación creó una cuenta de usuario llamada postgres asociada con el rol predeterminado de Postgres.

Los permisos de acceso a la base de datos en PostgreSQL se administran con el concepto de roles. Un rol puede representar un usuario de base de datos o un grupo de usuarios de base de datos.

PostgreSQL admite varios métodos de autenticación. Los métodos más utilizados son:

Confianza: un rol puede conectarse sin contraseña, siempre que se cumplan las condiciones definidas en pg_hba.conf.
Contraseña: un rol puede conectarse proporcionando una contraseña. Las contraseñas se pueden almacenar como scram-sha-256, md5 y password (en texto sin cifrar).
Ident: solo se admite en conexiones TCP /IP. Funciona al obtener el nombre de usuario del sistema operativo del cliente, con una asignación de nombre de usuario opcional.
Peer: idéntico a Ident, pero solo se admite en conexiones locales.

La autenticación del cliente PostgreSQL se define en el pg_hba.conf configuración llamado pg_hba.conf. De forma predeterminada, PostgreSQL usa el método de autenticación de pares para conexiones locales.

El postgres se crea automáticamente cuando instala PostgreSQL. Este usuario es el superusuario de la instancia de PostgreSQL y es equivalente al usuario raíz de MySQL.

Hay varios procedimientos para usar esta cuenta y acceder a postgres.

Para iniciar sesión con el usuario de postgres, desde la terminal:

sudo -i -u postgres

Luego, para acceder al indicador de Postgres y administrar la base de datos, escriba;

psql

psql (12.2 (Ubuntu 12.2-4))
 Type "help" for help.

 postgres=#

Para salir del indicador de Postgres, escriba:

\q

Crear un nuevo rol

Actualmente, solo tiene el rol de postgres configurado dentro de la base de datos. Puede crear nuevos roles desde la línea de comando con el comando createrole La --interactive le pedirá el nombre del nuevo rol y si habilitar los permisos de superusuario.

Si aún tiene el indicador de postgres activo, escriba:

\q

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

createuser --interactive

Si ha iniciado sesión con otra cuenta que aún no está asociada con postgres, use el siguiente comando:

sudo -u postgres createuser --interactive

Se le pedirá que ingrese un nombre para el nuevo rol y que le asigne privilegios de superusuario


Enter name of role to add: noviello
 Shall the new role be a superuser? (y/n) y

Crea una nueva base de datos

El sistema de autenticación de Postgres asocia de forma predeterminada el rol con una base de datos con el mismo nombre.

Entonces, si el usuario creado anteriormente se llama noviello, ese rol intentará conectarse a una base de datos que también se llama noviello por defecto. Puede crear la base de datos adecuada con el comando createdb.

Si ha iniciado sesión con la cuenta de postgres, puede crear una nueva base de datos con el siguiente comando:

createdb noviello

Si ha iniciado sesión con otra cuenta que aún no está asociada con postgres, use el siguiente comando:

sudo -u postgres createdb noviello

Para acceder al indicador de Postgres con el nuevo rol que se acaba de crear, debe existir un usuario de Linux, en este caso en Ubuntu, con el mismo nombre dado al rol y a la base de datos.

Si no hay ningún usuario con este nombre, puede crear uno con el comando adduser.

Realice este procedimiento con una cuenta de usuario que no sea postgres.

Para crear un nuevo usuario:

sudo adduser noviello

Ahora podemos conectarnos con el nuevo usuario a la base de datos y usar el indicador de postgres:

sudo -i -u noviello

psql

Para conectarse a una base de datos diferente con el usuario actual:

psql -d postgres

Para obtener información sobre la conexión actual:

\conninfo

Debería ver un mensaje de respuesta similar a este:


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

Agregar y eliminar datos en tablas

El siguiente es un ejemplo de creación de una tabla:

CREATE TABLE test (
 id serial PRIMARY KEY,
 esempio1 varchar (50) NOT NULL,
 esempio2 varchar (25) NOT NULL,
 esempio3 varchar(25) check (esempio3 in ('valore1', 'valore2', 'valore3', 'valore4', 'valore5', 'valore6', 'valore7', 'valore8')),
 datains date
 );

Para ver la tabla recién creada:

\d

Debería ver un mensaje de respuesta similar a este:


 List of relations
 Schema | Name | Type | Owner
 --------+-------------------------+----------+---------
 public | test | table | noviello
 public | testidseq | sequence | noviello
 (2 rows)

Para ver la tabla sin más información:

\dt

Debería ver un mensaje de respuesta similar a este:


 List of relations
 Schema | Name | Type | Owner
 --------+------------+-------+---------
 public | test | table | noviello
 (1 rows)

Para insertar nuevos valores en la tabla:

INSERT INTO test (esempio1, esempio2, esempio3, datains) VALUES ('valore1', 'valore2', 'valore3', '2018-06-29');

INSERT INTO test (esempio1, esempio2, esempio3, datains) VALUES ('valore4', 'valore5', 'valore6', '2018-06-29');

Para recuperar la información que acaba de ingresar:

SELECT * FROM test;

Debería ver un mensaje de respuesta similar a este:


 id | esempio1 | esempio2 | esempio3 | datains
 ----+-----------+-----------+-----------+------------
 1 | valore1 | valore2 | valore3 | 2018-06-29
 2 | valore4 | valore5 | valore6 | 2018-06-29
 (2 row)

Para eliminar una fila de la base de datos:

DELETE FROM test WHERE esempio1 = 'valore1';

Recupere la información de la base de datos nuevamente para verificar que la fila fue eliminada:

SELECT * FROM test;

Debería ver un mensaje de respuesta similar a este:


 id | esempio1 | esempio2 | esempio3 | datains
 ----+-----------+-----------+-----------+------------
 2 | valore4 | valore5 | valore6 | 2018-06-29
 (1 row)

Eliminar y agregar columnas en tablas

Para agregar una columna a la base de datos:

ALTER TABLE test ADD altro date;

Para ver los cambios, repita el siguiente comando:

SELECT * FROM test;

Debería ver un mensaje de respuesta similar a este:


 id | esempio1 | esempio2 | esempio3 | datains | altro
 ----+-----------+-----------+-----------+------------+-----------
 2 | valore4 | valore5 | valore6 | 2018-06-29 |
 (1 row)

Para eliminar una columna:

ALTER TABLE test DROP altro;

Para ver los cambios, repita el siguiente comando:

SELECT * FROM test;

Debería ver un mensaje de respuesta similar a este, por lo que sin la columna creada anteriormente:


 id | esempio1 | esempio2 | esempio3 | datains
 ----+-----------+-----------+-----------+------------
 2 | valore4 | valore5 | valore6 | 2018-06-29
 (1 row)

Cambiar los datos en una tabla

Para modificar los datos, debe especificar el nombre de la base de datos y la columna que desea modificar:

UPDATE test SET esempio2 = 'nuovovalore' WHERE esempio1 = 'valore4';

Para ver los cambios, repita el siguiente comando:

SELECT * FROM test;

Debería ver un mensaje de respuesta similar a este:


 id | esempio1 | esempio2 | esempio3 | datains
 ----+-----------+-----------+-----------+------------
 2 | valore4 | nuovovalore | valore6 | 2018-06-29
 (1 row)

Crear una base de datos y un rol de PostgreSQL

Solo los superusuarios y los roles con CREATEROLE pueden crear nuevos roles.

El siguiente ejemplo muestra cómo crear un nuevo rol llamado john y una base de datos llamada johndb y otorgar privilegios en la base de datos:

Cree un nuevo rol de PostgreSQL:

sudo su - postgres -c "createuser john"

Cree una nueva base de datos PostgreSQL:

sudo su - postgres -c "createdb johndb"

Para otorgar permisos al usuario en la base de datos, conéctese al shell de PostgreSQL:

sudo -u postgres psql

Ejecute la siguiente consulta:

grant all privileges on database johndb to john;

Habilite el acceso remoto al servidor PostgreSQL

De forma predeterminada, el servidor PostgreSQL solo escucha en la interfaz 127.0.0.1

Para habilitar el acceso remoto a su servidor PostgreSQL, abra el archivo de configuración postgresql.conf listen_addresses = '*' en la sección CONNECTIONS AND AUTHENTICATION

sudo nano /etc/postgresql/12/main/postgresql.conf

#------------------------------------------------------------------------------
 # CONNECTIONS AND AUTHENTICATION
 #------------------------------------------------------------------------------

 # - Connection Settings -

 listen_addresses = '*' # what IP address(es) to listen on;

/etc/postgresql/12/main/postgresql.conf

Guarde el archivo y reinicie el servicio PostgreSQL:

sudo service postgresql restart

Verifique los cambios con la utilidad ss

ss -nlt | grep 5432

El resultado muestra que el servidor PostgreSQL está escuchando en todas las interfaces 0.0.0.0

LISTEN 0 244 0.0.0.0:5432 0.0.0.0:*
 LISTEN 0 244 [::]:5432 [::]:*

El siguiente paso es configurar el servidor para aceptar conexiones remotas editando el pg_hba.conf.

A continuación, se muestran algunos ejemplos que muestran diferentes casos de uso:

# TYPE DATABASE USER ADDRESS METHOD

 # The user jane can access all databases from all locations using md5 password
 host all jane 0.0.0.0/0 md5

 # The user jane can access only the janedb from all locations using md5 password
 host janedb jane 0.0.0.0/0 md5

 # The user jane can access all databases from a trusted location (192.168.1.134) without a password
 host all jane 192.168.1.134 trust

/etc/postgresql/12/main/pg_hba.conf

El último paso es abrir el puerto 5432 su firewall.

Suponiendo que está utilizando UFW para administrar su firewall y desea permitir el acceso desde la 192.168.1.0/24, ejecute el siguiente comando:

sudo ufw allow proto tcp from 192.168.1.0/24 to any port 5432

Asegúrese de que el firewall esté configurado para aceptar conexiones solo de rangos de IP confiables.

Conclusión

La instalación de PostgreSQL en Ubuntu 20.04 LTS Focal Fossa ha finalizado.