Configuración básica de PostgreSQL

Rafael Martinez Guerrero
Words: 1389
Reading time: 7 min.
postgres • sysadm • postgresql-es • español

Table Of Contents

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.