Esta entrada intentará explicar como configurar una BBDD de respaldo en PostgreSQL. Lo que incluyo en esta entrada es un ejemplo rápido de cómo lo he hecho yo.
Lo primero de todo deberíamos tener 2 máquinas de similares características. En nuestro caso serán 2 máquinas con CentOS 7. Y en ambos deberíamos tener PostgreSQL instalado en cada máquina.
Máquina1 - Producción - propsql (IP 192.168.159.152)
Máquina2 - Producción - stbsql (IP 192.168.159.153)
Paso 1. Crear los directorios donde se van a ir copiando/moviendo los ficheros WAL (estos ficheros de REDO que guardan cambios realizados en la base de datos).
Producción
[postgres@propsql]$ mkdir -p /srv/redo
[postgres@propsql]$ mkdir -p /srv/wal_arch
[postgres@propsql]$ chmod -R 0700 /srv/redo
[postgres@propsql]$ chmod -R 0700 /srv/wal_arch
Standby
[postgres@stbsql]$ mkdir -p /srv/redo
[postgres@stbsql]$ mkdir -p /srv/wal_arch
[postgres@stbsql]$ mkdir -p /srv/wal_shipped
[postgres@stbsql]$ chmod -R 0700 /srv/redo
[postgres@stbsql]$ chmod -R 0700 /srv/wal_arch
[postgres@stbsql]$ chmod -R 0700 /srv/wal_shipped
Paso 2. Configuración del acceso mediante llaves SSH. Se crearán las llaves públicas para poder copiar los ficheros redos.
Producción
[postgres@propsql]$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/var/lib/pgsql/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/postgres/.ssh/id_rsa.
Your public key has been saved in /home/postgres/.ssh/id_rsa.pub.
Standby
[postgres@stbsql]$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/var/lib/pgsql/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/postgres/.ssh/id_rsa.
Your public key has been saved in /home/postgres/.ssh/id_rsa.pub.
Compartir las claves en las máquinas. Crear ficheros donde compartir la clave pública de cada máquina. Fijarse desde que máquina se lanza cada instrucción.
Permitir acceder de PRO a Standby. Copiar desde PRO el fichero con la clave pública a Standby.
[postgres@propsql]$ scp /var/lib/pgsql/.ssh/id_rsa.pub postgres@spropsql02.ibili.eus:~/claveProd.pub
Y desde Standby, añadir la clave pública en el fichero de las claves autorizadas.
[postgres@stbsql]$ cat claveProd.pub >> /var/lib/pgsql/.ssh/authorized_keys
Por último añadir la dirección IP en el fichero /etc/hosts.allow.
[root@stbsql]$ vi /etc/hosts.allow
sshd: 192.168.159.152
Permitir acceder de Standby a PRO
Copiar desde Standby el fichero con la clave pública a PRO
[postgres@stbsql]$ scp /var/lib/pgsql/.ssh/id_rsa.pub postgres@spropsql01.ibili.eus:~/claveProd.pub
Y desde PRO, añadir la clave pública en el fichero de las claves autorizadas.
[postgres@propsql]$ cat claveProd.pub >> /var/lib/pgsql/.ssh/authorized_keys
Por último añadir la dirección IP en el fichero /etc/hosts.allow.
[root@propsql]$ vi /etc/hosts.allow
sshd: 192.168.159.153
Paso3. Parametrizar el máquina de la BBDD Activa (Productivo).
Modificar el fichero de configuración.
Se deben cambiar algunos de los parámetros del fichero de configuración. El fichero es /srv/postgresql.conf.
wal_level = hot_standby - Este parámetro define cuanta información se grabará en los ficheros WAL generados. Se pueden utilizar tres valores, minimal, archive y hot_standby. En nuestro caso utilizamos el valor hot_standby, porque vamos a utilizar esta funcionalidad.
archive_mode = on - Con este parámetro activamos el archivo de ficheros WAL en el maestro.
archive_command = '/srv/archive_wal.sh -P %p -F %f' - Con el comando definido en este parámetro, copiamos los ficheros WAL a archivar al directorio /srv/wal_arch en el servidor maestro y transferimos los ficheros WAL archivados, al directorio /srv/wal_shipped en el servidor esclavo.
max_wal_senders = 3 - Con este parámetro definimos el número máximo de conexiones que se pueden realizar desde servidores esclavos al servidor maestro via SR
wal_keep_segments = 10 - Este parámetro define el número máximo de ficheros WAL que mantendremos sin reciclar en el servidor maestro.
Creación del fichero para copiar/mover redo.
El siguiente paso es crear el fichero /srv/archive_wal.sh, que será el encargado de copiar los ficheros de redo en la máquina de Standby. El contenido del fichero sería:
#!/bin/bash
CHMOD="/bin/chmod"
COPY="/bin/cp"
SCP="/usr/bin/scp"
# Directorio usado por PostgreSQL para generar los ficheros WAL
PG_XLOG_DIR="/srv/pg_xlog"
# Directorio usado por PostgreSQL para archivar los ficheros WAL
PG_WAL_ARCH_DIR="/srv/wal_arch"
# Servidor PostgreSQL Standby
STANDBY_SERVER="stbsql"
# Directorio en servidor esclavo donde transferimos los ficheros
# WAL archivados en el servidor maestro.
PG_WAL_SHIPPED="/srv/wal_shipped"
NO_ARGS=0
E_OPTERROR=65
# ########################################
# ########################################
#
# Function archive_wal()
#
# ########################################
# ########################################
archive_wal(){
if $COPY -dp $ABSOLUTE_PATH $PG_WAL_ARCH_DIR/$WAL_FILE
then
$CHMOD 400 $PG_WAL_ARCH_DIR/$WAL_FILE
$SCP $PG_WAL_ARCH_DIR/$WAL_FILE $STANDBY_SERVER:$PG_WAL_SHIPPED
else
sleep 1
exit 1
fi
}
# ########################################
# ########################################
# Script invoked with no command-line args?
# ########################################
# ########################################
if [ $# -eq "$NO_ARGS" ]
then
help
exit $E_OPTERROR
fi
# ########################################
# ########################################
# Getting command options
# ########################################
# ########################################
while getopts "P:F:" Option
do
case $Option in
P)
ABSOLUTE_PATH=$OPTARG;;
F)
WAL_FILE=$OPTARG;;
esac
done
shift $(($OPTIND - 1))
# ########################################
# ########################################
# Sanity check
# ########################################
# ########################################
if [ -z $ABSOLUTE_PATH ]
then
echo "Error: Absolute path not defined"
echo
exit $E_OPTERROR
fi
if [ -z $WAL_FILE ]
then
echo "Error: WAL filename not defined"
echo
exit $E_OPTERROR
fi
archive_wal
exit 0
#
# EOF
Antes de arrancar la bbdd de Producción se tiene que definir también en el fichero /srv/pg_hba.conf del servidor maestro una línea que permita el acceso del proceso receptor WAL (WAL receiver) al servidor maestro.
host replication all 192.168.159.153 trust
Ya se puede arrancar la bbdd de Producción.
/usr/bin/pg_ctl -D /srv start
Paso 4. Crear copia de seguridad base
A continuación, restauraremos esta copia de seguridad 'base' en el servidor esclavo.
[postgres@propsql]$ cd $PGDATA
[postgres@propsql]$ psql template1 -c "SELECT pg_start_backup('copia base inicial')"
pg_start_backup
-----------------
0/1000020
(1 row)
[postgres@propsql]$ tar -cvf /srv/pg_base_backup.tar /srv/
[postgres@propsql]$ psql template1 -c "SELECT pg_stop_backup()"
pg_stop_backup
----------------
0/10000D8
(1 row)
[postgres@propsql]$ scp /srv/pg_base_backup.tar spropsql02:~/tmp/
[postgres@propsql]$ ssh postgres@spropsql02 "cd / && tar -xvf /tmp/pg_base_backup.tar"
[postgres@propsql]$ ssh postgres@ spropsql02 "rm /srv/postmaster.pid"
Paso 5. Parametrización de STANDBY
Ahora se procede a configurar la bbdd de Standby. Se deben cambiar algunos de los parámetros de los ficheros de configuración.
Fichero /srv/postgresql.conf
hot_standby = on - Para definir que este servidor esclavo se podrá utilizar para realizar consultas de solo lectura.
Fichero /srv/recovery.conf (en caso de no existir se copia el fichero /usr/share/pgsql/recovery.conf.sample)
standby_mode = 'on' - Este parámetro define que el servidor no saldrá del modo de recuperación y continuará probando la restauración continua de información WAL.
primary_conninfo = 'host=spropsql port=5432 user=postgres' - Este parámetro define el servidor maestro usado para recoger registros WAL.
trigger_file = '/srv/pg_failover_trigger' - Con este parámetro se define un fichero que en caso de crearse/existir sacará al servidor esclavo del modo "hot standby" y de recuperación continua.
restore_command = 'cp /srv/wal_shipped/%f %p' - Con este parámetro definimos el comando a utilizar, si es necesario, para restaurar los ficheros WAL que se encuentran en /srv/wal_shipped.
Una vez realizado estos cambios podemos arrancar postgreSQL en el servidor esclavo.
/usr/bin/pg_ctl -D /srv start
Paso 5. Monitorización de la réplica.
Para controlar la replicación se ha creado una bd llamada "monitorización_replica" que contiene una tabla de HEARTBEAT que se actualiza en el origen cada 5 minutos.
Después de crear la tabla se crea una función que update el campo fecha.
CREATE OR REPLACE FUNCTION public.fnc_update_hb()
RETURNS integer AS
$BODY$
declare nCount int;
BEGIN
select count(*) into nCount from heartbeat;
IF nCount = 0 then
insert into heartbeat values (now());
else
update heartbeat set fecha=now();
END if;
return 1;
END;
Y finalmente se añade en el cron una tarea para que update la fecha/hora en la tabla HEARTBEAT. Se añade la siguiente línea
[postgres@propsql]$ crontab -e
#job para monitorizar la replicacion de standby
*/5 * * * * psql -d monitor_replica -c "select fnc_update_hb();"
Para saber si la replicación está al día basta con que logueemos en la máquina de standby (spropsql02) con el usuario postgres y lancemos esto:
[postgres@stbsql]$ psql -d monitor_replica -c "select * from heartbeat;"
Que nos devolverá la fecha en la que se hizo la última réplica:
fecha
---------------------------
2016-04-12 16:20:01.13292
(1 fila)
Si no está al día es que está fallando la réplica.
Para una explicación detallada de lo que es una BBDD Standby os recomiendo esta entrada donde lo detallan mucho mejor https://e-mc2.net/es/hot-standby-y-streaming-replication
