Upgrade de Zabbix 4.4.5 a 5.0.0 PostgreSQL 11 a 12 e Instalación de TSDB

From Zabbix-ES
Jump to navigation Jump to search

Actaulizacion de Zabbix

Stop Zabbix-Proxy processes

# systemctl stop zabbix-proxy

Stop Zabbix-Server processes

# systemctl stop zabbix-server
# systemctl stop zabbix-agent
# systemctl stop httpd

Back up the existing Zabbix database

# mkdir /opt/zabbix-backup/
# cp /etc/zabbix/zabbix_server.conf /opt/zabbix-backup/
# cp /etc/httpd/conf.d/zabbix.conf  /opt/zabbix-backup/
# cp -R /usr/share/zabbix/ /opt/zabbix-backup/
# cp -R /usr/share/doc/zabbix-* /opt/zabbix-backup/

Update repository configuration package (Server y Proxy) (RHEL/CentOS 7)

# rpm -Uvh https://repo.zabbix.com/zabbix/5.0/rhel/7/x86_64/zabbix-release-5.0-1.el7.noarch.rpm

Upgrade Zabbix Server components

# yum clean all
# yum repolist
# yum upgrade zabbix-server-pgsql zabbix-agent

Upgrade Zabbix FrontEnd

# yum remove zabbix-web-*
# yum install centos-release-scl

# vi /etc/yum.repos.d/zabbix.repo
[zabbix-frontend]
enabled=1
# yum install zabbix-apache-conf-scl
# yum install zabbix-web-pgsql-scl

# vi /etc/opt/rh/rh-php72/php-fpm.d/zabbix.conf
php_value[date.timezone] = Europe/Madrid

Start and enable php-fpm service

# systemctl start rh-php72-php-fpm
# systemctl enable rh-php72-php-fpm

Restart Apache

# systemctl restart httpd

Upgrade Zabbix Proxy components (RHEL/CentOS 7)

# rpm -Uvh https://repo.zabbix.com/zabbix/5.0/rhel/7/x86_64/zabbix-release-5.0-1.el7.noarch.rpm
# yum clean all
# yum repolist
# yum upgrade zabbix-proxy-sqlite3  zabbix-agent
# mv /database/sqlite3/zabbix.db /database/sqlite3/zabbix.db.4.2.0
# systemctl start zabbix-proxy
# systemctl start zabbix-agent
# systemctl status zabbix-proxy
# systemctl status zabbix-agent

Parcheamos la DB (PostgreSQL) para solventar el warning “database is not upgraded to use double precision values”

Importante: Zabbix server tiene que estar parado.

# systemctl stop zabbix-server
ALTER TABLE ONLY trends
	ALTER COLUMN value_min TYPE DOUBLE PRECISION,
	ALTER COLUMN value_min SET DEFAULT '0.0000',
	ALTER COLUMN value_avg TYPE DOUBLE PRECISION,
	ALTER COLUMN value_avg SET DEFAULT '0.0000',
	ALTER COLUMN value_max TYPE DOUBLE PRECISION,
	ALTER COLUMN value_max SET DEFAULT '0.0000';
ALTER TABLE ONLY history
	ALTER COLUMN value TYPE DOUBLE PRECISION,
	ALTER COLUMN value SET DEFAULT '0.0000';
# vi /etc/zabbix/web/zabbix.conf.php
$DB['DOUBLE_IEEE754'] = 'true';

Instalacion de PG12

# yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# yum install postgresql12 postgresql12-server
# /usr/pgsql-12/bin/postgresql-12-setup initdb
# systemctl status postgresql-12
# systemctl enable postgresql-12
# systemctl start postgresql-12

Upgrade de PG11 a PG12 via pg_upgrade

Nota: Toda la operatoria la hacemos con el usuario postgres.

PGDATA11 -> /var/lib/pgsql/11
PGDATA12 -> /var/lib/pgsql/12
- Copiamos el original para no perderlo
# cp  /var/lib/pgsql/12/data/postgresql.conf  /var/lib/pgsql/12/data/postgresql.conf.original
# cp  /var/lib/pgsql/12/data/pg_hba.conf      /var/lib/pgsql/12/data/pg_hba.conf.original
- Pasamos los de la version 11 a PGDATA de las 12
# cp -p /var/lib/pgsql/11/data/postgresql.conf /var/lib/pgsql/12/data/postgresql.conf
# cp -p /var/lib/pgsql/11/data/pg_hba.conf /var/lib/pgsql/12/data/pg_hba.conf
- Cambiamos los puertos de la 11 y la 12
# vi /var/lib/pgsql/12/data/postgresql.conf
  # port = 5432
# vi /var/lib/pgsql/11/data/postgresql.conf
  # port = 5433
- Ejecutamos el pg_upgrade
Nota: hay 2 opciones, si es en el mismo FS podemos utilizar la opcion -k que via hardlinks nos evita copiar datos y es casi instantaneo el upgrade.
      Con la opcion --check realiza la comprobacion pero no ejecuta el upgrade.      

# /usr/pgsql-12/bin/pg_upgrade -b /usr/pgsql-11/bin -B /usr/pgsql-12/bin -d /var/lib/pgsql/11/data -D /var/lib/pgsql/12/data -k --check
# /usr/pgsql-12/bin/pg_upgrade -b /usr/pgsql-11/bin -B /usr/pgsql-12/bin -d /var/lib/pgsql/11/data -D /var/lib/pgsql/12/data -k
# ./analyze_new_cluster.sh -> Realiza un Vacumm de las tablas.
# ./delete_old_cluster.sh  -> Elimina la instalacion antigua. Si hacemos un hardlink no perdemos la informacion nueva.

Instalacion de TimeScaleDB

Nota:Ejecutar bajo el usuario root
# tee /etc/yum.repos.d/timescale_timescaledb.repo <<EOL
[timescale_timescaledb]
name=timescale_timescaledb
baseurl=https://packagecloud.io/timescale/timescaledb/el/7/\$basearch
repo_gpgcheck=1
gpgcheck=0
enabled=1
gpgkey=https://packagecloud.io/timescale/timescaledb/gpgkey
sslverify=1
sslcacert=/etc/pki/tls/certs/ca-bundle.crt
metadata_expire=300
EOL
# yum clean all
# yum repolist
# yum update -y
# yum install -y timescaledb-postgresql-12
- Como el script timescaledb_tune modifica el postgres.conf hacemos una copia por si necesitamos algun dato.
Nota:Ejecutar bajo el usuario postgres
$ cp /var/lib/pgsql/12/data/postgresql.conf /var/lib/pgsql/12/data/postgresql.conf.preTSDB
$ cp /var/lib/pgsql/12/data/pg_hba.conf /var/lib/pgsql/12/data/pg_hba.conf.preTSDB
- Ejecutamos el script que tunea nuestro postgres segun los datos de CPU y Memoria que tengamos en el sistema.
$ timescaledb-tune --pg-config=/usr/pgsql-12/bin/pg_config
Using postgresql.conf at this path:/var/lib/pgsql/12/data/postgresql.conf

Is this correct? [(y)es/(n)o]: y
Writing backup to: /tmp/timescaledb_tune.backup202005261520
shared_preload_libraries needs to be updated
Current:
#shared_preload_libraries = 

Recommended:
shared_preload_libraries = 'timescaledb'

Is this okay? [(y)es/(n)o]: y
success: shared_preload_libraries will be updated
Tune memory/parallelism/WAL and other settings? [(y)es/(n)o]: y
Recommendations based on 23.37 GB of available memory and 32 CPUs for PostgreSQL 12

Memory settings recommendations
Current:
shared_buffers = 4GB
#effective_cache_size = 4GB
#maintenance_work_mem = 64MB
work_mem = 512MB

Recommended:
shared_buffers = 5982MB
effective_cache_size = 17946MB
maintenance_work_mem = 2047MB
work_mem = 1914kB

Is this okay? [(y)es/(s)kip/(q)uit]: y
success: memory settings will be updated
Parallelism settings recommendations
Current:
missing: timescaledb.max_background_workers
#max_worker_processes = 8
#max_parallel_workers_per_gather = 2
#max_parallel_workers = 8

Recommended:
timescaledb.max_background_workers = 8
max_worker_processes = 43
max_parallel_workers_per_gather = 16
max_parallel_workers = 32

Is this okay? [(y)es/(s)kip/(q)uit]: y
success: parallelism settings will be updated
WAL settings recommendations
Current:
#wal_buffers = -1
#min_wal_size = 80MB
max_wal_size = 2GB

Recommended:
wal_buffers = 16MB
min_wal_size = 512MB
max_wal_size = 1GB

Is this okay? [(y)es/(s)kip/(q)uit]: y
success: WAL settings will be updated
Miscellaneous settings recommendations
Current:
#default_statistics_target = 100
#random_page_cost = 4.0
#checkpoint_completion_target = 0.5
max_connections = 800
#max_locks_per_transaction = 64
#autovacuum_max_workers = 3
#autovacuum_naptime = 1min
#effective_io_concurrency = 1

Recommended:
default_statistics_target = 500
random_page_cost = 1.1
checkpoint_completion_target = 0.9
max_connections = 100
max_locks_per_transaction = 256
autovacuum_max_workers = 10
autovacuum_naptime = 10
effective_io_concurrency = 200

Is this okay? [(y)es/(s)kip/(q)uit]: y
success: miscellaneous settings will be updated
Saving changes to: /var/lib/pgsql/12/data/postgresql.conf
- Nosotros actualizamos el max_connections ya que sabemos que 100 es muy poco.
$ vi /var/lib/pgsql/12/data/postgresql.conf
  max_connections = 800
Nota:Ejecutar bajo el usuario root
# systemctl restart postgresql-12
- Crear la extension de TSDB
# psql -U postgres -d zabbix -c "CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;"
- Siempre mirar el script que trae zabbix por si hay cambios.
# zcat /usr/share/doc/zabbix-server-pgsql-5.0.0/timescaledb.sql.gz
- Creamos los chunks y tomamos tiempos de ejecucion. Nota: Recomendable ejecutar con screen.
time psql -U postgres -c "SELECT create_hypertable('history_log', 'clock', chunk_time_interval => 86400, migrate_data => true);" zabbix
time psql -U postgres -c "SELECT create_hypertable('history_str', 'clock', chunk_time_interval => 86400, migrate_data => true);" zabbix
time psql -U postgres -c "SELECT create_hypertable('history_text', 'clock', chunk_time_interval => 86400, migrate_data => true);" zabbix
time psql -U postgres -c "SELECT create_hypertable('history', 'clock', chunk_time_interval => 86400, migrate_data => true);" zabbix
time psql -U postgres -c "SELECT create_hypertable('trends', 'clock', chunk_time_interval => 86400, migrate_data => true);" zabbix
time psql -U postgres -c "SELECT create_hypertable('trends_uint', 'clock', chunk_time_interval => 86400, migrate_data => true);" zabbix
time psql -U postgres -c "SELECT create_hypertable('history_uint', 'clock', chunk_time_interval => 86400, migrate_data => true);" zabbix
time psql -U postgres -c "UPDATE config SET db_extension='timescaledb',hk_history_global=1,hk_trends_global=1;" zabbix
time psql -U postgres -c "UPDATE config SET compression_status=1,compress_older='7d';" zabbix
- Tiempos medios para una BD de 200GB
 create_hypertable: history_log  (5s)
 create_hypertable: history_str  (13s)
 create_hypertable: history_text (2m)
 create_hypertable: history      (1h 25m)
 create_hypertable: history_uint (141m)
 create_hypertable: trends       (27m)
 create_hypertable: trends_uint  (56m)
- Verificamos los chunks creados.
psql -U postgres -d zabbix -c "SELECT chunk_table,total_bytes FROM chunk_relation_size('history_log');"
psql -U postgres -d zabbix -c "SELECT chunk_table,total_bytes FROM chunk_relation_size('history_str');"
psql -U postgres -d zabbix -c "SELECT chunk_table,total_bytes FROM chunk_relation_size('history_text');"
psql -U postgres -d zabbix -c "SELECT chunk_table,total_bytes FROM chunk_relation_size('history_uint');"
psql -U postgres -d zabbix -c "SELECT chunk_table,total_bytes FROM chunk_relation_size('history');"
psql -U postgres -d zabbix -c "SELECT chunk_table,total_bytes FROM chunk_relation_size('trends');"
psql -U postgres -d zabbix -c "SELECT chunk_table,total_bytes FROM chunk_relation_size('trends_uint');"

TIPs: Eliminar extensiones antiguas

SELECT * FROM pg_extension;
DROP EXTENSION dblink;
DROP EXTENSION pg_cron;
DROP EXTENSION pg_partman;

Consultas a TSDB

psql -U postgres -d zabbix -c "SELECT * FROM timescaledb_information.compressed_chunk_stats;"
psql -U postgres -d zabbix -c "SELECT * FROM timescaledb_information.hypertable;"
psql -U postgres -d zabbix -c "SELECT * FROM timescaledb_information.hypertable WHERE table_schema='public' AND table_name='metrics';"
psql -U postgres -d zabbix -c "SELECT * FROM timescaledb_information.license;"
psql -U postgres -d zabbix -c "SELECT * FROM timescaledb_information.compressed_hypertable_stats;"

Notas generales (No aplica al Upgrade)

Configurar IP Flotante

- Monta la IP
# vi /var/adm/ssoo/opt/cluster/monta_ip.sh

#!/usr/bin/bash
ip a add 10.xxx.xxx.xxx/16 dev ens192
arping -c 10 -A -I ens192 10.xxx.xxx.xxx

- Desmonta la IP
# vi /var/adm/ssoo/opt/cluster/desmonta_ip.sh

#!/usr/bin/bash
ip a del 10.xxx.xxx.xxx/16 dev ens192

Replicacion de PG con pg_basebackup

# su - postgres
################################################################
#                                                              #
#        To load environment variables:                        #
#                                                              #
# pg11 -> . /postgres/admdba/entorno_bd_11.sh                  #
# pg12 -> . /postgres/admdba/entorno_bd_12.sh                  #
#                                                              #
################################################################

$ pg12
Environment Postgres 12 cargado!
- Creamos el usuario para la replicacion
$ createuser --replication -P -e replica
Enter password for new role: (passw0rd)
Enter it again: (passw0rd)

$ exit

- En host MASTER
$ echo "host replication replica [IP_REMOTA]/32 md5" >> $PGDATA/pg_hba.conf
$ psql -U postgres -c "select pg_reload_conf();"   <-- Releemos el pg_hba.conf
$ pg_basebackup -h [IP_REMOTA] -U replica -D $PGDATA -P -v -R -Xs -c fast -C -S pgstandby1
Password: passw0rd
186737098/186737098 kB (100%), 1/1 tablespace
 - En el Master verificamos la replicacion
$ psql -U postgres -x -c "SELECT * FROM pg_replication_slots;"
$ psql -U postgres -x -c "select * from pg_stat_replication;"

Script de environment /postgres/admdba/entorno_bd_12.sh

$ cat /postgres/admdba/entorno_bd_12.sh
########################
#VARIABLES MODIFICACLES#
########################
export V_DBVERSION=12
###############################
#!!VARIABLES NO MODIFICABLES!!#
###############################
export V_PGSOFT=/usr/pgsql-$V_DBVERSION
export V_ADMIN_HOME=/postgres/admdba
export PGDATA=/var/lib/pgsql/$V_DBVERSION/data
export PATH=$V_PGSOFT/bin:$PATH
export LD_LIBRARY_PATH=$V_PGSOFT/lib

echo "Environment Postgres $V_DBVERSION cargado!"
echo