PostgreSQL常用命令行操作

骑猪看日落 2022-03-15 15:24 1234阅读 0赞

# PostgreSQL常用命令行操作 #

### 注意 ###

*  注意有些命令要以`;`结尾，有些不用(一般是 `\命令` 的不用)
 *  不以`;`结尾的命令相当于未换行
 *  允许一次性粘贴执行多行命令

### 登录 ###

# 切换用户 (必须先切换到postgres的Linux用户)
    su - postgres
    
    # 使用postgres用户登录，如需密码会提示输入，登录后选中的库是postgres
    psql
    
    
    # 详解：
    省略数据库，默认和用户同名，
    省略host默认本机，
    省略端口默认5432，
    省略用户默认与同当前Linux用户同名
    psql
    psql -U postgres
    psql -U postgres -h 127.0.0.1
    psql -U postgres -h 127.0.0.1 -p 5432
    psql -U postgres -h 127.0.0.1 -p 5432 -postgres

### 常用命令 ###

\l  查看系统中现存的数据库 
    \c  切换库，如template1=# \c sales 从template1转到sales库
    \d  查看表和sequence
    \d  table_name，查看表结构，如：\d public.t_ip或\d t_ip，虽然`\d`看不到其他schema的表，但依然可以描述表`\d wechat.stat_basic_hour`
    \dt 只查看表
    \di 查看索引
    \du 查看有哪些用户
    \dn 查看schema
    \dp 显示表的权限分配情况
    \q 退出客户端程序psql
    SET SEARCH_PATH TO public,wechat;   设置搜索路径(不区分大小写，下同) 设置后`\d`能列出指定的schema的表和sequence
    SHOW SEARCH_PATH;    查看搜索模式
    SELECT USER;    查看当前是什么用户登录的
    postgres=# \conninfo 查看连接信息(什么用户连的)
    You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
    
    `postgres=#` 的提示符`postgres`表示当前数据库是`postgres`

### 建库建 Schema ###

# 建库
    CREATE DATABASE mpa_stat OWNER dbuser;
    # 删除
    DROP DATABASE mpa_stat;
    
    创建、删除schema
    CREATE SCHEMA schema01;   (是创建在当前数据库，使用\dn查看)
    DROP SCHEMA schema01;

### 导入 SQL 文件 ###

# 执行sql文件，`-d`什么苦，`-U`，以什么身份，`-f`文件路径
    psql -d mpa_stat -U postgres -f /var/lib/pgsql/public.sql

### 授权、所有者 ###

# 授权数据库给用户
    GRANT ALL ON DATABASE exampledb TO dbuser;
    
    #授权表给用户
    GRANT ALL ON public.mpa_app TO dbuser;
    
    #授权sequence给用户
    GRANT ALL ON SEQUENCE public.mpa_app_id_seq to dbuser;
    
    # 不需要一个个地授权，按schema整体打包授权给某用户
    GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO dbuser;
    GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO dbuser;
    
    
    ----所有者------
    # 修改库的所有者
    alter database mytestdb owner to mpa_dev;
    
    #修改schema所有者
    alter schema wechat owner to mpa_dev;

### 用户相关 ###

# 创建用户
    CREATE USER mpa_prod WITH PASSWORD '51la@xcx20';

### 重启 Postgresql ###

# 找到配置文件，修改
    locate postgresql.conf
    # 找到pg_ctl命令，重启(合适的用户)，-D是数据目录
    ./pg_ctl restart -D /opt/pgsql/data/

### 进阶 ###

# 查看最大连接数的配置值
    show max_connections;
    
    # 查看已被用的连接数
    select count(*) from pg_stat_activity;

### 附录： ###

#### 生产线文件路径 ####

配置文件路径
    /data/pgsql/data/postgresql.conf
    
    bin目录(pg_ctl所在地方)
    /usr/pgsql-11/bin/pg_ctl
    
    属于文件目录
    /data/pgsql/data
    
    重启脚本
    /usr/pgsql-11/bin/pg_ctl restart -D /data/pgsql/data