PostgreSQL关系型数据库介绍与部署
PostgreSQL 是当前功能最强大的开源的关系型数据库系统,支持跨平台的多种操作系统, 基于C语言开发。通常简称为PG或PGSQL。PostgreSQL是100%社区驱动的开源项自，由全球范围内千人以上的社区责献者共同维护。PostgreSQL提供了一个完整功能的瓶本，而不像MySQL那样提供多个不同的版本，如社区版、商业版及企业版。中文社区：PostgreSQL中文社区:: 世界上功能最强大
文章共6,947字 · 阅读需要大约24分钟
使用背景
在过去的几年中，PostgreSQL的使用量逐渐增加，而Oracle和MySQL的使用量则有所下降。这主要是由于以下几个原因：开源和免费、功能丰富、可扩展性强、安全性高、跨平台支持好、社区活跃、成熟稳定。这些因素使得PostgreSQL成为了许多开发者和组织的首选数据库解决方案，从而推动了它的流行。下面介绍一下这款关系型数据库PostgreSQL。
目录
一、PostgreSQL 介绍
1. 概念
PostgreSQL 是当前功能最强大的开源的关系型数据库系统,支持跨平台的多种操作系统, 基于C语言开发。通常简称为PG或PGSQL。 PostgreSQL是100%社区驱动的开源项自，由全球范围内千人以上的社区责献者共同维护。PostgreSQL提供了一个完整功能的瓶本，而不像MySQL那样提供多个不同的版本，如社区版、商业版及企业版。
官网：PostgreSQL: The world's most advanced open source database
中文社区： PostgreSQL中文社区:: 世界上功能最强大的开源数据库...
中文手册： PostgreSQL 14.1 手册
2. 特点
① 开源和免费：是一款完全开源的数据库系统，用户可以免费使用、修改和分发它。
② 功能丰富：具有许多高级功能，包括复杂查询、事务支持、触发器、存储过程、视图、全文搜索、地理空间数据处理等。
③ 可扩展性：具有良好的可扩展性，支持水平扩展和垂直扩展。通过使用分区表、复制、流复制等功能，可以轻松地扩展数据库以满足不断增长的需求。
④ 安全性：注重数据安全性，提供了各种安全功能，如访问控制、SSL支持、加密存储、审计功能等，可以帮助保护数据免受恶意攻击和数据泄露。
⑤ 跨平台支持：可以在各种操作系统上运行，包括Linux、Unix、Windows和macOS等，这使得它具有很好的跨平台兼容性。
⑥ 社区支持：拥有一个庞大的全球开发者社区，提供了丰富的文档、教程和支持资源。用户可以通过邮件列表、论坛等渠道获得帮助和交流经验。
⑦ 成熟稳定：已经存在了30多年，经过了多次的版本迭代和改进，已经非常成熟和稳定。许多大型企业和组织都在使用PostgreSQL来处理他们的关键业务数据。
3. 作用
PostgreSQL是一款功能丰富、可靠稳定、安全性高的开源数据库管理系统，适用于各种规模的应用程序和企业，可以满足各种复杂的数据需求。
① 数据存储和管理：PostgreSQL可以存储和管理大量的数据，包括结构化数据、半结构化数据和非结构化数据。
② 数据查询和分析：PostgreSQL提供了强大的查询和分析功能，包括复杂查询、聚合函数、窗口函数、分析函数等。
③ 数据安全：PostgreSQL提供了各种安全功能，如访问控制、SSL支持、加密存储、审计功能等，可以帮助保护数据免受恶意攻击和数据泄露。
④ 数据一致性和完整性：PostgreSQL支持事务，可以确保数据的一致性和完整性。
⑤ 数据复制和高可用性：PostgreSQL支持数据复制和流复制，可以提供数据的备份和高可用性。
⑥ 数据分区和分布式处理：PostgreSQL支持分区表，可以将数据分布到多个物理存储设备上，从而提高查询性能。
⑦ 地理信息系统（GIS）：PostgreSQL具有强大的地理信息系统支持，包括地理空间数据类型、索引和查询功能。
⑧ 全文搜索：PostgreSQL提供了全文搜索功能，包括全文索引和全文搜索查询。
4. 使用场景
① Web应用程序：PostgreSQL是许多Web应用程序的首选数据库解决方案，包括许多大型的社交网络、电子商务网站、新闻门户等。它的高级功能和可扩展性使得它能够处理大量的数据和复杂的查询需求。
② 地理信息系统（GIS）：PostgreSQL具有强大的地理信息系统支持，包括地理空间数据类型、索引和查询功能。因此，它被许多GIS应用程序用作后端数据库。
③ 全文搜索：PostgreSQL提供了全文搜索功能，包括全文索引和全文搜索查询。这使得它成为许多需要全文搜索功能的应用程序的首选数据库解决方案。
④ 大数据处理：PostgreSQL的可扩展性和高级功能使得它能够处理大量的数据，包括大数据处理、数据仓库等。
⑤ 金融和电信行业：PostgreSQL被许多金融和电信公司用于处理他们的关键业务数据，包括交易数据、客户数据等。它的高级功能和数据安全性使得它成为这些行业的首选数据库解决方案。
⑥ 科学研究：PostgreSQL被许多科学研究机构用于处理和分析科学数据，包括天文学、生物学、气象学等。它的高级功能和可扩展性使得它能够满足这些领域的复杂数据需求。
5. PostgreSQL 整体架构
- 后台进程：这些进程负责处理客户端的请求，包括查询处理、事务管理、并发控制等。其中，最重要的后台进程是 postmaster，它负责启动和管理其他后台进程。
- 共享内存和后台进程：这两者之间的协作是 PostgreSQL 的关键。共享内存用于存储数据库的缓冲区，以及用于进程间通信的数据结构。后台进程通过共享内存进行协作，以便处理客户端的请求。
- 客户端接口：PostgreSQL 支持多种客户端接口，包括命令行工具、图形用户界面、以及多种编程语言的 API。这些接口允许用户连接到数据库，并发送 SQL 查询。
- 数据存储：PostgreSQL 使用表来存储数据，每个表都有一个或多个列，每个列都有一个数据类型。表可以组织成模式（schema），模式可以包含多个表。
- 查询处理：当客户端发送 SQL 查询时，postmaster 进程会将查询发送给一个或多个后台进程。这些后台进程会解析查询，生成查询计划，执行查询，然后返回结果。
- 事务管理：PostgreSQL 支持 ACID（原子性、一致性、隔离性、持久性）事务，这是通过在后台进程之间共享内存来实现的。
- 并发控制：PostgreSQL 使用多版本并发控制（MVCC）来处理并发事务。MVCC 允许多个事务同时访问同一数据，而不会相互干扰。
- 错误处理：当出现错误时，PostgreSQL 会生成一个错误消息，然后将其发送给客户端。客户端可以选择忽略错误，或者根据错误消息来采取适当的行动。
6. PostgreSQL 与 MySQL 对比
6.1 PG 相对于 MySQL 的优势
- 在SQL的标准实现上要比MySQL完善，而且功能实现比较严谨。
- 对表连接支持较完整，优化器的功能较完整，支持的索引类型很多，复杂查询能力较强。
- PG主表采用堆表存放，MySQL采用索引组织表，能够支持比MySQL更大的数据量。
- PG的主备复制属于物理复制，相对于MySQL基于biniog的逻辑复制，数据的一致性更加可靠，复制性能更高，对主机性能的影响也更小。
- PostgreSQL支持ISON和其他NOSQL功能，如本机XML支持和使用HSTORE的键值对。它还支持索引SON数据以加快访问速度，特别是10版本JSONB更是强大。
- PostgreSQL完全免费，而且是BSD协议，如果你把PostgreSQL改一改，然后再拿去卖钱，也没有人管你，这一点很重要，这表明了PostgresQl数据库不会被其它公司控制。相反，MySQL现在主要是被Oracle公司控制。
6.2 MySQL 相对于 PG 的优势
- innodb的基于回滚段实现的MVCC机制，相对PG新老数据一起存放的基于XID的MVCC机制，是占优的。新老数据一起存放，需要定时触 发VACUUM，会带来多余的IO和数据库对象加锁开销，引起数据库整体的并发能力下降。而且VACUUM清理不及时，还可能会引发数据膨胀。
- MySQL采用索引组织表，这种存储方式非常适合基于主键匹配的查询、制改操作，但是对表结构设计存在约束。
- MySQL的优化器较简单，系统表、运算符、数据类型的实现都很精简，非常适合简单的查询操作。
- MySQL相对于PG在国内的流行度更高，PG在国内显得就有些落了。
- MySQL的存储引擎插件化机制，使得它的应用场景更加广泛，比如除了innodb适合事务处理场景外，mvisam适合静态数据的查询场暴。
总结
从应用场景来说，PG更加适合严格的企业应用场景(比如金融、电信、ERP、CRM)，但不仅仅限制于此，PostareSQl的ison，isonb，hstore等数据格式，特别适用于一些大数据格式的分析；而MySQL更加适合业务逻辑相对简单、数据可靠性要求较低的互联网场景(比如google、acebook、albaba)，当然现在MySQL的在innodb引擎的大力发展，功能表现良好。
二、Linux 上部署 PostgreSQL
1. Yum 安装 PG
使用官方源： PostgreSQL: Downloads
1.1 选择操作系统及版本
1.2 选择需要安装的软件版本及环境
1.3 安装并运行
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# 使用yum包管理器安装PostgreSQL的官方YUM存储库。这个存储库包含了PostgreSQL的二进制安装包，可以让你使用yum来安装和更新PostgreSQL。
sudo yum install -y postgresql14-server
sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
# 使用PostgreSQL 14的postgresql-14-setup脚本初始化一个新的PostgreSQL 14数据库。
sudo systemctl enable postgresql-14
sudo systemctl start postgresql-141.4 登录数据库
方法一：在默认配置下，本机访问 PostgreSQL
[root@localhost ~]# su - postgres # 切换 postgres 用户
上一次登录：日 3月 24 12:49:21 CST 2024pts/0 上
-bash-4.2$ psql # 登录 PostgreSQL 控制台
psql (14.11)
输入 "help" 来获取帮助信息.
postgres=# # 此时说明用 postgres 身份登录数据库，且不需要密码
postgres=# \password postgres # 给 postgres 用户设置密码
Enter new password for user "postgres":
再输入一遍：
2. 编译安装
2.1 安装依赖包
[root@localhost ~]# systemctl stop firewalld.service
[root@localhost ~]# setenforce 0
[root@localhost ~]# yum install -y gcc.x86_64 make readline-devel zlib-devel2.2 源码编译安装
[root@localhost ~]# cd /opt/
[root@localhost opt]# wget https://ftp.postgresql.org/pub/source/v14.1/postgresql-14.1.tar.gz
# 下载 PostgreSQL 14.1 的源代码压缩包
[root@localhost opt]# ls
postgresql-16.2.tar.gz rh
[root@localhost opt]# tar xf postgresql-14.1.tar.gz
[root@localhost opt]# cd postgresql-14.1/
[root@localhost postgresql-14.1]# ./configure --prefix=/apps/pgsql --with-pgport=5432
# 默认安装在/usr/local/pgsql，指定了PostgreSQL的安装路径/apps/pgsql，指定了 PostgreSQL 的默认端口号
[root@localhost postgresql-14.1]# make -j 2 && make install2.3 创建数据库用户和组
PostgreSQL 默认不支持 以 root 身份启动服务，虽然也可修改源码实现 root 启动，但基于安全考虑不建议，因此必须创建一个用于启动 PostgrepSQL 的普通用户。
[root@localhost postgresql-14.1]# useradd -s /bin/bash -m -d /home/postgres postgres
# -m 在/home目录下为新用户创建一个家目录
# -d /home/postgres：这个选项指定新用户的家目录为/home/postgres
# 创建数据库用户和组，注意此用户需要可以交互登录
[root@localhost postgresql-14.1]# echo "123123" | passwd --stdin postgres
更改用户 postgres 的密码 。
passwd：所有的身份验证令牌已经成功更新。
# 修改postgres密码2.4 创建数据目录并授权
[root@localhost postgresql-14.1]# mkdir /pgsql/data -pv
[root@localhost postgresql-14.1]# chown postgres.postgres /pgsql -R
# 将 /pgsql 目录及其所有子目录和文件的所有者和所属组都改为 postgres 用户和 postgres 组，-R递归2.5 设置环境变量
设置了一些环境变量以便于 PostgreSQL 的安装和使用
[root@localhost ~]# vim /etc/profile.d/pgsql.sh # 设置 PostgreSQL 的环境变量
export PGHOME=/apps/pgsql # 设置了 PostgreSQL 的安装目录为 /apps/pgsql
export PATH=$PGHOME/bin/:$PATH # 将 PostgreSQL 的 bin 目录添加到系统的 PATH 环境变量中，以便于在命令行中直接运行 PostgreSQL 的命令
export PGDATA=/pgsql/data # 设置了 PostgreSQL 的数据目录为 /pgsql/data
export PGUSER=postgres # 设置了 PostgreSQL 的默认用户为 postgres
export MANPATH=/apps/pgsql/share/man:$MANPATH # 将 PostgreSQL 的 man 目录添加到系统的 MANPATH 环境变量中，以便于在命令行中查看 PostgreSQL 的帮助文档2.6 初始化数据库
[root@localhost ~]# su - postgres
[postgres@localhost ~]$ initdb -D /pgsql/data # -D 指定数据目录
# 如果没有指定选项 -D <datadir> ,按环境变量$PGDATA指定的路径进行初始化
生产建议初始化方式：
initdb -A md5 -D $PGDATA -E utf8 --locale=C -U postgres -W
# -A #指定local connections默认的身份验证方法
# -D #指定数据目录
# -E #指定字符集
# --locale=C #指定语言环境
# -U #指定数据库superuser用户名
# -W #指定数据库superuser的用户密码
2.7 启动和关闭服务
[root@localhost ~]# su - postgres
[postgres@localhost ~]$ pg_ctl start # 开启
[postgres@localhost ~]$ pg_ctl stop # 停止
[postgres@localhost ~]$ pg_ctl -l logfile start # 将日志记录到文件中，不加就打印在屏幕上
[postgres@localhost ~]$ pg_ctl restart # 重启
启动:
pg_ctl -D /pgsql/data -l logfile start
# pg_ctl：PostgreSQL 控制命令，用于控制 PostgreSQL 服务器的启动、停止、重启等操作。
# -D /pgsql/data：指定 PostgreSQL 数据库的数据目录。在这个例子中，数据目录是 /pgsql/data。
# -l logfile：指定日志文件的路径。在这个例子中，日志文件的路径是 logfile。
# start：命令，用于启动 PostgreSQL 服务器。
postgres -D /pgsql/data &
# postgres：PostgreSQL 服务器的可执行文件。
# -D /pgsql/data：指定 PostgreSQL 数据库的数据目录。在这个例子中，数据目录是 /pgsql/data。
# &：在 Unix 系统中，& 符号用于将命令放入后台运行。
/opt/postgresql-14.1/contrib/start-scripts/linux
# 源码目录中内置PostgreSQL的启动脚本
停止:
pg_ctl stop -D $PGDATA [-m SHUTDOWN-MODE]
其中-m是指定数据库的停止方法，有以下三种:
# smart:等所有的连接中止后，关闭数据库。如果客户端连接不终止，则无法关闭数据库。
# fast :快速关闭数据库，断开客户端的连接，让已有的事务回滚，然后正常关闭数据库。相当于Oracle数据库关闭时的immediate模式。此为默认值,建议使用
# immediate:立即关闭数据库，相当于数据库进程立即停止，直接退出，下次启动数据库需要进行恢复。相当于Oracle数据库关闭时的 abort模式
示例：
pg_ctl stop -D /pgsql/data/ -ms # smart关闭
pg_ctl stop -D /pgsql/data/ -mf # fast关闭,推荐使用,也是默认模式
pg_ctl stop -D /pgsql/data/ -mi # immediate 相当于kill -9
或者发送信号,直接向数据库主进程发送的signal 信号有以下三种。
# SIGTERM:发送此信号为Smart Shutdown关机模式。
# SIGINT:发送此信号为Fast Shutdown关机模式。
# SIGQUIT:发送此信号为Immediate Shutdown关机模式。
pg_ctl restart -mf # 重启
2.8 创建 systemd service 文件
[root@localhost ~]# vim /lib/systemd/system/postgresql.service
[Unit] # 定义服务的基本属性
Description=PostgreSQL database server # 服务的描述，它会在系统日志中显示
After=network.target # 告诉 systemd 在网络服务启动后再启动 PostgreSQL
[Service] # 服务的主要部分，定义了服务的运行方式
User=postgres # 用户
Group=postgres # 用户组
ExecStart=/apps/pgsql/bin/postmaster -D /pgsql/data # 这是服务的启动命令。它告诉 systemd 在 /apps/pgsql/bin/postmaster 这个可执行文件的基础上启动 PostgreSQL，同时使用 /pgsql/data 作为数据目录
ExecReload=/bin/kill -HUP # 服务的重载命令。告诉 systemd 在服务运行时发送 SIGHUP 信号给服务的主进程，以便重新加载配置
[Install] # systemd 单元的结束标记，用于定义服务的安装属性
WantedBy=multi-user.target # 告诉 systemd 在多用户模式下（即正常的系统运行模式）启动这个服务
[root@localhost ~]# systemctl start postgresql.service
[root@localhost ~]# systemctl status postgresql
● postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled; vendor preset: disabled)
Active: active (running) since 六 2024-04-06 12:25:59 CST; 1s ago
Main PID: 47331 (postmaster)
2.9 登录数据库
[root@localhost ~]# su - postgres # 切换 postgres 用户
上一次登录：六 4月 6 12:25:09 CST 2024pts/2 上
[postgres@localhost ~]$ psql # 登录 PostgreSQL 控制台，可以输入该数据库支持的一些命令
psql (14.1)
Type "help" for help.
postgres=# 三、Windows 上部署 PostgreSQL
这里使用 EnterpriseDB 来下载安装，EnterpriseDB 是全球唯一一家提供基于 PostgreSQL 企业级产品与服务的厂商。下载地址：Community DL Page (enterprisedb.com)
安装环境：Windows Server 2016
1. 安装
① 下载安装包，双击安装包开始安装
② 选择路径
③ 勾选组件
④ 输入管理员密码
⑤ 其余默认下一步，去掉勾选，点击 Finish
2. 登录
① 打开 pgAdmin 4
② 主页如下
③ 登录
④ 控制面板如下
⑤ 打开 SQL Shell(psql)
旨在为数千万中国开发者提供一个无缝且高效的云端环境，以支持学习、使用和贡献开源项目。
更多推荐
34
1- 0
已为社区贡献4条内容
所有评论(0)