2023-01-17 LightDB常用命令.md

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)

• lt_catalog schema

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