- 作者简介：我是团团儿，是一名专注于云计算领域的专业创作者，感谢大家的关注
- 座右铭： 云端筑梦，数据为翼，探索无限可能，引领云计算新纪元
- 个人主页：团儿.-CSDN博客
目录
前言：
正文：
一.用户管理
1.创建账号
2.设置只读权限
3.设置可操作的数据库
4.授权可操作的模式和权限
-- 授权
-- 删除账号
二.模式 Schema
使用模式的优势：
语法格式如下:
创建和当前用户同名模式（schema）
自定义创建模式（schema）
查看数据库下的所有（schema）
三.数据库管理
查询所有数据库
创建数据库
删除数据库
四.表管理
建表模板语句
查询schema中所有表
创建表
创建自增序列
创建主键序列
根据已有表结构创建表
删除表
五.索引管理
创建索引
创建唯一索引
查看索引
执行sql脚本
方式一：先登录再执行
方式二：通过psql执行
导出数据到SQL文件
前言：
在当今数据驱动的时代，数据库作为信息系统的核心组件，其重要性不言而喻。PostgreSQL，作为一款功能强大、开源的对象-关系数据库管理系统（ORDBMS），凭借其卓越的稳定性、丰富的特性集、以及对高级功能的支持（如全文搜索、地理空间数据处理等），赢得了全球范围内众多开发者和企业的青睐。无论是初创公司还是大型企业，PostgreSQL都成为了他们构建复杂应用、处理海量数据、实现数据驱动决策的首选数据库之一。
然而，要充分发挥PostgreSQL的潜力，高效地进行数据库管理至关重要。数据库管理不仅涉及日常的运维任务，如备份恢复、性能调优、安全加固等，还涵盖了数据架构设计、查询优化、并发控制等深层次的技术挑战。对于数据库管理员（DBA）和开发者而言，掌握一套PostgreSQL的常用管理操作，不仅能够提升工作效率，还能确保数据库的稳定运行和高效性能，为业务的发展提供坚实的数据支撑。
本文旨在为广大PostgreSQL用户、数据库管理员及开发者提供一份实用的指南，详细介绍PostgreSQL的常用管理操作。从基础的数据库安装与配置，到进阶的性能调优与故障排查，再到高级的数据迁移与备份恢复策略，我们将一步步带你深入PostgreSQL的世界，让你能够轻松应对各种数据库管理挑战。
正文：
一.用户管理
1.创建账号
create user 用户名 password '密码';
2.设置只读权限
alter user 用户名 set default_transaction_read_only = on;
3.设置可操作的数据库
grant all on database 数据库名 to 用户名;
4.授权可操作的模式和权限
-- 授权
grant select on all tables in schema public to 用户名;
-- 删除账号
#撤回在public模式下的权限
revoke select on all tables in schema public from 用户名;#撤回在information_schema模式下的权限
revoke select on all tables in schema information_schema from 用户名;#撤回在pg_catalog模式下的权限
revoke select on all tables in schema pg_catalog from 用户名;#撤回对数据库的操作权限
revoke all on database 数据库名 from 用户名;
#删除用户
drop user 用户名;
二.模式 Schema
PostgreSQL 模式SCHEMA 可以看着是一个表的集合。一个模式可以包含视图、索引、数据类型、函数和操作符等。
相同的对象名称可以被用于不同的模式中而不会出现冲突，例如 schema1 和 myschema 都可以包含名为 mytable 的表。
使用模式的优势：
允许多个用户使用一个数据库并且不会互相干扰。
将数据库对象组织成逻辑组以便更容易管理。
第三方应用的对象可以放在独立的模式中，这样它们就不会与其他对象的名称发生冲突。
语法格式如下:
创建和当前用户同名模式（schema）
create schema AUTHORIZATION CURRENT_USER;
自定义创建模式（schema）
create schema 模式名称;
注意：如果不创建scheme，并且语句中不写scheme，则默认scheme使用内置的public。
查看数据库下的所有（schema）
select * from information_schema.schemata;
三.数据库管理
查询所有数据库
select datname from pg_database;
创建数据库
create database 数据库名 owner 所属用户 encoding UTF8;
注意：创建完数据库，需要切换到数据库下，创建和当前用户同名scheme，删除数据库后schema也会一并删除：
-- 重新登陆到新数据库下，执行如下语句
create schema AUTHORIZATION CURRENT_USER;删除数据库
drop database 数据库名;
注意：删库前需要关闭所有会话，不然会提示：ERROR: database "mydb" is being accessed by other users
关闭数据库所有会话
SELECT pg_terminate_backend(pg_stat_activity.pid)FROM pg_stat_activityWHERE datname='mydb' AND pid<>pg_backend_pid();
四.表管理
建表模板语句
create table "t_user" ("id" bigserial not null,"username" varchar (64) not null,"password" varchar (64) not null,"create_time" timestamp not null default current_timestamp,"update_time" timestamp not null default current_timestamp,constraint t_user_pk primary key (id));comment on column "t_user"."id" is '主键';
comment on column "t_user"."username" is '用户名';
comment on column "t_user"."password" is '密码';
comment on column "t_user"."create_time" is '创建时间';
comment on column "t_user"."update_time" is '更新时间';
查询schema中所有表
select table_name from information_schema.tables where table_schema = 'myuser';
创建表
CREATE TABLE public.t_user ("id" BIGSERIAL NOT NULL,"username" VARCHAR(64) NOT NULL,"password" VARCHAR(64) NOT NULL,"create_time" TIMESTAMP(0) default CURRENT_TIMESTAMP not null,"update_time" TIMESTAMP(0) default CURRENT_TIMESTAMP not null);-- 注释
COMMENT ON TABLE public.t_user IS '用户表';
COMMENT ON COLUMN public.t_user.id IS '主键';
COMMENT ON COLUMN public.t_user.username IS '用户名';
COMMENT ON COLUMN public.t_user.password IS '密码';
COMMENT ON COLUMN public.t_user.create_time IS '创建时间';
COMMENT ON COLUMN public.t_user.update_time IS '更新时间';
创建自增序列
alter sequence "t_user_ID_seq" restart with 1 increment by 1;创建主键序列
drop index if exists "t_user_pkey";alter table "t_user" add constraint "t_user_pkey" primary key ("ID");根据已有表结构创建表
create table if not exists 新表 (like 旧表 including indexes including comments including defaults);删除表
drop table if exists "t_template" cascade;五.索引管理
创建索引
drop index if exists t_user_username;create index t_user_username on t_user (username);创建唯一索引
drop index if exists t_user_username;create index t_user_username on t_user (username);查看索引
\d t_user执行sql脚本
方式一：先登录再执行
\i testdb.sql方式二：通过psql执行
psql -d testdb -U postgres -f /pathA/xxx.sql导出数据到SQL文件
pg_dump -h localhost -p 5432 -U postgres --column-inserts -t table_name -f save_sql.sql database_name--column-inserts #以带有列名的 `INSERT` 命令形式转储数据。
-t #只转储指定名称的表。
-f #指定输出文件或目录名。
期待您的关注~
