Annexos

Variables de Configuració de PostgreSQL

Es considera necessari conèixer algunes dades importants corresponents a la configuració dels locales. El terme locale, fa referència a la localització, també coneguda com a internacionalització, la qual cosa fa possible poder internacionalitzar aplicacions tant pel tipus d’idioma com per les diferents opcions d’escriptura, etc. A continuació farem unes proves modificant les localitzacions:

  • Mostrar localitzacions
  • Establir localitzacions
  • Reinicialitzar (reconfigurar) localitzacions

Mostrar localitzacions

Els resultats de la configuració són relatius ja que dependrà de la configuració des del moment de la instal·lació de PostgreSQL. Comencem a fer un test de la nostra configuració:

A Linux/Debian:

SHOW lc_messages; –> Retorna: “es_ES.UTF-8”

SHOW lc_monetary; –> Retorna: “es_ES.UTF-8”

SHOW lc_numeric; –> Retorna: “es_ES.UTF-8”

SHOW lc_time; –> Retorna: “es_ES.UTF-8”

A Windows:

SHOW lc_messages; –> Retorna: “Spanish_Spain.1252”

SHOW lc_monetary; –> Retorna: “Spanish_Spain.1252”

SHOW lc_numeric; –> Retorna: “Spanish_Spain.1252”

SHOW lc_time; –> Retorna: “Spanish_Spain.1252”

Cal aclarir que les localitzacions que pren Postgresql a Linux corresponen a les del sistema operatiu. Per veure l’idioma per defecte cal obrir un terminal i executar:

# echo $LANG

o també executar la consulta de Postgres:

SHOW lc_time; SHOW lc_numeric; SHOW lc_monetary; –etc etc etc

Notareu que la sortida en tots dos casos és igual en Linux. Si es volen afegir les noves localitzacions al sistema operatiu caldrà que reiniciar el servei de Postgresql per poder-hi utilitzar les noves localitzacions.

Les localitzacions que porta per defecte el Linux són POSIX i C i id de l’idioma escollit en el moment d’instal·lar el sistema operatiu, encara que és possible afegir noves localitzacions després de la instal·lació, particularment cal utilitzar a Debian la instrucció: dpkg-reconfigure locales per afegir altres locales.

Si feu servir un Linux podeu veure les localitzacions disponibles i els àlies existents per a les localitzacions respectivament als arxius: /etc/locale.gen i /etc/locale.alias

Establir localitzacions

Ara veurem un exemple amb lc_time per establir la configuració horària.

SELECT to_char(‘Apr 14 1987 12:00AM’::date, ‘TMDay, DD TMMonth YYYY’);

Retorna: “Martes, 14 Abril 1987″

Si trio la configuració de lc_time a anglès:

SET lc_time=‘english’;

ó

SET lc_time=‘C’;

Ara torno a fer la consulta:

SELECT to_char(‘Apr 14 1987 12:00AM’::date, ‘TMDay, DD TMMonth YYYY’);

Retorna: “Tuesday, 14 April 1987″

Finalment: Trio lc_time però ara en castellà.

La configuració es realitzarà amb qualsevol de les localitzacions obtingudes inicialment en el tòpic “1. Mostrar localitzacions:”

SET lc_time=‘spanish’;

ó

SET lc_time=‘Spanish_Spain.1252’;

ó

SET lc_time=‘es_ES.UTF-8’;

Tornem a fer la consulta:

SELECT to_char(‘Apr 14 1987 12:00AM’::date, ‘TMDay, DD TMMonth YYYY’);

Retorna: “Martes, 14 Abril 1987″

Reinicialitzar (reconfigurar) localitzacions

Fins al moment, totes les configuracions fetes han estat únicament de manera temporal. Si volem fer una cosa permanent, cal editar l’arxiu esmentat inicialment. Si volem restablir les localitzacions a la configuració inicial només cal reinicialitzar les localitzacions necessàries:

RESET lc_messages;

RESET lc_monetary;

RESET lc_numeric;

RESET lc_time;

Arquitectura de PostgreSQL

Els components més importants en un sistema PostgreSQL són els que es mostren a la figura següent:

Figura Arquitectura PostgreSQL

PostgreSQL està basat en una arquitectura client/servidor. El programa servidor s’anomena postgres i entre els molts programes client tenim, per exemple, pgaccess , pgAdmin III i psql. Libpq és el responsable de manipular les comunicacions entre l’aplicació client i l’administrador (servei del PostgreSQL en el servidor).

El servidor està compost per dos grans subsistemes, el Postmaster, que és el responsable d’acceptar les comunicacions amb el client i d’autenticar i donar accés, i el Postgre, que s’encarrega d’administrar les consultes i comandes enviades pel client. PostgreSQL treballa sota el concepte de process per user; això significa que hi ha un sol procés client per connexió.

Un procés servidor Postgres pot atendre exclusivament un sol client; és a dir, fan falta tants processos servidor Postgres com clients hi hagi. El procés Postmaster és l’encarregat d’executar un nou servidor Postgres per a cada client que sol·liciti una connexió. Tant el Postmaster com el Postgre han d’estar sempre junts al mateix servidor.

L’equip amfitrió (host) que emmagatzema un conjunt de bases de dades PostgreSQL s’anomena lloc. En un lloc s’executa només un procés Postmaster i múltiples processos Postgres. Els clients es poden executar en el mateix lloc o en equips remots connectats per TCP/IP.

Arxius de configuració

El comportament de PostgreSQL en el nostre sistema es pot controlar amb tres fitxers de configuració que es troben al directori de dades des d’on inicialitzem el nostre clúster PostgreSQL (en el nostre cas, /var/pgsql/data). Aquests tres fitxers són els següents:

  • pg_hba.conf: aquest fitxer s’utilitza per definir els diferents tipus d’accessos que un usuari té en el clúster.
  • pg_ident.conf: aquest fitxer s’utilitza per definir la informació necessària en el cas que utilitzem un accés de tipus ident a pg_hba.conf.
  • postgresql.conf: en aquest fitxer podem canviar tots els paràmetres de configuració que afecten el funcionament i el comportament de PostgreSQL a la nostra màquina.

Vegem a continuació els canvis més importants que podem fer en algun d’aquests fitxers.

pg_hba.conf

És possible restringir l’accés a usuaris o a adreces IP modificant les opcions de l’arxiu pg_hba.conf, que es troba a /etc/postgresql/pg_hba.conf.

Aquest fitxer s’utilitza per definir com, on i des d’on un lloc d’un usuari pot emprar el nostre clúster PostgreSQL.

Totes les línies que comencin pel caràcter # s’interpreten com a comentaris. La resta ha de seguir el format següent:

[Tipus de connexió][database][usuari][IP][Netmask][Tipus d’autentificació][opcions]

Depenent del tipus de connexió i del mètode d’autentificació, [IP],[Netmask] i [opcions] poden ser opcionals. Ara explicarem una mica com definir regles d’accés.

El tipus de connexió pot tenir els valors següents:

  • local
  • host
  • hostssl
  • hostnossl

El tipus de mètode pot tenir els valors següents:

  • trust
  • reject
  • md5
  • crypt
  • password
  • krb5
  • ident
  • pam
  • ldap

Una sèrie d’exemples ens ajudaran a entendre millor com hem de configurar diferents accessos al clúster PostgreSQL.

  • Exemple 1. Accés per TCP/IP (xarxa) a la base de dades test001, com a usuari test des de l’ordinador amb IP 10.0.0.100, i mètode d’autentificació md5:

host test001 test 10.0.0.100 255.255.255.255 md5

Aquesta mateixa entrada es podria escriure també amb la màscara de xarxa en notació CIDR:

host test001 test 10.0.0.100/32 md5

  • Exemple 2. Accés per TCP/IP (xarxa) a la base de dades test001, com a usuari test des de tots els ordinadors de la xarxa 10.0.0.0, amb màscara de xarxa 255.255.255.0 (254 ordinadors en total) i mètode d’autentificació md5:

host test001 test 10.0.0.0 255.255.255.0 md5

Aquesta mateixa entrada es podria escriure també amb la màscara de xarxa en notació CIDR:

host test001 test 10.0.0.0/24 md5

  • Exemple 3. Accés per TCP/IP (xarxa), encriptat, a totes les bases de dades del nostre clúster, com a usuari test des de l’ordinador amb IP 10.0.0.100, i l’ordinador 10.1.1.100 i mètode d’autentificació md5 (necessitem dues entrades al fitxer 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

  • Exemple 4. Denegar l’accés a totes les bases de dades del nostre clúster a l’usuari test, des de tots els ordinadors de la xarxa 10.0.0.0/24 i donar accés a la resta del món amb el mètode md5:

host all test 10.0.0.0/24 reject

host all all 0.0.0.0/0 md5

Així podríem continuar jugant amb totes les possibilitats que ens ofereix aquest fitxer de configuració. Donem per suposat que a les bases de dades i els usuaris emprats en aquest fitxer han d’existir en el nostre clúster perquè tot funcioni, i que a més alguns dels paràmetres només es podran usar si hem compilat amb les opcions pertinents en el procés d’instal·lació (per exemple, hostssl, pam, krb5).

Per posar en producció els canvis que fem en aquest fitxer cal donar l’ordre a PostgreSQL perquè torni a llegir-lo. Cal un senzill reload:

/usr/local/bin/pg_ctl -D /var/pgsql/data reload

des de la línia d’ordres o amb la funció pg_reload_conf() com a usuari Postgres des de psql, el client PostgreSQL.

Per a una documentació detallada sobre el fitxer pg_hba.conf, cal donar un cop d’ull a la secció “Chapter 20. Client Authentication” de la documentació oficial de 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=#

postgresql.conf

Els canvis que farem en aquest fitxer afectaran totes les bases de dades que tinguem definides al nostre clúster PostgreSQL. La majoria dels canvis es poden posar en producció amb un senzill reload:

/usr/local/bin/pg_ctl -D /var/pgsql/data reload

Altres canvis demanaran que tornem a arrancar el clúster:

/usr/local/bin/pg_ctl -D /var/pgsql/data restart

Tota la informació sobre tots els paràmetres que podem canviar en aquest fitxer, a què afecten i com es poden posar en producció es pot trobar a la secció 17, “Server Configuration”, de la documentació oficial de PostgreSQL.

Per habilitar la connexió a PostgreSQL des de clients remots, hem de verificar el paràmetre tcpip_socket = true al fitxer /etc/postgresql/postgresql.conf.

A continuació, vegem els paràmetres més importants que caldria canviar si comencem a emprar PostgreSQL per a un ús seriós i volem treure el màxim partit a la nostra màquina:

  • max_connections: nombre màxim de clients connectats a l’hora a les nostres bases de dades. Caldria incrementar aquest valor en proporció del nombre de clients concurrents al nostre clúster PostgreSQL. Un bon valor seria començar amb 100:

max_connections = 100

  • shared_buffers: aquest paràmetre és importantíssim i defineix la mida del buffer emprat per PostgreSQL. Incrementar molt aquest valor no vol dir tenir una resposta més bona. En un servidor dedicat podem començar amb un 25% del total de la nostra memòria. Mai més d’1/3 (33%) del total. Per exemple, en un servidor amb 4 Gbytes de memòria, podem usar 1024MB com a valor inicial.

shared_buffers = 1024MB

  • work_mem: usada en operacions que continguin ORDER BY, DISTINCT, joins, … En un servidor dedicat podem usar un 2-4% del total de la nostra memòria si tenim només unes quantes sessions (clients) grans. Com a valor inicial podem usar 8 Mbytes.

work_mem = 8MB

  • maintenance_work_mem: usada en operacions del tipus VACUUM, ANALYZE, CREATE INDEX, ALTER TABLE, ADD FOREIGN KEY. El seu valor dependrà molt de la mida de les nostres bases de dades. Per exemple, en un servidor con 4Gbytes de memòria, podem usar 256MB com a valor inicial.

maintenance_work_mem = 256MB

  • effective_cache_size: paràmetre usat pel planificador de consultes (query planner) del nostre motor de bases de dades per optimitzar la lectura de dades. En un servidor dedicat podem començar amb un 50% del total de la nostra memòria. Com a màxim uns 2/3 (66%) del total. Per exemple, en un servidor con 4Gbytes de memòria, podem usar 2048MB com a valor inicial.

effective_cache_size = 2048MB

  • checkpoint_segments: aquest paràmetre és molt important en bases de dades amb nombroses operacions d’escriptura (insert, update, delete). Per començar podem començar amb un valor de 64. En grans bases de dades amb molts Gbytes de dades escrites podem augmentar aquest valor fins a 128-256.

checkpoint_segments = 64

Tota la informació detallada per fer això es pot consultar a la secció 16.4 “Managing Kernel Resources” de la documentació oficial de PostgreSQL.

És molt important tenir en compte que, en augmentar els valors per defecte de molts d’aquests paràmetres, haurem d’augmentar els valors per defecte d’alguns paràmetres del nucli (kernel) del nostre sistema.

Anar a la pàgina anterior:
Exercicis
Anar a la pàgina següent:
SGBD distribuïts