PostgreSQL es un sistema de gestión de bases de datos objeto-relacional, distribuido bajo licencia BSD. Características a destacar:
pg_upgrade
PostgreSQL contrib
CREATE EXTENSION module_name;
PgFoundry
Diagrama cortesía de: http://raghavt.blogspot.in/2011/04/postgresql-90-architecture.html
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
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
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)
fsync()
page
(8kb) en shared_buffers.shared_buffers
y tambien WAL (no fsync())fsync()
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.
.
$ select pg_current_xlog_location();
pg_current_xlog_location
--------------------------
8/584A62E0
(1 row)
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.
pg_xlog
: 2 * checkpoint_segments + 1Pará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):
.
# 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
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%]
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
sort
.'/usr/bin/envdir /etc/wal-e.d/env /usr/local/bin/wal-e wal-push %p'
VACUUM
, CREATE INDEX
effective_cache_size
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.maintenance_work_mem
sobre 50MB por GB de RAMcheckpoint_segments
, por encima de 10. El valor correcto dependerá
de la calidad del hardware, e importancia de recuperaciones rápidas.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.
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
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=#
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
Caso típico, problemas de sobrepasar limite de memoria compartida de sistema
/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
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
CREATE EXTENSION module_name;
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
...
# 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
# 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'
# 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)
Parámetros importantes:
Parámetros que pueden modificar los tests significativamente:
Parámetros que no influirán en pgbench
Comunes:
zfs set recordsize=8K zp1/data
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:
sudo hdparm -W 0 /dev/sda
$ 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'
#!/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
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)
sysctl -w vm.overcommit_memory=2
vm.swappiness=0
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
Fuente: PostgreSQL 9.0 High Performance - Packt Publishing
Fuente: PostgreSQL 9.0 High Performance - Packt Publishing
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
ALTER TABLESPACE pg_default SET (random_page_cost=2);
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)
# 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
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
#!/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
# 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
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 ...
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
...
# 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
# 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
# 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
# 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
# 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
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)
# 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)
# 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)
# 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
#!/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
# 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
# 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
# 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
# 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
# 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
# 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
[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
cat /etc/pgbouncer/userlist.txt
"usuario1" "misma-clave-que-en-postgres-1"
"usuario2" "misma-clave-que-en-postgres-2"
#! /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)
$ 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
# 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
# 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
# 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
# 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)
# 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
Balanceo de carga:
El problema común: sincronización
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
}
Nos basamos en crmsh.
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
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"
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"
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"
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
Distinción entre status PostgreSQL y status Pacemaker
El RA de Pgsql mantiene atributos internos por nodo, para decidir acción a tomar según estado del cluster.
psql-data-status
pgsql-status
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
Table of contents | t |
---|---|
Exposé | ESC |
Autoscale | e |
Full screen slides | f |
Presenter view | p |
Source files | s |
Slide numbers | n |
Blank screen | b |
Notes | 2 |
Help | h |