digoal
2023-12-24
PostgreSQL , PolarDB , DuckDB , pgdd , PostgreSQL Data Dictionary , 数据库概览
PostgreSQL Data Dictionary, 快速了解数据库概览, 后期将集成到宇宙最强PostgreSQL学习镜像中.
《2023-PostgreSQL/DuckDB/MySQL/PolarDB-X Docker镜像学习环境 ARM64版, 已集成热门插件和工具》
《2023-PostgreSQL/DuckDB/MySQL/PolarDB-X Docker镜像学习环境 AMD64版, 已集成热门插件和工具》
例如
postgres=# select * from database;
-[ RECORD 1 ]---+---------
oid | 13757
db_name | postgres
db_size | 163 MB
schema_count | 2
table_count | 7
size_in_tables | 150 MB
view_count | 9
size_in_views | 0 bytes
extension_count | 2
postgres=# select * from tables ;
...
-[ RECORD 7 ]-----------+---------------------------------------------------------------
s_name | public
t_name | pgbench_accounts
data_type | table
owned_by | postgres
size_pretty | 128 MB
size_bytes | 134365184
rows | 1000000
bytes_per_row | 134
size_plus_indexes_bytes | 156852224
size_plus_indexes | 150 MB
description |
system_object | f
data_source |
sensitive |
oid | 25277
postgres=# select * from schemas ;
-[ RECORD 1 ]-----------+-------------------------------------------------------------------------------
s_name | dd
owner | postgres
data_source | Manually maintained
sensitive | f
description | Schema for Data Dictionary objects. See https://github.com/rustprooflabs/pgdd
system_object | f
table_count | 3
view_count | 9
function_count | 10
size_pretty | 48 kB
size_plus_indexes | 144 kB
size_bytes | 49152
size_plus_indexes_bytes | 147456
-[ RECORD 2 ]-----------+-------------------------------------------------------------------------------
s_name | public
owner | postgres
data_source |
sensitive |
description | standard public schema
system_object | f
table_count | 4
view_count | 0
function_count | 0
size_pretty | 128 MB
size_plus_indexes | 150 MB
size_bytes | 134447104
size_plus_indexes_bytes | 156966912
克隆项目
docker exec -ti pg bash
cd /tmp
git clone --depth 1 https://github.com/rustprooflabs/pgdd.git
配置cargo源, 参考: https://mirrors.ustc.edu.cn/help/crates.io-index.html
# export CARGO_HOME=/root
# mkdir -vp ${CARGO_HOME:-$HOME/.cargo}
# vi ${CARGO_HOME:-$HOME/.cargo}/config
[source.crates-io]
replace-with = 'ustc'
[source.ustc]
registry = "sparse+https://mirrors.ustc.edu.cn/crates.io-index/"
安装 pgdd 插件
cd /tmp/pgdd
grep pgrx Cargo.toml
cargo install --locked --version 0.10.2 cargo-pgrx
cargo pgrx init # create PGRX_HOME 后, 立即ctrl^c 退出
cargo pgrx init --pg14=`which pg_config` # 不用管报警
PGRX_IGNORE_RUST_VERSIONS=y cargo pgrx install --pg-config `which pg_config`
Installing extension
Copying control file to /usr/share/postgresql/14/extension/pgdd.control
Copying shared library to /usr/lib/postgresql/14/lib/pgdd.so
Discovering SQL entities
Discovered 14 SQL entities: 0 schemas (0 unique), 10 functions, 0 types, 0 enums, 4 sqls, 0 ords, 0 hashes, 0 aggregates, 0 triggers
Writing SQL entities to /usr/share/postgresql/14/extension/pgdd--0.5.2.sql
使用 pgdd:
postgres=# create extension pgdd ;
CREATE EXTENSION
postgres=# set search_path ="$user",dd,public;
SET
postgres=# \dv+
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+--------------------+------+----------+-------------+---------+----------------------------------------------------------------------------------------------------
dd | columns | view | postgres | permanent | 0 bytes | Data dictionary view: Lists columns, excluding system columns.
dd | database | view | postgres | permanent | 0 bytes | Data dictionary view: Provides basic statistics for the current database.
dd | functions | view | postgres | permanent | 0 bytes | Data dictionary view: Lists functions, excluding system functions.
dd | index | view | postgres | permanent | 0 bytes | Data dictionary view: Details about indexes in current database.
dd | partition_children | view | postgres | permanent | 0 bytes | Data dictionary view: Lists individual partitions (children) of partitioned tables.
dd | partition_parents | view | postgres | permanent | 0 bytes | Data dictionary view: Lists parent partition tables with aggregate details about child partitions.
dd | schemas | view | postgres | permanent | 0 bytes | Data dictionary view: Lists schemas, excluding system schemas.
dd | tables | view | postgres | permanent | 0 bytes | Data dictionary view: Lists tables, excluding system tables.
dd | views | view | postgres | permanent | 0 bytes | Data dictionary view: Lists views, excluding system views.
(9 rows)
postgres=# \dt+
List of relations
Schema | Name | Type | Owner | Persistence | Access method | Size | Description
--------+-------------+-------+----------+-------------+---------------+-------+----------------------------------------------------------------
dd | meta_column | table | postgres | permanent | heap | 16 kB | User definable meta-data at the schema + table + column level.
dd | meta_schema | table | postgres | permanent | heap | 16 kB | User definable meta-data at the schema level.
dd | meta_table | table | postgres | permanent | heap | 16 kB | User definable meta-data at the schema + table level.
(3 rows)
root@56000550f873:/tmp/pgdd# pgbench -i -s 10
dropping old tables...
NOTICE: table "pgbench_accounts" does not exist, skipping
NOTICE: table "pgbench_branches" does not exist, skipping
NOTICE: table "pgbench_history" does not exist, skipping
NOTICE: table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
1000000 of 1000000 tuples (100%) done (elapsed 0.87 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 2.07 s (drop tables 0.00 s, create tables 0.01 s, client-side generate 0.90 s, vacuum 0.30 s, primary keys 0.86 s).
root@56000550f873:/tmp/pgdd# psql
psql (14.10 (Debian 14.10-1.pgdg110+1))
Type "help" for help.
postgres=# set search_path ="$user",dd,public;
SET
postgres=# \x
Expanded display is on.
postgres=# select * from database;
-[ RECORD 1 ]---+---------
oid | 13757
db_name | postgres
db_size | 163 MB
schema_count | 2
table_count | 7
size_in_tables | 150 MB
view_count | 9
size_in_views | 0 bytes
extension_count | 2
postgres=# select * from tables ;
...
-[ RECORD 7 ]-----------+---------------------------------------------------------------
s_name | public
t_name | pgbench_accounts
data_type | table
owned_by | postgres
size_pretty | 128 MB
size_bytes | 134365184
rows | 1000000
bytes_per_row | 134
size_plus_indexes_bytes | 156852224
size_plus_indexes | 150 MB
description |
system_object | f
data_source |
sensitive |
oid | 25277
postgres=# select pg_get_viewdef('tables');
-[ RECORD 1 ]--+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pg_get_viewdef | SELECT tables.s_name, +
| tables.t_name, +
| tables.data_type, +
| tables.owned_by, +
| tables.size_pretty, +
| tables.size_bytes, +
| tables.rows, +
| tables.bytes_per_row, +
| tables.size_plus_indexes_bytes, +
| tables.size_plus_indexes, +
| tables.description, +
| tables.system_object, +
| tables.data_source, +
| tables.sensitive, +
| tables.oid +
| FROM tables() tables(s_name, t_name, data_type, owned_by, size_pretty, size_bytes, rows, bytes_per_row, size_plus_indexes_bytes, size_plus_indexes, description, system_object, data_source, sensitive, oid)+
| WHERE (NOT tables.system_object);
postgres=# \df
List of functions
-[ RECORD 1 ]-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Schema | dd
Name | about
Result data type | text
Argument data types |
Type | func
-[ RECORD 2 ]-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Schema | dd
Name | columns
Result data type | TABLE(s_name text, source_type text, t_name text, c_name text, data_type text, "position" bigint, description text, data_source text, sensitive boolean, system_object boolean, default_value text, generated_column boolean)
Argument data types |
Type | func
-[ RECORD 3 ]-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Schema | dd
Name | database
Result data type | TABLE(oid oid, db_name text, db_size text, schema_count bigint, table_count bigint, size_in_tables text, view_count bigint, size_in_views text, extension_count bigint)
Argument data types |
Type | func
-[ RECORD 4 ]-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Schema | dd
Name | functions
Result data type | TABLE(s_name text, f_name text, result_data_types text, argument_data_types text, owned_by text, proc_security text, access_privileges text, proc_language text, source_code text, description text, system_object boolean)
Argument data types |
Type | func
-[ RECORD 5 ]-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Schema | dd
Name | index
Result data type | TABLE(oid oid, s_name text, t_name text, i_name text, key_columns smallint, total_columns smallint, primary_key boolean, unique_index boolean, valid_index boolean, partial_index boolean, rows_indexed real, index_size text, index_size_bytes bigint, system_object boolean)
Argument data types |
Type | func
-[ RECORD 6 ]-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Schema | dd
Name | partition_children
Result data type | TABLE(oid oid, s_name text, t_name text, parent_oid oid, parent_name text, declarative_partition boolean, partition_expression text)
Argument data types |
Type | func
-[ RECORD 7 ]-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Schema | dd
Name | partition_parents
Result data type | TABLE(oid oid, s_name text, t_name text, partition_type text, partitions bigint)
Argument data types |
Type | func
-[ RECORD 8 ]-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Schema | dd
Name | schemas
Result data type | TABLE(s_name text, owner text, data_source text, sensitive boolean, description text, system_object boolean, table_count bigint, view_count bigint, function_count bigint, size_pretty text, size_plus_indexes text, size_bytes bigint, size_plus_indexes_bytes bigint)
Argument data types |
Type | func
-[ RECORD 9 ]-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Schema | dd
Name | tables
Result data type | TABLE(s_name text, t_name text, data_type text, owned_by text, size_pretty text, size_bytes bigint, rows bigint, bytes_per_row bigint, size_plus_indexes_bytes bigint, size_plus_indexes text, description text, system_object boolean, data_source text, sensitive boolean, oid oid)
Argument data types |
Type | func
-[ RECORD 10 ]------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Schema | dd
Name | views
Result data type | TABLE(s_name text, v_name text, view_type text, owned_by text, rows bigint, size_pretty text, size_bytes bigint, size_plus_indexes text, size_plus_indexes_bytes bigint, description text, system_object boolean, oid oid)
Argument data types |
Type | func
https://rustprooflabs.github.io/pgdd/query.html
