Appearance
入门介绍
本章将介绍PostgreSQL的基本概念、安装配置方法以及psql命令行工具的基本使用。
PostgreSQL概述
什么是PostgreSQL
PostgreSQL是一个功能强大的开源对象关系型数据库管理系统（ORDBMS），由加州大学伯克利分校计算机系开发。它以可靠性、数据完整性和正确性著称。
sql
-- PostgreSQL的核心特点：
-- 1. 对象关系型数据库
-- 不仅支持关系型特性，还支持面向对象特性
-- 支持自定义类型、继承等
-- 2. 标准兼容性
-- 高度兼容ANSI SQL标准
-- 支持复杂查询、子查询、窗口函数
-- 3. 扩展性
-- 支持自定义函数、操作符、类型
-- 支持扩展插件（如PostGIS、pg_trgm）
-- 4. 并发控制
-- 使用MVCC（多版本并发控制）
-- 读操作不阻塞写操作，写操作不阻塞读操作
-- 5. 数据完整性
-- 支持外键、约束、触发器
-- 支持事务的ACID特性PostgreSQL架构
sql
-- PostgreSQL的进程架构：
-- 1. postmaster（主进程）
-- - 监听客户端连接
-- - 为每个连接创建postgres进程
-- - 管理其他后台进程
-- 2. postgres（服务进程）
-- - 处理客户端请求
-- - 每个连接一个独立进程
-- 3. 后台进程
-- - bgwriter：后台写进程，将脏页写入磁盘
-- - walwriter：WAL日志写进程
-- - checkpointer：检查点进程
-- - autovacuum launcher：自动清理调度进程
-- - stats collector：统计信息收集进程
-- 内存结构：
-- - Shared Buffers：共享缓冲区，缓存数据页
-- - WAL Buffers：WAL日志缓冲区
-- - Work Memory：排序、哈希等操作的内存
-- - Maintenance Memory：维护操作内存安装PostgreSQL
Windows安装
powershell
# Windows安装步骤：
# 1. 下载安装包
# 访问官网：https://www.postgresql.org/download/windows/
# 或使用EDB安装包
# 2. 运行安装程序
# 双击安装文件，按向导安装
# 3. 选择安装组件
# - PostgreSQL Server：数据库服务器
# - pgAdmin 4：图形化管理工具
# - Stack Builder：扩展包管理器
# - Command Line Tools：命令行工具
# 4. 设置数据目录和密码
# 选择数据存储位置
# 设置超级用户postgres的密码
# 5. 设置端口
# 默认端口：5432
# 6. 验证安装
psql --version # 查看版本
# 7. 连接数据库
psql -U postgres # 使用postgres用户连接Linux安装
bash
# Ubuntu/Debian安装步骤：
# 1. 添加PostgreSQL官方源
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# 2. 导入签名密钥
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# 3. 更新包列表
sudo apt-get update
# 4. 安装PostgreSQL
sudo apt-get install -y postgresql postgresql-contrib
# 5. 启动服务
sudo systemctl start postgresql
sudo systemctl enable postgresql
# 6. 切换到postgres用户
sudo -u postgres psql
# CentOS/RHEL安装步骤：
# 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
sudo yum install -y postgresql-server postgresql-contrib
# 3. 初始化数据库
sudo postgresql-setup initdb
# 4. 启动服务
sudo systemctl start postgresql
sudo systemctl enable postgresqlmacOS安装
bash
# macOS安装步骤：
# 方式一：使用Homebrew（推荐）
# 1. 安装
brew install postgresql@16
# 2. 启动服务
brew services start postgresql@16
# 3. 连接数据库
psql postgres
# 方式二：使用Postgres.app
# 1. 下载Postgres.app
# 2. 拖到Applications文件夹
# 3. 启动应用
# 4. 配置PATH环境变量Docker安装
bash
# 使用Docker运行PostgreSQL
# 1. 拉取镜像
docker pull postgres:16
# 2. 运行容器
docker run -d \
--name postgres \
-e POSTGRES_PASSWORD=password123 \
-e POSTGRES_USER=admin \
-e POSTGRES_DB=mydb \
-p 5432:5432 \
-v postgres_data:/var/lib/postgresql/data \
postgres:16
# 3. 查看容器状态
docker ps
# 4. 进入容器
docker exec -it postgres psql -U admin -d mydb
# 使用Docker Compose
# docker-compose.yml内容：
version: '3.8'
services:
postgres:
image: postgres:16
container_name: postgres
environment:
POSTGRES_USER: admin
POSTGRES_PASSWORD: password123
POSTGRES_DB: mydb
ports:
- "5432:5432"
volumes:
- postgres_data:/var/lib/postgresql/data
volumes:
postgres_data:
# 启动服务
docker-compose up -dpsql命令行工具
连接数据库
sql
-- psql是PostgreSQL的命令行工具
-- 基本连接方式
psql -h localhost -p 5432 -U postgres -d mydb
-- 参数说明：
-- -h：主机地址
-- -p：端口号
-- -U：用户名
-- -d：数据库名
-- 使用连接字符串
psql "postgresql://postgres:password@localhost:5432/mydb"
-- 使用环境变量
export PGHOST=localhost
export PGPORT=5432
export PGUSER=postgres
export PGDATABASE=mydb
psql -- 直接连接
-- 连接成功后的提示符
-- mydb=# 表示超级用户
-- mydb=> 表示普通用户常用元命令
sql
-- psql元命令（以\开头）
-- 连接相关
\c mydb -- 连接到mydb数据库
\c mydb user -- 使用user用户连接mydb数据库
-- 查看信息
\l -- 列出所有数据库
\dt -- 列出当前数据库的所有表
\dt+ -- 列出表的详细信息
\d table_name -- 查看表结构
\d+ table_name -- 查看表详细结构
\di -- 列出索引
\df -- 列出函数
\du -- 列出所有用户
-- 格式化输出
\x -- 切换扩展显示模式（纵向显示）
\t -- 只显示数据，不显示标题
\a -- 切换对齐模式
-- 执行外部文件
\i script.sql -- 执行SQL脚本文件
-- 输出重定向
\o output.txt -- 将输出重定向到文件
\o -- 关闭重定向
-- 其他命令
\timing -- 显示SQL执行时间
\q -- 退出psql
\? -- 显示帮助信息
\h SELECT -- 显示SELECT语法帮助
\! command -- 执行系统命令执行SQL脚本
sql
-- 执行SQL脚本的方式
-- 方式一：在psql中使用\i
\i /path/to/script.sql
-- 方式二：命令行执行
psql -U postgres -d mydb -f script.sql
-- 方式三：使用管道
cat script.sql | psql -U postgres -d mydb
-- 方式四：使用-e参数显示执行的SQL
psql -U postgres -d mydb -f script.sql -e配置文件
postgresql.conf
ini
# PostgreSQL主配置文件
# 连接设置
listen_addresses = '*' # 监听所有IP
port = 5432 # 端口号
max_connections = 100 # 最大连接数
# 内存设置
shared_buffers = 256MB # 共享缓冲区大小
work_mem = 4MB # 排序、哈希操作内存
maintenance_work_mem = 64MB # 维护操作内存
effective_cache_size = 4GB # 预估系统可用缓存
# WAL设置
wal_level = replica # WAL级别
max_wal_size = 1GB # WAL最大大小
min_wal_size = 80MB # WAL最小大小
# 日志设置
logging_collector = on # 启用日志收集
log_directory = 'log' # 日志目录
log_filename = 'postgresql-%Y-%m-%d.log' # 日志文件名
log_statement = 'all' # 记录所有SQL语句
# 查询优化
random_page_cost = 1.1 # 随机IO代价（SSD可调低）
effective_io_concurrency = 200 # IO并发数pg_hba.conf
ini
# 客户端认证配置文件
# 格式：TYPE DATABASE USER ADDRESS METHOD
# 本地连接
local all all trust
# IPv4本地连接
host all all 127.0.0.1/32 scram-sha-256
# IPv6本地连接
host all all ::1/128 scram-sha-256
# 允许所有IP连接（需要密码）
host all all 0.0.0.0/0 scram-sha-256
# 复制连接
host replication all 127.0.0.1/32 scram-sha-256
# 认证方法说明：
# trust：信任连接，无需密码
# scram-sha-256：SCRAM认证（推荐）
# md5：MD5密码认证
# password：明文密码（不安全）
# peer：使用操作系统用户名
# ident：使用ident服务验证数据库基本操作
创建数据库
sql
-- 创建数据库
-- 基本语法
CREATE DATABASE mydb;
-- 指定所有者
CREATE DATABASE mydb OWNER myuser;
-- 指定编码和区域
CREATE DATABASE mydb
WITH
ENCODING = 'UTF8'
LC_COLLATE = 'zh_CN.UTF-8'
LC_CTYPE = 'zh_CN.UTF-8'
TEMPLATE = template0;
-- 使用已存在的数据库作为模板
CREATE DATABASE mydb_copy TEMPLATE mydb;
-- 查看数据库列表
SELECT datname FROM pg_database;
-- 使用psql元命令
\l删除数据库
sql
-- 删除数据库
-- 基本语法
DROP DATABASE mydb;
-- 如果存在则删除
DROP DATABASE IF EXISTS mydb;
-- 注意：不能删除正在连接的数据库
-- 需要先断开所有连接
-- 断开所有连接后删除
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'mydb' AND pid <> pg_backend_pid();
DROP DATABASE mydb;切换数据库
sql
-- 切换数据库
-- 在psql中使用\c
\c mydb
-- 查看当前数据库
SELECT current_database();
-- 查看当前用户
SELECT current_user;本章小结
本章介绍了PostgreSQL的基础知识：
- PostgreSQL概述：了解PostgreSQL的特点和架构
- 安装配置：掌握在Windows、Linux、macOS和Docker中安装PostgreSQL
- psql工具：学会使用psql命令行工具连接和操作数据库
- 配置文件：了解postgresql.conf和pg_hba.conf的主要配置
- 基本操作：掌握数据库的创建、删除和切换
下一章，我们将学习数据库操作，了解更详细的数据库管理知识。