Hi all,
I built a Zabbix 5 PostgreSQL/TimescaleDB instance for work as a proof of concept and over the last several months it's ended up in production and outgrown the hardware. I'm moving it to a new system with many more resources, however I have hit a couple of problems with moving the database backend, and hoped for some help and advice. Both systems are:
* Ubuntu 20.04 LTS server
* Zabbix 5.05 server
* PostgreSQL 13 (psql (PostgreSQL) 13.0 (Ubuntu 13.0-1.pgdg20.04+1)) & TimescaleDB backend
When I built the original system, I kept detailed notes of commands run, and have just copy/pasted these back into the CLI on the new system. Therefore I believe the only difference between old and new would be the several months of Ubuntu patches applied as necessary.
My first attempt at moving the database was to stop zabbix server process, do a pg_dump on the database to a file (sudo -u zabbix pg_dump zabbix | gzip -9 > /tmp/zabbixdb.sql.gz), move that to new system and create a blank database, then restore the .sql file into that database (sudo -u postgres psql -d zabbix -f zabbixdb.sql) and start the zabbix-server process on the new server. This gave me the full database, with all hosts, checks and history available. However any time a new check returned a result zabbix server / postgresql log would error with "PGRES_FATAL_ERROR:ERROR: invalid INSERT on the root table of hypertable "_hyper_X_XXX_chunk. HINT: make sure the TimescaleDB extension has been preloaded."
After a bit of googling and reading through the timescale forums and various help sites, I changed the syntax of the export and import processes. New method was to stop zabbix-server on both old/new hosts, delete the 'new' database on the new server, export 'old' database (sudo -u postgres pg_dump -d zabbix --jobs 4 --format directory -f /tmp/zabbixdbdump), move the folder and all the file across to new server, create new empty database and restore the database (sudo -u postgres pg_restore -d zabbix --jobs 2 /tmp/zabbixdbdump). This time round, after starting zabbix-server, I get all of the hosts and checks again, new data streams into the database and gets stored, however there is no history. I've lost the 5 months of history.
In both export/import scenarios, the database size looks right on either side, but I either get history and no current/future checks work, or I get current/future checks, but no history.
Is there a better method of moving the database from old to new host so I get a fully working platform with history?
Thanks in advance for any help or suggestions you can give.
I built a Zabbix 5 PostgreSQL/TimescaleDB instance for work as a proof of concept and over the last several months it's ended up in production and outgrown the hardware. I'm moving it to a new system with many more resources, however I have hit a couple of problems with moving the database backend, and hoped for some help and advice. Both systems are:
* Ubuntu 20.04 LTS server
* Zabbix 5.05 server
* PostgreSQL 13 (psql (PostgreSQL) 13.0 (Ubuntu 13.0-1.pgdg20.04+1)) & TimescaleDB backend
When I built the original system, I kept detailed notes of commands run, and have just copy/pasted these back into the CLI on the new system. Therefore I believe the only difference between old and new would be the several months of Ubuntu patches applied as necessary.
My first attempt at moving the database was to stop zabbix server process, do a pg_dump on the database to a file (sudo -u zabbix pg_dump zabbix | gzip -9 > /tmp/zabbixdb.sql.gz), move that to new system and create a blank database, then restore the .sql file into that database (sudo -u postgres psql -d zabbix -f zabbixdb.sql) and start the zabbix-server process on the new server. This gave me the full database, with all hosts, checks and history available. However any time a new check returned a result zabbix server / postgresql log would error with "PGRES_FATAL_ERROR:ERROR: invalid INSERT on the root table of hypertable "_hyper_X_XXX_chunk. HINT: make sure the TimescaleDB extension has been preloaded."
After a bit of googling and reading through the timescale forums and various help sites, I changed the syntax of the export and import processes. New method was to stop zabbix-server on both old/new hosts, delete the 'new' database on the new server, export 'old' database (sudo -u postgres pg_dump -d zabbix --jobs 4 --format directory -f /tmp/zabbixdbdump), move the folder and all the file across to new server, create new empty database and restore the database (sudo -u postgres pg_restore -d zabbix --jobs 2 /tmp/zabbixdbdump). This time round, after starting zabbix-server, I get all of the hosts and checks again, new data streams into the database and gets stored, however there is no history. I've lost the 5 months of history.
In both export/import scenarios, the database size looks right on either side, but I either get history and no current/future checks work, or I get current/future checks, but no history.
Is there a better method of moving the database from old to new host so I get a fully working platform with history?
Thanks in advance for any help or suggestions you can give.