bin目录:二进制可执行文件目录,此目录下有postgres、psql等可执行程序;pg_ctl工具在此目录,可以通过pg_ctl --help查看具体使用。
share:存放文档和配置模板文件,一些扩展包的sql文件在子目录extension下。
用于配置数据库的远程连接,通过加入以下命令行,运行任何用户远程连接本数据库,连接时需要提供密码。
所有配置信息在系统视图pg_settings中可查看,通过context可知修改相关配置后是否需要重启。
记录服务器上次启动的命令行参数 |
该系统字段只有在创建表时使用了
with oids
或配置参数default_with_oids
的值为真时出现。字段类型也是oid,是4字节无符号整数,不能提供大数据范围内的唯一性保证,因此pg官方不推荐在用户表中使用oid字段。
常见psql快捷命令(通过psql连接数据库后,通过“\”开头的快捷命令进行数据库相关操作,tab键可补全命令):
查询当前登录的数据库和用户 |
name可以包含通配符*或?,可以是表名、索引、视图、序列、函数。如果使用\d+ 会显示的更详细
|
pg中用户和角色是不区分的 |
执行外部文件的sql命令 |
类似vi,退出vi后会执行其中输入的内容 |
不加函数名显示函数模板。退出vi后可\reset来清除命令缓冲区数据,防止误操作。 |
不加函数名显示视图模板。退出vi后可\reset来清除命令缓冲区数据,防止误操作。 |
查看服务器版本详细信息 |
查看服务器数字版本信息包括小版号 |
服务器将不允许新连接,等所有连接断开才关闭数据库 |
不再允许新连接,并向所有子进程发送 SIGINT 信号,让它们立刻退出,然后等待子进程退出后关闭数据库 |
立即关闭并退出,下次启动时数据库重放WAL日志进行恢复。仅用于紧急情况的关闭。 |
分为逻辑备份和物理备份。
pg_dumpall是将一个pg集群全部转存到另一个脚本文件(sql脚本、归档文件)中,而pg_dump命令可以选择一个数据库或部份表进行备份。pg_dump结合pg_restore使用,能灵活备份和恢复。
最简单的物理备份就是冷备份,即:停止pg,然后拷贝pg的data目录。
即不停止数据库进行备份,常见的方法有:PITR方法、使用文件系统或块设备级别的快照功能完成备份。Linux下最简单的备份方式是使用LVM的快照功能。
查看最后load配置文件的时间 |
查看pg是否正在做基础备份 |
decimal精度可达1000,适用于货币金额等要求精度的场合。
real(非精确类型的浮点小数,float4)、double(非精确类型的浮点小数,float8);
另外两个浮点数值做相等性比较时可能不符合预期。
完全保证精度,不同国家其输出格式不同。
pg中的自增字段,通过序列(sequence)来实现的。
(2^n)-1
是好的磁盘或内存块对齐,对齐块更快。今天“块”的大小更大,内存和磁盘足够快,可以忽略对齐。
做大小写无关的比较时使用 |
第二个参数为起始位置,第三个参数为截取长度(可不填) |
适用于存储图片等信息。
是一串由1和0组成的字符串。相比二进制类型,在位操作方面更方便。
time、timestamp根据是否包括时区又分为两种类型。interval 表示时间间隔。
时间戳最大值,比任何时间都晚 |
mysql也有枚举类型,用法稍微不同。
包括类型:点(point)、直线(line)、线段(lseg)、路径(path)、多边形(polygon)、圆(cycle)。
ipv4或ipv6的网络地址 (总是显示掩码) |
ipv4或ipv6的网络地址或主机地址 |
数组的类型可以是数据库内建的类型、用户自定义的类型、枚举或者组合类型。
两个数组维度、元素个数及值和顺序完全一致为真 |
按BTree比较函数逐个元素比较 |
同理有<@ ,表示 被包含
|
是否有共同元素。可跨纬度计算。 |
在jpa中,两个数组取交集,即&&运算这样使用:
可以存储xml类型,自动校验是否合法。
主要区别在于效率:json类型存储快,使用慢;jsonb类型存储稍慢,使用较快。
json是对输入的完整拷贝,使用时再去解析,所以它会保留输入的空格,重复键以及顺序等。
jsonb是解析输入后保存的二进制,它在解析时会删除不必要的空格和重复的键,顺序和输入可能也不相同。使用时不用再次解析。
json类型和pg类型映射:
sql中NULL表示的意思不同 |
json和jsonb的操作与函数基本通用。
json类型没办法直接建索引,但是可以建函数索引。
jsonb类型的列上可以建索引,GIN索引可以高效的从jsonb内部的key/value对中搜索数据。(BTree索引效率较低)
主要用于范围快速搜索。
直接通过库里的开始值和结束值进行范围搜索效率较低,而使用range类型,通过创建空间索引的方式来执行范围搜索会大大提高效率。
不能作为pg的字段,主要用于声明函数的参数和结果类型。
pg核心库没有提供生成UUID的函数 | |
pg9.4以上版本支持。用于表示LSN的数据类型,64位大整数。 | LSN表示WAL日志的位置 |
pg表也支持TOAST(跨页存储的大字段,需要将行外数据存储到TOAST表)。
当主键或者unique key发生冲突时,什么都不做(同时也适用于多个字段的唯一性约束):
upsert方法,冲突则更新,否则插入:
表查询没有指定查询顺序时,按插入顺序进行排序。
类似like的用法,在like的基础上又增加了与POSIX正则表达式相同的模式匹配元字符。
重复前面的项0次或更多次 |
重复前面的项一次或更多次 |
重复前面的项0次或1次 |
重复前面的项m次或更多次 |
重复前面的项至少m次,不超过n次 |
pg中有两种正则表达式:
脚本语言中的标准正则表达式 |
匹配正则表达式,区分大小写 |
匹配正则表达式,不区分大小写 |
不匹配正则表达式,区分大小写 |
这个函数功能强大,可以使用正则表达式。
pg中的表空间就是为表一指定个存储目录,主要用于把表存放到不同的存储目录。
表分区是逻辑上把大表分割成物理上几块,如按时区分、按类型分等。
分表通过规则或触发器优缺点对比:
pg内部通过表继承来实现分区表。pg10.x通过声明式分区直接创建分区表,但其内部原理依然是表继承。
相比表继承分区,不需要在父表创建各种触发器(降低维护成本),对父表的DML操作会自动路由到相应分区。
目前仅支持范围分区和列表分区。
1. 创建全局主键必须携带分区字段
2. 不允许全局创建索引,可以在分表上创建索引。
3. 删除主表不会删除子表,通过级联删除可以一起删除。
4. 查询主表时若在where条件中携带分区字段(如日期),可直接去分区表检索,提高检索速度。注意:这个字段不包含运算,否则失效。
sql查询中,where语句的过滤条件与分区表的check条件进行对比,直接跳过不需要扫描的分区表。
注意:分区字段的where语句如果包含计算,可能会扫描全表,需要解释执行确认。
可能我的navicat版本太低了看不见主键,通过以下sql可以查询到主键。
序列对象(也叫序列生成器)就是用CREATE SEQUENCE 创建的特殊的单行表。一个序列对象通常用于为行或者表生成唯一的标识符。通常用于表的主键自增。
mysql中的序列有以下两个限制,但pg中的序列没有。
将id类型设置为:serial,pg会自动创建一个序列,同时将列设置为INT,默认值设置为nextval(‘序列’)。serial8会将列设置为int8(long)
事务回滚不会影响序列。
如果cache大于1,当序列被用于多会话时,每个会话在每次访问序列对象的过程中都会分配并缓存随后的序列值,会话结束时丢失没有使用的数字,从而导致序列出现空洞。
多个会话的情况下,如果cache大于1,那么只能保证nextval值是唯一的,缺不按顺序生成。比如cache=10,会话A保留了1……10并且返回nextval=1,会话B保留了11……20。会话A返回nextval=1之前会话B可能先返回nextval=11。
触发器是一种由事件自动触发执行的特殊的存储过程。主要用于加强数据的完整性约束和业务规则上的约束。
PostgreSQL的触发器是数据库自动执行\指定的数据库事件发生时调用的回调函数。
语句触发:(修改0行的操作依然会触发。按语句触发,而不管这条语句操作了多少行)
行触发:(如果执行的语句没有更新实际的行,那么不会+触发)
触发器删除不会删除对应的触发函数;但删除表时,表上的触发器会一并删除。
触发函数常用特殊变量:
2. 语句级别触发器、DELETE操作触发器 中此变量未分配。 4. 语句级别触发器、INSERT操作触发器 中此变量未分配。pg9.3开始支持Event Trigger,主要用于弥补pg以前版本不支持DDL触发器的不足。
[OR REPLACE]:是可选的,它允许修改/替换现有函数。
DECLARE:定义参数(参数名写在前面 类型写在后面)。
RETURN:指定要从函数返回的数据类型(它可以是基础,复合或域类型,或者也可以引用表列的类型)。
可以将 IN 参数传递给函数,但无法从返回结果里再获取到。 |
OUT 参数经常用于一个函数需要返回多个值,所以不需要 RETURN 语句。 |
INOUT 参数是 IN 和 OUT 参数的组合。这意味着调用者可以将值传递给函数,函数然后改变参数并且将该值作为结果的一部分传递回去。 |
PostgreSQL 函数可以接受可变数量的参数,其中一个条件是所有参数具有相同的数据类型。参数作为数组传递给函数。 |
与 Java 等编程语言相同,PostgreSQL 允许多个函数具有相同的名称,只要参数不同即可。如果多个函数具有相同的名称,那么我们说这些函数是重载的。当一个函数被调用时,PostgreSQL 根据输入参数调用确切的函数。
pg中角色和用户没区别。超级用户为postgres。
规则系统更准确地说是查询重写规则系统,使用时也可以被函数和触发器替代,但原理和使用场景不同(这在上文讲分表的时候就提到过)。对于批量操作,规则比触发器效率更高。
适合两个表的数据量都比较少的情况(最简单的 table join 方式)。
优化器使用两个表中较小的表(或数据源)利用连接键在内存中建立散列表,然后扫描较大的表并探测散列表,找出与散列表匹配的行。
这种方式适用于较小的表完全可以放于内存中的情况,这样总成本就是访问两个表的成本之和。但是在表很大的情况下并不能完全放入内存,这时优化器会将它分割成若干不同的分区,不能放入内存的部分就把该分区写入磁盘的临时段,此时要有较大的临时段从而尽量提高I/O 的性能。
mrege join的性能开销几乎都在前两步。
在全表扫描比索引范围扫描再通过rowid进行表访问更可取的情况下,merge join会比nested loops性能更佳。当表特别小或特别巨大的时候,实行全表访问可能会比索引范围扫描更有效。
对于重复的代码逻辑,sql执行速度远远大于代码逻辑。
但是,由于sql难以测试、难以复用、难以加工变量,对于复杂的逻辑不建议用在sql中。代码可以分成模块、逻辑独立、方便测试。
sql优化的思路有两种:一是:
即去掉无用的步骤;二是优化算法,如让sql走更优的执行计划上。
RBO和CBO是两种数据库引擎在执行sql语句时的优化策略。
这是一种比较老的技术,简单说基于规则的优化就是当数据库执行一条query语句的时候必须遵循预先定义好的一系列规则(比如oracle的15条规则,排名越靠前的执行引擎认为效率越高)来确定执行过程,它不关心访问表的数据分布情况,仅仅凭借规则经验来确定,所以说是一种比较粗放的优化策略。
基于代价的优化的产生就是为了解决上面RBO的弊端,让执行引擎依据预先存储到数据库中表的一些实时更新的统计信息来选择出最优代价最小的执行计划来执行query语句,CBO会根据统计信息来生成一组可能被使用到的执行计划,进而估算出每个计划的代价,从而选择出代价最小的交给执行器去执行,其中表的统计信息一般会有表大小,行数,单行长度,单列数据分布情况,索引情况等等。
基于规则的优化器更像是一个经验丰富熟知各条路段的老司机,大部分情况可以根据自己的经验来判断走哪条路可以更快的到达目的地,而基于代价的优化更像手机里面的地图,它可以选择出许多不同的路径根据实时的路况信息综合考虑路程长度,交通状况来挑出最优的路径。
pg中与内存有关的配置参数:
shared_buffers是一个8KB的数组,postgres在从磁盘中查询数据前,会先查找shared_buffers的页,如果命中,就直接返回,避免从磁盘查询。
多个进程通过共享内存技术来共享缓存中的数据。
索引,索引也存储在8K块中;
执行计划,存储基于会话的执行计划,会话结束,缓存的计划也就被丢弃。
1)在访问数据时,数据会先加载到os缓存,然后再加载到shared_buffers,这个加载过程可能是一些查询,也可以使用pg_prewarm预热缓存。
2)当然也可能同时存在os和shared_buffers两份一样的缓存(双缓存)。
3)查找到的时候会先在shared_buffers查找是否有缓存,如果没有再到os缓存查找,最后再从磁盘获取。
4)os缓存使用简单的LRU(移除最近最久未使用的缓存),而数据库采用的优化的时钟扫描,即缓存使用频率高的会被保存,低的被移除。
提高shared_buffers,增加缓存命中率,提高查询效率。
pg的数据文件都存储在文件系统中,os的文件系统也有缓存,这导致pg的数据库副本可能同时存在于共享内存和文件系统中,造成内存利用率低的问题。
2)linux服务器建议设置为25%,亚马逊服务器设置为75%(避免双缓存,数据会存储在os和shared_buffers两份)
os缓存的重要性:数据写入时,从内存到磁盘,这个页面就会被标记为脏页,一旦被标记为脏页,它就会被刷新到os缓存,然后写入磁盘。所以如果os高速缓存大小较小,则它不能重新排序写入并优化io,这对于繁重的写入来说非常致命,因此os的缓存大小也非常重要。给予shared_buffers太大或太小都会损害性能。
注意:work_mem是每次分配的内存,加入有M个并发进程,每个进程有N个HASH操作,那么需要分配的内存为 MNwork_mem。因此work_mem不宜设置太大,通常保持默认的4MB即可,如果设置的太大超过256MB,很容易因为瞬间的大并发操作导致oom。
为每个不同的进程单独分配的内存,不在共享内存中,默认为8MB。
默认为-1,表示根据shared_buffer的大小自动设置。
默认值为try,表示尽量使用大页。若os未开启大页,不使用大页内存,不影响数据库正常使用。
sql审计记录的标准,超过该时长的sql将被记录到日志文件。默认为-1,不记录超时sql。 |
none默认,不记录;all-记录所有语句;ddl-记录所有数据定义语句;mod记录所有ddl和数据修改语句; |
控制日志中是否记录导致数据库出现错误的SQL语句。默认为error |
启动pg,主进程为Postmaster(pg的bin目录下,是一个指向Postgres的链接)。Postmaster是整个数据库实例的总控进程,负责启动和关闭数据库实例,同时fork出一些与数据库实例相关的辅助进程,并对其进行管理。
每次客户端与数据库建立连接时,pg数据库都会启动一个服务进程来为该连接服务,故而是进程架构模型,而MySQL是线程架构模型。当某个服务进程报错时,Postmaster主进程会自动完成系统恢复,恢复过程中停掉所有的服务进程,然后进行数据的一致性恢复,恢复完成后数据库才能接受新的连接。
autovacuum 是 postgresql 里非常重要的一个服务端进程,能够自动运行,在一定条件下自动触发对 dead tuples 进行清理并对表进行分析。
在pg中更新、删除行后,数据行并不会马上从数据块中清理掉,而是需要等VACUUM时时清理。为了加快VACUUM速度并降低对系统I/O性能的影响,pg8.4.1之后为每个数据块文件加了一个后缀为“_vm”的文件(可见性映射表文件,VM文件)。这个文件为每个数据块存储了一个标志位,标记数据块中是否存在要清理的tuple。
默认为on,表示是否开起autovacuum。当需要冻结xid时,尽管此值为off,PG也会进行vacuum。 |
通过缩短实际,调整回收频率,减少每次回收量,可以减小wal压力 |
向日志打印autovacuum的统计信息(以及资源消耗),大于阈值,输出这次autovacuum触发的事件的统计信息 。 “-1”表示不记录。“0”表示每次都记录。 |
最大同时运行的worker数量,不包含launcher本身。 |
改小可以降低vacuum触发条件,提高vacuum频率 |
改小可以降低vacuum触发条件,提高vacuum频率 |
设置较大值,减少因事务id消耗造成全表扫描的频率。(1000million、1200million) |
设置过大,会导致AUTOVACUUM launcher触发的vacuum耗时过长。特别是大表,耗时会非常长,可能导致膨胀等问题。可以调小一点,0. |
Tuple:表中的行(其它DB中的ROW)。
Page:磁盘中的数据块 。
Buffer:内存中的数据块。
数据块大小默认8KB,最大为32KB,一个数据块中存储了多行数据。
MVCC是数据库中并发访问数据时保证数据一致性的一种方法。实现MVCC的方法有以下两种:
pg在事务提交前,只需要访问原来的数据;提交后,系统更新元组的存储标识,直到Vaccum进程回收为止。
① 事务回滚可以立即完成;
② 数据可以进行很多更新,不必像Oracle和InnoDB那样需要经常保证回滚段不会被用完,也不会像Oracle数据库那样,经常遇到ORA-1555错误的困扰。
① 旧数据需要Vaccum清理。
② 旧版本数据的存在降低查询速率,需要扫描更多的数据块。
插入该行版本的事务ID。
删除此行时的事务ID。
第一次插入时,xman=0。如果xman>0,说明删除事务未提交或者被回滚了。
当两个事务同时访问记录时,通过xmin和xman的标记判断记录的版本,根据版本号与自己当前事务标识比较,确定数据权限。
事务内部的插入类操作的命令ID,此标识是从0开始。
事务内部的删除类操作的命令ID。如果不是删除命令, xman = 0。
xid一直递增达到2^32为最大值,然后继续从头开始,以前的xid比新的xid大,导致比较新旧事务空难,即事务回卷问题。
为了确保事务的可见性需要频繁判断事务之间的新旧关系,如:数据行中已提交的事务比当前事务更早,则这条数据行对当前事务可见。
pg规定,最早和最新两个事务之间年龄差对多为231。
当超过231时,就把旧的事务换成一个FrozenTransactionId=2的特殊事务,当正常事务ID与冻结事务ID比较时,会认为正常xid比FrozenTransactionId更新。即:xid空间虽然有232,但被一分为二,对某个特定的xid,其后231个xid属于未来,均不可见;其前231个xid属于过去,可见。
直接将符合条件的元组的t_xmin设置为2,回收原来的xid。但这样实现的问题是:一)当前可见的数据页需要全部扫描,带来大量的IO扫描;二)符合条件的元组需要更新xmin,造成大量脏页,带来大量IO
不直接修改t_xmin,而是:一)只更新元组头结点的t_infomask为HEAP_XMIN_FROZEN,表示该元组已经被冻结过(frozen);二)有些插入操作,也可以直接将记录置为frozen,例如大批量的COPY数据,insert into等;三)如果整个page所有记录已经frozen,则在vm文件中标记为FROZEN,冻结清理会跳过该页,减少了IO扫描。
freeze是被动触发的,可以调节pg的一些参数优化freeze,更多时候提倡用户进行主动预测需要freeze的时机,选择合适的时间(比如pg负载较低的时间)主动执行vacuum freeze命令。目前已经有很多实现好的开源PostgreSQL vacuum freeze监控管理工具,比如flexible-freeze,能够:确定数据库的高峰和低峰期;在数据库低峰期创建一个cron
pg在数据目录的pg_wal子目录(10版本之前是pg_xlog目录)中始终维护一个WAL文件。
wal文件记录了db数据文件的每次改变,最初设计是为了db异常崩溃后,能够重放最后一次checkpoint之后的日志文件,把数据块推到最终的一致状态,避免数据丢失和不一致。
WAL文件的存在可以让备份数据块存在不一致的数据,通过重放WAL日志文件加以纠正,推到任意一个时间点,即基于时间点备份(PITR,Point-in-Time Recovery)。
对于standby db,只要应用wal日志足够快,就能保持与Master db一致的数据。把WAL日志传送到另一台机器的方法有两种:
主备同步wal相关配置:
如果双节点,设置为ON,如果是多副本,同步模式,建议设置为remote_write。如果磁盘性能很差,并且是OLTP业务。可以考虑设置为off降低COMMIT的RT,提高吞吐(设置为OFF时,可能丢失部分XLOG RECORD) |
如果文件系统支持COW例如ZFS,则建议设置为OFF。 如果文件系统可以保证datafile block size的原子写,在对齐后也可以设置为OFF。 |
缩短延迟,加快wal写的速度 |
反馈给主节点自己已经接受( replies )到数据信息。 |
增大,加快wal落盘速度 |
为pg提供的一个轻量级连接池工具。
基于触发器的两个pg的逻辑同步。
双向同步工具,可以实现pg的双(多)master方案。
pg和客户端之间的中间件。
Postgres-XC是基于pg实现的真正的数据水平拆分的分布式数据库。
路面修补料的优点是什么?
一建的高强快速修补材料专门针对水泥混凝土公路路面的麻面、起皮、脱壳、漏骨、起沙、冻融等害进行及时有效的修补,不需要长时间的封闭交通,直接涂刷在破损表面即可,实现超薄修补,具备强大的抗压性、粘结性、耐久性、抗裂性抗剥落和抗腐蚀功等优点。
二是子形,质地不限。若是仿制文物,应力求保持原文物的神韵。B翻模:翻模所用的原料不限,因常用的制模石膏、硅橡胶、泥质等均与人造石料无化学反应。子形若是模制品,可能因翻制而丢掉一些层次,或本身就有所欠缺时,则应先用石膏或收缩率小的原料,一翻制件,在其上修整,以弥补上述不足,然后再次翻制模具此法虽较麻烦,但制品的效果较佳。C脱模剂:凡常用的脱模剂均可,如软皂水、凡士林或机用润滑脂(俗称黄油,使用时均需用 等溶剂稀释)、硅油等。
声音在空气中传播的速度是340米/秒,火车速度为90千米/时,火车到桥头处鸣笛,5秒钟后
声音在空气中传播的速度是340米/秒,火车速度为90千米/时,火车到桥头处鸣笛,5秒钟后,站在另一桥头处的人听到火车鸣笛声(无风),求再过多少时间火车行驶到另一桥头?