PostgreSQL 14 y Postgis en Ubuntu

Este manual proporciona una guía detallada para instalar PostgreSQL 14 y PostGIS en Ubuntu. PostgreSQL es un sistema de gestión de bases de datos relacional de código abierto, mientras que PostGIS es una extensión de PostgreSQL que permite trabajar con datos geoespaciales. El manual cubre todos los pasos necesarios para instalar y configurar PostgreSQL y PostGIS en Ubuntu, incluyendo la instalación de dependencias y la configuración de la base de datos. Además, el manual también cubre la instalación del entorno gráfico de pgAdmin4, que es una herramienta popular para administrar bases de datos PostgreSQL. Con esta guía, los usuarios podrán instalar y configurar fácilmente PostgreSQL 14 y PostGIS en Ubuntu, así como utilizar pgAdmin4 para administrar sus bases de datos de manera eficiente.

 sudo apt update && sudo apt upgrade


Este comando de Ubuntu es una combinación de dos comandos separados por el operador. El primer comando es sudo apt update, que actualiza la lista de paquetes disponibles en los repositorios de software configurados en el sistema. El segundo comando es sudo apt upgrade, que actualiza los paquetes instalados en el sistema a sus últimas versiones disponibles:

sudo apt -y install gnupg2 wget vim

El comando sudo apt -y install gnupg2 wget vim es un comando de Ubuntu que se utiliza para instalar los paquetes de software gnupg2, wget y vim.

    gnupg2 es una herramienta de cifrado de datos que se utiliza para cifrar y descifrar archivos y correos electrónicos.

    wget es una herramienta de línea de comandos que se utiliza para descargar archivos de Internet.

    vim es un editor de texto avanzado que se utiliza para editar archivos de texto en la línea de comandos.

El comando sudo se utiliza para ejecutar el comando con privilegios de administrador. El parámetro -y se utiliza para aceptar automáticamente cualquier confirmación de instalación que se solicite durante la instalación de los paquetes.


sudo apt-cache search postgresql | grep postgresql

Este comando de Ubuntu se utiliza para buscar paquetes relacionados con PostgreSQL en los repositorios de paquetes de Ubuntu. La primera parte del comando, sudo apt-cache search postgresql, busca todos los paquetes que contienen la palabra "postgresql" en su nombre o descripción. La segunda parte del comando, | grep postgresql, filtra los resultados de la búsqueda anterior y muestra solo las líneas que contienen la palabra "postgresql".


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

Aquí se utiliza el comando echo para escribir una línea en el archivo /etc/apt/sources.list.d/pgdg.list.  Esta línea especifica la ubicación del repositorio de paquetes de PostgreSQL. La parte $(lsb_release -cs) se utiliza para obtener el nombre del código de versión de Ubuntu en el que se está ejecutando el comando. Esto se utiliza para asegurarse de que se agregue la versión correcta del repositorio de paquetes de PostgreSQL. El comando sudo se utiliza para ejecutar el comando como superusuario, lo que permite escribir en el archivo /etc/apt/sources.list.d/pgdg.list.


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

El comando wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - descarga el archivo ACCC4CF8.asc desde la URL https://www.postgresql.org/media/keys/ACCC4CF8.asc y lo pasa como entrada a apt-key add para agregar la clave pública de PostgreSQL al sistema. Desglosemos el comando en partes:

wget: es un comando de Linux que permite descargar archivos de la web.

--quiet: es una opción que hace que wget no muestre información de progreso en la pantalla.

-O -: es otra opción que indica que la salida de wget debe ser redirigida a la salida estándar (stdout) en lugar de guardarla en un archivo.

https://www.postgresql.org/media/keys/ACCC4CF8.asc: es la URL del archivo que se va a descargar.

|: es el operador de tubería (pipe) que permite pasar la salida de un comando como entrada a otro.

sudo apt-key add -: es un comando que agrega una clave pública al sistema. El guión (-) al final indica que la entrada se leerá desde la entrada estándar (stdin).



sudo apt -y update

el comando sudo apt -y update se utiliza para actualizar la lista de paquetes disponibles en los repositorios de software del sistema de Ubuntu sin necesidad de confirmar manualmente cada pregunta que pueda hacer el comando.

sudo apt -y install postgresql-14

instala la versión 14 de PostgreSQL en el sistema Ubuntu sin necesidad de confirmación manual.

systemctl status postgresql

Este comando es útil para verificar si el servicio de PostgreSQL está en ejecución o no, y para obtener información sobre su estado actual.



otros comandos utiles son:

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

    -u postgres especifica que el usuario de la base de datos PostgreSQL al que se conectará es postgres.

    psql es el comando de PostgreSQL que se utiliza para conectarse a una base de datos y ejecutar consultas SQL.

    -c indica que se va a ejecutar una consulta SQL en la línea de comandos.

    "SELECT version();" es la consulta SQL que se va a ejecutar. En este caso, se selecciona la versión de PostgreSQL que se está ejecutando.

En resumen, este comando se utiliza para conectarse a una base de datos PostgreSQL como el usuario postgres y seleccionar la versión de PostgreSQL que se está ejecutando.

Una vez que PostgreSQL está instalado, puedes ejecutarlo desde la terminal usando el siguiente comando:  sudo -u postgres psql

Si deseas conectarte a una base de datos específica, puedes usar el siguiente comando:

sudo -u postgres psql nombre_de_la_base_de_datos

para salir de PostgreSQL desde la terminal de Ubuntu, puedes seguir los siguientes pasos:

Si estás conectado a una base de datos de PostgreSQL, escribe el siguiente comando para salir de la sesión actual: \q

Si estás conectado a PostgreSQL como superusuario, escribe el siguiente comando para salir de la sesión actual: exit

Si estás conectado a PostgreSQL como un usuario normal, escribe el siguiente comando para salir de la sesión actual:  logout




Apartado:

Conéctate a la base de datos de PostgreSQL 14.

Hay dos formas de conectarse a una instancia de PostgreSQL.

1 Ejecutando el comando de Postgres directamente con sudo.

sudo -u postgres psql

2 Para esta opción, primero debe cambiar al usuario de Postgres creado después de la instalación de PostgreSQL 14

sudo -i -u postgres

El comando sudo -i -u postgres en Ubuntu se utiliza para cambiar al usuario postgres y obtener acceso a su shell.

sudo es un comando que permite a los usuarios ejecutar comandos con los privilegios de otro usuario, generalmente el usuario root o superusuario.

-i indica que se debe iniciar una sesión de shell interactiva con el usuario especificado.

-u postgres especifica que el usuario postgres es el usuario cuya sesión de shell se iniciará.

Por lo tanto, al ejecutar este comando, se iniciará una sesión de shell interactiva con el usuario postgres, lo que permitirá al usuario actual ejecutar comandos como el usuario postgres. Esto es útil, por ejemplo, para realizar tareas de administración de bases de datos PostgreSQL, ya que el usuario postgres es el usuario predeterminado utilizado por PostgreSQL para realizar estas tareas.

psql

El comando $ psql en Ubuntu es utilizado para acceder a la línea de comandos de PostgreSQL.


Configurar la instancia de PostgreSQL 14 para acceso remoto.


sudo sed -i '/^host/s/ident/md5/' /etc/postgresql/14/main/pg_hba.conf


Este comando de Ubuntu utiliza la herramienta sed para editar el archivo /etc/postgresql/14/main/pg_hba.conf. El archivo pg_hba.conf es un archivo de configuración de PostgreSQL que especifica cómo se autentican los usuarios que intentan conectarse al servidor de base de datos. El comando sed se utiliza para buscar y reemplazar texto en un archivo. En este caso, el comando busca la línea que comienza con la palabra "host" en el archivo pg_hba.conf. La expresión regular ^host busca la cadena "host" al principio de la línea. Si encuentra una línea que comienza con "host", el comando reemplaza la palabra "ident" con "md5" en esa línea. El parámetro -i se utiliza para editar el archivo pg_hba.conf directamente, en lugar de imprimir la salida en la pantalla. Esto significa que el archivo se modificará permanentemente. El comando sudo se utiliza para ejecutar el comando como superusuario, lo que es necesario para editar el archivo pg_hba.conf, ya que es un archivo de sistema que requiere permisos especiales para modificarlo.

sudo sed -i '/^local/s/peer/trust/' /etc/postgresql/14/main/pg_hba.conf

Este comando de Ubuntu utiliza la herramienta sed para editar el archivo /etc/postgresql/14/main/pg_hba.conf. En particular, el comando reemplaza la palabra "peer" por "trust" en todas las líneas que comienzan con la palabra "local" en el archivo pg_hba.conf. El comando sudo se utiliza para ejecutar el comando como superusuario, lo que permite realizar cambios en el archivo pg_hba.conf. El modificador -i se utiliza para editar el archivo en su lugar, es decir, para modificar el archivo pg_hba.conf directamente en lugar de crear una copia del archivo y modificar la copia. La expresión /^local/s/peer/trust/ es un patrón de búsqueda y reemplazo que se utiliza con el comando sed. La expresión /^local/ busca todas las líneas que comienzan con la palabra "local". La expresión s/peer/trust/ reemplaza la palabra "peer" por "trust" en todas las líneas que cumplen con el patrón de búsqueda anterior.

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

El comando sudo vim /etc/postgresql/14/main/pg_hba.conf en Ubuntu se utiliza para editar el archivo de configuración pg_hba.conf de PostgreSQL versión 14. Este archivo es utilizado por PostgreSQL para autenticar a los usuarios y determinar si tienen permiso para acceder a la base de datos. La parte sudo del comando se utiliza para ejecutar el comando como superusuario, lo que es necesario para editar el archivo de configuración. vim es un editor de texto en la terminal que se utiliza para editar el archivo pg_hba.conf. /etc/postgresql/14/main/ es la ruta del archivo de configuración, que se encuentra en el directorio /etc/postgresql/14/main/. 14 se refiere a la versión de PostgreSQL que se está utilizando, en este caso la versión 14.

En el archivo, se modifican o agregan estas lineas:

# IPv4 local connections:

host    all             all             127.0.0.1/32            scram-sha-256

host    all             all             0.0.0.0/0                md5

# IPv6 local connections:

host    all             all             ::1/128                 scram-sha-256

host    all             all             0.0.0.0/0                md5

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

se abre el archivo de configuración de PostgreSQL en el editor de texto Vim, lo que permite realizar cambios en la configuración del servidor de bases de datos PostgreSQL.

En el archivo, descomente y edite la línea como se muestra a continuación

#------------------------------------------------------------------------------

# CONNECTIONS AND AUTHENTICATION

#-----------------------------------------------------------------------------

.......

listen_addresses='*'

sudo systemctl restart postgresql

sudo systemctl enable postgresql


El comando sudo systemctl restart postgresql se utiliza en Ubuntu para reiniciar el servicio de PostgreSQL. PostgreSQL es un sistema de gestión de bases de datos relacionales de código abierto muy popular. Este comando se utiliza para reiniciar el servicio de PostgreSQL después de realizar cambios en la configuración o en la base de datos. El comando sudo

systemctl enable postgresql se utiliza para habilitar el servicio de PostgreSQL en Ubuntu. Esto significa que el servicio se iniciará automáticamente cada vez que se inicie el sistema. Este comando es útil si se desea que PostgreSQL se ejecute automáticamente en segundo plano cada vez que se inicie el sistema.


Gestión de usuarios en la base de datos PostgreSQL 14

sudo adduser postgres_user

El comando sudo adduser postgres_user en Ubuntu se utiliza para crear un nuevo usuario llamado postgres_user. El prefijo sudo se utiliza para ejecutar el comando con privilegios de superusuario, lo que significa que se necesitan permisos de administrador para ejecutar este comando. Una vez que se ejecuta el comando, se le pedirá que proporcione una contraseña para el nuevo usuario y que proporcione información adicional, como el nombre completo, el número de teléfono, etc. Si no desea proporcionar esta información, simplemente puede presionar Enter para omitir estos campos. Después de proporcionar la información necesaria, se creará una nueva cuenta de usuario con el nombre postgres_user. Esta cuenta de usuario tendrá su propio directorio de inicio y se agregará a los grupos predeterminados del sistema. También se le asignará un ID de usuario único (UID) y un ID de grupo único (GID) para identificarlo en el sistema. Una vez que se ha creado la cuenta de usuario, puede iniciar sesión en ella utilizando el comando su postgres_user y proporcionando la contraseña que se creó durante el proceso de creación de usuario.

A continuación accederemos a la base de datos y comprobaremos que podemos introducir sentencias SQL, creando otro usuario y una base de datos de la que sea dueño. Para ello, indicaremos en la línea de comandos:

sudo su – postgres

psql

Dentro de la base de datos como postgres, introduciremos una sentencia SQL similar a la siguiente:


CREATE USER geomapik WITH PASSWORD ‘geomapik_pw’;

CREATE DATABASE bbdd_prueba OWNER postgres_user;

para comprobar que la base de datos se ha guardado correctamente en PostgreSQL



Para salir de la conexión

\q

exit


otras manera

sudo -u postgres psql

CREATE ROLE admin WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'Passw0rd'

Aquí se está creando un nuevo rol de usuario llamado "admin". La opción "WITH LOGIN" indica que el usuario puede iniciar sesión en la base de datos. La opción "SUPERUSER" indica que el usuario tiene todos los privilegios en la base de datos. La opción "CREATEDB" indica que el usuario puede crear nuevas bases de datos. La opción "CREATEROLE" indica que el usuario puede crear nuevos roles de usuario. La opción "PASSWORD" indica que se está estableciendo una contraseña para el usuario, en este caso "Passw0rd".

Verifique que el usuario se haya creado con los privilegios necesario

postgres=# \du

Mientras está en el shell de Postgres, también puede crear usuarios de aplicaciones.

El primer comando "create database testdb;" crea una nueva base de datos llamada "testdb". El segundo comando "create user user1 with encrypted password 'Passw0rd';" crea un nuevo usuario llamado "user1" con una contraseña encriptada "Passw0rd". El tercer comando "grant all privileges on database testdb to user1" otorga todos los permisos en la base de datos "testdb" al usuario "user1". En resumen, estos comandos crean una nueva base de datos, un nuevo usuario y le otorgan todos los permisos en la base de datos recién creada.

Conectarse a la instancia de PostgreSQL 14 desde un host remoto.

Verifique que el servicio esté escuchando en el puerto 5432

$ ss -tunelp | grep 5432

tcp    LISTEN   0        244   0.0.0.0:5432           0.0.0.0:*      uid:123 ino:292971 sk:a <->                                                    

tcp    LISTEN   0        244       [::]:5432              [::]:*      uid:123 ino:292972 sk:d v6only:1 <->


Este comando de Ubuntu es una combinación de dos comandos diferentes: ss y grep. ss es un comando que muestra estadísticas de red detalladas, incluyendo información sobre los sockets de red. grep es un comando que busca patrones de texto en la salida de otros comandos. En este caso, el comando completo ss -tunelp | grep 5432 se utiliza para buscar información sobre el socket de red que está utilizando el puerto 5432. Aquí está lo que hace cada parte del comando:

    ss: muestra estadísticas de red detalladas.

    -tunelp: son opciones que se pasan al comando ss para filtrar la salida. En este caso, -t filtra los resultados para mostrar solo los sockets TCP, -u filtra los resultados para mostrar solo los sockets UDP, -n muestra las direcciones IP en formato numérico, -e muestra información detallada sobre los sockets, y -l muestra solo los sockets que están en estado de escucha (listening). La opción -p muestra el proceso que está utilizando cada socket.

    |: es un operador de tubería (pipe) que redirige la salida del comando ss al comando grep.

    grep: busca patrones de texto en la salida del comando anterior.

    5432: es el patrón de texto que se está buscando. En este caso, se busca cualquier línea que contenga el número de puerto 5432.

Permita el puerto a través del firewall si tiene habilitado ufw.

sudo ufw allow 5432/tcp


Ahora use la siguiente sintaxis para conectarse a su instancia desde una máquina remota

psql 'postgres://<username>:<password>@<host>:<port>/<db>?sslmode=disable'

El comando psql es una herramienta de línea de comandos que se utiliza para conectarse a una base de datos PostgreSQL. La sintaxis general del comando es la siguiente:

psql [opciones] [nombre_de_base_de_datos] [nombre_de_usuario]

El comando que mencionado anterior es una cadena de conexión que se utiliza para conectarse a una base de datos PostgreSQL utilizando psql. La cadena de conexión tiene el siguiente formato:

postgres://<nombre_de_usuario>:<contraseña>@<servidor>:<puerto>/<nombre_de_base_de_datos>?sslmode=disable

Donde:

    <nombre_de_usuario> es el nombre de usuario de la base de datos.

    <contraseña> es la contraseña del usuario de la base de datos.

    <servidor> es el nombre o la dirección IP del servidor de la base de datos.

    <puerto> es el número de puerto en el que se ejecuta el servidor de la base de datos (por defecto es 5432).

    <nombre_de_base_de_datos> es el nombre de la base de datos a la que se desea conectar.

    sslmode=disable es una opción que desactiva el uso de SSL para la conexión.


Instalar PostGIS

sudo apt install postgis

El comando sudo apt install postgis es utilizado en Ubuntu para instalar el paquete PostGIS. PostGIS es una extensión de base de datos espacial para PostgreSQL que permite el almacenamiento y la manipulación de datos geográficos.

accederemos a PostgreSQL con el usuario «postgres» y conectaremos a la base de datos que creamos

A continuación, una vez dentro de la base de datos, vamos a crear la extensión PostGIS. Introduciremos en la consola

CREATE EXTENSION postgis;

comprobaremos la versión de PostGIS instalada en la base de datos

SELECT postgis_version();

introduciremos el comando siguiente:

\dt

se mostrara un listado con las tablas (relaciones) creadas.

En este caso, a no ser que hayamos creado alguna otra tabla con anterioridad, aparecerá una única relación denominada «spatial_ref_sys» perteneciente a todos los SRC que contiene PostGIS.

Puedes hacer la prueba para visualizar el listado de SRC introduciendo:

SELECT srid, auth_name, proj4text FROM spatial_ref_sys;

Esta consulta se utiliza para seleccionar tres columnas específicas de la tabla spatial_ref_sys de una base de datos espacial. La primera columna seleccionada es srid, que es el identificador único de referencia espacial para un sistema de coordenadas específico. La segunda columna seleccionada es auth_name, que es el nombre de la organización que definió el sistema de coordenadas. La tercera columna seleccionada es proj4text, que es la definición del sistema de coordenadas en formato PROJ.4.

\q

exit

UNA VEZ INSTALADO TANTO POSTGRESQL COMO POSTGIS SE PUEDE INSTALAR LA INTERFAX GRAFICA DE pgAdmin4 en Ubuntu


para configurar el repositorio e instalar pgAdmin en Ubuntu.

Este comando descarga la clave pública del repositorio de pgAdmin y la instala en el sistema copielo completamente y ejecutalo en el terminal

curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg

Este comando crea un archivo de configuración para el repositorio de pgAdmin y lo guarda en el directorio /etc/apt/sources.list.d/. También actualiza la lista de paquetes disponibles.

sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'

Este comando instala pgAdmin en el sistema en ambos modos, escritorio y web.

sudo apt install pgadmin4-desktop

sudo apt install pgadmin4-web

si instaló pgAdmin en el modo web, debe configurar el servidor web para que funcione correctamente.

 

Alexander Carrasquel