Postgresql学习笔记

Postgresql源码安装：

从http://www.postgresql.org/download/下载源码v9.4.5编译安装

./configure
make
su
make install  //安装完成
adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data  //创建存储数据库的postgresql的数据目录
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &  //单用户访问
/usr/local/pgsql/bin/createdb test  //创建数据库test
/usr/local/pgsql/bin/psql test  //访问数据库test

 

元数据命令：

 

 

 

4.6.1  Postgres的数据安全

4.6.1.1、ACID

指数据库事务正确执行的四个基本要素的缩写。包含：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。一个支持事务（Transaction）的数据库，必需要具有这四种特性，否则在事务过程（Transaction processing）当中无法保证数据的正确性，交易过程极可能达不到交易方的要求。

1、原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。 

2、一致性（Consistency）
事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

3、隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

4、持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

4.6.1.2 事务系统

为了保证数据操作的ACID特性在数据库中引入事务，在PostgreSQL系统中，通过事务快俩实现数据库内部底层事务和终端命令的交互，一个事务块包含多个事务（PostgreSQL中的事务表示与命令相对应的事务）。

1、  事务块的操作

在PostgreSQL中，任何语句的执行都是通过事务块的入口函数进入并执行的，执行完毕后，通过事务块的出口函数退出，当系统执行中遇到错误时，会通过事务块出错处理函数完成相关的出错处理。用户也可以通过BEGIN/END/ROLLBACK等命令来改变事务块的状态，PostgreSQL针对每一个能改变事务块状态的命令提供了单独的函数。

StartTransactionCommand在每条语句执行前调用，它的作用是通过判断当前事务块的状态进入不同的底层事务执行函数中，它是进入事务处理模块的入口函数。在每条语句执行后调用CommitTransactionCommand，同StartTransactionCommand一样根据当前事务块的状态决定下一步的事务操作。AbortCurrentTransaction则在系统遇到错误时调用，也是根据当前事务块的状态决定下一步的事务操作，调用时可能进行事务退出或者事务清理操作。

事务块状态改变函数主要包括BeginTransactionBlock、EndTransactionBlock和UserAbortTransactionBlock，他们的功能主要是根据用户输入的改变事务状态的命令以及系统本省的状态来改变事务块的状态。执行BEGIN命令，调用函数BeginTransactionBlock完状态改变，系统会进入一个事务块。执行END命令，会调用EndTransactionBlock函数，该函数指示一个事务块的结束。函数UserAbortTransactionBlock用于执行用户提交的一个ROLLBACK命令。这三个函数都只是改变事务块状态，并没有做实际的事务操作。

 

1、 两阶段提交

PostgreSQL使用两阶段提交来支持分布式数据库的事务处理，PostgreSQL只是支持分布式数据库中的两阶段提交协议，即给其提供了相关的操作接口，但没有实现整个协议，两阶段的整个流程是由编程者在应用程序中保证的。

1）、预提交阶段

　　a、对于分布式事务，某一个事务所在的数据库管理系统会选择成为“协调者”。协调者在本地开始一个分布式事务，并且向其他数据库管理系统发送“Prepare”消息。发送消息时，会使用专门的事务ID（GID）来标识此分布式事务。这样数据库管理系统之间可以确定需要同步执行的事务。

　　b、其他数据库管理系统收到“Prepare”消息后，会试图开始一个本地事务以完成分布式事务的功能。它自行巨鼎这个事务是提交还是终止，然后把它的决定发送该协调者。

　　c、如果数据库决定提交上述的一个本地事务，它就进入“预提交”阶段。在此阶段，如果协调者没有发送终止的消息，它不能终止这个本地事务。

　　d、如果数据库决定终止这个事务，他会向协调者发送取消的信息。然后由协调者进行全局性的取消动作。

         在预提交阶段，各个分布式数据库系统将检查当前各自事务的情况，试图保证这个本地事务如果执行，就不会被终止。即使因系统出错而需要进行系统恢复，与这个事务相关的恢复操作也是应该Redo而不是Undo。

2）全局提交阶段

       a、如果协调者没有收到消息，就默认收到了“取消”消息。如果其他数据库返回给协调者的消息都是“Ready”，协调者将提交这个分布式事务，然后把“Commit”消息发送给其他数据库。如果协调者收到一条“取消”消息，则取消这个分布式事务，然后发送消息进行全局性的取消动作。

　　b、本地数据库根据协调者的消息，对本地事务进行COMMIT或者ABORT操作，在这个阶段，各个分布式数据库系统将进行事务的实质提交或者退出操作。

3、PostgreSQL的并发控制

         PostgreSQl利用多版本并发控制（MVCC）来维护数据的一致性，每个事务看到的都只是一段时间之前的数据快照。这样，如果对每个数据库会话进行事务隔离，就可以避免一个事务看到其他并发事务的更新而导致不一致的数据。因为MVCC并不能解决所有的并发控制情况，所以还需要使用传统数据库中的锁机制来保证事务的并发。另外，PostgreSQL还提供了会话锁机制，利用它可以扩大锁的使用范围，即一次对某个对象加锁可以保证对于多个事务都有效。

SQL标准考虑了三个必须在并行事务之间避免的现象：

　　1）、脏读： 指一个事务读取了另外一个事务未提交的数据。

　　　　这是非常危险的，假设Ａ向Ｂ转帐１００元，对应sql语句如下所示：

　　　　1.updateaccount set money=money+100 while name=‘b’;   

　　　　2.updateaccount set money=money-100 while name=‘a’;

　　　　当第1条sql执行完，第2条还没执行(A未提交时)，如果此时Ｂ查询自己的帐户，就会发现自己多了100元钱。如果A等B走后再回滚，B就会损失100元。

　　2）、不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。

　　　　例如银行想查询A帐户余额，第一次查询A帐户为200元，此时A向帐户存了100元并提交了，银行接着又进行了一次查询，此时A帐户为300元了。银行两次查询不一致，可能就会很困惑，不知道哪次查询是准的。

 　　　　和脏读的区别是，脏读是读取前一事务未提交的脏数据，不可重复读是重新读取了前一事务已提交的数据。

　　3）、幻读：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

 　　　　如丙存款100元未提交，这时银行做报表统计account表中所有用户的总额为500元，然后丙提交了，这时银行再统计发现帐户为600元了，造成虚读同样会使银行不知所措，到底以哪个为准。

为了避免出现这三种现象，SQL标准定义了4个事务隔离级别：

　　1）、读未提交（Read Uncommitted）：SELECT语句以非锁定方式被执行，所以有可能读到脏数据，隔离级别最低。

　　2）、读已提交（Read Committed）：postgresql里的缺省隔离级别。只能读取到已经提交的数据。即解决了脏读，但未解决不可重复读。

　　3）、可重复读（Repeated Read）：在同一个事务内的查询都是事务开始时刻一致的，InnoDB的默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻读。
　　4）、可串行化（Serializable）：它提供最严格的事务隔离。完全的串行化读，所有SELECT语句都被隐式的转换成SELECT ... LOCK IN SHARE MODE，即读取使用表级共享锁，读写相互都会阻塞。隔离级别最高。

隔离级别	脏读	不可重复读	幻读
读未提交	可能	可能	可能
读已提交	不可能	可能	可能
可重复读	不可能	不可能	可能
可串行读	不可能	不可能	不可能

4.1事务隔离级别表

 

一、PostgreSQL中的三种锁：

PostgreSQL实现并发控制的基本方法是使用锁来控制临界区互斥访问。后台进程对磁盘文件进行访问操作时，首先要获取锁。如果成功获得目标锁，则进入临界区执行磁盘读写访问，访问完成后退出临界区并释放锁；否则，进程睡眠直到被别的后台进程唤醒后重试。

PostgreSQL中定义了三种锁，分别是SpinLock、LWLock和RegularLock。

1）、SpinLock是最底层的锁，使用互斥信号量实现，与操作系统和硬件环境联系紧密，分为与机器相关的实现方法和与机器不相干的实现方法。SpinLock的特点是：封锁时间很短，没有等待队列和死锁检测机制，事务结束时不能自动释放SpinLock。

2）、LWLock（轻量级锁）主要提供对共享存储器的数据结构的互斥访问。LWLock有两种锁模式，一种为排他模式，另一种为共享模式。轻量级锁不提供死锁检测，单轻量级锁管理器在elog恢复期间自动释放，所以持有轻量级锁的期间调用elog发出错误消息不会出现轻量级锁未释放的问题。

LWLock利用SpinLock实现，当没有锁的竞争时，可以很快获得或释放LWLock。当一个进程阻塞在一个轻量级锁上时，相当于它阻塞在一个信号量上，所以不会消耗GPU时间，等待的进程将会以先来后到的顺序被授予锁。

LWLock定义了一个互斥量mutex，使用SpinLock对此互斥量加/解锁即可实现对一个LWLock的互斥访问。

3）、RegularLock是一般数据库事务管理中所指的锁，简称Lock。RegularLock由LWLock实现，其特点是：有等待队列，有死锁检测，能自动释放锁。

二、死锁处理机制：

在PostgreSQL中，事务可以按照任意顺序申请锁，所以必须考虑死锁的处理机制。PostgreSQL对于死锁的预防分为两步：

1）、当进程请求加锁时，如果失败，会进入等待队列。如果在队列中已经存在一些进程要求本进程中已持有的锁，那么为了尽量避免死锁，可以简单地把本进程插入到他们的前面。

2）、当一个锁被释放时，将会试图唤醒等待队列中的进程。如果其中某个进程的要求与排在它前面但由于某些原因不能被唤醒的进程冲突，这个进程将不被唤醒。

PostgreSQL使用等待图（WFG）来进行死锁检测，如下图：

 

图4.2死锁检测图

PostgreSQL死锁检测与消除算法如下：

a、       从每一个顶点开始出发，沿WFG中的有向边走，看能不能回到此顶点。如果找到一条路径，则说明出现死锁。

b、       如果此路径中没有soft Edge（A与B都在某个等待队列，A要求的锁与B要求的锁冲突），直接终止这个事务。

c、       否则记录所有出现的soft Edge。对于这个集合，递归地枚举它的所有子集，尝试进行调整。

d、       对于上述每个子集，使用拓扑排序排出一个调整方案，并逐一加以测试，如果找不到一个可以消除死锁的方案，则死锁消除失败，终止这个事务。

三、多版本并发控制

PostgreSQL利用多版本并发控制（MVCC）来维护数据的一致性。这就意味着当检索数据时，每个事务看到的只是一段时间之前的数据快照，而不是数据的当前状态。使用多版本并发控制的主要优点是对检索（读取）数据的锁请求与写数据的锁请求并不冲突，所以读不会阻塞写，而写也不会阻塞读。这就极大地提高了并发处理能力。

在PostgreSQL中，更新数据时并不是用新值覆盖旧值，而是在表中另开辟一片空间来存放新的元组，让新值与旧值同时存放在数据库中，通过设置一些参数，让系统识别它们。一个新元组被保存在磁盘中的时候，其t_ctid就被初始化为它自己的实际存储位置。如果这个元组被更新了，该元组的t_ctid字段就指向更新后的新元组。

快照记录了数据库当前某个时刻的活跃事务列表。通过快照，可以确定某个元组的版本对于当前快照是否可见。其中，不同的快照用于不同形式的可见性判断，需要注意的是如果当前事务的隔离级别是可串行化，那么只存在CurrentSnapshotData，不存在SecondarySnapshot Data，而在读已提交的隔离级别，这两种快照都存在。

4.6.2  Postgres数据库备份

备份 PostgreSQL 数据有三种完全不同的方法：

1、SQL 转储

2、文件系统级别的备份

3、在线备份

4.6.2.1  SQL 转储

这种转储方法是创建一个文本文件， 里面都是SQL命令，当把这个文件回馈给服务器时， 将重建与转储时状态一样的数据库。PostgreSQL为这个用途提供了pg_dump工具。 这条命令的基本用法是： pg_dump dbname > outfile正如你所见，pg_dump把结果输出到标准输出。

pg_dump是一个普通的PostgreSQL客户端应用(尽管是个相当聪明的东西)。 这就意味着你可以从任何可以访问该数据库的远端主机上面进行备份工作。 但是请记住pg_dump不会以任何特殊权限运行。 具体说来，就是它必须要有你想备份的表的读权限， 因此，实际上你几乎总是要成为数据库超级用户。

要声明pg_dump应该以哪个用户身份进行连接， 使用命令行选项 -h host和-p port 。缺省主机是本地主机或环境变量PGHOST声明的值。类似的，缺省端口是环境变量PGPORT或(如果它不存在的话)编译好了的缺省值。服务器通常都有相同的缺省，所以还算方便。和任何其它PostgreSQL客户端应用一样， pg_dump缺省时用与当前操作系统用户名同名的数据库用户名进行连接。 要覆盖这个名字，要么声明-U选项，要么设置环境变量PGUSER。请注意pg_dump的连接也和普通客户应用一样要通过客户认证机制(在章Chapter 19里描述)。

pg_dump跟别的备份方法相比较最重要的一个优点会在后面介绍， pg_dump的输出一般可以被重新加载到PostgreSQL的新版本， 而文件级备份和连续归档都是特定于服务器版本的。pg_dump是把数据库移到另一个机器架构上的唯一方法，就像从32位到64位的服务器。

由pg_dump创建的备份在内部是一致的，也就是说， 在pg_dump运行的时候对数据库进行一次快照。 pg_dump工作的时候并不阻塞其它对数据库的操作 (但是会阻塞那些需要排它锁的操作，比如ALTER TABLE)。

Important: 如果你的数据库结构依赖于OID(比如说用做外键)， 那么你必须告诉pg_dump把OID也导出来。要导出OID， 可以使用-o命令行选项。

一些操作系统的文件是有最大上限的，就会在创建大的pg_dump输出文件时 出现问题。幸运的是，因为pg_dump输出到标准输出，你可以用标准的Unix工具绕开这个问题： 如使用压缩的转储，使用你熟悉的压缩程序(比如 gzip):

pg_dump dbname | gzip > filename.gz

用下面命令恢复: gunzip -c filename.gz | psql dbname

或者: cat filename.gz | gunzip | psql dbname

使用 split 工具。split命令允许用下面的方法把输出分解成操作系统可以接受的大小。 比如，让每个块大小为 1MB ：

pg_dump dbname | split -b 1m – filename

用下面命令恢复： cat filename* | psql dbname

使用pg_dump自定义的转储格式. 如果PostgreSQL是在一个安装了zlib压缩库的系统上制作的， 那么自定义转储格式将在写入输出文件的时候压缩数据。 它会生成和使用gzip类似大小的转储文件， 但是还附加了一个优点：你可以有选择地恢复库中的表。 下面的命令用自定义转储格式转储一个数据库：

pg_dump -Fc dbname > filename自定义格式的转储不是脚本，不能用于psql ，而是需要使用pg_restore转储。例如：

pg_restore -d dbname filename对于每一个大型数据库来说，你可能需要把split和另外两个方法之一来结合使用。

4.6.2.2  文件系统级别的备份

文件系统级别的备份直接拷贝PostgreSQL用于存放数据库数据的文件。 Section 17.2里解释了这些文件的位置， 不过如果你想用这个方法，你应该早就找到它们的位置了。 你可以用任何自己喜欢的方法备份文件系统，例如:

tar -cf backup.tar /usr/local/pgsql/data

不过，这要受到两个限制，令这个方法不那么实用， 或者至少比pg_dump的方法逊色一些：

1、为了进行有效的备份，数据库服务器必须被关闭。 像拒绝所有连接这样的折衷的方法是不行的，(一部分是因为tar和类似的工具不能对文件系统的状态做原子快照， 但也会是因为服务器的内部缓冲问题) 有关关闭服务器的信息可以在Section 17.5里面找到。 不用说，你在恢复数据之前，同样必须关闭服务器。

2、如果你曾经深入了解了数据库在文件系统布局的细节， 你可能试图从对应的文件或目录里备份几个表或者数据库。 这样做是没用的，因为如果不提交日志文件包含在这些文件里的信息是不可用的， pg_clog/*，它包含所有事务的提交状态。 只有拥有这些信息，表文件的信息才是可用的。 当然，试图只恢复表和相关的pg_clog数据也是徒劳的， 因为这样会把数据库集群里的所有其它没有用的表的信息都拿出来。 所以文件系统的备份只适用于完整备份和一个数据库集群的完整恢复。

另外一个文件系统备份的方法是给数据目录做一个"一致的快照"， 条件是文件系统支持这个功能(并且你愿意相信它是实现正确的)。 典型的过程是制作一个包含数据库的卷的"冻结快照"， 然后把整个数据库目录(不仅仅是部分，见上文)从快照拷贝到备份设备， 然后释放冻结快照。这样甚至在数据库服务器在运行的时候都可以运转。 不过，这样创建的备份会把数据库文件保存在一个没有恰当关闭数据库服务器的状态下； 因此，如果你在这个备份目录下启动数据库服务器， 它就会认为数据库服务器经历过崩溃并且重放WAL日志。 这不是个问题，只要意识到它即可(并且确信在自己的备份中包含WAL文件)。

如果你的数据库分布在多个文件系统上，那么可能就没有任何方法获取所有卷上准确的同步冻结快照。 比如，你的数据文件和WAL日志在不同的磁盘上,或者表空间在不同的文件系统上， 这种情况下就不可能使用快照，因为快照必须是同时的。 在你信任这样的情况下的一致性快照的技术之前，仔细阅读你的文件系统文档。

如果同步快照是不可能的，一个选择就是关闭数据库足够长的时间以便建立所有的冰冻快照。 另一个选择是执行连续归档基础备份(Section 24.3.2)这样的备份可以避免在 备份时对文件系统产生变更。这需要在备份时启用连续归档；用连续存档恢复存储。

另外一个选择是使用rsync执行一次文件系统备份。 这是通过在数据库服务器正在运行的时候运行第一次rsync， 然后关闭数据库服务器一段足够的时间长度，用于运行第二次rsync。 第二次rsync会比第一次快很多，因为它要传输的数据相对较少， 并且最后的结果是一致的，因为服务器已经停止运行了。 这个方法允许用很少的时间执行一次文件系统备份。

还要说明的是，文件系统备份通常会比SQL转储大。 （比如pg_dump不用转储内容索引，只是创建它们的命令。） 然而，让一个文件系统备份可能会快点。

4.6.2.3  在线备份

在任何时候，PostgreSQL都在集群的数据目录的pg_xlog/子目录里维护着一套预写日志(WAL)。 这些日志记录着每一次对数据库的修改细节。 这些日志存在是为了防止崩溃： 如果系统崩溃，数据库可以通过"重放"上次检查点以来的日志记录以恢复数据库的完整性。 但是，日志的存在让它还可以用于第三种备份数据库的策略： 我们可以组合文件系统备份与WAL文件的备份。 如果需要恢复，我们就恢复文件系统备份，然后重放备份了的WAL文件， 把系统恢复到当前的状态。这个方法对管理员来说，明显比以前的方法更复杂， 但是有非常明显的优势：

在开始的时候我们不需要一个非常完美的一致的文件系统备份。 任何备份内部的不一致都会被日志重放动作修改正确(这个和崩溃恢复时发生的事情没什么区别)。 因此我们不需要文件系统快照的功能，只需要tar或者类似的归档工具。

因为我们可以把无限长的WAL文件序列连接起来， 所以连续的备份简化为连续地对WAL文件归档来实现。 这个功能对大数据库特别有用，因为大数据库的全备份可能并不方便。

没必要重放WAL记录的时候我们必须重放到结尾。 我们可以在任意点停止重放，这样就有一个在任意时间的数据库一致的快照。 因此，这个技术支持即时恢复： 我们可以把数据库恢复到你开始备份以来的任意时刻的状态。

如果我们持续把WAL文件序列填充给其它装载了同样的基础备份文件的机器， 我们就有了一套热备份系统：在任何点我们都可以启动第二台机器， 而它拥有近乎当前的数据库拷贝。

Note: pg_dump和pg_dumpall不产生 文件系统级别的备份，而且不能作为在线备份解决方案的一部分。像logical这样的转储， 在重现WAL时不会包含足够的信息。

和简单的文件系统备份技术一样，这个方法只能支持整个数据库集群的恢复， 而不是一个子集。同样，它还要求大量的归档存储：基础备份量可能很大， 而且忙碌的系统将生成许多兆需要备份的的WAL流量。 但是，它仍然时在需要高可靠性的场合下的最好的备份技术。

要想从在线备份中成功恢复（也叫"在线备份"），你需要一套连续的WAL归档文件， 它们最远回朔到你开始备份的时刻。因此，要想开始备份， 你应该在开始第一次基础备份之前根据我们讨论过的归档WAL文件机制设置并测试你的步骤。

 

Greenplum的数据安全：ACID、备份机制—张建龙

Dataturbines：备份机制

系统的数据安全

 

和其他分系统的关系
     和存储系统的关系
     和应用系统的关系
     在数据生命周期中的角色和关系  找写数据生命周期的人讨论
   内在结构
        组建间的结构关系
       内部数据流图
       内部调用关系和调用场景设计