PostgreSQL基础操作

栏目：云苍穹知识作者：金蝶来源：金蝶云社区发布：2024-09-23浏览：1

PostgreSQL基础操作

1 PG常用命令

1.1 登录数据库

[postgres@eas root]$ psql （切换到postgres用户进入postgresql命令行）

或使用客户端连接数据库

psql -U postgres -d dbname -h 127.0.0.1 -p 5432 （-U 指定用户 -d 指定数据库 -h 指定服务器 -p 指定端口）

1.2 常用命令

postgres=# \? # 显示帮助信息

postgres=# \q # 退出 or: exit;

postgres=# \l # 列出所有库

postgres=# \du # 列出所有用户, 指令显示用户和用户的用户属性

postgres=# \d # 列出库下所有表

postgres=# \di # 查看索引

postgres=# \dn # 查看模式schema列表

postgres=# \dn+ # 查看模式schema列表详情

postgres=# \z 或 \dp # 显示用户访问权限

postgres=# \c dbname # 切换数据库,相当于mysql的use dbname

postgres=# \c - username #切换用户

postgres=# \d tblname # 查看表定义, 相当于desc tblname,show columns from tbname

postgres=# \db #列出现有的表空间。

2 PG用户角色管理

2.1 用户角色介绍

角色的属性

角色可以拥有一些属性或者叫权限，比如登录数据库，需要角色拥有LOGIN属性，这类权限在创建角色的时候指定，或者通过alter role来修改。

创建角色： create role 角色名 with [属性项] password 密码

修改角色属性：alter role 角色名 [属性项]

常用属性项：

SUPERUSER/NOSUPERUSER，创建出来的用户是否为超级用户，只有超级用户才能创建超级用户

INHERIT/NOINHERIT，指定创建的用户是否继承某些角色的权限

CREATEDB/NOCREATEDB，指定创建出来的用户是否有权限创建数据库

CREATEROLE/NOCREATEROLE，指定创建出来的用户是否有创建其他角色的权限

CONNECTION LIMIT connlimit，指定用户能够使用的最大并发连接数量，默认-1，表示没有限制

REPLICATION，指定复制权限

PASSWORD password，指定密码

VALID UNTIL 'timestamp'，指定密码失效时间，如果不指定，永远有效

角色(role)和用户(user)可以等同使用，创建用户时，默认就已经带上了LOGIN属性，而角色默认没有带任何属性。

2.2 创建用户和数据库

创建用户

postgres=# create user 用户名 with password '密码' CREATEDB;

#创建用户，给予创建库的权限

创建数据库

postgres=# CREATE DATABASE 库名 WITH OWNER = 用户名 ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = -1 TEMPLATE template0;

# 创建数据库，指定字符集，表空间，所属者

3 PG常用数据字典

3.1 常用数据字典

常用数据字典（视图）	说明
pg_tables	该视图提供了对有关数据库中每个表的有用信息地访问
pg_user	该视图提供了对数据库用户的相关信息的访问。这个视图只是pg_shadow表的公众可读的部分的视图化，但是不包含口令字段
pg_views	该视图提供了对数据库里每个视图的有用信息的访问途径
pg_indexes	该视图提供对数据库中每个索引的有用信息的访问
pg_proc	该视图存储关于函数(或过程)的信息
pg_database	该视图存储关于数据库的信息
pg_namespace	该视图存储关于表空间的信息
pg_roles	该视图提供访问数据库角色有关信息的接口。这个视图只是pg_authid表的公开可读部分的视图化，同时把口令字段用空白填充
pg_settings	该视图提供了对服务器运行时参数的访问。它实际上是SHOW和SET命令的另外一种方式

常用数据字典（视图）	说明
select version();	查看数据库版本
select pg_postmaster_start_time();	查看数据库启动时间
select pg_conf_load_time();	查看最近一次load配置文件的时间
show timezone;	查看数据库时区
select now();	查看当前系统时间
select user; 或者 select current_user;	查看当前用户名
select current_database();	查看当前连接数据库
select pg_backend_pid();	查询当前session的pid
show parameter_name;	查询参数配置
select * from pg_stat_activity;	查询长时间运行的sql
select pg_cancel_backend(pid)	取消正在执行的sql
select pg_terminate_backend(pid);	终止后台服务进程
select u.usename,p.oid,p.proname from pg_user u,pg_proc p where u.usesysid=p.proowner and u.usename='用户名';	查看当前用户所在库的存储过程