PostgreSQL常用命令

数据库版本 ：9.6.6

注意 ：PostgreSQL中的不同类型的权限有

SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER,CREATE,CONNECT,TEMPORARY,EXECUTE 和 USAGE。

1. 登录PG数据库

以管理员身份 postgres 登陆，然后通过

#psql -U postgres
 #sudo -i -u postgres
 ?
 $ psql  xc_hzh_linan                    #登录xc_hzh_linan数据库
 $ psql -U postgres test               #以postgres用户身份，登录test数据库
 psql -h localhost -p 5432 -U postgress testdb

界面效果为:

postgres@ubuntu:~$ psql
 psql (9.5.19)
 Type "help" for help.
 ?
 postgres=# 

1.简单说一下pgsql 的相关命令

使用反斜线作为命令前缀.

退出    \q
 列出所有的数据库      \l
 列出所有的数据库的大小      \l+
 更改当前连接的数据库       \c
 列出当前数据库的连接信息    \connect
 列出当前数据库和连接的详细信息 \conninfo
 查看当前数据库里面的表和拥有者和表大小         \dt +
 展示所有用户           \dg
 ?
 查看所有表名的列表             \d
 获取表结构                   \d a
 展示所有用户               \du
 查看t_sms表的结构      \d t_sms 
 展示数据库里面的所有的表         \dt
 列出所有的数据库的详细信息（包括数据库大小和字符格式）         \l+
 显示用户访问权限。                            \z或\dp
 显示所有可设置的访问权限                     \h GRAN
 显示用户的对所有数据库表的详细访问权限     \dp或者\z
 确认当前连接的用户为超级用户postgres，且该用户后创建角色和数据库的权限等     #select current_user;
 在超级用户连接postgres后，设置不允许普通用户a连接数据库         #alter role a nologin;
 ?
 使用普通用户a连接数据库正常                   #\c highgo a
 ?
 查看当前连接的用户名：
 foo=#select * from current_user;
 或
 foo=#select user;
 ?
 查看所有用户名：
 foo=#\du
 ?
 数据库表的备份与恢复：
 pg_dump -h 192.168.2.242 -U postgres -p 5432 -c --if-exists -t t_sms dev_huishishuju > t_sms.db 远程备份

t_gj_tzy=# \l+
                                                                    List of databases
    Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   |  Size   | Tablespace |                Description                 
 -----------+----------+----------+------------+------------+-----------------------+---------+------------+--------------------------------------------
  postgres  | postgres | UTF8     | zh_CN.utf8 | zh_CN.utf8 |                       | 7079 kB | pg_default | default administrative connection database
  t_gj_tzy  | wsgjjkzg | UTF8     | zh_CN.utf8 | zh_CN.utf8 | =Tc/wsgjjkzg         +| 21 GB   | pg_default | 
            |          |          |            |            | wsgjjkzg=CTc/wsgjjkzg |         |            | 
  template0 | postgres | UTF8     | zh_CN.utf8 | zh_CN.utf8 | =c/postgres          +| 6969 kB | pg_default | unmodifiable empty database
 ?
 PostgreSQL 查找当前数据库的所有表 select * from pg_tables where schemaname = 'public';

查看每个表的大小

postgres=# \dt+
                                  List of relations
  Schema |            Name            | Type  |  Owner   |    Size    | Description 
 --------+----------------------------+-------+----------+------------+-------------
  public | acknowledges               | table | postgres | 0 bytes    | 
  public | actions                    | table | postgres | 16 kB      | 
  public | alerts                     | table | postgres | 8192 bytes | 
  public | application_discovery      | table | postgres | 0 bytes    | 
  public | application_prototype      | table | postgres | 8192 bytes | 
  public | application_template       | table | postgres | 40 kB      | 
  public | applications               | table | postgres | 56 kB      | 
  public | auditlog                   | table | postgres | 0 bytes    | 
  public | auditlog_details           | table | postgres | 8192 bytes | 

添加索引

CREATE INDEX idx_ke_kid
 ?
   ON base.keyword_engine
 ?
   USING btree
 ?
   (keyword_id);

2、创建用户以及相关权限设置

创建用户

create role zhaobsh;
 ?
 create user wzq with password '123456';                       #设置用户，并设置密码
 create database xc_hzh_linan owner xchzhlinan ;         #创建数据库以及所属用户

更改用户密码

alter role zhaobsh with password 'Test6530';
 ?
 or
 ?
 \password zhaobsh
 ?
 ALTER USER postgres WITH PASSWORD 'postgres';

注：

• 密码postgres要用引号引起来

• 命令最后有分号

修改数据库 属主

 alter database "GSCloud1903" owner to zhaobsh;
 ALTER ROLE user_4 WITH CREATEROLE;            /*赋予创建角色的权限*/  
 alter database "GSCloud1903" rename to zhaobsh;              --修改schema的名称

授权给用户 PostgreSQL中预定义了许多不同类型的内置权限，如：SELECT、INSERT、UPDATE、DELETE、RULE、REFERENCES、TRIGGER、CREATE、TEMPORARY、EXECUTE和USAGE。

grant all on database "GSCloud1903" to z