Cómo instalar, configurar y utilizar la última versión de PostgreSQL en Ubuntu

Postgres es una base de datos relacional de código abierto. Tiene muchas características avanzadas, como el rendimiento de clase empresarial, los triggers, la atomicidad, la consistencia, el aislamiento, la durabilidad y muchas otras características que harán de Postgres una buena opción para muchas aplicaciones complejas.

Instalación de PostgreSQL:

En este tutorial, descargamos la última versión de Postgres del sitio web oficial de PostgreSQLhttps://www.postgresql.org/download/linux/ubuntu/

PostgreSQL también está disponible en el repositorio de Ubuntu por defecto. Utilizando el repositorio apt dePostgreSQL desde su sitio oficial podemos instalar una versión específica de nuestra elección.

Para utilizar el repositorio apt, sigue estos pasos:

Elige tu versión de Ubuntu

Mi versión de Ubuntu es Bionic (18.04)

Crea el archivorepositorioPostgreSQL.

nano /etc/apt/sources.list.d/pgdg.list 

y añade una línea para el repositorio en pgdg.list

deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main

Si utilizas una versión de Ubuntu diferente a la 18.04 (Bionic), sustituye la palabra ‘bionic’ por el nombre de la versión de Ubuntu utilizada. Importa la clave de firma del repositorio, y actualiza las listas de paquetes

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

sudo apt-get update

Ahora instala el último servidor PostgreSQL en nuestro sistema Ubuntu utilizando los siguientes comandos.

sudo apt-get install postgresql postgresql-contrib

Después de la instalación, el servidorPostgreSQL se iniciará automáticamente.

Comprueba el estado del servicio PostgreSQL

systemctl status postgresql

Ahora verifica la conexión a PostgreSQL y la versión del servidor PostgreSQL.

sudo -u postgres psql -c "SELECT version();"

Esto conectará con PostgreSQLusando psql y dará laversión del servidor

 version 
-----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 11.4 (Ubuntu 11.4-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit

Utilizar los roles de PostgreSQL y los métodos de autenticación

PostgreSQL utiliza el concepto de rol de permiso de acceso a la base de datos. Un rol puede aplicarse a un usuario de la base de datos o a un grupo de usuarios de la misma.

PostgreSQL admite varios métodos de autenticación.

Los siguientes son los métodos de autenticación más utilizados en PostgreSQL.

1.Confianza

Los métodos de autenticación de confianza se pueden definir enpg_hba.conf, utilizando este método, el rol puede conectarse con una base de datos sin una contraseña.

2.Contraseña

Un rol puede conectarse con la base de datos probando la contraseña.

3.Ident

Los métodos de autenticación Ident sólo funcionan en conexiones TCP/IP.

4. Peer

Los métodos de autenticación Peer se utilizan sólo para la conexión local.

El archivo pg_hba.conf define los métodos de autenticación del cliente. podemos cambiar los métodos de autenticación editando los siguientes archivos.

nano /etc/postgresql/11/main/pg_hba.conf

En este archivo, PostgreSQL utiliza el método de autenticación peer por defecto. podemos cambiar los métodos de autenticación cambiando peer por otros métodos.

En el momento de la instalación de PostgreSQL, elusuario postgres será creado por defecto,postgres es el superusuario de la base de datos PostgreSQL, de forma similar al usuario root en el servidor de bases de datos MYSQL. este usuario ‘postgres’ también se crea en ubuntu.

Conectar con PostgreSQL

El siguiente comando para conectar con PostgreSQL.

sudo -i -u postgres

Ahora podemos conectarnos a un prompt de Postgres como MySQL escribiendo el siguiente comando.

psql

Obtendremos el siguiente prompt y estaremos listos para ejecutar consultas PostgreSQL en él.

Ten en cuenta que podemos conectarnos directamente al servidor de la base de datos simplemente escribiendo el siguiente comando.

sudo -u postgres psql

Creación de roles y base de datos en PostgreSQL

Ya tenemos el rol postgres en la base de datos, ahora creamos un nuevo rol utilizando los siguientes comandos.

root@ubuntu-pc:/home# sudo -u postgres createuser --interactive

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

Podemos listar los roles de usuario existentes utilizando el siguiente comando.

postgres=# \du

o ejecutar el siguiente comando desde el prompt de postgres.

SELECT rolname FROM pg_roles;

Podemos eliminar cualquier rol de usuario existente utilizando el comando DROP ROLE.

postgres=# DROP ROLE sohan;
DROP ROLE

Crear una base de datos PostgreSQL

Aquí podemos entrar con la cuenta postgres y ejecutar el siguiente comando.

postgres@ubuntu-pc:~$ createdb dummu

o simplemente podemos ejecutar los siguientes comandos con usuarios normales de ubuntu.

sudo -u postgres createdb dummu 

Entra en el prompt de postgres usando el nuevo rol

ejecuta directamente desde el terminal del sistema para crear un nuevo usuario.

sudo adduser sohan

Después de crear un nuevo usuario en el sistema, simplemente ejecuta

sudo -u sohan psql

o escribir el siguiente comando

sudo -i -u sohan
psql

Podemos conectarnos a una base de datos diferente simplemente proporcionando el nombre de la base de datos como

psql -d dummu

Después de conectarnos a la base de datos anterior, podemos obtener la información de esa base de datos mediante un simple comando

dummu=> \conninfo

output
You are connected to database "dummu" as user "sohan" via socket in "/var/run/postgresql" at port "5432"UPDATE dummu SET password = 'postgres' where username = 'sohan';

Crear tablas en PostgreSQL

Después de crear los roles, la base de datos y crear una conexión con el servidor de la base de datos, ahora crearemos una tabla.

CREATE TABLE dummu(user_id serial PRIMARY KEY,username VARCHAR (50) UNIQUE NOT NULL, password VARCHAR (50) NOT NULL, email VARCHAR (355) UNIQUE NOT NULL, created_on TIMESTAMP NOT NULL, last_login TIMESTAMP);

El comando anterior creará una nueva tabla dummy al igual que MySQL, podemos comprobar esta nueva tabla escribiendo

\d

Aquí veremos también la tabla dummu_user_id_seq, que llevará la cuenta del siguiente número de la secuencia.

Si sólo queremos mostrar el nombre de la tabla sin la secuencia, ejecuta el siguiente comando.

\dt

Esto sólo mostrará la tabla dummu

sohan=> \dt

Output

          List of relations
 Schema |    Na    | Type  | Owner 
--------+------------+-------+-------
 public | dummu      | table | soha

Añadir datos a la tabla

Ahora añadiremos datos a la tabla dummu escribiendo

Insert into dummu(user_id,username,password,email,created_on,last_login) values (1,'sohan','123456','[email protected]','2019-06-24 11:11:11','2019-06-24 11:11:11');

Ahora recuperaremos los datos de la tabla dummu,

SELECT * FROM dummu;

Borrar columna de la tabla

Ahora vamos a eliminar los datos de esta tabla

DELETE FROM dummu WHERE email = '[email protected]';

Alterar la tabla PostgreSQL

De forma similar podemos modificar la tabla utilizando la consulta alter como

ALTER TABLE dummu DROP email;

Esto eliminará el campo email de la tabla dummu.

Verifícalo con

SELECT * FROM dummu;

output:-

user_id | username | password |     created_on      |     last_login      
---------+----------+----------+---------------------+---------------------
       1 | sohan    | 123456   | 2019-06-24 11:11:11 | 2019-06-24 11:11:11

Actualizar datos en una tabla

Ahora veremos cómo actualizar los datos de una tabla

ejecuta el siguiente comando para actualizar la contraseña en la tabla dummu

UPDATE dummu SET password = 'postgres' where username = 'sohan';

verifica los cambios simplemente escribiendo

SELECT * FROM dummu;

output

user_id | username | password |     created_on      |     last_login      
---------+----------+----------+---------------------+---------------------
       1 | sohan    | postgres | 2019-06-24 11:11:11 | 2019-06-24 11:11:11

En resumen, en este tutorial aprendemos a instalar PostgreSQL, a crear un rol y una base de datos y a ejecutar un comando básico de base de datos.