Cómo instalar PostgreSQL en Ubuntu y uso efectivo de bases de datos

PostgreSQL es un sistema de gestión popular de bases de datos relacionales (RDBMS) de código abierto, conocido por su escalabilidad y eficacia en el manejo de grandes cantidades de datos.

Debido a su alto rendimiento, PostgreSQL es ideal para sitios web o aplicaciones de grandes empresas. También admite varios tipos de datos y lenguajes de programación, como PHP y Python.

En este tutorial, te explicaremos cómo instalar PostgreSQL en Ubuntu 22.04 y versiones posteriores. También cubriremos el proceso de instalación de pgAdmin y las soluciones a problemas comunes de PostgreSQL.

Cómo instalar PostgreSQL en Ubuntu

Antes de proceder, asegúrate de que tu servidor privado virtual (VPS) ejecuta Ubuntu 22.04 o posterior. De lo contrario, sigue nuestra guía para cambiar el sistema operativo en el VPS de Hostinger.

1. Instala PostgreSQL

La instalación de PostgreSQL implica ejecutar comandos Linux en tu máquina. En un sistema local, ejecútalos directamente a través de Terminal, el símbolo del sistema de Linux.

Para una máquina remota como VPS, conéctate usando una aplicación SSH o Terminal. Los usuarios de Hostinger pueden encontrar las credenciales de acceso a su máquina remota navegando a Visión general del VPS → Acceso SSH en hPanel.

Puedes instalar los paquetes PostgreSQL de Ubuntu desde el repositorio local o utilizando el gestor de paquetes APT. Te recomendamos esto último para asegurarte de que obtienes la última versión.

Opción 1: instalación desde el repositorio APT

El método más popular para instalar PostgreSQL en Ubuntu es desde el repositorio oficial a través del gestor de paquetes APT. Aquí tienes los pasos para hacerlo:

1. Ejecuta estos comandos para importar la clave desde el repositorio oficial de PostgreSQL al gestor de paquetes APT:

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

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

2. Actualiza APT para sincronizar todos los repositorios utilizando este comando:

sudo apt update

3. Instala la última versión de PostgreSQL con APT y privilegios sudo:

sudo apt-get -y install postgresql

4. Para confirmar la instalación, ejecuta este comando:

sudo -u postgres psql

5. Introduce la sentencia de consulta de la versión de PostgreSQL:

SELECT version();

Opción 2: instalación utilizando el repositorio local de Ubuntu

El método alternativo de instalación de PostgreSQL utiliza el repositorio local de Ubuntu. Aquí tienes los pasos:

1. Ejecuta este comando para actualizar el repositorio de Ubuntu:

sudo apt-get update

2. Verifica el repositorio apt PostgreSQL disponible utilizando este comando:

sudo apt show postgresql

3. Ejecuta este comando para instalar la última versión disponible. En nuestro caso, es la versión 12.15:

sudo apt install postgresql postgresql-contrib

4. Para comprobar si la instalación de PostgreSQL se ha realizado correctamente, ejecuta lo siguiente:

sudo -u postgres psql

5. A continuación, ejecuta este comando para comprobar la versión actual de PostgreSQL:

SELECT version();

La línea de comandos debería mostrar que la versión 12.15 del servidor PostgreSQL se ha instalado correctamente, de forma similar a esto:

2. Comprueba el estado de PostgreSQL

Tras instalar PostgreSQL, comprueba si está habilitado y activo. Utiliza la utilidad de comandos systemctl o service para verificar el estado del servicio:

sudo service postgresql status

sudo systemctl status postgresql

Si PostgreSQL está activado, en tu línea de comandos aparecerá activo y cargado, de forma similar a la siguiente:

Además del estado del servicio, comprueba si PostgreSQL está preparado para aceptar una conexión mediante este comando:

sudo pg_isready

3. Conectate a PostgreSQL

Durante la instalación, PostgreSQL crea automáticamente el rol postgres por defecto para iniciar sesión en la base de datos. Para cambiar a este usuario, ejecuta el siguiente comando:

sudo su - postgres

Tu línea de comandos debe comenzar ahora con postgres. Para conectarte a la base de datos PostgreSQL, utiliza el intérprete de comandos PostgreSQL con lo siguiente:

sudo psql

El comando psql creará una sesión en la base de datos por defecto. Si no encuentras el comando, instala manualmente el cliente PostgreSQL utilizando este comando:

sudo apt-get install postgresql-cliente

Para comprobar el estado de la conexión, ejecuta este comando desde el prompt de PostgreSQL:

\conninfo

Para salir del prompt de PostgreSQL y volver al usuario normal del sistema, utiliza el comando exit. Mientras tanto, usa \q para salir de la sesión interactiva de la base de datos.

4. Cambia la contraseña de usuario

Cambiar la contraseña predeterminada del rol postgres es esencial para mejorar la seguridad de la base de datos. A continuación te explicamos cómo hacerlo:

1. Activa el indicador PostgreSQL con este comando:

sudo -u postgres psql

2. Ejecuta esta sentencia para cambiar la contraseña de la cuenta postgres. No olvides sustituir NewPassword por tu contraseña real:

ALTER USER postgres PASSWORD 'NewPassword';

3. Comprueba si el cambio se ha aplicado correctamente utilizando el siguiente comando:

\p

4. Reinicia el servicio PostgreSQL con este comando:

sudo service postgresql restart

5. Crea un usuario en PostgreSQL

Para gestionar el acceso a la base de datos y garantizar una organización adecuada de los datos, necesitarás varios usuarios PostgreSQL. La forma más sencilla de crear uno es utilizar el modo interactivo. Aquí los pasos:

1. Cambia al usuario postgres:

sudo su - postgres

2. Ejecuta este comando para crear un nuevo usuario:

createuser --interactive

3. Introduce el nombre de la nueva cuenta PostgresQL. Para este tutorial, es TheNewUser.
4. Para validar el nuevo rol, inicia sesión en PostgreSQL con la cuenta postgres:

sudo -u postgres psql

5. Comprueba todos los usuarios con el siguiente comando:

\du

Como se muestra en la salida, la base de datos crea con éxito TheNewUser.

6. Crea una base de datos en PostgreSQL

PostgreSQL asume que el rol y la base de datos tendrán el mismo nombre. Por tanto, si creas un rol TheNewUser, PostgreSQL accederá a una base de datos con el mismo nombre.

Estos son los pasos de configuración de la base de datos PostgreSQL:

1. Ejecuta lo siguiente para cambiar al usuario postgres y crear una nueva base de datos:

sudo -u postgres createdb TheNewUser

2. Para acceder a la nueva base de datos, crea una cuenta de usuario Linux con el mismo nombre. Como el nombre de usuario contiene mayúsculas, añade la opción –force-badname.

sudo adduser TheNewUser --force-badname

3. Entra en el prompt interactivo de PostgreSQL utilizando la nueva cuenta de usuario:

sudo -u TheNewUser psql

4. Confirma la conexión mediante este comando:

\conninfo

Ahora el usuario debería conectarse a la nueva base de datos y poder crear una nueva tabla. Por ejemplo, introduce lo siguiente para crear una tabla de datos de comprador:

CREATE TABLE buyers(usr_id INT PRIMARY KEY, usr_name VARCHAR(240) NOT NULL, usr_location VARCHAR(240) NOT NULL);

Al crear una tabla de base de datos, ten en cuenta las seis restricciones de PostgreSQL:

• PRIMARY KEY: indica que una columna puede ser el identificador único de una fila.
• FOREIGN KEY: especifica que los valores de una columna deben ser iguales a los de las filas de otra tabla.
• NOT NULL: garantiza que el valor de una columna no está vacío.
• CHECK: establece el valor de una columna para que deba satisfacer una expresión booleana, que puede ser “verdadero” o “falso”.
• UNIQUE: garantiza que los valores de una columna son únicos entre todas las filas.
• EXCLUSION: impide que los valores de dos filas se superpongan al compararlos.

Para añadir valores a tu tabla, utiliza la siguiente sentencia. No olvides sustituir los marcadores de posición por el valor apropiado y ajustar el número de filas en consecuencia:

INSERT INTO tablename (columna1, columna2, columna3) VALUES (fila1, fila2, fila3);

Para mostrar la tabla, introduce la siguiente sentencia:

SELECT * FROM tablename

7. Configura el servidor PostgreSQL

Configura tu servidor PostgreSQL para permitir que otros sistemas accedan a la base de datos de forma remota. Para ello, deja que el servidor liste todas las direcciones IP editando el archivo de configuración.

Antes de continuar, utiliza el comando exit para salir del intérprete de comandos PostgreSQL y volver como usuario normal del sistema. A continuación, sigue estos pasos:

1. Abre el archivo de configuración con un editor de texto. Sustituye vim si utilizas otro editor y cambia el directorio en consecuencia:

vim /etc/postgresql/14/main/postgresql.conf

2. Busca la línea listen_addresses.
3. Elimina el símbolo # y cambia el valor por un asterisco. Así es como debería quedar:

listen_addresses = '*'

4. Utiliza el comando :wq para guardar los cambios y salir de vim.

5. Establece la política de acceso para autenticar la conexión del cliente a tu servidor PostgreSQL. Para ello, abre el archivo pg_hba.conf en el mismo directorio utilizando vim. El comando debería tener este aspecto

vim /etc/postgresql/14/main/pg_hba.conf

6. Añade una nueva política al final del archivo utilizando esta sintaxis:

connection database user ip_address encryption

7. Por ejemplo, habilitaremos todas las conexiones TCP/IP de todos los usuarios a todas las bases de datos PostgreSQL. Estos usuarios pueden utilizar cualquier dirección IP con una contraseña cifrada con MD5. La regla tiene el siguiente aspecto:

host all all 0.0.0.0/0 md5

8. Reinicia el servicio PostgreSQL para aplicar los cambios.

8. Establece una conexión remota a la base de datos

Tras configurar la base de datos, configura el acceso remoto a PostgreSQL mediante el comando psql. Para ello, utiliza esta sintaxis de comando en la máquina cliente:

psql -h ip_address -p port -d database -U username

Por ejemplo, ejecuta este comando para conectarte a la base de datos TheNewUser alojada en un VPS con la dirección IP 185.185.185.185 utilizando la cuenta TheNewUser:

psql -h 185.185.185.185 -p 5432 -d TheNewUser -U TheNewUser

Recuerda que el puerto PostgreSQL por defecto es 5423. Para comprobar si la conexión se ha realizado correctamente, ejecuta este comando:

\conninfo

Una vez conectado, puedes enviar consultas a la base de datos para recuperar datos. Para habilitar el acceso remoto PostgreSQL en tu aplicación, instala un controlador de base de datos e importa el módulo en tu código.

En este tutorial, te mostraremos cómo hacerlo en una aplicación Python alojada en un VPS Ubuntu 22.04. Tras conectarte via SSH, sigue estos pasos:

1. Instala el gestor de paquetes pip utilizando este comando. Si ya lo tienes, salta este paso:

sudo apt-get install python-pip

2. Descarga e instala el controlador de base de datos psycopg2 utilizando pip:

pip install psycopg2-binario

3. Añade la siguiente línea al código de tu aplicación para importar el módulo y establecer una conexión con la base de datos PostgreSQL. Recuerda cambiar el valor respectivamente:

import psycopg2
# Connect your app to the remote database.
conn = psycopg2.connect(host="182.182.182.182", port="5432", dbname="TestNewUser", user="TestNewUser", password="UserPassword")
# Open a cursor for database operations
cur = conn.cursor()
# Execute a query. Replace table with your own. 
cur.execute("SELECT * FROM table")
# Retrieve data
records = cur.fetchall()

9. Instala pgAdmin

Instala la interfaz gráfica de usuario basada en web pgAdmin para simplificar tus tareas de gestión de bases de datos PostgreSQL. En este caso, utilizaremos el gestor de paquetes APT para instalarlo:

1. Ejecuta estos comandos individualmente para añadir el repositorio oficial de pgAdmin:

curl -fsSL https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/pgadmin.gpg

sudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list'

2. Actualiza las listas de paquetes para sincronizar el repositorio.

sudo apt update

3. Ejecuta el siguiente comando para instalar pgAdmin:

sudo apt install pgadmin4

4. Una vez finalizada la configuración de la GUI, inicia la configuración primaria de pgAdmin con este comando:

sudo /usr/pgadmin4/bin/setup-web.sh

5. Introduce tu dirección de correo electrónico y tu contraseña. Debe tener el siguiente aspecto:

Para acceder a la interfaz basada en web de pgAdmin, introduce lo siguiente en la barra de direcciones de tu navegador web:

185.185.185.185/pgadmin4

Sustituye la dirección IP por la de tu VPS. A continuación, introduce tu dirección de correo electrónico y tu contraseña. Haz clic en Iniciar sesión para acceder al panel principal de pgAdmin.

Para conectar PostgreSQL, selecciona Añadir nuevo servidor y continúa con el proceso de configuración.

Solución de problemas de instalación de PostgreSQL

En esta sección, exploraremos los problemas más comunes relacionados con la instalación de PostgreSQL y sus soluciones.

1. No se puede conectar al servidor PostgreSQL

En primer lugar, debes comprobar si el sistema cliente está correctamente conectado a Internet. Si la conexión a Internet no es el problema, otras causas habituales de este error son una base de datos inactiva o un permiso de archivo mal configurado.

Comprueba el estado de PostgreSQL y asegúrate de que está cargado y activo. Si no es así, reinícialo utilizando el siguiente comando:

sudo systemctl restart postgresql

Si eso no funciona, comprueba si los permisos de directorio y archivos de PostgreSQL están configurados a 0700 y 0600. Para ello, utiliza el siguiente comando:

ls /var/lib/postgresql/main

Después, utiliza el comando chmod para cambiar sus permisos.

2. Problemas de conexión PostgreSQL desde el host remoto

Una política de conexión mal configurada puede causar problemas de host remoto que te impidan acceder al servidor PostgreSQL. Para solucionarlo, abre tu archivo pg_hba.conf y añade la política de acceso de la dirección IP de cada cliente:

host all all 0.0.0.0/0 md5

A continuación, comprueba el valor listen_address de tu archivo de configuración postgres.conf. Asegúrate de que tiene un asterisco para garantizar que PostgreSQL puede escuchar todas las direcciones IP.

Si eso no funciona, comprueba si tu cortafuegos cierra el puerto de escucha PostgreSQL. Para el servidor Ubuntu, ejecuta el comando ufw:

sudo ufw status verbose

Si la conexión desde y hacia el puerto 5432 está desactivada, ábrela utilizando el siguiente comando:

sudo ufw allow 5432/tcp

3. Problemas de inicio de sesión en pgAdmin

Los problemas de inicio de sesión en pgAdmin pueden deberse a un error de conexión con la base de datos. Si está relacionado con la red, prueba las soluciones anteriores y vuelve a instalar pgAdmin.

Si el problema está relacionado con las credenciales de acceso a la cuenta de usuario, entra en la sesión psql y restablece la contraseña utilizando esta sentencia:

ALTER USER username WITH PASSWORD 'new_password';

También puedes encontrarte con problemas de inicio de sesión si el usuario no tiene el privilegio LOGIN. Para concederlo, utiliza la siguiente sentencia:

ALTER USER username WITH LOGIN;

4. Conflicto de versiones de PostgreSQL

Los conflictos de versión se producen cuando PostgreSQL es incompatible con la base de datos u otras aplicaciones. Su causa habitual es no actualizar PostgreSQL, lo que provoca un desajuste de versiones.

Para solucionarlo, reinstala o actualiza PostgreSQL desde el repositorio oficial de APT. Actualiza también todas las aplicaciones de terceros, ya que pueden estar obsoletas.

Recomendamos probar la versión más reciente en un entorno de desarrollo para evitar más problemas en el servidor activo.

5. No se puede crear un usuario o base de datos PostgreSQL

Una sintaxis de comando incorrecta suele provocar errores en la creación de usuarios y bases de datos PostgreSQL. Para solucionarlo, asegúrate de que tu comando termina con punto y coma (;).

Además, debes ejecutar el comando utilizando el usuario postgres y la línea de comandos psql. Para ello, ejecuta este comando:

sudo -u postgres psql

Si tu Terminal muestra lo siguiente, deberías poder crear un usuario y una base de datos.

postgres=#

Conclusión

PostgreSQL es un sistema de gestión de bases de datos de código abierto impulsado por la comunidad. Utiliza una estructura lógica para almacenar eficazmente grandes cantidades de datos y simplificar el acceso.

En este tutorial sobre el sistema de gestión de bases de datos relacionales, hemos explicado los pasos para instalar PostgreSQL en Ubuntu 22.04 mediante el símbolo del sistema de Linux. Aquí el resumen:

1. Instala PostgreSQL desde el repositorio oficial utilizando APT.
2. Ejecuta systemctl status para comprobar si Postgresql está activo.
3. Conéctate a la cuenta PostgreSQL utilizando sudo su – postgres.
4. Cambia la contraseña de la cuenta de administrador por defecto para mejorar la seguridad.
5. Crea un nuevo usuario PostgreSQL para gestionar tus bases de datos.
6. Haz una nueva base de datos utilizando el comando createdb.
7. Configura el servidor PostgreSQL configurando su política de conexión.
8. Ajusta una conexión a tu base de datos PostgreSQL remota.
9. Instala pgAdmin para disponer de una interfaz gráfica de usuario.

Para evitar problemas, asegúrate de que las políticas de conexión y las credenciales de acceso a pgAdmin están configuradas correctamente.

Además, instala la última versión del RDBMS mediante APT para evitar problemas de incompatibilidad.

Cómo instalar PostgreSQL en Ubuntu – Preguntas frecuentes

En esta sección, responderemos a varias preguntas frecuentes sobre la instalación de PostgreSQL en Ubuntu.