Cannot insert into timescaledb table after migration from Signle Server to Flexible Server

Question

Cannot insert into timescaledb table after migration from Signle Server to Flexible Server

Vladimir Shurakov 66

Hi, our Azure Database for PostgreSQL single server was automatically migrated to Database for Azure Database for PostgreSQL flexible server last Saturday. Since then we can no longer insert data into timescaledb tables. The following error appears on insert: ERROR: invalid INSERT on the root table of hypertable "table_name" HINT: Make sure the TimescaleDB extension has been preloaded. SQL state: 0A000.

We have checked the flexible server's configuration and can confirm that the timescaledb extension is installed, preloaded, and enabled in the database.

Adithya Prasad K 1,375 Reputation points Microsoft External Staff Moderator

2025-03-17T14:15:47.3666667+00:00
Hi Vladimir Shurakov,
It sounds like you're encountering a issue with your Azure Database for PostgreSQL flexible server after the migration. Let's go through some steps and considerations to help resolve this problem.

Steps to Resolve the Issue

Verify TimescaleDB Extension:

Ensure that the TimescaleDB extension is correctly installed, preloaded, and enabled in your database. You can check this by running the following SQL command:

SELECT * FROM pg_extension WHERE extname = 'timescaledb';

If the extension is not listed, you need to install it using:

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

Check Configuration Settings:

Verify that the TimescaleDB extension is preloaded in the PostgreSQL configuration. You can do this by checking the shared_preload_libraries parameter:

SHOW shared_preload_libraries;

Ensure that timescaledb is included in the list. If not, you need to modify the configuration file (usually postgresql.conf) to include it:

shared_preload_libraries = 'timescaledb'

Restart the PostgreSQL Server:

After making changes to the configuration file, restart the PostgreSQL server to apply the changes.

Check for Compatibility Issues:

Ensure that the version of TimescaleDB you are using is compatible with the version of PostgreSQL on your flexible server. Compatibility issues can sometimes cause unexpected errors.

Common Issues and Troubleshooting Steps

Permission Issues: Ensure that the user account you are using has the necessary permissions to insert data into the hypertable.

Network Restrictions: Verify that there are no network restrictions preventing access to the database.

Configuration Errors: Double-check the configuration settings in the Azure portal and ensure that the TimescaleDB extension is correctly added and preloaded.

Relevant Documentation and Resources

Azure Database for PostgreSQL Documentation

GitHub Issue on Similar Error

https://learn.microsoft.com/en-us/azure/postgresql/extensions/concepts-extensions-considerations?source=recommendations

By following these steps and ensuring that all configurations are correct, you should be able to resolve the issue with inserting data into TimescaleDB tables on your Azure Database for PostgreSQL flexible server.

Let me know if you need further help or clarification on any of these steps!
Vladimir Shurakov 66 Reputation points

2025-03-17T16:39:48.7233333+00:00
Hi @Addithya Prasad , thanks a lot for the fast turnaround.

I've looked over what you suggested.

Here is the result:

Verify TimescaleDB Extension - the extension is installed in DB

Check Configuration Settings - timescaledb is included in the list.

Restart the PostgreSQL Server - done

Check for Compatibility Issues - timescaledb version in use is v1.7.4 which is compatible with Postgres v11

Tried to insert it once again, but ended up with the same error.

Could you please check on your end?

Accepted answer

1 additional answer

Your answer

Vladimir Shurakov 66 Reputation points

2025-03-17T16:39:48.7233333+00:00

Hi @Addithya Prasad , thanks a lot for the fast turnaround.

I've looked over what you suggested.

Here is the result:

Verify TimescaleDB Extension - the extension is installed in DB

Check Configuration Settings - timescaledb is included in the list.

Restart the PostgreSQL Server - done

Check for Compatibility Issues - timescaledb version in use is v1.7.4 which is compatible with Postgres v11

Tried to insert it once again, but ended up with the same error.

Could you please check on your end?

Answer 1

Hi Vladimir Shurakov,
I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to "Accept " the answer.
Issue:
our Azure Database for PostgreSQL single server was automatically migrated to Database for Azure Database for PostgreSQL flexible server last Saturday. Since then we can no longer insert data into timescaledb tables. The following error appears on insert: ERROR: invalid INSERT on the root table of hypertable "table_name" HINT: Make sure the TimescaleDB extension has been preloaded. SQL state: 0A000.

We have checked the flexible server's configuration and can confirm that the timescaledb extension is installed, preloaded, and enabled in the database.

Soultion:
Alright, after having a look at the database we found out that each of the timescaledb tables was restored from a single server database with triggers ts_insert_blocker. As explained here: https://github.com/timescale/timescaledb/issues/2304 the problem is solved by removing them from the tables.

Answer 2

Vladimir Shurakov 66

Alright, after having a look at the database we found out that each of the timescaledb tables was restored from a single server database with triggers ts_insert_blocker. As explained here: https://github.com/timescale/timescaledb/issues/2304 the problem is solved by removing them from the tables, but the reason why these triggers have appeared in DB after the migration is not clear.

Share via

Cannot insert into timescaledb table after migration from Signle Server to Flexible Server

1 additional answer

Your answer