curso de administración de postgresql - uv.es · – seguridad – procesamiento de instrucciones...

Administración de PostgreSQL

Jose M. Alarcón Medina (ITI - Formación)

Resumen

● Objetivos● Plan del curso● Metodología y Materiales● Desarrollo del curso● Próximos pasos

Objetivos

● Proporcionar a los profesionales responsables de las bases de datos los conocimientos para la instalación y administración de una base de datos PostgreSQL.

● El alumno al finalizar el curso sabrá cómo instalar PostgreSQL, configurar el entorno así como administrar los recursos que proporciona la base de datos.

Plan del curso (I)● Dia I:

– Presentación e Introducción– Estructura física y lógica

● Día II:– Instalación y Desinstalación– Puesta en marcha

● Día III:– Seguridad– Procesamiento de instrucciones y transacciones

Plan del curso (y II)● Día IV:

– Configuración– Copias de seguridad y respaldo– Tareas administrativas

● Día V:– Rendimiento– Monitorización– Afinamiento– Catálogo del Sistema– Otros temas

Metodología y Materiales● Metodología:

– Las clases teórico-prácticas.– Servidor central al que puedan conectarse los alumnos, así como

instalaciones en sus ordenadores. – Se proporcionaran ejercicios para realizar las prácticas.– El alumno al finalizar el curso sabrá cómo instalar PostgreSQL,

configurar el entorno así como administrar los recursos que proporciona la base de datos.

● Materiales:– Manual del curso– Boletines de prácticas con soluciones– Scripts de ayuda y ejemplo– http://www.uv.es/alarmedi/iti2007

Desarrollo del Curso

Introducción

● Presentación● SGBD, Lenguajes● Historia PostgreSQL● Uso de las herramientas● Psql y pgAdmin3

Estructura PostgreSQL (I)

● Arquitectura– Procesos Postmaster / Postgres (uso del socket

TCP/IP)– Memoria compartida– Almacenamiento en Disco

● Concepto: CLUSTER BASE DE DATOS

Estructura PostgreSQL (II)

● Estructura Física– Zona Memoria Compartida– Socket de TCP/IP– Almacenamiento en disco

● INITDB: Creación del cluster– Ficheros– Directorios

Estructura PostgreSQL – initdb (III)● Opciones:● [-D, --pgdata=]DATADIR ubicación para este cluster de bases de datos● -E, --encoding=CODIFICACION codificación por omisión para nuevas bases de datos● --locale=LOCALE inicializar usando esta configuración local● -A, --auth=METODO método de autentificación por omisión para

conexiones locales● -U, --username=USUARIO nombre del superusuario del cluster● -?, --help mostrar esta ayuda y salir

Si el directorio de datos no es especificado, se usa la variable de ambiente PGDATA.

Estructura PostgreSQL (IV)

● Estructura Lógica– Bases de Datos– Tablespaces– Roles de grupo– Roles de login

● Forman el CLUSTER DE BASE DE DATOS

Estructura Lógica:Creación bases de datos (V)

$ createdb [OPCIÓN]... [NOMBRE] [DESCRIPCIÓN]Opciones:

-D, --tablespace=TBLSPC tablespace por omisión de la base de datos -E, --encoding=CODIFICACIÓN codificación para la base de datos -O, --owner=DUEÑO usuario que será dueño de la base de datos -T, --template=PATRÓN base de datos patrón a copiar -e, --echo mostrar los comandos enviados al servidor -q, --quiet no desplegar mensajes --help mostrar esta ayuda y salir --version mostrar el n·mero de versión y salir

Opciones de conexión: -h, --host=ANFITRIÓN nombre del servidor o directorio del socket -p, --port=PUERTO puerto del servidor -U, --username=USUARIO nombre de usuario para la conexión -W, --password preguntar la contraseña

Si no se especifica, se creará una base de datos con el mismo nombre que el usuario actual.

Estructura Lógica:Creación bases de datos (VI)

Desde SQL:CREATE DATABASE name [ [ WITH ] [ OWNER [=] dbowner ] [ TEMPLATE [=] template ] [ ENCODING [=] encoding ] [ TABLESPACE [=] tablespace ] [ CONNECTION LIMIT [=] connlimit ] ];

Estructura Lógica:Creación usuarios y roles (VII)

createuser [OPCIÓN]... [ROL]Opciones: -s, --superuser el rol será un superusuario -S, --no-superuser el rol no será un superusuario -d, --createdb el rol podrá crear bases de datos -D, --no-createdb el rol no podrá crear bases de datos -r, --createrole el rol podrá crear otros roles -R, --no-createrole el rol no podrá crear otros roles -l, --login el rol podrá conectarse (predeterminado) -L, --no-login el rol no podrá conectarse -i, --inherit el rol heredará los privilegios de los roles de los cuales es miembro (predeterminado) -I, --no-inherit rol no heredará privilegios -c, --connection-limit=N límite de conexiones para el rol (predeterminado: sin límite) -P, --pwprompt asignar una contraseña al nuevo rol -E, --encrypted almacenar la constraseña cifrada -N, --unencrypted almacenar la contraseña sin cifrar -e, --echo mostrar los comandos a medida que se ejecutan -q, --quiet no escribir ningún mensaje --help desplegar esta ayuda y salir --version desplegar información de versión y salir

Estructura Lógica:Creación usuarios y roles (VIII)

... Continuacióncreateuser [OPCIÓN]... [ROL]Opciones de conexión: -h, --host=ANFITRIÓN nombre del servidor o directorio del socket -p, --port=PUERTO puerto del servidor -U, --username=NOMBRE nombre de usuario con el cual conectarse (no el usuario a crear) -W, --password pedir contraseña para conectarse

Si no se especifican -s, -S, -d, -D, -r, -R o el ROL, se preguntaráinteractivamente.

Desde SQL:CREATE ROLE name [ [ WITH ] option [ ... ] ] SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | CREATEUSER | NOCREATEUSER | INHERIT | NOINHERIT | LOGIN | NOLOGIN | CONNECTION LIMIT connlimit | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'timestamp' | IN ROLE rolename [, ...] | IN GROUP rolename [, ...] | ROLE rolename [, ...] | ADMIN rolename [, ...] | USER rolename [, ...] | SYSID uid

Estructura Lógica:Creación de tablespaces (y IX)

CREATE TABLESPACE tablespacename [ OWNER username ] LOCATION 'directory'

Instalación / Desinstalación (I)

● Instalación para Windows● Instalación para Linux

– Desde paquetes● Debian (apt-get)● RedHat – SUSE (rpm)

– Desde ficheros fuente

Instalación / Desinstalación Desde ficheros fuente (II)

● Instalación– Decisiones iniciales– Requerimientos de instalación y hardware– Configuración del entorno– Descarga del software– Compilación y enlazado– Configuración del usuario propietario

● Instalación de un cliente● Desinstalación

Instalación / Desinstalación Migraciones (III)

● Migraciones – Sin conservar el cluster– Conservando el cluster

● Instalación de varios PostgreSQL en el mismo servidor

Instalación / Desinstalación Más cosas (y IV)

● Configuración:– Parámetros del Kernel– Parámetros de funcionamiento

● Instalación de extensiones

Puesta en Marcha (I)

● Puesta en marcha y para del servidor, existen dos modos:– postmaster -> postgres– pg_ctl

● Parada manual (modos)● Puesta en marcha y parada automática

Puesta en Marchapostmaster - postgres (II)

postmaster [OPCION]...Opciones:

-B NBUFFERS número de búfers de memoria compartida -c VAR=VALOR definir parámetro de ejecución -d 1-5 nivel de depuración -D DATADIR directorio de bases de datos -e usar estilo europeo de fechas (DMY) -F desactivar fsync -h NOMBRE nombre de host o dirección IP en que escuchar -i activar conexiones TCP/IP -k DIRECTORIO ubicación del socket Unix -N MAX-CONN número máximo de conexiones permitidas -o OPCIONES pasar «OPCIONES» a cada proceso servidor (obsoleto) -p PUERTO número de puerto en el cual escuchar -s mostrar estadísticas después de cada consulta -S WORK-MEM mostrar cantidad de memoria para ordenamientos (en kB) --NOMBRE=VALOR definir parámetro de ejecución --describe-config mostrar parámetros de configuración y salir --help desplegar esta ayuda y salir --version desplegar número de versión y salir

Puesta en Marchapostmaster - postgres (III)

postmaster [OPCION]...Opciones de desarrollador: -f s|i|n|m|h impedir el uso de algunos tipos de planes -n no reinicializar memoria compartida después de salida anormal -O permitir cambios en estructura de tablas de sistema -P desactivar índices de sistema -t pa|pl|ex mostrar tiempos después de cada consulta -T enviar SIGSTOP a todos los backends si uno de ellos muere -W NÚM espera NÚM segundos para permitir acoplar un depurador

Opciones para modo mono-usuario: --single selecciona modo mono-usuario (debe ser el primer argumento) DBNAME nombre de base de datos (el valor por omisión es el nombre de usuario) -d 0-5 nivel de depuración -E mostrar las consultas antes de su ejecución -j no usar saltos de línea como delimitadores de consulta -r ARCHIVO enviar salida estándar y de error a ARCHIVO

Opciones para modo de inicio (bootstrapping): --boot selecciona modo de inicio (debe ser el primer argumento) DBNAME nombre de base de datos (argumento obligatorio en modo de inicio) -r ARCHIVO enviar salida estándar y de error a ARCHIVO -x NUM uso interno

Puesta en Marchapg_ctl (IV)

Empleo: pg_ctl start [-w] [-D DATADIR] [-s] [-l ARCHIVO] [-o «OPCIONES»] pg_ctl stop [-W] [-D DATADIR] [-s] [-m MODO-DETENCIÓN] pg_ctl restart [-w] [-D DATADIR] [-s] [-m MODO-DETENCIÓN] [-o «OPCIONES»] pg_ctl reload [-D DATADIR] [-s] pg_ctl status [-D DATADIR] pg_ctl kill NOMBRE-SEÑAL ID-DE-PROCESOOpciones comunes: -D, --pgdata DATADIR ubicación del área de almacenamiento de datos -s, --silent mostrar sólo errores, no mensajes de información -w esperar hasta que la operación se haya completado -W no esperar hasta que la operación se haya completado --help mostrar este texto y salir --version mostrar información sobre versión y salir(Por omisión se espera para las detenciones, pero no los inicios o reinicios)

Si la opción -D es omitida, se usa la variable de ambiente PGDATA.

Opciones para inicio y reinicio: -l --log ARCHIVO guardar el registro del servidor en ARCHIVO. -o OPCIONES parámetros de línea de órdenes a pasar a postgres (ejecutable del servidor de PostgreSQL) -p RUTA-A-POSTGRES normalmente no es necesario

Puesta en Marchapg_ctl (V)

--- continuación

Empleo: pg_ctl start [-w] [-D DATADIR] [-s] [-l ARCHIVO] [-o «OPCIONES»] pg_ctl stop [-W] [-D DATADIR] [-s] [-m MODO-DETENCIÓN] pg_ctl restart [-w] [-D DATADIR] [-s] [-m MODO-DETENCIÓN] [-o «OPCIONES»] pg_ctl reload [-D DATADIR] [-s] pg_ctl status [-D DATADIR] pg_ctl kill NOMBRE-SEÑAL ID-DE-PROCESO

Opciones para detención y reinicio: -m MODO-DE-DETENCIÓN puede ser «smart», «fast» o «immediate»

Modos de detención son: smart salir después que todos los clientes se hayan desconectado fast salir directamente, con apagado apropiado immediate salir sin apagado completo; se ejecutará recuperación en el próximo inicio

Nombres de señales permitidos para kill: HUP INT QUIT ABRT TERM USR1 USR2

Seguridad en PostgreSQL

● Seguridad en la gestión de ficheros● Seguridad en el acceso de los clientes● Seguridad lógica: privilegios

Seguridad: Gestión de ficheros

● Ficheros binarios● Ficheros $PGDATA (cluster)● Ficheros de tablespaces● Ficheros de configuración● Socket para conexiones locales

Seguridad: Acceso clientes (I)

● Ficheros de configuración:– pg_hba.conf– pg_ident.conf

● Fichero pg_hba.conf: configuración de autenticación

TIPO BASE DATOS USUARIO DIRECCION METODOLOCAL <base datos> <usuario> <metodo-autenticacion [opción]HOST <base datos> <usuario> <direccionCIDR> <metodo-autenticacion [opción]HOSTSSL <base datos> <usuario> <direccionCIDR> <metodo-autenticacion [opción]HOSTNOSSL <base datos> <usuario> <direccionCIDR> <metodo-autenticacion [opción]

Seguridad: Acceso clientes (II)

● Métodos de autenticación:– Trust– Reject– Password– Crypt– MD5– KRB5– Ident (sameuser / mapeo usuario -> pg_ident.conf)– PAM– LDAP

Seguridad: Nivel lógico

● Creación de roles (superuser, createuser, createdb)

● GRANT y REVOKE:– Bases de datos– Tablespaces– Lenguajes– Esquemas– Tablas y vistas– Procedimientos y funciones

Procesamiento de Instrucciones (I)

● Ejecución de sentencias (envio, análisis, preparación, ejecución y recuperación)

● Elementos que intervienen:– Shared Buffers (lista sucia)– Discos– Buffers WAL– Ficheros WAL

Procesamiento de Instrucciones (y II)

Gestión de transacciones (I)

● Cumplimiento reglas ACID:– Atomicidad (atomicity)– Consistencia (consistency)– Aislamiento (isolatio)– Persistencia (durability)

Gestión transacciones: Atomicidad (II)

● Todo o nada● BEGIN / COMMIT / ROLLBACK

– Restricciones inmediatas o diferidas– Modo autoconfirmación

● SAVEPOINTBEGIN;… operaciones1SAVEPOINT paso2;… operaciones2… si error => ROLLBACK paso2;… operaciones3COMMIT;

Gestión transacciones: Consistencia (III)

● Reglas de integridad:– Not null– Check– Unique– Primary Key– Foreign Key: únicas que son diferibles:[DEFERRABLE]

[INITIALLY {INMEDIATE | DEFERRED}]

Gestión transacciones: Consistencia (IV)

● Foreign Key:[DEFERRABLE]

[INITIALLY {INMEDIATE | DEFERRED}]– Deferrable: el punto de comprobación se puede

cambiar dinámicamente– Inmediate: se comprueba después de cada

sentencia SQL– Deferred: la comprobación se realiza al final de

la transacción.

Gestión transacciones: Aislamiento (IV)

● Control de concurrencia:– No se basa en el uso bloqueos– Se basa en el uso de MVCC (multiversion

Concurrency Control)● MVCC: cada transacción ve una

“instantánea” de la BD tal como era al empezar la transacción.

● Lectores y escritores no necesitan esperarse.

Gestión transacciones: Aislamiento (V)

● Niveles Aislamiento SQL92:

● Bloqueos sobre tablas– a nivel de tabla– a nivel de fila

Nivel Lectura sucia Lectura no repetible Lectura Fantasma BloqueosLectura no confirmada Posible Posible Posible NuncaLectura confirmada No Posible Posible Posible Si hay actualizacionesLectura repetible No Posible No Posible Posible Si hay consultasSerializable No Posible No Posible No Posible Siempre (fácil deadlock)

Gestión transacciones: Persistencia (y VI)

● Ficheros WAL (Write-ahead loggin): ficheros de escritura adelantada.

● Ficheros de 16Mb● Procesos REDO / UNDO

Configuración entorno de ejecución (I)

● Gestión de recursos del Kernel, parámetros:– SHMMAX, SHMMIN, SHMALL, SHMSEG,

SHMNI– SEMMNI, SEMMNS, SEMMSL, SEMMAP,

SEMVMX

Configuración entorno de ejecución (II)

● Fichero postgresql.conf● Activación de la modificación de valores:

– Con el servidor en marcha (SET)– Recargando el fichero (pg_ctl reload)– Reiniciando el servidor (pg_ctl restart)

Configuración entorno - postgresql.conf (III)

● Ubicación de ficheros● Conexión

– listen_adresses– Port– max_connections– superuser_reserved_connections– unix_socket_directory

● Seguridad y autenticado– ssl

Configuración entorno - postgresql.conf (IV)

● Uso de recursos– shared_buffers– work_mem– max_prepared_transaction– max_fsm_relations– max_fsm_pages– max_lock_per_transaction– Retraso de vacuum– Proceso de fondo writer

● WAL– wal_buffers– checkpoints_segments– checkpoint_timeout

Configuración entorno - postgresql.conf (V)

● Ajuste de rendimiento de consultas● Errores / fichero de seguimiento

– Lugar de seguimiento– Cuándo se efectúa– Qué se registra

● Estadísticas (recolección)● Vacuum● Conexión cliente

– search_path– default_transaction_isolation– datestyle– client_encoding

Configuración entorno - postgresql.conf (y VI)

● Gestión de bloqueos– deadlock_timeout–

● Opciones predefinidas– sólo se pueden cambiar en la instalación– block_size– integer_datetime– lc_collate, lc_ctype– max_identifier_length– max_ident_keys– server_encoding

Internacionalización y localización (I)

● Mensajes en varios idiomas● Mensajes en varios juegos de caracteres● Diversos tipos de ordenaciones● Clasificación de caracteres● Formatos de monedas, fechas, etc.

<idioma>_<ubicacion>.<cjto_caracteres>@<modificadores>

Codificación: Responsabilidades (II)

● Juego de caracteres en la base de datos = Administrador de base de datos

● Juego de caracteres en las aplicaciones = Responsable de las aplicaciones

● En el proceso de migración es posible que estos dos juegos sean distintos. Ejemplo estado actual: base de datos en UTF8 y aplicaciones en ISO-8859-1/15

Codificación: ejemplos problemas (III)

● Error al usar codificaciones distintas:– España (UTF8) --> España (Latin1)– Jesús Pérez (Latin1) --> Jes?s P?ez (UTF8)¿Cuándo se puede dar este problema?:

● Terminales distintas● Uso de editores incorrectos● Transmisiones de ficheros: FTP, CVS ...

Copias de seguridad y Recuperación (I)

● Copias de seguridad● Recuperación de datos● Migración de versiones

Copias de seguridad(II)

● Copia de seguridad de ficheros del S.O.– Base de datos parada– No se pueden recuperar partes

● Volcado SQL– pg_dump– pg_dumpall

● Volcado en línea y recuperación PITR

● Conviene automatizar: cron, scripts ...

Copias de seguridad – pg_dump (III)

● Volcar una bd o parte de ella.● En texto plano:

– Fichero de texto con instrucciones SQL– Uso de psql para restaurar– Portable a servidores SQL

● En formato propio de PostgreSQL– Se usa pg_restore para restaurar– Más flexible– Con pg_restore se puede crear archivo SQL.

Copias de seguridad – pg_dump (IV)

Uso: pg_dump [OPCIÓN]... [NOMBREDB]

Opciones generales: -f, --file=ARCHIVO nombre del archivo de salida -F, --format=c|t|p Formato del archivo de salida (personalizado, tar, sólo texto) -i, --ignore-version procede aún cuando las versiones del servidor y pg_dump no coinciden -Z, --compress=0-9 nivel de compresión para formatos comprimidos

Opciones que controlan el contenido de la salida: -a, --data-only extrae sólo los datos, no el esquema -b, --blobs incluye objetos grandes en la extracción -C, --create incluye comandos para crear la base de datos en la extracción -d, --inserts extrae los datos usando INSERT, en vez de COPY -D, --column-inserts extrae los datos usando INSERT con nombres de columnas -E, --encoding=CODIFIC extrae los datos con la codificación CODIFIC -n, --schema=ESQUEMA extrae sólo el esquema nombrado -N, --exclude-schema=ESQUEMA NO extrae el o los esquemas listados -o, --oids incluye OIDs en la extracción -O, --no-owner en formato de sólo texto, no reestablecer los dueños de los objetos -s, --schema-only extrae sólo el esquema, no los datos -t, --table=TABLE extrae sólo la tabla nombrada -x, --no-privileges no extrae los privilegios (grant/revoke) --use-set-session-authorization usa comandos SESSION AUTHORIZATION en lugar de comandos OWNER TO

Copias de seguridad – pg_dump (V)

Uso: pg_dump [OPCIÓN]... [NOMBREDB]

Opciones de la conexión: -h, --host=ANFITRIÓN anfitrión de la base de datos o el directorio del enchufe (socket) -p, --port=PUERTO número del puerto de la base de datos -U, --username=USUARIO nombre de usuario con el cual conectarse -W, --password fuerza un prompt para la contraseña (debería ser automático)

Si no se especifica un nombre de base de datos entonces el valor de la variable de ambiente PGDATABASE es usado.

Copias de seguridad – pg_dumpall (VI)

● Diferencias con pg_dump● No permite fichero de salida: uso de

redirección● No permite formatos: siempre texto plano● Opción '-g': exportar objetos globales

Copias de seguridad – pg_dumpall (VII)

Uso: pg_dumpall [OPCIÓN]...

Opciones que controlan el contenido de la salida: -a, --data-only extrae sólo los datos, no el esquema -c, --clean tira la base de datos antes de crearla -d, --inserts extrae los datos usando INSERT, en vez de COPY -D, --column-inserts extrae los datos usando INSERT con nombres de columnas -g, --globals-only extrae sólo los objetos globales, no bases de datos -o, --oids incluye OIDs en la extracción -O, --no-owner no reestablece los dueños de los objetos -s, --schema-only extrae sólo el esquema, no los datos -S, --superuser=NAME especifica el nombre del superusuario a usar en el guión -x, --no-privileges no extrae los privilegios (grant/revoke) -X use-set-session-authorization, --use-set-session-authorization usa comandos SESSION AUTHORIZATION en lugar de comandos OWNER TO

Opciones de la conexión:

-h, --host=ANFITRIÓN anfitrión de la base de datos o el directorio del enchufe (socket) -p, --port=PUERTO número del puerto de la base de datos -U, --username=USUARIO nombre de usuario con el cual conectarse -W, --password fuerza un prompt para la contraseña (debería ser automático)

Recuperación de Datos (I)

● Con psql a partir de un fichero SQL● Con pg_restore

– Directamente a una base de datos– Volcando a fichero SQL

● Volcado en línea y recuperación PITR– Habilitar archivado WAL– Copias de seguridad en línea– Recuperación PITR

Recuperación Datos: Volcado Línea + PITR (y II)

● Habilitar archivado WAL– Parámetro archive_command

● Copias de seguridad en línea– Select pg_start_backup('nombre_backup');– Copia física– Select pg_stop_backup();

● Recuperación PITR– Parar base datos– Recuperar copia física– Copiar ficheros WAL de la copia– Fichero recovery.conf– Arrancar base datos

COPY TO / COPY FROM

Copiar datos de una consulta a un fichero

COPY tablename [ ( column [, ...] ) ] FROM { 'filename' | STDIN } [ [ WITH ] [ BINARY ] [ OIDS ] [ DELIMITER [ AS ] 'delimiter' ] [ NULL [ AS ] 'null string' ] [ CSV [ HEADER ] [ QUOTE [ AS ] 'quote' ] [ ESCAPE [ AS ] 'escape' ] [ FORCE NOT NULL column [, ...] ];

Copiar datos de un fichero a una tabla

COPY { tablename [ ( column [, ...] ) ] | ( query ) } TO { 'filename' | STDOUT } [ [ WITH ] [ BINARY ] [ HEADER ] [ OIDS ] [ DELIMITER [ AS ] 'delimiter' ] [ NULL [ AS ] 'null string' ] [ CSV [ HEADER ] [ QUOTE [ AS ] 'quote' ] [ ESCAPE [ AS ] 'escape' ] [ FORCE QUOTE column [, ...] ];

VACUUM (I)

● Funciones:– Recuperar espacio en disco (filas modificadas

o borradas)– Actualizar estadísticas– Evitar pérdidas de datos antiguos por reuso de

identificador de transacciones● Ejecutar

– desde S.O. o desde SQL– Sobre una tabla o sobre la base de datos

VACUUM (y II)

● Opciones– FULL: libera y reorganiza– No FULL: solo libera– ANALYZE– FREEZE: inicializa XID (identif. transacciones)

● Estrategias● Autovacuum

REINDEX

● Desde S.O. ● Desde SQL● De tablas, índices, base de datos y sistema● Cuidado con los archivos WAL

Fichero de Seguimiento

● Formas de seguimiento– Salida estandar– Fichero indicado al iniciar– Usar syslog del sistema

● Conveniencia de rotación de logs:– Mejor información– Mejor rendimiento

● Parámetros en postgresql.conf– Dónde y cómo– Cuándo– Qué

Catálogo del Sistema

● Conjunto de tablas y vistas: constituyen un diccionario con metadatos

● Catálogo compartido (todo el cluster):– Pocas tablas: pg_databases, pg_shadow,

pg_group ...● Catálogo de cada base de datos: la mayoría

de tablas y vistas. Esquemas:– pg_catalog– pg_toast

Monitorización (I)

● De la actividad de la base de datos– Por comandos del sistema operativo– Usando el recolector de estadísticas– Consultado pg_locks– Compilarlo con traza dinámica

● Del uso de los discos

Monitorización: actividad base datos (II)

● Por comandos del sistema operativo– ps– top– vmstat– iostat– free

Monitorización: actividad base de datos (III)

● Uso del recolector de estadísticas:– Información de:

● Acceso a las tablas e índices● Instrucciones ejecutadas● Lecturas de disco● Uso de la caché, etc.

– postgresql.conf: start_stat_collector– Tipos:

● pg_stat*: frecuencias y tipos de accesos● pg_statio*: entrada/salida física● pg_locks: bloqueos y transacciones

Monitorización: uso de los discos (IV)

● Controlado por postgresql: el dba no tiene control

● Fichero inicial (heap) y fichero de desborde (toast)

● Formas de contro:– Vistas del catálogo– Extensiones “contrib”:

● dbsize● oid2name

Afinamiento, mejora del rendimiento (I)

● Gestión del diario (WAL)– checkpoint_segments– checkpoint_timeout

● Buffers de diario (caché WAL)– wal_buffers– fsync / wal_sync_method– commit_delay

Afinamiento, mejora del rendimiento (II)

● Caché de base de datos– Minimizar acceso a disco– Aumentar shared_buffers (shmmax, shmmni,

shmall)– Uso de vistas pg_statio*

● Acceso a discos– Reindex, vacuum, autovacuum– Uso de tablespaces– Control de toast

Afinamiento, mejora del rendimiento (III)

● Optimización de consultas– Elementos:

● Analizador (análisis sintáctico y semántico)● Planificador (generación planes ejecución)● Optimizador (búsqueda plan más económico)

– Uso de índices (btree, rtree, hash, GiST) para búsquedas y ordenaciones

– Uso de EXPLAIN para ver el plan de ejecución

Otros aspectos

● Pseudo-columnas del sistema: oid, ctid, tableoid ...

● Secuencias para Pks● Tipos básicos especiales: point, polygon,

cidr, macaddr, etc.● Tipos compuestos● Herencia de tablas: de datos, no de

estructura.

Próximos pasos

● Consultar documentación de PostgreSQL● Buscar en proyectos de PostgreSQL● Curso para Desarrolladores (programación

con PostgreSQL)● Curso de optimización SQL● Cluster, alta disponibilidad, balanceo de

carga, etc.

Resumen

● Objetivos● Plan del curso● Metodología y Materiales● Desarrollo del curso● Próximos pasos

FIN

● Conclusiones● Opiniones del curso a alarcon_jos@gva.es

Muchas graciasJosé M. Alarcón Medina