1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
|
--
-- This file is licensed under the Apache License, see LICENSE-APACHE
-- at the top level directory of the TimescaleDB distribution.
-- This script will dump relevant meta data from internal TimescaleDB tables
-- that can help our engineers trouble shoot.
--
-- usage:
-- psql [your connect flags] -d your_timescale_db < dump_meta_data.sql > dumpfile.txt
\echo 'TimescaleDB meta data dump'
\echo '<exclude_from_test>'
\echo 'Date, git commit, and extension version can change without it being an error.'
\echo 'Adding this tag allows us to run regression tests on this script file.'
select now();
\echo 'Postgres version'
select version();
\echo 'Build tag'
\set ON_ERROR_STOP 0
SELECT * FROM _timescaledb_internal.get_git_commit();
\set ON_ERROR_STOP 1
\dx
\echo '</exclude_from_test>'
\echo 'List of tables'
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 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('r','p','')
AND n.nspname <> 'pg_catalog'
AND n.nspname !~ '^pg_toast'
AND n.nspname <> 'information_schema'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
\echo 'List of hypertables'
SELECT * FROM _timescaledb_catalog.hypertable;
\echo 'List of chunk indexes'
SELECT ch.id AS chunk_id, ci.indexrelid::regclass::text AS index_name, h.id AS hypertable_id
FROM _timescaledb_catalog.hypertable h
JOIN _timescaledb_catalog.chunk ch ON ch.hypertable_id = h.id
JOIN pg_index ci ON ci.indrelid = format('%I.%I', ch.schema_name, ch.table_name)::regclass
ORDER BY h.id, ch.id, ci.indrelid::regclass::text;
\echo 'Size of hypertables'
SELECT hypertable,
table_bytes,
index_bytes,
toast_bytes,
total_bytes
FROM (
SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
SELECT
pgc.oid::regclass::text as hypertable,
sum(pg_total_relation_size('"' || c.schema_name || '"."' || c.table_name || '"'))::bigint as total_bytes,
sum(pg_indexes_size('"' || c.schema_name || '"."' || c.table_name || '"'))::bigint AS index_bytes,
sum(pg_total_relation_size(reltoastrelid))::bigint AS toast_bytes
FROM
_timescaledb_catalog.hypertable h,
_timescaledb_catalog.chunk c,
pg_class pgc,
pg_namespace pns
WHERE c.hypertable_id = h.id
AND pgc.relname = h.table_name
AND pns.oid = pgc.relnamespace
AND pns.nspname = h.schema_name
AND relkind = 'r'
AND c.dropped is false
GROUP BY pgc.oid
) sub1
) sub2;
\echo 'Chunk sizes:'
SELECT chunk_id,
chunk_table,
partitioning_columns,
partitioning_column_types,
partitioning_hash_functions,
ranges,
table_bytes,
index_bytes,
toast_bytes,
total_bytes
FROM (
SELECT *,
total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes
FROM (
SELECT c.id as chunk_id,
'"' || c.schema_name || '"."' || c.table_name || '"' as chunk_table,
pg_total_relation_size('"' || c.schema_name || '"."' || c.table_name || '"') AS total_bytes,
pg_indexes_size('"' || c.schema_name || '"."' || c.table_name || '"') AS index_bytes,
pg_total_relation_size(reltoastrelid) AS toast_bytes,
array_agg(d.column_name ORDER BY d.interval_length, d.column_name ASC) as partitioning_columns,
array_agg(d.column_type ORDER BY d.interval_length, d.column_name ASC) as partitioning_column_types,
array_agg(d.partitioning_func_schema || '.' || d.partitioning_func ORDER BY d.interval_length, d.column_name ASC) as partitioning_hash_functions,
array_agg('[' || _timescaledb_functions.range_value_to_pretty(range_start, column_type) ||
',' ||
_timescaledb_functions.range_value_to_pretty(range_end, column_type) || ')' ORDER BY d.interval_length, d.column_name ASC) as ranges
FROM
_timescaledb_catalog.hypertable h,
_timescaledb_catalog.chunk c,
_timescaledb_catalog.chunk_constraint cc,
_timescaledb_catalog.dimension d,
_timescaledb_catalog.dimension_slice ds,
pg_class pgc,
pg_namespace pns
WHERE pgc.relname = h.table_name
AND pns.oid = pgc.relnamespace
AND pns.nspname = h.schema_name
AND relkind = 'r'
AND c.hypertable_id = h.id
AND c.id = cc.chunk_id
AND cc.dimension_slice_id = ds.id
AND ds.dimension_id = d.id
AND c.dropped is false
GROUP BY c.id, pgc.reltoastrelid, pgc.oid ORDER BY c.id
) sub1
) sub2;
\echo 'Hypertable index sizes'
SET search_path TO pg_catalog, pg_temp;
SELECT
i.indrelid::regclass AS hypertable,
i.indexrelid::regclass AS index_name,
public.hypertable_index_size(i.indexrelid::regclass) AS index_bytes
FROM _timescaledb_catalog.hypertable h
JOIN pg_index i ON i.indrelid = format('%I.%I',h.schema_name,h.table_name)::regclass
ORDER BY i.indrelid::regclass::text, i.indexrelid::regclass::text;
RESET search_path;
|
