| OS: linux 7.5 DBMS: PostgreSQL 14.9 |
[목차여기]
설치 전 확인
## timescaledb 라이브러리 설정 조회
SELECT name, setting
FROM pg_settings
WHERE name LIKE '%shared_preload_libraries%'
;
name | setting
--------------------------+--------------------------------
shared_preload_libraries | pg_stat_statements,timescaledb
(1 row)
## extension 설치 조회
SELECT name, default_version,installed_version
FROM pg_available_extensions
WHERE name LIKE 'timescaledb'
;
name | default_version | installed_version
-------------+-----------------+-------------------
timescaledb | 2.12.2 |
(1 row)
timescaledb extension install
## create extension
create extension timescaledb ;
WARNING:
WELCOME TO
_____ _ _ ____________
|_ _(_) | | | _ \ ___ \
| | _ _ __ ___ ___ ___ ___ __ _| | ___| | | | |_/ /
| | | | _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \
| | | | | | | | | __/\__ \ (_| (_| | | __/ |/ /| |_/ /
|_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/
Running version 2.12.2
For more information on TimescaleDB, please visit the following links:
1. Getting started: https://docs.timescale.com/timescaledb/latest/getting-started
2. API reference documentation: https://docs.timescale.com/api/latest
Note: TimescaleDB collects anonymous reports to better understand and assist our users.
For more information and how to disable, please see our docs https://docs.timescale.com/timescaledb/latest/how-to-guides/configuration/telemetry.
CREATE EXTENSION
## extension install 조회
SELECT name, default_version,installed_version
FROM pg_available_extensions
WHERE name LIKE 'timescaledb'
;
name | default_version | installed_version
-------------+-----------------+-------------------
timescaledb | 2.12.2 | 2.12.2
(1 row)
extension install 후 오브젝트
## timescaledb 관련 스키마 생성됨
SELECT * FROM PG_CATALOG.PG_NAMESPACE ;
oid | nspname | nspowner | nspacl
-------+--------------------------+----------+-------------------------------------
99 | pg_toast | 10 |
11 | pg_catalog | 10 | {postgres=UC/postgres,=U/postgres}
2200 | public | 10 | {postgres=UC/postgres,=UC/postgres}
14118 | information_schema | 10 | {postgres=UC/postgres,=U/postgres}
17286 | _timescaledb_cache | 10 | {postgres=UC/postgres,=U/postgres}
17283 | _timescaledb_catalog | 10 | {postgres=UC/postgres,=U/postgres}
17284 | _timescaledb_functions | 10 | {postgres=UC/postgres,=U/postgres}
17285 | _timescaledb_internal | 10 | {postgres=UC/postgres,=U/postgres}
17287 | _timescaledb_config | 10 | {postgres=UC/postgres,=U/postgres}
17289 | timescaledb_information | 10 | {postgres=UC/postgres,=U/postgres}
17288 | timescaledb_experimental | 10 | {postgres=UC/postgres,=U/postgres}
## timescaledb 관련 오브젝트 생성됨
SELECT n.nspname as schema,
c.relname as name,
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'table' WHEN 'I' THEN 'index' END as type,
pg_catalog.pg_get_userbyid(c.relowner) as owner,
c2.relname as table
, c.reloptions as options
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
WHERE 1=1
AND n.nspname not in ('pg_catalog', 'information_schema')
AND n.nspname !~ '^pg_toast'
ORDER BY 1,2
;
schema | name | type | owner | table | options
--------------------------+-----------------------------------------------------------------+----------+----------+--------------------------------------------------+-------------------------
_timescaledb_cache | cache_inval_bgw_job | table | postgres | |
_timescaledb_cache | cache_inval_extension | table | postgres | |
_timescaledb_cache | cache_inval_hypertable | table | postgres | |
_timescaledb_catalog | chunk | table | postgres | |
_timescaledb_catalog | chunk_compressed_chunk_id_idx | index | postgres | chunk |
_timescaledb_catalog | chunk_constraint | table | postgres | |
_timescaledb_catalog | chunk_constraint_chunk_id_constraint_name_key | index | postgres | chunk_constraint |
_timescaledb_catalog | chunk_constraint_dimension_slice_id_idx | index | postgres | chunk_constraint |
_timescaledb_catalog | chunk_constraint_name | sequence | postgres | |
_timescaledb_catalog | chunk_copy_operation | table | postgres | |
_timescaledb_catalog | chunk_copy_operation_id_seq | sequence | postgres | |
_timescaledb_catalog | chunk_copy_operation_pkey | index | postgres | chunk_copy_operation |
_timescaledb_catalog | chunk_data_node | table | postgres | |
_timescaledb_catalog | chunk_data_node_chunk_id_node_name_key | index | postgres | chunk_data_node |
_timescaledb_catalog | chunk_data_node_node_chunk_id_node_name_key | index | postgres | chunk_data_node |
_timescaledb_catalog | chunk_data_node_node_name_idx | index | postgres | chunk_data_node |
_timescaledb_catalog | chunk_hypertable_id_idx | index | postgres | chunk |
_timescaledb_catalog | chunk_id_seq | sequence | postgres | |
_timescaledb_catalog | chunk_index | table | postgres | |
_timescaledb_catalog | chunk_index_chunk_id_index_name_key | index | postgres | chunk_index |
_timescaledb_catalog | chunk_index_hypertable_id_hypertable_index_name_idx | index | postgres | chunk_index |
_timescaledb_catalog | chunk_osm_chunk_idx | index | postgres | chunk |
_timescaledb_catalog | chunk_pkey | index | postgres | chunk |
_timescaledb_catalog | chunk_schema_name_table_name_key | index | postgres | chunk |
_timescaledb_catalog | compression_algorithm | table | postgres | |
_timescaledb_catalog | compression_algorithm_pkey | index | postgres | compression_algorithm |
_timescaledb_catalog | compression_chunk_size | table | postgres | |
_timescaledb_catalog | compression_chunk_size_pkey | index | postgres | compression_chunk_size |
_timescaledb_catalog | continuous_agg | table | postgres | |
_timescaledb_catalog | continuous_agg_migrate_plan | table | postgres | |
_timescaledb_catalog | continuous_agg_migrate_plan_pkey | index | postgres | continuous_agg_migrate_plan |
_timescaledb_catalog | continuous_agg_migrate_plan_step | table | postgres | |
_timescaledb_catalog | continuous_agg_migrate_plan_step_pkey | index | postgres | continuous_agg_migrate_plan_step |
_timescaledb_catalog | continuous_agg_migrate_plan_step_step_id_seq | sequence | postgres | |
_timescaledb_catalog | continuous_agg_partial_view_schema_partial_view_name_key | index | postgres | continuous_agg |
_timescaledb_catalog | continuous_agg_pkey | index | postgres | continuous_agg |
_timescaledb_catalog | continuous_agg_raw_hypertable_id_idx | index | postgres | continuous_agg |
_timescaledb_catalog | continuous_agg_user_view_schema_user_view_name_key | index | postgres | continuous_agg |
_timescaledb_catalog | continuous_aggs_bucket_function | table | postgres | |
_timescaledb_catalog | continuous_aggs_bucket_function_pkey | index | postgres | continuous_aggs_bucket_function |
_timescaledb_catalog | continuous_aggs_hypertable_invalidation_log | table | postgres | |
_timescaledb_catalog | continuous_aggs_hypertable_invalidation_log_idx | index | postgres | continuous_aggs_hypertable_invalidation_log |
_timescaledb_catalog | continuous_aggs_invalidation_threshold | table | postgres | |
_timescaledb_catalog | continuous_aggs_invalidation_threshold_pkey | index | postgres | continuous_aggs_invalidation_threshold |
_timescaledb_catalog | continuous_aggs_materialization_invalidation_log | table | postgres | |
_timescaledb_catalog | continuous_aggs_materialization_invalidation_log_idx | index | postgres | continuous_aggs_materialization_invalidation_log |
_timescaledb_catalog | continuous_aggs_watermark | table | postgres | |
_timescaledb_catalog | continuous_aggs_watermark_pkey | index | postgres | continuous_aggs_watermark |
_timescaledb_catalog | dimension | table | postgres | |
_timescaledb_catalog | dimension_hypertable_id_column_name_key | index | postgres | dimension |
_timescaledb_catalog | dimension_id_seq | sequence | postgres | |
_timescaledb_catalog | dimension_partition | table | postgres | |
_timescaledb_catalog | dimension_partition_dimension_id_range_start_key | index | postgres | dimension_partition |
_timescaledb_catalog | dimension_pkey | index | postgres | dimension |
_timescaledb_catalog | dimension_slice | table | postgres | |
_timescaledb_catalog | dimension_slice_dimension_id_range_start_range_end_key | index | postgres | dimension_slice |
_timescaledb_catalog | dimension_slice_id_seq | sequence | postgres | |
_timescaledb_catalog | dimension_slice_pkey | index | postgres | dimension_slice |
_timescaledb_catalog | hypertable | table | postgres | |
_timescaledb_catalog | hypertable_associated_schema_name_associated_table_prefix_key | index | postgres | hypertable |
_timescaledb_catalog | hypertable_compression | table | postgres | |
_timescaledb_catalog | hypertable_compression_hypertable_id_orderby_column_index_key | index | postgres | hypertable_compression |
_timescaledb_catalog | hypertable_compression_hypertable_id_segmentby_column_index_key | index | postgres | hypertable_compression |
_timescaledb_catalog | hypertable_compression_pkey | index | postgres | hypertable_compression |
_timescaledb_catalog | hypertable_data_node | table | postgres | |
_timescaledb_catalog | hypertable_data_node_hypertable_id_node_name_key | index | postgres | hypertable_data_node |
_timescaledb_catalog | hypertable_data_node_node_hypertable_id_node_name_key | index | postgres | hypertable_data_node |
_timescaledb_catalog | hypertable_id_seq | sequence | postgres | |
_timescaledb_catalog | hypertable_pkey | index | postgres | hypertable |
_timescaledb_catalog | hypertable_table_name_schema_name_key | index | postgres | hypertable |
_timescaledb_catalog | metadata | table | postgres | |
_timescaledb_catalog | metadata_pkey | index | postgres | metadata |
_timescaledb_catalog | remote_txn | table | postgres | |
_timescaledb_catalog | remote_txn_data_node_name_idx | index | postgres | remote_txn |
_timescaledb_catalog | remote_txn_pkey | index | postgres | remote_txn |
_timescaledb_catalog | tablespace | table | postgres | |
_timescaledb_catalog | tablespace_hypertable_id_tablespace_name_key | index | postgres | tablespace |
_timescaledb_catalog | tablespace_id_seq | sequence | postgres | |
_timescaledb_catalog | tablespace_pkey | index | postgres | tablespace |
_timescaledb_catalog | telemetry_event | table | postgres | |
_timescaledb_config | bgw_job | table | postgres | |
_timescaledb_config | bgw_job_id_seq | sequence | postgres | |
_timescaledb_config | bgw_job_pkey | index | postgres | bgw_job |
_timescaledb_config | bgw_job_proc_hypertable_id_idx | index | postgres | bgw_job |
_timescaledb_internal | bgw_job_stat | table | postgres | |
_timescaledb_internal | bgw_job_stat_pkey | index | postgres | bgw_job_stat |
_timescaledb_internal | bgw_policy_chunk_stats | table | postgres | |
_timescaledb_internal | bgw_policy_chunk_stats_job_id_chunk_id_key | index | postgres | bgw_policy_chunk_stats |
_timescaledb_internal | compressed_chunk_stats | view | postgres | |
_timescaledb_internal | hypertable_chunk_local_size | view | postgres | |
_timescaledb_internal | job_errors | table | postgres | |
public | pg_stat_statements | view | postgres | |
public | pg_stat_statements_info | view | postgres | |
timescaledb_experimental | chunk_replication_status | view | postgres | |
timescaledb_experimental | policies | view | postgres | |
timescaledb_information | chunks | view | postgres | |
timescaledb_information | compression_settings | view | postgres | |
timescaledb_information | continuous_aggregates | view | postgres | |
timescaledb_information | data_nodes | view | postgres | |
timescaledb_information | dimensions | view | postgres | |
timescaledb_information | hypertables | view | postgres | |
timescaledb_information | job_errors | view | postgres | | {security_barrier=true}
timescaledb_information | job_stats | view | postgres | |
timescaledb_information | jobs | view | postgres | |
(104 rows)'DBMS > PostgreSQL' 카테고리의 다른 글
| [PostgreSQL] 아카이브 모드 설정 (1) | 2023.11.19 |
|---|---|
| [PostgreSQL] pg_hint_plan 설치하여 SQL hint 사용 (0) | 2023.11.16 |
| PostgreSQL 외부 접속 허용 설정 (0) | 2023.11.08 |
| [PostgreSQL] 백업/복구(pg_dump, pg_restore) (0) | 2023.02.02 |
| vacuum / vacuum full 의 차이 (0) | 2022.06.21 |
댓글