Perfect PostgreSQL Zabbix restore procedure without warnings, pg_dump, pg

7.0.24:

BCKSRCDIR="/backup/postgres/2026/03/14/0303"

cd /tmp
wget https://cdn.zabbix.com/zabbix/sources/stable/7.0/zabbix-7.0.24.tar.gz
tar xvf zabbix-7.0.24.tar.gz

# create table structure wirhout contraints
grep -B9999999 "INSERT INTO dbversion VALUES" /tmp/zabbix-7.0.24/database/postgresql/schema.sql |\
grep -v "INSERT INTO dbversion VALUES" |\
PGHOST=10.0.0.249 PGUSER=zabbix PGPASSWORD=zabbix PGPORT=7418 psql zbxdb

# restore plain data data, without any structure, table by table
grep -oE "^CREATE TABLE \S+" /tmp/zabbix-7.0.24/database/postgresql/schema.sql | grep -Eo "\S+$" | sort |\
while IFS= read -r TABLE
do {
echo $TABLE
cd "$BCKSRCDIR" && PGHOST=10.0.0.249 PGUSER=zabbix PGPASSWORD=zabbix PGPORT=7418 pg_restore --table=$TABLE --exit-on-error --disable-triggers --data-only -Fc -d zbxdb z70.pg_dump.custom
} done

psql
DELETE FROM trigger_discovery WHERE triggerid NOT IN (SELECT triggerid FROM triggers);
DELETE FROM item_discovery WHERE itemid NOT IN (SELECT itemid FROM items);
DELETE FROM item_rtdata WHERE itemid NOT IN (SELECT itemid FROM items);
DELETE FROM item_rtname WHERE itemid NOT IN (SELECT itemid FROM items);
DELETE FROM task_remote_command WHERE taskid NOT IN (SELECT taskid FROM task);
DELETE FROM task_remote_command_result WHERE taskid NOT IN (SELECT taskid FROM task);

# restore contraints
grep -A9999999 "INSERT INTO dbversion VALUES" /tmp/zabbix-7.0.24/database/postgresql/schema.sql |\
grep -v "INSERT INTO dbversion VALUES" |\
PGHOST=10.0.0.249 PGUSER=zabbix PGPASSWORD=zabbix PGPORT=7418 psql zbxdb

cat /tmp/zabbix-7.0.24/database/postgresql/timescaledb/schema.sql |\
PGHOST=10.0.0.249 PGUSER=zabbix PGPASSWORD=zabbix PGPORT=7418 psql zbxdb

ALTER TABLE history_uint SET ( timescaledb.compress );
ALTER TABLE history SET ( timescaledb.compress );
ALTER TABLE history_log SET ( timescaledb.compress );
ALTER TABLE history_bin SET ( timescaledb.compress );
ALTER TABLE auditlog SET ( timescaledb.compress );

7.0:



BCKSRCDIR="/backup/postgres/2024/12/11/0303"

cd /tmp
wget https://cdn.zabbix.com/zabbix/sources/stable/7.0/zabbix-7.0.6.tar.gz
tar xvf zabbix-7.0.6.tar.gz

# create table structure wirhout contraints
grep -B9999999 "INSERT INTO dbversion VALUES" /tmp/zabbix-7.0.6/database/postgresql/schema.sql |\
grep -v "INSERT INTO dbversion VALUES" |\
PGHOST=193.123.33.206 PGUSER=zabbix PGPORT=7416 psql z70

# restore plain data data, without any structure, table by table
grep -oE "^CREATE TABLE \S+" /tmp/zabbix-7.0.6/database/postgresql/schema.sql | grep -Eo "\S+$" | sort |\
while IFS= read -r TABLE
do {
echo $TABLE
cd "$BCKSRCDIR" && PGHOST=193.123.33.206 PGUSER=zabbix PGPORT=7416 pg_restore --table=$TABLE --exit-on-error --disable-triggers --data-only -Fc -d z70 z70.pg_dump.custom
} done

psql
DELETE FROM trigger_discovery WHERE triggerid NOT IN (SELECT triggerid FROM triggers);
DELETE FROM item_discovery WHERE itemid NOT IN (SELECT itemid FROM items);
DELETE FROM item_rtdata WHERE itemid NOT IN (SELECT itemid FROM items);
DELETE FROM item_rtname WHERE itemid NOT IN (SELECT itemid FROM items);
DELETE FROM task_remote_command WHERE taskid NOT IN (SELECT taskid FROM task);
DELETE FROM task_remote_command_result WHERE taskid NOT IN (SELECT taskid FROM task);

# restore contraints
grep -A9999999 "INSERT INTO dbversion VALUES" /tmp/zabbix-7.0.6/database/postgresql/schema.sql |\
grep -v "INSERT INTO dbversion VALUES" |\
PGHOST=193.123.33.206 PGUSER=zabbix PGPORT=7416 psql z70

6.4:

BCKSRCDIR="/backup/postgres/2024/12/11/0303"

cd /tmp
wget https://cdn.zabbix.com/zabbix/sources/stable/6.4/zabbix-6.4.18.tar.gz
tar xvf zabbix-6.4.18.tar.gz

# create table structure wirhout contraints
grep -B9999999 "INSERT INTO dbversion VALUES" /tmp/zabbix-6.4.18/database/postgresql/schema.sql |\
grep -v "INSERT INTO dbversion VALUES" |\
PGHOST=193.123.33.206 PGUSER=zabbix PGPORT=7416 psql z64

# restore plain data data, without any structure, table by table
grep -oE "^CREATE TABLE \S+" /tmp/zabbix-6.4.18/database/postgresql/schema.sql | grep -Eo "\S+$" | sort |\
while IFS= read -r TABLE
do {
echo $TABLE
cd "$BCKSRCDIR" && PGHOST=193.123.33.206 PGUSER=zabbix PGPORT=7416 pg_restore --table=$TABLE --exit-on-error --disable-triggers --data-only -Fc -d z64 z64.pg_dump.custom
} done

# restore contraints
grep -A9999999 "INSERT INTO dbversion VALUES" /tmp/zabbix-6.4.18/database/postgresql/schema.sql |\
grep -v "INSERT INTO dbversion VALUES" |\
PGHOST=193.123.33.206 PGUSER=zabbix PGPORT=7416 psql z64

6.2:


BCKSRCDIR="/backup/postgres/2024/12/11/0303"

cd /tmp
wget https://cdn.zabbix.com/zabbix/sources/oldstable/6.2/zabbix-6.2.9.tar.gz
tar xvf zabbix-6.2.9.tar.gz

# create table structure wirhout contraints
grep -B9999999 "INSERT INTO dbversion VALUES" /tmp/zabbix-6.2.9/database/postgresql/schema.sql |\
grep -v "INSERT INTO dbversion VALUES" |\
PGHOST=193.123.33.206 PGUSER=zabbix PGPORT=7416 psql z62

# restore plain data data, without any structure, table by table
grep -oE "^CREATE TABLE \S+" /tmp/zabbix-6.2.9/database/postgresql/schema.sql | grep -Eo "\S+$" | sort |\
while IFS= read -r TABLE
do {
echo $TABLE
cd "$BCKSRCDIR" && PGHOST=193.123.33.206 PGUSER=zabbix PGPORT=7416 pg_restore --table=$TABLE --exit-on-error --disable-triggers --data-only -Fc -d z62 z62.pg_dump.custom
} done

# restore contraints
grep -A9999999 "INSERT INTO dbversion VALUES" /tmp/zabbix-6.2.9/database/postgresql/schema.sql |\
grep -v "INSERT INTO dbversion VALUES" |\
PGHOST=193.123.33.206 PGUSER=zabbix PGPORT=7416 psql z62

6.0:

BCKSRCDIR="/backup/postgres/2024/12/11/0303"

cd /tmp
wget https://cdn.zabbix.com/zabbix/sources/stable/6.0/zabbix-6.0.36.tar.gz
tar xvf zabbix-6.0.36.tar.gz

# create table structure wirhout contraints
grep -B9999999 "INSERT INTO dbversion VALUES" /tmp/zabbix-6.0.36/database/postgresql/schema.sql |\
grep -v "INSERT INTO dbversion VALUES" |\
PGHOST=193.123.33.206 PGUSER=zabbix PGPORT=7416 psql z60

# restore plain data data, without any structure, table by table
grep -oE "^CREATE TABLE \S+" /tmp/zabbix-6.0.36/database/postgresql/schema.sql | grep -Eo "\S+$" | sort |\
while IFS= read -r TABLE
do {
echo $TABLE
cd "$BCKSRCDIR" && PGHOST=193.123.33.206 PGUSER=zabbix PGPORT=7416 pg_restore --table=$TABLE --exit-on-error --disable-triggers --data-only -Fc -d z60 z60.pg_dump.custom
} done

# restore contraints
grep -A9999999 "INSERT INTO dbversion VALUES" /tmp/zabbix-6.0.36/database/postgresql/schema.sql |\
grep -v "INSERT INTO dbversion VALUES" |\
PGHOST=193.123.33.206 PGUSER=zabbix PGPORT=7416 psql z60


        UPDATE config SET db_extension='',hk_history_global=0,hk_trends_global=0;
        UPDATE config SET compression_status=0,compress_older='7d';

Restore trends:


# z60
PGUSER=zabbix PGPASSWORD=zabbix PGHOST=193.123.33.206 PGPORT=7416 psql z60 -c "\COPY history_str FROM PROGRAM 'xzcat /backup/postgres/2024/12/11/0303/z60.history_str.csv.xz' DELIMITER ',' CSV"
PGUSER=zabbix PGPASSWORD=zabbix PGHOST=193.123.33.206 PGPORT=7416 psql z60 -c "\COPY history_text FROM PROGRAM 'xzcat /backup/postgres/2024/12/11/0303/z60.history_text.csv.xz' DELIMITER ',' CSV"
PGUSER=zabbix PGPASSWORD=zabbix PGHOST=193.123.33.206 PGPORT=7416 psql z60 -c "\COPY trends_uint FROM PROGRAM 'xzcat /backup/postgres/2024/12/11/0303/z60.trends_uint.csv.xz' DELIMITER ',' CSV"
PGUSER=zabbix PGPASSWORD=zabbix PGHOST=193.123.33.206 PGPORT=7416 psql z60 -c "\COPY trends FROM PROGRAM 'xzcat /backup/postgres/2024/12/11/0303/z60.trends.csv.xz' DELIMITER ',' CSV"

PGUSER=zabbix PGPASSWORD=zabbix PGHOST=193.123.33.206 PGPORT=7416 psql z62 -c "\COPY history_str FROM PROGRAM 'xzcat /backup/postgres/2024/12/11/0303/z62.history_str.csv.xz' DELIMITER ',' CSV"
PGUSER=zabbix PGPASSWORD=zabbix PGHOST=193.123.33.206 PGPORT=7416 psql z62 -c "\COPY history_text FROM PROGRAM 'xzcat /backup/postgres/2024/12/11/0303/z62.history_text.csv.xz' DELIMITER ',' CSV"
PGUSER=zabbix PGPASSWORD=zabbix PGHOST=193.123.33.206 PGPORT=7416 psql z62 -c "\COPY trends_uint FROM PROGRAM 'xzcat /backup/postgres/2024/12/11/0303/z62.trends_uint.csv.xz' DELIMITER ',' CSV"
PGUSER=zabbix PGPASSWORD=zabbix PGHOST=193.123.33.206 PGPORT=7416 psql z62 -c "\COPY trends FROM PROGRAM 'xzcat /backup/postgres/2024/12/11/0303/z62.trends.csv.xz' DELIMITER ',' CSV"

PGUSER=zabbix PGPASSWORD=zabbix PGHOST=193.123.33.206 PGPORT=7416 psql z64 -c "\COPY history_str FROM PROGRAM 'xzcat /backup/postgres/2024/12/11/0303/z64.history_str.csv.xz' DELIMITER ',' CSV"
PGUSER=zabbix PGPASSWORD=zabbix PGHOST=193.123.33.206 PGPORT=7416 psql z64 -c "\COPY history_text FROM PROGRAM 'xzcat /backup/postgres/2024/12/11/0303/z64.history_text.csv.xz' DELIMITER ',' CSV"
PGUSER=zabbix PGPASSWORD=zabbix PGHOST=193.123.33.206 PGPORT=7416 psql z64 -c "\COPY trends_uint FROM PROGRAM 'xzcat /backup/postgres/2024/12/11/0303/z64.trends_uint.csv.xz' DELIMITER ',' CSV"
PGUSER=zabbix PGPASSWORD=zabbix PGHOST=193.123.33.206 PGPORT=7416 psql z64 -c "\COPY trends FROM PROGRAM 'xzcat /backup/postgres/2024/12/11/0303/z64.trends.csv.xz' DELIMITER ',' CSV"

PGUSER=zabbix PGPASSWORD=zabbix PGHOST=193.123.33.206 PGPORT=7416 psql z70 -c "\COPY history_str FROM PROGRAM 'xzcat /backup/postgres/2024/12/11/0303/z70.history_str.csv.xz' DELIMITER ',' CSV"
PGUSER=zabbix PGPASSWORD=zabbix PGHOST=193.123.33.206 PGPORT=7416 psql z70 -c "\COPY history_text FROM PROGRAM 'xzcat /backup/postgres/2024/12/11/0303/z70.history_text.csv.xz' DELIMITER ',' CSV"
PGUSER=zabbix PGPASSWORD=zabbix PGHOST=193.123.33.206 PGPORT=7416 psql z70 -c "\COPY trends_uint FROM PROGRAM 'xzcat /backup/postgres/2024/12/11/0303/z70.trends_uint.csv.xz' DELIMITER ',' CSV"
PGUSER=zabbix PGPASSWORD=zabbix PGHOST=193.123.33.206 PGPORT=7416 psql z70 -c "\COPY trends FROM PROGRAM 'xzcat /backup/postgres/2024/12/11/0303/z70.trends.csv.xz' DELIMITER ',' CSV"

Create monitoring user:


CREATE USER zbx_monitor WITH PASSWORD 'PasswordOfMonitor' INHERIT;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_ls_dir(text) TO zbx_monitor;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_stat_file(text) TO zbx_monitor;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_ls_waldir TO zbx_monitor;

Enable compression for certain tables:


ALTER TABLE history_uint SET ( timescaledb.compress );
ALTER TABLE history SET ( timescaledb.compress );

https://luna.oracle.com/lab/8a060473-bff3-4c04-9799-eb944951007c/steps

Perfect PostgreSQL Zabbix restore procedure without warnings, pg_dump, pg_restore

No comments:

Post a Comment

solve, automate, speed up

Zabbix PostgreSQL

Perfect PostgreSQL Zabbix restore procedure without warnings, pg_dump, pg_restore

No comments:

Post a Comment