原文：https://www.cnblogs.com/gc65/p/10652995.html

1.高可用

       服务可用时间量，冗余是高可用的基础

2.备用数据库

       备用数据库的想法是保留始终具有相同数据的生产数据库的副本，并且可以在故障发生时使用备份数据库。有以下几种方式对备用数据库进行分类。

       根据复制的性质：

              物理备份数据库：复制磁盘块

              逻辑备份数据：复制数据流更改

       备份的同步性：

              异步：存在数据丢失的可能性

              同步：不存在数据丢失的可能性，主设备的提交等待备用设备的响应。

3.件目录结构

postgresql的磁盘布局：

data/base

       存放各数据库实例对应文件夹，命名方式是数据库的OID，select oid,datname from pg_database;可以查询每个数据库的OID，对应的可以在base目录下找到这个数据库的文件夹。

　　数据表使用表名对应的relfilenode作为文件名存放在数据库文件夹下，select relfilenode,relname from pg_class;

　　可以查询表的relfilenode，然后在相应的数据库文件夹下可以看到这个表的文件，该文件用于存储表的数据，最大1G，超出自动扩展,扩展名为relfilenode.1,relfilenode.2 ...

       为了提高I/O性能，pg总是以8K大小的块执行IO。因此数据文件总是以8K的步长增长。（物理复制时，双方需要使用相同的块大小）

global  -全局数据

       包括全局系统表

pg_clog  -提交日志

       提交日志是一个工作数据库实例的一个重要组成部分。它存储系统上进行的事物的状态。

　　一个事物有四种状态（TRANSACTION_STATUS_IN_PROGRESS,TRANSACTION_STATUS_COMMITTED,TRANSACTION_STATUS_ABORTED,TRANSACTION_STATUS_SUB_COMMITTED）,

　　如果一个事物的提交日志的状态是不可用，postgresql将不知道是否应该被看到。

pg_hba.conf  -基于主机的网络配置

       常用认证方式

              trust

              ident

              md5

pg_ident.conf  -身份认证

pg_notify-监听、通知数据

       系统存储关于监听、通知的信息（异步后端接口）

pg_serial-关于提交序列化事物的信息

       序列化事物的信息存储在这里。我们必须在磁盘上存储序列化事物提交信息，以确保长时间运行的事物不会膨胀内存，内部采用一个简单的SLRU结构来跟踪这些事物

pg_snapshot-输出快照

pg_stat_tmp-临时统计数据

       该信息被大多数pg_stat_*系统视图所需要

pg_subtrans -子事物数据

pg_xlog  postgresql的事物日志（WAL）

       WAL(Write Ahead Log)和XLOG是一个事物的两个名字。默认事物日志文件大小16M。

　　xlog不可能无限增大，需要删除无用的xlog，原则：如果xlog文件中的所有更改也被放到数据文件中，xlog才可以被截断（删除）。需要检查点的配置

postgresql.conf  -pg配置文件

4.配置参数

shared_buffers

　　数据库服务使用的共享内存缓冲区。默认值128MB。（一般使用Mem的25%，不超过40%,因为pg还要依赖于os cache）

background writer

　　pg的一个独立子进程，用于将shared_buffers中的dirty buffer写入磁盘。（数据的修改不用立即写入磁盘，因为写入了wal日志，可以用于故障恢复）

　　background writer会根据LRU链表，扫描shared buffers，如果发现脏页，就会调用系统write，写入磁盘。具体调用策略见下面参数

bgwriter_delay

　　指定background writer将dirty buffer写入磁盘的频率，默认200ms，即200ms写一次，写入周期200ms。

bgwriter_lru_maxpages

　　指定一次写入周期 写入的dirty buffer的最大数量(单位buffers)，默认值100

bgwriter_lru_multiplier

　　一次写入周期要写到磁盘的dirt buffers的数量T，是基于上一个周期内server进程需要的新的buffers数量N。

　　bgwriter_lru_multiplier是一个乘法因子p，T = N * p;较大的值为峰值提供了缓冲，较小的值则需要服务器进程完成写入。

　　例:

　　　　上一个写入周期内服务器使用了100个new buffer（100次写产生不同的脏数据），bgwriter_lru_multiplier使用了默认值2.那么这次会将100*2个dirty buffers写到磁盘中，也就是腾出了200个干净的buffers，

　　　　如果再下一次写入周期前，服务器申请的buffers数量小于这个值，则自然够用，如果大于200，那么需要服务器自己去触发将dirty buffers写入磁盘。

备注

　　上面的写入磁盘行为指的是普通意义的向磁盘文件写，但是会被操作系统缓存，所以实际行为可能没有写到磁盘中。

bgwriter_flush_after

　　当超过bgwriter_flush_after bytes的数据被bgwriter写时，强制OS将缓存数据写入物理磁盘。默认值为512KB

write ahead log（WAL）

wal_level

　　指定写入到wal中的信息，默认是minimal，只写从crash中恢复或者快速shutdown需要的信息。

　　replica 是在minimal的基础上添加wal archiving需要的信息。

　　logical 增加逻辑编码需要的信息。minimal wal不包含从base backup和wal log重新构造数据库数据的信息。replica或者logical可以。老版本的参数archive或者hot_standby 在这里被映射到replica模式

fsync

　　指明数据更新时是否调用fsync将数据从os cache中刷新到磁盘。

synchronous_commit

       指定在向事物提交指令返回success之前是否需要等待wal records写到物理磁盘。可选值on，remote_apply，remote_write，local，off。

       如果synchronous_standby_names非空(集群)，这个参数控制事物提交是否需要等待wal records被复制到standby servers。

       如果设置成on，事物提交 需要等到current synchronous standby应答已经收到事物记录切刷新到磁盘。

       如果设置成remote_apply，事物提交 需要等到current synchronous standby应答已经收到事物记录且应用到内存中。

       当设置成remote_write，事物提交需要等到current synchronous standby应答已经收到事物记录且写到了os cache中。

       local表示事物提交只需要等到本机wal records flush to disk。

       如果synchronous_standby_names为空，on，remote_apply,remote_write，local提供同样的同步级别：事物提交需要等到wal record flush to disk（由fsync来控制）

wal_sync_method

       强制wal 从os cache更新信息到磁盘的方法，如果fsync是关闭的，这个参数无效。

       下面open开头的是基于linux open功能函数，f开头的是基于linux sync功能函数

       Short answer seems to be that the physical write is guaranteed: open = immediately; fsync = at commit

       open_datasync

       fdatasync（linux默认选项）

       　　和fsync类似，但是只在必要的时候才将metadata写到磁盘。如：文件access time，modified time改变不需要flushing，file size改变需要写到磁盘（因为时间相关的元数据出错不影响文件读写，size出错会导致新增数据读不到）

       fsync

       　　将缓存的内核数据刷新到物理磁盘，同时刷新文件的metadata（文件属性：大小，修改更新时间等）数据到磁盘。一般文件元数据和文件不在一起，所以这里需要写两次磁盘（两次寻址）

　　fsync_writethrough

       open_sync

full_page_writers

       当设置为on的时候，pg server会在checkpoint之后页的第一次修改之时将整个页写到wal records中（wal replay从最后一个checkpoint之后开始）。

　　这是因为当向数据库写入一个页时，可能发生os奔溃，导致数据库中的这个页同时存在新旧数据，存储在wal log中的行修改记录不足以进行奔溃恢复。关闭这个参数不影响PITR。默认on

wal_log_hints

       当这个参数为on时，PostgreSQL服务器一个检查点之后页面被第一次修改期间把该磁盘页面的整个内容都写入WAL，即使对所谓的提示位做非关键修改也会这样做。默认关闭

wal_compression

       设置为on时，server会压缩一个整页镜像到wal log中（当开启full_page_writes或者在base backup期间）。压缩的页镜像会在wal 回放时解压。默认off。开启会减小内存消耗，但是增加cpu消耗

wal_buffers

       wal data（写入磁盘之前存在内存中的数据量）使用的共享内存大小。默认是shared_buffers/32.不大于wal segment（一般16M）。

wal_writer_delay

       指定flush wal的频率。每执行完一次flush就会sleep wal_writer_delay ms，除非被异步提交事物唤醒。如果距离上次flush时间不到wal_write_delay并且新产生的wal data小于wal_writer_flush_after bytes，则写wal信息只会写到os，不会刷新到磁盘。

       即 将wal刷新到磁盘的条件：执行周期大于等于wal_write_delay，或者小于wal_write_delay但是新产生的wal data大小大于wal_writer_flush_after。

       wal_writer_delay默认值是200ms。

wal_writer_flush_after

       指定触发wal writer flush的 wal data的大小。默认值为1MB。

commit_delay

       flush wal data之前的等待的时间间隔，单位us。通过一次flush多个事物日志来提高吞吐量（如果在给定的时间间隔内同时有其它事物提交）。

       当准备提交事物的时候如果并发活跃事物数量大于commit_siblings才会应用commit_delay机制（避免无效等待，如：延迟之后只提交了自己的事物）。如果关闭了fsync，也不会采用delay机制。默认值为0（no delay）

commit_siblings

       应用commit_delay的前提：统一时间并发活跃连接的数量。

5.checkpoints

       At checkpoint time,all dirty data pages are flushed to disk and a special checkpoint record is written to the log file

checkpoint_timeout

       执行检查点时间周期。默认值是5min。即5分钟执行一次checkpoints

checkpoint_comletion_target

       指定checkpoint任务实际完成时间：checkpoint_timeout * checkpoint_comletion_target。 默认值0.5（即需要2.5min完成一次检查点操作）

       主要用来控制checkpoint操作进行磁盘IO的速率，checkpoint进程会根据这个值来调整磁盘IO的速率（避免IO负载过高）。

checkpoint_flush_after

       当执行检查点的时候，如果超过checkpoint_flush_after bytes的数据被写入了，那么强制os刷新数据到物理磁盘。这样限制了内存页的脏数据数量，减少了刷新数据到磁盘时的停顿时间。默认值256KB

checkpoint_warning

       当填充检查点段文件的总时间超过这个时间时，向server log中写入日志记录。

max_wal_size

       wal log的最大数量，这是一个软限制，如wal_keep_segments设置的较大。默认值是1GB。增大这个值可能导致crash recovery时间延长

min_wal_size

       只要磁盘使用量低于这个值，老的wal 文件会被循环使用而不是删除。可以理解为wal日志的保留磁盘大小，在这个大小之内的文件不会被删除，后续可以直接复写，那么其磁盘空间也是一直被自己占着。

6.Vacuum

       autovacuum

              默认为on，表示后台是否开启vacuum daemon线程。vacuum用于回收数据库的无用数据

       log_autovacuum_min_duration

              vacuum执行时间超过设置值，则会打印日志记录

       autovacuum_max_workers

              vacuum线程的最大数量,默认值为 3

       autovacuum_naptime

              vacuum执行间隔，默认1min

       autovacuum_vacuum_threshold

              Specifies the minimum number of updated or deleted tuples needed to trigger a VACUUM in any one table. The default is 50 tuples.（指定表级别 触发vacuum的修改操作的阈值）

       autovacuum_analyze_threshold

              Specifies the minimum number of inserted, updated or deleted tuples needed to trigger an ANALYZE in any one table. The default is 50 tuples.（指定表级别触发analyze的最小改动条数）

       autovacuum_vacuum_scale_factor

              Specifies a fraction of the table size to add to autovacuum_vacuum_threshold when deciding whether to trigger a VACUUM. The default is 0.2 (20% of table size). （触发vacuum的乘数因子）

       autovacuum_analyze_scale_factor

              Specifies a fraction of the table size to add to autovacuum_analyze_threshold when deciding whether to trigger an ANALYZE. The default is 0.1 (10% of table size).（触发vacuum analyze的乘数因子）

       触发vacuum的条件： update/delete 行数  >  autovacuum_vacuum_threshold + 表的行数 * autovacuum_vacuum_scale_factor   （对应默认值为 50+count(*) * 20%）

       触发vacuum analyze的条件：同上（对应默认值为 50+count(*) * 10%）

vacuum执行器分为 LAZY vacuum和FULL vacuum

lazy vacuum

       手动执行不带参数的vacuum或者数据库autovacuum都是使用这个，会找到dead的tuple，标记为空间可用（不进行空间合并），清理index，更新统计信息。可以和增删改查并行运行，但是不能修改表结构

full vacuum

       手动执行vacuum full。  除了lazy vacuum的功能外还进行空间合并（将数据移到连续磁盘空间，类似聚餐索引执行功能）。所以需要锁表。

       别人此时不能操作这个表。full vacuum或者alter table，或者手动cluster都需要和这个表一样大的额外磁盘空间，用于拷贝数据。

analyze

       用于收集表的信息，方便查询优化器（query planner）进行优化sql查询，

       执行方式： analyze 或者 vacuum analyze  或者 使用工具vacuumdb -z

7.Archiving（用于集群）

archive_mode

       归档事物日志到另外的一个磁盘或者文件中。

       当使能了archive_mode，完整的wal segments会被发送到archive storage（自己通过archive_command指定的一个磁盘空间/文件）。值；off，on，always。

　　一般情况下on和always一致。当设置了always，在archive recovery和standby mode两种情况下依然可以执行归档。在always模式下，所有的文件（从archive中恢复的或者流复制的流文件）会被再一次归档。

archive_command

       实际执行归档操作的命令。如果archive_command为empty，server还是会积累wal segments以期望archive_command会被尽快提供。

archive_timeout

       archive_command仅应用在完整的wal segments上。如果服务器只产生很少的wal数据，那么事物完成和它被安全的记录到归档存储之间有一个很长的延迟。archive_timeout指定切换到新的wal segment file的时间间隔（前提是有数据改动），

8.Replication（复制，用于集群）

       下面的参数控制内建的流控制特性。数据库服务实例会成为master或者slave server。master可以发送数据，slave接收master发来的复制数据。当使用级联复制时，standby server可以发送接收数据。

8.1Sending Server

       这些参数可以在任何发送复制集数据的server上设置。

max_wal_senders

       指定最大数量的并发连接数（简单理解为slave的数量）。由于time_wait的存在，可能需要设置的比实际使用值稍大。

max_replication_slots

       指定replication slots的最大数量。只有wal_level设置成replica/logical时，replication slot才有效。

wal_keep_segments

       指定pg_xlog目录保存的wal log的最小数量。每个文件一般是16M。

wal_sender_timeout

       指定复制流连接的超时时间，用于感知故障。默认是60S

track_commit_timestamp

       记录事物提交时间

Master server

       下面这些参数在master server上设置，在slave server上无效（无影响）

synchronous_standby_names

       指定同步流复制的standby server，配合synchronous_commit使用，提交事物时需要收到standby server的应答之后才能完成提交，如配置项 2(slave1,slave2,slave3,slave4) 表示提交事物需要得到两个slave的应答，优先取配置中前面的slave的应答，即这里取slave1和slave2的应答。如果slave1或者slave2某个挂了，下一个接管应答。上面的slave1，slave2...指的是slave的名字，由参数项application_name设置。如果这个参数没有配置，则不会使用同步配置

vacuum_defer_cleanup_age

       指定vacuum延迟清理的事物的数量。默认值为0，表示在其它事物中不被看到的无用行的（mvcc产生，如update会复制一条新行数据，老的行被标记为清除）可以被尽快清除。对于单机版，这个参数设置为0正常使用，但是对于集群模式，master感知到dead row只是根据本身来判断的，不能确定slave是否有用。所以这里做一个清理延迟，如设置成100，表示保留最近的100条事物产生的dead row。这个参数只能降低冲突的概率，如果需要更精确，可以使用hot_standby_feedback。

8.2Standby Servers

下面的设置控制standby server接收复制数据的行为。配置在master上无影响。

hot_standby

       指定在recovery期间（standby一直处于这个状态），是否允许连接查询

max_standby_archive_delay

       应用当前wal segments的总时间（应用时间+等待本地冲突的sql查询的时间）。当要应用的wal信息和standby server上的sql查询冲突时，需要等待一定时间之后把sql查询cancel掉。默认值是30S，设置成-1表示一直等到sql查询执行结束，这会导致wal archive信息不断增大但是得不到应用。

max_standby_streaming_delay

       参数意义同上，只是这个wal data来自流复制，上面的wal data是来自wal archive。

       场景举例：master发来的wal 信息是删除一个表，slave正在该表执行sql查询。

wal_receiver_status_interval

       指定slave向master发送replication 信息最长时间间隔，默认10S，发送的主要信息：最后一条事物日志的位置（写到os cache），最后一条flush到磁盘的事物日志，最后一条应用的事物日志。write事物日志或者flush位置改变都会触发一次发送，或者最长等到这个最大时间间隔。

hot_standby_feedback

       指定slave是否将当前执行的查询反馈给master。这能尽可能的减少由于master的cleanup record导致的slave查询取消。反馈信息发送次数为在wal_receiver_status_interval时间间隔内最多一次

wal_receiver_timeout

       复制连接的超时时间。默认值60S。用于感知master故障。

wal_retrieve_retry_interval

       指定slave在wal 数据源（streaming replication，local pg_xlog or wal archive）不可用时的重试等待时间。默认5S。

9.lsn（log sequence number）

       wal日志为了replay的有序性需要加上编号。实现的时候，是按日志的产生的顺序写入磁盘的，即使是写到磁盘缓冲区中，也是按产生的顺序一次写到日志缓冲区中，再将日志顺序写到磁盘。

　　因此采用日志在日志文件中的偏移来代替这个日志编号，可以通过日志编号迅速定位到日志。这个日志编号就叫做lsn。

       故障恢复或者重启后需要进行数据恢复，从pg_controldata中找出最后一个checkpoint检查点位置，从这个位置之后定位到wal log文件的位置，然后读取后面的wal log进行恢复。但是checkpoint之后的wal log对应操作是否在之前已经刷新到了磁盘，这个难以确定。所以在每个数据块的块头记录下最后一次修改这个数据块操作的日志编号lsn，当redo log时，将数据块加载到缓冲区中，若块头的lsn大于当前lsn，表示当前日志不需要重做（恢复），继续redo下一条日志。通过这种方法避免了同一个操作被多次执行。

10.pg_clog

       默认大小8K，每2bit 存储一个事物的提交状态，也就是一个文件可以存储32K个事物。

       pg_clog将32个事物分为一组，存储这些事物的最大LSN。存储在SlruSharedData结构中。

       在将clog buffer write磁盘前，需要确保该clog page对应事物的xlog LSN已经flush到磁盘。

11.测试工具类

       pg_test_fsync

              测试不同的同步方法的写入速率

       pg_xlogdump

              查看wal log日志信息，查看时间点

              pg_xlogdump -fb 00000001000000000000000A

       pg_controldata

       pg_rewind

              pg_rewind 工具主要实现了从源集群到目的集群的文件级别数据同步。但是和rsync的区别是，pg_rewind 不需要去读那些未变化的文件块，当数据量比较大而变化较小的时候，pg_rewind会更快。

              需要将下面两个参数打开： wal_log_hints=on， full_page_writes=on

12.pg事物隔离级别

       脏读

       不可重复读

       幻影读

       事物隔离级别：read uncommitted，read committed，repeatable read，serializable

       pg实现了后三种，第一种效果同read committed。

       read committed

              默认隔离级别。只能读到自己sql查询之前已经提交的事物（可以看到本事物内自己之前做的修改）。即前提条件：在当前查询之前已提交（不要求在自己的事物开始之前提交）

       repeatable read

              只能 看到本事物开始前已经提交的事物（可以看到本事物内自己之前做的修改）。即前提条件：在当前事物之前已提交。

       serializable

              类似序列化读写，最严格的隔离级别

13.mvcc

       select txid_current(); 　　　　　　　　　　　　　　  查询当前的事物id

       select xmin,xmax,cmin,cmax,ctid,* from test; 　　　　查询当前表的内部字段

       begin transaction isolation level read committed; 　　 在当前session中设置事物级别为读已提交

       begin transaction isolation level repeatable read;　　  在当前session中设置事物级别为重复读

       begin transaction isolation level serializable;

xmin，xmax

       xmin在创建记录时（insert tuple），记录此值为插入tuble的事物id

       xmax在更新或者删除tuple时，记录当前事物id。或者为0（无删除更新操作）

       举例：

　　　　当前事物id为1000，插入一条记录A之后，A的（xmin，xmax）为（1000，0），再起一个事物插入B 对应（1001，0）

       　　窗口1新起一个事物，事物id为1002，更新B（不提交），在本事物中查看B（1002，0），新开一个shell后台（窗口2），查看B（1001，1002），表示B是由事物1001创建的，由事物1002修改，但是还没有提交。

　　　　实际上，上面操作，窗口1（操作者）和窗口2（观察者）看到的不是同一条记录 ，pg的MVCC机制决定，更新操作会创建新的tuple.

       　　在事物1002中删除A（不提交），在本事物中查不到A的信息了，在其它窗口查看，A（1000，1002）

      　　 即xmax表示当前行待删除，由于mvcc的作用，其它事物依然能看到老的数据，update对老数据的操作等同删除。

cmin，cmax

　　同一个事物中的，该行记录对应的sql的执行的顺序，从0开始。

       举例：

　　　　在同一个事物中执行三次插入操作，产生三条记录A,B,C 则他们分别对应的（cmin，cmax）为（0，0），（1，1），（2，2）

       　　此时update B记录，那么B对应的（cmin，cmax）变为（3，3）

pg判断自身可见性原理（自身未提交事物）：

　　xmax为0，则判断xmin是否为当前事物id；

　　若xmax不为0，则判断xmax是否为当前事物id（判断是否被自身删除）

14.集群配置

一个集群是一组主机共同工作并被视为一个主机，提供了水平伸缩性，可以抵御少数节点故障。

       根据共享内容可以划分为两种集群：

              共享存储：所有节点访问相同的存储设备

              不共享：每个节点有自己的存储空间

 异步复制和同步复制：

       异步复制：事物被提交到master上之后数据才可以复制，slave不会超前master，通常滞后一些

       同步复制：事物同时再master和slave上提交

单master复制和多master复制

       单master复制：写操作只发送到一台服务器，该服务器同步数据到内部设置的slave上。slave只接收读操作

       多master复制：允许写操作发送到所有集群内部的服务器。

逻辑复制和物理复制：

       逻辑复制：复制的是实际操作的sql

       物理复制：系统将复制数据到远程服务器上，复制内容是数据的二进制格式。