Advertising:
Upgrade de Zabbix 4.4.5 a 5.0.0 PostgreSQL 11 a 12 e Instalación de TSDB
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