SRE文档 | SRE文档

0. 查看帮助

`\h`	SQL 命令语法帮助
`\h` DELETE	DELETE SQL 语句语法
`\?`	PostgreSQL 命令列表

在 PostgreSQL 控制台中运行

1. psql 命令

参数	示例	说明
`[-d] <database>`	psql -d mydb	连接到数据库
`-U`	psql -U john mydb	以特定用户身份连接
`-h` `-p`	psql -h localhost -p 5432 mydb	连接到主机/端口
`-U` `-h` `-p` `-d`	psql -U admin -h 192.168.1.5 -p 2506 -d mydb	连接远程 PostgreSQL
`-W`	psql -W mydb	强制密码
`-c`	psql -c '\c postgres' -c '\dt'	执行 SQL 查询或命令
`-H`	psql -c "\l+" -H postgres > database.html	生成 HTML 报告
`-l`	psql -l	列出所有数据库
`-f`	psql mydb -f file.sql	从文件执行命令
`-V`	psql -V	打印 psql 版本

2. Recon 观察

显示版本

sql

SHOW SERVER_VERSION;

SHOW SERVER_VERSION;

显示系统状态

sql

\conninfo

\conninfo

显示环境变量

sql

SHOW ALL;

SHOW ALL;

列出用户

sql

SELECT rolname FROM pg_roles;

SELECT rolname FROM pg_roles;

显示当前用户

sql

SELECT current_user;

SELECT current_user;

显示当前用户的权限

sql

\du

\du

显示当前数据库

sql

SELECT current_database();

SELECT current_database();

显示数据库中的所有表

sql

\dt

\dt

列出函数

sql

\df <schema>

\df <schema>

3. Databases 数据库

列出数据库

sql

\l

\l

连接到数据库

sql

\c <database_name>

\c <database_name>

显示当前数据库

sql

SELECT current_database();

SELECT current_database();

创建数据库

sql

CREATE DATABASE <database_name> WITH OWNER <username>;

CREATE DATABASE <database_name> WITH OWNER <username>;

删除数据库

sql

DROP DATABASE IF EXISTS <database_name>;

DROP DATABASE IF EXISTS <database_name>;

重命名数据库

sql

ALTER DATABASE <old_name> RENAME TO <new_name>;

ALTER DATABASE <old_name> RENAME TO <new_name>;

4. Tables 表

列出当前数据库中的表

sql

\dt
SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name;

\dt
SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name;

全局列表

sql

\dt *.*.
SELECT * FROM pg_catalog.pg_tables

\dt *.*.
SELECT * FROM pg_catalog.pg_tables

列表表架构

sql

\d <table_name>
\d+ <table_name>
SELECT column_name, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = '<table_name>';

\d <table_name>
\d+ <table_name>
SELECT column_name, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = '<table_name>';

创建表

sql

CREATE TABLE <table_name>(
  <column_name> <column_type>,
  <column_name> <column_type>
);

CREATE TABLE <table_name>(
  <column_name> <column_type>,
  <column_name> <column_type>
);

创建表，主键自增

sql

CREATE TABLE <table_name> (
  <column_name> SERIAL PRIMARY KEY
);

CREATE TABLE <table_name> (
  <column_name> SERIAL PRIMARY KEY
);

删除表

sql

DROP TABLE IF EXISTS <table_name> CASCADE;

DROP TABLE IF EXISTS <table_name> CASCADE;

5. Permissions 权限

shell

sudo su - postgres
psql

sudo su - postgres
psql

授予对数据库的所有权限

sql

GRANT ALL PRIVILEGES ON DATABASE <db_name> TO <user_name>;

GRANT ALL PRIVILEGES ON DATABASE <db_name> TO <user_name>;

授予数据库连接权限

sql

GRANT CONNECT ON DATABASE <db_name> TO <user_name>;

GRANT CONNECT ON DATABASE <db_name> TO <user_name>;

授予架构权限

sql

GRANT USAGE ON SCHEMA public TO <user_name>;

GRANT USAGE ON SCHEMA public TO <user_name>;

授予函数权限

sql

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO <user_name>;

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO <user_name>;

授予在所有表上选择、更新、插入、删除的权限

sql

GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO <user_name>;

GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO <user_name>;

在表上授予权限

sql

GRANT SELECT, UPDATE, INSERT ON <table_name> TO <user_name>;

GRANT SELECT, UPDATE, INSERT ON <table_name> TO <user_name>;

授予对表的选择权限

sql

GRANT SELECT ON ALL TABLES IN SCHEMA public TO <user_name>;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO <user_name>;

6. Columns 列

sql

ALTER TABLE <table_name> IF EXISTS
ADD <column_name> <data_type> [<constraints>];

ALTER TABLE <table_name> IF EXISTS
ADD <column_name> <data_type> [<constraints>];

更新栏

sql

ALTER TABLE <table_name> IF EXISTS
ALTER <column_name> TYPE <data_type> [<constraints>];

ALTER TABLE <table_name> IF EXISTS
ALTER <column_name> TYPE <data_type> [<constraints>];

删除列

sql

ALTER TABLE <table_name> IF EXISTS
DROP <column_name>;

ALTER TABLE <table_name> IF EXISTS
DROP <column_name>;

将列更新为自增主键

sql

ALTER TABLE <table_name>
ADD COLUMN <column_name> SERIAL PRIMARY KEY;

ALTER TABLE <table_name>
ADD COLUMN <column_name> SERIAL PRIMARY KEY;

使用自动递增的主键插入表中

sql

INSERT INTO <table_name>
VALUES (DEFAULT, <value1>);
INSERT INTO <table_name> (<column1_name>,<column2_name>)
VALUES ( <value1>,<value2> );

INSERT INTO <table_name>
VALUES (DEFAULT, <value1>);
INSERT INTO <table_name> (<column1_name>,<column2_name>)
VALUES ( <value1>,<value2> );

7. Data 数据

选择所有数据

sql

SELECT * FROM <table_name>;

SELECT * FROM <table_name>;

读取一行数据

sql

SELECT * FROM <table_name> LIMIT 1;

SELECT * FROM <table_name> LIMIT 1;

搜索数据

sql

SELECT * FROM <table_name> WHERE <column_name> = <value>;

SELECT * FROM <table_name> WHERE <column_name> = <value>;

插入数据

sql

INSERT INTO <table_name> VALUES( <value_1>, <value_2> );

INSERT INTO <table_name> VALUES( <value_1>, <value_2> );

更新数据

sql

UPDATE <table_name>
SET <column_1> = <value_1>, <column_2> = <value_2>
WHERE <column_1> = <value>;

UPDATE <table_name>
SET <column_1> = <value_1>, <column_2> = <value_2>
WHERE <column_1> = <value>;

删除所有数据

sql

DELETE FROM <table_name>;

DELETE FROM <table_name>;

删除特定数据

sql

DELETE FROM <table_name>
WHERE <column_name> = <value>;

DELETE FROM <table_name>
WHERE <column_name> = <value>;

8. Users 用户

列出角色

sql

SELECT rolname FROM pg_roles;

SELECT rolname FROM pg_roles;

创建用户

sql

CREATE USER <user_name> WITH PASSWORD '<password>';

CREATE USER <user_name> WITH PASSWORD '<password>';

删除用户

sql

DROP USER IF EXISTS <user_name>;

DROP USER IF EXISTS <user_name>;

更改用户密码

sql

ALTER ROLE <user_name> WITH PASSWORD '<password>';

ALTER ROLE <user_name> WITH PASSWORD '<password>';

9. Schema

列出 Schemas

sql

\dn
SELECT schema_name FROM information_schema.schemata;
SELECT nspname FROM pg_catalog.pg_namespace;

\dn
SELECT schema_name FROM information_schema.schemata;
SELECT nspname FROM pg_catalog.pg_namespace;

创建架构

sql

CREATE SCHEMA IF NOT EXISTS <schema_name>;

CREATE SCHEMA IF NOT EXISTS <schema_name>;

删除模式

sql

DROP SCHEMA IF EXISTS <schema_name> CASCADE;

DROP SCHEMA IF EXISTS <schema_name> CASCADE;

1. 查看数据库

bash

[ptgres@beta ~]$ psql -U postgres -h 127.0.0.1  -p 5532
psql (12.2)
Type "help" for help.

postgres=# \l
postgres=# \l
                                    List of databases
      Name       |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------------+----------+----------+------------+------------+-----------------------
 advertisedb     | postgres | UTF8     | en_US.UTF8 | en_US.UTF8 | 
 agile           | postgres | UTF8     | en_US.UTF8 | en_US.UTF8 | 
 eidu            | postgres | UTF8     | en_US.UTF8 | en_US.UTF8 |

[ptgres@beta ~]$ psql -U postgres -h 127.0.0.1  -p 5532
psql (12.2)
Type "help" for help.

postgres=# \l
postgres=# \l
                                    List of databases
      Name       |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------------+----------+----------+------------+------------+-----------------------
 advertisedb     | postgres | UTF8     | en_US.UTF8 | en_US.UTF8 | 
 agile           | postgres | UTF8     | en_US.UTF8 | en_US.UTF8 | 
 eidu            | postgres | UTF8     | en_US.UTF8 | en_US.UTF8 |

2.查看表

postgres=# \c han
You are now connected to database "han" as user "postgres".

han=# \d
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | you  | table | postgres
(1 row)

postgres=# \c han
You are now connected to database "han" as user "postgres".

han=# \d
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | you  | table | postgres
(1 row)

3.查看表结构

d跟一个表名，表示显示这个表的结构定义

han=# \d you
                     Table "public.you"
 Column |       Type        | Collation | Nullable | Default 
--------+-------------------+-----------+----------+---------
 name11 | character varying |           |          | 
 fdf    | character varying |

han=# \d you
                     Table "public.you"
 Column |       Type        | Collation | Nullable | Default 
--------+-------------------+-----------+----------+---------
 name11 | character varying |           |          | 
 fdf    | character varying |

4.查看索引名字

d 后跟一个索引，可以显示索引的信息

d 后跟一个索引，可以显示索引的信息

5.模糊查询

d后面跟通配符如"*" 或""?"

han=# \d y*
                     Table "public.you"
 Column |       Type        | Collation | Nullable | Default 
--------+-------------------+-----------+----------+---------
 name11 | character varying |           |          | 
 fdf    | character varying |           |          |

han=# \d y*
                     Table "public.you"
 Column |       Type        | Collation | Nullable | Default 
--------+-------------------+-----------+----------+---------
 name11 | character varying |           |          | 
 fdf    | character varying |           |          |

6.查看表大小

\d+命令显示比\d更多的信息包括表和列的注释等相关信息

han=# \d+
                   List of relations
 Schema | Name | Type  |  Owner   | Size  | Description 
--------+------+-------+----------+-------+-------------
 public | you  | table | postgres | 16 kB | 
(1 row)

han=# \d+
                   List of relations
 Schema | Name | Type  |  Owner   | Size  | Description 
--------+------+-------+----------+-------+-------------
 public | you  | table | postgres | 16 kB | 
(1 row)

7.显示执行时间

可以使用\timing on 命令开启计时功能，如果要关闭，使用\timing off关闭计时功能

han=# \timing on
Timing is on.

han=# \d+
                   List of relations
 Schema | Name | Type  |  Owner   | Size  | Description 
--------+------+-------+----------+-------+-------------
 public | you  | table | postgres | 16 kB | 
(1 row)

han=# select * from you ;
 name11 |   fdf   
--------+---------
 han    | student
(1 row)

Time: 0.479 ms

han=# \timing on
Timing is on.

han=# \d+
                   List of relations
 Schema | Name | Type  |  Owner   | Size  | Description 
--------+------+-------+----------+-------+-------------
 public | you  | table | postgres | 16 kB | 
(1 row)

han=# select * from you ;
 name11 |   fdf   
--------+---------
 han    | student
(1 row)

Time: 0.479 ms

8.列出所有schema

han=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
(1 row)

han=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
(1 row)

9.列出所有表空间

han=# \db
       List of tablespaces
    Name    |  Owner   | Location 
------------+----------+----------
 pg_default | postgres | 
 pg_global  | postgres | 
(2 rows)

han=# \db
       List of tablespaces
    Name    |  Owner   | Location 
------------+----------+----------
 pg_default | postgres | 
 pg_global  | postgres | 
(2 rows)

10.列出所有用户

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

#或者

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

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

#或者

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

11. 权限分配情况

\dp或者\z

\dp talbe_name

\dp talbe_name

12. 执行文件中的命令

一般\i用于执行sql文件的脚本

一般\i用于执行sql文件的脚本

13. 指定字符集编译的命令

当客户端的字符编码与服务器不一致时，可能出现乱码现象，可以使用\encoding命令指定客户端的字符编码。

    比如：

   \encoding gbk    设置客户端的字符编码为gbk.

   \encoding utf8    设置客户端的字符编码为uft8

当客户端的字符编码与服务器不一致时，可能出现乱码现象，可以使用\encoding命令指定客户端的字符编码。

    比如：

   \encoding gbk    设置客户端的字符编码为gbk.

   \encoding utf8    设置客户端的字符编码为uft8

14. pset命令

 \pset命令用于设置输出的格式

    \pset border 0:表示输出内容无边框

    \pset border 1:表示边框只在内部存在

    \pset border 2:表示内外都有边框

 \pset命令用于设置输出的格式

    \pset border 0:表示输出内容无边框

    \pset border 1:表示边框只在内部存在

    \pset border 2:表示内外都有边框

15. x命令

使用\x命令，可以把表中的每一行的每列数据都拆分为单行展示，这种适合列比较多，界面显示不全的情况下比较有用

通过使用? 可以获取到更多的命令的解释

2-Harbor

3-Docker

1.安装

4-Containerd

1.安装

3.镜像管理

4.构建镜像

5-Dockerfile

🍂 env案例

6-Docker-Compose

7-Swarm

8-KVM

2-资源对象

2-Pod

5-Deployment

6-StatefulSet

7-Service

9-Job

10-ConfigMap

11-Secret

13-CoreDns

16-发布

3-存储

1- 存储卷概念

2-NFS

4-Minio

1-安装

4-网络

1-Calico

2-Cilium

OpenELB

5-认证与授权

6-安装

1.二进制安装

2.kubeadm安装

7-监控

1-Prometheus

2-Alertmanager

3-PrometheusAlert

4-Grafana

5-VictoriaMetrics

8-备份

9-常用操作

10-Yaml配置

11-Helm

8-Helm项目

12-CICD

1-Jenkins

2-ArgoCD

13-Ingress

1-Ingress_nginx

2-Higress

15-Autoscaler

1-HPA

2-VPA

3-OpenKruise

1-Kruise

16-Scheduler

17-云k8s

1-AWS EKS

5-ingress-nginx

维护手册

18-Kubernetes故障排查

19-Kubernetes排查手册

1-WireShark

20-Kubernetes维护手册

21-Kubernetes面试

1-Go

2-Go框架

3-Go编译

5-Go文档

6-Go日志

10-Go模块

11-Web前端开发

4-vue

5-vue架构

12-Git

13-Client-Go

FAQ

PostgreSQL