If you run into problems when using TimescaleDB, there are a few things that you can do. There are some solutions to common errors in this section as well as ways to output diagnostic information about your setup. If you need more guidance, you can join the community Slack group or post an issue on the TimescaleDB GitHub.
The ALTER EXTENSION timescaledb UPDATE
command must be the first
command executed upon connection to a database. Some administration tools
execute commands before this, which can disrupt the process. You might
need to manually update the database with psql
. See the
update docs for details.
If your PostgreSQL logs have this error preventing it from starting up, you
should double check that the TimescaleDB files have been installed to the
correct location. The installation methods use pg_config
to get PostgreSQL's
location. However if you have multiple versions of PostgreSQL installed on the
same machine, the location pg_config
points to may not be for the version you
expect. To check which version TimescaleDB used:
$ pg_config --versionPostgreSQL 12.3
If that is the correct version, double check that the installation path is
the one you'd expect. For example, for PostgreSQL 11.0 installed via
Homebrew on macOS it should be /usr/local/Cellar/postgresql/11.0/bin
:
$ pg_config --bindir/usr/local/Cellar/postgresql/11.0/bin
If either of those steps is not the version you are expecting, you need to
either uninstall the incorrect version of PostgreSQL if you can, or update your
PATH
environmental variable to have the correct path of pg_config
listed
first, that is, by prepending the full path:
export PATH = /usr/local/Cellar/postgresql/11.0/bin:$PATH
Then, reinstall TimescaleDB and it should find the correct installation path.
If the error occurs immediately after updating your version of TimescaleDB and
the file mentioned is from the previous version, it is probably due to an
incomplete update process. Within the greater PostgreSQL server instance, each
database that has TimescaleDB installed needs to be updated with the SQL command
ALTER EXTENSION timescaledb UPDATE;
while connected to that database.
Otherwise, the database looks for the previous version of the timescaledb
files.
See our update docs for more info.
Your scheduled jobs might stop running for various reasons. On self-hosted TimescaleDB, you can fix this by restarting background workers:
SELECT _timescaledb_internal.restart_background_workers();
On Timescale and Managed Service for TimescaleDB, restart background workers by doing one of the following:
- Run
SELECT timescaledb_pre_restore()
, followed bySELECT timescaledb_post_restore()
. - Power the service off and on again. This might cause a downtime of a few minutes while the service restores from backup and replays the write-ahead log.
You might see this error message in the logs if background workers aren't properly configured:
"<TYPE_OF_BACKGROUND_JOB>": failed to start a background worker
To fix this error, make sure that max_worker_processes
,
max_parallel_workers
, and timescaledb.max_background_workers
are properly
set. timescaledb.max_background_workers
should equal the number of databases
plus the number of concurrent background workers. max_worker_processes
should
equal the sum of timescaledb.max_background_workers
and
max_parallel_workers
.
For more information, see the worker configuration docs.
You might see this error message when trying to compress a chunk if the permissions for the compressed hypertable is corrupt.
tsdb=> SELECT compress_chunk('_timescaledb_internal._hyper_65_587239_chunk');ERROR: role 149910 was concurrently dropped
This can be caused if you dropped a user for the hypertable before
TimescaleDB 2.5. For this case, the user would be removed from
pg_authid
but not revoked from the compressed table.
As a result, the compressed table contains permission items that refers to numerical values rather than existing users (see below for how to find the compressed hypertable from a normal hypertable):
tsdb=> \dp _timescaledb_internal._compressed_hypertable_2Access privilegesSchema | Name | Type | Access privileges | Column privileges | Policies--------+--------------+-------+---------------------+-------------------+----------public | transactions | table | mats=arwdDxt/mats +| || | | wizard=arwdDxt/mats+| || | | 149910=r/mats | |(1 row)
This means that the relacl
column of pg_class
needs to be updated
and the offending user removed, but it is not possible to drop a user
by numerical value. Instead, you can use the internal function
repair_relation_acls
in _timescaledb_function
schema:
tsdb=> CALL _timescaledb_functions.repair_relation_acls();
WARNING: Note that this requires superuser privileges (since you're modifying the
pg_class
table) and that it removes any user not present inpg_authid
from all tables, so use with caution.
The permissions are usually corrupted for the hypertable as well, but
not always, so it is better to look at the compressed hypertable to
see if the problem is present. To find the compressed hypertable for
an associated hypertable (readings
in this case):
tsdb=> select ht.table_name,tsdb-> (select format('%I.%I', schema_name, table_name)::regclasstsdb-> from _timescaledb_catalog.hypertabletsdb-> where ht.compressed_hypertable_id = id) as compressed_tabletsdb-> from _timescaledb_catalog.hypertable httsdb-> where table_name = 'readings';format | format----------+------------------------------------------------readings | _timescaledb_internal._compressed_hypertable_2(1 row)
PostgreSQL's EXPLAIN feature allows users to understand the underlying query plan that PostgreSQL uses to execute a query. There are multiple ways that PostgreSQL can execute a query: for example, a query might be fulfilled using a slow sequence scan or a much more efficient index scan. The choice of plan depends on what indexes are created on the table, the statistics that PostgreSQL has about your data, and various planner settings. The EXPLAIN output let's you know which plan PostgreSQL is choosing for a particular query. PostgreSQL has a in-depth explanation of this feature.
To understand the query performance on a hypertable, we suggest first
making sure that the planner statistics and table maintenance is up-to-date on the hypertable
by running VACUUM ANALYZE <your-hypertable>;
. Then, we suggest running the
following version of EXPLAIN:
EXPLAIN (ANALYZE on, BUFFERS on) <original query>;
If you suspect that your performance issues are due to slow IOs from disk, you
can get even more information by enabling the
track_io_timing variable with SET track_io_timing = 'on';
before running the above EXPLAIN.
To help when asking for support and reporting bugs,
TimescaleDB includes a SQL script that outputs metadata
from the internal TimescaleDB tables as well as version information.
The script is available in the source distribution in scripts/
but can also be downloaded separately.
To use it, run:
psql [your connect flags] -d your_timescale_db < dump_meta_data.sql > dumpfile.txt
and then inspect dump_file.txt
before sending it together with a bug report or support question.
Keywords
Found an issue on this page?
Report an issue!