Cluster PostgreSQL 13

En esta ocasión veremos como instalar y configurar un cluster en alta disponibilidad de base de datos bajo PostgreSQL 13.

n primer lugar agregamos el repositorio 

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/postgresql-pgdg.list > /dev/null

Instalamos el software requerido

apt install postgresql-13 postgresql-13-repmgr postgresql-contrib-13 postgresql-plperl-13 postgresql-plpython3-13

En mi caso, voy a mover el data_directory a otro path, para ello paramos el servicio

systemctl stop postgresql

Copiamos a la nueva unidad el directorio postgresql

mkdir -p /psql/postgresql/13/main/

cp -Rf /var/lib/postgresql /psql

Damos permisos al usuario postgres

chown postgres:postgres -R /psql/

Modificamos el parámetro data_directory en el fichero de configuración de postgres (/etc/postgresql/13/main/postgresql.conf)

data_directory = '/psql/postgresql/13/main'

A continuación vamos a configurar en el fichero /etc/sudoers para que el usuario postgres tenga permisos para poder gestionar el servicio de postgres

Cmnd_Alias POSTGRESQL = /usr/bin/systemctl status postgresql.service, /usr/bin/systemctl start postgresql.service, /usr/bin/systemctl restart postgresql.service, /usr/bin/systemctl reload postgresql.service, /usr/bin/systemctl stop postgresql.service, /usr/bin/systemctl status repmgrd.service, /usr/bin/systemctl start repmgrd.service, /usr/bin/systemctl restart repmgrd.service, /usr/bin/systemctl reload repmgrd.service, /usr/bin/systemctl stop repmgrd.service


postgres ALL= NOPASSWD: POSTGRESQL

Iniciamos el servicio de postgres


sudo /usr/bin/systemctl start postgresql.service

Creamos la base de datos para repmgr

CREATE USER repmgr WITH REPLICATION password 'p4sSwo0ord.';
CREATE DATABASE repmgr OWNER repmgr;
ALTER USER repmgr WITH SUPERUSER;

Comprobamos que wal_level, max_weal_senders y hot_standby están configurada de la siguiente forma:

psql -c "show wal_level;"
 wal_level
-----------
 replica
(1 fila)

 psql -c "show max_wal_senders;"
 max_wal_senders
-----------------
 10
(1 fila)

psql -c "show hot_standby;"
 hot_standby
-------------
 on

Creamos un directorio en donde se van a guardar los archives logs

mkdir /psql/postgresql/archivelog

Configuramos en /etc/postgresql/13/main/postgresql.conf los siguientes parametros

listen_addresses = '*'
archive_mode = on              
archive_command = 'test ! -f /psql/postgresql/archivelog/%f && cp %p /psql/postgresql/archivelog/%f '
listen_addresses = '*'
archive_mode = on              
archive_command = 'test ! -f /psql/postgresql/archivelog/%f && cp %p /psql/postgresql/archivelog/%f '

Configuramos la replicación en: /etc/postgresql/13/main/pg_hba.conf

# Allow replication connections from localhost, by a user with the
# replication privilege.
local   repmgr          all                                     trust

host    replication     repmgr          192.168.1.9/32             trust
host    replication     repmgr          192.168.1.10/32            trust
host    replication     repmgr          192.168.1.11/32            trust
host    replication     repmgr          192.168.1.12/32        trust

host    repmgr          repmgr          192.168.1.9/32             trust
host    repmgr          repmgr          192.168.1.10/32            trust
host    repmgr          repmgr          182.168.1.11/32            trust
host    repmgr          repmgr          192.168.1.12/32        trust

Reiniciamos el servicio

systemctl restart postgresql.service

Comprobamos que desde cualquier servidor nos conectamos a la base de datos

psql -h 192.168.1.12 -d repmgr -U repmgr
psql (13.5 (Ubuntu 13.5-2.pgdg20.04+1))
Conexión SSL (protocolo: TLSv1.3, cifrado: TLS_AES_256_GCM_SHA384, bits: 256, compresión: desactivado)
Digite «help» para obtener ayuda.

repmgr=>

Hacemos un clon del nodo primario (esto lo tenemos que repetir en todos los nodos standby)

pg_basebackup -h 192.168.1.12 -U repmgr -p 5432 -D basebackup -Fp -Xs -P -R
Contraseña:
32101/32101 kB (100%), 1/1 tablespace

Copiamos la configuración

rsync -a basebackup/ postgres@10.3.1.9:/psql/postgresql/13/main
rsync -a basebackup/ postgres@10.3.1.10:/psql/postgresql/13/main
rsync -a basebackup/ postgres@10.3.1.11:/psql/postgresql/13/main

Iniciamos el servicio en los nodos standby

systemctl start postgresql.service

Comprobamos que incia correctamente en: /var/log/postgresql/postgresql-13-main.log

2021-12-20 16:40:29.500 UTC [3502] LOG:  iniciando PostgreSQL 13.5 (Ubuntu 13.5-2.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
2021-12-20 16:40:29.501 UTC [3502] LOG:  escuchando en la dirección IPv4 «0.0.0.0», port 5432
2021-12-20 16:40:29.501 UTC [3502] LOG:  escuchando en la dirección IPv6 «::», port 5432
2021-12-20 16:40:29.506 UTC [3502] LOG:  escuchando en el socket Unix «/var/run/postgresql/.s.PGSQL.5432»
2021-12-20 16:40:29.519 UTC [3503] LOG:  el sistema de bases de datos fue apagado durante la recuperación en 2021-12-20 16:40:25 UTC
2021-12-20 16:40:29.519 UTC [3503] LOG:  entrando al modo standby
2021-12-20 16:40:29.540 UTC [3503] LOG:  redo comienza en 0/D000028
2021-12-20 16:40:29.544 UTC [3503] LOG:  el estado de recuperación consistente fue alcanzado en 0/D000138
2021-12-20 16:40:29.544 UTC [3502] LOG:  el sistema de bases de datos está listo para aceptar conexiones de sólo lectura
2021-12-20 16:40:29.558 UTC [3507] LOG:  iniciando el flujo de WAL desde el primario en 0/E000000 en el timeline 1

Nos conectamos a la base de datos y vemos si se replico la base de datos repmgr

postgres=# \l
                                Listado de base de datos
  Nombre   |  Dueño   | Codificación |   Collate   |    Ctype    |      Privilegios
-----------+----------+--------------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8         | es_ES.UTF-8 | es_ES.UTF-8 |
 repmgr    | repmgr   | 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
(4 filas)

Nos conectamos a la base de datos y comprobamos que no tenemos ninguna tabla

postgres=# \c repmgr
Ahora está conectado a la base de datos «repmgr» con el usuario «postgres».

repmgr=# \dt+
No se encontró ninguna relación.

Nos vamos al nodo primary, nos conectamos a la base de datos repmgr y creamos una tabla.

repmgr=# create table test (int serial);
CREATE TABLE
repmgr=# insert into test (select generate_series(1,1000));
INSERT 0 1000

Comprobamos en los nodos standby si replico correctamente la tabla.

repmgr=# \dt+
                           Listado de relaciones
 Esquema | Nombre | Tipo  |  Dueño   | Persistencia | Tamaño  | Descripción
---------+--------+-------+----------+--------------+---------+-------------
 public  | test   | tabla | postgres | permanente   | 64 kB   |
(1 filas) 

A continuación vamos a configurar repmgr, para ello tenemos que activarlo en /etc/postgresql/13/main/postgresql.conf

shared_preload_libraries = 'repmgr'  # (change requires restart)

Nos descargamos la configuración de repmgr

wget https://raw.githubusercontent.com/EnterpriseDB/repmgr/master/repmgr.conf.sample -O /etc/postgresql/13/main/repmgr.conf

Configuramos los siguientes parametros en repmgr.conf

node_id=1 
node_name='srvpsql01'
conninfo='host=192.168.1.12 port=5432 dbname=repmgr user=repmgr'
data_directory='/psql/postgresql/13/main'
data_directory='/psql/postgresql/13/main'              
use_replication_slots=yes
log_level='INFO'
log_facility='STDERR'
log_file='/var/log/postgresql/repmgr.log'             
log_status_interval=60
ssh_options='-q -o ConnectTimeout=10'
primary_follow_timeout=10
failover='automatic'
priority=100
promote_command='repmgr standby promote -f /etc/postgresql/13/main/repmgr.conf --log-to-file --siblings-follow'
follow_command='repmgr standby follow -f /etc/postgresql/13/main/repmgr.conf --log-to-file --siblings-follow --upstream-node-id=1'
monitoring_history=yes        
monitor_interval_secs=2
standby_disconnect_on_failover=yes
service_start_command = 'sudo  /usr/bin/systemctl start postgresql.service'
service_stop_command = 'sudo /usr/bin/systemctl stop postgresql.service'
service_restart_command = 'sudo /usr/bin/systemctl restart postgresql.service'
service_reload_command = 'sudo /usr/bin/systemctl reload postgresql.service'
service_promote_command='/usr/lib/postgresql/13/bin/pg_ctl -w -D /psql/postgresql/13/main promote'
 

Copiamos este fichero a los nodos de standby

scp repmgr.conf  postgres@192.168.1.9:/psql/postgresql/13/main/repmgr.conf                                                                                                                                                                                100%   21KB 260.1KB/s   00:00
scp repmgr.conf  postgres@192.168.1.10:/psql/postgresql/13/main/repmgr.conf                                                                                                                                                                                          100%   21KB 264.7KB/s   00:00
scp repmgr.conf  postgres@192.168.1.11:/psql/postgresql/13/main/repmgr.conf

En este fichero de configuración tenemos que modificar los siguientes parámetros: node_id, node_name, priority y conninfo. De la siguiente manera:



node_id=2 
node_name='srvpsql02'
conninfo='host=192.168.1.9 port=5432 dbname=repmgr user=repmgr'
priority=80


«follow_command» agregamos que el nodo primary sea el node_id=1


follow_command='repmgr standby follow -f /etc/postgresql/13/main/repmgr.conf --log-to-file -W --upstream-node-id=1' 

Paramos el servicio en el nodo primary

systemctl stop postgresql.service

Lo registramos con el rol de primary

repmgr -f /etc/postgresql/13/main/repmgr.conf master register
INFO: connecting to primary database...
NOTICE: attempting to install extension "repmgr"
NOTICE: "repmgr" extension successfully installed
NOTICE: primary node record (ID: 1) registered

Nos conectamos a la base de datos repmgr

psql -d repmgr -U repmgr
psql (13.3 (Ubuntu 13.3-1.pgdg20.04+1), servidor 13.5 (Ubuntu 13.5-2.pgdg20.04+1))
Digite «help» para obtener ayuda.

repmgr=# \dt
              Listado de relaciones
 Esquema |       Nombre       | Tipo  |  Dueño
---------+--------------------+-------+----------
 public  | test               | tabla | postgres
 repmgr  | events             | tabla | repmgr
 repmgr  | monitoring_history | tabla | repmgr
 repmgr  | nodes              | tabla | repmgr
 repmgr  | voting_term        | tabla | repmgr
(5 filas)

Comprobamos la tabla eventos

repmgr=# select * from events;
 node_id |      event       | successful |        event_timestamp        | details
---------+------------------+------------+-------------------------------+---------
       1 | cluster_created  | t          | 2021-12-21 16:00:47.564375+00 |
       1 | primary_register | t          | 2021-12-21 16:00:47.56597+00  |

De la misma forma comprobamos los nodos que tenemos registrados (que como podemos ver es el maestro que acabamos de registrar)

 select * from nodes;
 node_id | upstream_node_id | active | node_name |  type   | location | priority |                                                 conninfo                                                  | repluser | slot_name |             config_file
---------+------------------+--------+-----------+---------+----------+----------+-----------------------------------------------------------------------------------------------------------+----------+-----------+-------------------------------------
       1 |                  | t      | srvpsql01      | primary | default  |      100 | user=repmgr dbname=repmgr host=192.168.1.12 port=5432 connect_timeout=2 fallback_application_name=repmgr | repmgr   |           | /etc/postgresql/13/main/repmgr.conf
(1 fila)

Clonamos la base de datos del nodo primary a los standby. (Este comando lo tenemos que ejecutar en cada uno de los standby)

repmgr -h 192.168.1.12 -U repmgr -d repmgr -f /etc/postgresql/13/main/repmgr.conf standby clone -F

Iniciamos el servicio de postgres

systemctl start postgres.service

Registramos el servidor como stanby

repmgr -f /etc/postgresql/13/main/repmgr.conf standby register

Comprobamos si se han agregado los nodos al cluster

repmgr=# \x
Se ha activado el despliegue expandido.

repmgr=# select * from nodes;
-[ RECORD 1 ]----+----------------------------------------------------------------------------------------------------------
node_id          | 1
upstream_node_id |
active           | t
node_name        | srvpsl01
type             | primary
location         | default
priority         | 100
conninfo         | user=repmgr dbname=repmgr host=192.168.1.12 port=5432 connect_timeout=2 fallback_application_name=repmgr
repluser         | repmgr
slot_name        |
config_file      | /etc/postgresql/13/main/repmgr.conf
-[ RECORD 2 ]----+----------------------------------------------------------------------------------------------------------
node_id          | 2
upstream_node_id | 1
active           | t
node_name        | srvplsql02
type             | standby
location         | default
priority         | 100
conninfo         | user=repmgr dbname=repmgr host=192.168.1.9 port=5432 connect_timeout=2 fallback_application_name=repmgr
repluser         | repmgr
slot_name        |
config_file      | /etc/postgresql/13/main/repmgr.conf
-[ RECORD 3 ]----+----------------------------------------------------------------------------------------------------------
node_id          | 3
upstream_node_id | 1
active           | t
node_name        | srvpsql03
type             | standby
location         | default
priority         | 100
conninfo         | user=repmgr dbname=repmgr host=192.168.1.10 port=5432 connect_timeout=2 fallback_application_name=repmgr
repluser         | repmgr
slot_name        |
config_file      | /etc/postgresql/13/main/repmgr.conf
-[ RECORD 4 ]----+----------------------------------------------------------------------------------------------------------
node_id          | 4
upstream_node_id | 1
active           | t
node_name        | srvpsql04
type             | standby
location         | default
priority         | 100
conninfo         | user=repmgr dbname=repmgr host=192.168.1.11 port=5432 connect_timeout=2 fallback_application_name=repmgr
repluser         | repmgr
slot_name        |
config_file      | /etc/postgresql/13/main/repmgr.conf

Paramos el servicio en el nodo primario

systemctl stop  postgresql.service

Comprobamos que a balanceado correctamente.

repmgr -f /etc/postgresql/13/main/repmgr.conf cluster show
 ID | Name        | Role    | Status    | Upstream    | Location | Priority | Timeline | Connection string
----+-------------+---------+-----------+-------------+----------+----------+----------+-----------------------------------------------------------------------------------------------------------
 1  | srvpsl01 | primary | - failed  | ?        | default  | 100      |          | user=repmgr dbname=repmgr host=192.168.1.12 port=5432 connect_timeout=2 fallback_application_name=repmgr
 2  | srvpsl02 | primary | * running |          | default  | 80      | 4        | host=192.168.1.9 port=5432 dbname=repmgr user=repmgr
 3  | srvpsl03 | standby |   running | srvpsl02 | default  | 100      | 4        | user=repmgr dbname=repmgr host=192.168.1.10 port=5432 connect_timeout=2 fallback_application_name=repmgr
 4  | srvpsl04 | standby |   running | srvpsl02 | default  | 100      | 4        | user=repmgr dbname=repmgr host=192.168.1.11 port=5432 connect_timeout=2 fallback_application_name=repmgr

WARNING: following issues were detected
  - unable to connect to node "srvpsl01" (ID: 1)

HINT: execute with --verbose option to see connection error messages

Clonamos el nodo que actualmente esta como primario

 repmgr -h 192.168.1.9 -U repmgr -d repmgr -f /etc/postgresql/13/main/repmgr.conf standby clone -F

Una vez terminado el proceso de clonado iniciamo sel servicio

systemctl start postgresql.service

Promocionamos como standby el nodo

repmgr -f /etc/postgresql/13/main/repmgr.conf standby register

En caso que queramos hacer un switchover para volver a promocionar el nodo srvpsl01 como primario ejecutamos

repmgr -f /etc/postgresql/13/main/repmgr.conf standby switchover

De la misma forma, los nodos srvpsql03 y srvpsql04 tenemos que comprobar que tengan a srvpsl01 como primario, en caso de que no lo haga lo forzamos

repmgr standby follow -f /etc/postgresql/13/main/repmgr.conf --log-to-file --siblings-follow --upstream-node-id=1

:wq!

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

*