PostgreSQL常用SQL语句

1. 创建数据库、用户，并授予用户权限

# 创建用户并指定owner（不指定owner，对应的用户授权了也看不见）
CREATE DATABASE xxx owner xxx;
create user test with password 'test';

# 授权
GRANT ALL PRIVILEGES ON DATABASE xxx TO xxx;
GRANT ALL PRIVILEGES ON all tables in schema public TO xxx;
GRANT ALL ON SCHEMA public TO xxx;
GRANT USAGE ON SCHEMA public TO xxx;

# 重载配置，让修改生效
SELECT pg_reload_conf();

2. 删除数据库

如果有授权信息，删除数据库时会报错，要删除数据库需要先取消授权。

# 禁止连接
UPDATE pg_database 
SET datallowconn = 'false' 
WHERE datname = 'xxx';

# 关闭已连接进程
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'xxx';

# 取消授权并删除用户
revoke all on database postgres from xxx;
revoke all on all tables in schema public from xxx;
revoke all on schema public from xxx;
revoke usage on schema public from xxx;

# 删除用户(不取消授权也无法删除用户)
drop role xxx;

# 重载配置，让修改生效
SELECT pg_reload_conf();

3. 重命名用户、数据库

# 重命名用户
ALTER USER name RENAME TO new_name;

# 重命名数据库
ALTER DATABASE name RENAME TO new_name;

4. 修改密码

ALTER USER xxx WITH PASSWORD 'xxx';

5. 其他常用

# 统计当前所有连接数
select count(1) from pg_stat_activity;

# 查询当前连接数详细信息
select * from pg_stat_activity;

# 查询最大连接数
show max_connections;