Cómo configurar la replicación de transmisión en instancias de PostgreSQL

En este artículo, le mostraré la instalación de Postgresql y su configuración de replicación de transmisión de dos instancias de PostgreSQL en diferentes hosts. Nuestro objetivo es instalar Postgres 9.5 en el host maestro y Postgres 9.5 en el host esclavo, y configurar la replicación de transmisión entre ellos.

Instalacion y configuracion

Para empezar, instalaremos y prepararemos PostgreSQL 9.5 en el host maestro que ejecuta CentOS. En primer lugar, debemos usar el comando rpm para realizar una consulta del paquete centos-release para revelar la versión de CentOS:

rpm --query centos-release

En nuestro ejemplo, consideraremos la versión 6.8 de CentOS con arquitectura x86_64. Ahora, necesitamos instalar el paquete rpm para ejecutar el comando yum install:

rpm -i https://yum.postgresql.org/9.5/redhat/rhel-6.8-x86_64/pgdg-redhat95-9.5-3.noarch.rpm

Compruebe que todo esté en el lugar correcto ejecutando:

yum list postgresql95*

Ahora puede instalar PostgreSQL 9.5:

yum install postgresql95-server.x86_64

Se pueden instalar otros paquetes según sus necesidades.

Una vez que se instala el servidor Postgresql-9.5, se debe inicializar y configurar una base de datos. El primer comando (solo se necesita una vez) es inicializar la base de datos en PGDATA.

/etc/init.d/postgresql-9.5 initdb

o

service postgresql-9.5 initdb

Si desea que PostgreSQL se inicie automáticamente cuando se inicie el sistema operativo, haga lo siguiente:

chkconfig postgresql-9.5 on

En este momento está listo para iniciar el servicio, pero antes actualizaremos el archivo /var/lib/pgsql/9.5/data/postgresql.conf. Descomente la línea listen_addresses y escriba su dirección IP junto con localhost.

listen_addresses="your IP, 127.0.0.1"

Luego, modifique el archivo /var/lib/pgsql/9.5/data/pg_hba.conf de la siguiente manera:

# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all your IP md5

Para controlar el servicio de la base de datos, use

service postgresql-9.5 [command]

dónde [command] puede ser:

start – inicia la base de datos
detener – detener la base de datos
reiniciar: detiene / inicia la base de datos. Usualmente se usa para leer cambios en archivos de configuración centrales.
recargar: recarga el archivo pg_hba.conf mientras se mantiene la base de datos en ejecución

Inicie el servicio y terminamos con la instalación de Postgresql 9.5 en el host maestro.

En el siguiente paso, instalaremos Postgresql 9.5 en el host esclavo. Los pasos son prácticamente los mismos. Para empezar, necesitamos instalar el paquete rpm.

Recordatorio: los siguientes pasos deben realizarse en otro host, el denominado host esclavo.

rpm -i https://yum.postgresql.org/9.5/redhat/rhel-6.8-x86_64/pgdg-redhat95-9.5-3.noarch.rpm

e instale Postgresql 9.5

yum install postgresql95-server.x86_64

Entonces inicialízalo

service postgresql-9.5 initdb

Haz que se inicie automáticamente en las startups

chkconfig postgresql-9.5 on

Y modifique el archivo de configuración /var/lib/pgsql/9.5/data/postgresql.conf de la siguiente manera:

listen_addresses="your IP, 127.0.0.1"

A continuación, actualice el archivo /var/lib/pgsql/9.5/data/pg_hba.conf:

# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all your IP md5

Ahora estás listo para iniciar el servicio.

service postgresql-9.5 start

Configuración de replicación

Streaming Replication proporciona la capacidad de enviar y aplicar continuamente los registros de WAL XLOG (Write-Ahead Log) a una cierta cantidad de servidores en espera para mantener la actualización. Esta característica se agregó a PostgresSQL 9.0.
Bien, tenemos dos instancias de PostgreSQL diferentes en diferentes hosts instalados. Ahora necesitamos crear un usuario llamado replicación con privilegios de REPLICACIÓN en el host maestro.

Bien, tenemos dos instancias de PostgreSQL diferentes en diferentes hosts instalados. Ahora necesitamos crear un usuario llamado replicación con privilegios de REPLICACIÓN en el host maestro.

su - postgres
psql
CREATE ROLE replication WITH REPLICATION PASSWORD 'password' LOGIN

Configure las conexiones y la autenticación en el host maestro para que el servidor en espera (host esclavo) pueda conectarse con éxito a la pseudo-base de datos de replicación en el primario. Editar /var/lib/pgsql/9.5/data/pg_hba.conf

host replication replication slave host IP md5

Ahora necesita actualizar el archivo de configuración de maestros /var/lib/pgsql/9.5/data/postgresql.conf de la siguiente manera:

# Add settings for extensions here
max_connections = 200
shared_buffers = 15GB
effective_cache_size = 45GB
work_mem = 39321kB
maintenance_work_mem = 2GB
checkpoint_segments = 32
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100

#logging settings
log_directory = '/var/log/pg_log95'
log_filename="postgresql-%d_%H%M%S.log"
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
log_min_duration_statement = 30000
log_connections = true
tcp_keepalives_idle = 30

# Streaming replication
# To enable read-only queries on a standby server, wal_level must be set to
# "hot_standby". But you can choose "archive" if you never connect to the
# server in standby mode.
wal_level = hot_standby

# Set the maximum number of concurrent connections from the standby servers.
max_wal_senders = 5

# Enable WAL archiving on the primary to an archive directory accessible from
# the standby. If wal_keep_segments is a high enough number to retain the WAL
# segments required for the standby server, this is not necessary.
archive_mode = on
archive_command = 'rsync -a %p postgres@[SLAVE IP]:/db/psql95_wal_archive/%f'

Necesitamos mover el directorio de datos en el host esclavo

mv /var/lib/pgsql/9.5/data /var/lib/pgsql/9.5/data_bkp

Ahora, necesita hacer una copia de seguridad base copiando el directorio de datos del host maestro al host esclavo. Puede hacerlo con el comando pg_basebackup en el host esclavo.

pg_basebackup -h [MASTER IP] -D /var/lib/pgsql/9.5/data -P -U replication --xlog-method=stream

Luego debe modificar /var/lib/pgsql/9.3/data/postgresql.conf en el host esclavo de la siguiente manera

# Add settings for extensions here
max_connections = 200
shared_buffers = 15GB
effective_cache_size = 45GB
work_mem = 39321kB
maintenance_work_mem = 2GB
checkpoint_segments = 32
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100

#logging settings
log_directory = '/var/log/pg_log93'
log_filename="postgresql-%d_%H%M%S.log"
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
log_min_duration_statement = 30000

# Streaming replication
hot_standby = on

Cree un archivo de comando de recuperación en el host esclavo /var/lib/pgsql/9.5/data/recovery.conf. Los siguientes parámetros son necesarios para la replicación de transmisión

# Specifies whether to start the server as a standby. In streaming replication,
# this parameter must to be set to on.
standby_mode="on"

# Specifies a connection string which is used for the standby server to connect
# with the primary.
primary_conninfo = 'host=[MASTER IP] user=replication password=[PASSWORD]'

# Specifies a trigger file whose presence should cause streaming replication to
# end (i.e., failover).
trigger_file="/var/lib/pgsql/9.5/data/trigger_file"

# Specifies a command to load archive segments from the WAL archive. If
# wal_keep_segments is a high enough number to retain the WAL segments
# required for the standby server, this may not be necessary. But
# a large workload can cause segments to be recycled before the standby
# is fully synchronized, requiring you to start again from a new base backup.
restore_command = 'cp /db/psql95_wal_archive/%f "%p"'
archive_cleanup_command = '/usr/pgsql-9.5/bin/pg_archivecleanup /db/psql95_wal_archive/ %r'

Confirme que en ambos hosts el propietario de /var/lib/pgsql/9.5/data y sus subdirectorios es el usuario de postgres. Además, debe crear el directorio / db / psql93_wal_archive / con el propietario de postgres en el host esclavo.
Y el último paso antes de reiniciar las instancias de PostgreSQL es generar la clave RSA en el host esclavo para el usuario de Postgres y copiarla al maestro.

ssh-keygen -t rsa
ssh-copy-id -i ~/.ssh/id_rsa postgres@[MASTER IP]

Ahora, está listo para reiniciar el servicio postgres en el maestro primero y luego en el esclavo.

Conclusión

Hemos configurado la replicación de transmisión que permite que un servidor en espera se mantenga más actualizado de lo que es posible con el envío de registros basado en archivos. El modo de espera se conecta al primario, que transmite los registros WAL al modo de espera a medida que se generan, sin esperar a que se complete el archivo WAL.