We are using timescaledb 2.14.2 with postgres 15.6 (Ubuntu 15.6-1.pgdg22.04+1). we have a table that have data retention of 24 hours. When we query the table with a time span that is larger than 2 days (e.g. 30 days), the psotgres process crashes.
SELECT
time_bucket(‘1m’, s.“time”) AS “time”,
s.service,
AVG(s.vm_rss)
FROM timeseries_data as s
WHERE
s.“time” BETWEEN ‘2024-03-18T06:04:35.126Z’ AND ‘2024-04-18T06:04:35.126Z’ AND
s.id = ‘123456680’
GROUP BY 1, 2
ORDER BY 1, 3 DESC;
If we limit the time span to <24 hours, the query returned results fine.
Is this a bug with timescaledb 2.14.2 , or we missed some settings?
Hi @yu.yang , when you crashed, can you share the error outuput?
Have you ran timescaledb-tune to reconfig your postgresql after installing? Also, please share your config.
Seems reasonable that you cannot query data after retention policy but it should not fail.
Can you confirm the error is related to the retention settings?
The following is the error log that we observed when the postgres process crashed:
Sourcing /home/postgres/.pgbackrest_environment
2024-04-18 19:45:21 UTC [88]: [66207a39.58-17] @,app= [00000] LOG: server process (PID 583438) was terminated by signal 11: Segmentation fault
2024-04-18 19:45:21 UTC [88]: [66207a39.58-18] @,app= [00000] DETAIL: Failed process was running: SELECT
time_bucket(‘5m’, s.“time”) AS “time”,
s.service,
AVG(s.vm_rss)
FROM timeseries_data as s
WHERE
s.“time” BETWEEN ‘2024-04-11T19:43:49.53Z’ AND ‘2024-04-18T19:43:49.53Z’ AND
s.device_id = ‘1234553432’
GROUP BY 1, 2
ORDER BY 1, 3 DESC
2024-04-18 19:45:21 UTC [88]: [66207a39.58-19] @,app= [00000] LOG: terminating any other active server processes
2024-04-18 19:45:21 UTC [584538]: [66217851.8eb5a-1] [unknown]@[unknown],app=[unknown] [00000] LOG: connection received: host=10.56.1.32 port=47510
2024-04-18 19:45:21 UTC [584538]: [66217851.8eb5a-2] standby@[unknown],app=[unknown] [57P03] FATAL: the database system is in recovery mode
the following is our postgres configuration:
max_wal_size = 1GB
min_wal_size = 80MB
log_timezone = ‘UTC’
datestyle = ‘iso, mdy’
timezone = ‘UTC’
default_text_search_config = ‘pg_catalog.english’
listen_addresses = ‘*’
archive_command = ‘/etc/timescaledb/scripts/pgbackrest_archive.sh %p’
archive_mode = ‘on’
archive_timeout = ‘1800s’
autovacuum_analyze_scale_factor = ‘0.02’
autovacuum_max_workers = ‘10’
autovacuum_naptime = ‘5s’
autovacuum_vacuum_cost_limit = ‘500’
autovacuum_vacuum_scale_factor = ‘0.05’
cluster_name = ‘timescaledb-camera-status-prod’
cron.database_name = ‘postgres’
effective_cache_size = ‘32GB’
effective_io_concurrency = ‘200’
hot_standby = ‘on’
listen_addresses = ‘0.0.0.0’
log_autovacuum_min_duration = ‘1min’
log_checkpoints = ‘on’
log_connections = ‘on’
log_disconnections = ‘on’
log_line_prefix = '%t [%p]: [%c-%l] %u@%d,app=%a [%e] ’
log_lock_waits = ‘on’
log_min_duration_statement = ‘1s’
log_statement = ‘ddl’
maintenance_io_concurrency = ‘200’
max_connections = ‘100’
max_locks_per_transaction = ‘64’
max_prepared_transactions = ‘150’
max_replication_slots = ‘10’
max_wal_senders = ‘10’
max_wal_size = ‘50GB’
max_worker_processes = ‘8’
port = ‘5432’
shared_buffers = ‘16GB’
shared_preload_libraries = ‘timescaledb,pg_stat_statements,pg_cron’
ssl = ‘on’
ssl_cert_file = ‘/etc/certificate/tls.crt’
ssl_key_file = ‘/etc/certificate/tls.key’
tcp_keepalives_idle = ‘900’
tcp_keepalives_interval = ‘100’
temp_file_limit = ‘1GB’
timescaledb.passfile = ‘…/.pgpass’
track_commit_timestamp = ‘off’
unix_socket_directories = ‘/var/run/postgresql’
unix_socket_permissions = ‘0750’
wal_compression = ‘pglz’
wal_decode_buffer_size = ‘512kB’
wal_keep_size = ‘500GB’
wal_level = ‘replica’
wal_log_hints = ‘on’
hba_file = ‘/var/lib/postgresql/data/pg_hba.conf’
ident_file = ‘/var/lib/postgresql/data/pg_ident.conf’
primary_conninfo = ‘user=standby passfile=/home/postgres/.pgpass.patroni host=10.55.3.144 port=5432 sslmode=prefer application_name=timeseries_db-1 gssencmode=prefer channel_binding=prefer’
primary_slot_name = ‘timescaledb_prod_1’
recovery_target = ‘’
recovery_target_lsn = ‘’
recovery_target_name = ‘’
recovery_target_time = ‘’
recovery_target_timeline = ‘latest’
recovery_target_xid = ‘’
restore_command = ‘/etc/timescaledb/scripts/pgbackrest_archive_get.sh %f “%p”’
Have you tried to remove the retention and run the query? Is it working?