[Bug]: pg_restore cannot restore data correctly after running timescaledb_post_restore, but the right data when running timescaledb_pre_restore

[ruoshui1314]

What type of bug is this?

Incorrect result

What subsystems and features are affected?

Backup, Restore

What happened?

1. running pg_dump to dump database: pg_dump -h localhost -U username -p 5434 -Fc -f vnpy.back vnpy
2. running pg_restore to restore database to another machine with the same version of postgresql and timescaledb.

CREATE DATABASE vnpy;
\c vnpy
CREATE EXTENSION IF NOT EXISTS timescaledb;
SELECT timescaledb_pre_restore();
\! pg_restore -U postgres --no-owner -Fc -v -d vnpy vnpy.back
SELECT timescaledb_post_restore();

3. result:
   There is no any data after running 'SELECT timescaledb_post_restore()', but I can get the data when running 'SELECT timescaledb_pre_restore();'
   details:
   

TimescaleDB version affected

2.17.0

PostgreSQL version used

14.13

What operating system did you use?

ubuntu 20.04

What installation method did you use?

Deb/Apt

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

No response

How can we reproduce the bug?

In my environment, the issue is inevitable following steps as above.
How to restore database correctly, please give me advices?

[ruoshui1314]

Anyone could help ?

[ruoshui1314]

I just drop database and do it again, but it still dost not work.

[antekresic]

Can you look at and post explain plans before and after post restore?

explain select count(*) from bar_data_1m;

[ruoshui1314]

Can you look at and post explain plans before and after post restore?

explain select count(*) from bar_data_1m;

results:
before post restore:

after post restore:

please help to check...

[antekresic]

There is something we are missing here, I have tried doing what you are doing and for me the behavior is opposite. I don't see any rows before post restore and see them after post restore.

Is there anything else you did? Could you do an explain with analyze? Maybe that can give us some hints whats going on here.

[ruoshui1314]

There is something we are missing here, I have tried doing what you are doing and for me the behavior is opposite. I don't see any rows before post restore and see them after post restore.

Is there anything else you did? Could you do an explain with analyze? Maybe that can give us some hints whats going on here.

I create table and database before "SELECT timescaledb_pre_restore();".

The detail steps:

1. use pg_dump database to vnpy.back in server A
2. copy vnpy.back into server B and drop database and create database and table using python script in server B.
3. run pg_restore and recover data from vnpy.back in server B.

SELECT timescaledb_pre_restore();
\! pg_restore -U postgres --no-owner -Fc -v -d vnpy vnpy.back
SELECT timescaledb_post_restore();

each table is hypertable and create table and excute sql SELECT create_hypertable('bar_data_1m', 'datetime', if_not_exists => TRUE); though python script in step 2.

[antekresic]

I don't think you need create tables and run create_hypertable in step 2.

Can you just try without that? Just run on server B:

CREATE DATABASE vnpy;
\c vnpy
CREATE EXTENSION IF NOT EXISTS timescaledb;
SELECT timescaledb_pre_restore();
\! pg_restore -U postgres --no-owner -Fc -v -d vnpy vnpy.back
SELECT timescaledb_post_restore();

[ruoshui1314]

I don't think you need create tables and run create_hypertable in step 2.

Can you just try without that? Just run on server B:

CREATE DATABASE vnpy;
\c vnpy
CREATE EXTENSION IF NOT EXISTS timescaledb;
SELECT timescaledb_pre_restore();
\! pg_restore -U postgres --no-owner -Fc -v -d vnpy vnpy.back
SELECT timescaledb_post_restore();

It works, but there is another message.
ERROR: table "bar_data_1m" is already partitioned\nDETAIL: It is not possible to turn tables that use inheritance into hypertables.\n
Is it right and need to do something?

[antekresic]

This seems like bar_data_1m is still created before you run pg_restore. Make sure you don't create it manually or drop it if already exists.

[ruoshui1314]

This seems like bar_data_1m is still created before you run pg_restore. Make sure you don't create it manually or drop it if already exists.

I have restored data into database but table will be created again in our python script (we created table through python script when running) and it throw an exception as above.

[antekresic]

This is what I'm suggesting, don't create the table or hypertable, the restore step will do that for you.

[ruoshui1314]

This is what I'm suggesting, don't create the table or hypertable, the restore step will do that for you.

ok.thank you for your support...

[ruoshui1314]

This is what I'm suggesting, don't create the table or hypertable, the restore step will do that for you.

Sorry to open this issue and please help.There are some extract incremental datas from Server A that I need to sync it to Server B.
I use psql -h "x.x.x.x" -d $DB_NAME -U $DB_USER -c "\copy (SELECT * FROM $TABLE WHERE datetime>='$SPECIAL_TIME') TO '$CSV_FILE' CSV HEADER;" to generate csv files due to the data not big.
Run psql -d $DB_NAME -U $DB_USER -c "\COPY $TABLE FROM '$CSV_FILE' DELIMITER ',' CSV HEADER;" to restore from csv into database and it reports an error and I can get the incremental data in db.

Exporting data from bar_data after 2024-10-21
COPY 2306
WARNING:  function _timescaledb_internal.insert_blocker() is deprecated and has been moved to _timescaledb_functions schema. this compatibility function will be removed in a future version.
ERROR:  insert_blocker: not called by trigger manager
CONTEXT:  PL/pgSQL function _timescaledb_internal.insert_blocker() line 6 at RETURN
COPY bar_data, line 2: "IF2412,CFFEX,IF,1h,2024-10-21 10:00:00,24095,28505346840,153278,3952.8,3975.2,3911,3915.4,3914.8,391..."
Exporting data from io_bar_data after 2024-10-21
COPY 6260

But I can find these data in csv so there are some incremental datas actually.

[ruoshui1314]

This is what I'm suggesting, don't create the table or hypertable, the restore step will do that for you.

Another question is that tables is not hypertable anymore when use pg_restore tools to restore data in Server B.
SELECT * FROM timescaledb_information.hypertables;
This information is from server A:

but not in Server B

How can I restore data from Server A corretly?

[antekresic]

I don't think your dump is OK then. There should be hypertables when you restore.

Please take a look at our documentation on how to do dump/restore properly and follow the exact steps:
https://docs.timescale.com/self-hosted/latest/migration/entire-database/

[ruoshui1314]

I don't think your dump is OK then. There should be hypertables when you restore.

Please take a look at our documentation on how to do dump/restore properly and follow the exact steps: https://docs.timescale.com/self-hosted/latest/migration/entire-database/

I know the reason. The timescaledb version in Server A is 2.8.1 but version in Server B is 2.17.1. Thank you so much.