postgresql 订阅
PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统(ORDBMS),是以加州大学计算机系开发的POSTGRES,4.2版本为基础的对象关系型数据库管理系统。POSTGRES的许多领先概念只是在比较迟的时候才出现在商业网站数据库中。PostgreSQL支持大部分的SQL标准并且提供了很多其他现代特性,如复杂查询、外键、触发器、视图、事务完整性、多版本并发控制等。同样,PostgreSQL也可以用许多方法扩展,例如通过增加新的数据类型、函数、操作符、聚集函数、索引方法、过程语言等。另外,因为许可证的灵活,任何人都可以以任何目的免费使用、修改和分发PostgreSQL。 [1] 展开全文
PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统(ORDBMS),是以加州大学计算机系开发的POSTGRES,4.2版本为基础的对象关系型数据库管理系统。POSTGRES的许多领先概念只是在比较迟的时候才出现在商业网站数据库中。PostgreSQL支持大部分的SQL标准并且提供了很多其他现代特性,如复杂查询、外键、触发器、视图、事务完整性、多版本并发控制等。同样,PostgreSQL也可以用许多方法扩展,例如通过增加新的数据类型、函数、操作符、聚集函数、索引方法、过程语言等。另外,因为许可证的灵活,任何人都可以以任何目的免费使用、修改和分发PostgreSQL。 [1]
信息
外文名
PostgreSQL
领    域
计算机技术
开发机构
加州大学伯克利分校
中文名
PostgreSQL
功    能
数据管理
特    性
复杂查询、外键、触发器
PostgreSQL简述
PostgreSQL是一个功能非常强大的、源代码开放的客户/服务器关系型数据库管理系统(RDBMS)。PostgreSQL最初设想于1986年,当时被叫做Berkley Postgres Project。该项目一直到1994年都处于演进和修改中,直到开发人员Andrew Yu和Jolly Chen在Postgres中添加了一个SQL(Structured Query Language,结构化查询语言)翻译程序,该版本叫做Postgres95,在开放源代码社区发放。1996年,再次对Postgres95做了较大的改动,并将其作为PostgresSQL6.0版发布。该版本的Postgres提高了后端的速度,包括增强型SQL92标准以及重要的后端特性(包括子选择、默认值、约束和触发器)。PostgreSQL是一个非常健壮的软件包,有很多在大型商业RDBMS中所具有的特性,包括事务、子选择、触发器、视图、外键引用完整性和复杂锁定功能。另一方面,PostgreSQL也缺少商业数据库中某些可用的特性,如用户定义的类型、继承性和规则。从用户的角度来讲,PostgreSQL惟一不具备的主要特性就是外部连接,在今后的版本中会将其加入。PostgreSQL提供了两种可选模式。一种模式保证如果操作系统或硬件崩溃,则数据将保存到磁盘中,这种模式通常比大多数商业数据库要慢,这是因为它使用了刷新(或同步)方法;另一种模式与第一种不同,它不提供数据保证,但它通常比商业数据库运行得快。遗憾的是,还没有一种折中的模式:既提供一定程度的数据安全性,又有较快的执行速度。今后的版本将会提供这种模式。 [2] 
收起全文
精华内容
参与话题
问答
  • PostgreSQL入门篇

    万次阅读 2018-05-22 21:47:02
    PostgreSQL 鉴于网上PostgreSQL的资料看的让人头疼,所以想象中,这是一篇比较全面的PostgreSQL教程。我会尽量去写的详细全面易读易懂一点,也只能是尽力而为。为了方便,在后面的书写中,我会用pgsql表示...

    PostgreSQL

    鉴于网上PostgreSQL的资料看的让人头疼,所以想象中,这是一篇比较全面的PostgreSQL教程。我会尽量去写的详细全面易读易懂一点,也只能是尽力而为。为了方便,在后面的书写中,我会用pgsql表示PostgreSQL,理解一下打字困难户,能偷懒就偷个懒啦。
    5b037e031c9f2
    我写的内容都来自pgsql的官网,英语好的同学不妨去做做阅读理解。英语不好的同学,下面这个是给你们准备的。
    5b037ecd30e37
    本篇的环境是Window7 + PostgreSQL 10。


    入门

    架构基础

    行话:pgsql使用了客户端/服务器模式。一个pgsql会话包含包含两个进程:
    服务器进程。管理数据库文件,接收客户端程序的连接请求,代表客户端执行数据库操作。数据库服务器程序叫做postgres。客户端应用程序。进行数据库操作。客户端应用程序本质上可以非常多样化,一个客户端可以是一个面向文本的工具,一个图形界面应用程序,一个通过访问数据库来显示网页的web服务器,或者一个专门的数据库维护工具。有些是pgsql自带的,大部分是用户开发的。
    客户端和服务器可以在不同的主机上,通过TCP/IP连接通信。记住这一点很重要,因为客户机上的文件再服务器上可能是访问不到的,或者只能换个文件名访问。
    pgsql服务器可以处理多并发的连接,它会为每个连接fork一个新的进程。客户端和新的服务器进程的通信与就服务器进程没有关系。主服务器进程会一直保持运行,等待客户端连接。

    创建数据库

    创建数据库可以检查你是否连接到了服务器。一个pgsql服务器可以管理很多数据库。
    创建一个名为mydb的数据库:
    $ createdb mydb
    如果回车之后没有任何输出,说明创建数据库成功。你可以跳过本节了。
    5b0389b049c68
    万一不幸出错了,这里给出了一些解决办法。
    1. 找不到命令,错误如下:
    createdb: command not found
    说明pgsql的bin目录没有添加到环境变量。你可以选择将bin目录添加到环境变量,或者去bin目录下打开命令窗口,再或者使用绝对路径调用createdb命令。
    $ /user/local/pgsql/bin/createdb mydb
    建议添加还是添加环境变量,一劳永逸。
    2. 服务未启动,错误如下:
    createdb: could not connect to database postgres: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

    表示服务器未启动,至少是在createdb执行的时候还未启动。多试试吧,我也不知道该咋办。
    3. 没有账户,错误如下:
    createdb: could not connect to database postgres: FATAL: role "joe" does not exist
    这里joe是操作系统账户,pgsql用户账户与操作系统账户不同。你需要登录Postgres账户,创建一个用户账户。也可能是你被分配的pgsql账户名与操作系统用户名不同,这时,你可以使用-U选项指定账户或设置PGUSER环境变量来指定pgsql的用户名。
    $ createdb -U user mydb
    其中,user就是你的pgsql账户名。
    4. 权限不足,错误如下:
    createdb: database creation failed: ERROR: permission denied to create database
    这是你的账户没有创建数据库的权限,你可以登录管理员账户进行修改。
    pgsql允许创建任意数量的数据库。数据库名称必须以英文字母开头,且不超过63字节。
    可以省略数据库名,创建与当前用户同名的数据库,如下:
    $ createdb
    删除数据库用dropdb命令:
    $ dropdb mydb
    dropdb命令必须指定数据库名称,即使是与用户名同名的数据库也必须指定,它不会默认用账户名。它会删除与数据库相关联的所有文件,并且不能撤销,所以一定要三思而行。

    访问数据库

    有三种方式可以访问数据库:
    pgsql终端,psql命令行工具;pgAdmin或支持ODBC或JDBC的office套件;编程语言API。
    这里只介绍第一种方式,第二种方式不做介绍,第三种方式后面再说。
    打开cmd(或PowerShell)命令行窗口,希望你之前已经设置过环境 变量了。输入一下命令启动psql
    $ psql mydb
    mydb就是之前创建的数据库。如果不指定数据库名称,默认会访问与当前账户名同名的数据库。
    回车后输入密码,你会得到如下输出:
    ​x psql (10.4)Type "help" for help.​mydb=>
    mydb=>就是命令提示符,你可以输入SQL指令(以;结尾)或psql命令(以\开头)。
    如果命令提示符是mydb=#,表示当前账户是超级用户。
    你可以输入以下几个命令试试:
    mydb=> SELECT version();
    mydb=> SELECT current_date;
    mydb=> SELECT 2 + 2;
    上面三个命令都是SQL命令,它们都以;结尾。
    psql内部命令都以\开头。例如\h命令可以获取帮助,\q命令用来退出psql。
    mydb=> \h
    mydb=> \q

    psql与cmd

    pgsql安装后会默认安装两个工具,pgAdmin和SQL Shell(psql)。前者是图形界面工具,后者是命令行工具。在打开psql之后首先需要登录账户,然后才能使用。
    1. 双击打开psql。
    这里写图片描述
    2. 按照提示一次输入服务器地址(本地使用127.0.0.1),数据库名字,端口号(默认5432),账户名,密码。
    这里写图片描述
    3. 回车之后就可以登录到你的数据库,注意,输入密码是不会有任何显示的。
    这里写图片描述
    现在你可以输入SQL命令或psql命令来操作数据库了。pgsql也提供了许多命令可以通过cmd运行,比如前面讲的createdb命令。pgsql安装默认不会将bin目录写到环境变量,因此为了使用方便,我们可以自行这样做。在cmd中运行psql同样简单,只需要打开cmd然后输入相应的命令即可,前提是你已经设置了环境变量。本节所讲操作就是在cmd中进行的。
    这里写图片描述
    前面以说过,-U选项用来指定pgsql账户,psql命令启动psql。输入密码后即可进入psql。这与直接使用pgsql提供的SQL Shell效果是一样的。
    这里写图片描述
    好了,入门就入到这里了。下一期是SQL语言的简介。

    展开全文
  • PostgreSql简介

    万次阅读 2019-04-22 14:47:42
    关于PostgreSQL: 1.什么是PostgreSqlPostgreSQL是一个功能强大的开源对象关系型数据库系统,他使用和扩展了SQL语言,并结合了许多安全存储和扩展最复杂数据工作负载的功能。PostgreSQL的起源可以追溯到1986年...

    关于PostgreSQL:

    1.什么是PostgreSql:

    PostgreSQL是一个功能强大的开源对象关系型数据库系统,他使用和扩展了SQL语言,并结合了许多安全存储和扩展最复杂数据工作负载的功能。PostgreSQL的起源可以追溯到1986年,作为加州大学伯克利分校POSTGRES项目的一部分,并且在核心平台上进行了30多年的积极开发。

    PostgresSQL凭借其经过验证的架构,可靠性,数据完整性,强大的功能集,可扩展性以及软件背后的开源社区的奉献精神赢得了良好的声誉,以始终如一地提供高性能和创新的解决方案。PostgreSQL在所有主要操作系统开始使用PostgreSQL从未如此简单。

     

    2.为什么要使用PostgreSQL

    PostgreSql提供了许多功能,旨在帮助开发人员构建应用程序,管理员保护数据完整性并且构建容错环境,并帮助你管理数据,无论数据集的大小。除了免费和开源之外,Postgre SQL还具有高度的可扩展性。例如,你可以定义自己的数据类型,构建自定义SQL函数(此sql函数在另一篇帖子讨论:点此跳转),甚至可以编写来自不同编程语言的代码,而不需要重新编译数据库。

    PostgreSql试图符合SQL标准,在这种标准中,这种一致性不会与传统特性相矛盾,或者可能导致糟糕的架构决策。支持SQL标准所需的许多功能,但是有时候语法或者功能略有不同。随着时间的推移,可以预期进一步向一致性迈进。从2018年10月发布的11版本开始,PostgreSQL符合SQL:2011核心一致性的179个强制性功能中的至少160个,在此之前,没有任何关系型数据库符合此标准的完全符合。

    3.下面是PostgreSQL中的各种功能介绍:

    1. 数据类型:
      1. 基本类型:Integer, Numeric, String, Boolean
      2. 结构类型:Date/Time, Array, Range, UUID
      3. 文档类型:JSON/JSONB, XML, Key-value(Hstore)
      4. 几何类型:Point, Line, Circle, Polygon
      5. 自定义类型:Composite, Custom Types

     

    1. 数据的完整性
      1. 唯一性,不为空
      2. 主键
      3. 外键
      4. 排除约束
      5. 显式锁定,咨询锁定

     

    1. 并发性,性能
      1. 索引:
      2. 高级索引
      3. 复杂的查询计划期/优化器
      4. 交互
      5. 多版本并发控制(MVCC)
      6. 读取查询的并行化和构建B树索引
      7. 表分区
      8. Sql标准中定义的所有事物隔离级别,包括Serializable
      9. 即时表达式汇编(JIT)

     

    1. 可靠性,灾难恢复
      1. 预写日志(WAL)
      2. 复制:异步,同步,逻辑
      3. 时间点恢复(pitr),主动备用
      4. 表空间

     

    1. 安全性
      1. 身份验证:GSSAPI, SSPI, LDAP, SCRAM-SHA-256, 证书等
      2. 强大的访问控制系统
      3. 列和行级安全性

     

    1. 可扩展性
      1. 存储的功能和程序
      2. 程序语言:PL/PGSQL, Perl, Python (more)
      3. 外部数据包装器:使用标准SQL接口连接到其他数据库或流
      4. 许多提供附加功能的扩展,包括PostGIS

     

    1. 国际化,文本搜索
      1. 支持国际字符集,例如通过ICU校对
      2. 全文检索

     

    1. 对比Mysql:
      1. PostgreSQL的稳定性极强,Innodb等引擎在崩溃、断电之类的灾难场景下抗打击能力有了长足的进步,然而很多Mysql用户都遇到过Server级的数据库丢失的场景---Mysql系统库是MyISAM的,相较而言,PG数据库在这方面要好一些。
      2. 任何系统都有他的性能极限,在高并发读写,负载逼近极限下,PG的性能指标仍然可以维持双曲线甚至对数曲线,到顶峰之后不再下降,而MySQL明细出现一个波峰后下滑。
      3. PG多年在GIS领域处于优势地位,因为它有丰富的几何类型,实际上不止几何类型,PG中有大量的字典、数组、bitmap等数据类型,相比之下MaySQL就差很多,insagram就是因为PG的空间数据库扩展POSTGIS远远强于MySQL的my spatial而采用PGSQL的。
      4. PG的“无锁定”特性非常突出,甚至包括vacuum这样的整理数据空间的操作,这个和PGSSQL的MVCC实现有关系。
      5. PG的可以使用函数和条件索引,这使得PG数据库的调优非常灵活,mysql就没有这个功能,条件索引在web应用中很重要。
      6. PG有极其强悍的SQL编程能力,有丰富的统计函数和统计语法支持,比如分析函数(Oracle的叫法,PG里面叫Window函数),还可以用多种语言来写存储过程,对于R的支持也很好。这一点上MySQL就差的很远,很多分析功能那个都没有,腾讯内部数据存储主要是Mysql,但是主要的数据分析就是Hadoop+PGsql。
      7. PG的有很多中集群架构可以选择,plproxy可以支持语句级的镜像或者分片,slony可以进行字段级的同步设置,standby可以构建WAL文件级或者流式的读写分离集群,同步频率和集群策略调整方便,操作非常简单。
      8. 一般关系型数据库的字符串有限定长度8k左右,无限长Text类型的功能受限,只能作为外部大数据访问。而PG的TEXT类型可以直接方法,SQL语法内置正则表达式,可以索引,还可以全文检索,或使用xml xpath。用PG的话,文档数据库就可以省略了
      9. 对于web应用来说,复制的特性很重要,Mysql到现在也是异步复制,pgsql可以做到同步,异步,半同步复制。还有mysql的同步是基于binlog复制,类似oracle golden gate, 是基于stream的复制,做到同步很困难,这种方式更加适合异地复制,pgsql的复制基于wal,可以做到同步复制。同时pgsql还提供stream复制。
      10. Pgsql对于numa架构的支持要比mysql强一些,比mysql对于读的性能要好些,pgsql提交可以完全异步,而mysql的内存表不够实用(表锁原因)
    展开全文
  • Postgresql使用及维护

    千人学习 2018-12-24 13:58:32
    基于开源Postgresql V9x V10版本,系统梳理技术知识,面向PG广大爱好者和使用者提供PG技术分享系列视频,共同学习交流。 通过本课程,可以熟悉PG的使用及日常维护,包括安装部署、数据库及对象管理、角色权限、备份...
  • PostgreSQL是以加州大学伯克利分校计算机系开发的POSTGRES,现在已经更名为PostgreSQL. PostgreSQL支持大部分SQL标准并且提供了许多其它现代特性:复杂查询、外键、触发器、视图、事务完整性等。   PostgreSQL...
  • postgresql 中文学习手册

    热门讨论 2013-11-20 15:12:38
    PostgreSQL学习手册(数据表) PostgreSQL学习手册(模式Schema) PostgreSQL学习手册(表的继承和分区) PostgreSQL学习手册(常用数据类型) PostgreSQL学习手册(函数和操作符<一>) PostgreSQL学习手册(函数和操作符<二>) ...
  • PostgreSQL是以加州大学伯克利分校计算机系开发的POSTGRES,现在已经更名为PostgreSQL. PostgreSQL支持大部分SQL标准并且提供了许多其它现代特性:复杂查询、外键、触发器、视图、事务完整性等。   PostgreSQL...
  • PostgreSQL 优势

    千次阅读 2019-09-29 15:19:23
    PostgreSQL 是一个自由的对象-关系数据库服务器(数据库管理系统),功能很强大。包括了可以说是目前世界上最丰富的数据类型的支持,比如 IP 类型和几何类型等等。 发现很多读者都问过这样一个问题:如果打算为项目...

    PostgreSQL 是一个自由的对象-关系数据库服务器(数据库管理系统),功能很强大。包括了可以说是目前世界上最丰富的数据类型的支持,比如 IP 类型和几何类型等等。

    发现很多读者都问过这样一个问题:如果打算为项目选择一款免费、开源的数据库,那么你可能会在MySQL与PostgreSQL之间犹豫不定。针对这个问题,我们采访到了即将在Postgres中国用户2016大会上作分享的平安科技数据库技术部总监 汪洋,请他从实践者的角度给出一些经验分享和建议。

    InfoQ:对于金融领域的应用来说,使用 PostgreSQL 与使用 MySQL 相比,优势有哪些?

    汪洋:这个问题的本质回归到为什么平安科技会选择 PostgreSQL 来做为 Oracle 的替代品。

    我们并不是没有使用过 MySQL,事实上,从2013年底我们就开始引入和使用 MySQL 数据库,比 PostgreSQL 还早了一年多。但最后,经过评估和实践的检验,发现 PostgreSQL 对于大部分的金融应用场景更加适合,因此还是选择了 PostgreSQL 数据库做为科技的主流开源数据库。

    这里面包含有多方面的因素。支付场景是金融领域里较为受关注的部分。平安集团是一家综合性金融服务企业,业务覆盖了包括保险、基金、投资、证券等。保险业务就是最明显的例子,业务逻辑极其复杂,即使是 OLTP 系统,也涉及对大量数据的访问和计算。因此,很多业务逻辑都是在数据库内部通过 package 或者 stored procedure 来实现的,这种处理方式对于 PostgreSQL 来说游刃有余。

    我认为 MySQL 属于 Thin Database,而 Oracle 和 PostgreSQL 属于 Thick Database。Thin Database 的概念在于轻数据库重应用。换句话说,就是数据库只做为数据的存储,只提供简单的查询访问。而复杂的业务逻辑前移到应用服务器端来完成。MySQL 数据库自身的特性并不十分丰富,例如 innoDB 存储引擎只提供索引组织表形式的数据存储格式,某种程度上限制了它的使用场景。对于触发器和存储过程的支持较弱,并不建议使用。应用的 CRUD 操作尽量通过主键进行,虽然支持二级索引,但通过二级索引操作会有性能损失。在进行关系型数据库中必不可少的表关联操作时,只支持 Nested Loops 关联方法,缺少对  sort merge join 和 hash join 的支持。当关联表超过2张时,MySQL 的优化器有时生成的执行计划不优,造成性能下降。

    也正因为如此,我们制定了针对 MySQL 的开发规范,例如表的大小不要超过多大,尽量书写简单查询,通过主键来访问数据,不要写超过2张表相互关联的 SQL 等。MySQL 更加适合业务逻辑简单的 OLTP 应用。而对于 PostgreSQL 来说,无论业务逻辑简单还是复杂,OLTP 还是 OLAP 负载,PostgreSQL 都可以支持,也都有很成熟的产品,很多著名的 OLAP 数据库产品例如 Greenplum、AWS 的 Redshift 等都是基于 PostgreSQL 开发的。PostgreSQL 的查询优化器非常强大,而且对于三种表关联方法 Nested Loops,Sort-Merge Join 和 Hash Join 全部支持,对于复杂的 SQL 语句优化起来也不是问题,而这正是保险行业进行业务逻辑处理需要的特性。

    PostgreSQL 还提供了强大的数据库内部 function 支持,而且可以用多种语言编写,对于复杂业务逻辑计算以及大数据量访问完全可以在数据库本地化实现,大大减少了网络交互成本,从而整体提升应用性能。

    因此,也有人说 PostgreSQL 不止是一个数据库,更是一个强大的开发平台,足见其功能之丰富。

    我认为,PostgreSQL 的进程体系结构与 MySQL 的线程体系结构的不同点在于更加适合于垂直扩展。现在大家都在谈分布式架构,横向扩展,share nothing,我们不妨微观上来看一下进程和线程架构的不同。以进程为主的体系结构,在操作系统内部来看,就是一个分布式架构,每个进程有自己的地址空间,share nothing,只在需要交互的时候通过 IPC 机制 shared memory 和 semaphore 来进行通讯,大多数情况下都是在处理自己的事务,互不干涉;而以线程为主的体系结构,共享地址空间,相当于 share everything,需要处理好线程间的关系以及对资源的访问控制。因此,创建进程的成本虽然相比创建线程高,但却容易扩展。对于平安如此体量的企业,系统负载非常大,虽然横向扩展非常重要,但垂直扩展能力也是不容置疑需要考虑的因素。而且 PostgreSQL 和 MySQL 的横向扩展能力都需要中间层或者代理层来实现,并不是依靠数据库内核完成,在这一点上两者并没有区别,至少现在如此。这样数据库内核的垂直扩展能力更加重要。如果说到横向扩展技术,MySQL 有 Fabric 和 TDDL,PostgreSQL 近年在分布式上发展也非常之快,有 Postgres-XL,pg_shard,Postgres-XZ 等产品。

    还有重要的一点。在谈到 TCO(Total Cost of Ownership)的时候,大家会觉得 PostgreSQL 和 MySQL 两者都是开源产品,并没有差别。但实际上,TCO 除了可能的 License 成本之外,还包括学习成本和运维成本。对于金融领域来讲,过去使用的都是 Oracle、DB2 等商业数据库。之前 Oracle 和 DB2 这些商业化数据库发展了几十年,数据库自身的特性非常丰富,和 PostgreSQL 都属于 Thick Database,也难怪 PostgreSQL 被称为开源领域的 Oracle。大家可以想一下,对于用惯了商业化数据库的开发人员还是 DBA 来讲,哪一种数据库对于他们来讲更加熟悉一些?学习成本更低?多年的经验更加容易转化,复用?运维成本更低?我想答案是显而易见的,这也是为什么平安选择 PostgreSQL 的一个重要考量因素。

    InfoQ:当数据量很庞大的时候,平安科技是怎么利用 PostgreSQL 实现异地备份的?

    汪洋:平安对于系统的高可用和数据安全是极其重视的。我们结合了多年积累的数据库运维经验,为 PostgreSQL 建立了 MAA(Maximum Availability Architecture)架构。我理解这里的备份应该是广义的备份概念,而不单指的是磁带备份。我们对于 PostgreSQL 的磁带备份是在本地的主库上进行的。由于不支持直接上带,所以先把备份写入到 WOS(Web Object Scaler)分布式存储上,然后再由 WOS 上带。

    由于在主库备份会与应用争抢 IO 资源,最初也评估过在本地从库备份,但我们采用的 pg_rman 工具还是需要在主库发起 begin backup 和 end backup 命令,考虑到网络影响,加之当前 IO 资源也比较充足,所以备份暂时还是在主库上进行,后续会考虑将备份操作迁移到从库。异地的数据库备份是通过搭建远程容灾库实现的,使用 PostgreSQL 自身的 Streaming Replication 技术以异步方式传输并应用 WAL 日志来保持本地主库和远程从库之间的数据同步。

    这种情况下,主从之间的差异取决于日志量产生的速度,与数据库大小关系不大。当前,平安科技最大的 PostgreSQL cluster 是 3TB,每分钟产生的 WAL 在 80MB 左右,在这样的负载下,本地主库基本上和远程从库保持准同步的关系。

    InfoQ:使用开源的 PostgreSQL 数据库,平安科技在保证系统稳定、性能方面做了哪些优化措施?

    汪洋:确实,PostgreSQL 是一个开源数据库,我们在引入的时候对于系统的稳定性也尤其关注和重视。加之平安是一个综合金融服务集团,业务范围涵盖金融行业的方方面面,各个业务线之间也有复杂的相互调用关系。如果一个系统出现问题,经常会有“牵一发而动全身”的影响。不仅可能会造成经济损失,也会影响声誉,所以在系统高可用、可靠性和稳定性上面我们做了大量工作。

    最初我们采用的一主两从的高可用架构。本地一主一从,远程一从,大家都使用本地磁盘。这样,当主数据库发生问题,可以快速切换到本地从库继续提供服务。可很快我们发现,这样在故障切换时有可能会发生数据丢失!对于某些金融应用来讲,数据丢失是不能接受的。由于考虑到主从之间的网络延迟会导致 OLTP 应用性能下降,主从之间并没有采用同步复制(尽管PostgreSQL支持,这也是优于MySQL的地方)。

    由于主从之间的强一致性没有保证,所以切换时可能会发生数据库丢失,影响 RPO。同时,使用本地盘使得计算和存储强耦合。数据显示,系统出现问题更多的是 CPU 和 RAM 而不是存储。这样,即使不是存储问题,CPU 或者 RAM 的故障也会引发切换导致数据丢失。因此我们对架构进行了优化,一方面从使用本地盘切换为使用共享盘,另一方面为 PostgreSQL 建立了主备集群。这样,在服务器发生故障时,集群可以快速将 Postgres 实例从主机切换到备机,并且因为使用的是共享盘,数据的一致性和完整性得到保障,不会有丝毫的数据丢失。

    从性能上来看,共享盘也提供了比本地盘更好的 IO 性能,无论从 IOPS 上还是吞吐量上。在每一次的架构优化调整时,不止从理论上论证其可行性,也都是经过严格的测试验证后才会实施。这点也体现在 Linux IO 调度器的选择上。Linux 内核在 IO 调度上有四种策略,分别为 NOOP,CFQ(Complete Fairness Queueing),Deadline 和 Anticipatory。我们分别针对机械磁盘和 SSD 进行了 IO 的基准测试,在测试中模拟了数据库读写负载 IO 的行为,包括随机读、随机写、顺序读、顺序写的不同组合,还包括对不同 IO 大小的测试,在各种组合下观察监控不同 IO 调度策略下的性能数据。根据性能数据制定不同 IO 调度器设置规范。基于机械磁盘,CFQ 和 Deadline 表现优于其他两种策略,但 CFQ 和 Deadline 之间差别不大,为了保持更大的适应性,选择了 CFQ;而对于 SSD 闪存,经过测试 NOOP 和 Deadline 要由于 CFQ 和 Anticipatory,而且 Linux 7 内核已经缺省使用 Deadline,因此在 SSD 上我们采用 Deadline。

    对于开源数据库来讲,监控自然也是必不可少的。我们所有的数据库都采用统一的监控框架 Zabbix,极大降低了运维成本。根据 PostgreSQL 自己的特性,结合了多年 Oracle 运维的经验,定制了很多监控指标。例如对慢查询的监控,对于剩余事务年龄的监控用于判断是否需要及早进行 vacuum,以及对 checkpoint 效率的监控等。为了对每一个 cluster 的性能有一个全面的了解,方便进行性能趋势分析和定位问题发生的异常时段,我们借鉴了 Oracle 中非常优秀的 AWR(Automatic Workload Repository)概念,自己开发了 PGAWR 数据库性能报告工具。里面不仅有 OS 的性能数据,也有 DB 整体的负载情况和性能数据,还有按照不同维度排序的 SQL 语句列表,可以对 DB 极其运行主机的情况一目了然,快速诊断问题和发现异常 SQL 语句。

    种种,都是为了能够让 PostgreSQL 运行地更稳定,在问题发生时能够快速定位、分析并加以解决,满足金融行业对系统运行和恢复时效的需要。

    InfoQ:能不能介绍一下平安科技 PostgreSQL 开源数据库的架构设计,以及有哪些规范?

    汪洋:从高可用架构上来看,对于 PostgreSQL 数据库分为两个层面,首先是主机故障,其次是存储故障。之前提到,大部分的问题是由主机故障导致,存储故障只占很少一部分。由于主机和故障发生机率的不对等性,我们进行了计算和存储的解耦。为了防范主机故障,我们采用共享存储,并且建立 Linux 服务器集群在主机发生问题时快速切换。由于计算和存储解耦,也更加方便对主机进行主动维护,大大降低了数据丢失的可能性。虽然存储故障发生机率小,但影响面却很大,所以我们也没有忽视对存储故障的防护。在本地和远程我们都建立了 PostgreSQL 的从库,它们和主库之间都采用异步复制策略。如果主库存储发生问题,短时间无法恢复,就会切到本地从库继续提供服务;如果发生地区性问题,则切换到远程继续提供服务。实现两地三中心的防护。

    除了高可用架构规范,在 PostgreSQL 架构规范中我们还定义了:

    1. 生产环境所采用的 PostgreSQL 版本号,以及 PostgreSQL 的 Patch 策略和升级策略;
    2. 服务器和操作系统用户的命名规范;
    3. 数据库应用使用的字符集设置;
    4. 每一个 cluster 的卷和目录命名规则;
    5. PostgreSQL 数据库的标准参数设置规范。

    此外我们还制定了用户架构规范,在规范中,定义了 PostgreSQL cluster 都应该有哪些标准的初始化用户,例如 DBA 用户,部署专用用户,复制用户,监控用户等。对于每一个 Database,创建属主用户以及应用连接用户,以最小化权限原则授予应用连接用户必需的权限,规避进行危害性操作的风险。对于运营人员,在规范中也有明确的定义,按照职责划分创建不同的专属用户。这样,实现 DBA、运营和开发人员的权限分离,一方面最小化权限将对数据库的可能危害降到最低,另一方面便于后续的审计和追踪。

    对于数据库系统的健康、稳定运行,开发规范一样重要。在给开发人员制定的开发规范中,我们分别从命名规范和设计规范两大方面进行了描述。命名规范包括对表、字段、索引、视图等数据库对象的命名要求,而设计规范包括对表、索引、分区表在设计时的一些原则的描述。通过这些规范要求,让开发人员可以相对简单地进行应用系统的设计,降低系统运行时的性能风险。而且,我们还将这些规范集成到了数据库自动化审计平台中,例如在版本提交阶段自动审核数据库版本是否合规,SQL 语句是否包含全表扫描,隐式转换等高风险操作等,进一步加强了质量控制,降低了系统上线后的风险,简化了运维工作。

    InfoQ:在后期运维过程中对架构、规范进行了怎样的优化,引入了哪些新技术?目的是什么?

    汪洋:PostgreSQL 毕竟在平安引入的时间不长,到今天也差不多1年半的时间。虽然,我们制定了 PostgreSQL 的推广策略,按照系统复杂度、系统重要程度分层级逐步推广,但在推广使用的过程中,还是会遇到这样那样的一些问题。对此,我们要求每一个问题都要找到根本原因,只有这样才能有的放矢地不断调整和优化。

    之前提到的从最初的使用主从架构和本地磁盘调整到使用 Linux 服务器集群加共享存储就是一个明显的例子。在这里,我想再分享一个例子。有一次,在运行的一个数据库上,发现产生的 WAL 日志非常之快,数据库本身并不大,只有 100GB,但每小时产生的 WAL 却可达到 300GB 之多。这是一个典型的小库高负载的案例,通过详细分析发现,虽然库本身负载很高,但也和未经优化的 checkpoint 相关参数配置相关。负载高和 checkpoint 的配置形成了恶性循环,导致 WAL 生成量巨大,造成系统磁盘空间告急此类可用性风险。虽然调整过一次参数,但因为未找到根本原因,并没有从根本上解决问题。随着越来越多高负载的重要数据库使用 PostgreSQL,对于以后继续推广存在较大隐患,必须找到根本原因,彻底解决问题。经过对 WAL 生成原理进行研究,对日志进行分析,甚至对 PostgreSQL 源码进行理解,终于对 WAL 生成机制有了较为清晰的了解。并针对性地制定了参数调整策略和未来使用新版本时的规范。问题主要和两个参数相关,一个是 wal segment size,一个是 checkpoint_segments。缺省值设置较低,wal_segment_size 是 16MB,checkpoint_segments 是 32,不太适合运行金融系统的高负载应用。由于 wal segment size 只能在 PostgreSQL 软件编译时调整,所以对于现有系统,只能调整 checkpoint_segments。对于这个特定的数据库,第一次在没有找到根本原因的情况下将 checkpoint_segments 调整到了 128,但没有从根本上解决问题。在分析后,调整到了 512,WAL 生成量大幅减少,达到了预期的效果。在未来使用新的 PostgreSQL 版本时,我们调整了规范,在编译时将 wal segment size 调整为 64MB(缺省的4倍),并且根据系统的负载情况灵活调整从9.5开始引入的参数 max_wal_size,使得系统可以更加稳定地运行金融系统应用。

    另外,随着 PostgreSQL 对 JSON 的支持越来越丰富,越来越成熟,我们也开始在 PostgreSQL 上推荐使用 JSON 数据类型。特别要提到的是,在关系型数据库领域,PostgreSQL 的 JSON 支持要比 MySQL 和 Oracle 都早,MySQL 从5.7开始支持 JSON,Oracle 是从 12cR1 将对 JSON 的支持 backport 到了 11.2.0.4,PostgreSQL 则走在了前面。和 Document DB 领域中原生支持 JSON 数据类型的佼佼者 MongoDB 比较,经过测试验证,我们发现 PostgreSQL 的性能并不差。而 MongoDB 由于属于 NoSQL,并不支持表(或者说 collection)之间的关联,PostgreSQL 恰好填补这方面的空缺。对于一些有 JSON 文档存储需求,但又需要和关系型或者结构化数据关联的应用场景 PostgreSQL 无疑是非常适合的一款数据库。

    当然,我们对于 PostgreSQL 还在不断地学习,不断地探索。但随着对它的认识越来越深入,我们的信心也在不断增强。特别是前不久 PostgreSQL 9.6 的发布,里面的很多新特性更是让人期待,也让大家看到了 PostgreSQL 的未来!

    InfoQ:通过 PostgreSQL+金融的完美结合这一案例,你有哪些心得感想可以和 InfoQ 的读者分享的?

    汪洋:2015年可以称之为平安的 PostgreSQL 元年,从2014年底开始接触 PostgreSQL,到2015年初开始立项准备、调研、评估、测试、制定规范、制定推广策略,到真正的使用推广,到现在有超过1000个 PostgreSQL 的实例在运行,一路走来,既为取得的成绩而高兴,同时又感觉殊为不易。但今天我可以告诉大家,当初的选择是正确的,我很庆幸选择了 PostgreSQL 这样一款优秀的数据库产品,并且在团队的努力下能够让 PostgreSQL 在平安扎根,发芽并且茁壮成长。过程中,不是没有人质疑过,但我们相信自己的判断和选择,更重要的是我们相信 PostgreSQL 产品本身能够帮助我们实现支撑金融系统应用的稳定运行,降低数据库运营成本,拥抱开源技术,达到信息自主可控的目标。于是,我们耐心地说服,全力地配合,悉心地指导,以获得大家的认同。渐渐地,事实和数据也证明了 PostgreSQL 是一款非常稳定的高性能数据库产品,能够运行金融级别的应用系统。

    每一种数据库产品都有它的应用场景,每一家公司也都有着不同的资源,在选择一款数据库产品时,不能随大溜,跟潮流,一定要根据自身的情况,对包括未来数据库技术的发展,业务的特性,金融监管的要求,公司人员具备的技能等做出全方位的分析,进而选择一款最适合自己的数据库。而且,在数据库技术蓬勃发展的今天,企业可能也会根据不用的应用场景选择多种数据库产品,用以满足存放和访问不同数据类型的需要。这本身也是一个寻找最优化配置的过程,实现价值的最大化。

    还有一点我觉得很重要,就是每一种数据库,都要按照它原本的设计思想和设计目标去使用它,而不能把它当成黑盒子。首先要去了解它,然后再去适配它,只有这样才能达到最好的磨合效果,才有可能将性能发挥到极致。反之,则会出现强扭的瓜不甜,导致性能问题。

    虽然平安科技的 PostgreSQL 应用规模已经很大,取得了不小的成绩,但平安在 PostgreSQL 数据库上还有很长的路要走。例如如何使用分布式架构来提升 PostgreSQL 的横向扩展能力,支撑 VLDB(Very Large Database),不仅可以运行 OLAP 负载,也可以运行 OLTP 负载;还有如何丰富 PostgreSQL 的性能数据和诊断数据,进一步提升在问题发生时的分析、定位以及解决时效,将对系统的影响程度降到最低。

    这些都是我们接下来面临的挑战,对于象平安这样的综合金融服务集团挑战尤其严峻。但可喜的是,我们看到 PostgreSQL 的发展越来越成熟,功能越来越丰富,很多我们希望的特性要么已经被实现,要么就是已经被纳入规划。PostgreSQL 的社区也在不断壮大,日趋活跃,不断有新的公司开始采用 PostgreSQL。在这样的大好形势下,相信 PostgreSQL 一定可以巩固在开源数据库领域的领导地位,不仅成为最先进的开源数据库,而且成为金融服务行业最强大的开源数据库!

    展开全文
  • postgresql_95

    2019-01-22 09:09:53
    PostgreSQL是以加州大学伯克利分校计算机系开发的 POSTGRES,现在已经更名为PostgreSQL,版本 4.2为基础的对象关系型数据库管理系统(ORDBMS)。PostgreSQL支持大部分 SQL标准并且提供了许多其他现代特性:复杂查询...
  • PostgreSQL下载安装

    千次阅读 2018-12-03 11:38:21
    1.PostgreSQL下载安装 (1)在浏览器中输入网址https://www.postgresql.org/进行PostgreSQL相应版本的下载; (2)双击下载的文件,打开后如下图,点击next; (3)选择软件的安装路径(路径 根据自己的需要来定)...

    1.PostgreSQL下载安装

    (1)在浏览器中输入网址https://www.postgresql.org/进行PostgreSQL相应版本的下载;
    在这里插入图片描述
    (2)双击下载的文件,打开后如下图,点击next;
    在这里插入图片描述
    (3)选择软件的安装路径(路径 根据自己的需要来定),点击next;

    在这里插入图片描述
    (4)设置数据库超级管理员的密码,设置完成后 ,点击next;
    在这里插入图片描述
    (5)如图,可选择设置端口号,也可以不设置,默认即可,然后点击next;

    在这里插入图片描述
    (6)然后一直点击下一步,显示正在安装,等安装结束后,点击next;
    在这里插入图片描述
    (7)出现如图所示,则表示安装已完成,点击finish;
    在这里插入图片描述

    2.PostGIS安装配置

    (1)在安装PostGIS前首先必须安装PostgreSQL,然后再安装好的Stack Builder中选择安装PostGIS组件;
    在这里插入图片描述
    在这里插入图片描述
    (2)下载完成后,进行PostGIS的安装,在安装的同时默认创建空间数据库,点击next;
    在这里插入图片描述
    (3)设置安装路径;
    在这里插入图片描述
    (4)设置PostGIS用户名密码,默认端口:5432(也可自己设置,一般都选择默认的),点击next;
    在这里插入图片描述
    (5)设置空间数据库名,安装完成;
    在这里插入图片描述
    在这里插入图片描述
    (6)使用pgAdmin4管理数据库:如下图,一个为空间数据库,一个为二维数据库;
    在这里插入图片描述

    展开全文
  • postgreSQL

    2018-09-27 10:43:19
    一款postgreSQL 的客户端,无需安装,解压后,直接运行 bin下的pgAdmin3 就可以了。
  • PostgreSQL 10 安装详细步骤

    万次阅读 多人点赞 2018-10-31 12:24:07
    PostgreSQL 10 安装详细步骤:https://blog.csdn.net/antma/article/details/83579920 PostGIS 2.5.0 安装详细步骤:https://blog.csdn.net/antma/article/details/83580859 一、下载安装包 1、进入PostgreSQL ...
  • postgresql 安装教程 带图 on windows

    千次阅读 2015-12-26 11:22:18
    第一步 去官网下载最新版PostGresql 安装版 (这次版本选择 9.4.5 ) http://www.postgres.cn/download (中国postgresql社区网址) http://www.enterprisedb.com/products-services-training/pgdownload(postgresql...
  • PostgreSQL安装详细步骤(windows)

    千次阅读 2017-05-26 09:00:17
    PostgreSQL安装: 一、windows下安装过程 安装介质:postgresql-9.1.3-1-windows.exe(46M),安装过程非常简单,过程如下: 1、开始安装: 2、选择程序安装目录: 注:安装 PostgreSQL 的分区最好是 ...
  • 文章目录开发背景源码核心依赖Java源码运行结果 开发背景 源码 核心依赖 <dependencies> <!-- eureka注册中心 --> <dependency> <groupId>org.springframework....artifactId&...
  • 对Oracle 、SQL Server、MySQL、PostgreSQL数据库优缺点分析   Oracle Database Oracle Database,又名Oracle RDBMS,或简称Oracle。是甲骨文公司的一款关系数据库管理系统。它是在数据库领域一直处于领先...
  • 目录 实际迁移入门 模式迁移 迁移工具 免费/开源工具 ETL工具 手动迁移模式 数据迁移 开源工具 ETL工具 手动迁移数据 ETL/ELT注意事项 Talend Pentaho 报表平台 JasperReports ...调度...
  • SQL Server:历史记录,版本和版本 PostgreSQL:历史,版本和版本 根据您的需要选择合适的PostgreSQL版本 生产工作负载 开发,测试,登台或试点 本地与云 为什么要开源? 尽管毫无疑问,许可和专有数据库的...
  • SQL优化In PostgreSQL

    2017-10-21 17:11:23
    1、Create Index Directly 2、Change Conditions to Use Index 3、尽量避免在where子句中对字段进行运算,导致查询规划器...14、查找需要维护的索引,并自定创建索引维护SQL 15、一个index size影响query plan的例子
  • PostgreSQL Server Encoding sql_ascii attention

    千次阅读 2015-10-14 22:10:21
    " 我有一个postgresql,比较大,编码是sqlascii码,我想转换成有utf8 ,有方案可行吗? " 如果使用场景中未用到non-ASCII编码的字符, 那很幸运, 导出导入就可以了. 但是如果场景中使用了non-ASCII编码的字符, 那就没...
  • 基于benchmark对postgresqlsqlserver的性能进行对比
  • http://wiki.openbravo.com/wiki/ERP/2.50/Developers_Guide/Concepts/DB/PL-SQL_code_rules_to_write_Oracle_and_Postgresql_code Procedure Language rules Openbravo ERP supports Oracle and PostgreS
  • SQL Server 2012链接服务器到PostgreSQLSQL Server 2012链接服务器到PostgreSQLSQL Server 2012链接服务器到PostgreSQLSQL Server 2012链接服务器到PostgreSQLSQL Server 2012链接服务器到PostgreSQL
  • PostgreSQL 常用SQL语句

    2020-02-18 07:15:00
    PostgreSQL 简介[1]PostgreSQL可以说是目前功能最强大、特性最丰富和结构最复杂的开源数据库管理系统,其中有些特性甚至连商业数据库都不具备。这个起源于加州大学伯克利分...
  • <pre><code>sql.Register("sqlWithHooks", sqlhooks.Wrap(r.Driver(), &Hooks{})) // Connect to the registered wrapped driver db, err := sql.Open("sqlWithHooks", ":memory:") if err != nil { fmt....
  • SQL优化之PostgreSQL 分区

    千次阅读 2018-05-01 21:21:15
    本文转发自Jason’s Blog,原文链接 http://www.jasongj.com/2015/12/13/SQL3_partition/  一、典型使用场景 随着使用时间的增加,数据库中的数据量也不断增加,所以数据库查询越来越慢。 加速数据库的方法很多...
  • PostgreSQL导入备份sql文件

    千次阅读 2020-06-03 21:24:16
    在完成PostgreSQL安装之后,可能需要对数据库表结构进行导入,此时可直接使用PostgreSQL提供的..../psql -d databaseName -U postgres -f /Users/xxx/xx_dump.sql 其中-d后面紧跟的为“数据库名”,-U后面紧跟的为“数
  • PostgreSQL API & SQL API

    2011-04-28 00:04:00
    PostgreSQL 7.3 API SQL语法大全-最新版.chm
  • PostgreSQL导入sql文件

    万次阅读 2019-07-05 13:15:30
    最近新项目中用到PostgreSQL,运行测试导入表结构和数据是必须的,用navicat导入sql文件报错,之后查询了一下资料,得到了正确的导入方式: 1.命令行(cmd或powershell)进入PostgreSQL安装目录 cd \PostgreSQL\11\...
  • EMS SQL Manager 2007 for PostgreSQL v4.5.0.1破解
  • postgresql 导入sql

    千次阅读 2018-12-26 15:58:43
    1、找到postgresSql安装...2、E:\Program Files\PostgreSQL\9.5\bin (考入要执行的sql文件) 3、运行cmd 4、输入执行 psql -U username -W -d dbname -f xx.sql 例如:psql -U 用户名 -W -d 数据库名 -f xx.sql ...

空空如也

1 2 3 4 5 ... 20
收藏数 3,559,376
精华内容 1,423,750
关键字:

postgresql