PostgreSQL 数据库设计与管理(四)
1. 数据库设计原则
1.1 规范化
规范化是组织数据库结构的一种方法,旨在减少数据冗余并提高数据完整性。常用的规范化范式包括:
- 第一范式(1NF): 确保每列都是原子的,不可再分。
- 第二范式(2NF): 满足1NF,且每个非主属性完全依赖于主键。
- 第三范式(3NF): 满足2NF,且每个非主属性不传递依赖于主键。
1.2 反规范化
在某些情况下,出于性能考虑,可以进行反规范化。反规范化是适当地引入冗余数据,以减少查询时的联接操作,从而提高性能。
1.3 实体关系模型(ER模型)
ER模型用于描述数据和数据之间的关系。它包括实体(如用户、订单)、属性(如用户名、订单金额)和关系(如用户与订单之间的一对多关系)。
1.4 数据完整性
数据完整性是指数据库中数据的准确性和一致性。常见的数据完整性约束包括:
- 实体完整性: 每个表都有主键,且主键唯一且不为空。
- 参照完整性: 外键约束,确保引用的记录在外键表中存在。
- 域完整性: 限制列中的数据类型和取值范围。
2. 数据库管理
2.1 事务与并发控制
事务是一个或多个SQL操作的集合,具有以下四个特性(ACID):
- 原子性(Atomicity): 事务要么全部执行,要么全部回滚。
- 一致性(Consistency): 事务执行前后,数据库保持一致性状态。
- 隔离性(Isolation): 各事务之间相互隔离,互不影响。
- 持久性(Durability): 事务一旦提交,数据将永久保存。
PostgreSQL 使用多版本并发控制(MVCC)来实现高效的并发控制。
2.2 备份与恢复
备份是保护数据免受损失的重要手段。PostgreSQL 提供了多种备份和恢复方法:
2.2.1 逻辑备份
使用 pg_dump 工具进行逻辑备份:
pg_dump dbname > dbname_backup.sql
恢复逻辑备份:
psql dbname < dbname_backup.sql
2.2.2 物理备份
使用 pg_basebackup 工具进行物理备份:
pg_basebackup -D /path/to/backupdir -Fp -Xs -P
恢复物理备份涉及到将备份文件复制回数据目录,并启动数据库。
2.3 安全管理
确保数据库的安全性非常重要。以下是一些常见的安全管理措施:
2.3.1 用户和权限管理
创建用户和角色:
CREATE ROLE readaccess;
CREATE USER alice WITH PASSWORD 'password';
GRANT readaccess TO alice;
授予权限:
GRANT SELECT ON TABLE users TO readaccess;
2.3.2 数据加密
PostgreSQL 支持 TLS/SSL 加密通信。配置SSL证书以启用加密:
编辑 postgresql.conf 文件,设置 ssl = on:
ssl = on
配置 pg_hba.conf 文件,启用加密连接:
hostssl all all 0.0.0.0/0 md5
2.3.3 审计和日志
配置审计日志记录用户活动和SQL查询。使用 pgaudit 扩展来实现审计功能:
安装 pgaudit:
CREATE EXTENSION pgaudit;
配置审计选项:
pgaudit.log = 'all'
3. 实战演练
3.1 练习题目
-
设计一个图书管理系统,包括以下实体:
- 用户(
id,username,email) - 图书(
id,title,author,published_date) - 借阅记录(
id,user_id,book_id,borrow_date,return_date)
- 用户(
-
为上述系统定义外键约束,以确保数据完整性。
-
实现用户借阅图书的事务操作,确保数据一致性。
-
配置数据库用户权限,使得普通用户只能查询图书信息,而管理员可以执行所有操作。
3.2 示例答案
- 设计图书管理系统:
CREATE TABLE users (id SERIAL PRIMARY KEY,username VARCHAR(50) NOT NULL,email VARCHAR(100) NOT NULL
);CREATE TABLE books (id SERIAL PRIMARY KEY,title VARCHAR(100) NOT NULL,author VARCHAR(100) NOT NULL,published_date DATE
);CREATE TABLE borrow_records (id SERIAL PRIMARY KEY,user_id INTEGER REFERENCES users(id),book_id INTEGER REFERENCES books(id),borrow_date DATE NOT NULL,return_date DATE
);
-
定义外键约束(已在上面的表定义中体现)。
-
用户借阅图书的事务操作:
BEGIN;INSERT INTO borrow_records (user_id, book_id, borrow_date)
VALUES (1, 2, CURRENT_DATE);UPDATE books
SET status = 'borrowed'
WHERE id = 2;COMMIT;
- 配置数据库用户权限:
创建角色和用户:
CREATE ROLE librarian;
CREATE ROLE member;
CREATE USER admin WITH PASSWORD 'adminpassword';
CREATE USER user1 WITH PASSWORD 'userpassword';GRANT librarian TO admin;
GRANT member TO user1;
授予权限:
GRANT SELECT ON TABLE books TO member;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO librarian;
系统文章目录:
PostgreSQL 简介与基础(一)
PostgreSQL 基本SQL语法(二)
PostgreSQL 高级SQL查询(三)
PostgreSQL 数据库设计与管理(四)
PostgreSQL 高级功能(五)
PostgreSQL 性能优化与调优(六)
PostgreSQL 高可用性与灾难恢复策略(七)
PostgreSQL 安全性与权限管理(八)
PostgreSQL 高级功能与扩展(九)
PostgreSQL 分区表与并行查询(十)
PostgreSQL 索引优化与性能调优(十一)
PostgreSQL 日志管理与故障排查(十二)
PostgreSQL 高可用性与容错性(十三)
相关文章:
PostgreSQL 数据库设计与管理(四)
1. 数据库设计原则 1.1 规范化 规范化是组织数据库结构的一种方法,旨在减少数据冗余并提高数据完整性。常用的规范化范式包括: 第一范式(1NF): 确保每列都是原子的,不可再分。第二范式(2NF&a…...
Studying-代码随想录训练营day21| 669.修建二叉搜索树、108.将有序数组转换为二叉搜索树、538.把二叉搜索树转换为累加树、二叉树总结
第21天,二叉树最后一篇,冲💪 目录 669.修建二叉搜索树 108.将有序数组转换为二叉搜索树 538.把二叉搜索树转换为累加树 二叉树总结 669.修建二叉搜索树 文档讲解:代码随想录修建二叉搜索树 视频讲解:手撕修建二叉…...
GraphQL:简介
GraphQL 图片来源: 我们将探索GraphQL 的基础知识,并学习如何使用Apollo将其与 React 和 React Native 等前端框架连接起来。这将帮助您了解如何使用 GraphQL、React、React Native 和 Apollo 构建现代、高效的应用程序。 什么是 GraphQL?…...
AI大模型安全挑战和安全要求解读
引言 随着人工智能技术的飞速发展,大模型技术以其卓越的性能和广泛的应用前景,正在重塑人工智能领域的新格局。然而,任何技术都有两面性,大模型在带来前所未有便利的同时,也引发了深刻的安全和伦理挑战。 大模型&…...
前端面试题-token的存放位置
哈喽小伙伴们大家好,本系列是一个专门针对前端开发岗的面试题系列,每周将会不定期分享一些面试题,希望对大家有所帮助. 面试官:token 一般在客户端存在哪儿 求职者:Token一般在客户端存在以下几个地方: (1)Cookie:Token可以存储在客户端的Cookie中。服…...
深入探讨计算机网络中的各种报文
在计算机网络中,报文(Packet)是数据传输的基本单位。不同的协议使用不同类型的报文来实现数据传输的各种功能。本文将详细探讨计算机网络中常见的几种报文类型,并通过举例说明其具体应用。 一、TCP/IP协议栈中的报文 TCP/IP协议…...
Debezium系列之:Mysql和SQLServer数据库字段类型覆盖测试
Debezium系列之:Mysql和SQLServer数据库字段类型覆盖测试 一、需求背景二、类型对比三、完整流程三、Mysql数据库全字段类型覆盖测试四、SQLServer数据库字段类型覆盖测试一、需求背景 Debezium版本升级迭代,要做字段类型测试,确保版本间字段类型的差异下游能够自动适应,或…...
Mathtype7在Word2016中闪退(安装过6)
安装教程:https://blog.csdn.net/Little_pudding10/article/details/135465291 Mathtype7在Word2016中闪退是因为安装过Mathtype6,MathPage.wll和MathType Comm***.dotm),不会随着Mathtype的删除自动删除,而新版的Mathtype中的文件…...
SQL面试题练习 —— 合并用户浏览行为
目录 1 题目2 建表语句3 题解 1 题目 有一份用户访问记录表,记录用户id和访问时间,如果用户访问时间间隔小于60s则认为时一次浏览,请合并用户的浏览行为。 样例数据 ------------------------ | user_id | access_time | ---------------…...
【Docker】docker 替换宿主与容器的映射端口和文件路径
every blog every motto: You can do more than you think. https://blog.csdn.net/weixin_39190382?typeblog 0. 前言 docker 替换宿主与容器的映射端口和文件夹 1. 正文 1.1 关闭docker 服务 systemctl stop docker1.2 找到容器的配置文件 cd /var/lib/docker/contain…...
GPU算力租用平台推荐
推荐以下几家GPU算力租用平台: 1. AWS (Amazon Web Services) EC2 - AWS提供多种GPU实例,适合不同的计算需求,如机器学习、深度学习和图形渲染等。 - 优点:全球覆盖面广,稳定性高,服务支持全面。 …...
定个小目标之刷LeetCode热题(31)
238. 除自身以外数组的乘积 给你一个整数数组 nums,返回 数组 answer ,其中 answer[i] 等于 nums 中除 nums[i] 之外其余各元素的乘积 。题目数据 保证 数组 nums之中任意元素的全部前缀元素和后缀的乘积都在 32 位 整数范围内。请 不要使用除法&#…...
我在高职教STM32——LCD液晶显示(3)
大家好,我是老耿,高职青椒一枚,一直从事单片机、嵌入式、物联网等课程的教学。对于高职的学生层次,同行应该都懂的,老师在课堂上教学几乎是没什么成就感的。正因如此,才有了借助 CSDN 平台寻求认同感和成就…...
uniapp横屏移动端卡片缩进轮播图
uniapp横屏移动端卡片缩进轮播图 效果: 代码: <!-- 简单封装轮播图组件:swiperCard --> <template><swiper class"swiper" circular :indicator-dots"true" :autoplay"true" :interval"10000&quo…...
整合Spring Boot和Apache Solr进行全文搜索
整合Spring Boot和Apache Solr进行全文搜索 大家好,我是免费搭建查券返利机器人省钱赚佣金就用微赚淘客系统3.0的小编,也是冬天不穿秋裤,天冷也要风度的程序猿! 在现代应用开发中,全文搜索是许多应用不可或缺的功能之…...
网络治理新模式:Web3时代的社会价值重构
随着Web3技术的崛起,传统的网络治理模式正在经历革新,这不仅仅是技术的进步,更是对社会价值观念的挑战和重构。本文将深入探讨Web3时代的网络治理新模式,其背后的技术基础、社会影响以及未来的发展方向。 1. 引言 Web3时代&#…...
[个人感悟] MySQL应该考察哪些问题?
前言 数据存储一直是软件开发中必不可少的一环, 从早期的文件存储txt, Excel, Doc, Access, 以及关系数据库时代的MySQL,SQL Server, Oracle, DB2, 乃至最近的大数据时代f非关系型数据库:Hadoop, HBase, MongoDB. 此外还有顺序型数据库InfluxDB, 图数据库Neo4J, 分布式数据库T…...
《数据结构与算法基础》学习笔记——1.2基本概念和术语
一、本章结构 二、四个数据相关专业名词的解释 两者的区别 三、数据结构相关内容 四、逻辑结构的分类 五、存储结构的分类及四种基本存储结构...
Java之线程相关应用实现
后台线程 一个进程中只有后台进程运行,该进程将会结束。 新创建的线程默认为前台线程,Java中只要有一个前台线程运行,就不会结束程序,如果只有后台线程运行,程序就会结束,可以在线程对象启动前执行setDae…...
一加全机型TWRP合集/橙狐recovery下载-20240603更新-支持一加12/Ace3V手机
TWRP是目前安卓平台的刷机神器,可快速刷写第三方ROM或官方系统,刷入TWRP之前需要解锁BL,目前已适配一加多个机型。ROM乐园小编20240603整理,涵盖一加1到一加Ace3V多机型专用TWRP文件,个人机型橙狐recovery适配相对完整…...
嵌入式C语言变量初始化技术详解
## 1. 嵌入式C语言变量初始化技术详解### 1.1 初始化的重要性与基本原则在嵌入式系统开发中,变量初始化是防止未定义行为的关键步骤。由于嵌入式编译器特性的差异,未初始化的变量可能包含随机值,导致系统出现不可预测的行为。根据变量类型的不…...
告别Transformer?手把手复现SegNeXt语义分割模型(附PyTorch代码)
从零实现SegNeXt:用纯卷积架构挑战Transformer的语义分割霸主地位 在计算机视觉领域,语义分割技术正经历着一场静默的革命。当大多数研究者将目光聚焦于Transformer架构时,SegNeXt却用纯粹的卷积神经网络(CNN)设计刷新…...
MFCMouseEffect:把桌面输入反馈这件事,做成一个真正可扩展的引擎
MFCMouseEffect:把桌面输入反馈这件事,做成一个真正可扩展的引擎 很多录屏、教程、演示和桌面工具,功能本身已经足够好,但一到“用户看你怎么操作”这一步,体验就会突然掉下来。 为什么? 因为点击不够明…...
Cursor最新版0.44.11配置DeepSeek-R1模型保姆级教程(含报错解决方案)
Cursor 0.44.11深度适配DeepSeek-R1模型全流程指南 当技术爱好者第一次在Cursor中尝试调用DeepSeek-R1模型时,往往会遇到各种"水土不服"的情况。就像刚拿到新相机的摄影师需要调整镜头焦距一样,我们需要对Cursor进行精确配置才能充分发挥这个强…...
Qwen1.5-0.5B-Chat电商应用:商品咨询机器人搭建教程
Qwen1.5-0.5B-Chat电商应用:商品咨询机器人搭建教程 1. 引言:为什么需要一个轻量级商品咨询机器人? 想象一下,你经营着一家网店,每天有成百上千的顾客涌入。他们的问题五花八门:“这件衣服有L码吗&#x…...
3步解锁iOS激活锁:Applera1n工具完整使用指南
3步解锁iOS激活锁:Applera1n工具完整使用指南 【免费下载链接】applera1n icloud bypass for ios 15-16 项目地址: https://gitcode.com/gh_mirrors/ap/applera1n 当你面对一部显示"激活锁"界面的iPhone,反复输入Apple ID却始终无法进入…...
PVB于EVA胶片的区别
PVB于EVA胶片的区别实例:PVB用于封装“双玻璃光伏组件”:玻璃+PVB+电池片+PVB+玻璃,PVB胶片已取代EVA胶片。为什么用PVB,不像我们现在一样用EVA?因为: 在玻璃…...
Java Web 新冠物资管理系统系统源码-SpringBoot2+Vue3+MyBatis-Plus+MySQL8.0【含文档】
摘要 新冠疫情的爆发对全球公共卫生体系提出了严峻挑战,物资管理成为疫情防控中的关键环节。传统物资管理方式依赖人工操作,效率低下且易出错,难以应对突发公共卫生事件中的大规模物资调配需求。为解决这一问题,新冠物资管理系统应…...
分享一份2026金三银四Java面试通关宝典!
金三银四快到了,不少人找LZ咨询,问我现在的面试需要提前准备什么?为了造福更多的开发者,也为了让更多的小伙伴通过面试;LZ近期也一直想着怎么才能帮到大家。所以近期在各大渠道整合大厂相关面试题,并结合了…...
HunyuanVideo-Foley应用场景:播客自动化剪辑、TTS语音情感增强音效
HunyuanVideo-Foley应用场景:播客自动化剪辑与TTS语音情感增强音效 1. 镜像概述与核心能力 HunyuanVideo-Foley私有部署镜像是一款专为音视频生成任务优化的AI工具包,特别针对RTX 4090D 24GB显存显卡进行了深度优化。这个开箱即用的解决方案将视频生成…...
