3.postgresSQL常用命令

常用命令

https://wangchujiang.com/reference/docs/postgres.html

[root@controller postgresSQL]# docker exec -it postgres-mall bash
root@4ec35088c424:/# psql --help
psql is the PostgreSQL interactive terminal.

Usage:
  psql [OPTION]... [DBNAME [USERNAME]]

General options:
  -c, --command=COMMAND    run only single command (SQL or internal) and exit
  -d, --dbname=DBNAME      database name to connect to (default: "root")
  -f, --file=FILENAME      execute commands from file, then exit
  -l, --list               list available databases, then exit
  -v, --set=, --variable=NAME=VALUE
                           set psql variable NAME to VALUE
                           (e.g., -v ON_ERROR_STOP=1)
  -V, --version            output version information, then exit
  -X, --no-psqlrc          do not read startup file (~/.psqlrc)
  -1 ("one"), --single-transaction
                           execute as a single transaction (if non-interactive)
  -?, --help[=options]     show this help, then exit
      --help=commands      list backslash commands, then exit
      --help=variables     list special variables, then exit

Input and output options:
  -a, --echo-all           echo all input from script
  -b, --echo-errors        echo failed commands
  -e, --echo-queries       echo commands sent to server
  -E, --echo-hidden        display queries that internal commands generate
  -L, --log-file=FILENAME  send session log to file
  -n, --no-readline        disable enhanced command line editing (readline)
  -o, --output=FILENAME    send query results to file (or |pipe)
  -q, --quiet              run quietly (no messages, only query output)
  -s, --single-step        single-step mode (confirm each query)
  -S, --single-line        single-line mode (end of line terminates SQL command)

Output format options:
  -A, --no-align           unaligned table output mode
      --csv                CSV (Comma-Separated Values) table output mode
  -F, --field-separator=STRING
                           field separator for unaligned output (default: "|")
  -H, --html               HTML table output mode
  -P, --pset=VAR[=ARG]     set printing option VAR to ARG (see \pset command)
  -R, --record-separator=STRING
                           record separator for unaligned output (default: newline)
  -t, --tuples-only        print rows only
  -T, --table-attr=TEXT    set HTML table tag attributes (e.g., width, border)
  -x, --expanded           turn on expanded table output
  -z, --field-separator-zero
                           set field separator for unaligned output to zero byte
  -0, --record-separator-zero
                           set record separator for unaligned output to zero byte

Connection options:
  -h, --host=HOSTNAME      database server host or socket directory (default: "local socket")
  -p, --port=PORT          database server port (default: "5432")
  -U, --username=USERNAME  database user name (default: "root")
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)

For more information, type "\?" (for internal commands) or "\help" (for SQL
commands) from within psql, or consult the psql section in the PostgreSQL
documentation.

Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>

root@4ec35088c424:/# psql mall_db admin
psql (16.9 (Debian 16.9-1.pgdg120+1))
Type "help" for help.

mall_db=# help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

1、\l：psql 元命令，列出所有数据库

mall_db=# \l
                                                   List of databases
   Name    | Owner | Encoding | Locale Provider |  Collate   |   Ctype    | ICU Locale | ICU Rules | Access privileges
-----------+-------+----------+-----------------+------------+------------+------------+-----------+-------------------
 mall_db   | admin | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           |
 postgres  | admin | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           |
 template0 | admin | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/admin         +
           |       |          |                 |            |            |            |           | admin=CTc/admin
 template1 | admin | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/admin         +
           |       |          |                 |            |            |            |           | admin=CTc/admin
(4 rows)

2、\du：psql 元命令，列出所有用户 / 角色

mall_db=# \du 
                             List of roles
 Role name |                         Attributes
-----------+------------------------------------------------------------
 admin     | Superuser, Create role, Create DB, Replication, Bypass RLS

mall_db=#

3、查看是否可以远程登录和监听地址

https://hub.docker.com/_/postgres

容器启动的话，通过下面这个参数可以实现外部访问postgres是否需要密码验证

下面这两个文件默认都是配置

root@4ec35088c424:/# egrep -v "^#|^$" /var/lib/postgresql/data/pg_hba.conf 
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust
host all all all scram-sha-256

# 上面则是通过其他IP访问到这个数据库需要密码校验，本地访问无需密码
# 旧版本（低于14的）允许所有IP通过密码认证连接所有数据库
host    all             all             0.0.0.0/0               md5

root@4ec35088c424:/# cat /var/lib/postgresql/data/postgresql.conf | grep listen_addresses
listen_addresses = '*'

上面表示侦听来自所有的IP地址的请求

4、验证远程登录成功

localhost代表本地登录成功，改成其他地址
root@4ec35088c424:/# psql -h localhost -p 5432 -U admin -d mall_db
psql (16.9 (Debian 16.9-1.pgdg120+1))
Type "help" for help.

mall_db=#

5、查看管理员用户密码

mall_db=# SELECT usename, passwd FROM pg_shadow;
 usename |                                                                passwd
---------+---------------------------------------------------------------------------------------------------------------------------------------
 admin   | SCRAM-SHA-256$4096:sbmLhhavaZCx87Tt2apolA==$EulkzTemAKSQ6hOTo6s5EFFkOE/OdIDOVumTLSvIBEU=:xEIPhKFbF+F1gA5aJ3hVND2VWXUYY8o3oFxQ2wdJrC8=
(1 row)

6、扩展的添加和删除

启用扩展（SQL 命令），假设扩展是pg_trgm
CREATE EXTENSION pg_trgm; 

查看已经启用的扩展有哪些
SELECT * FROM pg_extension;


查看可用的扩展
SELECT * FROM pg_available_extensions ;

卸载扩展
DROP EXTENSION extension_name;

参考官方文档：https://www.postgresql.org/docs/16/sql-createextension.html