PostgreSQL教程（1）PGSQL体系结构与安装部署

一、PostgreSQL 特性

PostgreSQL（简称 PGSQL）是一款功能强大的开源企业级关系型数据库，拥有众多高级特性，包括多版本并发控制（MVCC）、按时间点恢复（PITR）、异步复制、在线热备、读写分离、分布式架构、数据水平拆分等，广泛适用于金融、电信、政府、互联网等对高可用性和大规模并发有较高要求的场景，在全球范围内广受欢迎。

由于其开源免费的特性，成为众多国产数据库的基础版本，约90%的国产数据库都是在 PostgreSQL 基础上进行二次开发的，例如华为的 GaussDB、人大金仓的 KingBase、阿里的 PolarDB-Postgres 以及腾讯的 TDSQL-Postgres。

PostgreSQL 在架构设计上采用多进程模型，相较于 MySQL 的多线程机制，在处理复杂场景（如高并发、大量计算任务）时具有更优的性能表现。PostgreSQL 在数据类型方面，拥有比一般数据库更丰富的类型，包括数组、IP地址、JSON、XML、几何类型、复合类型等，能够满足多样化的数据存储需求。它也完全支持标准的 SQL 查询语言。在跨平台兼容性上也非常优秀，能够运行于 Linux、Windows、macOS 等多种操作系统之上，具备高度的可移植性。通过丰富的开发语言支持，提供了多种编程语言 API 接口，如 C、C++、Java、Python、Go、PHP 。

在管理工具方面，PostgreSQL 社区提供了大量免费高质量的图形化管理工具和辅助软件，如 pgAdmin 4 等，极大地提升了数据库的可视化管理体验。在安全性方面也具备完善的认证机制与访问控制策略，能够为企业提供稳定、可靠、安全的数据支撑环境。

二、PostgreSQL 体系结构

1、进程结构

PostgreSQL是一个多进程架构的数据库管理系统，服务启动后由多个进程来共同维护数据库的工作，以下是各进程的作用介绍：

· postgres：它是数据库实例启动后运行的第一个进程，也是所有进程的父进程。早期版本为PostMaster进程，现在为postgres的软连接。该进程用于控制整个实例的运行、内存分配、建立客户端连接等

· background writer：后台写进程（BgWriter），用于把共享内存中的脏页写到磁盘的进程，可以提高性能、释放内存块

· WAL Writer：预写日志进程，类似MySQL中的redo log

· CheckPoint：检查点进程，数据库发生宕机重启后以最近的检查点为参考，将未完成的事务前滚，再重放检查点之后的WAL日志，保证数据一致

· SysLogger：系统日志进程，当postgres.conf中的logging_collection参数为on时才会启动该进程，用于对日志进行管理

· PgArch：归档进程，当开启归档模式后才会存在该进程。该进程用于对WAL预写日志进行归档备份，避免早期WAL日志被循环写入而覆盖，通过归档日志可以实现将数据恢复到任一有记录的时间点
· AutoVacuum：自动清理进程，当数据库中的数据被执行删除操作后，会先标记为删除状态，当没有其他事务读这些数据时才会由AutoVacuum进行清理

· statistics collector：统计收集进程（PgStat），进行数据统计、收集工作，便于优化器做出正确的判断

2、数据结构

在PostgreSQL是最像Oralce的开源数据库。在 PGSQL 中，数据的存储结构分为逻辑存储结构和物理存储结构。

2.1 逻辑存储结构

数据库内部组织和管理数据的方式，包括数据库集群、数据库、表、索引、表空间等所有数据库对象。从对象组织结构上来说，PostgreSQL 的逻辑层级比 MySQL 和 Oracle 都更复杂，MySQL没有Schema的概念、Oracle的Schema基本同等于用户，一个用户对应了一个同名的Schema；而PostgreSQL中则同时引入了 Database 和 Schema 两个概念，一个 Database 下可有多个 Schema，而且一个用户可以对应多个Schema

· 表空间（tablespace）

从逻辑上将数据库分成多个存储单元，通过表空间把逻辑上相关的数据存放在一起。在初始化数据库时，会自动创建pg_default和pg_global两个表空间，pg_default是在后面创建表时默认使用的表空间；pg_default用于存放系统表

· 模式（schema）

类似于namespace的概念，用于将数据库里的对象（表、索引、函数等）逻辑上组织在一起，便于控制管理。通常建议根据业务进行对象分类，不同的业务放在不同的模式下，模式之间的数据是互相隔离的（类似于MySQL中的不同database）。一个用户可以创建多个模式，而一个模式只能属于一个用户。在创建数据库时，如果没有声明模式，默认属于PUBLIC模式下。PUBLIC模式下所有用户都有CREATE和USAGE权限，所以建议创建一个自定义模式然后让模式所有者来进行授权。

· 其他对象

块、区、段和其他对象。在PostgreSQL所有的数据对象都由一个对象标识符（OID）管理。数据库的OID存储在pg_database系统表中；表、索引等数据库对象存放在pg_class系统表中

#查询库OID
SELECT oid,datname FROM pg_database;

#查询表OID
SELECT relname, relfilenode FROM pg_class WHERE relname='mytable';

2.2 物理存储结构

PostgreSQL 存储在磁盘上的各种文件，包括数据文件、WAL 日志、配置文件、控制文件等,这些物理文件保存在指定的数据目录中（初始化时会进行指定）

· 参数文件

· 数据文件

用于存储数据，文件以OID命名。超过1G大小的文件，将自动被拆分为多个文件进行存储

#查询表OID与存放路径
select sys_relation_filepath('table_name')

· 日志文件

PostgreSQL日志分为运行日志、WAL预写日志（功能类似重做日志）、事务日志和服务器日志，其中服务器日志和运行日志都可以通过配置文件或者命令行 -l 参数开启，而WAL日志由一串24位数字命名，前8位为时间戳、中间8位为逻辑ID、最后8位为物理ID，每个WAL日志大小默认为16M（通过初始化参数--wal-segsize进行设置，无法后期修改）

· 控制文件

记录数据库运行信息，如OID、是否为打开状态、检查点信息、预写日志位置等。控制文件默认保存路径为$PGDATA/global/pg_control，该文件需要使用bin/pg_controldata工具查看

bin/pg_controldata /data

三、PostgreSQL 安装部署

PostgreSQL安装方式分为源码编码安装和YUM安装，两种安装包都可以通过官方下载地址https://www.postgresql.org/download/获取，下载的时候可以根据提示选择合适的版本 

1、YUM安装PostgreSQL

· 配置PostgreSQL官方YUM仓库并安装，过程简单，但是不能灵活定义一些参数

yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install -y postgresql14-server

· 初始化数据库，如果需要自定义一些初始化参数，可以选择启用 postgresql-14-setup脚本直接调用 initdb，参考编译安装部分

postgresql-14-setup initdb
Initializing database ... OK

· 启动数据库，PostgreSQL默认监听端口为5432

systemctl enable postgresql-14
systemctl start postgresql-14

· 安装后会自动创建一个拥有数据库登录权限的用户postgres。必须切换到postgres用户才能进行数据库连接，原因在于pg_hba.conf中还没有相应的配置项，后面文章会进行相关配置讲解

su - postgres
psql  #运行该命令进入pgsql交互界面

2、编译安装PostgreSQL

需要自行安装依赖，但是最为灵活，生产环境推荐使用

· 安装编译工具和依赖

yum install cmake gcc zlib zlib-devel gcc-c++ perl readline readline-devel ncurses-devel

· 下载PGSQL源码包

· 编译安装

安装完成后输出"PostgreSQL installation complete"代表成功

wget --no-check-certificate https://ftp.postgresql.org/pub/source/v14.6/postgresql-14.6.tar.gz
tar zxf postgresql-14.6.tar.gz
cd postgresql-14.6
./configure --prefix=/usr/local/postgersql14.6 [--without-readline] [--with-blocksize=32K]
# --without-readline 是否显示PG操作的历史命令，为了数据库安全可以取消安装
# --with-blocksize 数据库块大小，默认为8K，可以调大

make
make install

· 创建环境变量

vi ~/.bash_profile
export PGHOME=/usr/local/postgres/
export PGDATA=/data/pgsql/data
export PGLIB=/usr/local/postgres/lib
export PATH=PGHOME/bin:SPATH

· 创建用户和数据目录

useradd pgadmin
mkdir /data/pgsql/{data,logs}
chown -R pguser. /data/pgsql

· 初始化PGSQL数据库

初始化完成后会创建template0、template1、postgres三个默认数据库。其中template0和template1作为数据库模板包含了系统的元数据表，在后续使用过程中如果创建了新的数据库，默认会以template1里的数据进行克隆，如果修改了template1里的信息，那新建的数据库就会继承这些设置。而template0则是一个纯净的数据库模板（该模板不应该进行任何修改），该模板不包含任何用户定义的对象，适用于需要一个完全干净的数据库的场景。除此还会创建pg_default和pg_global两个表空间，pg_default存放于$pgdata/base目录，pg_global存放于$pgdata/global目录。编译完成后还会在数据目录下创建postgresql.conf和pg_hba.conf2个配置文件，这里暂时先不做配置

su - postgrels
/usr/local/pgsql/bin/initdb -D /data/pgsql/data -U postgres -E UTF8 -W -X /data/pgsql/logs --wal-segsize=64
# -D：指定数据存放目录    
# -E：指定字符集
# -W：初始化时为管理员设置密码
#  --wal-segsize：每份WAL日志文件的大小，默认为16M

四、PostgreSQL 进程管理

通过pg_ctl工具对数据库进行管理，如果没有配置环境变量的话都需要通过-D选项指定数据目录

· 启动PGSQL服务

大部分情况下都使用pg_ctl工具启动数据库，该工具实际上也是postgres命令的封装

#-D指定数据目录
#-l指定日志文件，如果不加该选项会将日志全部输出在前台，日志文件默认存放在-D数据目录下
pg_ctl -D /data/pg12/data -l logfile start

· 停止PGSQL服务

有3种关闭服务的方式可以选择

smart：等所有连接中止后再关闭数据库。 如果客户端连接不终止则无法关闭数据库

fast：快速关闭数据库。如果有客户端连接都会被断开，未完成的事务将被回滚

immediate：强行关闭数据库，相当于杀死数据库进程

#停止服务
pg_ctl -D /data/pg12/data/ stop [-m fast|smart|immediate]

· 查看PGSQL运行状态

pg_ctl -D /data/pg12/data/ status

· 重新加载PGSQL

#下面两种方法都可以
pg_ctl reload;
select pg_reload_conf();  #登录PGSQL后执行

数据库postgresqlpgsql