PostgreSQL - perfeccion-ar/infraestructura-clasica-y-avanzada GitHub Wiki

Recomendado: construya un container o un haga un snapshot de su container antes de realizar esta instalación:

Instalación:

sudo apt-get install postgresql postgresql-contrib

libpq-dev son solo necesarias si va a compilar librerías para conectarse a PosgtreSQL. Ejemplo, la gema pg para Ruby on Rails.

Roles

De forma predeterminada, Postgres utiliza un concepto llamado "roles" para manejar la autenticación y la autorización. Estos son, en cierto modo, similares a los usuarios y grupos habituales de estilo Unix.

La idea detrás de todo esto es que cada usuario del SO debería tener su propia base de datos. Y el usuario postgres, administrar cualquier de ellas

El procedimiento de instalación (el apt-get install) debe haber creado una cuenta de usuario llamada postgres que está asociada con la función predeterminada de Postgres. Hay algunas formas de utilizar esta cuenta para acceder a Postgres. Una forma es cambiar a la cuenta de postgres en su servidor ejecutando el siguiente comando:

sudo su - postgres

o, si ya es root, simplemente

su - postgres

Ya se puede ingresar al motor

psql

Aparecerá el prompt de PostgreSQL

   postgres=#

Puede salir con \q

Otros comandos rápidos son

- \dS+        → Lista todas las tablas
- \d+         → describe tabla
- \dt+        → describe tamaño de tabla
- \i algo.sql → Correr algo.sql, por ejemplo, un backup

Y a que base nos hemos conectado? Pues como dijimos, si habia un usuario postgres, debía de haber una base postgres. Y es así, existe no solo para honrar la filosofía cuenta_de_usuario → base, sino porque necesitamos una base por defecto donde guardar información sobre las otras bases hospedadas en el motor.

De hecho este usuario puede ver que otars bases hay, y conectarse a ellas si hace falta

- \l+         → lista otras bases, sus tamaños, tablespaces, etc.
                Desde el sistema operativo podemos hacer lo mismo, con sql -U postgres -l
- \c          → conecta a una base.
                Desde el sistema operativo, también podemos conectarnos directo, usando psql -d (base)

Usuarios

Como usuario postgres, y desde el sistema operativo, puede crear nuevos usuarios

postgres@pancutan-tests:~$ createuser --interactive
Enter name of role to add: sammy
Shall the new role be a superuser? (y/n) y

Sin embargo, algo que PostgreSQL asume cuando uno de sus usuarios se loguea, es que existirá una base de datos con el mismo nombre. Si nos logueamos como sammy, PostgreSQL tratará de conectar a Sammy con su base. Para que no falle, le creamos también su base:

postgres@pancutan-tests:~$ createdb sammy

Ahora debemos crear un usuario en el sistema operativo llamado sammy. Probablemente no podamos hacerlo con el usuario postgres, sino con root o con sudo

postgres@pancutan-tests:~$ exit
logout

root@pancutan-tests:~# sudo adduser sammy
Creating home directory `/home/sammy' ...
Copying files from `/etc/skel' ...
New password: 
Retype new password: 
Is the information correct? [Y/n]

Si ahora nos convertimos en sammy

su - sammy

sudo su - sammy

Podemos entrar a su base

sammy@pancutan-tests:~$ psql
psql (14.12 (Ubuntu 14.12-0ubuntu0.22.04.1))
Type "help" for help.

sammy=#

Si en cambio queremos entrar a la base de sammy como administradores, lo haremos como usuario postgres

root@pancutan-tests:~# su - postgres

postgres@pancutan-tests:~$ psql -d sammy
psql (14.12 (Ubuntu 14.12-0ubuntu0.22.04.1))

sammy=#

Autenticación avanzada

Conectarse como otro usuario, local

Esta dualidad que exigen los roles de PostgreSQL ("el usuario debe existir en el SO y debe tener su propia base") puede no ser necesaria para nuestros propósitos. Pongamos que tenemos un pequeño sistema en Rails que necesita una base, y no necesitamos un usuario con el mismo nombre. De hecho, Rails necesitará conectarse por un puerto, o por el socket, no "con el comando psql".

Lo mismo nosotros, si necesitamos conectarnos desde afuera, con algún cliente como PgAdmin.

Consideremos este usuario que crearemos "solo adentro de PostgreSQL". Queremos que acceda a una base que no le corresponde, y que ni siquiera tiene su mismo nombre

root@pancutan-tests:~# su - postgres

postgres@pancutan-tests:~$ createdb inventario

Le cambiamos el password desde adentro de la base

postgres@pancutan-tests:~$ psql
postgres=# \password inventario_user
Enter new password for user "inventario_user":

postgres@pancutan-tests:~$ createuser inventario_user

Ahora necesitaremos configurar sus accesos en el archivo pg_hba.conf

Puesto que su ubicación varía según la distro de Linux que usemos, una forma de encontrarlo será con find. ejemplo como usuario root

root@pancutan-tests:~$ find / -name pg_hba.conf 2>/dev/null
/etc/postgresql/14/main/pg_hba.conf

root@pancutan-tests:~$ sudo vim /etc/postgresql/14/main/pg_hba.conf

Este archivo tiene una magnífica documentación adentro. Solo hay que detenerse a leerlo. Ejemplo de permisos que podemos configurar en nuestro caso de base inventario y de un usuario inventario_user

local inventario inventario_user trust

Tras alterar el pg_hba.conf, reiniciamos el servicio

systemctl restart postgresql

Ahora podemos conectarnos a la base desde cualquier usuario. Es decir: ya no necesitamos crear con adduser un usuario inventario_user

root@pancutan-tests:~# psql -U inventario_user -d inventario
psql (14.12 (Ubuntu 14.12-0ubuntu0.22.04.1))
Type "help" for help.

inventario=>

Comprobamos que tenemos permisos adentro

CREATE TABLE clientes (
    id int,
    cliente varchar(80)
);

Si listamos las tablas con \dSs, la última será productos

List of relations
  Schema   |              Name               | Type  |      Owner      | Persistence | Access method |    Size    | Description 
------------+---------------------------------+-------+-----------------+-------------+---------------+------------+-------------
public     | productos                       | table | inventario_user | permanent   | heap          | 0 bytes    |

Conectarse local desde otro usuario, con contraseña

Incrementemos ahora el nivel de seguridad. Volvemos al archivo pg_hba.conf y cambiamos la línea con trust como

local inventario inventario_user password

El método password dejará pasar la contraseña en texto plano. Puede escoger md5 en su lugar.

Restarteamos el motor con systemctl restart postgresql. Si ahora nos queremos loguear, nos pedirá el password que habíamos definido con \password:

root@pancutan-tests:~# psql -U inventario_user -d inventario
Password for user inventario_user: 
psql (14.12 (Ubuntu 14.12-0ubuntu0.22.04.1))
Type "help" for help.

inventario=>

Conectarse localmente a la ip local

Esto es muy útil si tenemos un CMS, un framework (ej: Django, Rails, etc), corriendo en el mismo equipo

Abrimos pg_hba.conf y en lugar de comenzar una la línea con local, agregamos una línea "host"

host inventario inventario_user 127.0.0.1/32 md5

Reiniciamos nuevamente el servicio, y esta vez la conexión será así

psql -U inventario_user -d inventario -h 127.0.0.1

Password for user inventario_user: 
psql (14.12 (Ubuntu 14.12-0ubuntu0.22.04.1))

inventario=>

Conectarse desde afuera

Para conectarse desde afuera, puede hacerlo desde la librería que utilice su framework, su lenguaje, desde pgadmin, o desde el comando psql. De paso, si puede conectarse con el comando psql, además se asegura que los otros métodos funcionen, independientemente de su funcionamiento.

Para tener "solo" el cliente psql, en el Ubuntu que pasaremos a denominar máquina cliente, solo necesita instalar el paquete postgresql-client-, en nuestro caso:

apt-get install postgresql-client-14

sin embargo, todavía no puede conectarse desde afuera. PostgreSQL debe abrir su puerto 5432. Para ello, primero busque el archivo postgresql.conf

root@pancutan-tests:~# find / -name postgresql.conf 2>/dev/null

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

Son muchas lineas. Busque esta línea y déjela así:

listen_addresses = '*'

Reinicie el servicio.

Obtenga ahora la ip externa del server / container.

Si ha instalado PostgreSQL en un container es posible que necesite que le proxeen un puerto externo desde el host hacia su container.

Si solo desea conectarse desde otro container, obtenga la ip del container donde ha instalado PostgreSQL:

root@hetzner01 # lxc list

+----------------+---------+------------------------------+------+-----------------+-----------+
|      NAME      |  STATE  |             IPV4             | IPV6 |      TYPE       | SNAPSHOTS |
+----------------+---------+------------------------------+------+-----------------+-----------+
| pancutan-tests | RUNNING | 10.19.29.155 (eth0)          |      | CONTAINER       | 1         |
+----------------+---------+------------------------------+------+-----------------+-----------+

Ok, la dirección es 10.19.29.155. Si intentamos conectarnos:

root@hetzner01 /tmp # psql -U inventario_user -d inventario -h 10.19.29.155                                                                    
psql: error: connection to server at "10.19.29.155", port 5432 failed: FATAL:  no pg_hba.conf entry for host "10.19.29.1", user "inventario_use
r", database "inventario", SSL encryption

El mensaje es bastante claro. Dice que deberíamos agregar la ip cliente en pg_hba.conf - también podemos ser más genéricos y permitir la conexión desde cualquier ip:

host inventario inventario_user 0.0.0.0/0 md5

Restarteamos el servicio postgresql e intentamos de nuevo:

root@hetzner01 /tmp # psql -U inventario_user -d inventario -h 10.19.29.155
Password for user inventario_user: 

SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

inventario=>

Magnífico! Incluso, si nos han proxeado el puerto, podemos conectarnos incluso al puerto externo del server de este taller(atención a su puerto asignado)

sergio@bebop $ psql -U inventario_user -d inventario -h 37.27.49.225 -p 5432

Password for user inventario_user: 
psql (16.3 (Ubuntu 16.3-0ubuntu0.24.04.1), server 14.12 (Ubuntu 14.12-0ubuntu0.22.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)

inventario=>

PgAdmin

El cliente gráfico "oficial" de PostgreSQL es Pgadmin.

Ejemplo para configurarlo

Ejemplo tras conectarse

Conectarse desde un Framework (Rails)

Si bien podemos "usar" PostgreSQL lanzandole querys, lo mas probable es que le carguemos datos desde un LMS (como Moodle), un CMS (como Joomla), o desde un microservicio como Flask o como Sinatra, o desde un framework como Django, o Rails.

Veamos un caso rápido. Tenemos un un usuario inventario_user, y una base inventario. Hemos comprobado que podemos acceder mediante

psql -U inventario_user -d inventario -h 10.19.29.155

Fabriquemos entonces un ABM rápido mediante Ruby on Rails.

Desde el mismo container, o desde otro container, agreguemos Ruby, Gem, el compilador GCC, y un par de librerías para compilar la "gema" pg

apt-get install ruby ruby-dev ruby-rubygems ruby-bundler libpq-dev build-essential libyaml-dev postgresql-common postgresql-client-14

Así como se tiende a manejar las versiones de Python mediante conda, o mediante Virtualnwrapper, se tiende a utilizar Ruby bajo Rbenv o bajo Rvm. Por esta vez, para simplificar, instalaremos Ruby y el manejador de librerías Gem, directamente usando los paquetes del sistema operativo.

Instalamos Rails

gem install rails bundler

Creamos un proyecto simple, le pedimos que nos deje todo listo para usar como motor a PostgreSQL

    rails new sistema_inventario -d=postgresql
    cd sistema_inventario/
    bundle install

Editamos config/database.yml, y agregamos

default: &default
  adapter: postgresql
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: inventario_user
  password: "un_password"

development:
  <<: *default
  database: inventario
  host: 10.19.29.155
  port: 5432

Para comprobar que Rails puede acceder sin problemas, podemos hacer

rails dbconsole

Creamos un ABM

rails generate scaffold Product nombre:string cantidad:integer activo:boolean

rails db:migrate

== 20240727185927 CreateProduct: migrating ===================================
-- create_table(:products)
   -> 0.0144s
== 20240727185927 CreateProduct: migrated (0.0145s) ==========================

Levantamos el miniserverver de Rails, Puma

root@pancutan-rails-prueba:~/sistema_inventario# rails server -b 0.0.0.0

=> Booting Puma
=> Rails 7.1.3.4 application starting in development 
*  Environment: development
*          PID: 4467
* Listening on http://0.0.0.0:3000
Use Ctrl-C to stop

Entramos ahora a http://ip:3000/productos, y ya podemos cargar datos en la base

Backup

Volcar un backup a un archivo inventario.sql

pg_dump -U inventario_user -h 10.19.29.155 -p 5432 inventario > inventario.sql

Restauración:

pg_restore -h 10.19.29.155 -p 5432 -U postgres -d old_db -v inventario.sql

Mas info en https://stackoverflow.com/questions/2732474/restore-a-postgres-backup-file-using-the-command-line

Dudas? [email protected]

TODO:

Túneles
Backup y restore
Ejemplo conectando algún framework
Troubleshooting cuando la base está lenta
- Índices
- Muchas conexiones (matar, matar)
- Conexiones muy largas
- Deadlocks
- Querys muy grandes: explain
- Datos de ejemplo, de https://wiki.postgresql.org/wiki/Sample_Databases usaremos
  - https://github.com/df7cb/omdb-postgresql