Problem with backup

TomReb3l 2 months ago

Hello everyone. I have a VPS with traccar and psql + timescale database. I want to transfer all data to a new server we bought. The old server has psql 14.19 and timescale 2.19.3. In my new server I installed psql 16 and timescale 2.23.0. I made a backup in my old server but when I'm trying to restore this backup in my new server I get always mistakes.
One is that:

lalala@....: ~$ sudo -u postgres pg_restore -Fc -d traccar /tmp/traccar_backup.bak
pg_restore: error: could not execute query: ERROR:  relation "_timescaledb_catalog.chunk_index" does not exist
Command was: COPY _timescaledb_catalog.chunk_index (chunk_id, index_name, hypertable_id, hypertable_index_name) FROM stdin;
pg_restore: error: could not execute query: ERROR:  function _timescaledb_functions.insert_blocker() does not exist
Command was: CREATE TRIGGER ts_insert_blocker BEFORE INSERT ON public.tc_events FOR EACH ROW EXECUTE FUNCTION _timescaledb_functions.insert_blocker();
pg_restore: error: could not execute query: ERROR:  function _timescaledb_functions.insert_blocker() does not exist
Command was: CREATE TRIGGER ts_insert_blocker BEFORE INSERT ON public.tc_positions FOR EACH ROW EXECUTE FUNCTION _timescaledb_functions.insert_blocker();
pg_restore: warning: errors ignored on restore: 3
and the other is that:
lalala@....: ~$  sudo -u postgres psql -d traccar -c "SELECT timescaledb_post_restore();"
ERROR:  catalog version mismatch, expected "2.23.0" seen "2.19.3"
CONTEXT:  PL/pgSQL function public.timescaledb_post_restore() line 12 at RAISE

Obviously the problem is the mismatch but I don't know what to do. I don't want to update psql and timescale in old server because I'm afraid loosing data...
Any suggestion???

Anton Tananaev 2 months ago

I believe you need to restore on the same version of timescale DB and then do an upgrade if needed.

TomReb3l 2 months ago

Is it safe to update psql and timescale to the latest version on the old server or I will lose data?

Anton Tananaev 2 months ago

You should probably check the database documentation to answer this question.