Migrate zabbix-postgresql-timescaledb from 17 to 18

Alain De Vos

Alain De Vos

Not for the fainth of harth (SirDice will know this).
A bit incomplete here or redundanded (excuse me)
Code: 
cat doit*
#!/bin/sh
# 1. Maak de private map aan in je eigen directory
mkdir -p ~/pg17_engine

# 2. Haal het package op
pkg fetch -y databases/postgresql17-server

# 3. Pak het GEHELE package uitsluitend uit BINNEN de veilige map
tar -xf /var/cache/pkg/postgresql17-server-17.10.pkg -C ~/pg17_engine/
chown -R postgres:postgres ~/pg17_engine/
su - postgres -c "~/pg17_engine/usr/local/bin/postgres -D /var/db/postgres/data17 -p 5417 &"

su - postgres -c "/root/pg17_engine/usr/local/bin/postgres -D /var/db/postgres/data17 -p 5417 &"
mv /root/pg17_engine /var/db/postgres/pg17_engine
chown -R postgres:postgres /var/db/postgres/pg17_engine

su - postgres -c "/var/db/postgres/pg17_engine/usr/local/bin/postgres -D /var/db/postgres/data17 -p 5417 &"

pg_dump -p 5417 -U postgres -Fc zabbix > ./zabbix_v17.bak
psql -U postgres -c "CREATE DATABASE zabbix OWNER x;"
psql -U postgres -d zabbix -c "ALTER DATABASE zabbix SET timescaledb.restoring = 'on';"

# Forceer het stoppen van alle eventuele hangende postgres-processen
pkill -u postgres

# Verwijder de achtergebleven lock-files uit de data18 map die de start blokkeren
rm -f /var/db/postgres/data18/postmaster.pid
rm -f /tmp/.s.PGSQL.5432*

service postgresql start
service postgresql status
psql -U postgres -c "CREATE ROLE x WITH LOGIN SUPERUSER;"
psql -U postgres -c "CREATE DATABASE zabbix OWNER x;"
psql -U postgres -d zabbix -c "ALTER DATABASE zabbix SET timescaledb.restoring = 'on';"

pg_restore -U postgres -d zabbix -j 4 ./zabbix_v17.bak

psql -U postgres -c "DROP DATABASE IF EXISTS zabbix;"
psql -U postgres -c "CREATE DATABASE zabbix OWNER x;"

psql -U postgres -d zabbix -c "CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;"
psql -U postgres -d zabbix -c "ALTER DATABASE zabbix SET timescaledb.restoring = 'on';"

psql -U postgres -c "DROP DATABASE IF EXISTS zabbix;"
psql -U postgres -c "CREATE DATABASE zabbix OWNER x;"
psql -U postgres -d zabbix -c "CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;"
psql -U postgres -d zabbix -c "ALTER DATABASE zabbix SET timescaledb.restoring = 'on';"

pg_restore -U postgres -d zabbix ./zabbix_v17.bak
pkill -u postgres
rm -f /var/db/postgres/data18/postmaster.pid
rm -f /tmp/.s.PGSQL.5432*
service postgresql start

psql -U postgres -c "DROP DATABASE IF EXISTS zabbix;"
psql -U postgres -c "CREATE DATABASE zabbix OWNER x;"
psql -U postgres -d zabbix -c "CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;"
psql -U postgres -d zabbix -c "ALTER DATABASE zabbix SET timescaledb.restoring = 'on';"

pg_restore -U postgres -d zabbix --data-only --disable-triggers /root/17/zabbix_v17.bak

psql -U postgres -d zabbix -c "ALTER DATABASE zabbix SET timescaledb.restoring = 'off';"
service zabbix_server start

#!/bin/sh

# 1. Knal processen af en ruim locks op
pkill -9 -u postgres
pkill -9 -f zabbix_server
rm -f /var/db/postgres/data18/postmaster.pid
rm -f /tmp/.s.PGSQL.5432*

# 2. Start de server normaal op (shared_preload_libraries='timescaledb' MOET aanstaan in de config)
service postgresql start
sleep 3

# 3. Database handmatig schoon opbouwen
psql -U postgres -c "DROP DATABASE IF EXISTS zabbix;"
psql -U postgres -c "CREATE DATABASE zabbix OWNER x;"

# 4. Activeer de extensie NATIVE in de lege database
psql -U postgres -d zabbix -c "CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;"

# 5. Activeer de OFFICIËLE TimescaleDB pre-restore hook
# (Dit bevriest de bgw_job views en voorkomt de 4 catalogusfouten)
psql -U postgres -d zabbix -c "SELECT timescaledb_pre_restore();"

# 6. Voer de VOLLEDIGE restore uit (Zonder --data-only, single-threaded)
pg_restore -U postgres -d zabbix /tmp/zabbix_v17.bak

# 7. Deactiveer de herstelmodus via de officiële post-restore hook
psql -U postgres -d zabbix -c "SELECT timescaledb_post_restore();"

# 8. Start Zabbix Server
chown -R zabbix:zabbix /var/log/zabbix/
service zabbix_server onestart

#!/bin/sh

# 1. Knal alle processen en locks af
pkill -9 -u postgres
pkill -9 -f zabbix_server
rm -f /var/db/postgres/data18/postmaster.pid
rm -f /tmp/.s.PGSQL.5432*

# 2. Start PostgreSQL 18 met TimescaleDB VOLLEDIG UITGESCHAKELD
# Dit voorkomt dat de engine crasht op de herstelfase
su - postgres -c "/usr/local/bin/pg_ctl -D /var/db/postgres/data18 -o '-c shared_preload_libraries=''' start"
sleep 3

# 3. Maak de database database kaal en leeg aan
psql -U postgres -c "DROP DATABASE IF EXISTS zabbix;"
psql -U postgres -c "CREATE DATABASE zabbix OWNER x;"

# 4. Maak de rollen correct aan met wachtwoord
psql -U postgres -c "CREATE ROLE zabbix WITH LOGIN PASSWORD 'zabbix';" 2>/dev/null
psql -U postgres -c "ALTER ROLE zabbix WITH PASSWORD 'zabbix';" 2>/dev/null

# 5. DE DOORBRAAK: Herstel uitsluitend het 'public' schema (De Zabbix-data)
# We slaan alle _timescaledb schema's over zodat de import 100% foutloos slaagt
pg_restore -U postgres -d zabbix --schema=public /tmp/zabbix_v17.bak

# 6. Stop de tijdelijke kale server
su - postgres -c "/usr/local/bin/pg_ctl -D /var/db/postgres/data18 stop"
sleep 2

# 7. Schrijf 'timescaledb' weer terug in de echte config en start productie
if ! grep -q "shared_preload_libraries = 'timescaledb'" /var/db/postgres/data18/postgresql.conf; then
    echo "shared_preload_libraries = 'timescaledb'" >> /var/db/postgres/data18/postgresql.conf
fi

service postgresql start

# 8. Activeer een schone TimescaleDB extensie op de geïmporteerde data
psql -U postgres -d zabbix -c "CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;"

# 9. Start Zabbix Server
chown -R zabbix:zabbix /var/log/zabbix/
service zabbix_server onestart

psql -U postgres -d zabbix -c "CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;"
psql -U postgres -d zabbix -c "SELECT timescaledb_post_restore();"

chown -R zabbix:zabbix /var/log/zabbix/
service zabbix_server onestart

# Maak 'zabbix' de eigenaar van de database
psql -U postgres -c "ALTER DATABASE zabbix OWNER TO zabbix;"

# Rechten toekennen op het schema zelf
psql -U postgres -d zabbix -c "GRANT ALL ON SCHEMA public TO zabbix;"

# Forceer dat alle bestaande tabellen, sequenties en views toegankelijk zijn voor zabbix
psql -U postgres -d zabbix -c "GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO zabbix;"
psql -U postgres -d zabbix -c "GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO zabbix;"

cat << 'EOF' > /usr/local/www/zabbix74/conf/zabbix.conf.php
<?php
// Zabbix GUI configuration file.
$DB['TYPE']     = 'POSTGRESQL';
$DB['SERVER']   = '127.0.0.1';
$DB['PORT']     = '5432';
$DB['DATABASE'] = 'zabbix';
$DB['USER']     = 'zabbix';
$DB['PASSWORD'] = 'zabbix';
$DB['SCHEMA']   = '';

$ZBX_SERVER      = '127.0.0.1';
$ZBX_SERVER_PORT = '10051';
$ZBX_SERVER_NAME = 'My FreeBSD Zabbix';

$IMAGE_FORMAT_DEFAULT = IMAGE_FORMAT_PNG;
EOF


chown www:www /usr/local/www/zabbix74/conf/zabbix.conf.php
chmod 600 /usr/local/www/zabbix74/conf/zabbix.conf.php

root@myfreebsd:~/17 #
 
Alain De Vos said:
(SirDice will know this)
Click to expand...
It's not that bad, I've done it a couple of times now. It was for PuppetDB though, but that shouldn't really matter.

One thing I can recommend, upgrade to a newer PostgreSQL before the old version disappears from the ports tree (and there are no more packages for it). So plan ahead.
 
I removed 100 zfs graphs in ZABBIX
Code: 
pkg install -y jq
htop -s PERCENT_MEM -d 100
htop -s PERCENT_CPU -d 50
geany zabbix_agentd.conf
su - postgres -c "psql -d zabbix"
zabbix_server -R config_cache_reload
zabbix_server -R housekeeping_execute
zabbix_server -R housekeeper_execute
 
Note i lost all my "su -" commands.
Update .shrc
Code: 
export HISTSIZE=1000
export HISTFILE=/root/.sh_history
set -o emacs
set -o allexport
alias history='fc -l 1'

Update zabbix_agentd.conf,
My zpool is called SSD,
Code: 
UserParameter=vfs.fs.discovery,zfs list -H -o name | grep -vE '@|/SSD/' | /usr/local/bin/jq -R -s 'split("\n") | map(select(length &gt; 0)) | map({"{#FSNAME}": .}) | {data: .}'
 
You must log in or register to reply here.
Back
Top