00:00:00

Formación PostgreSQL

APSL

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Agenda

  • Introducción
  • Historia
  • Arquitectura
  • Configuración
  • Operación cluster
  • Monitorización
  • Sistema Operativo y rendimiento
  • Backup. PTR.
  • Replicación
  • Cluster Pacemaker (linux-HA)

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

APSL

  • Usamos PostgreSQL en producción desde versión 7.4 (2004).
  • Usos Postgres:
    • Tuning para arquitecturas Web
    • Mejor Backend para ORM Django
    • BD OpenERP
    • Hot backups. PITR. Wal-e, Bacula/Bareos
    • HA con pacemaker: Master/Slave replica.
  • Los profes :P
    • Edu Herraiz eherraiz@apsl.net
    • Bernardo Cabezas bcabezas@apsl.net
  • http://cursos.apsl.net/postgresql

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

PostgreSQL

PostgreSQL es un sistema de gestión de bases de datos objeto-relacional, distribuido bajo licencia BSD. Características a destacar:

  • Es una base de datos ACID compilant
  • Integridad referencial
  • Tablespaces
  • Replicación asincrónica/sincrónica / Streaming replication - Hot Standby
  • PITR - point in time recovery
  • Copias de seguridad en caliente (Online/hot backups)
  • Multi-Version Concurrency Control (MVCC)
  • Multiples métodos de autentificación
  • Actualización in-situ (pg_upgrade)
  • Linux, UNIX (AIX, BSD, HP-UX, Mac OS X, Tru64), y Windows 32/64bit
  • ...

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Historia

  • Se inició como proyecto académico en Berkeley (1985), por Michael Stonebraker, bajo el nombre de Postgres, y con licencia BSD.
  • Se trabajó hasta 1994, hasta la versión 4, cuando el proyecto académico se disolvió.
  • Postgres fué comercializado como Illustra, comprado por Informix e integrado en su paquete Universal Server. Informix fué compardo por IBM en 2001.
  • En 1995 retomaron el trabajo Andrew Yu y Jolly Chen para añadir soporte al lenguaje SQL, creando postgres95.
  • En 1996 se introducen colaboradores fuera del ámbito universitario coordinandose por internet. Se decide cambiar el nombre a PostgreSQL.
  • Desde entonces, se ha seguido el desarrollo, y como la licencia lo permite, muchas empresas se han involucrado en su comercialización. The PostgreSQL Global Development Group (PGDG)

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Versiones y rendimiento

  • Hay mejores alternativas para data warehouse (cambiando).
  • Para BD sencillas sin integridad referencial, puede ser más rápida MySQL o incluso sqlite
  • En medio de estos 2 extremos, es una de las mejores soluciones, y el cuello de botella será el hardware o la app.

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Versiones y rendimiento

Postgres History

  • Conclusiones:
    • Para mejorar rendimiento, antes de pensar en cualquier tipo de tuning: Actualizar a la última Versión
    • Cualquier conclusión antigua sobre rendimiento, está desactualizada.

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Versiones

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Distribución PostgreSQL

  • PosgreSQL distribuye sólo core y utilidades que sólo se pueden desarrollar como parte de la misma BD (psql).
    • Si algo se puede mantener fuera del core, se mantiene como proyecto externo.
  • Major versions:
    • Normalmente, no compatibles hacia atrás.
    • pg_upgrade
    • 5 años soporte.
  • Minor versions:
    • Actualización sencilla. Actualizar siempre a la última minor disponible.

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Distribución PostgreSQL

  • PostgreSQL contrib

    • Utilidades que no están instaladas por defecto. Desarrolladas y distribuidas con el core.
    • Desde 9.1: CREATE EXTENSION module_name;
    • Lista contrib modules
  • PgFoundry

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Arquitectura Básica PostgreSQL

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Diagrama cortesía de: http://raghavt.blogspot.in/2011/04/postgresql-90-architecture.html

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Arquitectura Básica PostgreSQL

  • Una instancia PostgreSQL consta de una serie de procesos y memoria.
  • Arquitectura cliente/servidor, tipo process per-user
    • postmaster: daemon supervisor. Controla acceso a segmento shmmem
    • utility porcesses: bgwriter, walriter, syslogger, archiver, statscollector, autovacuum launcher.
    • user Backend process: Proceso postgres dedicado a usuario. Server process.

Cuando un cliente abre nueva conexión, Postmaster autentifica y autoriza, y pasa el control a un proceso backend server.

PostgreSQL usa multiprocesos en vez de multihilos para garantizar la estabilidad del sistema. Un fallo en uno de los procesos no afectará el resto

Cada proceso puede relaizar una sola query en un momento dado, y comparte con el resto una serie de zonas de memoria shmmem

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Arquitectura PostgreSQL · Procesos

Utility Process:

  • Obligatorios: No tienen opción habilitar/deshabilitar
    • bgwriter
    • wal writer
    • checkpointer
  • Opcionales
    • stats collector
    • autovacuum launcher
    • archiver
    • syslogger
    • wal sender, wal reciver

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Arquitectura PostgreSQL · Procesos

ps -u postgres o pid,stat,cmd

bercab@kiwi7:~$ ps -u postgres o pid,stat,cmd
PID STAT CMD
15997 Ss   postgres: spusr spokenpic [local] idle in transaction                                                                       
16033 Ss   postgres: spusr spokenpic [local] idle                                                                                      
19350 S    /usr/lib/postgresql/9.3/bin/postgres -D /var/lib/postgresql/9.3/main -c config_file=/etc/postgresql/9.3/main/postgresql.conf
19352 Ss   postgres: checkpointer process                                                                                              
19353 Ss   postgres: writer process                                                                                                    
19354 Ss   postgres: wal writer process                                                                                                
19355 Ss   postgres: autovacuum launcher process                                                                                       
19356 Ss   postgres: archiver process   last was 00000001000000150000009F                                                              
19357 Ss   postgres: stats collector process                                                                                           
24088 Ss   postgres: uaploy aploy 127.0.0.1(48948) idle                                                                                
29196 S    /usr/sbin/pgbouncer -R -d /etc/pgbouncer/pgbouncer.ini

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

WAL

Write-Ahead Logging

  • Asegurar integridad de datos mejorando el rendimiento
  • Los cambios a un archivo de log en disco antes de aplicarlos
  • Objetivo: evitar escrituras a disco
  • Problemas y cambios no están en disco? Podemos recuperar del log
  • Aprovechable : Master/esclavo, backup contínuo, restauración punto en el tiempo
  • Introducción a WAL en la doc de postgresql
  • Wal en depesz.com

Notes

Write-Ahead Logging (WAL) WAL es un método estándar para asegurar integridad de datos. El método es complejo pero, en resumen, se basa en que no se aplica ningún cambio en los archivos de datos (tablas e índices) hasta que los cambios se han logueado. Es decir, que estén bajo almacenamiento permanente (disco). Aplicando este procedimiento no es necesario que ante cada commit escribir las páginas de datos a disco, pues en caso de caída seremos capaces de recuperar la base de datos utilizando el log. Cualquier cambio que no esté aplicado en las páginas de datos puede ser rehecho con los registros en los logs. (Roll-forward recovery, también conocido como REDO)

Esto tiene grandes implicaciones en mejora de rendimiento, pues el número de escrituras a disco muy reducido.

Además gracias al WAL, tenemos la oportunidad de aprovecharlo para cluster Master/Esclavo (streaming replication), el backup contínuo (online-backup) y la restauración de cualquier punto en el tiempo (point-in-time recovery)

Introducción a WAL en la doc de postgresql

Curso PostgreSQL · Oct 2014 · apsl.net

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

WAL

  • Abrir fichero xlog, en modo append
  • Escribir, en un fichero, en un offset.
  • Cerrar fichero
  • Asegurar escritura: fsync()
  • Cambiar datafile
  • Marcar operación como realizada en xlog.

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

WAL

  • INSERT
    • Se carga page (8kb) en shared_buffers.
    • Se esribe en shared_buffers y tambien WAL (no fsync())
    • COMMIT: Se realiza fsync()
  • Dos copias:
    • Página modificada en shared_buffers
    • WAL
  • ¿Cuando escribimos a datafiles ?
    • check_point
    • page_swap (la página se necesita)

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Archivos en el servidor

  • /var/lib/postgresql/9.3/main/pg_xlog

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Formato archivos

  • Formato de los archivos WAL

  • TLI: TimelineID , identifica el timeline
  • LSN: Log Serial Number, Identificador del LOG
  • Nomenclatura de WAL

Notes

Los archivos de WAL postgres los mantiene en el directorio: /var/lib/postgresql/9.3/main/pg_xlog

La nomenclatura de estos archivos es incremental (en hexadecimal)

Solo se guardan cierto tiempo, luego se borran.

Si no puede escribir en pg_xlog, p.e. por falta de espacio, postgresql hará un PANIC shutdown.

El número de WAL's varia dependiendo de la carga y lo que vaya haciendo el server, pero se puede establecer una cota máxima con una de las siguientes formulas:

(2 + checkpoint_completion_target) * checkpoint_segments + 1 or checkpoint_segments + wal_keep_segments + 1 files

TLI: TimelineID , identifica el timeline. El número de timeline solo cambia al terminar un proceso de recuperación, para que no se sobreescriban archivos.

Curso PostgreSQL · Oct 2014 · apsl.net

Checkpoint

  • Un punto del log donde se escriben los datos pendientes a disco
  • La carga de entrada/salida se ve afectada al hacer checkpoint
  • En caso de caida, recuperar ficheros WAL xlog desde el log a partir del último checkpoint.

.

$ select pg_current_xlog_location();
pg_current_xlog_location
--------------------------
8/584A62E0
(1 row)

Notes

Un checkpoint es un punto el log en el que se escriben los cambios a disco para reflejar todos los cambios que ya estaban escritos en el log. En caso de caída, al recuperar se mirará cual es el último checkpoint escrito en disco y a partir de ahí se recuperará del log, aplicando de nuevo las operaciones que no están aplicadas en los archivos de datos de disco para mantener la integridad de datos. Este requerimiento de guardar los cambios a disco hace que la carga de entrada/salida se ve aumentada.

Configuración de WAL en la doc de postgresql

Curso PostgreSQL · Oct 2014 · apsl.net

Parámetros checkpoint

  • checkpoint_segments (valor por defecto: 3)
    • nº Ficheros en pg_xlog: 2 * checkpoint_segments + 1
  • checkpoint_timeout (valor por defecto: 300s)
  • checkpoint_completion_target (valor por defecto: 0.5)

Notes

Parámetros configurables sobre los checkpoints:

checkpoint_segments (defecto: 3): El número de segmentos que se tienen que llenar antes de hacer un checkpoint

checkpoint_timeout (defecto: 300s): Si no se han llenado los checkpoints_segments en este tiempo se hace un checkpoint.

checkpoint_completion_target (defecto: 0.5):

Curso PostgreSQL · Oct 2014 · apsl.net

Background writer

  • Proceso que se encarga de escritura de buffers a disco. (nuevos o dirty)
  • Intenta evitar que los procesos Backend tengan que realizar la escritura
  • Puede escribir varias veces entre checkpoints
  • Algunos parámetros que afectan:
    • bgwriter_delay (defecto: 200ms)
    • shared_buffers (defecto: 32MB) -> entre 25% y 33% de la memoria

.

# select * from pg_stat_bgwriter;
-[ RECORD 1 ]------+-----------
checkpoints_timed  | 3236
checkpoints_req    | 83044
buffers_checkpoint | 1376460896
buffers_clean      | 59124159
maxwritten_clean   | 304410
buffers_backend    | 285595787
buffers_alloc      | 6643047623

Notes

Proceso que se encarga de actualizar los cambios.

La importancia de éste proceso radica en que, si bien, el parámetro checkpoint_segments provoca la actualización "real" en base de datos, durante el intervalo en el que éste tiene lugar, el background writer puede haber actualizado la misma información varias veces, lo que hará que sea el "último cambio" el que se almacene realmente en base datos.

Parametros de configuración del bgwriter: bgwriter_delay (defecto: 200ms): Establece el intervalo de tiempo, en milisegundos, en el que se lanzará el background writer. shared_buffers (defecto: 32MB): Importante!, establece y define el tamaño del buffer de memoria que se reservará como zona de trabajo en el momento del arranque para procesar las consultas. La disminución de éste permite ahorrar recursos del sistema en un sistema con poca carga, su aumento puede mejorar el rendimiento en un sistema de producción muy utilizado. [25% del total de nuestra memoria, nunca más del 33%]

Curso PostgreSQL · Oct 2014 · apsl.net

Vacuuming

  • Revisor de tablas con muchas modificaciones
  • En definitiva, mantenimiento periodico para:
    • Recuperar espacio de disco por actualizaciones o eliminiaciones de filas.
    • Actualizar las estadísiticas del planificador de consultas
    • Otras tareas de mantenimiento
  • Dos comandos: VACUUM y ANALYZE

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Autovacuuming

  • Es un servicio que automatiza la ejecución del vacuuming
  • Se configura por defecto y es recomendable
  • Varios servicios: un launcher y workers
    • Un worker por cada base de datos
    • Cada 'autovacuum_naptime' segundos
  • Vacuuming en la doc de postgresql

Notes

Es el proceso encargado de revisar periódicamente la tablas con modificaciones considerables, información ésta que suministra el recolector de estadísticas, que lo ayudan a llevar a cabo las tareas: VACUUM y ANALYZE.

En definitiva es un trabajo de mantenimiento periodico para

Curso PostgreSQL · Oct 2014 · apsl.net

postgresql.conf

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Parametrización: postgresql.conf

  • Database connections
    • max_connections = 50
    • listen_addresses = '*'
  • Memoria
    • shared_buffers = 128MB
      • 25% memoria sistema. PostgreSQL usará Buffer-caché linux.
      • Mas shared_buffers, mas checkpoint_segments
    • work_mem = 1MB
      • Usada por procesos backend, para operaciones como sort.
      • La memoria total se multiplicará por el número de procesos.
    • effective_cache_size = Entre 50% y 75% RAM
      • Memoria que Linux tendrá disponible para buffer-caché.

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Parametrización: postgresql.conf

  • checkpoints
    • checkpoint_completion_target = 0.5
    • checkpoint_timeout = 5m
    • checkpoint_segments = 3
  • bgwriter
    • bgwriter_delay = 200ms
      • Tiempo entre rondas de ejecución de bgwriter
    • bgwriter_lru_maxpages = 100
      • Número máximo de páginas en cada ejecución
    • bgwriter_lru_multiplier = 2.0
      • Porcentaje de escrituras, respecto al número de nuevos buffers pedidos durante la última ronda

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Parametrización: postgresql.conf

  • WAL
    • wal_buffers = 64KB
      • Si tenemos mucha memoria, podemos aumentar, p.e. 16MB. Mejorará rendimiento en situaciones de mucha escritura.
    • wal_sync_method=fsync_writethrough
    • wal_writer_delay = 600ms
    • Casos Especiales:
      • synchronous_commit = on
      • fsync = on

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Parametrización: postgresql.conf

  • Archiving
    • archive_mode = on
    • archive_command = '/usr/bin/envdir /etc/wal-e.d/env /usr/local/bin/wal-e wal-push %p'
    • archive_timeout = 1800

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Parametrización: postgresql.conf

  • Logging
    • log_min_duration_statement=1000
    • log_checkpoints = on
  • Stats
    • track_activities = on
    • track_counts = on
    • stats_temp_directory = 'pg_stat_ram'

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Parametrización: postgresql.conf

  • Mantenimiento
    • maintenance_work_mem = 16MB
      • Memoria usada por operaciones de mantenimiento, como VACUUM, CREATE INDEX
    • autovacuum = on

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Parametrización servidor dedicado

  1. Ajustar logging, más información.
  2. Determinar shared_buffers. Empezar por 25% de la RAM total. Lo podemos subir si tenemos versiones recientes de PostgreSQL, y configuramos checkpoints adecuadamente.
  3. Estimar el número de conexiones.
  4. Lanzar server, observar buffers. Ajustar effective_cache_size
  5. Dividir OS cache por max_connections y después por 2. Esto nos da un valor razonable para work_mem. Si no dependemos de sort, podemos disminuir el valor.
  6. Configurar maintenance_work_mem sobre 50MB por GB de RAM
  7. Incrementar checkpoint_segments, por encima de 10. El valor correcto dependerá de la calidad del hardware, e importancia de recuperaciones rápidas.
  8. Incrementar wal_buffers a 16MB

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Nuestro caso

  • Base de datos de aplicaciones web
  • Un postgresql por servidor de aplicaciones
  • Para alta disponibilidad, montamos un master/esclavo
  • No tenemos ningún caso con necesidad de master/esclavo por carga

Notes

Nuestro caso de uso

Nosotros lo utilizamos como servidor de base de datos de aplicaciones web y mantenemos un servidor postgresql para cada servidor de aplicaciones.

Tenemos servidores con muchas aplicaciones que comparten el mismo postgresql, para desarrollo y aplicaciones no criticas.

Otras aplicaciones con necesidad de alta disponibilidad, las montamos sobre dos servidores en cluster, donde en caso de que caiga cualquiera de los dos nodos el servicio postgresql sigue funcionando automáticamente.

Curso PostgreSQL · Oct 2014 · apsl.net

Instalación

  • Añadir el repositorio al sistema
  • Bajar la e instalar la key del nuevo repositorio
  • Actualizar la lista de paquetes
  • Instalar el paquete

Notes

Añadir el repositorio al sistema

echo 'deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main' >  /etc/apt/sources.list.d/pgdg.list
Bajar la e instalar la key del nuevo repositorio

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
Actualizar la lista de paquetes

aptitude update
Instalar el paquete

aptitude install postgresql-9.3
Curso PostgreSQL · Oct 2014 · apsl.net

Servicio y consola

  • Ya deberíamos ver el servicio corriendo

  • Y poder acceder a la consola de administración

Notes

Ya deberíamos ver el servicio corriendo:
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               ESCUCHAR    1382/postgres

Esta instalación crea un usuario postgres. Desde este usuario ya podemos acceder al terminal interactivo de de postgres.

root@apsl-edu:~# sudo su - postgres
postgres@apsl-edu:~$ psql
psql (9.3.5)
Type "help" for help.

postgres=#
Curso PostgreSQL · Oct 2014 · apsl.net

Directorio principal

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Configuración pg_hba.conf

  • /etc/postgresql/9.3/main/pg_hba.conf

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Configuración pg_hba.conf (formatos)

  • Formatos disponibles

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Configuración pg_hba.conf (host)

  • local: conexión mediante socket unix. Sin una de estas los sockets estan desactivados.
  • host: conexión por TCP/IP. Tanto sea por ssl o sin.
  • hostssl: conexión por TCP/IP. Solo si viene con cifrado SSL.
  • hostnossl: opuesto a hostssl.

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Configuración pg_hba.conf

  • database: especifica que base de datos.
    • 'all', configura para todas las bases de datos
  • user: especifica que usuario de base de datos
    • 'all', configura para todos los usuarios
  • address: especifica la maquina del cliente que hace la conexión
    • 'all', configura para cualquier host
    • 'samehost', cualquiera de las IP's del host servidor
    • 'samenet', cualquier dirección en cualquier subred en la que servidor este directamente conectada.

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Configuración pg_hba.conf (auth-method)

  • trust: permitir la conexión incondicionalmente
  • reject: denegar la conexión incondicionalmente
  • md5: el cliente debe suministrar la contraseña cifrada con md5
  • password: requiere una contraseña sin cifrar
  • krb5: kerberos v5 para autentificación
  • ident: validación por usuario de sistema del cliente TCP/IP coincidente con el de base de datos
  • peer: idem que ident para conexiones socket.
  • ldap o radius: autentificación por servicios externos ldap o radius, respectivamente.
  • cert: validación mediante certificados SSL.
  • pam: servicio de autentificación PAM

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Configuración pg_hba.conf (ejemplos)

  • Ejemplos:
    • Ciertas IP's sin autentificación:
      • host all all 192.168.0.0/24 trust
    • Para una determinada bd:
      • host basededatos all 192.168.0.0/24 md5

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Configuración postgresql.conf

  • /etc/postgresql/9.3/main/postgresql.conf
    • Archivo principal de Configuración
    • Opcionalmente con subarchivos en directorio conf.d
      • (se sobre-escriben las configuraciones por defecto)
      • Muy útil con sistemas de configuración
    • Cada versión o cluster tendrá su propia carpeta de configuración

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Configuración postgresql.conf (parámetros básicos)

  • listen_addresses
    • 'localhost': solo se aceptarán peticiones desde 127.0.0.1
    • '*' : desde cualquiera
    • '192.168.0.0/24': solo a una IP o rango
  • port: puerto en el que se escuchará
    • por defecto el 5432
    • Para instalaciones con múltilples versiones, 5433, 5434, ...
  • max_connections: número máximo de conexiones
    • por defecto 100

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Configuración postgresql.conf (pgtune)

Notes

postgres@apsl-edu:~/9.3/prueba$ /usr/local/src/pgtune/pgtune -i postgresql.conf -o postgresql.conf.pgtune
postgres@apsl-edu:~/9.3/prueba$ diff postgresql.conf postgresql.conf.pgtune
114c114
< shared_buffers = 128MB                        # min 128kB
---
> # shared_buffers = 128MB                      # min 128kB
596a597,610
>
> #------------------------------------------------------------------------------
> # pgtune run on 2014-10-21
> # Based on 8038900 KB RAM, platform Linux
> #------------------------------------------------------------------------------
>
> default_statistics_target = 100
> maintenance_work_mem = 480MB
> checkpoint_completion_target = 0.9
> effective_cache_size = 5632MB
> work_mem = 36MB
> wal_buffers = 16MB
> checkpoint_segments = 32
> shared_buffers = 1920MB
Curso PostgreSQL · Oct 2014 · apsl.net

Configuración del sistema

  • Algunas configuraciones requieren de la moficiación de parámetros de sistema
  • Es posible en instalaciones con muchas instalaciones en una máquina o instalaciones muy grandes
  • Salta una execepción al arrancar es servidor porque se ha sobrepasado algún limite
  • Caso típico, problemas de sobrepasar limite de memoria compartida de sistema

    • Para ampliar a 16GB (por ejemplo)
    • $ sysctl -w kernel.shmmax=17179869184
    • $ sysctl -w kernel.shmall=4194304
  • Mas info en al doc de postgresql

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

psql

  • sudo su - postgres
  • psql
    • -p 5432
    • parametros de conexión -h host, -U user, ...
  • Operadores de control
    • \? -> Ayuda de operadores existentes
    • \l -> Listado de bases de datos
    • \du -> Listado de roles
    • \c basededatos -> Conectar a una base de datos
    • \dt -> Listado de relaciones
    • \dp -> Listado de privilegios

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Crear usuario y base de datos

  • Crear usuario:
    • postgres=# create role usuario with login password 'xxxxxx';
  • Crear base de datos:
    • postgres=# create database usuariodb with owner usuario;
  • Borrar usuario:
    • postgres=# drop role usuario;
  • Borrar base de datos:
    • postgres=# drop database usuariodb;
  • Conceder permisos de acceso a base datos para un usuario
    • postgres=# grant all privileges on database usuariodb to usuario;

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Clustering

  • Postgresql permite tener varias instalaciones para un mismo servidor
  • Tiene algunas utilidades para manejar estas instalaciones
  • Las utilidades de la documentación de postgres no estan en el path
  • El paquete ubuntu introduce otras utilidades de más alto nivel
  • En la documentación de postgres comenta por ejemplo:
    • initdb -> para crear un entorno nuevo en un directorio
    • pg_controldata -> muestra la información de una instalación
    • pg_ctl -> comandos de control de una instalación (initialize, start, stop)
    • pg_upgrade -> Actualización entre versiones (muy útil)

Notes

/usr/lib/postgresql/9.3/bin/pg_initdb /var/lib/postgresql/9.3/prueba/
/usr/lib/postgresql/9.3/bin/pg_controldata /var/lib/postgresql/9.3/prueba/

# Cambiar el puerto para que no coincida con una instalación existente en el postgresql.conf
/usr/lib/postgresql/9.3/bin/pg_ctl start -D /var/lib/postgresql/9.3/prueba

#Desde otra shell
/usr/lib/postgresql/9.3/bin/pg_ctl stop -D /var/lib/postgresql/9.3/prueba
Curso PostgreSQL · Oct 2014 · apsl.net

Clustering (alto nivel)

  • Es mejor manejar la creación de cluster con las utilidades de alto nivel
  • postgres@apsl-edu:~$ pg_createcluster 9.3 prueba --start
    • Servicio en marcha en un puerto libre, con su config y datos separados
  • postgres@apsl-edu:~$ pg_lsclusters
    • Listado de clusters activos
  • postgres@apsl-edu:~$ pg_dropcluster 9.3 prueba --stop
    • Borrado de un cluster

Notes

postgres@apsl-edu:~$ pg_createcluster 9.3 prueba --start
Creating new cluster 9.3/prueba ...
config /etc/postgresql/9.3/prueba
data   /var/lib/postgresql/9.3/prueba
locale es_ES.UTF-8
port   5433

postgres@apsl-edu:~$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory                 Log file
9.3 main    5432 online postgres /var/lib/postgresql/9.3/main   /var/log/postgresql/postgresql-9.3-main.log
9.3 prueba  5433 online postgres /var/lib/postgresql/9.3/prueba /var/log/postgresql/postgresql-9.3-prueba.log

postgres@apsl-edu:~$ pg_dropcluster 9.3 prueba --stop
Curso PostgreSQL · Oct 2014 · apsl.net

Objetos de Base de datos

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Objetos de Base de datos (típicos)

  • Base de datos (database): es un contenedor de otros objetos como tablas, vistas, funciones, indices, etc.
  • Tabla (table): se usa para estructurar los datos almacenados, permite la herencia de tablas.
  • Esquema (schema): es un contenedor lógico de tablas y otros objetos dentro de una base de datos.
  • Vista (view): una tabla virtual, para simplificar consultas o securizar.

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Objetos de Base de datos (tablespace)

  • Tablespace
    • Definen a que ruta de sistema se almacenan los datos.
    • Por ejemplo, una tabla de una base de datos a una ruta específica.
    • Ventajas:
      • Una tabla que no requiera rendimiento escriba a un disco lento y grande.
      • Una tabla que así lo requiera escriba a un disco rápido y pequeño.
    • Más info en la doc de postgresql

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Objetos de Base de datos (otros)

  • Función (function): Una función es código SQL re-utilizable.
  • Operador (operator): es una función simbólica.
  • Cast: permiten convertir un tipo de datos en otro, en definitiva funciones. Se pueden sobreescribir casts por defecto.
  • Secuencia (sequence): se utilizan para controlar las campos auto-incrementales de las tablas.
  • Disparador (trigger): asocian eventos de base de datos con ejecución de funciones

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Objetos de Base de datos (extension)

  • Extensión (extension)
    • Postgresql deja partes interesantes del proyecto para desarrollo aparte
    • Esas partes se incorporan al servidor mediante extensiones
    • Implican tipos nuevos, indices, funciones, etc.
    • PostgreSQL contrib
      • Utilidades que no están instaladas por defecto. Desarrolladas y distribuidas con el core.
      • Desde 9.1: CREATE EXTENSION module_name;
      • Lista contrib modules

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Extensión postgis

  • Postgis, extensión geoespacial para postgresql
  • Permite operar con tipos de datos relacionados con posiciones espaciales
  • Tiene funciones para operar con esos datos:
    • Consultas de puntos dentro de un radio o un poligono
  • Poca competencia en bases de datos relacionales:
  • Se integra muy bien con ORMs como el de Django
  • Se está convirtiendo en indispensable al necesitar geolocalización

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Extensión oid2name

postgres@canape:~$ /usr/lib/postgresql/9.3/bin/oid2name 
All databases:
    Oid       Database Name  Tablespace
-----------------------------------------
227923                abi4  pg_default
656166       agregadoresdb  pg_default
729346          agrofincdb  pg_default
...


postgres@canape:~$ oid2name  -d abi4
From database "abi4":
Filenode                             Table Name
-------------------------------------------------
    230572                             auth_group
    230577                 auth_group_permissions
    230582                        auth_permission
    230587                              auth_user
    ...

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

pgAdmin3

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

pgAdmin3

  • Es un entorno gráfico de escritorio para gestionar servidores postgresql
  • Multi-plataforma (Windows, Linux, Mac OSX, FreeBSD, Solaris)
  • Escrito en C++ con wxWidgets
  • Software libre bajo licencia "Artistic License"
  • http://www.pgadmin.org
  • Disclaimer: nosotros no lo utilizamos para operar con los servidores de producción

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

pgAdmin3 (instalación)

  • En local:
    • aptitude install pgadmin3
  • En el servidor:
    • Crear un superusuario con contraseña para que pueda acceder el pgadmin
    • Modificar la configuración de postgresql.conf para que se pueda acceder desde fuera
    • Modificar el pg_hba.conf para permitir conexiones remotas al servidor con contraseña md5

Notes

# Crear el superusuario
postgres@cursopostgres:~$ createuser --superuser upgadmin
postgres@cursopostgres:~$ psql
psql (9.3.5)
Type "help" for help.
postgres=# \password upgadmin

# Cambiar el postgresql.conf para que acepte conexiones desde fuera
listen_addresses = '*'

# Cambiar el pg_hba para permitir conexiones remotas al servidor con contraseña md5
# IPv4 local connections:
host    all             all             all                     md5
Curso PostgreSQL · Oct 2014 · apsl.net

pgAdmin3 (añadir servidor)

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

pgAdmin3 (operación)

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

pgAdmin3

  • Para cada modificación nos muestra las setencias SQL correspondientes al cambio
  • Realizar consultas
  • Examinar ejecución de consultas (comando EXPLAIN)
  • Control de privilegios de usuario, muy útil el para asignar a objetos específicos
  • Plugins para extensiones, pgadmin-postgis-viewer por ejemplo

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Monitorización

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Monitorización

  • Utilidades de monitorización de consola
    • htop: cpu, memoria, general y por proceso
    • dstat: cpu, memoria, disco, red, paginación, sistema
    • iotop: entrada/salida disco, total y por proceso
    • nmon: cpu, memoria, disco, red, y mas ...
    • Instalación: aptitude install htop dstat sysstat nmon
  • Herramientas monitorización externas
    • nagios/check_mk
    • newrelic
    • datadog

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Monitorización externa (nagios/check_mk)

  • check_mk es software libre y se puede instalar muy fácil
  • check_mk incluye un plugin especifico para monitorización de postgres
  • Está incluido por defecto en la instalación de check_mk
  • Puede inventorizar las bases de datos del servidor
  • Controlar el tamaño de cada base de datos
  • Estádisticas de performance para cada base de datos
    • lecturas, escrituras, commits
  • Más información en la página de check_mk

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Monitorización externa (newrelic)

  • Es un servicio de pago, con capa gratuita con retención de datos de 24 horas
  • Newrelic te ofrece un agente que se instala en el sistema
  • Los datos se envían a sus servidores y tu tienes un backend web para verlos
  • Existen plugins para monitorizar postgresql
  • Interesante si tenemos monitorización de aplicación también en newrelic para correlar
  • Aunque es un servicio caro. Precio

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Monitorización externa (datadog)

  • Es un servicio de pago, con capa gratuita con retención de datos de 24 horas
  • Datadog te ofrece un agente que se instala en el sistema
  • Los datos se envían a sus servidores y tu tienes un backend web para verlos
  • Tiene plugin para postgresql. Integración
  • Mucho más barato que newrelic. Precio

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Monitorización externa (instalación del agente de datadog)

  • Desde el panel de datadog
  • Instalar el agente:
    • Integrations -> Agent
    • Seleccionar sistema operativo y seguir los pasos
  • Configurar el agente para la integración con postgresql
    • Integrations -> Integrations
    • Seleccionar postgresql y seguir pasos
    • La ruta de configuración del agente en ubuntu es: /etc/dd-agent/conf.d
    • Reiniciar el agente
    • Comprobar con: dd-agent info

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Configuración PostgreSQL Logs

# logs por defecto
log_destination = 'stderr'
logging_collector = off
log_line_prefix = ''
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

# logging slow query
log_min_duration_statement = 1000
log_duration = off
log_statement = 'none'

# Auto explain
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '3s'

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

pgbench

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

pgbench

  • Una utilidad de consola para cargar el servidor
  • Útil para hacer pruebas de rendimiento
  • Ejecuta muchas veces la misma sequencia de comandos SQL
  • Resultado: ratio de transacciones por segundo
  • Implementa por defecto la transacción TPC-B
    • Basado en 5 selects, updates y queries por transacción
  • Aunque puedes escribir tus propios scripts de transacciones
  • Más información en la documentación de postgresql

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

pgbench (parámetros)

  • Parámetros de inicialización:
    • -i : inicializa la base de datos creando las tablas que utiliza para los test
    • -s : factor de escala para el número de filas a crear al inicializar (1 por defecto)
      • -s 100 crea 10 millones de filas
  • Parámetros de ejecución:
    • -c : clientes simulados, número de sesiones
    • -C : una conexión para cada sesion, útil para medir el overhead por conexión
    • -j : procesos de pgbench, útil para máquinas multiprocesador (1 por defecto)
    • -t : transacciones que cada cliente hace (10 por defecto)
    • -T : segundos que debe estar corriendo el test

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

pgbench (instalación y funcionamiento)

# Instalamos el paquete necesario de utilidades de contrib de postgresql
aptitude install postgresql-contrib-9.3

# Preparamos la base de datos que queremos testear
su - postgres
createdb pgbench
pgbench -i -s 50 pgbench

# Vamos probando ejecuciones
pgbench pgbench
...
pgbench -c 20 -j 10 pgbench
...
pgbench -c 80 -C -j 10 -T 100  pgbench
# Con -T podemos dejar el test corriendo e ir revisando monitorización

# Veremos un resultado con el valor de transacciones por segundo
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 100
query mode: simple
number of clients: 20
number of threads: 10
duration: 10 s
number of transactions actually processed: 8929
tps = 891.371476 (including connections establishing)
tps = 1693.701621 (excluding connections establishing)

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Configurar DB para pgbench

  • Parámetros importantes:

    • shared_buffers, checkpoint_segments, autovacuum, wal_buffers, checkpoint_completion_target
  • Parámetros que pueden modificar los tests significativamente:

    • wal_sync_method, synchronous_commit, wal_writer_delay
  • Parámetros que no influirán en pgbench

    • ffective_cache_size , default_statistics_target , work_mem , random_page_cost

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Sistema Operativo

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Filesystems

Comunes:

  • ext3
  • ext4 (2008)
  • XFS (SGI)
  • ZFS (Sun, Solaris)
    • zfs set recordsize=8K zp1/data
  • ReiserFS

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Filesystems - Journal

Como PostgreSQL, mediante WAL, asegura consistencia de datos, tanto de ficheros WAL como de datafiles, postgres no necesita sistemas de ficheros con journal. De hecho, pueden perjudicar al rendimiento, especialmente si se trata de journaling de metadatos. PostgreSQL WAL

Opciones importantes:

  • Journaling:
    • data=writeback (recomendado)
    • data=ordered, data=journal
  • noatime, nodiratime
  • nodelalloc
  • Barriers: Evitar cachés internas disco
    • barrier=1
    • ¿Deshabilitamos completamente write caché en el disco?
      • sudo hdparm -W 0 /dev/sda

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Filesystems - mount options

$ cat /etc/fstab
/dev/sda3 /pg/9.2/main  ext4  noatime,data=ordered,barrier=1 0 2
/dev/sda2 /pg/9.2/main/pg_xlog  ext4  noatime,data=ordered,barrier=1 0 2

stats ram fs:

$ cat /etc/fstab
# tmpfs para pg_stat
tmpfs /pg/9.2/main/pg_stat_ram tmpfs defaults,noatime,size=128M,uid=105, 
      gid=114,mode=0700 0 0

$ cat postgresql.conf | grep stats_temp_dir
stats_temp_directory = 'pg_stat_ram'

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Límites de memoria compartida

PostgreSQL kernel resources

#!/bin/bash
# simple shmsetup script
page_size=`getconf PAGE_SIZE`
phys_pages=`getconf _PHYS_PAGES`
shmall=`expr $phys_pages / 2`
shmmax=`expr $shmall \* $page_size`
echo kernel.shmmax = $shmmax
echo kernel.shmall = $shmall

# ./shmsetup >> /etc/sysctl.conf

# sysctl -p
kernel.shmmax = 1055092736
kernel.shmall = 257591

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Configuración Linux vm

Cuando PostgreSQL corre en un servidor dedicado y controlamos el uso de memoria, podemos configurar el comportamiento óptimo en la gestión de memoria de Linux

Out of Memory: Killed process 12345 (postgres)

  • No permitimos alojar más memoria que la física a procesos.
    • sysctl -w vm.overcommit_memory=2
  • Prevenimos swapping de páginas
    • vm.swappiness=0

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Read Ahead

Uno de los settings más importantes a nivel de S.O., ya que tiene influencia directa sobre las lecturas secuenciales en PostgreSQL (Seq scans, COPY...)

$ blockdev --getra /dev/sda
256

$ blockdev --setra 4096 /dev/sda

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

RAID y disposición de discos

  • Evitar tener WAL en el mismo disco que el S.O.
  • Cuidado con ext3 y fsync.
  • Separar ayuda a evaluar mejor rendimiento.

Fuente: PostgreSQL 9.0 High Performance - Packt Publishing

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

RAID y disposición de discos

  • Tipos de acceso en Servidor dedicado PostgreSQL

Fuente: PostgreSQL 9.0 High Performance - Packt Publishing

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Random Page Cost y la calidad de los discos

Otro setting que afecta al planificador, es random_page_cost: El coste relativo del disco para obtener una fila usando acceso secuencial versus acceso random. Random Page Cost Revisited

  • Lo usará el planificador para decidir entre:
    • indexes vs. table scan
    • composite vs. simple indexes
  • Se puede configurar por tablespace: ALTER TABLESPACE pg_default SET (random_page_cost=2);
  • En el artículo mencionado se sugiere lo siguiente:
    • High-End NAS/SAN: 2.5 or 3.0
    • Amazon EBS and Heroku: 2.0
    • iSCSI and other bad SANs: 6.0, but varies widely
    • SSDs: 2.0 to 2.5
    • NvRAM (or NAND): 1.5
  • En situaciones en las que la BD está en cache, puede beneficiar bajar de 1.0

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

EXPLAIN

pgbench=# explain UPDATE pgbench_branches SET bbalance = bbalance + 1246 WHERE bid = 16;
                                            QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
Update on pgbench_branches  (cost=0.14..8.16 rows=1 width=370)
->  Index Scan using pgbench_branches_pkey on pgbench_branches  (cost=0.14..8.16 rows=1 width=370)
        Index Cond: (bid = 16)
(3 rows)

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Particionamiento

  • Basic table partitioning
    • Funciones
    • Triggers
  • PL/Proxy - skype
    • Sharding (con pl/proxy)
  • GridSQL - EnterpriseDB

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Sistemas de copia (backup)

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Resumen

  • Copia y restauración tradicional
    • pg_dump, pg_dumpall, pg_restore
  • Utilidad de chequeo de copia y replicación
  • Copia completa física
    • pg_basebackup
  • Copia continua (archiving)
    • Introducción y configuración
    • Opciones: Artesanal, Barman, WAL-E
    • Recuperación de un punto en el tiempo

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Copia y restauración tradicional

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Copia y restauración tradicional

  • Copia o backup con pg_dump
  • Uso y ejemplos pg_dump
  • Copia o backup con pg_dumpall
  • Script todas las bases de datos
  • Restauración con psql
  • Restauración con pg_restore
  • Uso y ejemplos pg_restore

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Copia o backup (pg_dump)

  • Comando pg_dump
  • Por base de datos
  • Backup consistente
  • No bloqueante
  • Dos formatos:
    • Script SQL -> Archivo con sentencias SQL y se restaurará con psql
      • Selección a priori
    • Archive file -> Archivo/s con un formato para restaurar con pg_restore
      • Selección a posteriori
      • Permitirá operar sobre que queremos que sea restaurado
      • Permitirá reordenar los elementos antes de restaurar
      • Está diseñado para ser portable entre arquitecturas

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Copia o backup (pg_dump)

  • Parámetros interesantes:
    • -a : solo datos, sin el esquema.
    • -c : añadir comandos para limpiar los objetos antes de crearlos (drop)
      • Solo tiene sentido en el backup Script SQL
    • -F : formato en el cual generar la copia, aplican los operadores:
      • p : plain, SQL script (es el valor por defecto)
      • c : custom, formato archive file
      • d : directory, crea un directorio, con un archivo por tabla
      • t : tar, crea un archivo tar
      • c, d, t son formatos que acepta pg_restore para restaurar
    • -j : número de jobs en paralelo del formato directory (más carga y menos tiempo)
    • -s : solo el esquema
    • Y muchos más en 'man pg_dump'

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Copia o backup (uso y ejemplos pg_dump)

# Uso
pg_dump [connection-option...] [option...] [dbname]
# Con salida hacia la salida estandard

# Copia formato script sql
pg_dump pruebadb > pruebadb.sql

# Copia formato archive file
pg_dump -Fc pruebadb > pruebadb.dump

# Copia formato archive file y comprimiendo con gzip
pg_dump -Fc pruebadb | gzip > pruebadb.dump.gz

# Copia formato directory con 10 procesos en paralelo, (siempre uno por tabla)
pg_dump -Fd pruebadb -j 10 -f pruebadbdir

Más ejemplos en la documentación de postgres

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Copia o backup (pg_dumpall)

  • Copia todas las bases de datos a un script SQL
  • No se podrá restaurar con pg_restore
  • Copia objetos globales que no copia pg_dump (usuarios, permisos, tablespaces, ...)
  • Se debería lanzar como superusuario:
    • Privilegios con permisos sobre objetos globales
    • Validación por contraseña la pediría para cada base de datos
    • Parámetros interesantes que difieran de pg_dump:
      • -g : solo objetos globales
      • -r : solo roles de usuario

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Copia o backup (ejemplos pg_dumpall)

Ejemplos

# Clásico de todas las bases de datos y objetos globales
pg_dumpall > db.out

# Solo objetos globales
pg_dumpall -g > db.out

# Solo roles de usuario
pg_dumpall -r > db.out

Más en la documentación de postgres

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Copia o backup (script todas las bases de datos)

#!/bin/bash
# Hace un backup de todas las bases de datos en el servidor postgresql por defecto
# A incluir en el cron del usuario postgres

DIR=/var/lib/postgresql/backups
LANG=en_US LIST=$(psql -l | awk '{ print $1}' | grep -vE '^-|^List|^Name|template[0|1]|^\(')

for db in $LIST
do
    pg_dump -Fc $db | gzip >  $DIR/$db.gz
done

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Restauración (psql)

  • Restauración con psql
  • En caso de que tengamos un backup en script SQL
  • Ejecutaremos el script en una base de datos limpia
  • O haber creado el script con el paramentro -c "Clean" que añade los DROP
  • Podemos cargar el script de dos maneras:
    • Con el comando psql: psql -d newdb -f db.sql
    • Dentro de la consola psql con el operador \i sqlfile.sql
      • Estando conectado a la base de datos correspondiente

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Restauración (pg_restore)

  • Restauración con pg_restore
  • En caso de que tengamos un backup en formato Archive file
  • Le especificamos a pg_restore lo que queremos restaurar:
    • En que base de datos
    • Con que usuario
    • Si queremos que nos recree la base de datos
    • Solo esquema
    • Solo datos
    • Solo una tabla

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Restauración (pg_restore)

  • Parámetros interesantes:
    • -a : solo datos, sin el esquema.
    • -c : borrar los objetos antes de crearlos
    • -j N : N, número de procesos a utilizar para la restauración (¿número de CPUS?)
      • Tiene sentido si es una base de datos grande
    • -l : listar el contenido de un dump, sin aplicar nada.
    • -s : solo el esquema
    • -O : no restuaurar la información de propietario de objetos
      • Muy útil si restauras en otro entorno con otro nombre usuario
    • -t : restaurar solo una tabla
    • -x : no restaurar los permisos de acceso
    • Y muchos más en 'man pg_restore'

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Restauración (uso y ejemplos pg_restore)

# Uso
pg_restore [connection-option...] [option...] [filename]

# Listar un resumen del contenido de un dump
pg_restore -l pruebadb.dump

# Restaurar solo una tabla
pg_restore -t tabla -d pruebadb pruebadbold.dump

# Restaurando desde un archivo comprimido gzip, con otro usuario, opciones extra
zcat pruebadbold.dump.gz | pg_restore -U unew -O -x -d pruebadb

Más ejemplos en la documentación de postgres

Notes

postgres@apsl-edu:~$ pg_restore -l htvdb.dump 
;
; Archive created at Wed Oct 22 15:29:20 2014
;     dbname: htvdb
;     TOC Entries: 467
;     Compression: -1
;     Dump Version: 1.12-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 9.3.5
;     Dumped by pg_dump version: 9.3.5
;
;
; Selected TOC Entries:
;
2655; 1262 19641 DATABASE - htvdb uhtv
6; 2615 30461 SCHEMA - public postgres
2656; 0 0 COMMENT - SCHEMA public postgres
2657; 0 0 ACL - public postgres
260; 3079 30462 EXTENSION - plpgsql 
2658; 0 0 COMMENT - EXTENSION plpgsql 
170; 1259 30467 TABLE public admin_tools_dashboard_preferences uhtv
171; 1259 30473 SEQUENCE public admin_tools_das
... informacion de todos los objetos ...
Curso PostgreSQL · Oct 2014 · apsl.net

Utilidad de chequeo de copia y replicación

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Utilidad de chequeo de copia y replicación

  • https://github.com/APSL/postgresql-checker
  • Crea una base de datos de pruebas
  • Utilidades para probar el funcionamiento de las copias postgresql
    • Inserción continua y conteo
  • También para la replicación
  • Configuración
    • Crear la base de datos y el usuario
    • Configurar el archivo config.yml
  • Nos servirá para ir probando los diferentes sistemas de backup

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Copia completa física

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Copia completa física

  • Copia a nivel de sistema de ficheros
  • Todo el directorio de trabajo del cluster de postgresql
  • No es posible separar a nivel de diferentes bases de datos
  • Se utiliza:
    • Como base para una recuperación de un punto en el tiempo
    • Como inicio de copia de otro servidor replicado
  • Utiliza la funcionalidad de replicación para hacer la copia
  • El backup se hace desde una conexión normal
  • Mediante superusuario o usuario que tenga permisos REPLICATION
  • Son necesarios un mínimo de archivos WAL para poder restaurar

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Copia completa física (configuración)

  • Es necesario permitir explicitamente la replicación
    • En el pg_hba.conf
      • local replication postgres peer
  • Requiere tener configurado max_wal_senders para dejar almenos una sesión disponible y marcar el wal_level*
  • Si no estamos haciendo archiving debemos incluir:
    • -X stream, para que adjunte al backup los archivos WAL necesarios para restaurar

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Copia completa física (parámetros pg_basebackup)

  • Parámetros pg_basebackup
    • -D : directorio al cual hacer la copia
    • -F : formato, p para ficheros planos y t para un fichero.tar
    • -R : escribir en la copia el archivo recovery.conf para que sea un esclavo
    • -X : que añada los archivos WAL necesarios para restaurar en la copia. Dos formatos:
      • -f : fetch, copiarlos al final
      • -s : stream, ir copiándolos mientras se hace la copia
    • -z : activar la compresión por gzip en el formato tar
    • -P : enseñar el progreso mientras se va haciendo el backup
    • Más en la documentación oficial de psotgresql

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Utilizar la API de bajo nivel para copia

  • Quizá prefiramos hacer la copia nosotros
    • No replicar la copia de datos a otra ruta por falta de espacio
    • Backups incrementales de sistemas de ficheros para ahorrar transferencia
  • Procedmiento
    • Ejecutar una select antes de iniciar el proceso de copia
      • SELECT pg_start_backup('label');
        • Hace el checkpoint
        • Donde label es el identificador que queramos
    • Hacer copia de sistema de ficheros o snapshot
    • Ejecutar otra select al terminar la copia de los ficheros
      • SELECT pg_stop_backup();
    • Este procedimiento genera dos archives

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Copia completa física (planificación de la prueba)

  • Configurar el cluster actual para que permita copia contínua
  • Dejar corriendo un elefants.py en un cluster (5432 por ejemplo)
  • Hacer un pg_basebackup a un directorio
  • Restaurar el pg_basebackup en un cluster nuevo (5434 por ejemplo)
  • Comprobar las diferencias de elefantes en cada uno de los clusters

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Copia completa física (prueba elefants.py)

edu@apsl-edu:~/Proyectos/postgresql-checker$ cat config.yml
database: checkertest
host: localhost
port: 5432
user: checkeruser
password: 12345678

edu@apsl-edu:~/Proyectos/postgresql-checker$ python elefants.py 
0 elefantes se balanceaban, como veian que no se caian, fueron a llamar a otro elefante, llamado: 0
1 elefantes se balanceaban, como veian que no se caian, fueron a llamar a otro elefante, llamado: 1
2 elefantes se balanceaban, como veian que no se caian, fueron a llamar a otro elefante, llamado: 2
3 elefantes se balanceaban, como veian que no se caian, fueron a llamar a otro elefante, llamado: 3
4 elefantes se balanceaban, como veian que no se caian, fueron a llamar a otro elefante, llamado: 4
5 elefantes se balanceaban, como veian que no se caian, fueron a llamar a otro elefante, llamado: 5
6 elefantes se balanceaban, como veian que no se caian, fueron a llamar a otro elefante, llamado: 6
...

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Copia completa física (prueba pg_basebackup)

# Creamos la copia física con pg_basebackup
postgres@apsl-edu:~$ pg_basebackup -X stream -P -D /var/lib/postgresql/backup/
1590259/1590259 kB (100%), 1/1 tablespace

# Ya podemos ver la copia creada
postgres@apsl-edu:~$ cd /var/lib/postgresql/backup/
postgres@apsl-edu:~/backup$ ls -ltr
total 72
-rw------- 1 postgres postgres  210 oct 27 09:57 backup_label
drwx------ 8 postgres postgres 4096 oct 27 09:57 base
drwx------ 2 postgres postgres 4096 oct 27 09:57 pg_subtrans
drwx------ 2 postgres postgres 4096 oct 27 09:57 pg_stat_tmp
drwx------ 2 postgres postgres 4096 oct 27 09:57 pg_stat
drwx------ 2 postgres postgres 4096 oct 27 09:57 pg_serial
drwx------ 2 postgres postgres 4096 oct 27 09:57 pg_notify
drwx------ 4 postgres postgres 4096 oct 27 09:57 pg_multixact
drwx------ 2 postgres postgres 4096 oct 27 09:57 pg_xlog
-rwx------ 1 postgres postgres    4 oct 27 09:57 PG_VERSION
drwx------ 2 postgres postgres 4096 oct 27 09:57 pg_twophase
drwx------ 2 postgres postgres 4096 oct 27 09:57 pg_tblspc
drwx------ 2 postgres postgres 4096 oct 27 09:57 pg_clog
drwx------ 2 postgres postgres 4096 oct 27 09:57 pg_snapshots
drwx------ 2 postgres postgres 4096 oct 27 09:57 global

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Copia completa física (prueba restauración)

# Creamos un cluster nuevo para restaurar
postgres@apsl-edu:~$ pg_createcluster 9.3 restore
Creating new cluster 9.3/restore ...
config /etc/postgresql/9.3/restore
data   /var/lib/postgresql/9.3/restore
locale es_ES.UTF-8
port   5434

# Borramos el directorio de datos que ha creado
postgres@apsl-edu:~$ rm -R /var/lib/postgresql/9.3/restore/*

# Restauramos la copia creada antes con pg_basebackup en el directorio de datos del nuevo cluster
postgres@apsl-edu:~$ cp -Rp /var/lib/postgresql/backup/* /var/lib/postgresql/9.3/restore/

# Iniciamos el cluster
postgres@apsl-edu:~$ pg_ctlcluster 9.3 restore start

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Copia completa física (prueba comprobación)

# Prueba en el cluster original
edu@apsl-edu:~/Proyectos/postgresql-checker$ cat config.yml
database: checkertest
host: localhost
port: 5432
user: checkeruser
password: 12345678

edu@apsl-edu:~/Proyectos/postgresql-checker$ python count.py 
Hay 355 elefantes en la tela

# Prueba en el cluster restaurado
edu@apsl-edu:~/Proyectos/postgresql-checker$ cat config.yml
database: checkertest
host: localhost
port: 5434
user: checkeruser
password: 12345678

edu@apsl-edu:~/Proyectos/postgresql-checker$ python count.py 
Hay 255 elefantes en la tela

# En el cluster nuevo habrá tantos elefantes como hubiese al momento de hacer la copia

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Copia continua (archiving)

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Copia continua (archiving)

  • Se archivan los archivos WAL a otra ruta a medida que se van generando
  • Podremos restaurar los datos de los arhivos WAL que queramos a partir de un basebackup
  • Lo interesante es que los archivos WAL se saquen del servidor
  • Opciones:
    • Artesanal: se copian los archivos mediante rsync o a una ruta de red
    • Barman: Software de servidor que maneja WALs y basebackups de multiples servidores
    • Wal-e: Software que sube los WAL y basebackups a AWS S3

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Copia continua (archiving configuración)

# postgresql.conf
archive_mode = on # activar la copia continua (archiving)
archive_command = 'wal-push OR barman OR rsync OR ... %p' # comando para archivar el wal
archive_timeout = 1800      # Numero de segundos tras los que forzar que un log file cambie
  • El parámetro %p es el que recibirá el comando para saber de que logfile se trata (nombre de fichero)

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

barman

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

barman

  • Introducción comercial
  • Se instala en un servidor propio
  • Conexión entre los clientes y el servidor via key ssh
  • El servidor barman tiene que poder ejecutar como superusuario en los servidores postgresql
  • Hace tanto los basebackups como copiado de los archives
  • Empaquetado en ubuntu 14.04

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

barman (uso)

# Pruebas con barman, teniendo barman y el servidor postgresql en el mismo servidor
# Hay que configurar el acceso del usuario barman por claves ssh

# postgresql.conf
wal_level = archive
archive_mode = on
archive_command = 'rsync -a %p barman@localhost:main/incoming/%f'

# /etc/barman.conf
[main]
description =  "Main PostgreSQL Database" 
ssh_command = ssh postgres@localhost
conninfo = host=localhost user=postgres password=1234
minimum_redundancy = 1
retention_policy = RECOVERY WINDOW OF 4 WEEKS

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

barman (uso)

barman@apsl-edu:~$ barman check main
Server main:
    ssh: OK
    PostgreSQL: OK
    archive_mode: OK
    archive_command: OK
    directories: OK
    retention policy settings: OK
    compression settings: OK
    minimum redundancy requirements: FAILED (have 0 backups, expected at least 1)       
barman@apsl-edu:~$ barman list-server
main - Main PostgreSQL Database
barman@apsl-edu:~$ barman backup main
Starting backup for server main in /var/lib/barman/main/base/20140723T163443
Backup start at xlog location: 0/23000028 (000000010000000000000023, 00000028)
Copying files.
Copy done.
Asking PostgreSQL server to finalize the backup.
Backup end at xlog location: 0/230000B8 (000000010000000000000023, 000000B8)
Backup completed
barman@apsl-edu:~$ barman check main
Server main:
    ssh: OK
    PostgreSQL: OK
    archive_mode: OK
    archive_command: OK
    directories: OK
    retention policy settings: OK
    compression settings: OK
    minimum redundancy requirements: OK (have 1 backups, expected at least 1)

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

barman (uso)

# Ya se pueden listar los backups que haya para un servidor determinado
barman@apsl-edu:~$ barman list-backup main
main 20140723T163443 - Wed Jul 23 16:34:45 2014 - Size: 159.0 MiB - WAL Size: 0 B
main 20140723T163133 - Wed Jul 23 16:31:37 2014 - Size: 159.0 MiB - WAL Size: 16.0 MiB

# Se puede recuperar un backup en local, con
barman recover main 20140723T163133 /ruta/local/para/recuperar

# Incluso se puede hacer directamente en el server remoto con (o en otro server remoto!)
barman@apsl-edu:~$ barman recover --remote-ssh-command='ssh postgres@localhost' \
main 20140723T163133 /var/lib/postgresql/9.3/main
Starting remote restore for server main using backup 20140723T163133 
Destination directory: /var/lib/postgresql/9.3/main
Copying the base backup.
Copying required wal segments.
The archive_command was set to 'false' to prevent data losses.

Your PostgreSQL server has been successfully prepared for recovery!

Please review network and archive related settings in the PostgreSQL
configuration file before starting the just recovered instance.

WARNING: Before starting up the recovered PostgreSQL server,
please review also the settings of the following configuration
options as they might interfere with your current recovery attempt:

    data_directory = '/var/lib/postgresql/9.3/main' # use data in another directory
    external_pid_file = '/var/run/postgresql/9.3-main.pid' # write an extra PID file
    hba_file = '/etc/postgresql/9.3/main/pg_hba.conf' # host-based authentication file
    ident_file = '/etc/postgresql/9.3/main/pg_ident.conf' # ident configuration file
    ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem' # (change requires restart)
    ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key' # (change requires restart)

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

barman (uso)

# Está preparado para hacer recovery en un punto en el tiempo
# pasandole uno de estos parámetros extra el comando recover

--target-time TARGET_TIME: to specify a timestamp
--target-xid TARGET_XID: to specify a transaction ID
--target-name TARGET_NAME: to specify a named restore point -
    previously created with the pg_create_restore_point(name)
  • Conclusiones:
    • Es una buena opción para no tener que manejar los archives a mano
    • Está muy bien hecho y es robusto

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

wal-e

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

wal-e

  • Wal-e, copyright con posibilidad de modificación y distribución.
  • Originalmente desarrollado por heroku
  • Programa hecho en python que se configura en cada servidor postgresql
  • Sube los archivos a Amazon Web Services S3 (Simple Storage Service)
  • Proporciona comandos:
    • backup-fetch: traer un basebackup desde S3
    • backup-push: subir un basebackup a S3
    • wal-fetch: traer un archivo WAL desde S3
    • wal-push: subir un archivo WAL a S3
    • backup-list: para listar que backups hay guardados en S3
    • delete: para borrar backups antiguos que ya no queramos conservar
      • delete retain: deja el número de backups que le especifiquemos
  • Utilizado por instagram
    • Overall, we’ve been very happy with Postgres’ performance and reliability.

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

wal-e (instalación y configuración)

# Dependecias de sistema
aptitude install daemontools python-dev git libevent-dev pv lzop python-pip

# Instalamos wal-e
pip install six==1.7.3 # Requisito de wal-e en algunos sitemas con six antiguo
pip install wal-e

# Configuración de conexión
postgres@apsl-edu:/etc/wal-e.d/env$ ls -l
-rwxr--r-- 1 root postgres 21 oct 27 16:44 AWS_ACCESS_KEY_ID
-rwxr--r-- 1 root postgres 41 oct 28 09:39 AWS_SECRET_ACCESS_KEY
-rwxr--r-- 1 root postgres 33 oct 27 16:45 WALE_S3_PREFIX


# postgresql.conf
archive_mode = on
archive_command = '/usr/bin/envdir /etc/wal-e.d/env /usr/local/bin/wal-e wal-push %p'
archive_timeout = 30 # Tiempo muy bajo, solo para las pruebas, nosotros configuramos 1800

# Crons típicos
postgres@apl-edu:~$ crontab -l
# Base backup diario
25 0 * * * /usr/bin/envdir /etc/wal-e.d/env /usr/local/bin/wal-e backup-push \ 
/var/lib/postgresql/9.3
# Borramos backups antiguos, retencion de los ultimos 10 dias
0 3 * * * /usr/bin/envdir /etc/wal-e.d/env /usr/local/bin/wal-e delete --confirm retain 10

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

wal-e (monitorización del basebackup)

#!/usr/bin/python
### Check if the backup wal-e postgres is executed in the last day

import subprocess
import sys
from datetime import datetime, timedelta
import time

command = "/usr/bin/envdir /etc/wal-e.d/env /usr/local/bin/wal-e backup-list | tail -1"

p = subprocess.Popen(command, stdout=subprocess.PIPE, shell=True)
output, err = p.communicate()
last =  output

last_string = last.split()[1][:-5]
last_datetime = datetime.strptime(last_string, "%Y-%m-%dT%H:%M:%S")
#print last_datetime

last_expected = datetime.now() - timedelta(hours=28)
#print last_expected

if last_datetime < last_expected:
    print "CRITICAL: Ultimo backup en wal-e es de mas de un dia, el ultimo es de: %s !" % last_datetime
else:
    print "OK. Backup correcto. Ultimo backup es de: %s" % last_datetime

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

wal-e (monitorización de la subida de los wals)

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Copia continua (planificación prueba)

  • Configurar el cluster para que haga archiving hacia wal-e
    • Con un archive_timeout bajo para que suba archivos cada poco tiempo
  • Dejar corriendo un elefants.py en un cluster (5432 por ejemplo)
  • Hacer un basebackup con wal-e
  • Dejar un tiempo que se sigan subiendo archivos de WAL a S3
  • Restaurar en otro cluster diferente con un recovery.conf para que traiga los archivos de S3

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Copia continua (prueba)

# Lanzamos el checker
edu@apsl-edu:~/Proyectos/postgresql-checker$ python elefants.py
0 elefantes se balanceaban, como veian que no se caian, fueron a llamar a otro elefante, llamado: 0
1 elefantes se balanceaban, como veian que no se caian, fueron a llamar a otro elefante, llamado: 1
...

# Lanzamos el basebackup con wal-e
postgres@apsl-edu:~$ /usr/bin/envdir /etc/wal-e.d/env /usr/local/bin/wal-e \
backup-push /var/lib/postgresql/9.3/main
wal_e.operator.backup INFO     MSG: start upload postgres version metadata
        DETAIL: Uploading to s3://cursopostgres-wale/test-edu/basebackups_005/base_000000010000000000000020_00000040/extended_version.txt.
        STRUCTURED: time=2014-10-28T09:33:22.893252-00 pid=17929
wal_e.operator.backup INFO     MSG: postgres version metadata upload complete
        STRUCTURED: time=2014-10-28T09:33:23.241488-00 pid=17929
wal_e.worker.upload INFO     MSG: beginning volume compression
        DETAIL: Building volume 0.
        STRUCTURED: time=2014-10-28T09:33:23.269704-00 pid=17929
wal_e.worker.upload INFO     MSG: begin uploading a base backup volume
        DETAIL: Uploading to "s3://cursopostgres-wale/test-edu/basebackups_005/base_000000010000000000000020_00000040/tar_partitions/part_00000000.tar.lzo".
        STRUCTURED: time=2014-10-28T09:33:23.557607-00 pid=17929
wal_e.worker.upload INFO     MSG: finish uploading a base backup volume
        DETAIL: Uploading to "s3://cursopostgres-wale/test-edu/basebackups_005/base_000000010000000000000020_00000040/tar_partitions/part_00000000.tar.lzo" complete at 122.419KiB/s.
        STRUCTURED: time=2014-10-28T09:34:01.338401-00 pid=17929
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Copia continua (prueba)

# Podemos listar los backups existentes
postgres@apsl-edu:~$ /usr/bin/envdir /etc/wal-e.d/env /usr/local/bin/wal-e backup-list
name    last_modified   expanded_size_bytes     wal_segment_backup_start        wal_segment_offset_backup_start wal_segment_backup_stop wal_segment_offset_backup_stop
base_000000010000000000000020_00000040  2014-10-28T09:34:03.000Z                000000010000000000000020        00000040

# Creamos un cluster para la restauración del backup
postgres@apsl-edu:~$ pg_createcluster 9.3 wale
Creating new cluster 9.3/wale ...
config /etc/postgresql/9.3/wale
data   /var/lib/postgresql/9.3/wale
locale es_ES.UTF-8
port   5433

# Borramos el contenido de la carpeta de datos del nuevo cluster creado
postgres@apsl-edu:~$ rm -R /var/lib/postgresql/9.3/wale/*

# Restauramos el basebackup 
postgres@apsl-edu:~$ /usr/bin/envdir /etc/wal-e.d/env /usr/local/bin/wal-e backup-fetch \ 
/var/lib/postgresql/9.3/wale/ base_000000010000000000000020_00000040
# Donde hay que especificar la ruta
# Y el identificador que nos ha dado el listado de backups
# Ya tendremos el directorio preparado para configurar el recovery.conf

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Copia continua (prueba)

# Si intentamos levantar el cluster sin recovery.conf nos dará error
# Necesitamos los archivos WAL
postgres@apsl-edu:~/9.3/wale$ pg_ctlcluster 9.3 wale start
The PostgreSQL server failed to start. Please check the log output:
2014-10-28 10:50:21 CET LOG:  database system was interrupted; last known up at 2014-10-28 10:33:22 CET
2014-10-28 10:50:21 CET LOG:  creating missing WAL directory "pg_xlog/archive_status"
2014-10-28 10:50:21 CET LOG:  invalid checkpoint record
2014-10-28 10:50:21 CET FATAL:  could not locate required checkpoint record
2014-10-28 10:50:21 CET HINT:  If you are not restoring from a backup, try removing the file "/var/lib/postgresql/9.3/wale/backup_label".
2014-10-28 10:50:21 CET LOG:  startup process (PID 21139) exited with exit code 1
2014-10-28 10:50:21 CET LOG:  aborting startup due to startup process failure

# Preparamos el recovery.conf para que sepa donde encontrar los WAL
postgres@apsl-edu:~/9.3/wale$ cat recovery.conf
restore_command = '/usr/bin/envdir /etc/wal-e.d/env /usr/local/bin/wal-e wal-fetch "%f" "%p"'

# Levantamos el cluster con el recovery.conf
postgres@apsl-edu:/var/log/postgresql$ pg_ctlcluster 9.3 wale start
WARNING: connection to the database failed, disabling startup checks:
psql: FATAL:  the database system is starting up

# El error FATAL es normal, pues la restauración requiere que se bajen los WALs
# antes de tener operativo el server

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Copia continua (prueba)

# Veremos en los logs como va bajando los archivos wal
tail -n 100 /var/log/postgresql/postgresql-9.3-wale.log -f

# Hasta que ya ha terminado de restaurar y podemos ver
2014-10-28 10:58:02 CET LOG:  archive recovery complete
2014-10-28 10:58:02 CET LOG:  autovacuum launcher started
2014-10-28 10:58:02 CET LOG:  database system is ready to accept connections

# Podemos comprobar la base de datos de elefants para ver los cambios    
edu@apsl-edu:~/Proyectos/postgresql-checker$ cat config.yml
...
port: 5432
...

edu@apsl-edu:~/Proyectos/postgresql-checker$ python count.py
Hay 1633 elefantes en la tela

edu@apsl-edu:~/Proyectos/postgresql-checker$ cat config.yml
...
port: 5433
...

edu@apsl-edu:~/Proyectos/postgresql-checker$ python count.py
Hay 1594 elefantes en la tela

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Copia continua (conclusiones)

  • Tendremos un tiempo de perdida de datos cómo máximo de archive_timeout
    • Los archivos wal que no se hayan subido no se pueden restaurar
  • Pero un archive_timeout bajo generará más archivos wal
    • Perjudicando al tiempo de restauración (tiene que bajar más archivos)

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Recuperación en un punto en el tiempo

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Recuperación en un punto en el tiempo

  • Point in time recovery (PITR)
  • Como tenemos todas las transacciones existentes en los WAL
  • Le podemos decir que nos recupere a un punto exacto :
    • Una marca que hayamos añadido nosotros (útil hacerlo previo cambio peligroso)
    • Marca de tiempo
    • ID de transacción
  • No hay que prerarar nada al hacer el backup
    • Se especifica donde, al restaurar
  • Gran herramienta de recuperación ante fallos humanos

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Recuperación en un punto en el tiempo (configuración)

  • Se añaden parámetros extra en el recovery.conf
  • Podemos restaurar con diferentes tipos de indicaciones:
    • recovery_target_name: un nombre que creamos con pg_create_restore_point()
    • recovery_target_time: una marca de tiempo
    • recovery_target_xid: un id de transacción
  • Otras opciones:
    • recovery_target_inclusive: si especificamos si nos queremos parar antes o despues de la transacción
    • recovery_target_timeline: que timeline queremos
    • pause_at_recovery_target: pausar el server (solo lecturas) al terminar la recuperación
  • Más en la documentación oficial de postgresql

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Recuperación en un punto en el tiempo (prueba)

  • Aprovechamos el backup que hemos hecho al probar la copia continua
  • Creamos un nuevo cluster para esta prueba
  • Restauramos el mismo basebackup de wal-e de antes
  • Configuramos un punto en el tiempo posterior al base backup
  • Comprobamos los resultados del checker

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Recuperación en un punto en el tiempo (prueba)

# Creamos el cluster para la restauración pitr
postgres@apsl-edu:/var/log/postgresql$ pg_createcluster 9.3 pitr
Creating new cluster 9.3/pitr ...
config /etc/postgresql/9.3/pitr
data   /var/lib/postgresql/9.3/pitr
locale es_ES.UTF-8
port   5434

# Listamos los backups
postgres@apsl-edu:~/9.3/pitr$ /usr/bin/envdir /etc/wal-e.d/env /usr/local/bin/wal-e backup-list
name    last_modified   expanded_size_bytes     wal_segment_backup_start        wal_segment_offset_backup_start wal_segment_backup_stop wal_segment_offset_backup_stop
base_000000010000000000000020_00000040  2014-10-28T09:34:03.000Z                000000010000000000000020        00000040


# Restauramos el mismo base backup que antes
postgres@apsl-edu:~/9.3/pitr$ rm -r /var/lib/postgresql/9.3/pitr/*
postgres@apsl-edu:~/9.3/pitr$ /usr/bin/envdir /etc/wal-e.d/env /usr/local/bin/wal-e \ 
backup-fetch /var/lib/postgresql/9.3/pitr/ base_000000010000000000000020_00000040

# Configuración del recovery.conf (un minuto después del basebackup)
postgres@apsl-edu:~/9.3/pitr$ cat recovery.conf
restore_command = '/usr/bin/envdir /etc/wal-e.d/env /usr/local/bin/wal-e wal-fetch "%f" "%p"'
recovery_target_time = '2014-10-28 10:34:50'

# Levantamos el cluster pitr
postgres@apsl-edu:~/9.3/pitr$ pg_ctlcluster 9.3 pitr start

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Recuperación en un punto en el tiempo (prueba)

# Comprobamos con el checker que los datos sean coherentes con la fecha que pusimos
edu@apsl-edu:~/Proyectos/postgresql-checker$ cat config.yml
database: checkertest
host: localhost
port: 5434
user: checkeruser
password: 12345678
edu@apsl-edu:~/Proyectos/postgresql-checker$ python count.py
Hay 273 elefantes en la tela

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Unificación de conexiones (connection pooling)

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Unificación de conexiones (connection pooling)

  • Una capa intermedia entre la aplicación y postgresql para mantener las conexiones abiertas
  • La aplicación hace las peticiones a la capa intermedia como si de un servidor se tratase
  • La capa intermedia se encarga de gestionar la conexión final con el servidor postgresql
  • Hablando con propiedad, sería una caché de conexiones, ya que se reusa una conexión previa, o abre una nueva.

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Unificación de conexiones (connection pooling)

  • Ventajas:
    • Principal: La conexión está siempre creada y se reduce el tiempo de conexión (latencia)
    • Permite controlar memoria procesos backend PostgreSQL
    • Las conexiones se pueden encolar si se alcanza el número máximo de conexiones
  • Si como requisito debemos soportar muchas conexiones (>150), y nuestra aplicación no implementa pooling, necesitaremos un pool de conexiones externo

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Unificación de conexiones (opciones)

  • Pgpool
    • No solo pooling:
      • Balanceo de carga
      • Control de replicación PostgreSQL
      • Particionamiento, sharding
    • Usa procesos
  • PgBouncer (Skype)
    • Dedicado exclusivamente a pooling
    • Arquitectura asíncrona, basada en libevent. Escala mucho mejor. Consume pocos recursos, no lanza proceso por conexión.
    • Configuración sencilla

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

PgBouncer

  • Modos de caché de conexiones:
    • Session pooling: Por defecto. Se asigna una conexión del pool mientras dura conexión del cliente.
    • Transaction pooling: Se asigna conexión al servidor sólo durante la transacción. Al finalizar se libera conexión y puede ser reusada. No soporta algunos modos de sesión de PostgreSQL
    • Statement Pooling. La más agresiva. El cliente debe funcionar en modo autocommit. Orientado a PL/Proxy.

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

PgBouncer · Configuración pgbouncer.ini

[databases]

; Fallback, si no encuentra el nombre exacto
* = host=localhost port=5432

; pgbench
pgbench = host=127.0.0.1 dbname=pgbench

; Acceso a la BD sobre un unico usuario, y por tanto unico pool
mybench = host=127.0.0.1 port=300 user=foo password=baz dbname=pgbench

[pgbouncer]
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
listen_addr = 127.0.0.1
listen_port = 6432
unix_socket_dir = /var/run/postgresql
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
admin_users = postgres
stats_users = postgres
pool_mode = session
server_reset_query = DISCARD ALL
max_client_conn = 1000
default_pool_size = 20

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

PgBouncer · auth_file

cat /etc/pgbouncer/userlist.txt

"usuario1"  "misma-clave-que-en-postgres-1"
"usuario2"  "misma-clave-que-en-postgres-2"
  • Hay que mantener usuarios por duplicado
  • Si usamos auth_type=md5, podemos usar hash md5

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

PgBouncer · Ejemplo generación userlist.txt

mkauth.py

#! /usr/bin/env python
import sys, os, tempfile, psycopg2

# [...]
# create new file data
db = psycopg2.connect(sys.argv[2])
curs = db.cursor()
curs.execute("select usename, passwd from pg_shadow order by 1")
lines = []
for user, psw in curs.fetchall():
    user = user.replace('"', '""')
    if not psw: psw = ''
    psw = psw.replace('"', '""')
    lines.append('"%s" "%s" ""\n' % (user, psw))
db.commit()
cur = "".join(lines)

with open("/etc/pgbouncer/userlist.txt", 'w') as f:
    f.write(cur)

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

PgBouncer · Command line

$ psql -p 6432 -U test -d pgbouncer
pgbouncer=# show help;
NOTICE:  Console usage
DETAIL:  
    SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|VERSION
    SHOW STATS|FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM
    SHOW DNS_HOSTS|DNS_ZONES
    SET key = arg
    RELOAD
    PAUSE [<db>]
    RESUME [<db>]
    KILL <db>
    SUSPEND
    SHUTDOWN
SHOW
pgbouncer=# show stats;
database  | total_requests | total_received | total_sent | total_query_time | avg_req | avg_recv | avg_sent | avg_query 
-----------+----------------+----------------+------------+------------------+---------+----------+----------+-----------
pgbouncer |              1 |              0 |          0 |                0 |       0 |        0 |        0 |         0
template1 |              0 |              0 |          0 |                0 |       0 |        0 |        0 |         0
(2 rows)

pgbouncer=# reload ;
RELOAD

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Replicación

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Replicación (historia)

  • Históricamente el proyecto no quería introducir replicación en el núcleo
  • Lo dejaban en manos de extensiones externas que se encargaban de ello
    • pg-pool, slony, londsite, etc
  • Soluciones complejas y penalizaba a la elección de postgresql
  • En 2008 cambiaron de idea y decidieron dedicarse
  • Han ido añadiendo funcionalidades en el núcleo y siguen desarrollando
    • Empezaron por maestro/esclavo simple
    • Pronto llegará multi-maestro

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Replicación (teoría)

  • Aprovechar los registros WAL para que otro servidor postgresql tenga los datos sincronizados
  • Sin carga extra para el servidor maestro para la sincronización, es "gratis"
  • Desde la versión 8.3 de postgresql
  • No requiere cambios en base de datos o tablas
  • El servidor esclavo va leyendo los WAL y los va aplicando
  • Varios tipos de replicación
  • Siempre existirá un retraso entre el maestro y el esclavo
    • Ese retraso será diferente dependiendo del tipo de replicación
  • Se pueden realizar consultas al esclavo para aliviar al maestro

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Replicación (tipos de replicación)

  • Warm Standby
    • Se copia el WAL una vez completado en el maestro
    • Se van aplicando en el esclavo según se reciben
    • No permite lecturas de base de datos
  • Hot Standby
    • Igual que Warm Standby pero permitiendo lecturas
    • WAL via almacenamiento de red, teóricamente escalable infinitamente
    • Mucho retraso: archive_timeout + pasarlo por red + aplicarlo
    • No aplica para balanceo de carga ni para alta disponibilidad

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Replicación (tipos de replicación)

  • Streaming Replication
    • Mantiene una conexión de red entre el maestro y el esclavo para sincronizar
    • No espera a que un archivo este completo, va envíando el flujo de transacciones
    • Procesos en el maestro (walsender) y esclavo (walreceiver) para gestionar esto
    • Supone una carga extra en el maestro
      • Un proceso walsender para cada esclavo
      • Aunque un maestro puede gestionar múltiples esclavos sin problemas
    • Es replicación asíncrona
      • Los datos del esclavo pueden no estar actualizados al consultar
      • El retraso es variable dependiendo de la carga y la conexión de red

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Replicación (tipos de replicación)

  • Cascading Replication
    • esclavos que se sincronizan desde otros esclavos
  • Synchronous Replication
    • Es replicación síncrona
    • El maestro espera a que esten sincronizados los esclavos ante cualquier escritura
    • Aumenta la latencia de todas las escrituras
    • Requiere de buena conectividad de red para que sea viable

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Replicación (futuro de replicación)

  • Bi-Directional Replication
    • Replicación multi-maestro
    • Los maestros son consistentes individualmente
    • Un grupo de servidores será eventualmente consintente
    • Utiliza un nuevo stream lógico añadiendo mas información a los archivos WAL
    • La replicación se hace nivel de base de datos, no de servidor
      • Podríamos replicar solo una base de datos
      • Una conexión/proceso por cada base de datos
    • Actualmente en desarrollo
      • Disponible en la próxima versión 9.4 aplicando un parche
      • Previsión de incluirlo en versión 9.5 sin necesidad de parche

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Replicación (configuración)

  • La configuración será la misma para el maestro y el esclavo
  • El esclavo arranca con un archivo de recovery.conf y el maestro no
  • Configuración:
    • Crear el usuario de replicación
      • Por md5 en modo replication en el pg_hba.conf para la red compartida
    • postgresql.conf
      • listen_addreses: permitir la ip del esclavo
      • wal_level: 'hot_standby' (permitiendo lecturas en el esclavo) o 'archive'
      • wal_keep_segments: número de segmentos WAL que se guardan
      • archiving: activarlo si el número de wal_keep_segments no es suficientemente alto
      • hot_standby: activarlo (el servidor maestro lo ignorará)
    • Archivo de recovery.conf en el esclavo
      • standby_mode: activar el servidor como esclavo
      • primary_conninfo: conexión con el servidor maestro (usuario de replicación)

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Replicación (procedimiento de iniciación)

  • Iniciar el master con la configuración aplicada
  • Se hace un base backup en el maestro que se restaura en el esclavo
    • Esto es necesario para que compartan historial temporal
  • Se configura el recovery.conf del esclavo
  • Iniciar el esclavo
  • Demostración
    • Vamos a ver el funcionamiento con dos clusters dentro de un mismo servidor
    • La copia del base backup se hace facilmente
    • Es más sencillo de probar

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Replicación (demostración)

# Creamos dos nuevos clusters
postgres@apsl-edu:~/9.3$ pg_createcluster 9.3 maestro
Creating new cluster 9.3/maestro ...
config /etc/postgresql/9.3/maestro
data   /var/lib/postgresql/9.3/maestro
locale es_ES.UTF-8
port   5433
postgres@apsl-edu:~/9.3$ pg_createcluster 9.3 esclavo
Creating new cluster 9.3/esclavo ...
config /etc/postgresql/9.3/esclavo
data   /var/lib/postgresql/9.3/esclavo
locale es_ES.UTF-8
port   5434

# Configuramos el pg_hba para la replicación
# Habría que revisar esta configuración para el caso de servidores diferentes
postgres@apsl-edu:~/9.3$ cat /etc/postgresql/9.3/maestro/pg_hba.conf | grep replicator
host    replication     replicator      localhost               md5

# Configuramos el postgresql.conf del maestro
postgres@apsl-edu:~/9.3$ tail -n 4 /etc/postgresql/9.3/maestro/postgresql.conf
wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 32
hot_standby = on

# Iniciamos el cluster maestro
postgres@apsl-edu:~/9.3$ pg_ctlcluster 9.3 maestro start

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Replicación (demostración)

# Creamos el usuario replicator
postgres@apsl-edu:~/9.3$ psql -p 5433 
postgres=# CREATE ROLE replicator WITH REPLICATION PASSWORD 'password' LOGIN;
CREATE ROLE

# Configuramos el esclavo igual que el maestro
postgres@apsl-edu:~/9.3$ cat /etc/postgresql/9.3/esclavo/pg_hba.conf | grep replicator
host    replication     replicator      localhost               md5

postgres@apsl-edu:~/9.3$ tail -n 4 /etc/postgresql/9.3/esclavo/postgresql.conf
wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 32
hot_standby = on

# Borramos el contenido de la carpeta de datos del esclavo
postgres@apsl-edu:~$ rm -r /var/lib/postgresql/9.3/esclavo/*

# Hacemos un base backup con destino al esclavo, con el usuario replicator
# Nos pedirá la contraseña
postgres@apsl-edu:~$ pg_basebackup -p 5433 -U replicator -h localhost \ 
-R -P -D /var/lib/postgresql/9.3/esclavo/
Password:
19614/19614 kB (100%), 1/1 tablespace
NOTICE:  WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to complete the backup

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Replicación (demostración)

# Ya tenemos el esclavo preparado
postgres@apsl-edu:~$ ls -ltr /var/lib/postgresql/9.3/esclavo/
total 64
-rw------- 1 postgres postgres  206 oct 29 12:57 backup_label
drwx------ 5 postgres postgres 4096 oct 29 12:57 base
drwx------ 2 postgres postgres 4096 oct 29 12:57 pg_subtrans
drwx------ 2 postgres postgres 4096 oct 29 12:57 pg_stat_tmp
drwx------ 2 postgres postgres 4096 oct 29 12:57 pg_stat
drwx------ 2 postgres postgres 4096 oct 29 12:57 pg_serial
drwx------ 2 postgres postgres 4096 oct 29 12:57 pg_notify
drwx------ 4 postgres postgres 4096 oct 29 12:57 pg_multixact
-rw-rw-r-- 1 postgres postgres  152 oct 29 12:57 recovery.conf
drwx------ 2 postgres postgres 4096 oct 29 12:57 pg_xlog
-rw------- 1 postgres postgres    4 oct 29 12:57 PG_VERSION
drwx------ 2 postgres postgres 4096 oct 29 12:57 pg_twophase
drwx------ 2 postgres postgres 4096 oct 29 12:57 pg_tblspc
drwx------ 2 postgres postgres 4096 oct 29 12:57 pg_snapshots
drwx------ 2 postgres postgres 4096 oct 29 12:57 pg_clog
drwx------ 2 postgres postgres 4096 oct 29 12:57 global

# Como añadimos el parametro -R nos habrá generado un recovery.conf
postgres@apsl-edu:~/9.3/esclavo$ cat recovery.conf
standby_mode = 'on'
primary_conninfo = 'user=replicator password=password host=localhost port=5433 \ 
sslmode=prefer sslcompression=1 krbsrvname=postgres'

# Ya podemos iniciar el esclavo
postgres@apsl-edu:~$ pg_ctlcluster 9.3 esclavo start

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Replicación (demostración)

# Cualquier cambio en el maestro se verá reflejado en el esclavo
postgres@apsl-edu:~/9.3/esclavo$ psql -p 5433
psql (9.3.5)
Type "help" for help.

postgres=# CREATE DATABASE test;
CREATE DATABASE
postgres=# \q
postgres@apsl-edu:~/9.3/esclavo$ psql -p 5434
psql (9.3.5)
Type "help" for help.

postgres=# \l
                                List of databases
Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres  | postgres | UTF8     | es_ES.UTF-8 | es_ES.UTF-8 |
template0 | postgres | UTF8     | es_ES.UTF-8 | es_ES.UTF-8 | =c/postgres          +
        |          |          |             |             | postgres=CTc/postgres
template1 | postgres | UTF8     | es_ES.UTF-8 | es_ES.UTF-8 | =c/postgres          +
        |          |          |             |             | postgres=CTc/postgres
test      | postgres | UTF8     | es_ES.UTF-8 | es_ES.UTF-8 |
(4 rows)

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Replicación (demostración, monitorización y enlaces)

# Podemos ver los procesos encargados del envío y replicación en marcha
postgres@apsl-edu:~/9.3/esclavo$ ps -ax|grep postgres|grep wal
11418 ?        Ss     0:00 postgres: wal writer process                                                                                           
11672 ?        Ss     0:00 postgres: wal writer process                                                                                           
13051 ?        Ss     0:01 postgres: wal receiver process   streaming 0/3001138                                                                   
13052 ?        Ss     0:00 postgres: wal sender process replicator 127.0.0.1(36991) streaming 0/3001138

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

PostgreSQL HA

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

PostgreSQL HA · El problema

  • Alta Disponibilidad (HA):
    • Eliminar Puntos únicos de fallo (SPOF)
    • Un segundo servidor podría tomar el control si el primero falla.
  • Balanceo de carga:

    • Permitir que varios servidores sirvan los mismos datos.
  • El problema común: sincronización

    • La información debe ser propagada de forma consistente.
    • Existen múltiples soluciones, que abordan el problema de la sincronización de formas distintas.
    • Podemos evaluar las distintas soluciones en función de características: sincronicidad (síncronas, asíncronas), granularidad, rendimiento, complejidad, hardware...

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

PostgreSQL HA · Soluciones

PG Docs: different replication solutions

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Sobre HA

  • Sistema sin puntos únicos de fallo
  • HA no significa automatización: Tras fallo, puede requerir intervención manual para volver al estado inicial.
  • HA siempre añade complejidad a la administración de sistemas
  • HA no es alto rendimiento
  • HA no es balanceo de carga
  • Sobre disponibilidad:
    • Un sistema es 99% disponible si está caido menos de 4 días en un año
    • El deseo: cinco 9s: 99.999%, menos de 5 minutos de caída anual.

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

PostgreSQL HA con Streaming Replication (SR)

  • El nodo standby puede servir queries de lectura.
  • Modos de sincronía:
    • Síncrono: La escritura no finaliza hasta que todos los esclavos reciben el dato. El hardware es fundamental.
    • Asíncrono: Según hardware y red, hay un tiempo de repliación. Se pueden perder unos segundos de datos en caso de failover.
  • Se necesita un agente HA externo para gestionar el failover. No está incluido en el core de PostgreSQL.

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

PostgreSQL HA SR · Requisitos

  • La aplicación necesita una IP fija.
  • Debería haber un monitor en los nodos (primario, standby), revisando los procesos PostgreSQL.
  • El monitor debería reiniciar PostgreSQL si no está funcionando, configurable con un número máximo de fallos.
  • En caso de caída del primario, se debe promocionar un nodo standby (y sólo uno)
  • La IP se debe mover al nuevo nodo primario.
  • Lo anterior debe ser automático. La restauración del antiguo primario como standby puede ser manual o automática.

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Posibles soluciones para failover SR

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

PostgreSQL SR + Linux-HA: La pareja perfecta!

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

PostgreSQL SR + Linux-HA

  • Usamos el streaming replication de PostgreSQL para solucionar el problema de la sincronización.
  • Linux-HA nos da el resto de requisitos mencionados, sobre infraestructura completamente software libre.

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Linux-HA

  • http://www.linux-ha.org/
  • Solución de alta disponibilidad para Linux, FreeBSD, Solaris, etc.
  • Desde 1999: heartbeat
  • Ecosistema de múltiples proyectos de software libre: heartbeat, corosync, pacemaker, cluster-glue, openais, CMAN...
  • Cada distribución adapta su propia solución: RHEL, SuSE dan soporte a clustering, y proveen su propio Stack con soporte.
  • Solución presente y futura: pacemaker + corosync (>2.0)
    • http://clusterlabs.org/

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Componentes Cluster Pacemaker

  • Capa de paso de mensajes y permanencia del cluster
    • corosync, openais, heartbeat
    • Pertenencia a nodos
    • Mensajes entre nodos
    • Quorum
  • Cluster resource Manager (CRM)
    • pacemaker
    • Gestiona configuración del cluster (CIB)
    • Usa capa de mensajes
    • Gestiona recursos locales

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Componentes Cluster Pacemaker

  • Cluster Glue
    • Librerías y servicios comunes
    • STONITH. fencing
  • Resource Agents (RA)
    • Gestiona el recurso del cluster (PostgreSQL, IP, Filesystem, Haproxy..)
    • Soporta operaciones como start, stop, monitor, promote, demote...
    • Varios estándares y proveedores de RA
      • LSB: scripts inicio y parada init.d
      • OCF: (Open Cluster Framework)

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Pacemaker · Arquitectura cluster

http://clusterlabs.org/

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Pacemaker · Arquitectura interna

http://clusterlabs.org/

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Pacemaker · Ejemplo cluster failover

http://clusterlabs.org/

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Pacemaker · Instalación

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Pacemaker · Configuración -> corosync.conf

totem {
        version: 2
        crypto_cipher: none
        crypto_hash: none
        cluster_name: loapha
        rrp_mode: active
        interface {
                ringnumber: 0
                ttl: 1
                bindnetaddr: 192.168.0.0
                mcastaddr: 239.255.43.1
                mcastport: 5405
        }
}
logging {
        to_stderr: yes
        syslog_facility: local7
        [...]
}
quorum {
        provider: corosync_votequorum
        expected_votes: 2
        two_node: 1
        wait_for_all: 0
}

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Pacemaker · Configuración servicios

Nos basamos en crmsh.

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Pacemaker

root@haha1 ~ # crm_mon -1
Last updated: Thu Oct 30 14:12:10 2014
Last change: Wed Oct 22 11:00:05 2014
Stack: corosync
Current DC: haha1 (3232235521) - partition with quorum
Version: 1.1.12-561c4cf
2 Nodes configured
13 Resources configured

Online: [ haha1 haha2 ]

stonith_haha1  (stonith:external/hetzner): Started haha2 
stonith_haha2  (stonith:external/hetzner): Started haha1 
Resource Group: Frontend
    p_haproxy  (lsb:haproxy):  Started haha1 
    p_net_hetzner  (ocf::kumina:hetzner-failover-ip):  Started haha1 
Master/Slave Set: ms_redis [p_redis]
    Masters: [ haha1 ]
    Slaves: [ haha2 ]
p_redis_reader_vip (ocf::heartbeat:IPaddr2):   Started haha2 
p_redis_writer_vip (ocf::heartbeat:IPaddr2):   Started haha1 
Master/Slave Set: msPostgresql [pgsql]
    Masters: [ haha1 ]
    Slaves: [ haha2 ]
Resource Group: vip-pg
    vip-pgmaster   (ocf::heartbeat:IPaddr2):   Started haha1 
    vip-pgrep  (ocf::heartbeat:IPaddr2):   Started haha1 
vip-pgslave    (ocf::heartbeat:IPaddr2):   Started haha2

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Pacemaker PostgreSQL RA

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Pacemaker PostgreSQL RA · Configuración

VIP aplicación (escritura)

crm configure primitive vip-pgmaster ocf:heartbeat:IPaddr2 \
    params \
        ip="192.168.0.100" \
        nic="eth1" \
        cidr_netmask="24" \
    op start   timeout="60s" interval="0s"  on-fail="stop" \
    op monitor timeout="60s" interval="10s" on-fail="restart" \
    op stop    timeout="60s" interval="0s"  on-fail="block"

VIP para replicación

crm configure  primitive vip-pgrep ocf:heartbeat:IPaddr2 \
    params \
        ip="192.168.0.50" \
        nic="eth1" \
        cidr_netmask="24" \
    meta \
            migration-threshold="0" \
    op start   timeout="60s" interval="0s"  on-fail="restart" \
    op monitor timeout="60s" interval="10s" on-fail="restart" \
    op stop    timeout="60s" interval="0s"  on-fail="block"

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Pacemaker PostgreSQL RA · Configuración

pgsql RA

crm configure primitive pgsql ocf:heartbeat:pgsql \
    params \
        pgctl="/usr/lib/postgresql/9.3/bin/pg_ctl" \
        psql="/usr/bin/psql" \
        pgdata="/var/lib/postgresql/9.3/main" \
        tmpdir="/var/lib/postgresql/tmp" \
        start_opt="-p 5432" \
        rep_mode="sync" \
        node_list="haha1 haha2" \
        config="/etc/postgresql/9.3/main/postgresql.conf" \
        logfile="/var/log/postgresql/postgresql.log" \
        repuser="replicator" \
        monitor_user="monitor" \
        monitor_password="******" \
        primary_conninfo_opt="password=**** sslmode=require keepalives_idle=60 keepalives_interval=5 keepalives_count=5" \
        master_ip="192.168.0.50" \
        restart_on_promote="false" \
        stop_escalate="0" \
    op start   timeout="60s" interval="0s"  on-fail="restart" \
    op monitor timeout="60s" interval="4s" on-fail="restart" \
    op monitor timeout="60s" interval="3s"  on-fail="restart" role="Master" \
    op promote timeout="60s" interval="0s"  on-fail="restart" \
    op demote  timeout="60s" interval="0s"  on-fail="stop" \
    op stop    timeout="60s" interval="0s"  on-fail="block" \
    op notify  timeout="60s" interval="0s"

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Pacemaker PostgreSQL RA · Configuración

Master Slave Resource

crm configure ms msPostgresql pgsql \
    meta master-max="1" master-node-max="1"   clone-max="2"     clone-node-max="1"  notify="true"

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Pacemaker PostgreSQL RA · Configuración

Grupos, Restricciones de order y location

crm configure group vip-pg vip-pgmaster vip-pgrep

crm configure colocation vip-pg-col inf: vip-pg msPostgresql:Master

crm configure order vip-pg_order-1 0: msPostgresql:promote  vip-pg:start  symmetrical=false
crm configure order vip-pg_order-2 0: msPostgresql:demote   vip-pg:stop   symmetrical=false

crm configure location l_postgres_master msPostgresql rule $role="Master" +inf: #uname eq haha1

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

CRMSH

Hands on

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Pacemaker PostgreSQL · Promote / Demote

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Pacemaker PostgreSQL · Promote / Demote

Distinción entre status PostgreSQL y status Pacemaker

  • PostgreSQL Status
    • PRI: Ejecutnado como Primario (Master)
    • HS: Ejecutando como Hot Standby (Slave)
    • STOP: stopped
  • Pacemaker Status
    • Master
    • Slave
    • Stopped

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Pacemaker PostgreSQL · Estados

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Pacemaker PostgreSQL · Estados

El RA de Pgsql mantiene atributos internos por nodo, para decidir acción a tomar según estado del cluster.

  • psql-data-status

    • STREAMING|SYNC , STREAMING|ASYNC
    • DICONNECT
    • LATEST
  • pgsql-status

    • HS:alone, HS:async, HS:sync
    • PRI
    • UNKNOWN

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Pacemaker PostgreSQL · Estados

root@lohap1 ~ # crm_mon -1 -Afr
Last updated: Thu Oct 30 16:02:04 2014
Last change: Tue Oct 21 09:31:12 2014 via crmd on lohap1
Stack: corosync
Current DC: lohap1 (3232235521) - partition with quorum
Version: 1.1.11-f0f09b8
2 Nodes configured
15 Resources configured

Online: [ lohap1 lohap2 ]

Full list of resources:

Master/Slave Set: msPostgresql [pgsql]
    Masters: [ lohap1 ]
    Slaves: [ lohap2 ]

Node Attributes:
* Node lohap1:
    + master-pgsql                      : 1000      
    + pgsql-data-status                 : LATEST    
    + pgsql-master-baseline             : 000000814B000090
    + pgsql-status                      : PRI       
* Node lohap2:
    + pgsql-data-status                 : STREAMING|SYNC
    + pgsql-status                      : HS:sync

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Pacemaker PostgreSQL · Estados

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Pacemaker PostgreSQL HA · Conclusiones

  • Solución completa HA. Cumple todos los requisitos vistos anteriormente
  • Failover Automático. Recuperación del antiguo Master Manual
  • Varía comportamiento típico PostgreSQL: Siempre inicia como HS
    • Requiere history file en pg_xlog
  • Últimos pasos:
    • Probar
    • Probar
    • Volver a Probar. Romper: nodos, red, postgres...

Notes

Curso PostgreSQL · Oct 2014 · apsl.net

Demo pacemaker

Hands on

Notes

Curso PostgreSQL · Oct 2014 · apsl.net