精通PostgreSQL：解锁高效数据库管理的十大必备技巧与最佳实践

•  作者简介：我是团团儿，是一名专注于云计算领域的专业创作者，感谢大家的关注
•  座右铭：   云端筑梦，数据为翼，探索无限可能，引领云计算新纪元
•  个人主页：团儿.-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_activity

WHERE 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 #指定输出文件或目录名。

---

期待您的关注~