忧愁的锅盖儿-CSDN博客

原创 PostgreSQL（二十五）PG_FDW的使用

FDW：Foreign Data Wrappers，可以让同构和异构数据库之间进行数据的访问。通过这个接口，可以让pg数据库与其他数据库进行数据的迁移。目前支持：pg-pg，pg-mysql，pg-oracle，pg-hadoop，pg-hive，pg-mongodb，pg-riak等。

2024-08-05 14:26:28 559

窗口函数也称为 OLAP 函数。通过 PARTITION BY 分组后的记录集合称为“窗口”，这里的窗口表示“范围”的意思。在使用时也可以不指定 PARTITION BY，此时会将这个表当成一个“大窗口”。从词语意思的角度考虑，可能“组”比“窗口”更合适一些，但是在SQL中，“组”更多的是用来特指使用 GROUP BY 分割后的记录集合，因此，为了避免混淆，在使用PARTITION BY 时称为窗口。

2024-08-05 14:16:48 1758

原创 PostgreSQL（二十三）TOAST技术

1、TOAST技术：超尺寸字段在PG的存储方式。2、TOAST技术产生背景：元组不允许跨页面存储。3、TOAST技术特点：它会将大字段值压缩或者分散为多个物理行来存储。4、对于用户来说，这一技术实现是透明的，无需关注。PG的部分类型数据支持toast，因为有些字段类型是不会产生大字段数据（比如等）。支持TOAST的数据类型应当是可变长度的（variable-length）。假如PG的表中任何一个字段存在TOAST，这个表都会有这一个相关联的TOAST表TOAST表的OID被存储在里。

2024-08-02 11:42:23 1120

原创 PostgreSQL（二十二）缓冲区管理器

缓冲区：存放各种类型的数据块（1）数据文件页、表和索引块（2）可用空间地图块（fsm（3）可见性地图块(_vm)（4）缓冲区数组索引--buffer_ids。

2024-07-12 16:05:26 737

原创 PostgreSQL（二十一）clog的作用与管理

85/32=2.6，表示第85个块在第2.6个数据文件中，0000 0001 0002，此事务ID记录在名为0002的clog文件中（也说明当前最大的事务文件就是这个）T1时刻：事务号200的事务提交，事务号201的事务未提交，则clog中200记录为commit，201记录为in_progress；前面已知：一个事务占用2bit位，一个字节可以存放4个事务状态，则一个页块可以存放32768个事务状态（8192*4）。T2时刻：事务号201的事务回滚，则clog中201的状态修改为abort。

2024-07-12 15:35:06 1042

原创 PostgreSQL（二十）行可见性规则

PG目前的官方版本并没有闪回功能，但从理论上来说，PG数据的更新时，新旧数据都保留在数据块中，可以实现像Oracle一样的闪回查询功能，只要在判断时先判断该查询是否是闪回査询，然后再根据一个针对闪回査询的可见性规则判断就可以实现。如果要实现闪回查询，会涉及到 Vacuum操作时需要考虑更多的因素，需要有个参数来设置块中被删除的行保留的时间长度。如果t_xmax≠0，则还得判断t_xmax的状态是否为当前事务，判断规则较为复杂；此时的状态判断需要考虑t_xmax的值，如果t_xmax=0，则此行可见；

2024-06-07 16:18:19 567

原创 PostgreSQL（十九）PG的事务概述

并发控制是一种在数据库中并发运行多个事务时，保持一致性和隔离性的机制，这是ACID的两个属性。并发控制技术有三种：MVCC（2）严格的两阶段锁：S2PL（3）乐观并发控制：OCC这一小节主讲MVCC。

2024-06-05 14:23:37 1230

原创 PostgreSQL（十八）TXIDS与冻结

txid=事务id：当一个事务开始时，pg的事务管理系统会为该事务分配一个唯一标识符：事务id，即txidpg中的txid被定义为一个32位的无符号整数，约能记录42亿个事务；通常来说，txid对管理者是透明的，但我们可以利用pg的内部函数，来获取当前事务的txid：事务ID用来标识一个事务的先后顺序，该顺序决定了锁申请的优先权，以及访问一张表时对行的可见性规则判断。

2024-04-07 13:34:06 1067

原创 PostgreSQL（十七）Autovacuum工具

Autovacuum是PG的一个后台进程，在9.6版本之后才出现，在PG实例启动时自动启动；每隔一分钟执行一次自动收集需要做Vacuum操作的表；当表的操作记录满足指定条件时，自动执行Vacuum操作；（1）一次可以运行多少个Autovacuum进程autovacuum_max_workers =3 （缺省值）#Autovacuum的工作进程个数参数，意为一次可以运行多少个Autovacuum进程，可以使用此参数，对工作进程进程管理。此参数运用在可能包含多个数据库的实例/群集上；

2024-04-07 13:17:34 1536

原创 PostgreSQL（十五）行结构与DML操作

此元组的事务的txid，即这一行是被哪一个事务插入的，它的状态是行可见性判断的关键依据，例如：当这个值=ABORTED时，判断此行为不可见；如果此元组未被删除或更新，则t_xmax设置为0，这意味着无效, 它的状态是行可见性判断的关键依据；t_ctid=(0,1)：指向自身，因为这是最新的元组，事务插入的第一行，未被修改过。t_ctid=(0,1)：指向自身，因为这是最新的元组，事务插入的第一行，未被修改过；在PG中，假如数据被删除，会修改行头和块头信息，但数据本身被保留，空间不会立刻释放。

2024-04-07 12:55:04 903

原创 PostgreSQL（十六）Vacuum工具

通过学习十五章可知，PG块中被删除的行，只是逻辑上删除（标记为删除状态），物理上还保留在块中，如果长时间不清理，会造成垃圾空间膨胀。设想，如果一个块中有50%死元组，那么就浪费50%的存储空间；如果读到内存，也会浪费50%的内存空间，所以需要定期的清理，定时清理工作由 Autovacuum来操作，也可以手动Vacuum操作。pg_freespacemap插件可以很好的监测块中空间的使用情况，可当作full vacuum操作的参考信息，可以提前安装。

2024-04-07 12:52:52 1056

原创 PostgreSQL（十四）数据文件与块存储结构

在PG的数据文件存储路径下，会发现有两个和数据文件同一个relfilenode但多了个后缀的文件，这两个文件就是空闲空间地图（’_fsm’，free space map）和可见性地图（’_vm’，Visibility map）当一个新的行被添加到页面中时，个新的行指针也被推到数组中以指向新的行。当单个数据文件超过1G后，将会产生新的文件，在oid后加上后缀，例如$oid.1、$oid.2以此类推。数据文件的fork为0号，空闲空间文件_fsm的fork为1号，可见性地图文集_vm的fork号为2.

2024-03-01 17:09:59 1337

原创 PostgreSQL（十三）检查点管理

因为备份时数据文件存在先后顺序，导致备份得到的数据文件不一致，所以将来恢复时，需要应用归档日志使其同步，此时，开始备份的位置就是将来recovery的位置。，则实例恢复需要的时间就短，可提高数据库的可用性，但是会增加l/O操作，降低数据库状态性能，检査点发生时属于密集型l/O操作，会占用大量系统资源；，则实例恢复需要的时间就长，会降低数据库的可用性，但是会减少I/O操作，提高数据库状态性能。，I/O占用资源越多，数据库性能越差，但检查点完成速度越快（值越小，要求检查点写的越快）；，开始recovery。

2024-03-01 16:44:30 1254

原创 PostgreSQL（十二）全页写-full_page_writes

在PostgreSQL（十一）WAL日志管理中曾提到过，PG默认记录WAL日志的方式，是将原始SQL转换为特定格式后记录在文件中。但实际上，pg还支持另一种记录WLA日志的方式，那就是全页写-full-page Writes。全页写模式下，会将一整个数据块的内容写入 WAL文件中，而非只记录日志条目。解决块分离问题，保护数据完整性；全页写会导致WAL日志膨胀，增加额外I/O，影响数据库整体性能；使用full_page_writes参数控制on/off .

2024-03-01 16:32:11 633

原创 PostgreSQL（十一）WAL日志管理

PG的WAL（Write Ahead Log）日志，意为预写式日志，特点是先记后做。例如：假设发生一个update事务，数据库的操作步骤：（1）将update事务转化为特定格式，形成日志条目，记录至内存中的wal buffer；（2）从磁盘的数据文件中读取需要update的数据至内存中的buffer cache，然后进行update，得到脏块；（3）commit这个事务（或满足其他触发条件），wal writer进程将wal buffer中日志条目记录至磁盘的wal文件中；

2024-03-01 16:25:41 2615

原创 PostgreSQL（十）控制文件管理

参数值获取方法：在$PGDATA/pg_multixact/members下面，找到文件编号中最大的数字，+1，然后乘以65536.转换成16进制为100000，然后末尾添加4个0 （-O 0x1000000000）参数值获取方法：在pg_xact下面，找到最大的文件编号，+1，后面跟上5个0（-x 0x000100000）参数值获取方法：在pg_multixact/offsets下面，找到最大的文件编号，+1，然后末尾添加4个0。得到：0x000100000。

2023-12-29 15:49:58 1116 1

原创 PostgreSQL（九）数据库集簇管理

数据库是数据库对象的集合。是由 PostgresqL服务器管理的数据库的集合，也名数据库集群（ Postgresql中的“数据库集群”一词并不意味着“一组数据库服务器”，而是 Postgresql服务器在单个主机上运行并管理的单个数据库群集）。（2）数据库属主不一定拥有存放在该数据库中其它用户创建的对象的访问权限，例如：数据库A的属主为用户a，假设用户b在数据库A中创建了对象x，用户a对对象x并不拥有访问权等权限，依然需要授权。同理，要访问某个数据库中的对象，就需要登录到指定的数据库中。

2023-12-06 15:16:20 181 1

原创 PostgreSQL（八）pg_hba.conf文件-实例连接访问控制

用户映射为 PG数据库内部用户,并以该内部用户的权限登录,且此时无需提供登录密码。在数据库、用户或地址域中引用一个关键字(例如，all或replication)将使该词失去其特殊含义，并且只是匹配一个有该名字的数据库、用户或主机。3、在 initdb初始化数据目录（$PGDATA）时，它会安装一个默认的pg_hba. conf文件。:这是当前提供的方法中最安全的一种,但是旧的客户端库不支持这种方法。4、pg_ hba.conf文件的常用格式是一组记录，每行一条。：表示文件中包含的用户列表。

2023-11-30 15:17:00 479

原创 PostgreSQL（七）权限管理

3、对超级用户 postgres不做权限检查，其它用户走ACL（Access Control list）；2、schema：PG的一个用户可以有多个schema，但一个schema只能属于一个用户；（可用此预防表被误删除）1、实例权限：控制部分用户、主机是否允许登录、访问数据库（白名单&黑名单）；1、每个数据库对象都有一个所有者，默认情况下，所有者拥有该对象的所有权限；4、对于数据库对象，开始只有所有者和超级用户可以做任何操作，其它走ACL；3、object：表、视图、索引等对象的增删改查的权限。

2023-11-28 17:06:22 2652

原创 PostgreSQL（六）用户与角色管理

1、用户与角色数据库用户用来访问、管理数据库中的对象(表、索引.)2、数据库角色用来管理数据库访问权限,简化权限的管理。在PG中，用户也可以实现这个功能。3、用户和角色在整个数据库集簇中是全局性的，不是针对某个单一数据库，只要有足够的权限，用户可以访问所有数据库的对象。4、数据库用户可以分为两类:超级用户-- postgres普通用户--根据需要创建。

2023-11-28 16:52:45 1400

原创 PostgreSQL（四）逻辑存储与物理存储结构

PG中所有数据库对象都由各自的oid在内部管理；oid是无符号的4字节整数。数据库对象和oid之间的关系存储在适当的数据字典中，具体取决于对象的类型；数据库oid存储于，表空间oid存储于；

2023-11-23 17:08:31 616 1

原创 PostgreSQL（三）实例结构与后台进程

WAL数据（也称为XLOG记录）是Postgresql中的事务日志；在这个过程中，共享缓冲池上的脏页会逐渐定期写入永久存储器（如HDD、SSD）。4、超时（心跳机制）；提交日志（CLOG）为并发控制（CC）机制保存所有事务的状态（例如，正在进行、已提交、已中止）。与用户进程一一对应，接收用户进程发来的请求，并将请求发送给后台进程（pgserver等）处理；后端进程，由postgres服务器进程启动，并处理一个连接的客户端发出的所有查询；各后台进程执行用于数据库管理的每个特性的进程(如清空、检査点进程)

2023-11-23 16:05:19 467 1

shmily_coco的博客