psql常用命令使用
最近更新时间: 2024-06-12 15:06:00
连接到一个数据库实例
- 使用参数连接
[pgxz@VM_0_3_centos root]$ psql -h 172.16.0.29 -p 15432 -U tbase -d postgres
psql (PostgreSQL 10 (TBase 2.01))
Type "help" for help.
postgres=# - 使用conninfo字符串或者一个URI
[pgxz@VM_0_3_centos root]$ psql
postgresql://pgxz@172.16.0.29:15432/postgres
psql (PostgreSQL 10 (TBase 2.01))
Type "help" for help.
postgres=# 建立一个新连接
- 查看当前连接会话pid
postgres=# select pg_backend_pid();
pg_backend_pid
--
2408
(1 row)- 连接到当前库
postgres=# \c
You are now connected to database "postgres" as user "tbase".
postgres=# select pg_backend_pid();
pg_backend_pid
--
2426
(1 row)- 连接到其他库
postgres=# \c template1
You are now connected to database "template1" as user "tbase".
template1=#连接到其他用户
postgres=# \c - postgres You are now connected to database "postgres" as user "postgres".连接到其他服务器上的库
postgres=# \c postgres tbase 172.16.0.5 11000
You are now connected to database "postgres" as user "tbase" on host
"172.16.0.5" at port "11000".显示和设置该连接当前运行参数
- 显示当前连接用户
postgres=# SELECT CURRENT_USER;
current_user
--------------
pgxz
(1 row)- 显示当前连接的schema
postgres=# show search_path ;
search_path
----------------
"$user",public
(1 row)- 显示节点参数值
postgres=# show work_mem ;
work_mem
----------
4MB
(1 row)- 设置当前连接的运行参数
postgres=# set search_path = "$user",public,pg_catalog;
SET
postgres=# set work_mem = '8MB';
SET- 打开和关闭显示每个sql语句执行的时间
postgres=# \timing on
Timing is on.
postgres=# select count(1) from tbase;
count
-------
10000
(1 row)
Time: 5.139 ms
postgres=# \timing off
Timing is off.
postgres=# select count(1) from tbase;
count
-------
10000
(1 row)配置行列显示格式
postgres=# \x on Expanded display is on. postgres=# select * from tbase where id=1; -[ RECORD 1 ] id | 1 mc | 1 -[ RECORD 2 ] id | 1 mc | 2 -[ RECORD 3 ] id | 1 mc | 2 postgres=# \x off Expanded display is off. postgres=# select * from tbase where id=1; id | mc ----+---- 1 | 1 1 | 2 1 | 2 (3 rows)显示和配置客户端编码
postgres=# \encoding
UTF8配置客户端编码为SQL_ASCII
postgres=# \encoding sql_ascii
postgres=# \encoding
SQL_ASCII退出连接
postgres=# \q
psql执行一个sql命令
- 显示标题
[pgxz@VM_0_3_centos root]$ psql -h 172.16.0.29 -p 15432 -U tbase -d
postgres -c "select count(1) from pg_class"
count
-------
317
(1 row)- 不显示标题
[pgxz@VM_0_3_centos root]$ psql -h 172.16.0.29 -p 15432 -U tbase -d
postgres -t -c "select count(1) from pg_class"
317psql执行一个sql文件中所有命令
- 在外部执行
[pgxz@VM_0_3_centos ~]$ cat /data/tbase/tbase.sql
set search_path = public;
insert into tbase values(1,2);
select count(1) from tbase;
[pgxz@VM_0_3_centos ~]$ psql -h 172.16.0.29 -p 15432 -U tbase -d postgres
-f /data/tbase/tbase.sql
SET
INSERT 0 1
count
-------
10001
(1 row)- 在内部执行
[pgxz@VM_0_3_centos ~]$ psql -h 172.16.0.29 -p 15432 -U tbase -d postgres
psql (PostgreSQL 10 (TBase 2.01))
Type "help" for help.
postgres=# \i /data/tbase/tbase.sql
SET
INSERT 0 1
count
-------
10002
(1 row)数据库相关操作
- 显示当前集群中所有数据库
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+------------+------------+-------------------
postgres | tbase | UTF8 | en_US.utf8 | en_US.utf8 |
template0| tbase | UTF8 | en_US.utf8 | en_US.utf8| =c/tbase +
| | | | | tbase=CTc/tbase
template1 | tbase | UTF8 | en_US.utf8 | en_US.utf8 | =c/tbase +
| | | | | tbase=CTc/tbase
(3 rows)- \l+显示当前当前集群中所有数据库（包含库大小及注释）
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size
| Tablespace | Description
-----------+-------+----------+------------+------------+-------------------+-------+------------+--------------------------------------------
postgres | tbase | UTF8 | en_US.utf8 | en_US.utf8 | | 31 MB |
pg_default | default administrative connection database
template0 | tbase | UTF8 | en_US.utf8 | en_US.utf8 | =c/tbase +| 27 MB
| pg_default | unmodifiable empty database
| | | | | tbase=CTc/tbase | | |
template1 | tbase | UTF8 | en_US.utf8 | en_US.utf8 | =c/tbase +| 27 MB
| pg_default | default template for new databases
| | | | | tbase=CTc/tbase | | |
(3 rows)- 创建一个新库
postgres=# create database mydb;
CREATE DATABASE模式相关操作
- \dn显示当前库所有模式
postgres=# \dn
List of schemas
Name | Owner
--------+-------
pgxc | tbase
public | tbase
(2 rows)- \dn+显示当前库所有模式（包含注释）
postgres=# \dn+
List of schemas
Name | Owner | Access privileges | Description
--------+-------+-------------------+------------------------
pgxc | tbase | |
public | tbase | tbase=UC/tbase +| standard public schema
| | =UC/tbase |
(2 rows)- 创建一个新模式
postgres=# create schema mysche;
CREATE SCHEMA用户相关操作
- \du显示当前集群中所有数据库用户
postgres=# \du
List of roles
Role name | Attributes | Member of
---------------+------------------------------------------------------------+-----------
audit_admin | No inheritance | {}
mls_admin | No inheritance | {}
tbase | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
tbase01_admin | Superuser, Create role, Create DB | {}- \du+显示当前集群中所有数据库用户（包含注释）
postgres=# \du+
List of roles
Role name | Attributes | Member of | Description
---------------+------------------------------------------------------------+-----------+-------------
audit_admin | No inheritance | {} |
mls_admin | No inheritance | {} |
tbase | Superuser, Create role, Create DB, Replication, Bypass RLS | {} |
tbase01_admin | Superuser, Create role, Create DB | {} |- 创建一个新的用户
postgres=# create role pgxc with login ;
CREATE ROLE
postgres=# create user pgxz ;（使用create user，那么用户自动拥有login权限）- 配置用户密码
postgres=# \password pgxc
Enter new password:
Enter it again:表相关操作
建立数据表
postgres=# create table tbase(id int,mc text) distribute by shard(id); CREATE TABLE\d查看表结构，包括使用的触发器
postgres=# \d tbase
Table "public.tbase"
Column | Type | Modifiers
--------+---------+-----------
id | integer|
mc | text |- \d+查看表结构（包含注释），表类型，分布节点
postgres=# \d+ tbase
Table "public.tbase"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+----------+--------------+-------------
id | integer | | plain | |
mc | text | | extended | |
Has OIDs: no
Distribute By SHARD(id)
Location Nodes: ALL DATANODES- \dt查看表列表
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+--------------+-------+-------
public | t_time_range | table | tbase
public | tbase | table | tbase
(2 rows)- \dt+查看表列表详细信息，包含表大小和注释
这里连接的节点如果是cn的话，表大小为所有dn节点大小之和，否则为只是该节点的表大小
postgres=# \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+--------------+-------+-------+---------+--------------------
public | t_time_range | table | tbase | 0 bytes | 这是一个日期分区表
public | tbase | table | tbase | 576 kB |
(2 rows)- \dt+显示某个模式下的所有表
postgres=# \dt+ pgxc.*
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------------+-------+-------+---------+-------------
pgxc | order_main | table | tbase | 0 bytes |
(1 row)- \dt+表名 显示某个表的详细信息
postgres=# \dt+ tbase
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-------+-------+-------+--------+-------------
public | tbase | table | tbase | 576 kB |
(1 row)- \dt+通配符列出适配的表
postgres=# \dt+ t*
List of relations
Schema | Name | Type | Owner | Size | Description
--------+--------------+-------+-------+---------+--------------------
public | t_time_range | table | tbase | 0 bytes | 这是一个日期分区表
public | tbase | table | tbase | 576 kB |
(2 rows)插件管理
- 查看当前库加载了那些插件
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------------+---------+------------+-----------------------------------------------------------
pg_stat_statements | 1.1 | public | track execution statistics of all SQL
statements executed
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)- 给当前库加载插件
postgres=# create extension pg_stat_statements;
CREATE EXTENSION- 删除当前库某个插件
postgres=# drop extension pg_stat_statements;
DROP EXTENSIONsql帮助命令
postgres=# \h
postgres=# \h create table;
\h 可以查看一些sql命令的使用方法。