Cómo configurar la replicación Maestro-Esclavo para PostgreSQL 9.6 en Ubuntu 16.04

PostgreSQL o Postgres es un sistema de gestión de bases de datos objeto-relacional de código abierto (ORDBMS) con más de 15 años de desarrollo activo. Es un potente servidor de bases de datos y puede manejar altas cargas de trabajo. PostgreSQL se puede utilizar en servidores Linux, Unix, BSD y Windows.

La replicación de bases de datos maestro/esclavo es un proceso de copia (sincronización) de datos de una base de datos en un servidor (el maestro) a una base de datos en otro servidor (los esclavos). El principal beneficio de este proceso es distribuir bases de datos a múltiples máquinas, de modo que cuando el servidor maestro tiene un problema, hay una máquina de respaldo con los mismos datos disponible para manejar solicitudes sin interrupción.

PostgreSQL proporciona varias formas de replicar una base de datos. Se puede utilizar para fines de respaldo y para proporcionar un servidor de bases de datos de alta disponibilidad. En este tutorial, le mostraremos cómo instalar y configurar la replicación Maestro-Esclavo de PostgreSQL 9.6 en un servidor Ubuntu 16.04. Usaremos el modo de espera activa, y es un muy buen punto de partida para aprender PostgreSQL en profundidad.

Lo que haremos:

Instalar PostgreSQL 9.6.
Configurar el cortafuegos UFW.
Configurar el servidor MAESTRO.
Configurar el servidor ESCLAVO.
Copiar datos de PostgreSQL del MAESTRO al ESCLAVO.
Pruebas.

Requisitos previos:

1 servidor Ubuntu 16.04 - MAESTRO - Permitiendo Lectura y Escritura - IP: 10.0.15.10
1 servidor Ubuntu 16.04 - ESCLAVO - Solo Lectura - IP: 10.0.15.11
Privilegios de Root

Paso 1 - Instalar PostgreSQL 9.6

En este tutorial, instalaremos la última versión de PostgreSQL 9.6. En el repositorio oficial de Ubuntu, solo proporcionan PostgreSQL 9.5, por lo que necesitamos instalar la última versión directamente desde el repositorio de PostgreSQL.

Agregue el repositorio de postgreSQL 9.6 al directorio sources.list.d.

echo 'deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main' | tee /etc/apt/sources.list.d/postgresql.list

Y importe la clave de firma de PostgreSQL al sistema.

wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | sudo apt-key add -

Ahora actualice el repositorio del sistema con el comando apt.

apt-get update

A continuación, instale el paquete PosgreSQL 9.6 con el siguiente comando apt.

apt-get install -y postgresql-9.6 postgresql-contrib-9.6

Si la instalación se ha completado, agréguelo para que se inicie automáticamente al arrancar.

systemctl enable postgresql

Por defecto, PostgreSQL se ejecuta en la dirección IP localhost (127.0.0.1) con el puerto 5432 en Ubuntu, verifíquelo con el comando netstat.

netstat -plntu

Verificar si postgres está en ejecución

PostgreSQL 9.6 está en ejecución en el sistema. En el siguiente paso, debemos configurar una contraseña para el usuario postgres.

Desde la cuenta root, inicie sesión en el usuario postgres con el comando su, luego acceda al terminal frontal de postgres psql.

su - postgres  
psql

Por favor, cambie la contraseña para el usuario postgres y verifique la información de conexión con las consultas postgres a continuación.

\password postgres  
\conninfo

Cambiar contraseña de postgres

PostgreSQL 9.6 se ha instalado en el sistema, está funcionando sin errores y la contraseña para el usuario postgres ha sido actualizada.

Paso 2 - Configurar el cortafuegos UFW

UFW o Cortafuegos Sin Complicaciones es una aplicación para gestionar el cortafuegos basado en iptables en Ubuntu. UFW es la herramienta de configuración de cortafuegos predeterminada para Ubuntu Linux y proporciona una forma fácil de configurar el cortafuegos.

Podemos instalar ufw desde el repositorio principal de Ubuntu con el comando apt.

apt-get install -y ufw

Agregue nuevos servicios al cortafuegos UFW: agregue los servicios SSH y PostgreSQL con los siguientes comandos.

ufw allow ssh  
ufw allow postgresql

Habilite el cortafuegos UFW y verifique el estado.

ufw enable  
ufw status

Instalar y verificar el cortafuegos UFW

El cortafuegos UFW ha sido instalado y el servicio PostgreSQL ha sido agregado.

NOTA:

Ejecute el paso 1 y el paso 2 en el servidor MAESTRO y en el servidor ESCLAVO.

Paso 3 - Configurar el servidor PostgreSQL MAESTRO

El servidor maestro tiene la dirección IP 10.0.15.10, y el servicio postgres se ejecutará bajo esa IP con el puerto predeterminado. El servidor maestro tendrá permiso para la LECTURA y ESCRITURA en la base de datos, y realizará la replicación en streaming al servidor esclavo.

Vaya al directorio de configuración de postgres ‘/etc/postgresql/9.6/main‘ y edite el archivo postgresql.conf con vim.

cd /etc/postgresql/9.6/main/  
vim postgresql.conf

Descomente la línea ‘listen_addresses’ y cambie el valor a la dirección IP del servidor maestro ‘10.0.15.10‘.

listen_addresses = '10.0.15.10'

Descomente la línea ‘wal_level’ y cambie el valor a ‘hot_standby‘.

wal_level = hot_standby

Para el nivel de sincronización, utilizaremos la sincronización local. Descomente y cambie el valor de la línea como se muestra a continuación.

synchronous_commit = local

Habilite el modo de archivo y cambie la opción archive_command al comando ‘cp %p /var/lib/postgresql/9.6/main/archive/%f’.

archive_mode = on  
archive_command = 'cp %p /var/lib/postgresql/9.6/main/archive/%f'

Para la configuración de ‘Replicación’, en este tutorial usamos solo 2 servidores, maestro y esclavo, descomente la línea ‘wal_sender‘ y cambie el valor a 2, y para el valor ‘wal_keep_segments‘ es 10.

max_wal_senders = 2  
wal_keep_segments = 10

Para el nombre de la aplicación, descomente la línea ‘synchronous_standby_names’ y cambie el valor al nombre ‘pgslave001‘.

synchronous_standby_names = 'pgslave001'

Guarde el archivo y salga del editor.

En el archivo postgresql.conf, el modo de archivo está habilitado, por lo que necesitamos crear un nuevo directorio para el archivo. Cree un nuevo directorio de archivo, cambie los permisos y cambie el propietario al usuario postgres.

mkdir -p /var/lib/postgresql/9.6/main/archive/  
chmod 700 /var/lib/postgresql/9.6/main/archive/  
chown -R postgres:postgres /var/lib/postgresql/9.6/main/archive/

A continuación, edite el archivo pg_hba.conf para la configuración de autenticación.

vim pg_hba.conf

Pegue la configuración a continuación al final de la línea.

# Localhost  
host    replication     replica          127.0.0.1/32            md5  
   
# Dirección IP del Maestro de PostgreSQL  
host    replication     replica          10.0.15.10/32            md5  
   
# Dirección IP del Esclavo de PostgreSQL  
host    replication     replica          10.0.15.11/32            md5

Guarde y salga, luego reinicie PostgreSQL.

systemctl restart postgresql

PostgreSQL está ejecutándose bajo la dirección IP 10.0.15.10, verifíquelo con el comando netstat.

netstat -plntu

Configurar replicación de Postgres

A continuación, cree un nuevo usuario para la replicación. Crearemos un nuevo usuario llamado ‘replica‘ con la contraseña ‘aqwe123@‘. ¡Por favor, elija una contraseña segura aquí para su configuración! Inicie sesión en el usuario postgres y acceda al terminal frontal de postgres psql.

su - postgres  
psql

Cree un nuevo usuario ‘replica‘ con la contraseña ‘aqwe123@‘ con la consulta postgres a continuación.

CREATE USER replica REPLICATION LOGIN ENCRYPTED PASSWORD 'aqwe123@';

Ahora verifique el nuevo usuario con la consulta ‘du‘ a continuación, y verá el usuario replica con privilegios de replicación.

\du

Crear usuario de replicación de postgres

La configuración del servidor MAESTRO ha sido completada.

Paso 4 - Configurar el servidor ESCLAVO

El servidor ESCLAVO tiene la dirección IP 10.0.15.11. Y este servidor solo tendrá permiso de LECTURA en la base de datos. El servidor de bases de datos Postgres se ejecutará bajo la dirección IP del servidor, no una IP localhost.

Detenga el servicio postgres en el servidor esclavo con el siguiente comando systemctl.

systemctl stop postgresql

Vaya al directorio de configuración de Postgres ‘/etc/postgresql/9.6/main‘, luego edite el archivo de configuración ‘postgresql.conf‘.

cd /etc/postgresql/9.6/main/  
vim postgresql.conf

Descomente la línea listen_addresses y reemplace el valor con la dirección IP del servidor esclavo 10.0.15.11.

listen_addresses = '10.0.15.11'

Descomente la línea ‘wal_level’ y cambie el valor a ‘hot_standby‘.

wal_level = hot_standby

Para el nivel de sincronización, descomente la línea synchronous_commit y cambie el valor como se muestra a continuación.

synchronous_commit = local

Para la configuración de replicación, descomente la línea max_wal_senders y reemplace el valor con ‘2‘ porque solo usamos 2 servidores. Y para wal_keep_segments, cambie el valor a ‘10‘.

max_wal_senders = 2  
wal_keep_segments = 10

Descomente la línea ‘synchronous_standby_names’ para el nombre de la aplicación y cambie el valor al nombre ‘pgslave001‘.

synchronous_standby_names = 'pgslave001'

Habilite hot_standby para el servidor esclavo descomentando la siguiente línea y cambie el valor a ‘on‘.

hot_standby = on

Guarde el archivo y salga del editor.

Paso 5 - Copiar datos de PostgreSQL del MAESTRO al ESCLAVO

A continuación, queremos reemplazar el directorio principal de postgres en el servidor ‘ESCLAVO‘ con el directorio principal de datos del servidor ‘MAESTRO‘.

Inicie sesión en el servidor ESCLAVO y acceda al usuario postgres.

su - postgres

Vaya al directorio de datos de postgres ‘main‘ y respáldelo renombrando el nombre del directorio.

cd 9.6/  
mv main main-bekup

Cree un nuevo directorio ‘main‘ como usuario ‘postgres‘ y asegúrese de que tenga permisos como el directorio main-bekup.

mkdir main/  
chmod 700 main/

A continuación, copie el directorio principal del servidor MAESTRO al servidor ESCLAVO con el comando pg_basebackup, utilizaremos el usuario replica para realizar esta copia de datos.

pg_basebackup -h 10.0.15.10 -U replica -D /var/lib/postgresql/9.6/main -P --xlog  
Password:

Cuando la transferencia de datos esté completa, vaya al directorio principal de datos y cree un nuevo archivo recovery.conf.

cd /var/lib/postgresql/9.6/main/  
vim recovery.conf

Pegue la configuración a continuación:

standby_mode = 'on'  
primary_conninfo = 'host=10.0.15.10 port=5432 user=replica password=aqwe123@ application_name=pgslave001'  
restore_command = 'cp /var/lib/postgresql/9.6/main/archive/%f %p'  
trigger_file = '/tmp/postgresql.trigger.5432'

Guarde y salga, luego cambie los permisos del archivo a 600 con chmod.

chmod 600 recovery.conf

configurar el servidor esclavo de postgres

Ahora inicie PostgreSQL 9.6 en el servidor ESCLAVO y asegúrese de que el servicio postgres esté en ejecución en la dirección IP 10.0.15.11 con netstat.

systemctl start postgresql  
netstat -plntu

La transferencia de datos y la configuración para el servidor ESCLAVO se han completado.

Paso 6 - Pruebas

Para probar, verificaremos el estado de replicación de PostgreSQL 9.6 y trataremos de crear una nueva tabla en el servidor MAESTRO, luego verificaremos la replicación revisando todos los datos del servidor ESCLAVO.

Inicie sesión en el servidor MAESTRO e inicie sesión en el usuario postgres.

su - postgres

Ejecute los comandos psql a continuación para ver el estado de replicación.

psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"  
psql -x -c "select * from pg_stat_replication;"

Obtendrá los resultados como a continuación.

Verificar estado de replicación de postgres

A continuación, intente crear una nueva tabla desde el servidor MAESTRO. Inicie sesión en el usuario postgres en el servidor maestro.

su - postgres  
psql

Y cree una nueva tabla llamada ‘replica_test‘ e inserte datos en la tabla ejecutando las consultas postgres a continuación.

CREATE TABLE replica_test (hakase varchar(100));  
INSERT INTO replica_test VALUES ('howtoforge.com');  
INSERT INTO replica_test VALUES ('Esto es del Maestro');  
INSERT INTO replica_test VALUES ('replicación pg por hakase-labs');

Agregar algunos datos de prueba

A continuación, inicie sesión en el usuario postgres en el servidor ESCLAVO y acceda al terminal psql.

su - postgres  
psql

Verifique los datos en la tabla ‘replica_test‘ con la consulta postgres a continuación.

select * from replica_test;

Y obtendrá los mismos datos que en el MAESTRO, se ha replicado desde el servidor MAESTRO al servidor ESCLAVO.

Verificar replicación de datos

Prueba adicional:

Pruebe escribir en el servidor ESCLAVO con la consulta a continuación.

INSERT INTO replica_test VALUES ('esto es ESCLAVO');

Y obtendrá un mensaje de error sobre ‘ No se puede ejecutar la consulta INSERT ‘ en el servidor ESCLAVO.

La instalación y configuración de PostgreSQL 9.6 con replicación Maestro-Esclavo en Ubuntu 16.04 Xenial Xerus ha sido exitosa.

Referencia

https://www.howtoforge.com/tutorial/postgresql-replication-on-ubuntu-15-04/