CONFIGURANDO ANDO CON POSTGRESQL

Cuanto más pequeño es el detalle, mayor es el valor.

– Doug Johson

Seguimos con el segundo capitulo de la instalación de postgreSQL. Esta vez dividiré este proceso en dos partes: instalación y configuración. Ya que anteriormente en los tutoriales que se hicieron en FEDORA y UBUNTU lo hacíamos en un mismo artículo pero jamás nos adentrábamos a conocer un poco mas sobre dichas configuraciones. Y es que en nuestro nuevos retos de resolución de problemas ya estamos entrando a un nivel donde prácticamente el NEXT,NEXT ya no es suficiente y debemos conocer con mas detalles de la infraestructura que rige nuestro software REMORA . El cual es un software que valida datos biométricas para entidades financieras. Y pues prácticamente debemos mejorar nuestra seguridad y performance y comenzaremos desde la capa de datos. Y poco a poco veremos como ir mejorando por que esto de Performance y la seguridad es pura mejora continua.

Conociendo las rutas de instalación

Lo primero que debemos conocer es identificar donde se instalan nuestro postgres. Y es que es muy común que si la instalas en diferentes distros este se encuentre en diferentes lugares. Para eso teclaremos desde la terminal comandos que nos permitirá conocer la ubicación exacta. Para eso usaremos el comando Find también por ahi se tiene el comando Locate; pero este a veces no se encuentra instalado. Por lo cual es mas practico el primero. Si quieren conocer mas acerca de estos comandos pueden ir a esta pagina.

jc@sandjump-box:~> find / -name postgresql.conf
/var/lib/pgsql/data/postgresql.conf
/usr/lib/tmpfiles.d/postgresql.conf
find: ‘/run/user/1000/gvfs’: Permission denied
find: ‘/run/user/1000/doc’: Permission denied
jc@sandjump-box:~> find / -name pg_hba.conf
find: ‘/run/user/1000/doc’: Permission denied
sandjump-box:/var/lib/pgsql/data # find / -name pg_hba.conf
find: File system loop detected; ‘/.snapshots/1/snapshot’ is part of the same file system loop as ‘/’.
/var/lib/pgsql/data/pg_hba.conf
find: ‘/run/user/1000/gvfs’: Permission denied
find: ‘/run/user/1000/doc’: Permission denied

Configurando la Seguridad

Como lo comente anteriormente el archivo que se va modificar es el pg_hba.conf. Como buenas practica les recomiendo hacer un backup de dicho archivo antes de que lo editen. Este archivo es una autenticación basada en host y es almacenada dentro del mismo cluster del la base de datos; pero este puede ser movido para mejorar la seguridad. El archivo cuanta con los siguientes parámetros.

[Tipo de conexion] [database] [usuario] [IP][Netmask] [Tipo de autentificacion]

Este archivo puede considerarse un tipo de firewall el cual solo permitirá conexiones IP sockets “Local” y Unix Sockets “host”. Teniendo restricciones de conexión hacia base de datos , usuarios y rangos de IP’s. Y como plus extras se añade los métodos los cuales permite asegurar el acceso por el tipo de conexión. Por lo regular se recomienda usar los métodos : Trust e Ident para conexiones de tipo local y Password para conexiones remotas de tipo Host. Las demás parámetros requieren hacer una configuración extras en el servidor. Les dejo la documentación oficial de cada uno de los parámetros. https://www.postgresql.org/docs/13/auth-methods.html

De esta forma configure mi archivo. El cual le indico que solo cuando este en mi SO. podre acceder a todas las bases de datos pero con el usuario postgres. Pero si quiero conectarme con un cliente ya sea DBeaver o algún otro podre hacerlo mediante una contraseña pero solo estando en mi ordenador . Por ahí quizá se vea extraño el método y es que la pagina oficial te dice que se recomienda usar el scram pero por tema de compatibilidad puede que este falle por lo que por el momento se recomienda el md5 esto por cuestiones de compatibilidad. Pero en su momento testare la el scram. Si después quieren ir agregando nuevos usuarios al PostgreSQL y estos puedan acceder desde cualquier ip. Solo el ADDRESS poner ALL. Tenemos un articulo que habla de como crear usuarios en postgres para que puedan implementarlo -> https://blog.junglacode.org/software/base-de-datos/permisos-en-postgres/

Configurando la Performance

El siguiente archivo que se va a configurar es el postgresql.conf. Este archivo tiene una configuración muy básica y esta acondicionada para ser compatible con la mayoría del hardware. Si se requiere sacarle mas jugo a este gran Gestor de base de datos. Este es el archivo indicado. Sin embargo los parámetros que edites deben estar muy de acuerdo con el Hardware donde se tiene instalado. Si no tienes esa información puedes que en vez de optimizar lo pongas mas lento que el caballo del malo. Este archivo tiene mas de 200 parámetros. Por lo cual es muy necesario darle una leída a todos, para que podamos tener la configuración mas optima. https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server/es

Componentes del hardware que se se deben tener en cuenta.

• CPU : Optimización de consultas para aprovechar al maximo
• RAM : Cada que se envía una consulta todas las operaciones se ejecutan en memoria antes de grabarlas en el Disco, por lo tanto se debe tener bastante para asegurar el grabado.
• Disk I/O : Un buen proceso escritura y lectura , aumenta rendimiento ya que es el proceso mas costoso y mas cuando se tienen muchos procesos haciendo lo mismo se debe diversificar.
• Network o la RED : Entre mas crezcan los datos mas será la carga que se enviara en la red por lo que el factor de confiabilidad de su la infraestructura debe ser prioridad para evitar cuellos de botella.
• Optimización del SO : Algo que se debe considerar es que cada Sistema operativo debe tener su propios parámetros de configuración ya que al final es donde Su SGBD reside. Por lo tanto es necesario mantenerlo actualizado y configurarlo con buenas practicas.

Algunos parámetros para el performance

Parámetros de configuración de conexión

max_connections afecta el comportamiento del servidor PostgreSQL y las conexiones del cliente. Puede usar esto para configurar la cantidad máxima de conexiones paralelas que puede admitir un servidor PostgreSQL. La fórmula utilizada para calcular esto es:

max_connections = max(4 * number of CPU cores, 100)

Parámetros de configuración de memoria

Los siguientes parámetros de configuración rigen cómo varios procesos y funciones de la base de datos de PostgreSQL utilizan la memoria del sistema. Es importante tener en cuenta que todas las configuraciones relacionadas con la memoria combinadas no deben exceder la cantidad máxima de memoria disponible.

shared_buffers determina la cantidad de memoria que PostgreSQL puede usar para los búferes de memoria compartida. Es una buena regla general asignar no más del 25 % de la memoria disponible para esto.

temp_buffers determina la cantidad de memoria utilizada para los búferes temporales para cada sesión de la base de datos. Debido a que esto es local para cada sesión, debe multiplicarlo por el número de sesiones activas para obtener la cantidad máxima que puede alcanzar.

wal_buffers es la cantidad de memoria shared_buffers que se usará para almacenar en búfer los datos WAL antes de que se puedan escribir en el disco. De forma predeterminada, el valor se establece en -1, equivalente a alrededor del 3 % del tamaño de shared_buffers. No puede ser inferior a 64 KB ni superior al tamaño de un segmento WAL, que suele ser de 16 MB.

work_mem es la cantidad máxima de memoria que una consulta puede usar para su operación, como clasificaciones, tablas hash, etc. Tenga en cuenta que este límite es por consulta y no para toda la base de datos. De forma predeterminada, el límite se establece en 4 MB, que se puede cambiar para adaptarse mejor a sus casos de uso.

Maintenance_work_mem es la cantidad de memoria asignada para realizar actividades de mantenimiento en la base de datos, como crear índices, modificar tablas, vaciar, cargar datos, etc. Por lo general, cuando se realizan estas operaciones, hay un aumento en las operaciones de E/S del disco, ya que el los cambios deben escribirse en el disco. La forma óptima de hacerlo sería realizar tantas operaciones como sea posible en la memoria y escribir la salida final en el disco, reduciendo así las costosas operaciones de E/S del disco. Así que tener al menos 1 GB de memoria para trabajos de mantenimiento es un buen punto de partida.

Bueno pues hasta aqui os dejo este tutorial que considero que es el mas extenso que he hecho. Pero bueno todo sea para poder subir de nivel y pueda solventar el problema de rendimiento que actualmente tiene nuestra app. Pues hasta la aproxima

Referencias

https://sematext.com/blog/postgresql-performance-tuning/

https://pgtune.leopard.in.ua

https://momjian.us/main/writings/pgsql/performance.pdf