Instalar Repositorio PostgreSQL

Lo primero que haremos será configurar los repositorios de PostgreSQL para tener la última versión disponible.

Para acceder a la versión correcta de tus repositorios visita la pagina oficial. En este caso lo hare con Debian.

Primero creamos el archivo del repositorio

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

Dentro añadiremos la siguiente línea, cuidando tu versión de debian

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

Luego añadimos la clave y actualizamos los repositorios

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

sudo apt-get update

Crear al usuario

Como último paso, por cuestiones de seguridad, crearemos al usuario de la base de datos y le daremos una contraseña para la Base de Datos.

$ sudo su

$ su postgres

$ psql postgres

Ahora le damos todos los permisos a nuestro usuario a la base de datos que deseemos trabajar.

postgres=# CREATE USER TuUsuario PASSWORD 'password';

Si ya tenemos creado al usuario, simplemente le cambiamos la contraseña

postgres=# alter user TuUsuario with password 'tucontraseña';

ALTER ROLE

Para darle permisos de super usuario tecleamos:

postgres=# ALTER ROLE TuUsuario WITH SUPERUSER;

Para salir de la Base de Datos hacemos:

pruebaBD=# \q

# exit

$ exit

$ createdb MiBasedeDatos;

Luego permitir al usuario "TuUsuario" el acceso mediante nombre de usuario y contraseña (método password/md5) desde el archivo de configuración de autenticación pg_hba.conf, el cual se encuentra en la ruta /etc/postgresql/14/main/pg_hba.conf.

Debajo de la configuración del usuario "postgres", agregar la línea para que se autentique al usuario "TuUsuario" mediante el método md5, aquí les dejo la imagen de mi configuración, mí usuario se llama pi:

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

añadir

local all pi md5

Habilitar la conexión Remota

Primero Editamos el archivo pg_hba.conf

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

Cambiar por la versión correspondiente

y agregamos una línea al final (la dir ip remota a la que queremos darle permiso de conexión)

host all all 192.168.11.111 255.255.255.0 trust

(pueden dejar una ip o un grupo de direcciones cambiando la ip y la máscara de subred, recuerda cambiarlos por tus datos)

Si deseamos que se puedan conectar desde cualquier lado:

host all all 0.0.0.0/0 md5

Una vez agregado el archivo presionamos Ctrl+x y nos pide confirmación con un “Y” luego enter y listo, queda modificado.

Por último tenemos que editar el archivo /etc/postgresql/14/main/postgresql.conf y modificar el parámetro listen_addresses para indicar que escuche en las interfaces necesarias, en este caso lo habilitaremos para todas:

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

Agregar

listen_addresses='*'

Recargar la configuración de PostgreSQL para que tomen efecto los cambios:

sudo /etc/init.d/postgresql restart

Cambiar codificación de la contraseña

Postgres por defecto usa la codificación scram-sha-256 y si seguiste este manual vamos a usar MD5, por lo que debes abrir el archivo pg_hba.conf:

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

Buscar la sección # - Authentication -

y dejar la linea como siguie

password_encryption = md5 # scram-sha-256 or md5

Crear Primera Base de Datos para PostgreSQL

Procedemos a crear una Base de Datos para experimentar, en mi caso crearé la BD pruebaBD, directamente logeado en la terminal estándar teclea:

$ createdb pruebaBD;

$ psql pruebaBD

Ahora le damos todos los permisos a nuestro usuario a la base de datos que deseemos trabajar.

postgres=# GRANT ALL PRIVILEGES ON DATABASE pruebaDB to TuUsuario;

Listo, estamos logeados en nuestra base de datos

psql (9.6.11)

Type "help" for help.

Digite:

\copyright para ver los términos de distribución

\h para ayuda de comandos SQL

\? para ayuda de comandos psql

\g o or termine con punto y coma para ejecutar una consulta

\q para salir

pruebaBD=#

Lo cual nos indica que hemos entrado a la Base de Datos. En éste punto, podremos ejecutar todos los comandos en SQL que necesitemos.

Para salir de la Base de Datos hacemos:

pruebaBD=# \q

Capacidades especificas

Cubo Olap

sudo apt-get install odbc-postgresql

Base de Datos Espacial

En el caso de que queramos instalar el paquete para base de datos espaciales, faltaría añadir el siguiente paquete:

sudo apt -y install postgis postgresql-14-pglogical

Instalar las capacidades a la base de datos

-- Enable PostGIS (as of 3.0 contains just geometry/geography)

CREATE EXTENSION postgis;

-- enable raster support (for 3+)

CREATE EXTENSION postgis_raster;

-- Enable Topology

CREATE EXTENSION postgis_topology;

-- Enable PostGIS Advanced 3D

-- and other geoprocessing algorithms

-- sfcgal not available with all distributions

CREATE EXTENSION postgis_sfcgal;

-- fuzzy matching needed for Tiger

CREATE EXTENSION fuzzystrmatch;

-- rule based standardizer

CREATE EXTENSION address_standardizer;

-- example rule data set

CREATE EXTENSION address_standardizer_data_us;

-- Enable US Tiger Geocoder

CREATE EXTENSION postgis_tiger_geocoder;

-- Upgrades postgis and other installed postgis packaged extensions

SELECT postgis_extensions_upgrade();ç

Código Espacial

-- Create table with spatial column

CREATE TABLE mytable (

id SERIAL PRIMARY KEY,

geom GEOMETRY(Point, 26910),

name VARCHAR(128)

);

-- Add a spatial index

CREATE INDEX mytable_gix

ON mytable

USING GIST (geom);

-- Add a point

INSERT INTO mytable (geom) VALUES (

ST_GeomFromText('POINT(0 0)', 26910)

);

-- Query for nearby points

SELECT id, name

FROM mytable

WHERE ST_DWithin(

geom,

ST_GeomFromText('POINT(0 0)', 26910),

1000

);