postgresql常用命令

\du 或者 \dg                        #列出数据库中全部用户或角色
\db                                 #显示所有的表空间
\dn                                 #列出所有的schema
\timing on                          #开启时间，后续执行sql语句即可显示执行时间
\df                                 #显示函数
\dv                                 #显示视图
\ds                                 #显示序列
\di                                 #显示索引
\dt                                 #显示匹配的表 
\d + 表名                           #查看表结构         
\h                                  #显示帮助信息
\h create table                     #显示创建表语法
\l                                  #查看包含哪些数据库(L的小写)
\c db_name                          #切换到db_name数据库
\dp 或 \z                           #显示表的权限分配情况
\pset border 2                      #输出内容内外有边框，执行此命令后执行select即可看到效果
\pset border 1                      #输出内容只有内表框
\pset border 0                      #输出内容无边框
\x                                  #把按行展示的数据变成按列展示，类似与MySQL中的\G
\i /root/a.sql;                     #执行root路径下的sql文件
psql -U test_user -d test_db -f /root/a.sql #终端命令行执行sql文件
\echo hello world                   #输出信息,可写在sql脚本中
\?                                  #更多其他命令查看
\d + 连续按两次tab键可弹出用法
create database dbname              #创建数据库
create user username with password ''  #创建用户
CREATE TABLE user (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    role VARCHAR(32) NOT NULL DEFAULT 'operator',
    email_verified BOOLEAN NOT NULL DEFAULT FALSE,
    verify_token VARCHAR(64)
);                                  #创建表
COMMENT ON TABLE user IS '用户表';  #给表添加注释
select obj_description('user'::regclass); #查看表的注释内容
GRANT ALL PRIVILEGES ON DATABASE dbname TO username; #授权
alter user postgres with password 'Abcd1234#';  #重置postgres密码
ALTER TABLE tablename DROP COLUMN cloumName; #删除单个列
ALTER TABLE guacamole_connection
DROP COLUMN col1,
DROP COLUMN col2,
DROP COLUMN col3;               #删除多个列
# 导出完整数据：格式：pg_dump -U 用户名 -d 数据库名 -h 主机 -p 端口 -f 导出文件.sql
pg_dump -U username -d dbname -h localhost -p 5432 -f full_backup.sql

# 导入数据到数据库中,最好使用postgres用户，否则会提示权限不足
psql -U postgres -d dbname -h localhost -p 5432 -f full_backup.sql

新建一个库，如果还想用某个普通用户连接并让程序自动建表，就需要给这个库里的 public schema 授权，例如：

CREATE DATABASE newdb;

\c newdb

GRANT USAGE, CREATE ON SCHEMA public TO appuser;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO appuser;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO appuser;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON TABLES TO appuser;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON SEQUENCES TO appuser;

GRANT CONNECT ON DATABASE newdb TO appuser; #连接库的权限