CNDBA社区
在之前的博客，我们了解的PG的相关信息，如下：
PostgreSQL 学习笔记（1） — PG 概述
https://www.cndba.cn/dave/article/116370
PostgreSQL 学习笔记（2） — PG 版本发布策略 和 生命周期说明
https://www.cndba.cn/dave/article/116372
PostgreSQL 学习笔记（3） — PG 单实例安装手册
https://www.cndba.cn/dave/article/116374
PostgreSQL 学习笔记（4） — PG 启动 与 关闭
https://www.cndba.cn/dave/article/116375
1 psql 工具说明
psql 是postgresql中一个命令行交互式客户端工具，类似Oracle中的sqlplus。 psql的命令都是以斜杠”/”开头的。
在安装PostgreSQL的数据库时，会建立一个与初始化数据库时的操作系统用户同名的数据库用户，同时，这个用户是数据库的超级用户，在这个OS用户下，登录数据库时执行的是操作系统认证，不需要用户名和密码，当然也可以通过修改 pg_hba.conf文件来要求输入密码。
使用psql-1查看数据库信息∶
-bash-4.2$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | 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
(3 rows)
-bash-4.2$
安装PG后，默认会有一个叫postgres的数据库，还有两个模板数据库template0和templatel。当用户在建数据库时，默认是从模板数据库template1克隆出来的，所以通常可以定制templatel数据库中的内容，如往 templatel 中添加一些表和函数，这样后续创建的数据库就会继承 templatel中的内容，也会拥有这些表和函数。而 template0是一个最简化的模板库，创建数据库时，如果明确指定从此数据库中继承，将创建出一个最简化的数据库。
我们这里使用psql 连接pg后创建一个测试数据库：
postgres=# create database cndba;
CREATE DATABASE
postgres=# /l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
cndba | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | 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)
postgres=#
创建超级用户dave:
postgres=# create user dave with superuser password 'ustc';
CREATE ROLE
使用/c cndba 连接到cndba这个数据库：
postgres=# /c cndba
You are now connected to database "cndba" as user "postgres".
cndba=# create table dave(id int primary key,url varchar(40));
CREATE TABLE
cndba=# insert into dave values(1,'https://www.cndba.cn');
INSERT 0 1
cndba=# select * from dave;
id | url
----+----------------------
1 | https://www.cndba.cn
(1 row)
cndba=# /d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | dave | table | postgres
(1 row)
cndba=#
最后验证下通过指定IP 地址和端口的信息连接cndba 这个数据库：
-bash-4.2$ psql -h localhost -p 5432 -d cndba -U dave
Password for user dave:
psql (14.6)
Type "help" for help.
cndba=# /d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | dave | table | postgres
(1 row)
cndba=#
-bash-4.2$ cat ustc.sql
insert into dave values(3,'https://www.cndba.cn');
-bash-4.2$ psql -d cndba -x -W -f ustc.sql
Password:
INSERT 0 1
-bash-4.2$ psql
psql (14.6)
Type "help" for help.
postgres=# /c cndba
You are now connected to database "cndba" as user "postgres".
cndba=# select * from dave;
id | url
----+----------------------
1 | https://www.cndba.cn
2 | https://www.cndba.cn
3 | https://www.cndba.cn
(3 rows)
cndba=#
2 psql 常用命令
2.1 命令帮助
psql 的命令有很多，可以直接使用man psql 来查看：
[dave@www.cndba.cn ~]# su - postgres
Last login: Mon Aug 8 13:52:08 CST 2022 on pts/3
-bash-4.2$ man psql
postgres=# /?
General
/copyright show PostgreSQL usage and distribution terms
/crosstabview [COLUMNS] execute query and display results in crosstab
/errverbose show most recent error message at maximum verbosity
/g [(OPTIONS)] [FILE] execute query (and send results to file or |pipe);
/g with no arguments is equivalent to a semicolon
/gdesc describe result of query, without executing it
/gexec execute query, then execute each value in its result
/gset [PREFIX] execute query and store results in psql variables
/gx [(OPTIONS)] [FILE] as /g, but forces expanded output mode
/q quit psql
/watch [SEC] execute query every SEC seconds
Help
/? [commands] show help on backslash commands
/? options show help on psql command-line options
/? variables show help on special variables
/h [NAME] help on syntax of SQL commands, * for all commands
Query Buffer
/e [FILE] [LINE] edit the query buffer (or file) with external editor
/ef [FUNCNAME [LINE]] edit function definition with external editor
/ev [VIEWNAME [LINE]] edit view definition with external editor
/p show the contents of the query buffer
/r reset (clear) the query buffer
/s [FILE] display history or save it to file
/w FILE write query buffer to file
psql 工具的命令都是以/ 开头，这里列举几个常见的命令。
2.2 显示SQL 执行时间
cndba=# /timing on
Timing is on.
cndba=# select count(1) from film;
count
-------
1000
(1 row)
Time: 0.812 ms
cndba=# /timing off
Timing is off.
cndba=# select count(1) from film;
count
-------
1000
(1 row)
cndba=#
2.3 /d 命令
该命令将显示每个匹配关系（表、视图、索引、序列）的信息，包括对象所有的列、它们的类型、表空间（如果不是默认的）和任何特殊属性（诸如NOTNULL或默认值等）等。与唯一约束相关的索引、规则、约束、触发器也同样会显示出来。如果关系是一个视图，还会显示视图的定义。
cndba=# /d
List of relations
Schema | Name | Type | Owner
--------+----------------------------+----------+----------
public | actor | table | postgres
public | actor_actor_id_seq | sequence | postgres
public | actor_info | view | postgres
public | address | table | postgres
public | address_address_id_seq | sequence | postgres
public | category | table | postgres
public | category_category_id_seq | sequence | postgres
public | city | table | postgres
……
/d + 表名，会显示表的定义：
cndba=# /d film
Table "public.film"
Column | Type | Collation | Nullable | Default
------------------+-----------------------------+-----------+----------+---------------------------------------
film_id | integer | | not null | nextval('film_film_id_seq'::regclass)
title | character varying(255) | | not null |
description | text | | |
release_year | year | | |
language_id | smallint | | not null |
rental_duration | smallint | | not null | 3
rental_rate | numeric(4,2) | | not null | 4.99
length | smallint | | |
replacement_cost | numeric(5,2) | | not null | 19.99
rating | mpaa_rating | | | 'G'::mpaa_rating
last_update | timestamp without time zone | | not null | now()
special_features | text[] | | |
fulltext | tsvector | | not null |
Indexes:
"film_pkey" PRIMARY KEY, btree (film_id)
"film_fulltext_idx" gist (fulltext)
"idx_fk_language_id" btree (language_id)
"idx_title" btree (title)
Foreign-key constraints:
"film_language_id_fkey" FOREIGN KEY (language_id) REFERENCES language(language_id) ON UPDATE CASCADE ON DELETE RESTRICT
Referenced by:
TABLE "film_actor" CONSTRAINT "film_actor_film_id_fkey" FOREIGN KEY (film_id) REFERENCES film(film_id) ON UPDATE CASCADE ON DELETE RESTRICT
TABLE "film_category" CONSTRAINT "film_category_film_id_fkey" FOREIGN KEY (film_id) REFERENCES film(film_id) ON UPDATE CASCADE ON DELETE RESTRICT
TABLE "inventory" CONSTRAINT "inventory_film_id_fkey" FOREIGN KEY (film_id) REFERENCES film(film_id) ON UPDATE CASCADE ON DELETE RESTRICT
Triggers:
film_fulltext_trigger BEFORE INSERT OR UPDATE ON film FOR EACH ROW EXECUTE FUNCTION tsvector_update_trigger('fulltext', 'pg_catalog.english', 'title', 'description')
last_updated BEFORE UPDATE ON film FOR EACH ROW EXECUTE FUNCTION last_updated()
cndba=#
/d + 索引名：
cndba=# /d film_pkey
Index "public.film_pkey"
Column | Type | Key? | Definition
---------+---------+------+------------
film_id | integer | yes | film_id
primary key, btree, for table "public.film"
cndba=#
/d + 通配符* 或 ？
cndba=# /d fil?
Table "public.film"
Column | Type | Collation | Nullable | Default
------------------+-----------------------------+-----------+----------+---------------------------------------
film_id | integer | | not null | nextval('film_film_id_seq'::regclass)
title | character varying(255) | | not null |
description | text | | |
release_year | year | | |
language_id | smallint | | not null |
rental_duration | smallint | | not null | 3
rental_rate | numeric(4,2) | | not null | 4.99
length | smallint | | |
replacement_cost | numeric(5,2) | | not null | 19.99
rating | mpaa_rating | | | 'G'::mpaa_rating
last_update | timestamp without time zone | | not null | now()
special_features | text[] | | |
fulltext | tsvector | | not null |
Indexes:
"film_pkey" PRIMARY KEY, btree (film_id)
"film_fulltext_idx" gist (fulltext)
"idx_fk_language_id" btree (language_id)
"idx_title" btree (title)
Foreign-key constraints:
"film_language_id_fkey" FOREIGN KEY (language_id) REFERENCES language(language_id) ON UPDATE CASCADE ON DELETE RESTRICT
Referenced by:
TABLE "film_actor" CONSTRAINT "film_actor_film_id_fkey" FOREIGN KEY (film_id) REFERENCES film(film_id) ON UPDATE CASCADE ON DELETE RESTRICT
TABLE "film_category" CONSTRAINT "film_category_film_id_fkey" FOREIGN KEY (film_id) REFERENCES film(film_id) ON UPDATE CASCADE ON DELETE RESTRICT
TABLE "inventory" CONSTRAINT "inventory_film_id_fkey" FOREIGN KEY (film_id) REFERENCES film(film_id) ON UPDATE CASCADE ON DELETE RESTRICT
Triggers:
film_fulltext_trigger BEFORE INSERT OR UPDATE ON film FOR EACH ROW EXECUTE FUNCTION tsvector_update_trigger('fulltext', 'pg_catalog.english', 'title', 'description')
last_updated BEFORE UPDATE ON film FOR EACH ROW EXECUTE FUNCTION last_updated()
cndba=# /d fil*
Table "public.film"
Column | Type | Collation | Nullable | Default
------------------+-----------------------------+-----------+----------+---------------------------------------
film_id | integer | | not null | nextval('film_film_id_seq'::regclass)
title | character varying(255) | | not null |
description | text | | |
release_year | year | | |
language_id | smallint | | not null |
rental_duration | smallint | | not null | 3
rental_rate | numeric(4,2) | | not null | 4.99
length | smallint | | |
replacement_cost | numeric(5,2) | | not null | 19.99
rating | mpaa_rating | | | 'G'::mpaa_rating
last_update | timestamp without time zone | | not null | now()
special_features | text[] | | |
fulltext | tsvector | | not null |
Indexes:
"film_pkey" PRIMARY KEY, btree (film_id)
"film_fulltext_idx" gist (fulltext)
"idx_fk_language_id" btree (language_id)
"idx_title" btree (title)
……
/d+ 显示信息比/d 更详细
cndba=# /d+ film
Table "public.film"
Column | Type | Collation | Nullable | Default | Storage | Compression | S
tats target | Description
------------------+-----------------------------+-----------+----------+---------------------------------------+----------+-------------+--
------------+-------------
film_id | integer | | not null | nextval('film_film_id_seq'::regclass) | plain | |
|
title | character varying(255) | | not null | | extended | |
|
description | text | | | | extended | |
|
release_year | year | | | | plain | |
|
language_id | smallint | | not null | | plain | |
|
rental_duration | smallint | | not null | 3
……
匹配不同对象类型的 /d命令
/dt 只显示表
/di只显示索引
/ds只显示序列
/dv只显示视图
/df只显示函数
/dn 显示所有可用的schema
cndba=# /dn
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)
/db 显示所有可用的表空间
cndba=# /db
List of tablespaces
Name | Owner | Location
------------+----------+----------
pg_default | postgres |
pg_global | postgres |
(2 rows)
cndba=#
/du,/dg 显示数据库所有的角色或用户，在pg中不区分用户和角色，这2个命令等价：
cndba=# /du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
dave | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
cndba=# /dg
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
dave | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
cndba=#
/dp 或 /z 显示表的权限分配情况
cndba=# /dp film
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
public | film | table | | |
(1 row)
cndba=# /z file
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+------+-------------------+-------------------+----------
(0 rows)
cndba=#
2.4 指定字符集编译的命令
当客户端的字符编码与服务器的不一样时，可能会显示乱码，可以使用/encoding命令指定客户端的字符编码:
cndba=# /encoding gbk;
cndba=# /encoding utf8
cndba=#
2.5 /pset 命令
/pset命令用于设置输出的格式:
/pset border 0∶表示输出内容无边框。
/pset border 1∶表示边框只在内部。
/pset border 2∶表示内外都有边框。
cndba=# /pset border 0;
Border style is 0.
cndba=# select * from dave;
id url
-- --------------------
1 https://www.cndba.cn
(1 row)
cndba=# /pset border 1;
Border style is 1.
cndba=# select * from dave;
id | url
----+----------------------
1 | https://www.cndba.cn
(1 row)
cndba=# /pset border 2;
Border style is 2.
cndba=# select * from dave;
+----+----------------------+
| id | url |
+----+----------------------+
| 1 | https://www.cndba.cn |
+----+----------------------+
(1 row)
cndba=#
2.6 /x 命令
使用/x命令，可以把表中每一行的每列数据都拆分为单行展示，如果一行数据有太多的拆行，显示不下，就可以使用这里介绍的 /x 命令。
cndba=# select * from dave;
+----+----------------------+
| id | url |
+----+----------------------+
| 1 | https://www.cndba.cn |
+----+----------------------+
(1 row)
cndba=# /x
Expanded display is on.
cndba=# select * from dave;
+-[ RECORD 1 ]---------------+
| id | 1 |
| url | https://www.cndba.cn |
+-----+----------------------+
cndba=#
2.7 /i 执行外部的SQL
这里有两种方式，在psql里面使用/i, 在psql命令外使用-f。
-bash-4.2$ cat ustc.sql
insert into dave values(2,'https://www.cndba.cn');
-bash-4.2$ psql
psql (14.6)
Type "help" for help.
postgres=# /c cndba
You are now connected to database "cndba" as user "postgres".
cndba=# /i ustc.sql
INSERT 0 1
cndba=# select * from dave;
id | url
----+----------------------
1 | https://www.cndba.cn
2 | https://www.cndba.cn
(2 rows)
cndba=#
2.8 显示实际执行的SQL
之前/ 开头的命令都是类似快捷方式，如果要查看这些命令背后的实际SQL，需要适用-E 启动psql。
-bash-4.2$ psql -E
psql (14.6)
Type "help" for help.
postgres=# /c cndba
You are now connected to database "cndba" as user "postgres".
cndba=# /d dave
********* QUERY **********
SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname OPERATOR(pg_catalog.~) '^(dave)$' COLLATE pg_catalog.default
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
**************************
********* QUERY **********
SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, false AS relhasoids, c.relispartition, '', c.reltablespace, CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence, c.relreplident, am.amname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
LEFT JOIN pg_catalog.pg_am am ON (c.relam = am.oid)
WHERE c.oid = '16385';
**************************
********* QUERY **********
SELECT a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod),
(SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid, true)
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
a.attnotnull,
(SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation,
a.attidentity,
a.attgenerated
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '16385' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;
**************************
********* QUERY **********
SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, i.indisvalid, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),
pg_catalog.pg_get_constraintdef(con.oid, true), contype, condeferrable, condeferred, i.indisreplident, c2.reltablespace
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x'))
WHERE c.oid = '16385' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
ORDER BY i.indisprimary DESC, c2.relname;
**************************
********* QUERY **********
SELECT pol.polname, pol.polpermissive,
CASE WHEN pol.polroles = '{0}' THEN NULL ELSE pg_catalog.array_to_string(array(select rolname from pg_catalog.pg_roles where oid = any (pol.polroles) order by 1),',') END,
pg_catalog.pg_get_expr(pol.polqual, pol.polrelid),
pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid),
CASE pol.polcmd
WHEN 'r' THEN 'SELECT'
WHEN 'a' THEN 'INSERT'
WHEN 'w' THEN 'UPDATE'
WHEN 'd' THEN 'DELETE'
END AS cmd
FROM pg_catalog.pg_policy pol
WHERE pol.polrelid = '16385' ORDER BY 1;
**************************
********* QUERY **********
SELECT oid, stxrelid::pg_catalog.regclass, stxnamespace::pg_catalog.regnamespace::pg_catalog.text AS nsp, stxname,
pg_catalog.pg_get_statisticsobjdef_columns(oid) AS columns,
'd' = any(stxkind) AS ndist_enabled,
'f' = any(stxkind) AS deps_enabled,
'm' = any(stxkind) AS mcv_enabled,
stxstattarget
FROM pg_catalog.pg_statistic_ext
WHERE stxrelid = '16385'
ORDER BY nsp, stxname;
**************************
********* QUERY **********
SELECT pubname
FROM pg_catalog.pg_publication p
JOIN pg_catalog.pg_publication_rel pr ON p.oid = pr.prpubid
WHERE pr.prrelid = '16385'
UNION ALL
SELECT pubname
FROM pg_catalog.pg_publication p
WHERE p.puballtables AND pg_catalog.pg_relation_is_publishable('16385')
ORDER BY 1;
**************************
********* QUERY **********
SELECT c.oid::pg_catalog.regclass
FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
WHERE c.oid = i.inhparent AND i.inhrelid = '16385'
AND c.relkind != 'p' AND c.relkind != 'I'
ORDER BY inhseqno;
**************************
********* QUERY **********
SELECT c.oid::pg_catalog.regclass, c.relkind, inhdetachpending, pg_catalog.pg_get_expr(c.relpartbound, c.oid)
FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
WHERE c.oid = i.inhrelid AND i.inhparent = '16385'
ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
**************************
Table "public.dave"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
id | integer | | not null |
url | character varying(40) | | |
Indexes:
"dave_pkey" PRIMARY KEY, btree (id)
版权声明：本文为博主原创文章，未经博主允许不得转载。
