📘 PostgreSQL 入门（介绍 -> 安装 -> 配置）

官方地址：https://www.postgresql.org/
中文官方文档：http://www.postgres.cn/docs/14/

一：📘 PostgreSQL 简介

1、引言：为什么选择 PostgreSQL？

在数据库世界里，有这样一款系统，它不仅开源免费，还拥有强大的功能、良好的兼容性、极高的扩展性，并被全球无数企业和开发者所信赖——它就是 PostgreSQL，简称 Postgres。

如果你曾经使用过 MySQL、SQL Server 或 Oracle，那么 PostgreSQL 将是你通往更高阶数据库技术的理想选择。它被誉为“世界上最先进的开源关系型数据库系统”。

2、PostgreSQL 是什么？

PostgreSQL 是一个功能强大、符合 SQL 标准的对象-关系型数据库系统。

关系型数据库：数据以表格形式组织，通过 SQL 查询语言操作。
对象特性：支持继承、复杂数据类型、用户自定义函数等面向对象特性。
开源免费：采用 PostgreSQL 许可证（类似于 MIT），可自由用于商业和个人项目。
跨平台支持：Windows、Linux、macOS 皆可运行。

3、PostgreSQL 的核心优势

✅ 1. 标准合规 + 功能强大

完全支持 SQL 标准（SQL:2011）
支持事务、外键、视图、触发器、存储过程等
支持 多版本并发控制（MVCC），无锁读写更高效

✅ 2. 丰富的数据类型

除了常规的 INTEGER、VARCHAR、BOOLEAN 外，PostgreSQL 还支持：

JSON / JSONB：高效处理结构化文档数据
ARRAY：数组类型，可直接存储多个值
UUID, HSTORE, CIDR, GEOMETRY 等专业类型

✅ 3. 高度可扩展

PostgreSQL 支持动态加载扩展模块，比如：

PostGIS：空间地理信息处理
pg_trgm：字符串相似度搜索
full-text search：全文索引与搜索

开发者还可以创建自己的：

数据类型
操作符
索引方法
存储过程（支持多种语言，如 PL/pgSQL、Python、SQL、C）

✅ 4. 安全性和可靠性

支持细粒度权限控制（基于用户、角色）
提供备份/恢复工具（如 pg_dump、pg_restore）
支持 WAL（Write-Ahead Logging）机制，保障数据完整性

4、PostgreSQL 应用场景

PostgreSQL 被广泛应用于以下领域：

🏢 企业级系统：银行、ERP、CRM 系统
🌐 Web 开发：与 Django、Ruby on Rails、Node.js 等框架集成
📊 大数据分析：结合 TimescaleDB 做时间序列数据分析
📍 地理信息系统（GIS）：配合 PostGIS 处理地图和位置数据

5、PostgreSQL 与其他数据库的比较

特性	PostgreSQL	MySQL	Oracle	SQLite
开源免费	✅	✅	❌（商业许可）	✅
标准兼容性	⭐⭐⭐⭐⭐	⭐⭐⭐	⭐⭐⭐⭐	⭐⭐
扩展性	⭐⭐⭐⭐⭐	⭐⭐	⭐⭐⭐⭐	⭐
支持事务	✅	✅	✅	✅
JSON 支持	高效原生支持	基本支持	中等	基本支持
MVCC 并发控制	✅（强大）	部分支持	✅（强大）	❌

二：⚙️ PostgreSQL安装

官方地址：https://www.postgresql.org/download/

1、环境说明

在开始 PostgreSQL 的学习和实践之前，首先明确我们的系统环境和目标版本：

操作系统：Ubuntu 22.04 LTS
PostgreSQL 版本：14.18，14.15

PostgreSQL 的安装和配置方式会随着系统和版本略有不同，本笔记以 Ubuntu 系统为例，演示两种常见安装方式：apt 安装和源码编译安装。

2、安装方式详解

方法一：使用 APT 包管理器安装（推荐）-version14.18

Ubuntu 默认的软件源通常包含 PostgreSQL，但版本可能不是最新。为确保安装的是你指定的 14.18 版本，推荐添加官方 PostgreSQL 仓库。

步骤：

# 1. 更新系统软件包索引
sudo apt update

# 2. 安装必要工具
sudo apt install wget ca-certificates gnupg lsb-release

# 3. 自动化存储库配置：
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
复制脚本
# 4. 要手动配置 Apt 存储库，请按照以下步骤操作：
> 导入存储库签名密钥：
sudo apt install curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
> 创建存储库配置文件：
. /etc/os-release
sudo sh -c “echo 'deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $VERSION_CODENAME-pgdg main' > /etc/apt/sources.list.d/pgdg.list”

# 5. 更新软件包列表：
sudo apt update

# 6. 安装最新版本的 PostgreSQL：
# 如果您想要特定版本，请使用“postgresql-17”或类似版本代替“postgresql”
sudo apt -y install postgresql-14

服务管理命令：

确保服务启动

# 启动 PostgreSQL
sudo systemctl start postgresql

# 设置开机自启
sudo systemctl enable postgresql

# 查看服务状态
sudo systemctl status postgresql

安装完成后，确认版本：

默认情况下，Postgres使用称为“角色”的概念来处理认证和授权。这些在某些方面类似于常规的Unix/Linux用户和组。安装过程中创建了一个名为postgres的用户账户，它与默认的Postgres角色相关联。

有几种方法可以使用这个账户访问Postgres。一种是切换到服务器上的postgres账户：

su postgres

然后你可以通过运行以下命令访问Postgres提示符：

psql

这将登录到PostgreSQL提示符，从这里你可以立即与数据库管理系统交互。

要退出PostgreSQL提示符，运行以下命令：

\q

这将带你回到postgres Linux命令提示符。要返回到你的常规系统用户，运行exit命令：

exit

另一种连接到Postgres提示符的方法是直接使用sudo运行psql命令：

sudo -u postgres psql

这将直接登录到Postgres，无需中间的bash shell。

同样，你可以通过运行以下命令退出交互式Postgres会话：

\q

查看版本

postgres@zabbix-proxy:~$ psql --version
psql (PostgreSQL) 14.18 (Ubuntu 14.18-1.pgdg22.04+1)

参考链接：https://www.postgresql.org/download/linux/ubuntu/

方法二：源码编译安装（适用于自定义或特殊需求）-version14.15

源码安装适合需要自定义配置、调试源码或安装非官方补丁的高级用户。

步骤：

# 1. 安装依赖工具和库
sudo apt update
sudo apt install build-essential libreadline-dev zlib1g-dev flex bison  gcc make pkg-config liblz4-dev zlib1g-dev libssl-dev

# 2. 下载源码（指定版本）
wget https://ftp.postgresql.org/pub/source/v14.15/postgresql-14.15.tar.gz

# 3. 解压源码包
tar -xvzf postgresql-14.15.tar.gz
cd postgresql-14.15

# 4. 配置编译参数
./configure --prefix=/app/postgresql/

# 5. 编译并安装
make -j$(nproc)
sudo make install

创建运行用户

sudo useradd -m -s /bin/bash postgres
sudo passwd postgres

用户添加sudo权限

cat <<EOF | sudo tee /etc/sudoers.d/postgres > /dev/null 
postgres    ALL=(ALL)       ALL
EOF

创建存储目录

sudo mkdir -p /data/{pg_data,pg_wal}
sudo chown -R postgres. /data/{pg_data,pg_wal}
sudo chmod 700 /data/{pg_data,pg_wal}

设置环境变量

sudo su - postgres

cat <<'EOF' | tee -a ~/.bashrc >>/dev/null
# Postgresql Service Environment variable
export PGHOME=/app/postgresql
export PGDATA=/data/pg_data
export LD_LIBRARY=$PGHOME/lib
export PATH=$PGHOME/bin:$PATH
EOF

source ~/.bashrc

初始化数据库

sudo su - postgres

initdb --pgdata=/data/pg_data --waldir=/data/pg_wal --encoding=UTF8 --locale=en_US.utf8 --auth=scram-sha-256 --username=postgres --pwprompt

postgres@sql:~$ initdb --pgdata=/data/pg_data --waldir=/data/pg_wal --encoding=UTF8 --locale=en_US.utf8 --auth=scram-sha-256 --username=postgres --pwprompt
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

Enter new superuser password:#输入密码
Enter it again:#输入密码

fixing permissions on existing directory /data/pg_data ... ok
fixing permissions on existing directory /data/pg_wal ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctl -D /data/pg_data -l logfile start

初始化报错 initdb: error: invalid locale name "en_US.utf8"

Ubuntu 解决办法：安装 language-pack-en 包，查看所有 locales 方法 locale --all-locales

初始化报错 initdb: error: invalid locale name "zh_CN.utf8"
Ubuntu 解决办法：安装 language-pack-kde-zh-hans 包，查看所有 locales 方法 locale --all-locales

添加postgresql配置

cat <<'EOF' | tee -a $PGDATA/postgresql.conf > /dev/null
# 服务监听相关配置
listen_addresses = '*' # 监听地址
port = 5432 # 监听端口

# 设置时区
timezone = 'Asia/Shanghai'
log_timezone = 'Asia/Shanghai'

# WAL相关配置
wal_level = replica # 该参数决定了系统生成和记录 WAL 日志的程度 logical>replica>minimal
full_page_writes = on # 控制是否在 WAL（Write-Ahead Logging）记录中包括整个页面的副本，而不仅仅是被修改的部分【优点】可以提高崩溃恢复的可靠性【缺点】WAL日志的空间使用高
wal_log_hints = on # 控制是否在 WAL 中记录对表的索引的更改操作的提示信息【优点】有助于在崩溃时加速恢复过程【缺点】增加 WAL 记录的生成量

# 服务日志配置参数
log_directory = 'logs' # 日志目录 $PGDATA/log_directory
logging_collector = on # 开启日志采集
log_destination = stderr # 日志输出格式
log_filename = 'postgresql-%Y-%m-%d.log' # 日志文件名称
log_truncate_on_rotation = on # 可重用同名日志文件
log_rotation_age = 1d # 多长时间轮换文件记录日志
log_rotation_size = 0 # 日志轮换大小的参数，当设置为0时不轮换

# 记录慢SQL日志设置 
log_statement = all # 需设置跟踪所有语句，否则只能跟踪出错信息，设置跟踪的语句类型，有4种类型：none(默认), ddl, mod, all。跟踪所有语句时可设置为 "all"。
log_min_duration_statement = 5000 # milliseconds,记录执行5秒及以上的语句，跟踪慢查询语句，单位为毫秒。如设置 5000，表示日志将记录执行5秒以上的SQL语句

EOF

启动服务

1. 手工启动服务

pg_ctl start

2. systemd启动

cat <<'EOF' | sudo tee /usr/lib/systemd/system/postgresql.service >> /dev/null
[Unit]
Description=Postgresql Server
Documentation=https://www.postgresql.org/docs/14/index.html
Wants=network.service
After=network.service

[Service]
Type=forking
User=postgres
Environment=PGDATA=/data/pg_data
ExecStart=/app/postgresql/bin/pg_ctl -D ${PGDATA} start
ExecStop=/app/postgresql/bin/pg_ctl -D ${PGDATA} stop
ExecReload=/app/postgresql/bin/pg_ctl -D ${PGDATA} reload
Restart=on-failure

[Install]
WantedBy=multi-user.target
EOF

sudo systemctl daemon-reload
sudo systemctl start postgresql

查看日志

cd $PGDATA/logs/
tail -f postgresql-`date "+%Y-%m-%d"`.log

检查版本

postgres@sql:~$ psql --version
psql (PostgreSQL) 14.15

小结：选择哪种安装方式？

安装方式	优点	缺点	适合人群
apt 安装	快速、简单、自动配置服务	不易定制，依赖系统版本	初学者、生产环境
编译安装	灵活，可自定义路径/功能	复杂，需要手动配置环境变量等	高级用户、开发调试

三：🔧 PostgreSQL配置

1、核心配置文件汇总

配置文件	主要功能	默认路径	备注
`postgresql.conf`	主配置文件，控制服务器行为	`[数据目录]/`	最重要的配置文件
`pg_hba.conf`	客户端连接认证配置	`[数据目录]/`	控制访问权限
`pg_ident.conf`	用户身份映射配置	`[数据目录]/`	用于OS用户到DB用户的映射
`postgresql.auto.conf`	系统自动管理配置	`[数据目录]/`	优先级最高

2、配置文件详解

2.1 postgresql.conf - 主配置文件

2.1.1 连接与认证配置

# 监听设置
listen_addresses = '*'          # 监听所有IP地址，也可指定具体IP
port = 5432                     # 默认监听端口

# 连接限制
max_connections = 100           # 最大并发连接数
authentication_timeout = 1min   # 认证超时时间

# 密码加密
password_encryption = scram-sha-256  # 推荐使用强加密

2.1.2 资源与内存配置

# 内存设置
shared_buffers = 128MB          # 共享缓冲区大小，建议为内存的25%
work_mem = 4MB                  # 每个操作使用的内存
maintenance_work_mem = 64MB     # 维护操作使用的内存
effective_cache_size = 1GB      # 系统估计缓存大小

2.1.3 预写式日志(WAL)配置

wal_level = replica             # WAL级别：minimal, replica, logical

2.1.4 日志记录配置

# 日志基础设置
logging_collector = on        # 日志开启
log_destination = 'stderr'    
log_directory = 'pg_log'      # 日志路径
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'  #日志文件名

# 慢查询日志
log_min_duration_statement = 1000  # 记录执行超过1秒的查询

2.2 pg_hba.conf - 客户端认证配置

2.2.1 文件格式

# 类型  数据库  用户  地址      认证方法
TYPE  DATABASE  USER  ADDRESS   METHOD

-----------
示例：
# local         DATABASE  USER  METHOD  [OPTIONS]
# host          DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostssl       DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostnossl     DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostgssenc    DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostnogssenc  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]

2.2.2 连接类型

local：Unix域套接字连接
host：TCP/IP连接（SSL和非SSL）
hostssl：仅SSL加密的TCP/IP连接
hostnossl：仅非SSL的TCP/IP连接

2.2.3 认证方法

trust：无条件允许连接（⚠️生产环境慎用）
scram-sha-256：SCRAM-SHA-256密码认证（推荐）
md5：MD5加密密码认证
ident：通过操作系统用户身份认证
peer：使用操作系统用户名进行peer认证

2.2.4 配置示例

# 允许本地所有用户信任连接
local   all             all                                     trust

# 允许本地用户密码认证
local   all             all                                     scram-sha-256

# 允许任意地址所有用户对所有数据库，密码认证 -》远程连接
host    all             all             0.0.0.0                 md5

# 允许网络段用户连接
host    all             all             192.168.1.0/24          scram-sha-256

# 允许特定用户从特定IP连接特定数据库
host    mydb            myuser          192.168.1.100/32        scram-sha-256

# SSL连接要求
hostssl all             all             0.0.0.0/0               scram-sha-256

2.3 pg_ident.conf - 用户映射配置

2.3.1 文件格式

# 映射名  系统用户名  PostgreSQL用户名
MAPNAME  SYSTEM-USERNAME  PG-USERNAME

2.3.2 配置示例

# 将操作系统用户映射到数据库用户
mymap   john   john_db
mymap   jane   jane_db

2.4 postgresql.auto.conf

由ALTER SYSTEM命令自动维护
优先级高于postgresql.conf
不应手动编辑此文件

3、配置管理操作

3.1 查找配置文件位置

-- 查看数据目录
SELECT setting FROM pg_settings WHERE name = 'data_directory';

-- 查看配置文件路径
SELECT name, setting FROM pg_settings WHERE name LIKE '%config_file%';

3.2 配置生效方法

3.2.1 重新加载配置（无需重启）

# 方法1：使用pg_ctl
pg_ctl reload -D $PGDATA

# 方法2：使用SQL命令
SELECT pg_reload_conf();

# 方法3：使用系统服务
sudo systemctl reload postgresql

3.2.2 重启服务（完全重启）

# 方法1：使用pg_ctl
pg_ctl restart -D $PGDATA

# 方法2：使用系统服务
sudo systemctl restart postgresql

3.3 配置查看与修改

3.3.1 查看当前配置

-- 查看所有参数
SELECT name, setting, unit, context FROM pg_settings;

-- 查看特定参数
SELECT name, setting FROM pg_settings WHERE name = 'shared_buffers';

3.3.2 修改配置

-- 方法1：使用ALTER SYSTEM（写入postgresql.auto.conf）
ALTER SYSTEM SET shared_buffers = '256MB';

-- 方法2：直接编辑postgresql.conf文件
-- 然后重新加载或重启服务

4、安全最佳实践

4.1 认证安全

避免使用trust认证：在生产环境中禁止使用
使用强密码加密：优先选择scram-sha-256
限制网络访问：仅允许必要的IP段连接
使用SSL加密：对远程连接启用SSL

4.2 文件权限

# 配置文件权限设置
chmod 600 $PGDATA/postgresql.conf
chmod 600 $PGDATA/pg_hba.conf
chmod 600 $PGDATA/pg_ident.conf

5、故障排查技巧

5.1 连接问题排查

检查pg_hba.conf中的认证规则
确认listen_addresses设置正确
验证端口是否开放和防火墙设置

5.2 配置验证

# 检查配置语法
postgres --check-config

# 查看生效的配置
psql -c "SELECT name, setting, source FROM pg_settings WHERE source != 'default';"

6、重要提醒

修改前备份：修改配置文件前务必进行备份
测试环境验证：重要变更先在测试环境验证
变更记录：记录所有配置变更及原因
监控影响：配置变更后监控系统性能

posted @ 2025-07-06 16:31 kyle_7Qc 阅读(181) 评论(0) 收藏举报

刷新页面返回顶部

kyle-7Qc

📘 PostgreSQL 入门 （介绍 -> 安装 -> 配置）