PostgreSQL第一课：初识与提高

DATAGURU专业数据分析社区PostgreSQL初始与提高讲师黄晓涛第1课PostgreSQL介绍与体系结构PostgreSQL初始与提高讲师黄晓涛DATAGURU专业数据分析社区什么是PostgreSQLPostgreSQL是一种极为优秀的关系型数据库，目前全球数据库排名第四初始与提高讲师黄晓涛DATAGURU专业数据分析社区PostgreSQL普及趋势PostgreSQL初始与提高讲师黄晓涛DATAGURU专业数据分析社区PostgreSQL的奖项创始人MichaelStonebraker获得数据库界唯一图灵奖2018年度数据库奖2017年度数据库奖*使用BSD开源协议PostgreSQL初始与提高讲师黄晓涛DATAGURU专业数据分析社区PostgreSQL的起源Ingresproject；UniversityofCalifornia,BerkeleyProprietaryversionofIngres；商业化MichaelStonebrakerPost-IngresMichaelStonebraker重返伯克利PostgresV1.0→V4.2ProjectendPostgres95开源(伯克利大学学生AndrewYu,JollyChen重写了SQL解释器，替换原项目中的基于Ingres的SQL解释器)更名PostgreSQL,1997.1.29发布开源版本V6.0。1973年1982年1985年1989年1994年1996年historyPostgreSQL初始与提高讲师黄晓涛DATAGURU专业数据分析社区PostgreSQL版本发布时间专业数据分析社区PostgreSQL初始与提高讲师黄晓涛PostgreSQL体系结构PostgreSQL初始与提高讲师黄晓涛DATAGURU专业数据分析社区体系结构ClientProcessPostmasterSharedbufferStatsbufferOtherbufferClogbufferWALbuffer共享内存BackgroupwriterWALWriterArchiverAutovacuumlauncher/workersSysloggerWALSender/ReceiverStatscollectorCheckpointer辅助进程WorkmemoryMaintenanceworkmemoryTempbuffer本地内存postgres服务进程INSTANCEDatafilesXlogfilesClogfilesLockfilesStatsfilesArchivelogsLogfilesConfigfilesDatabaseClusterPostgreSQL初始与提高讲师黄晓涛DATAGURU专业数据分析社区Postmaster进程Postmaster是守护进程，实际上是第一个postgres进程，主要职责有：数据库的启停监听客户端连接为每个客户端连接衍生(fork)专用的postgres服务进程当postgres进程出错时尝试修复管理数据文件管理数据库的辅助进程PostgreSQL初始与提高讲师黄晓涛DATAGURU专业数据分析社区postgres进程Postgres进程是实际上是postgres的子进程：直接与客户端进程通讯负责接收客户端所有的请求包含数据库引擎，负责解析SQL和生成执行计划等根据命令的需要调用各中辅助进程和访问各内存结构负责返回命令执行结果给客户端在客户端断开连接时释放进程ClientProcessPostmasterpostgres服务进程SharedbufferINSTANCEPostgreSQL初始与提高讲师黄晓涛DATAGURU专业数据分析社区本地内存本地内存是服务器进程独占的内存结构，每个postgre子进程都会分配一小块相应内存空间，随着连接会话的增加而增加，它不属于实例的一部分work_mem：用于排序的内存maintenance_work_mem：用于内部运维工作的内存，如VACUUM垃圾回收、创建和重建索引等等temp_buffers：用于存储临时表的数据PostgreSQL初始与提高讲师黄晓涛DATAGURU专业数据分析社区数据库实例实例是管理和访问数据库的一套方法，即所有客户端的请求，最终都会转换成实例对数据库磁盘文件的各种操作，从而达到读写数据的目的实例和数据库是一一对应的实例包含了若干个内存结构和进程SharedbufferStatsbufferOtherbufferClogbufferWALbuffer共享内存BackgroupwriterWALWriterArchiverAutovacuumlauncher/workersSysloggerWALSender/ReceiverStatscollectorCheckpointer辅助进程INSTANCEPostgreSQL初始与提高讲师黄晓涛DATAGURU专业数据分析社区共享内存SharedBuffer：–用于缓存表和索引的数据块–数据的读写都是直接对BUFFER操作的，若所需的块不再缓存中，则需要从磁盘中读取–在buffer中被修改过的，但又没有写到磁盘文件中的块被称之为脏块–由shared_buffers参数控制尺寸WAL(WriteAheadLog)Buffer：–预写日志缓存用于缓存增删改等写操作产生的事务日志–由wal_buffers参数控制尺寸ClogBuffer：–CommitLogBuffer是记录事务状态的日志缓存PostgreSQL初始与提高讲师黄晓涛DATAGURU专业数据分析社区辅助进程Backgroupwriter：–工作任务是将sharedbuffer中的脏数据页写到磁盘文件中–使用LRU算法进行清理脏页–平时多在休眠，被激活时工作Autovacuumlauncher/workers：–自动清理垃圾回收进程–当参数autovacuum设为on的时候启用自动清理功能–Launcher为清理的守护进程，每次启动的时候会调用一个或多个worker–Worker是负责真正清理工作的进程，由autovacuum_max_workers参数设定其数量PostgreSQL初始与提高讲师黄晓涛DATAGURU专业数据分析社区辅助进程WALwriter：–将预写日志写入磁盘文件–触发时机：•WALBUFFER满了•事务commit时；•WALwriter进程到达间歇时间时；•checkpoint发生时；Checkpoint：–用于保证数据库的一致性–它会触发bgwriter和walwriter动作–拥有多个参数控制其启动的间隔PostgreSQL初始与提高讲师黄晓涛DATAGURU专业数据分析社区辅助进程Syslogger：–采集PostgreSQL的运行状态，并将运行日志写入日志文件–logging_collector参数为on时启动，不建议关闭–log_directory设定日志目录–log_destination设定日志输出方式，甚至格式–log_filename设定日志文件名–log_truncate_on_rotation设定是否重复循环使用且删除日志–log_rotation_age设定循环时间–log_rotation_size设定循环的日志尺寸上线PostgreSQL初始与提高讲师黄晓涛DATAGURU专业数据分析社区辅助进程Archiver：–用于将写满的WAL日志文件转移到归档目录，该进程只有在归档模式才会启用StatisticsCollector：–统计信息的收集进程。收集表和索引的空间信息和元组信息等，甚至是表的访问信息。收集到的信息除了能被优化器使用以外，还有autovaccum也能利用，甚至给数据库管理员作为数据库管理的参考信息PostgreSQL初始与提高讲师黄晓涛DATAGURU专业数据分析社区数据库PostgreSQL在磁盘上的一整套文件集合叫做databasecluster数据库包含了数据文件、日志文件等多种文件，用于存储用户数据和保证数据一致性DatafilesXlogfilesClogfilesLockfilesStatsfilesDatabaseClusterPostgreSQL初始与提高讲师黄晓涛DATAGURU专业数据分析社区PostgreSQL目录结构data–base–global–pg_hba.conf–pg_logical–pg_multixact–pg_notify–pg_replslot–pg_serial–pg_snapshots–pg_stat–pg_stat_tmp–pg_subtrans–pg_tblspc–pg_twophase–PG_VERSION–pg_wal–pg_xact–postgresql.auto.conf–postgresql.conf–postmaster.opts–postmaster.pidPostgreSQL初始与提高讲师黄晓涛DATAGURU专业数据分析社区PostgreSQL目录结构data–global•1136#pg_pltemplate•1137#pg_pltemplate_name_index•1213#pg_tablespace•1214#pg_shdepend•1232#pg_shdepend_depender_index•1233#pg_shdepend_reference_index•1260#pg_authid•1261#pg_auth_members•1262#pg_database•……•pg_control•pg_filenode.map•pg_internal.initpg_control–用于存储全局控制信息pg_filenode.map–用于将当前目录下系统表的OID与具体文件名进行硬编码映射（每个用户创建的数据库目录下也有同名文件）。pg_internal.init–用于缓存系统表，加快系统表读取速度（每个用户创建的数据库目录下也有同名文件）。全局系统表文件–数字命名的文件，用于存储系统表的内容。它们在pg_class里的relfilenode都为0，是靠pg_filenode.map将OID与文件硬编码映射。PostgreSQL初始与提高讲师黄晓涛DATAGURU专业数据分析社区PostgreSQL目录结构data–base•1•13211•13212•16385–112–113–1247–1247_fsm–1247_vm–1249–1249_fsm–1249_vm–……–pg_filenode.map–pg_internal.init–PG_VERSIONbase目录用于存放数据库的所有实体文件下属子目录均以数据库OID命名数据库子目录下是以对象OID命名的文件PG_VERSION是当前数据库数据格式对应的版本号名字以_fsm结尾的文件是数据文件对应的FSM(freespacemap)文件，用位图方式来标识哪些block是空闲的以_vm结尾的文件是数据文件对应的VM(visibilitymap)，在做多版本并发控制时是通过在元组头上标识“已无效”来实现删除或更新的，最后通过VACUUM功能来清理无效数据回收空闲空间。PostgreSQL初始与提高讲师黄晓涛DATAGURU专业数据分析社区PostgreSQL目录结构pg_hba.conf–客户端认证配置文件，可以配置客户的连接协议、加密方式、ACL等等pg_wal–WAL日志目录，非常重要pg_xact–Commitlog目录，非常重要。V9版本以前为pg_clog目录postgresql.conf–该databasecluster的配置文件，文本格式postgresql.auto