PostgreSQL教程（1）PostgreSQL体系结构与部署
一、PostgreSQL 特性
PostgreSQL简称PGSQL，作为目前最火爆的企业级关系型数据库，主要优点如下：
· 开源免费，国产数据库90%基于PostgreSQL进行二次开发，比如华为GaussDB、人大金仓KingBase、阿里Polardb-Postgres、腾讯TDSQL-Postgres
・多进程工作，对于复杂场景的处理速度比MySQL更快
・简单易用，可移植，可在Linux、Winddows、Mac OS等不同系统中运行
・为不同开发语言提供了丰富的API接口
・完全的事务安全性数据库，完整支持外键、视图、触发器、存储过程
· 支持标准的查询语言
· 更多的数据类型，比如数组、IP地址类型、JSON类型、几何类型、XML类型、复合类型等
· 作为企业级数据库，支持MVCC、按时间点恢复、异步复制、在线热备、分布式、读写分离、数据水平拆分等特性
・有很多免费的高品质图形化管理工具以及开源软件可以配合使用，比如pgAdmin 4
・安全性较高
二、PostgreSQL 体系结构
1、进程结构
PostgreSQL是一个多进程架构的数据库管理系统，服务启动后由多个进程来共同维护数据库的工作，以下是各进程的作用介绍：
· postgres进程：PostgreSQL启动后所有进程的父进程，早期版本为PostMaster。用于控制整个实例的运行、内存分配、建立客户端连接等
· background writer进程：后台写进程（BgWriter），用于把共享内存中的脏页写到磁盘的进程，可以提高性能、释放内存块
· WAL Writer：预写日志进程，类似MySQL中的redo log
· CheckPoint：检查点进程，数据库发生宕机重启后以最近的检查点为参考，将未完成的事务前滚，再重放检查点之后的WAL日志，保证数据一致
· SysLogger：系统日志进程，需在postgres.conf中将logging_collection设置为on，此时主进程才启动SysLogger进程
· PgArch：归档进程，WAL日志循环使用时，通过PgArch把需要被覆盖的日志进行归档，通过全备+归档数据可以实现数据恢复
· AutoVacuum：自动清理进程，当PGSQL表中数据进行删除后只是先标记为删除状态，当没有其他事务读这些数据时才会由AutoVacuum进行清理
· statistics collector：统计收集进程（PgStat），进行数据统计收集工作，比如一个表和索引进行了多少次的插入、更新等信息都会记录。
2、目录结构
在 PGSQL 中，数据文件、WAL 日志、配置文件、控制文件 等所有物理文件都保存在指定的数据目录中，数据目录在初始化时会进行指定，如/data/pgdata/。数据目录中会包含以下主要目录或文件
| 文件 | current_logfiles | 记录当前正在使用的日志文件 |
| PG_VERSION | 记录数据库版本 | |
| pg_hba.conf | 客户端认证配置文件 | |
| postgresql.conf | 数据库静态配置文件 | |
| pg_ident.conf | 用户映射文件 | |
| postgresql.auto.conf | 存储ALTER SYSTEM修改后生成的参数 | |
| postmaster.opts | 记录上次启动服务时的选项 | |
| 目录 | base | 每个逻辑数据库的目录，OID形式命名 |
| log | 日志目录 | |
| global | 数据库系统对象文件和pg_control文件 |
由于PGSQL为每个数据库单独创建的子目录是 OID 的形式，通过SQL语句可以查询数据库目录对应OID以及每个表对应的OID
#查询库OID SELECT oid,datname FROM pg_database; #查询表OID SELECT relname, relfilenode FROM pg_class WHERE relname='mytable';
3 、数据结构
在PostgreSQL中，数据结构分为数据库（database）、模式（schema）、表（relation）、行（tuple），相比MySQL来说多了一个模式（schema）的层级。模式（schema）用于将数据库里的对象（表、索引、函数等）逻辑上组织在一起，便于控制管理。通常建议把用户的对象根据业务进行分类，不同的业务放在不同的模式（schema）下。这些模式之间的数据是互相隔离的（类似于MySQL中的不同database）。一个用户可以创建多个模式，而一个模式只能属于一个用户。
如果在创建和访问表时不指定模式，那么操作的对象都属于PUBLIC模式，这是在建库时自动创建的一个模式。在PUBLIC模式下所有用户都有CREATE和USAGE权限，所以建议创建一个自定义模式然后让模式所有者来进行授权。
PostgreSQL数据文件和MySQL所不同的地方在于它并不是直接直观的存放在指定路径下以表名作为文件名，而是有一个OID的概念，每个库表都会生成一个OID，然后以OID作为文件名。要查询表的OID与存放路径可以运行以下SQL
select sys_relation_filepath('table_name');三、PostgreSQL 安装部署
PostgreSQL安装方式分为源码编码安装和YUM安装，两种安装包都可以通过官方下载地址https://www.postgresql.org/download/获取，下载的时候可以根据提示选择合适的版本
方法1、官方YUM安装PostgreSQL（安装过程简单，但无法自定义一些配置）
#配置yum仓库并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 #初始化数据库 /usr/pgsql-14/bin/postgresql-14-setup initdb #启动数据库，默认监听5432端口 systemctl enable postgresql-14 systemctl start postgresql-14
· 使用YUM安装后会自动创建一个拥有数据库登录权限的用户postgres。必须sudo到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 ln -s /usr/local/postgersql14.6 /usr/local/pgsql
· 为PGSQL创建管理用户和数据目录
useradd pgadmin
mkdir /data/pgsql/{data,logs}
chown -R pguser. /data/pgsql· 初始化PGSQL数据库集群
初始化完成后会创建template0、template1、postgres三个默认数据库，2个template数据库包含了系统的元数据表，用户后续创建数据库时都会以template1里的数据进行克隆，如果修改了template1里的信息，那新建的数据库就会继承这些设置。除此还会创建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 # -D：指定数据存放目录 # -E：指定字符集 # -W：初始化时为管理员设置密码
四、PostgreSQL进程管理
通过pg_ctl工具对数据库进行管理，如果没有配置环境变量的话都需要通过-D选项指定数据目录
· 启动PGSQL服务
#启动服务（该命令在初始化完成后会输出在屏幕上） #-D指定数据目录 #-l指定日志存储路径和文件名，否则默认输出在屏幕上，可通过配置文件进行修改 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后执行
评论