跳至正文

PostgreSQL

  • 由

PostgreSQL是一个开源的对象-关系数据库系统(ORDBMS)，在灵活的BSD许可证下发行。

PostgreSQL事实上不被任何一家公司所掌控，PostgreSQL全球开发小组，是一个松散的组织，BSD许可协议是自由软件中使用最广泛的许可协议之一。 PostgreSQL在可靠性、稳定性上已经十分强大，并且被应用的十分广泛。

官方网站：https://www.postgresql.org/

安装环境和准备

一台CentOS 7服务器如下:

192.168.1.153    pgsql

下载PostgreSQL的源码压缩包:

wget https://ftp.postgresql.org/pub/source/v17.6/postgresql-17.6.tar.gz

解压缩源码:

tar -zxvf postgresql-17.6.tar.gz

安装编译工具及相关依赖:

yum install -y make gcc bison gcc-c++ readline readline-devel zlib zlib-devel systemd-devel libicu-devel flex bison

在这台服务器上创建postgres用户:

useradd -m postgres

创建postgresql相关目录:

mkdir /opt/postgres/data
mkdir /opt/postgres/init
chown -R postgres:postgres /opt/postgres

将使用/opt/postgres/data作为数据库的数据目录。

编译安装

进入到源码解压缩目录:

cd postgresql-17.6

configure执行配置准备构建环境:

export LD_LIBRARY_PATH=/usr/lib

./configure --with-pgport=5432 \
  --prefix=/usr/local/pgsql \
  --with-systemd \
  --with-segsize=16 \
  --with-blocksize=8 \
  --with-wal-blocksize=8 \
  --datadir=/opt/postgres/init
  • LD_LIBRARY_PATH是一个用于指定动态链接库（shared library）的搜索路径的环境变量。
  • --with-segsize=SEGSIZE设置段大小（以GB为单位）。大型表分为多个操作系统文件，每个文件的大小等于段大小。建议（尽管不是绝对必需）此值为2的幂。这样可以避免许多平台上存在的文件大小限制问题。默认段大小1GB，在所有受支持的平台上都是安全的。如果您的操作系统具有“大文件”支持（现在大多数都支持），则可以使用更大的段大小。这有助于减少处理非常大的表时消耗的文件描述符数。请注意，更改此值会破坏磁盘上的数据库兼容性，这意味着您无法用于 pg_upgrade 升级到具有不同段大小的内部版本。
  • --with-blocksize=BLOCKSIZE设置块大小（以KB为单位）。这是表中的存储和I/O单元。默认值8KB适用于大多数情况;但其他值在特殊情况下可能有用。该值必须是介于1和32（千字节）之间的2次幂。请注意，更改此值会破坏磁盘上的数据库兼容性，这意味着您无法用于pg_upgrade升级到具有不同块大小的生内部版本。
  • --with-wal-blocksize=WALBLOCKSIZE设置WAL块大小（以KB为单位）。这是WAL日志中的存储和 I/O 单元。默认值8KB适用于大多数情况;但其他值在特殊情况下可能有用。该值必须是介于1和64（千字节）之间的2次方。请注意，更改此值会破坏磁盘数据库兼容性，这意味着您无法用于 pg_upgrade 升级到具有不同WAL块大小的版本。

开始编译安装:

make
make install

初始化数据库

执行下面的命令对数据库进行初始化，数据库的初始化需要切换到postgres用户:

su - postgres
/usr/local/pgsql/bin/initdb -D /opt/postgres/data

切回到root用户创建systemd配置文件/etc/systemd/system/postgresql.service:

[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)
After=network-online.target
Wants=network-online.target

[Service]
Type=notify
User=postgres
ExecStart=/usr/local/pgsql/bin/postgres -D /opt/postgres/data
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT

[Install]
WantedBy=multi-user.target

配置开机启动并启动PostgreSQL:

systemctl enable postgresql --now

默认使用postgres用户可以本机登录到数据库:

#建立软链接方便直接调用
ln -s /usr/local/pgsql/bin/psql /usr/bin/psql

#启动pgsql
systemctl start postgresql --now

psql -p5432 -h127.0.0.1 -Upostgres
psql (16.1)
Type "help" for help.

postgres=#

初始化数据库后，会有名为postgres的数据库，来存储数据库的基础信息，例如用户信息等等，postgres数据库中会初始化一名超级用户postgres可以修改该用户的密码:

postgres=#  \password

语法和用法

连接数据库

# 登录 PostgreSQL
psql -U 用户名 -d 数据库名 -h 主机 -p 端口

默认端口 5432。
进入 psql 后，常用命令：

\l      -- 列出所有数据库
\c db   -- 切换到数据库 db
\dt     -- 列出所有表
\d 表名 -- 查看表结构
\q      -- 退出

数据库操作

-- 创建数据库
CREATE DATABASE mydb;

-- 删除数据库
DROP DATABASE mydb;

-- 修改数据库所有者
ALTER DATABASE mydb OWNER TO newuser;

用户与权限

-- 创建用户
CREATE USER myuser WITH PASSWORD 'mypassword';

-- 创建超级用户
CREATE ROLE admin SUPERUSER LOGIN PASSWORD 'admin123';

-- 给用户赋予权限
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

-- 撤销权限
REVOKE ALL PRIVILEGES ON DATABASE mydb FROM myuser;

表操作

-- 创建表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 删除表
DROP TABLE users;

-- 修改表（加列）
ALTER TABLE users ADD COLUMN email VARCHAR(100);

-- 修改表（改列名）
ALTER TABLE users RENAME COLUMN name TO username;

-- 修改表（删列）
ALTER TABLE users DROP COLUMN age;

数据操作 (CRUD)

插入数据

INSERT INTO users (name, age, email) 
VALUES ('Alice', 25, 'alice@test.com');

查询数据

SELECT * FROM users;

-- 条件查询
SELECT name, age FROM users WHERE age > 20;

-- 排序
SELECT * FROM users ORDER BY age DESC;

-- 分页
SELECT * FROM users LIMIT 10 OFFSET 20;

-- 聚合
SELECT COUNT(*), AVG(age) FROM users;

更新数据

UPDATE users SET age = 30 WHERE name = 'Alice';

删除数据

DELETE FROM users WHERE id = 1;

索引

-- 创建索引
CREATE INDEX idx_users_name ON users(name);

-- 删除索引
DROP INDEX idx_users_name;

视图

-- 创建视图
CREATE VIEW adult_users AS
SELECT id, name, age FROM users WHERE age >= 18;

-- 查询视图
SELECT * FROM adult_users;

-- 删除视图
DROP VIEW adult_users;

事务

BEGIN;

UPDATE users SET age = age + 1 WHERE id = 1;
DELETE FROM users WHERE id = 2;

COMMIT;   -- 提交
ROLLBACK; -- 回滚

常用函数

-- 字符串拼接
SELECT 'Hello ' || 'World';

-- 日期函数
SELECT NOW();
SELECT CURRENT_DATE;
SELECT EXTRACT(YEAR FROM NOW());

-- 条件判断
SELECT CASE WHEN age >= 18 THEN 'adult' ELSE 'child' END FROM users;

导入导出

# 导出数据库
pg_dump -U user -d mydb > mydb.sql

# 导入数据库
psql -U user -d mydb < mydb.sql

PostgreSQL vs MySQL 对比

维度PostgreSQLMySQL
定位功能全面，强调 标准化 + 扩展性，被称为“世界上最先进的开源数据库”简单高效，强调 易用性 + 高性能，互联网项目常用
SQL 标准支持接近 100% SQL 标准，支持复杂查询、递归、CTE、窗口函数部分支持，早期不完全兼容标准（现在 InnoDB 好很多）
存储引擎内置 一个统一引擎（类似 Oracle），功能强大插件式，常用 InnoDB（事务安全）、MyISAM（性能高）
事务支持完整支持 ACID + MVCC（多版本并发控制），无读锁InnoDB 引擎支持 ACID 和 MVCC，MyISAM 不支持事务
并发性能MVCC 实现优秀，读写并发能力强，适合高并发InnoDB 也支持 MVCC，但在高并发复杂查询时性能不如 PG
数据类型丰富：JSON/JSONB、数组、UUID、XML、地理数据（PostGIS）相对有限，JSON 有支持但功能较弱
扩展性可自定义函数、数据类型、索引方法，扩展能力极强扩展性较弱，基本依赖内置功能
查询优化优化器更智能，适合复杂 SQL、数据仓库场景优化器较简单，更适合 OLTP、读多写少的场景
全文搜索内置 tsvector 支持强大的全文检索MySQL 也有全文索引（InnoDB/MyISAM），但功能弱
主从复制原生流复制 + 逻辑复制（PG10+），灵活主从复制成熟（半同步、组复制），常用于互联网高可用架构
社区生态偏学术 + 企业，功能前沿（PostGIS、TimescaleDB 等扩展）偏互联网 + 工程，生态广，周边工具丰富
性能特点复杂查询性能更优（OLAP，BI，大数据分析）简单查询性能高（OLTP，电商网站、后台系统）
学习成本较高，功能多、SQL 更复杂较低，上手快，常见资料多
典型应用金融、电信、GIS、大数据、企业级应用Web 应用、电商、游戏、SaaS 等互联网场景

📌 总结

  • 选 PostgreSQL：
    如果你需要 复杂查询、事务完整性、数据分析、地理信息处理、可扩展性，PG 更适合。
    （例如：金融系统、BI 分析平台、GIS 应用）
  • 选 MySQL：
    如果你追求 高并发读写、运维成本低、生态广、上手快，MySQL 更适合。
    （例如：网站后台、电商平台、云原生服务）

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注