【PostgreSQL】【第1章】入门&安装-EW帮帮网

1-PostgreSQL入门&安装

1. 今日安排

核心内容：
1. PostgreSQL 简介与适用场景
2. PostgreSQL vs MySQL 核心差异
3. Linux 环境安装（非 Docker）
4. 远程连接与日志配置
5. 基本操作：用户、数据库、权限管理

2. PostgreSQL 介绍

2.1 核心特性

开源协议：基于 BSD/MIT 协议，支持二次封装商业化。
版本选择：
- 稳定版：12.x
- 新特性版：14.x
社区活跃：版本迭代快（约 3 个月一次），Bug 修复及时。

2.2 国内应用

华为高斯DB、腾讯 TBase 等国产数据库基于 PostgreSQL 二次开发。

3. PostgreSQL vs MySQL 对比

3.1 核心差异

特性	PostgreSQL	MySQL
数据类型	支持几何、数组等复杂类型	基础类型为主
主键自增	序列（Sequence）	`AUTO_INCREMENT`
插件生态	丰富（如 pgloader）	较少
主从同步	支持同步复制	异步复制为主
MVCC 实现	单行多版本存储	基于 Undo 日志

3.2 适用场景

PostgreSQL：复杂查询、GIS 数据、高并发写入。
MySQL：简单查询、高读取频率场景。

4. PostgreSQL 安装

4.1 环境要求

操作系统：CentOS 7.x（推荐 7.6/7.8）
用户权限：非 root 用户（默认创建 postgres 用户）。

4.2 安装步骤

# 1. 下载 RPM 包
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# 2. 安装 PostgreSQL 12
sudo yum install -y postgresql12-server

# 3. 初始化数据库
sudo /usr/pgsql-12/bin/postgresql-12-setup initdb

# 4. 启动服务
sudo systemctl start postgresql-12
sudo systemctl enable postgresql-12

5. 配置远程连接

5.1 修改配置文件

允许所有 IP 连接：
```
sudo vi /var/lib/pgsql/12/data/postgresql.conf
```
修改 listen_addresses = '*'

配置访问权限：

sudo vi /var/lib/pgsql/12/data/pg_hba.conf

添加行：

host    all    all    0.0.0.0/0    md5

重启服务：
```
sudo systemctl restart postgresql-12
```

6. 日志配置

sudo vi /var/lib/pgsql/12/data/postgresql.conf

关键参数：

logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%a.log'  # 按周分割日志
log_truncate_on_rotation = on      # 覆盖旧日志

7. 基本操作

7.1 用户与数据库

-- 创建用户
CREATE USER dev_user WITH PASSWORD '123456';

-- 创建数据库
CREATE DATABASE dev_db OWNER dev_user;

-- 进入数据库
\c dev_db

7.2 Schema 与权限

-- 创建 Schema
CREATE SCHEMA dev_schema;

-- 授权 Schema 权限
GRANT USAGE ON SCHEMA dev_schema TO dev_user;

-- 创建表并授权
CREATE TABLE dev_schema.users (id SERIAL, name TEXT);
GRANT SELECT, INSERT, UPDATE ON dev_schema.users TO dev_user;

8. 小任务

创建用户与数据库：
- 用户：yourname
- 数据库：yourname_db
Schema 操作：
- 创建 Schema your_schema
- 授权 yourname 用户对 Schema 的读写权限
表操作：
- 在 your_schema 下创建表 tasks
- 授权 yourname 用户对表的 SELECT/INSERT/UPDATE 权限

提示：使用 \help 查看命令语法。

9. 图形化工具推荐

Navicat：支持 PostgreSQL 连接。
pgAdmin：官方开源管理工具。

本教程基于 CentOS 7 环境编写，其他 Linux 发行版需调整命令路径。

10. PostgreSQL基础操作与数据类型指南

权限操作与小任务

1. PostgreSQL逻辑结构

层级关系：

集群（Cluster） → 数据库（Database） → 模式（Schema） → 对象（表、视图、索引等）

权限级别：
- Server/Cluster级：通过 pg_hba.conf 配置。
- Database级：使用 GRANT 命令。
- Schema级：较少使用。
- 对象级：最常用，通过 GRANT 控制表/视图的增删改查权限。

2. 小任务：创建用户、数据库与权限分配

-- 1. 创建用户
CREATE USER laozheng WITH PASSWORD 'laozheng';

-- 2. 创建数据库
CREATE DATABASE laozheng;

-- 3. 切换数据库
\c laozheng

-- 4. 创建Schema并修改拥有者
CREATE SCHEMA laozheng_schema;
ALTER SCHEMA laozheng_schema OWNER TO laozheng;

-- 5. 赋予表权限
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA laozheng_schema TO laozheng;

11. 课程回顾与实战操作

1. 用户切换问题

报错原因：pg_hba.conf 默认使用 peer 认证，需系统用户与数据库用户同名。
解决方案：改用 远程连接 或修改认证方式为 md5：
```
psql -h 192.168.1.1 -p 5432 -U laozheng -W
```

2. 生产环境最佳实践

权限控制：避免赋予 DELETE 权限，推荐逻辑删除。
安装方式：使用二进制安装，避免中文插件干扰。

3. 测试权限分配

-- 管理员创建表
CREATE TABLE laozheng_schema.test (id INT);

-- 用户测试权限（切换用户后执行）
INSERT INTO laozheng_schema.test VALUES (1);  -- 成功（INSERT权限）
DELETE FROM laozheng_schema.test WHERE id=1;  -- 失败（无DELETE权限）

pgAdmin图形化界面连接

1. 下载与安装

官方下载地址：pgAdmin官网
安装步骤：
1. 选择操作系统版本（Windows/macOS）。
2. 完成傻瓜式安装。

2. 配置连接

打开pgAdmin，点击 Add New Server。
输入连接信息：
- Name: 自定义名称（如 laozheng_server）。
- Connection:
  - Host: 192.168.1.1
  - Port: 5432
  - Username: laozheng
  - Password: laozheng

3. 常用操作

执行SQL：右键数据库 → Query Tool → 输入命令 → F5执行。
切换语言：File → Preferences → User Language → 选择中文。

数据类型详解（一）

1. 常用数据类型对比（PostgreSQL vs MySQL）

类型	PostgreSQL	MySQL	说明
布尔型	`BOOLEAN`	`TINYINT(1)`	PostgreSQL原生支持布尔
整型	`SMALLINT`, `INT`, `BIGINT`	同左	字节大小相同
浮点型	`DECIMAL`, `NUMERIC`	`DECIMAL`	高精度计算
字符串	`VARCHAR`, `TEXT`	`VARCHAR`, `TEXT`	PostgreSQL支持更大容量
日期时间	`DATE`, `TIME`, `TIMESTAMP`	同左	支持时区设置

2. 特殊类型

布尔型：直接存储 TRUE/FALSE。
JSONB：二进制存储JSON，支持索引和高效查询。
数组：支持多维数组，如 INT[]。

数据类型详解（二）

1. 扩展数据类型

类型	说明	示例
`BYTEA`	二进制数据（类似MySQL的BLOB）	存储图片、文件
`BIT(n)`	定长位图	`BIT(8)` 存储8位二进制
`ENUM`	枚举类型	`ENUM('active', 'inactive')`
`GEOMETRY`	几何图形（点、线、多边形）	地理信息系统（GIS）

2. 高级功能

IP地址：CIDR 类型校验IP格式。
复合类型：自定义组合字段，类似结构体。
区间类型：INTERVAL 表示时间跨度。

-- 示例：JSONB类型操作
CREATE TABLE user_profile (data JSONB);
INSERT INTO user_profile VALUES ('{"name": "Laozheng", "age": 30}');
SELECT data->>'name' AS name FROM user_profile;  -- 输出 "Laozheng"

关键术语高亮：

GRANT：权限分配命令。
JSONB：高效JSON存储格式。
pg_hba.conf：认证配置文件。
Schema：命名空间，用于组织数据库对象。

【PostgreSQL】【第1章】入门&安装

1-PostgreSQL入门&安装

1. 今日安排

2. PostgreSQL 介绍

2.1 核心特性

2.2 国内应用

3. PostgreSQL vs MySQL 对比

3.1 核心差异

3.2 适用场景

4. PostgreSQL 安装

4.1 环境要求

4.2 安装步骤

5. 配置远程连接

5.1 修改配置文件

6. 日志配置

7. 基本操作

7.1 用户与数据库

7.2 Schema 与权限

8. 小任务

9. 图形化工具推荐

10. PostgreSQL基础操作与数据类型指南

目录

权限操作与小任务

1. PostgreSQL逻辑结构

2. 小任务：创建用户、数据库与权限分配

11. 课程回顾与实战操作

1. 用户切换问题

2. 生产环境最佳实践

3. 测试权限分配

pgAdmin图形化界面连接

1. 下载与安装

2. 配置连接

3. 常用操作

数据类型详解（一）

1. 常用数据类型对比（PostgreSQL vs MySQL）

2. 特殊类型

数据类型详解（二）

1. 扩展数据类型

2. 高级功能

网站公告

今日签到

热门文章

最新发布