DTCC2014：PostgreSQL和MySQL存储层深度解析-李海翔_IT168文库

PostgreSQL安装和简单使用

PostgreSQL安装和简单使用 PostgreSQL安装和简单使用 作者：小P 来自：https://www.sodocs.net/doc/b912127063.html, 摘要：PostgreSQL是现在比较流行的数据库之一，这个起源于伯克利（BSD）的数据库研究计划目前已经衍生成一项国际开发项目，并且有非常广泛的用户。据我了解国内四大国产数据库，其中三个都是基于PostgreSQL开发的。并且，因为许可证的灵活，任何人都可以以任何目的免费使用，修改，和分发PostgreSQL，不管是私用，商用，还是学术研究使用。本文只是简单介绍一下postgresql的安装和简单的使用，语法方面涉及的比较少，以方便新手上路为目的。目录1.系统环境及安装方法；1.1 系统环境； 1.2 安装； 2.启动PostgreSQL 数据库服务器；2.1 在流行Linux发行版的启动方法； 2.2 关于PostgreSQL启动和存储目录； 3.创建用户；3.1 添加用户; 3.1.1 不带参数的创建用户; 3.1.2 为指定的主机和端口上创建用户; 3.1.3创建超级用户; 3.2 删除用户：3.2.1 删除本地的Postgres用户;

3.2.2 删除远程Postgres服务器上的用户; 4. 创建和删除数据库；4.1创建数据库 4.2 删除数据库 5.访问数据库5.1 激活数据库 5.2 帮助和退出数据库 6. Postgresql图形化管理工具pgAdmin3 ;6.1 安装;6.1.1 Ubuntu安装; 6.1.2 其它系统的安装;6.2 pgAdmin3的简单使用;6.2.1 pgAdmin3的启动; 6.2.2 连接已创建的数据库mydb ; 7. 创建和删除表;7.1 创建新表; 7.2 数据类型; 7.3 删除表;8. 向表中添加行;8.1 INSERT; 8.2 point类型输入; 8.3 COPY;9. 查询一个表;9.1 SELECT; 9.2 WHERE; 9.3 排序;10. 视图; 11. 更新行; 12. 删除行; 13. 关于本文； 14. 更新日志； 15. 参考文档； 16. 相关文档； +++++++++++++++++++++++++++++++++++++++++++

PostgreSQL数据库使用pg_dump—psql 转储数据库

使用pg_dump/psql转储数据库 pg_dump/psql应用程序在pg安装目录的bin目录下。 只要在安装pg数据库的服务器上，且能够连通远程pg数据库，都可以实现数据导出。 一、pg_dump导出 pg_dump –h hostname –U name –p port –d database –f “file_name” -h: 数据库服务器地址 -U: 大写的U，用户名 -p: 端口号 -d: 数据库名 -f: 存储的文件路径和名称 pg_dump -h 110.84.129.40 -U postgres -p 3306 -d O2O -f "/home/wsxcde/database_file/O2O_bak_140430.dmp" 回城执行，会要求输入密码，正确，即可导出，没有进度条 远程导出300M的文件，大概时间10分钟。 以上命令是导出数据的全部对象，包括数据，对象（index，table，sequence，function等），但不包括blob的大对象，如果要导出大对象，要加上“-b”。 二、psql数据导入 psql –h localhost –U postgres –d new_db –f "xxx.dmp" -h: 数据库服务器地址，如果导入本机，直接使用localhost -U:大写的U，被导入数据库的用户名 -d: 数据库名，想导入的数据库，导入前请检查此数据库是否存在，不存在会报错 -f: 备份文件dmp的来源 psql–h localhost –U postgres –d O2O –f "/home/wsxcde/database_file/O2O_bak_140430.dmp" 300M的文件，导入时间不大约10多秒。

PostgreSQL详解

PostgreSQL数据库 一:PostgreSQL介绍 1、PostgreSQL就是以加州大学伯克利分校计算机系开发得 POSTGRES,现在已经更名为POSTGRES,版本 4、2为基础得对象关系型数据库管理系统(ORDBMS)。PostgreSQL支持大部分 SQL标准并且提供了许多其她现代特性:复杂查询、外键、触发器、视图、事务完整性、MVCC。同样,PostgreSQL 可以用许多方法扩展,比如, 通过增加新得数据类型、函数、操作符、聚集函数、索引方法、过程语言。并且,因为许可证得灵活,任何人都可以以任何目得免费使用、修改、与分发PostgreSQL,不管就是私用、商用、还就是学术研究使用。 2、PostgreSQL图标 3.PostgreSQL优点 有目前世界上最丰富得数据类型得支持支持,其中有些数据类型可以说连商业数据库都不具备,具体类型下文会说明、 PostgreSQL拥有一支非常活跃得开发队伍,而且在许多黑客得努力下,PostgreSQL 得质量日益提高 PostgreSQL 对接口得支持也就是非常丰富得,几乎支持所有类型得数据库客户端接口。这一点也可以说就是 PostgreSQL 一大优点。 4.PostgreSQL缺点 首先,早期得 PostgreSQL 继承了几乎所有 Ingres, Postgres, Postgres95 得问题:过于学院味,因为首先它得目得就是数据库研究,因此不论在稳定性, 性能还就是使用方方面面,长期以来一直没有得到重视,直到 PostgreSQL 项目开始以后,情况才越来越好,PostgreSQL 已经完全可以胜任任何中上规模范围内得应用范围得业务 其次,PostgreSQL 得确还欠缺一些比较高端得数据库管理系统需要得特性,比如数据库集群,更优良得管理工具与更加自动化得系统优化功能等提高数据库性能得机制等。 5.目前官方最新版本:9、3、2 二、windows下安装过程 1、开始安装:

PostgreSQL数据库安装教程

PostgreSQL数据库安装教程 （仅供参考） 最新更新：2014年08月27日 深圳市恩布网络科技有限公司 （内部技术文档）

目 录 1. Linux安装PostgreSQL数据库 (3) 1.1. 概述 (3) 1.2. 新建postgres用户和目录 (3) 1.3. 配置postgres用户的环境变量： (3) 1.4. yum安装 (3) 1.5. 初始化数据库目录 (3) 1.6. 给postgres赋予权限 (4) 1.7. 配置监听地址、端口 (4) 1.8. 配置支持远程连接 (4) 1.9. 设置开机启动 (4) 1.10. 启动服务 (4) 1.11. 修改postgres帐号密码 (4) 2. Windows安装PostgreSQL数据库 (5) 2.1. 概述 (5) 2.2. 安装 (5) 2.3. 配置监听地址、端口 (5) 2.4. 配置支持远程连接 (5) 3. 注意事项 (6) 4. 附录一：PostgreSQL数据库介绍 (6) 5. 附录二：PostgreSQL与MySQL比较 (6)

1.Linux安装PostgreSQL数据库 1.1.概述 以CentOS 6.4（或以上）X64（64位）版本，PostgreSQL8.4为例子说明。 本文仅供参考，如果安装不成功，或需要更多PostgreSQL技术资料，请自行上网搜索； 1.2.新建postgres用户和目录 mkdir /var/lib/pgsql groupadd postgres useradd ‐g postgres postgres 1.3.配置postgres用户的环境变量： cat >>/var/lib/pgsql/.bash_profile<

PostgreSQL安装详细步骤(windows)

PostgreSQL安装详细步骤 PostgreSQL安装： 一、windows下安装过程 安装介质：postgresql-9.5.3-1-windows-x64，安装过程非常简单，过程如下：1、开始安装：

2、选择程序安装目录： 注：安装 PostgreSQL 的分区最好是 NTFS 格式的。PostgreSQL 首要任务是要保证数据的完整性，而 FAT 和 FAT32 文件系统不能提供这样的可靠性保障，而且 FAT 文件系统缺乏安全性保障，无法保证原始数据在未经授权的情况下被更改。此外，PostgreSQL 所使用的"多分点"功能完成表空间的这一特征在FAT文件系统下无法实现。 然而，在某些系统中，只有一种 FAT 分区，这种情况下，可以正常安装PostgreSQL，但不要进行数据库的初始化工作。安装完成后，在 FAT 分区上手动执行 initdb.exe 程序即可，但不能保证其安全性和可靠性，并且建立表空间也会失败。 3、选择数据存放目录：

4、输入数据库超级用户和创建的OS用户的密码

注：数据库超级用户是一个非管理员账户，这是为了减少黑客利用在 PostgreSQL 发现的缺陷对系统造成损害，因此需要对数据库超级用户设置密码，如下图所示，安装程序自动建立的服务用户的用户名默认为 postgres。 5、设置服务监听端口，默认为5432 6、选择运行时语言环境

注：选择数据库存储区域的运行时语言环境（字符编码格式）。 在选择语言环境时，若选择"default locale"会导致安装不正确；同时，PostgreSQL 不支持 GBK 和 GB18030 作为字符集，如果选择其它四个中文字符集：中文繁体香港（Chinese[Traditional], Hong Kong S.A.R.）、中文简体新加坡（Chinese[Simplified], Singapore）、中文繁体台湾 （Chinese[Traditional], Taiwan）和中文繁体澳门（Chinese[Traditional], Marco S.A.R.），会导致查询结果和排序效果不正确。建议选择"C"，即不使用区域。 ----我选择了default localt，安装正确；建议选择default localt。 7、安装过程（2分钟）

PostgreSQL索引

什么是索引？ 索引的目的及优缺点？ PostgreSQL中的索引及分类 索引是指按表中某些关键属性或表达式建立元组的逻辑顺序，它是由一系列表元组的标识号组成的一个列表。 在关系数据库中，索引是一种与表有关的数据库结构，它可以使对应于表的SQL语句执行得更快。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。 索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。 索引的分类 按存储结构区分：“聚集索引（又称聚类索引，簇集索引）”，“分聚集索引（非聚类索引，非簇集索引）” 按数据唯一性区分：“唯一索引”，“非唯一索引” 按键列个数区分：“单列索引”，“多列索引”。 为什么要创建索引呢？这是因为，创建索引可以大大提高系统的性能。 第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。 第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。 第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。 第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。 第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。 也许会有人要问：增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？这种想法固然有其合理性，然而也有其片面性。虽然，索引有许多优点，但是，为表中的每一个列都增加索引，是非常不明智的。这是因为，增加索引也有许多不利的一个方面。 第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。 第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。 第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。 索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。一般来说，应该在这些列上创建索引，例如： 在经常需要搜索的列上，可以加快搜索的速度； 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构； 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度； 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

MySQL和PostgreSQL两数据库的对决

MySQL和PostgreSQL两数据库的对决 在这篇文章中，我们选用MySQL4.0.2-alpha与PostgreSQL7.2进行比较，因为MySQL4.0.2-alpha开始支持事务的概念，因此这样的比较对于MySQL应该较为有利。我们这样的比较不想仅仅成为一份性能测试报告，因为至少从我个人来看，对于一个数据库，稳定性和速度并不能代表一切。对于一个成熟的数据库，稳定性肯定会日益提供。而随着硬件性能的飞速提高，速度也不再是什么太大的问题。 一、前言 前一段时间，我曾经翻译过一篇将你的网站从MySQL改为PostgreSQL，其实当初我更感兴趣的是一个应用程序的后台数据库从MySQL转为PostgreSQL的具体操作，并没有关心MySQL和PostgreSQL的优劣，没想到反应出乎意料的大，因此我也就觉得有写这篇文章的必要了。 在这篇文章中，我们选用MySQL4.0.2-alpha与PostgreSQL7.2进行比较，因为MySQL4.0.2-alpha开始支持事务的概念，因此这样的比较对于MySQL应该较为有利。 我们这样的比较不想仅仅成为一份性能测试报告，因为至少从我个人来看，对于一个数据库，稳定性和速度并不能代表一切。对于一个成熟的数据库，稳定性肯定会日益提供。而随着硬件性能的飞速提高，速度也不再是什么太大的问题。 二、两者的共同优势 这两个产品都属于开放源码的一员，性能和功能都在高速地提高和增强。MySQLAB的人们和PostgreSQL的开发者们都在尽可能地把各自的数据库改得越来越好，所以对于任何商业数据库使用其中的任何一个都不能算是错误的选择。 三、两者不同的背景 MySQL的背后是一个成熟的商业公司，而PostgreSQL的背后是一个庞大的志愿开发组。这使得MySQL的开发过程更为慎重，而PostgreSQL的反应更为迅速。 这样的两种背景直接导致了各自固有的优点和缺点。 四、MySQL的主要优点 1、首先是速度，MySQL通常要比PostgreSQL快得多。MySQL自已也宣称速度是他们追求的主要目标之一，基于这个原因，MySQL在以前的文档中也曾经说过并不准备支持事务和触发器。但是在最新的文档中，我们看到MySQL4.0.2-alpha已经开始支持事务，而且在MySQL的TODO中，对触发器、约束这样的注定会降低速度的功能也列入了日程。但是，我们仍然有理由相信，

postgresql群集

注意事项：由于wordpress会自动将半角单引号转换为全角单引号，引用代码时需要手动修改为单引号 原理只是一个概念，可操作性不强，看了N多的半原理半细节的相关文章，却总是被各种各样的细节困惑住。为了自己下次能够快速的使用PostgreSQL，以流水帐的方式记录配置PostgreSQL集群的步骤，有些结论是自己猜想的或者引用的，以后发现错误再修正吧。 顺便发些感想（跳过下面一段，都是题外话，其实就是废话）： 其实很多东西，从原理上说都很简单，几乎我们每个人都能说上一点。但是实际上，原理性的文章一般可操作性都不是很强。我个人觉得，如果目标是给自己看或者目标是让团队的人能够读懂并可以操作的文章，还是应该多一些细节描述，用文学的观点来说，就是表现手法要细腻一些。 Over，转入正题： 首先解释一下数据库集群：N个数据库堆到一起，找一个当个头头，管理所有的数据库并让它们协同工作。当然了，要不要找个头头，找几个头头，如何协作等等问题这些都可以商量和约定，因此，也就形成了不同的数据库集群。 如果数据库系统是PostgreSQL，这个集群就是PostgreSQL数据库集群。PostgreSQL数据库管理集群的方法有很多，有人提出了PL/Proxy方式的集群（这才是本文的重点）。这个PL/Proxy方式的集群是这样的：有很多安装了PostgreSQl数据库的计算机，有台计算机是头头，我们把这个头头叫做proxy，其他的叫做database0，database1……。当然名字叫什么是无所谓的，关键是有个是头头，其他的受头头指挥。 看个例子吧：以三台机器的集群为例子，看看PostgreSQL集群的架构是什么。proxy节点：proxy节点实际上也是一个PostgreSQL数据库节点，但是所有数据均不存放到proxy节点上，主要做三件事情： 1.接受用户的sql查询； 2.分析用户的sql查询并转换成集群上执行的SQL语句； 3.合并集群执行sql的结果，然后返回给用户。 说白了，就是把用户的sql语句交给database0，database1去执行，然后合并执行结果返回给用户。 database1节点和 database2节点： 就是普通的数据库节点，接收proxy节点的sql查询请求并返回结果给proxy 节点，是真正存放数据的节点。 没图没真相，还是来张集群结构图，结构更清晰一些： 看了原理图，是不是觉得很简单，稍微了解技术的人都能想到这种架构。既然这么简单，那就开始创建一个数据库集群吧，需求如下：

【VIP专享】postgresql数据库 pg

postgresql数据库 pg_hba.conf配置说明 当一个客户端应用与数据库服务器进行联接时，它声明它将以哪个 PostgreSQL 用户的名称进行联接，就象我们登录一台 Unix 计算机一样。在 SQL 环境里，活跃的数据库用户名决定数据库对象的各种访问权限 — 参阅Chapter 17获 取更多信息。因此，实际上我们要限制的是用户可以联接的数据库。 认证是数据库服务器建立客户端的标识，然后通过一些手段判断是否允 许此客户端应用（或者运行这个客户端应用的用户）与它所要求的用户名进行 联接的过程。 PostgreSQL 提供多种不同的客户端认证方式。认证某个特定客户端联接所使用的方法可以通过基于（客户端）的主机地址，数据库和用户的方式进行选择；一些认证方法还允许你通过用户名进行限制。 PostgreSQL 用户名在逻辑上是和服务器运行的操作系统用户名相互独立的。如果某个服务器的所有用户在那台服务器机器上也有帐号，那么给数据库用 户赋与操作系统用户名是有意义的。不过，一个接收远程访问的服务器很有可能有许多没有本地操作系统帐号的用户，因而在这种情况下数据库用户和操作系统用户名之间不必有任何联系。 pg_hba.conf 文件 客户端认证是由一个配置文件控制的，通常其文件名是 pg_hba.conf，存放在数据库集群的数据目录里。（HBA 的意思是 host-based authentication：基于主机的认证。）在initdb初始化数据目录的时候，它 会安装一个缺省的文件。不过我们也可以把认证配置文件放在其它地方；参阅hba_file 配置参数。 文件 pg_hba.conf 的常用格式是一套记录，每行一条。空白行行被忽略，井号（ # ）开头的注释也被忽略。一条记录是由若干用空格和/或 tab 分隔 的字段组成。如果字段用引号包围，那么它可以包含空白。记录不能跨行存在。 每条记录声明一种联接类型，一个客户端 IP 地址范围（如果和联接类型 相关的话），一个数据库名，一个用户名字，以及对匹配这些参数的联接使用的认证方法。第一条匹配联接类型，客户端地址和联接企图请求的数据库名和用户名的记录将用于执行认证。这个处理过程没有"跨越"或者"回头"的说法：如果选择了一条记录而且认证失败，那么将不考虑后面的记录。如果没有匹配的记录，那么访问将被拒绝。 每条记录可以下面七种格式之一 local database user authentication-method [authentication-option] host database user CIDR-address authentication-method

Postgresql存储过程

用PL/pgSQL写postgreSQL的存储过程 一、存储过程结构： Create or replace function 过程名(参数名参数类型,…..) returns 返回值类型as $body$ //声明变量 Declare 变量名变量类型； 如： flag Boolean; 变量赋值方式（变量名类型：=值；） 如： Str text :=值; / str text; str :=值； Begin 函数体； End; $body$ Language plpgsql; 二、变量类型： 除了postgresql内置的变量类型外，常用的还有 RECORD ，表示一条记录 三、连接字符： Postgresql存储过程中的连接字符不再是“+”,而是使用“||”。 四、控制结构： 1、if 条件（五种形式） IF ... THEN IF ... THEN ... ELSE IF ... THEN ... ELSE IF IF ... THEN ... ELSIF ... THEN ... ELSE IF ... THEN ... ELSEIF ... THEN ... ELSE（注：ELSEIF 是 ELSIF 的别名）

2、循环 使用LOOP，EXIT，CONTINUE，WHILE，和 FOR 语句，可以控制PL/pgSQL 函数重复一系列命令。 1）、LOOP [ <> ] LOOP statements END LOOP [ label ]; LOOP 定义一个无条件的循环，无限循环，直到由EXIT或者RETURN语句终止。可选的label可以由 EXIT 和 CONTINUE 语句使用，用于在嵌套循环中声明应该应用于哪一层循环。 2）、EXIT EXIT [ label ] [ WHEN expression ]; 如果没有给出label，那么退出最内层的循环，然后执行跟在 END LOOP 后面的语句。如果给出label，那么它必须是当前或者更高层的嵌套循环块或者语句块的标签。然后该命名块或者循环就会终止，而控制落到对应循环/块的 END 语句后面的语句上。 如果声明了WHEN，循环退出只有在expression为真的时候才发生，否则控制会落到 EXIT 后面的语句上。 EXIT 可以用于在所有的循环类型中，它并不仅仅限制于在无条件循环中使用。在和 BEGIN 块一起使用的时候，EXIT 把控制交给块结束后的下一个语句。 例如: Loop 循环 If … then 条件判断 Exit ; 条件成立，则退出循环。 End if; End loop;

Postgresql数据库 变量类型

一、数值类型： 下面是PostgreSQL所支持的数值类型的列表和简单说明： 1. 整数类型： 类型smallint、integer和bigint存储各种范围的全部是数字的数，也就是没有小数部分的数字。试图存储超出范围以外的数值将导致一个错误。常用的类型是integer，因为它提供了在范围、存储空间和性能之间的最佳平衡。一般只有在磁盘空间紧张的时候才使用smallint。而只有在integer的范围不够的时候才使用bigint，因为前者(integer)绝对快得多。 2. 任意精度数值： 类型numeric可以存储最多1000位精度的数字并且准确地进行计算。因此非常适合用于货币金额和其它要求计算准确的数量。不过，numeric类型上的算术运算比整数类型或者浮点数类型要慢很多。 numeric字段的最大精度和最大比例都是可以配置的。要声明一个类型为numeric的字段，你可以用下面的语法： NUMERIC(precision,scale) 比如数字23.5141的精度为6，而刻度为4。 在目前的PostgreSQL版本中，decimal和numeric是等效的。 3. 浮点数类型： 数据类型real和double是不准确的、牺牲精度的数字类型。不准确意味着一些数值不能准确地转换成内部格式并且是以近似的形式存储的，因此存储后再把数据打印出来可能显示一些缺失。 4. Serial(序号)类型： serial和bigserial类型不是真正的类型，只是为在表中设置唯一标识做的概念上的便利。 CREATE TABLE tablename ( colname SERIAL

); 等价于 CREATE SEQUENCE tablename_colname_seq; CREATE TABLE tablename( colname integer DEFAULT nextval('tablename_colname_seq') NOT NULL ); 这样，我们就创建了一个整数字段并且把它的缺省数值安排为从一个序列发生器取值。应用了一个NOT NULL约束以确保空值不会被插入。在大多数情况下你可能还希望附加一个UNIQUE或者PRIMARY KEY约束避免意外地插入重复的数值，但这个不是自动发生的。因此，如果你希望一个序列字段有一个唯一约束或者一个主键，那么你现在必须声明，就像其它数据类型一样。 还需要另外说明的是，一个serial类型创建的序列在其所属字段被删除时，该序列也将被自动删除，但是其它情况下是不会被删除的。因此，如果你想用同一个序列发生器同时给几个字段提供数据，那么就应该以独立对象的方式创建该序列发生器。 二、字符类型： 下面是PostgreSQL所支持的字符类型的列表和简单说明： SQL 定义了两种基本的字符类型，varchar(n)和char(n)，这里的n是一个正整数。两种类型都可以存储最多n个字符长的字串，试图存储更长的字串到这些类型的字段里会产生一个错误，除非超出长度的字符都是空白，这种情况下该字串将被截断为最大长度。如果没有长度声明，char等于char(1)，而varchar则可以接受任何长度的字串。 MyTest=> CREATE TABLE testtable(first_col varchar(2)); CREATE TABLE MyTest=> INSERT INTO testtable VALUES('333'); --插入字符串的长度，超过其字段定义的长度，因此报错。 ERROR: value too long for type character varying(2) --插入字符串中，超出字段定义长度的部分是空格，因此可以插入，但是空白符被截断。 MyTest=> INSERT INTO testtable VALUES('33 '); INSERT 0 1 MyTest=> SELECT * FROM testtable; first_col ----------- 33

PostgreSQL数据库用户认证

PostgreSQL数据库用户认证作者：小P 来自：https://www.sodocs.net/doc/b912127063.html, 摘要：为了保证PostgreSQL数据库的安全性，我们需要对访问数据库的用户进行身份验证，本文介绍了有关于PostgreSQL数据库的用户认证的基本知识; 目录 1. pg_hba.conf 文件； 1.1 各个字段的含义; 1.2 authentication-method（认证方法）; 1.3 示例 ; 2. 认证方法; 2.1. 信任认证; 2.2. 口令认证; 2.3. Kerberos 认证; 2.4. 基于 Ident 的认证; 2.4.1. 透过 TCP/IP 的身份认证; 2.4.2. 透过本地套接字的身份认证; 2.4. 3. Ident 映射; 2.5 一个 pg_ident.conf 文件例子; 2.6 PAM 认证; 3. 认证问题; 4. 关于本文; 5. 更新日志; 6. 参考文档; 7. 相关文档; +++++++++++++++++++++++++++++++++++++++++++ 正文 +++++++++++++++++++++++++++++++++++++++++++ 当一个客户端应用与数据库服务器进行联接时，它声明它将以哪个PostgreSQL 用户的名称进行联接，就象我们登录一台Unix 计算机一样．在SQL 环境里，活跃的数据库用户名决定数据库对象的各种访问。

因此，实际上我们要限制的是用户可以联接的数据库; 认证是数据库服务器建立客户端应用的标识，然后通过一些手段判断是否允许此客户端应用（或者运行这个客户端应用的用户）与它所要求的用户名进行联接的过程; PostgreSQL 提供多种不同的客户端认证方式．认证某个特定客户端联接所使用的方法可以通过基于(客户端)的主机地址，数据库和用户的方式进行选择；一些认证方法还允许你通过用户名进行限制; PostgreSQL 用户名在逻辑上是和服务器运行的操作系统用户名相互独立的．如果某个服务器的所有用户在那台服务器机器上也有帐号，那么给数据库用户赋与操作系统用户名是有意义的．不过，一个接收远程访问的服务器很有可能有许多没有本地帐号的用户，因而在这种情况下数据库用户和操作系统用户名之间不必有任何联系; 1. pg_hba.conf 文件； 客户端认证是由数据目录里的文件pg_hba.conf 控制的，比如: /etc/postgresql/8.2/main/pg_hba.conf （hba 的意思是host-based authentication：基于主机的认证．）在initdb初始化数据目录的时候，它会安装一个缺省的文件; 文件pg_hba.conf 的常用格式是一套记录，每行一条。空白行行被忽略，井号( "#" )开头的注释也被忽略。一条记录是由若干用空格和/或tab 分隔的字段组成。如果字段用引号包围，那么它可以包含空白．记录不能夸行存在; 每条记录声明一种联接类型，一个客户端IP 地址范围(如果和联接类型相关的话)，一个数据库名，一个用户名字，以及对匹配这些参数的联接使用的认证方法．匹配联接类型，客户端地址和联接企图请求的数据库名和用户名的第一条记录将用于执行认证．这个处理过程没有"跨越"或者"回头"的说法∶如果选择了一条记录而且认证失败，那么将不考虑后面的记录．如果没有匹配的记录，那么访问将被拒绝．每条记录可以下面三种格式之一: local database user authentication-method [authentication-option] host database user IP-address IP-mask authentication-method [authentication-option] hostssl database user IP-address IP-mask authentication-method [authentication-option] 1.1 各个字段的含义; local 这条记录匹配通过Unix 域套接字进行的联接企图．没有这种类型的记录，就不允许Unix 域套接字的联接。 host 这条记录匹配通过TCP/IP 网络进行的联接尝试．请注意，除非服务器是带着-i 选项或者打开了postgresql.conf 里面的tcpip_socket 配置参数集启动的，否则TCP/IP 联接是被禁止掉的． hostssl 这条记录匹配通过在TCP/IP 上进行的SSL 联接企图．host 记录可以匹配SSL 和非SSL 的联接企图，但hostssl 记录需要SSL 联接。 要使用这个选项，制作服务器的时候必须打开SSL 支持．而且在服务器启动的时候，必须打开在postgresql.conf里的ssl选项。 database 声明记录所匹配的数据库。值all 表明该记录匹配所有数据库，值sameuser表示如果被请求的数据库和请求的用户同名，则匹配。samegroup 表示请求的用户必须是一个与数据库同名的组中的成员。在其他情况里，这就是一个特定的PostgreSQL 的名字。我们可以通过用逗号分隔的方法声明多个数据库。一个包含数据库名的文件可以通过对该文件前缀@ 来声明．该文件必需和pg_hba.conf 在同一个目录；user

PostgreSQL+PostGIS的使用

PostgreSQL+PostGIS的使用 一、PostgreSQL与PostGIS的关系 PostgreSQL 是世界上技术最先进的开源数据库，其前身是1977年一个源于Berkeley名为Ingres 的非关系型数据库，其项目领导人为Michael Stonebraker教授。1982年该教授商业化了Ingres；1985年，Michael Stonebraker教授回到Berkeley，开始对新的数据库设计进行研究，并于次年在美国防务高级研究项目局（DARPA）、陆军研究办公室（ARO）、国家科学基金（NSF）以及ESL, Inc等机构的赞助下启动了Postgres（Post-Ingres）项目。 Postgres 在1987年形成第一个Demo，1989年发布第一个版本，直到1993年的4.2版本，由于外部用户过多，做技术支持和维护源代码的时间影响到了对数据库的研究，因此Berkeley中止了该项目。在此期间，Postgres项目就已经被使用在了一些GIS系统中。 Postgres项目并未就此消亡，在1994年两个Berkeley的研究生向Postgres中加入了SQL语言解释器，将之改名为Postgre95并发布到了互联网上。经过一些黑客的修改，1996年Postgres95再次更名为PostgreSQL，并采用BSD许可证发布了第一个开源版本。经过多年发展，PostgreSQL 已经发展成为一个技术非常先进的开源数据库，其支持特性之多性能之强可与诸多高级商业数据库比肩。 这里提供了一个世界上主流数据库的特性比较，有兴趣的可以从中管窥PostgreSQL在数据库领域中的成就。 https://www.sodocs.net/doc/b912127063.html,/wiki/Comparison_of_relational_database_management_systems PostGIS则是PostgreSQL的一个扩展，目的是使PostgreSQL支持空间数据的存储和使用，其本质类似于ArcSDE和Oracle Spatial Extension。PostGIS是采用GPL许可发布的，完整地实现了OGC的《Simple Features Specification for SQL》规范，并于2006年获得OGC认证。在此基础上，PostGIS还对规范进行了一些扩展，在后面的特性中我们可以慢慢了解到。 二、PostGIS中的几何类型 PostGIS支持所有OGC规范的“Simple Features”类型，同时在此基础上扩展了对3DZ、3DM、4D坐标的支持。 1. OGC的WKB和WKT格式 OGC定义了两种描述几何对象的格式，分别是WKB（Well-Known Binary）和WKT（Well-Known Text）。 在SQL语句中，用以下的方式可以使用WKT格式定义几何对象： POINT(0 0) ——点 LINESTRING(0 0,1 1,1 2) ——线 POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1)) ——面 MULTIPOINT(0 0,1 2) ——多点

PostgreSQL的配置文件及用户权限

PostgreSQL的配置文件及用户权限 作者：小P 来自：https://www.sodocs.net/doc/b912127063.html, 摘要：为了能够远程修改和维护PostgreSQL数据库，我们会涉及到配置文件和用户权限的问题，本文主要介绍了如何配置PostgreSQL的主要配置文件，修改用户权限，使我们能够远程打开数据库; 目录 1. 修改配置文件; 1.1 postgresql.conf; 1.2 pg_hba.conf; 1.3 重新启动postgresql服务器； 2. 配置用户权限; 2.1 创建一个用户; 2.2 创建一个数据库data1,属主为test1; 2.3 创建一个数据表; 2.4 修改数据表的属主; 3. 关于本文; 4. 更新日志; 5. 参考文档; 6. 相关文档; +++++++++++++++++++++++++++++++++++++++++++ 正文 +++++++++++++++++++++++++++++++++++++++++++ 为了能远程使用postgresql数据库,比如说我要在网络上另外一台Windows电脑上使用pgAdmin3来管理使用该Postgresql服务器,您就需要进行一些修改，这些修改主要包括两方面的修改: 一是修改Postgresql的两个配置文件，二是修改数据库的一些用户权限; 1. 修改配置文件;

注：记得修改任何东西之前都需要备份一下; 1.1 postgresql.conf; 首先我们需要修改Postgresql的配置文件postgresql.conf，该文件在数据目录/etc/postgresql/8.2/main/下，终端下执行： root@xiaop-laptop:/home/xiaop# vim /etc/postgresql/8.2/main/postgresql.conf 将其中的一句： listen_address=’localhost’ 前面的注释#去掉，并把’localhost’该为’*’; 1.2 pg_hba.conf; 然后我们再来修改Postgresql的配置文件pg_hba.conf，该文件在数据目录/etc/postgresql/8.2/main/下，终端下执行 root@xiaop-laptop:/home/xiaop# vim /etc/postgresql/8.2/main/pg_hba.conf 在文件后面加一句： host all all 192.168.1.0/24 password 这句的意思是：同网络中192.168.1.*的机器可以以密码的形式使用所有的数据库。更具体的参数意义直接看该配置文件中的注释就可以了; 这里一定要配置正确，否则无法在远程连接postgresql数据库。 1.3 重新启动postgresql服务器； root@xiaop-laptop:/home/xiaop# /etc/init.d/postgresql-8.2 restart 2. 配置用户权限; 这里介绍一些数据库用户权限的简单配置，以下是在psql中使用： 2.1 创建一个用户; xiaop=# CREATE USER test1 ; CREATE ROLE 2.2 创建一个数据库data1,属主为test1;

postgresql的关键字

SQL命令 ABORT---退出当前事物 ALTER AGGREGATE -----修改一个聚集函数的定义ALTERCONVERSION-----修改一个编码装换的定义ALTER DATABASE ------修改一个数据库 ALTER DOMAIN------改变一个域的定义 ALTER FUNCTION ------修改一个函数的定义ALTER GROUP -----修改角色名或者成员关系ALTER INEX------改变一个索引的定义 ALTER LANGUAGE----修改一个过程语言的定义ALTER OPERATOR----改变一个操作符的定义 ALTER OPERATOR CLASS----修改一个操作符表的定义ALTER ROLE----修改一个数据库角色 ALTER SCHEMA---修改一个模式的定义 ALTER SE QUENCE----更改一个序列生成器的定义ALTER TABLE----修改表的定义ALTERTABLESPACE—改变一个表空间的定义ALTERTRIGGER—改变一个触发器的定义ALTERTYPE—改变一个类型的定义ALTERUSER—改变数据库角色 ANALYZE—收集与数据库有关的统计

BEGIN—开始一个事务块 CHECKPOINT—强制一个事务日志检查点 CLOSE—关闭一个游标 CLUSTER—根据一个索引对某个表集簇 COMMENT—定义或者改变一个对象的评注 COMMIT—提交当前事务 COMMITPREPARED—提交一个早先为两阶段提交准备好的事务 COPY—在表和文件之间拷贝数据CREATEAGGREGATE—定义一个新的聚集函数CREATECAST—定义一个用户定义的转换CREATECONSTRAINTTRIGGER—定义一个新的约束触发器CREATECONVERSION—定义一个新的的编码转换CREATEDATABASE—创建新数据库 CREATEDOMAIN—定义一个新域 CREATEFUNCTION—定义一个新函数 CREATEGROUP—定义一个新的用户组 CREATEINDEX—定义一个新索引CREATELANGUAGE—定义一种新的过程语言CREATEOPERATOR—定义一个新的操作符CREATEOPERATORCLASS—定义一个新的操作符表CREATEROLE—defineanewdatabaseroleCREATERULE—定义

PostgreSQL数据库培训

PostgreSQL使用常见的客户端/服务器的模式。一次PostgreSQL会话由下列相关的进程(程序)组成： 服务器进程 它管理数据库文件，接受来自客户端应用与数据库的连接，并且代表客户端在数据库上执行操作。数据库服务器程序叫postgres。 客户端应用 客户端应用可能本身就是多种多样的：它们可以是一个字符界面的工具，也可以是一个图形界面的应用，或者是一个通过访问数据库来显示网页的web 服务器，或者是一个特殊的数据库管理工具。一些客户端应用是和PostgreSQL发布一起提供的，但绝大部分是用户开发的。 PostgreSQL服务器可以处理来自客户端的多个并发连接。因此，它为每个连接启动(“forks”)一个新的进程。从这个时候开始，客户端和新服务器进程就不再经过最初的postgres进程进行通讯。因此，主服务器总是在运行，等待客户端连接，而客户端及其相关联的服务器进程则是起起停停。 一、数据库连接 例子：psql -h 192.168.100.160 -p 10000 -d bcstream_data-U stream -W Usage: psql[OPTION]... [DBNAME [USERNAME]] General options: -c, --command=COMMAND run only single command (SQL or internal) and exit -d, --dbname=DBNAME database name to connect to (default: "rdbdba")

-f, --file=FILENAME execute commands from file, then exit -l, --list list available databases, then exit -?, --help show this help, then exit Connection options: -h, --host=HOSTNAME database server host or socket directory (default: "local socket") -p, --port=PORT database server port (default: "5432") -U, --username=USERNAME database user name (default: "rdbdba") -w, --no-password never prompt for password -W, --password force password prompt (should happen automatically) 二、对象创建 1、创建用户及数据库 CREATE USER streamWITH PASSWORD '1qaz!QAZ'; CREATE DATABASE bcstream_data WITH OWNER=stream TEMPLATE=template0 ENCODING='UTF8'; 说明： ●用户与角色说明 在PG数据库中用户与角色为同一概念。区别在于：使用create role语句创建角色时没有login权限，需单独赋权 ●配置远程连接访问： postgresql默认情况下，不能远程访问不能，如果需允许远程访问，需修改两个配置文件，说明如下：