TimescaleDB手动减压

Question

在这个指南之后，我通过手动解压缩来解决这个问题。

以下是尝试过的步骤：

1. 查找并暂停与压缩策略相关的作业。

SELECT alter_job(job_id => 
(
SELECT s.job_id
FROM timescaledb_information.jobs j
INNER JOIN timescaledb_information.job_stats s ON j.job_id = s.job_id
WHERE j.proc_name = 'policy_compression' AND s.hypertable_name = 'sensorsdata'
), scheduled => false, next_start => 'infinity');

1. 按时间间隔解压缩块(都没有当前的时间间隔)。

SELECT decompress_chunk(chunk, if_compressed => true)
FROM show_chunks('sensorsdata', older_than => now()::timestamp) AS chunk;

或者单身的名字

SELECT decompress_chunk('_timescaledb_internal._hyper_1_2_chunk', if_compressed => true);

但是步骤2导致db连接丢失。这发生在pgadmin和使用occurs的C#应用程序中。

图1.解压缩单个块时的连接丢失

但是，当我连续几次执行该命令时，会在尝试时执行该命令。

这是我在日志文件中遇到的。

2021-04-20 21:22:50.188 GMT [7728] LOG:  statement: SELECT decompress_chunk('_timescaledb_internal._hyper_1_5_chunk', if_compressed => true);
2021-04-20 21:22:50.228 GMT [6972] LOG:  server process (PID 7728) was terminated by exception 0xC0000005
2021-04-20 21:22:50.228 GMT [6972] DETAIL:  Failed process was running: SELECT decompress_chunk('_timescaledb_internal._hyper_1_5_chunk', if_compressed => true);
2021-04-20 21:22:50.228 GMT [6972] HINT:  See C include file "ntstatus.h" for a description of the hexadecimal value.
2021-04-20 21:22:50.233 GMT [6972] LOG:  terminating any other active server processes
2021-04-20 21:22:50.248 GMT [16160] WARNING:  terminating connection because of crash of another server process
2021-04-20 21:22:50.248 GMT [16160] DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2021-04-20 21:22:50.248 GMT [16160] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2021-04-20 21:22:50.257 GMT [6972] LOG:  all server processes terminated; reinitializing
2021-04-20 21:22:50.337 GMT [1052] LOG:  database system was interrupted; last known up at 2021-04-20 21:22:49 GMT
2021-04-20 21:22:51.095 GMT [1052] LOG:  database system was not properly shut down; automatic recovery in progress
2021-04-20 21:22:51.102 GMT [1052] LOG:  invalid record length at 0/11801C78: wanted 24, got 0
2021-04-20 21:22:51.104 GMT [1052] LOG:  redo is not required
2021-04-20 21:22:51.159 GMT [6972] LOG:  database system is ready to accept connections
2021-04-20 21:22:51.217 GMT [16440] LOG:  TimescaleDB background worker launcher connected to shared catalogs
2021-04-20 21:22:51.819 GMT [7128] LOG:  statement: SET DateStyle=ISO; SET client_min_messages=notice; SELECT set_config('bytea_output','hex',false) FROM pg_settings WHERE name = 'bytea_output'; SET client_encoding='UNICODE';
2021-04-20 21:22:52.547 GMT [7128] LOG:  statement: SELECT version()
2021-04-20 21:22:52.549 GMT [7128] LOG:  statement: 
    SELECT
        db.oid as did, db.datname, db.datallowconn,
        pg_encoding_to_char(db.encoding) AS serverencoding,
        has_database_privilege(db.oid, 'CREATE') as cancreate, datlastsysoid,
        datistemplate
    FROM
        pg_database db
    WHERE db.datname = current_database()
2021-04-20 21:22:52.555 GMT [7128] LOG:  statement: 
            SELECT
                oid as id, rolname as name, rolsuper as is_superuser,
                CASE WHEN rolsuper THEN true ELSE rolcreaterole END as
                can_create_role,
                CASE WHEN rolsuper THEN true ELSE rolcreatedb END as can_create_db
            FROM
                pg_catalog.pg_roles
            WHERE
                rolname = current_user
2021-04-20 21:22:52.561 GMT [7128] LOG:  statement: SELECT decompress_chunk('_timescaledb_internal._hyper_1_5_chunk', if_compressed => true);
2021-04-20 21:22:52.688 GMT [7388] LOG:  statement: SET DateStyle=ISO; SET client_min_messages=notice; SELECT set_config('bytea_output','hex',false) FROM pg_settings WHERE name = 'bytea_output'; SET client_encoding='UNICODE';
2021-04-20 21:22:53.371 GMT [7388] LOG:  statement: SELECT version()
2021-04-20 21:22:53.373 GMT [7388] LOG:  statement: 
    SELECT
        db.oid as did, db.datname, db.datallowconn,
        pg_encoding_to_char(db.encoding) AS serverencoding,
        has_database_privilege(db.oid, 'CREATE') as cancreate, datlastsysoid,
        datistemplate
    FROM
        pg_database db
    WHERE db.datname = current_database()
2021-04-20 21:22:53.378 GMT [7388] LOG:  statement: 
            SELECT
                oid as id, rolname as name, rolsuper as is_superuser,
                CASE WHEN rolsuper THEN true ELSE rolcreaterole END as
                can_create_role,
                CASE WHEN rolsuper THEN true ELSE rolcreatedb END as can_create_db
            FROM
                pg_catalog.pg_roles
            WHERE
                rolname = current_user
2021-04-20 21:22:53.383 GMT [7388] LOG:  statement: SELECT oid, format_type(oid, NULL) AS typname FROM pg_type WHERE oid IN (2205) ORDER BY oid;

有什么想法吗？

Answer 1

您的config显示了什么：

select * from timescaledb_information.jobs where job_id=<compression job id>

有时，如果后台作业的参数不好，它可能会失败，并导致另一个连接关闭。