PostgreSQL教程（2）客户端工具psql的使用

一、命令行客户端

psql 是 PostgreSQL 的命令行客户端工具，类似于MySQL中的mysql、Oracle中的sqlplus。通过psql可以实现对 PostgreSQL 数据库的连接和各种管理工作，比如远程登录数据库、 执行 SQL 脚本、实现用户权限管理等

1、psql 语法格式

#psql语法格式
psql -h <主机名或IP地址> -p <端口> [数据库名] [用户名]

#非交互模式，使用-f选项可以在连接数据库后执行指定的脚本
psql -h <主机名或IP地址> -p <端口> [数据库名] [用户名] -f test.sql

2、psql 常用命令

\l：查看当前实例中的数据库，类似mysql中的show databases

\c：查看当前数据库或者切换到指定数据库

\d：显示当前数据库中所有表、索引、视图等，如果接上表名可以显示该表结构、接上索引名则显示索引信息，支持通配符模糊匹配

\d+：比\d显示更多信息

\dn：显示所有模式

\db：显示所有表空间信息

\du：显示所有角色或用户

\dp：显示分配的权限信息

\x：将查询结果单行显示，适用于列多而显示不全的场景

\timing on：显示SQL执行时间

\i file：执行指定的SQL脚本

\q：退出psql

3、psql 用例

· 连接数据库

psql -h 192.168.0.103 -p 5432 -d tanglu_database -U tanglu
#-U：指定用户名
#-W：进行交互式密码验证
#-w：非交互式登录
#-p：指定服务端口
#-h：指定服务地址
#-d：指定连接数据库，默认为postgres
#-f：登录时同时指定指定的sql文件，类似mysql -e
#连接参数也可以用环境变量指定，这样就不用每次手写参数
export PGDATABASE=tanglu_database
export PGHOST=192.168.0.103
export PGPORT=5432
export PGDUSER=postgres

· 查看帮助

通过 \? 可以查看所有psql子命令的用法，如果忘记了某个对象管理的命令可以通过这个帮助进行查看

· 查看连接信息

通过 \conninfo 可以查看当前用户与数据库的连接情况，类似操作系统 who 命令

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/run/postgresql" at port "5432".

· 切换数据库

#没有指定库名会直接显示当前连接情况
postgres=# \c
You are now connected to database "postgres" as user "postgres".

#使用\c切换到指定库
postgres=# \c tanglu_database;
You are now connected to database "tanglu_database" as user "postgres".

#同时切换数据库和用户
postgres=# \c tanglu_database tanglu;
You are now connected to database "tanglu_database" as user "tanglu".

· 列出实例中所有数据库

tanglu_database=# \l
                                     List of databases
      Name       |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------------+----------+----------+-------------+-------------+-----------------------
 postgres        | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 tanglu_database | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                 |          |          |             |             | postgres=CTc/postgres
 template1       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                 |          |          |             |             | postgres=CTc/postgres
(4 rows)

· 显示当前数据库中的表

通过 \dt 显示 search_path 覆盖到的 schema 里的表，默认是public。如果要指定查看某个schema下的表需要加上schema名

aglma=# \dt   #这里查看的schema实际是public
                         List of relations
 Schema |                Name                | Type  |    Owner    
--------+------------------------------------+-------+-------------
 public | t_0temp_jh_jhag_dxhhb8_t001        | table | aglma_qa
 public | t_0temp_jh_jhag_dxhhb8_t002        | table | aglma_qa
 public | t_0temp_jh_tmp_jhag_dcnrz1_t001    | table | aglma_qa
 public | t_0temp_runin_djrrzd               | table | aglma_qa
 public | t_0temp_runin_djrrzd1              | table | aglma_qa
 public | t_0temp_runin_djrrzd3              | table | aglma_qa
 public | t_0temp_runin_djrrzd4              | table | aglma_qa
 public | t_0temp_runin_djrrzd8              | table | aglma_qa
 public | t_0temp_runin_nyl                  | table | aglma_qa
 public | t_0temp_runin_ztm001               | table | aglma_qa
 
 
  
aglma=# \dt ods_ems.*      #查看ods_ems这个schema下的所有表
                       List of relations
 Schema  |             Name              | Type  |    Owner    
---------+-------------------------------+-------+-------------
 ods_ems | ods_ems_comm_energy_base_dict | table | group_admin
 ods_ems | ods_ems_rz_index_analy_day    | table | group_admin
(2 rows)

· 查看表权限

通过元命令 \dp [schema.]pattern 列出指定 schema 下所有表的权限。Access privileges 这一列输出内容代表：角色名=权限简写/授权人

= 前面是被授权的角色，比如 aglma_qa=

= 后面一串字母是具体权限，每个字母对应一种操作：r=SELECT、w=UPDATE、a=INSERT、d=DELETE、D=TRUNCATE、x=REFERENCES、t=TRIGGER，/ 后面是谁授予的这个权限，比如 /group_admin 表示这条 GRANT 是 group_admin 执行的

aglma=# \dp acdwd.*         #查看acdwd这个schema下所有表的授权情况
                                                        Access privileges
 Schema |                   Name                    |   Type   |        Access privileges         | C
olumn privileges | Policies 
--------+-------------------------------------------+----------+----------------------------------+--
-----------------+----------
 acdwd  | T_DWD_FACT_JHAG_DCNDX1                    | table    | group_admin=arwdDxtm/group_admin+|  
                 | 
        |                                           |          | aglma_qa=arwdD/group_admin       |  
                 | 
 acdwd  | T_DWD_FACT_JHAG_DCNDX1_ID_seq             | sequence | group_admin=arwdDxtm/group_admin+|  
                 | 
        |                                           |          | aglma_qa=rwU/group_admin

· 查看当前库所有表的详细信息

通过 \d+打印出数据库表的详细信息，包含表大小、属主等

tanglu_database=# \d+
                                   List of relations
 Schema | Name | Type  |  Owner   | Persistence | Access method |  Size   | Description 
--------+------+-------+----------+-------------+---------------+---------+-------------
 public | t1   | table | postgres | permanent   | heap          | 0 bytes | 
 public | t2   | table | postgres | permanent   | heap          | 0 bytes | 
 public | t3   | table | postgres | permanent   | heap          | 0 bytes | 
(3 rows)

· 查看表结构

tanglu_database=# \d t1;
                        Table "public.t1"
 Column |         Type          | Collation | Nullable | Default 
--------+-----------------------+-----------+----------+---------
 id     | integer               |           |          | 
 name   | character varying(10) |           |          |

· 查看索引

通过 \di 打印指定表的索引信息，display index

tanglu_database=# \di

· 查看用户或者角色信息

通过\du 打印用户和权限信息，display user

tanglu_database=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

· 查看PGSQL所支持的参数

类似show variables，同样支持通配符

tanglu_database=# \df *size*

· 导入SQL文件

tanglu_database=# \i test.sql
test.sql: No such file or directory

· 导出查询结果到本地

#该查询结果都会存储到test.sql中
tanglu_database=# \o test.sql
select * from t1 where id >10;

· 查看SQL执行时长

tanglu_database=# \timing
select * from t1;
Time: 202.372ms

· 查看数据库参数配置

postgres=#  show $具体参数名
postgres=#  show maintenance_work_mem

二、图形化客户端

PostgreSQL官方提供了一款图形化管理工具pgAdmin，官网下载地址为https://www.pgadmin.org/download/。在安装Windows版的PGSQL时也可以勾选安装该工具pgAdmin，该工具安装后的使用方式类似SQLyog等工具，并且进行了汉化

数据库postgresqlpgsql