Need to identify the tables containing Zabbix config data in postgresql

[ppsanthosh]

Need a different approach to backup postgresql database to ensure just the config data for zabbix. Do not want to backup the transient data for history and trends.
The current backps are more than 800 MB in size. Need to upload this to a datastore where there is a size limit. Need to reduce the size but wanted make sure that we keep the required schema objects to ensure a successful restore.The script is using the following options.

pg_dumpall -U postgres --globals-only --file=$backupdir/globals.sql and then zip -j $backupdir/backup.$index.zip $backupdir/*.sql

The purpose here is to come back on with a zabbix set up, just in case a crash occurs. In other words what are the required schema objects that Zabbix depend on, that need to be backed up to ensure a perfect restore, ignoring all the monitored data that may be available in history and trends. Thanks for your help.

[heaje]

The answer is in your question friend . Literally, just don't backup any tables that have the name "history" or "trends" in them. If you back up all other tables, you'll get all the configuration data that exists in the database.

[ppsanthosh]

Need to match the correct tables in postgresql DB

Hi Admins

I am trying to reduce the backup size and need to eliminate history and trend related tables in Zabbix database.I am using the following command but still can not elimainate those tables from the backup.

pg_dump -U postgres -T 'history'~* -T 'trend'~* zabbix >zabbix-test.sql

I know, I did not match the pattern correctly. I would appreciate if someone could correct me. Appreciate your help.

[ppsanthosh]

Found the answer:

zabbix=# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 10;
relname | relpages
------------------+----------
history | 862089
history_1 | 451437
history_uint | 139582
history_uint_1 | 73096
trends_uint | 52653
trends | 21384
trends_uint_pkey | 20754
trends_pkey | 8589
alerts | 1213
events | 664
-----------------------------------
Then do the backup with the following options.
pg_dump -U postgres -T history -T history_1 -T history_uint -T history_uint_1 -T trends_uint -T trends -T trends_uint_pkey -T alerts -T events zabbix >/pgdata/zabbix.dump

The resultant backup has all the tables that have config data but it will have no monitored data. The size of the backup file has reduced from 3.4GB to just 5 MB.

Thanks heaje for confirming the direction.