Esta pagina se ve mejor con JavaScript habilitado

PostgreSQL replicación

 ·  🎃 kr0m

El artículo se divide en las siguientes secciones:


Consideraciones a tener en cuenta:

Los servidores implicados son los siguientes:

PostgreSQL00: 192.168.69.22
PostgreSQL01: 192.168.69.23

Si estamos instalándolos sobre un sistema basado en jails como es mi caso con Bastille, PostgreSQL dará un error sobre sysvipc:

2023-03-02 23:51:17.310 CET [63048] DETAIL:  Failed system call was shmget(key=65282, size=56, 03600).

Permitimos el acceso por jail, también se puede permitir de forma global en el host padre para todas las jails pero es preferible ser mas granular:

bastille config PostgreSQL00 set sysvmsg=new
bastille config PostgreSQL00 set sysvsem=new
bastille config PostgreSQL00 set sysvshm=new

bastille stop PostgreSQL00
bastille start PostgreSQL00
bastille config PostgreSQL01 set sysvmsg=new
bastille config PostgreSQL01 set sysvsem=new
bastille config PostgreSQL01 set sysvshm=new
bastille stop PostgreSQL01
bastille start PostgreSQL01

NOTA: No utilicéis allow.sysvipc ya que se considera deprecated y otorga mas permisos de los estrictamente necesarios.


Instalación PostgreSQL:

Ahora ya podemos instalar PostgreSQL sin problemas:

pkg install postgresql15-server

Inicializamos la base de datos:

/usr/local/etc/rc.d/postgresql initdb

Habilitamos y arrancamos el servicio:

sysrc postgresql_enable=yes
service postgresql start

Creamos en el MASTER el usuario de replicación:

su postgres -c psql

postgres=# CREATE USER replication REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'POSTGRE_REP_PASS';
postgres=# \du
                                    List of roles
  Role name  |                         Attributes                         | Member of 
-------------+------------------------------------------------------------+-----------
 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 replication | Replication                                               +| {}
             | 1 connection                                               | 

postgres=# ALTER ROLE replication CONNECTION LIMIT -1;

Permitimos el acceso al usuario de replicación:

vi /var/db/postgres/data15/pg_hba.conf

```
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    replication     replication     SLAVE_IP/0              md5
```
```
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    replication     replication     MASTER_IP/0             md5
```

Reiniciamos el servicio:

service postgresql restart


Enganchar el SLAVE:

Eliminamos cualquier dato previo que pueda tener el SLAVE:

service postgresql stop
rm -rfv /var/db/postgres/data15/*

Cargamos el backup en el SLAVE:

su postgres
pg_basebackup -h MASTER_IP -U replication -p 5432 -D /var/db/postgres/data15/ -Fp -Xs -P -R
POSTGRE_REP_PASS

Arrancamos el servicio:

service postgresql start


Comprobaciones:

Comprobamos que funcione creando una base de datos en el MASTER y viendo como se replica en el SLAVE.

su postgres -c psql

postgres=# CREATE DATABASE testkr0m;
postgres=# \l
                                             List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  | ICU Locale | Locale Provider |   Access privileges   
-----------+----------+----------+---------+---------+------------+-----------------+-----------------------
 testkr0m  | postgres | UTF8     | C       | C.UTF-8 |            | libc            | 

En el MASTER podemos ver los SLAVEs conectados:

postgres=# SELECT * FROM pg_stat_replication;
  pid  | usesysid |   usename   | application_name |  client_addr  | client_hostname | client_port |         backend_start         | backend_xmin |   state   | sent_lsn  | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state |          reply_time           
-------+----------+-------------+------------------+---------------+-----------------+-------------+-------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----------+-----------+------------+---------------+------------+-------------------------------
 61745 |    16395 | replication | walreceiver      | 192.168.69.23 |                 |       37560 | 2023-03-02 23:57:42.344823+01 |              | streaming | 0/4434EF0 | 0/4434EF0 | 0/4434EF0 | 0/4434EF0  |           |           |            |             0 | async      | 2023-03-02 23:47:54.524335+01
(1 row)

postgres=# select * from pg_stat_wal_receiver;
 pid | status | receive_start_lsn | receive_start_tli | written_lsn | flushed_lsn | received_tli | last_msg_send_time | last_msg_receipt_time | latest_end_lsn | latest_end_time | slot_name | sender_host | sender_port | conninfo 
-----+--------+-------------------+-------------------+-------------+-------------+--------------+--------------------+-----------------------+----------------+-----------------+-----------+-------------+-------------+----------
(0 rows)

En los SLAVEs podemos ver de donde lee los datos:

postgres=# select * from pg_stat_replication;
 pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time 
-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------+-----------+------------+-----------+-----------+------------+---------------+------------+------------
(0 rows)

postgres=# select * from pg_stat_wal_receiver;
  pid  |  status   | receive_start_lsn | receive_start_tli | written_lsn | flushed_lsn | received_tli |      last_msg_send_time       |     last_msg_receipt_time     | latest_end_lsn |        latest_end_time        | slot_name |  sender_host  | sender_port |                                                                                                                                         conninfo                                                                                                                                          
-------+-----------+-------------------+-------------------+-------------+-------------+--------------+-------------------------------+-------------------------------+----------------+-------------------------------+-----------+---------------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 61515 | streaming | 0/4000000         |                 1 | 0/4434EF0   | 0/4434EF0   |            1 | 2023-03-02 23:48:04.486251+01 | 2023-03-02 23:48:04.486281+01 | 0/4434EF0      | 2023-03-02 23:02:59.470311+01 |           | 192.168.69.22 |        5432 | user=replication password=******** channel_binding=prefer dbname=replication host=192.168.69.22 port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any
(1 row)

NOTA: Si el servidor está en modo standalone, ninguna de las dos querys retorna datos


Promocionar SLAVE:

Por defecto un SLAVE está en modo RO(hot_standby), si el MASTER llega a tener algún problema debemos promocionar el SLAVE y pasará a modo RW.

Comprobamos que sea un SLAVE:

su postgres -c psql

postgres=# SELECT pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 t
(1 row)

Lo promocionamos:

postgres=# SELECT pg_promote();
 pg_promote 
------------
 t
(1 row)

Comprobamos que se haya promocionado:

postgres=# SELECT pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 f
(1 row)

Ahora si intentamos escribir en el SLAVE promocionado, nos lo permitirá.

Si te ha gustado el artículo puedes invitarme a un RedBull aquí