Configuración básica de PostgreSQL

servidor

PostgreSQL se puede empezar a utilizar nada más terminar de instalarlo y despues de inicializar nuestro "cluster", sin necesidad de configurar nada. Pero si vamos a utilizar PostgreSQL para algo importante y con cierto volumen de datos y usuarios es imprescindible que lo configuremos para dicho trabajo.

No es la primera vez que algun asuario protesta o esta super preocupado de lo mal y lo lento que funciona su cluster de base de datos PostgreSQL en un servidor ultimo modelo con muchisima memoria. Normalmente el problema es que PostgreSQL no ha sido configurado para trabajar con el volumen de datos y usuarios con el que lo estamos usando. No es una gran ayuda tener un servidor con varios GBytes de memoria RAM si le hemos dicho a PostgreSQL, por ejemplo, que no utilice más de 32MBytes.

Tambien tenemos que decir que cualquier base de datos que se este usando activamente, no solo PostgreSQL, es un elemento dinamico y vivo en el que estamos cambiando los datos constantemente y donde el tamaño de los datos almacenados suele ir creciendo con el tiempo. Esto significa que una configuracion que funcione bien con ciertos valores hoy, puede que no funcione tan bien despues de unos meses de uso y que necesite ajustarse para que funcione optimalmente.

El comportamiento de PostgreSQL en nuestro sistema se puede controlar con tres ficheros de configuración que se encuentran en el directorio de datos donde inicializamos nuestro cluster PostgreSQL (En nuestro caso /var/pgsql/data). Estos tres ficheros son:

  • pg_hba.conf: Este fichero se utiliza para definir los diferentes tipos de accesos que un usuario tiene en el cluster.
  • pg_ident.conf: Este fichero se utiliza para definir la información necesaria en el caso que utilicemos un acceso del tipo ident en pg_hba.conf .
  • postgresql.conf: En este fichero podemos cambiar todos los parametros de configuracion que afectan al funcionamiento y al comportamiento de PostgreSQL en nuestra maquina.

Pasamos a continuación a explicar los cambios mas importantes que podemos hacer en algunos de estos ficheros.

pg_hba.conf

Este fichero se utiliza para definir como, donde y desde que sitio un usuario puede utilizar nuestro cluster PostgreSQL. Todas las lineas que empiezen con el caracter # se interpretan como comentarios. El resto debe de tener el siguiente formato:

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

Dependiendo del tipo de conexion y del tipo de autentificacion, [IP],[Netmask] y [opciones] pueden ser opcionales. Vamos a explicar un poco como definir las reglas de acceso. El tipo de conexion puede tener los siguientes valores, local, host, hostssl y hostnossl. El tipo de metodo puede tener los siguientes valores, trust, reject, md5, crypt, password, krb5, ident, pam o ldap

Una serie de ejemplos nos ayudaran a comprender mejor como podemos configurar diferentes accesos al cluster PostgreSQL.

Ejemplo 1 .- Acceso por tcp/ip (red) a la base de datos test001, como usuario test desde el ordenador con IP 10.0.0.100, y metodo de autentificacion md5:

host    test001   test  10.0.0.100 255.255.255.255   md5

Esta misma entrada se podria escribir tambien con la mascara de red en notacion CIDR:

host    test001   test  10.0.0.100/32  md5

Ejemplo 2 .- Acceso por tcp/ip (red) a la base de datos test001, como usuario test desde todos los ordenadores de la red 10.0.0.0, con mascara de red 255.255.255.0 (254 ordenadores en total) y metodo de autentificacion md5:

host    test001   test  10.0.0.0 255.255.255.0   md5

Esta misma entrada se podria escribir tambien con la mascara de red en notacion CIDR:

host    test001   test  10.0.0.0/24  md5

Ejemplo 3 .- Acceso por tcp/ip (red), encriptado, a todas las bases de datos de nuestro cluster, como usuario test desde el ordenador con IP 10.0.0.100, y el ordenador 10.1.1.100 y metodo de autentificacion md5 (necesitamos dos entradas en nuestro fichero pg_hba.conf:

hostssl    all   test  10.0.0.100 255.255.255.255   md5
hostssl    all   test  10.1.1.100 255.255.255.255   md5

Ejemplo 4.- Denegar el acceso a todos las bases de datos de nuestro cluster al usuario test, desde todos los ordenadores de la red 10.0.0.0/24 y dar accesso al resto del mundo con el metodo md5:

host       all   test  10.0.0.0/24   reject
host       all   all  0.0.0.0/0     md5

Asi podriamos seguir jugando con todas las posibilidades que nos brinda este fichero de configuracion. Por supuesto que las bases de datos y usuarios usados en este fichero tienen que existir en nuestro cluster para que todo funcione y algunos de los parametros solo se pueden usar si hemos compilado con las opciones pertinentes en el proceso de instalacion (por ejemplo, hostssl, pam, krb5)

Para poder en produccion los cambios en este fichero tendremos que decirle a PostgreSQL que vuelva a leerlo. Basta con un simple 'reload' (/usr/local/bin/pg_ctl -D /var/pgsql/data reload) desde la linea de comandos o con la funcion pg_reload_conf() como usuario postgres desde psql, el cliente PostgreSQL.

[postgres@servidor]# /usr/local/bin/psql 

Welcome to psql 8.2.4, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

postgres=# SELECT pg_reload_conf();

 pg_reload_conf 
----------------
 t
(1 row)

postgres=# 

Para una documentacion detallada sobre el fichero pg_hba.con, pasaros por la seccion Chapter 20. Client Authentication de la documentacion oficial de PostgreSQL.

postgresql.conf

Los cambios que realicemos en este fichero afectaran a todas las bases de datos que tengamos definidas en nuestro cluster PostgreSQL. La mayoria de los cambios se pueden poner en produccion con un simple 'reload' (/usr/local/bin/pg_ctl -D /var/pgsql/data reload), otros cambios necesitan que arranquemos de nuevo nuestro cluster (/usr/local/bin/pg_ctl -D /var/pgsql/data restart).

Mas informacion sobre todos los parametros que podemos cambiar en este fichero, que afectan y como se pueden poner en produccion se puede encontrar en la seccion 17. Server Configuration de la documentacion oficial de PostgreSQL.

A continuacion vamos a ver los parametros mas importantes que deberiamos cambiar si empezamos a usar PostgreSQL para un uso serio y si queremos sacarle el maximo partido a nuestra maquina. Existen muchos mas parametros que se pueden y con el tiempo se deberan de ajustar, aqui nos vamos a centrar en los mas importantes y los cuales deberiamos cambiar antes de empezar a utilizar PostgreSQL de una manera seria.

max_connections: Numero maximo de clientes conectados a la vez a nuestras bases de datos. Deberiamos de incrementar este valor en proporcion al numero de clientes concurrentes en nuestro cluster PostgreSQL. Un buen valor para empezar es el 100:

max_connections = 100

shared_buffers: Este parametro es importantisimo y define el tamaño del buffer de memoria utilizado por PostgreSQL. No por aumentar este valor mucho tendremos mejor respuesta. En un servidor dedicado podemos empezar con un 25% del total de nuestra memoria. Nunca mas de 1/3 (33%) del total. Por ejemplo, en un servidor con 4Gbytes de memoria, podemos usar 1024MB como valor inicial.

shared_buffers = 1024MB

work_mem: Usada en operaciones que contengan ORDER BY, DISTINCT, joins, .... En un servidor dedicado podemos usar un 2-4% del total de nuestra memoria si tenemos solamente unas pocas sesiones (clientes) grandes. Como valor inicial podemos usar 8 Mbytes.

work_mem = 8MB

maintenance_work_mem: Usada en operaciones del tipo VACUUM, ANALYZE, CREATE INDEX, ALTER TABLE, ADD FOREIGN KEY. Su valor dependera mucho del tamaño de nuestras bases de datos. Por ejemplo, en un servidor con 4Gbytes de memoria, podemos usar 256MB como valor inicial.

maintenance_work_mem = 256MB 

effective_cache_size: Parametro usado por el 'query planner' de nuestro motor de bases de datos para optimizar la lectura de datos. En un servidor dedicado podemos empezar con un 50% del total de nuestra memoria. Como maximo unos 2/3 (66%) del total. Por ejemplo, en un servidor con 4Gbytes de memoria, podemos usar 2048MB como valor inicial.

effective_cache_size = 2048MB

checkpoint_segments: Este parametro es muy importante en bases de datos con numerosas operaciones de escritura (insert,update,delete). Para empezar podemos empezar con un valor de 64. En grandes databases con muchos Gbytes de datos escritos podemos aumentar este valor hasta 128-256.

checkpoint_segments = 64

Es muy importante tener en cuenta que al aumentar los valores por defecto de muchos de estos parametros, tendremos que aumentar los valores por defecto de algunos parametros del kernel de nuestro sistema. Informacion detallada de como hacer esto se encuentra en la seccion 16.4. Managing Kernel Resources de la documentacion oficial de PostgreSQL.

En fin, esto es solo un aperitivo de lo que podemos hacer. Con la practica y la experiencia podremos y tendremos que ajustar otros muchos parametros. Pero esto sera materia de un proximo articulo.

Fuentes: postgresql.org.

Comentarios

Opciones de visualización de comentarios

Seleccione la forma que prefiera para mostrar los comentarios y haga clic en «Guardar las opciones» para activar los cambios.

Con respecto a las max_connections...

100 conexiones puede ser contraproducente, y llevar a problemas de muchos cambios de contexto, lo que puede definitivamente matar el rendimiento de la BBDD.

Para este tipo de situaciones es mejor bajar el número de conexiones a un número razonable (nº de cores x 2 ó x 3) y meter un pool de conexiones por delante, bien a nivel del servidor de aplicaciones, bien mediante un wrapper específico como pgpool, por ejemplo.

Imagen de rafaelma

pool de conexiones

Como bien dices, en ciertos casos un pool de conexiones es totalmente imprescindible, pero en mi modesta opinión, en la mayoría de los casos, los servidores de hoy en dia aguantan sin problemas muchas más conexiones que nº de cores x2 ó x3.

Creo recordar que lei en las listas de postgresql.org hace un tiempo que en una maquina de 32bits el máximo aconsejado estaba alrededor de las 600 y que antes de llegar a este valor deberiamos de empezar a utilizar un pool de conexiones.

Yo sinceramente nunca he tenido problemas con este tema. Trabajo con maquinas de 64bits y en sistemas muy ocupados (alrededor de 48.000.000 de transacciones al dia) y suelo tener entre entre 100 y 170 conexiones abiertas sin problemas. Tambien es verdad que no todas se estan usando continuamente a la vez y que muchas se abren, se usan y se cierran una vez usadas.

Seria interesante tener algunos datos de la diferencia de uso de los recursos con y sin pool de conexiones para sistemas con un alto número concurrente de clientes.

Archivo postgresql.conf

La variable listen_addresses está comentada por defecto en el archivo postgresql.conf, y creo que eso impediría conexiones remotas a las bases de datos.
Si ponemos un asterisco se permitirá la conexión a todo el mundo, pero también podemos indicar sólo las IPs que queramos, por ejemplo:

listen_adresses '*'
listen_adresses '192.168.10.20,192.168.10.21'

Aunque está mejor explicado en la documentación oficial.

Imagen de rafaelma

parámetro listen_addresses

Tienes razón, listen_addresses está comentado por defecto y esto significa que solo te puedes conectar via sockets a PostgreSQL.

Una puntualización, un asterisco no significa que "permitirá la conexión a todo el mundo", sino que PostgreSQL escuchará por todas las IPs instaladas en el servidor.

Si el servidor tiene más de una IP instalada, se podria definir como tu indicas, por cuales de ellas PostgreSQL escuchará por peticiones de conexión.

El permitir ó no el acceso a una IP/red se define en el fichero pg_hba.conf.

problema de conexion - ayuda porfavor

añadir a lo anterior que expuse es que si inicio el servidor en el de sobremesa, como puedo leer la base de datos desde el portatil?

salu2.

Imagen de jrockgonzalez

Postgresql.conf no puede asignar una dirección IP solicitada

Buen día.
Amigo quiero accesar a postgresql instalado en ubuntu server desde otro computador local. La dirección ip de mi computador cliente es 192.168.1.4 Lo coloqué en el archivo en la sección Connection Settings:
listen_addresses='192.168.1.4' y me sale el error antes expuesto y también:
VET WARNING:could not create listen socket for "192.168.1.4"
VET FATAL: could not create any TCP/IP sockets

informacion

buenas, tengo una consulta tengo una base de datos para realizar nominas, el detalle que se me presenta es que cuando varios usuarios generan al mismo tiempo el servicio me da lugar a un solo usuario a los demas no me les permite realizar el proceso. por otro lado no se que estara sucediendo que algunas veces el tiempo de generacion varia demaciado ejemplo si duraba 18min a veces toma hasta 2 horas realizando el proceso. agradeciendo la ayuda que me puedan prestar.

mira lo que pasa es que debes

mira lo que pasa es que debes estar usando en la liquidacion de la nomina tablas a las que accesan muchos otros procesos, o tambien puede ser que estes usando tablas sin indices o muchas cosas, pero podrias intentar crear una tabla temporal y realizar la liquidacion desde ahi haciendo un insert select al inicio de tu procedimiento

problema de conexion - ayuda porfavor

Hola a todos, os comento:

no soy experto ni programador, solo me defiendo y este es el problema:

unos un programa que utiliza postgres, se llama Holdem Manage, en el saco estadisticas de poker.

el caso es que tengo la base de datos en una unidad externa e-sata, el programa lo tengo en el pc sobremesa y en un portatil, el pc accede sin problema a la base de datos, el portatil tambien accede pero los 2 a la vez no ya no se que hacer hice mil pruebas creando rol de usuarios, etc y nada, Me podei ayudar?

Gracias.

ayuda con conexion a postgres

tengo un problema con algo que quiero hacer, quiciera mantener una conexion abierta por sesion, al momento que el usuario cierre la sesion quiciera que recien en ese momento se deconecte mi conexion, y bueno concurrentemente otro usuario podria tambien estar conectado.
les agradesco las posibles soluciones.