LightDB常用命令
查看当前连接数据库
lightdb@postgres=# select current_database();
current_database
------------------
postgres
(1 row)
查看当前schema
lightdb@postgres=# select current_schema();
current_schema
----------------
public
(1 row)
查看数据库列表
lightdb@postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+---------+----------+------------+------------+---------------------
lt_test | lightdb | UTF8 | en_US.utf8 | en_US.utf8 |
mydb | lightdb | UTF8 | en_US.utf8 | en_US.utf8 |
mydb1 | lightdb | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | lightdb | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | lightdb | UTF8 | en_US.utf8 | en_US.utf8 | =c/lightdb +
| | | | | lightdb=CTc/lightdb
template1 | lightdb | UTF8 | en_US.utf8 | en_US.utf8 | =c/lightdb +
| | | | | lightdb=CTc/lightdb
(6 rows)
查看schema列表
lightdb@postgres=# \dn
List of schemas
Name | Owner
--------------------------+---------
cron | lightdb
dbms_alert | lightdb
dbms_application_info | lightdb
dbms_assert | lightdb
dbms_job | lightdb
dbms_lob | lightdb
dbms_lock | lightdb
dbms_metadata | lightdb
......
查看当前数据库所有表
lightdb@postgres=# \d
List of relations
Schema | Name | Type | Owner
------------+--------------------------------------+----------+---------
lt_catalog | baselines | table | lightdb
lt_catalog | baselines_bl_id_seq | sequence | lightdb
lt_catalog | bl_samples | table | lightdb
lt_catalog | deps_saved_ddl | table | lightdb
lt_catalog | funcs_list | table | lightdb
lt_catalog | import_queries | table | lightdb
......
查看数据库描述
#指令: \l database_name
lightdb@postgres=# \l postgres
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
----------+---------+----------+------------+------------+-------------------
postgres | lightdb | UTF8 | en_US.utf8 | en_US.utf8 |
(1 row)
查看schema描述
#指令:\dn schema_name
lightdb@postgres=# \dn lt_catalog
List of schemas
Name | Owner
------------+---------
lt_catalog | lightdb
(1 row)
查看表结构
#指令: \d table_name
lightdb@lt_test=# \d app;
Table "public.app"
Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
name | text | | not null |
Indexes:
"app_pkey" PRIMARY KEY, btree (name)
"app_name_idx" btree (name COLLATE "C")
"app_reverse_idx" btree (reverse(name) COLLATE "C")
查看用户
lightdb@postgres=# select * from pg_user;
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
--------------+----------+-------------+----------+---------+--------------+----------+----------+-----------
lightdb | 10 | t | t | t | t | ******** | |
lt_probackup | 24188 | f | f | f | f | ******** | |
(2 rows)
查看权限
lightdb@postgres=# \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
------------+--------------------------------------+----------+-------------------------+-------------------+----------
lt_catalog | baselines | table | | |
lt_catalog | baselines_bl_id_seq | sequence | | |
lt_catalog | bl_samples | table | | |
lt_catalog | deps_saved_ddl | table | lightdb=arwdDxt/lightdb+| |
| | | =arwdD/lightdb | |
lt_catalog | funcs_list | table | | |
lt_catalog | import_queries | table | | |
lt_catalog | import_queries_version_order | table | | |
lt_catalog | indexes_list | table | | |
......
查看当前进程id
lightdb@postgres=# select pg_backend_pid();
pg_backend_pid
----------------
7937
(1 row)
查看会话连接信息
lightdb@postgres=# select * from pg_stat_activity order by backend_start desc limit 1;
-[ RECORD 1 ]----+--------------------------------------------------------------------
datid | 14198
datname | postgres
pid | 11503
leader_pid |
usesysid | 10
usename | lightdb
application_name | ltsql
client_addr | ::1
client_hostname |
client_port | 51308
backend_start | 2023-01-17 16:35:37.42709+08
xact_start | 2023-01-17 16:38:24.147109+08
query_start | 2023-01-17 16:38:24.147109+08
state_change | 2023-01-17 16:38:24.147113+08
wait_event_type |
wait_event |
state | active
backend_xid |
backend_xmin | 147967
query | select * from pg_stat_activity order by backend_start desc limit 1;
backend_type | client backend
查看参数配置
#查询配置表:pg_settings
lightdb@postgres=# select * from pg_settings where name like '%buffer%';
name | setting | unit | category | short_desc | extra_desc | context | vartype |
source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline |
pending_restart
-----------------+---------+------+----------------------------+--------------------------------------------------------------------+------------+------------+---------+--
------------------+---------+------------+----------+----------+-----------+--------------------------------------------------------------------------------+------------+-
----------------
lt_ring_buffers | 32 | 8kB | Resource Usage / Memory | Sets the number of ring buffers used by each session. | | user | integer | d
efault | 32 | 1073741823 | | 32 | 32 | | |
f
shared_buffers | 120832 | 8kB | Resource Usage / Memory | Sets the number of shared memory buffers used by the server. | | postmaster | integer | c
onfiguration file | 16 | 1073741823 | | 1024 | 120832 | /usr/local/lightdb/lightdb-x/13.8-22.3/bin/../data/defaultCluster/lightdb.conf | 857 |
f
temp_buffers | 8192 | 8kB | Resource Usage / Memory | Sets the maximum number of temporary buffers used by each session. | | user | integer | c
onfiguration file | 100 | 1073741823 | | 1024 | 8192 | /usr/local/lightdb/lightdb-x/13.8-22.3/bin/../data/defaultCluster/lightdb.conf | 865 |
f
wal_buffers | 16384 | 8kB | Write-Ahead Log / Settings | Sets the number of disk-page buffers in shared memory for WAL. | | postmaster | integer | c
onfiguration file | -1 | 262143 | | -1 | 16384 | /usr/local/lightdb/lightdb-x/13.8-22.3/bin/../data/defaultCluster/lightdb.conf | 846 |
f
(4 rows)
#也可以用show查看
lightdb@postgres=# show shared_buffers;
shared_buffers
----------------
944MB
(1 row)
lightdb@postgres=# show wal_buffers;
wal_buffers
-------------
128MB
(1 row)
查看执行计划
lightdb@postgres=# explain select * from pg_settings where name like '%buffer%';
QUERY PLAN
------------------------------------------------------------------------------
Function Scan on pg_show_all_settings a (cost=0.00..12.50 rows=1 width=485)
Filter: (name ~~ '%buffer%'::text)
(2 rows)
lightdb安装之后,连接数据后,可以看到在lt_catalog schema下有几张默认表,视图,序列生成器,用于收集数据库运行期间的数据
lightdb@lt_test=# \d
List of relations
Schema | Name | Type | Owner | PS |
------------+--------------------------------------+----------+---------
lt_catalog | deps_saved_ddl | table | lightdb
lt_catalog | lt_sm_key_ext_stat | table | lightdb
lt_catalog | lt_sm_key_stat | table | lightdb
lt_catalog | lt_stat_activity | view | lightdb | 会话连接信息 |
lt_catalog | pg_buffercache | view | lightdb
lt_catalog | pg_show_plans | view | lightdb
lt_catalog | pg_stat_statements | view | lightdb
lt_catalog | sample_active_session_history | table | lightdb
lt_catalog | sample_active_session_history_id_seq | sequence | lightdb
lt_catalog | sample_active_session_profile | table | lightdb
lt_catalog | sample_active_session_profile_id_seq | sequence | lightdb
lt_catalog | sample_activity_history | table | lightdb
lt_catalog | sample_activity_history_id_seq | sequence | lightdb
lt_catalog | sample_activity_profile | table | lightdb
lt_catalog | sample_activity_profile_id_seq | sequence | lightdb