细致-专业-实操

主库正常运行中，备库可以随意切换为主库，此时变成两个主库，数据无法同步，此时如果这两个库都发生了各自数据变化，然后又想将一台变成备库，则需要在指定为备库的数据库上进行同步操作，然后再变成备库。备库变主库的时候，这个standby.signal 建议拷贝到一个地方，以后如果又变成备库，将其拷贝回来。上面的配置和讲解都是异步同步，对于主库的性能影响是最小的，但是会丢失数据，可以将其配置成实时同步。如果设置了多个备库，并且进行实时同步，可以通过FIRST，设置前几个库同步完成即可。重启数据库，查看后台进程。

PostgreSQL既使用自身的缓冲区，也使用内核缓冲IO。这意味着数据会在内存中存储两次，首先是存入PostgreSQL缓冲区，然后是内核缓冲区。这被称为双重缓冲区处理。对大多数操作系统来说，这个参数是最有效的用于调优的参数。此参数的作用是设置PostgreSQL中用于缓存的专用内存量。shared_buffers的默认值设置得非常低，因为某些机器和操作系统不支持使用更高的值。但在大多数现代设备中，通常需要增大此参数的值才能获得最佳性能。

pg_rman是一款专门为postgresql设计的在线备份恢复的工具。其支持在线（热备份）和基于时间点备份方式。它是一个开源的PostgreSQL备份软件,使用的是pg_start_backup(), copy, pg_stop_backup()的备份模式,因为是文件拷贝模式，所以pg_rman必须在数据库节点上运行。

如果没有开启WAL归档，备份依然会进行，但是备份结束后会有提示信息，大概意思是备份了，但是WAL日志需要确保用其他方式将必需的WAL日志复制到归档目录。精确的停止点受recovery_target_inclusive影响。recovery_target_name(string)：由pg_create_restore_point()创建的还原点，用于恢复到指定还原点。recovery_target_lsn：恢复到WAL的指定LSN，精确的停止点也受 recovery_target_inclusive的影响。

当一个事务开始时，PostgreSQL中的事务管理系统会为该事务分配一个唯一标识符,即事务ID(txid).PostgreSQL中的txid被定义为一个32位的无符号整数，也就是说，它能记录大约42亿个事务。通常txid对我们是透明的，但是我们可以利用PostgreSQL内部的函数来获取当前事务的txid（oracle是scn）。请注意，BEGIN命令没有指定txid。在PostgreSQL中，当第一个命令在BEGIN命令执行之后执行时，事务管理器会分配一个tixd，然后它的事务开始。

Autovacuum是启动PostgreSQL时自动启动的后台实用程序进程之一，每隔一分钟执行一次在生产系统中不应该将其设置为关闭。

Vacuum处理对数据库中的指定表或所有表执行以下任务：1、移除死元组删除死元组并对每个页面的活元组进行碎片整理。删除指向死元组的索引元组。2、冷冻老的Txid必要时冻结老元组的Txid更新冻结的与系统目录（pg_database和pg_class）相关的txid如有可能，移除clog中不必要的部分3、其他更新已处理表的FSM和VM。更新几个统计数据（pg_stat_all_tables等）。相关术语：死元组、冻结txid、FSM和clog。从指定的表中获取每个表。

执行第一个更新命令时，通过将txid 100设置为t_xmax，逻辑上删除Tuple_1，然后插入Tuple_2。与delete操作一样，如果提交了txid 100，Tuple 1和Tuple 2将是死元组，如果中止(rollback)了txid 100，Tuple 2和Tuple 3将是死元组。当执行第二个UPDATE命令时，与第一个UPDATE命令一样，Tuple_2在逻辑上被删除，Tuple_3被插入。Tuple_1 t_max有值 是100，则它通常就是无效的了，被修改了。本节只描述通常的元组。

在数据文件（堆表和索引，以及可用空间映射和可见性映射）中，它被划分为固定长度的页（或块），默认值为8192字节（8kb）。每个文件中的那些页从0开始按顺序编号，这些编号称为块编号。行指针形成一个简单的数组，它起到元组索引的作用。当一个新的行被添加到页面中时，一个新的行指针也被推到数组中以指向新的行。仔细查看数据库子目录，您会发现每个表都有两个相关的文件，分别以‘_fsm’和‘_vm’作为后缀。它们被称为空闲空间地图和可见性地图，分别存储表文件中每页的自由空间容量和可见性信息。结构的主要变量如下页所述。

每次进行物理备份时都会发生一个检查点，用来判断将来进行恢复时的起始位置，因为备份时数据文件是有先后顺序，备份出来的数据文件是不一致的，将来恢复出来后需要应用归档日志把他们变成同步，开始备份的位置就是将来recovery的位置。检查点发生时，检查点进程会把共享缓冲区中的脏块（被修改过的块）写入磁盘，永久保存，否则如果发生主机断电等故障，内存中的数据块就会丢失，该检查点的发生频率由checkpoint_timeout控制，定期发生。准确说，恢复的起始位置，是检查点当中的Redo point。

类似于Oracle表空间热备，当把表空间变成备份状态时，由于备份采用的是操作系统cp命令，在备份时数据块还允许修改，会导致备份的数据块前后状态不一致，为了解决整个问题，备份期间修改的数据块会整个块的写入redolog文件中，此时会导致redolog膨胀。块是数据库最小的I/O单位，当数据库写一个数据块时，操作系统需要I/O多次，可能在I/O过程中系统断电、磁盘故障等等原因导致一个数据块没有完整的写入，导致块不一致。操作系统进行I/O操作时，总是以块为单位，比如512字节、1KB等等。

让我们来看看WAL机制的概述。为了澄清WAL一直在研究的问题，第一小节展示了如果PostgreSQL没有实现WAL，当崩溃发生时会发生什么。第二部分介绍了一些关键概念，并对本章的主要内容、WAL写数据和数据库恢复处理进行了概述。最后一小节完成了WAL的概述，增加了一个关键概念。

postgresql为“大字段“的物理存储提供了TOAST功能，超大尺寸属性存储技术，通过合适的配置策略能够减少IO次数和扫描块数，进而提升查询速度。TOAST采用最高的两个二进制位用于标识压缩与行外存储，因此“大字段“的逻辑长度被限制在了1GB。在pg_multixact/offsets下面，找到最大的文件编号，+1 后面跟上4个0。-l 定位办法：pg_wal下面创建的最大的日志文件，编号+1。在pg_xact下面，找到最大的文件编号，+1 后面跟上5个0。4、数据库备份时会一起备份。

在PostgreSQL中，数据库本身也是数据库对象，在逻辑上彼此分离。每个数据库存储的对象（表、索引、视图等等）是独立的、私有的，每个数据库类似于每个房间，从房间中取东西，就需要到房间里面；同理，要访问某个数据库中的对象，就需要登录到指定的数据库中。所以如果要取消某个用户对指定数据库连接的权限，需要先取消public的连接权限，再取消该用户的连接权限。PostgreSQL是参数设置分为实例级、数据库级、用户级和会话级，而有些参数可以在所有级别中设置，优先级顺序为会话级>用户级>数据库级>实例级。

• 实例访问控制就像是一道防火墙，用它来控制来自于不同主机、不同用户是否允许访问指定的数据库、以及验证方式。

• 每个数据库对象都有一个所有者，默认情况下，所有者拥有该对象的所有权限• 在数据库中所有的权限都和角色挂钩• 对超级用户postgres不做权限检查，其它用户走ACL(Access ControL List)• 对于数据库对象，开始只有所有者和超级用户可以做任何操作，其它走ACL例如zhangsan,scott 这些普通用户可以修改属主对于自身对象的权限，这个oracle数据库不能操作。

用户对象的集合叫做模式。比如：scott用户下有表emp、dept、salgrade等，还有几个索引，还有一些约束等等，那么scott用户下所有对象的集合就叫scott模式。- 可以把用户下拥有的对象根据业务分类，不同的对象存放在不同的模式下。- 新建的数据库默认会创建不同的模式来管理对象。比如：information_schema、pg_catalog、pg_temp_1、pg_toast、pg_toast_temp_1、public 等。

用户与角色数据库用户用来访问、管理数据库中的对象（表、索引…)数据库角色用来管理数据库访问权限，简化权限的管理用户和角色在整个数据库集簇中是全局性的，不是针对某个单一数据库，只要有足够的权限，用户可以访问所有数据库的对象。超级用户–postgres普通用户–根据需要创建用户与角色的区别用户管理数据库对象：表、视图、索引角色：简化权限的管理可以简单理解为用户=角色oracle当中，新建用户没有任何权限，pg当中新建用户默认可以登录数据库，创建对象（表、索引…）

PostgreSQL中的所有数据库对象都由各自的对象标识符（oid）在内 部管理。例如，当数据库sampledb的OID为16384时，其子目录名为16384。每个数据库是base子目录下的子目录；数据库目录名与相应的oid相同。当没有指定表空间的时候，默认就是在base下。表名->所属的数据库->所在表空间(目录)表空间：可以将数据放在不同的磁盘中。默认是放在base目录下。• 数据库集簇逻辑结构。

类似oracle的buffer cache + shared pool。Backend process: 目前只支持专有连接，不支持共享连接。注意：可选参数文件，需要在静态参数文件中指定后，才能用到。: 记录事务状态，例如提交回滚之类的状态。• 用来访问PostgreSQL数据库。oracle dbwr 同步脏块的进程。连接到一个PostgreSQL实例。: 类似 redo buffer。• 一个实例对应一个数据库集簇。可以存在多个初始化参数文件。• 由内存和后台进程组成。• 建立一个用户连接。

pg sql： 标准SQL的简写，别名sql: 标准sql登录到数据库后查看帮助通过帮助几乎可以查看到所有需要用到的命令，非常方便help\q to quit执行pgsql命令（类似sql的别名,简称）\l执行标准sql命令。

不同大版之前分区改进pg9使用继承式分区，pg10实现了声明式分区，pg11完善了功能，pg12提升了性能不同版本在分区表上的性能改进版本新增特性pg91.继承式分区2.手动添加触发器或规则pg101.声明式分区2.分区索引手动创建，不能基于分区父表创建pg111.新增哈希分区2.基于的分区表创建索引3.支持update分区4.会创建一个默认default分区5.分区支持创建主键，外键，索引，触发器pg121.新增哈希分区。

将postgresql的目录权限全部赋予给postgres用户。执行命令：vim /etc/init.d/postgresql。切换到postgresql源码包的解压目录下；执行命令：vim pg_hba.conf。清除缓存：yum clean all。生成缓存：yum makecache。使用\password进行设置密码。