PostgreSQL

PostgreSQL 9.3 beta已经发布, release note详见,http://www.postgresql.org/docs/devel/static/release-9-3.html

最近在对release note中提到的部分新特性进行测试和分析. 

以下是测试文章汇总 :

[未完]

1. PostgreSQL 9.3 add copy to|from external program

http://blog.163.com/digoal@126/blog/static/163877040201325102638951/

2. PostgreSQL 9.3 add LATERAL support

http://blog.163.com/digoal@126/blog/static/1638770402012984936451/

推荐书籍：

概念入门：

《PostgreSQL Introduction and Concepts》

开发入门：

《PostgreSQL开发必备参考手册》

官方手册：

http://www.postgresql.org/docs/

翰高翻译的中文手册：

http://www.highgo.com.cn/hgdb/docs

管理类书籍：

《PostgreSQL 9 Administration Cookbook》

《PostgreSQL 9.0 High Performance》

《How PostgreSQL Processes a Query》

src/tools/backend/index.html

培训PPT：

《PostgreSQL Inside 系列》

《PostgreSQL DBA培训PPT》

中文社区：

http://bbs.pgsqldb.com

QQ群里一些网友问到的问题，收集如下 : 

目录 :

1. PostgreSQL存储过程中自定义异常怎么弄?

2. PostgreSQL9.1的同步事务在某些情况下用户主动cancel等待sync replication standby 的acknowledge,实际本地已提交.

3. PostgreSQL如何满足已经存在则更新, 不存在则插入的需求.

4. copy和insert哪个效率高?

5. PostgreSQL能不能限制数据库的大小?

6. 怎样给一个用户授予只读角色?

7. 不想让数据插入到某个表应该怎么做?

8. PostgreSQL 中有没有rownum这样的，显示结果集的序号?

9. PostgreSQL 函数中如何使用savepoint?

10.请问, pg脚本有宏替换, 计算字符串公式的能力? 类似 a=2 ; evaluate('5-a') ; 如果将这个值赋值给这个变量呢? zresult = evaluate('5-a') ;

11.UPDATE A表 FROM B表 ?

12. hex转decimal

13. PostgreSQL 时区.

14. PostgreSQL 不同的版本, 只读事务中是否允许 nextval() 取序列值;

15. 修改数据库记录的时候,

前面几篇BLOG介绍了PostgreSQL 数据传输的加密. 数据传输加密可以防止数据在传输过程中泄露.

http://blog.163.com/digoal@126/blog/static/163877040201342233131835/

http://blog.163.com/digoal@126/blog/static/16387704020134229431304/

http://blog.163.com/digoal@126/blog/static/163877040201342383123592/

http://blog.163.com/digoal@126/blog/static/1638770402013423102431541/

http://blog.163.com/digoal@126/blog/static/16387704020134231058553/

本篇主要介绍一下数据存储的加密.

存储加密的好处是, 不用担心因为硬盘被盗导致的数据泄露.

存储加密的方法很多, 有些硬件厂商就提供了硬件层面的加密.

本文主要介绍一下Linux下面的卷加密的软件cryptsetup.

在上一篇BLOG中介绍了不要在pg_hba.conf中使用password认证方法, 除非你的客户端和数据库服务器之间的网络是绝对安全的.

http://blog.163.com/digoal@126/blog/static/1638770402013423102431541/

MD5方法,认证过程 :

Encrypting Passwords Across A Network

The MD5 authentication method double-encrypts the password on the client before sending it to the server. It first MD5-encrypts it based on the user name, and then encrypts it based on a random salt sent by the server when the database connection was made. It is this double-encrypted value that is sent over the network to the server. Double-encryption not only prevents the password

前面一篇BLOG介绍了PostgreSQL ssl数据加密的性能, 相比未加密性能下降得比较厉害.

http://blog.163.com/digoal@126/blog/static/16387704020134229431304/

本文将测试一下ssh tunnel加密的性能情况.

测试机与前面测试一致.

首先在测试机生成key.

pg92@db-172-16-3-39-> ssh-keygen -t rsa

一路回车

生成私钥和公钥.

pg92@db-172-16-3-39-> cd .ssh

pg92@db-172-16-3-39-> ll

total 8.0K

-rw------- 1 postgres postgres 887 May 23 07:32 id_rsa

-rw-r--r-- 1 postgres postgres 246 May 23 07:32 id_rsa.pub

查看公钥内容, 将要拷贝到数据库服务器上.

pg92@db-172-16-3-39-> cat id_rsa.pub

ssh-rsa AAAAB3NzaC1yc2E

上一篇BLOG介绍了PostgreSQL使用ssl加密客户端和服务端的数据传输.

查看openssl支持哪些ciphers :

pg93@db-172-16-3-33-> openssl ciphers

DHE-RSA-AES256-SHA:DHE-DSS-AES256-SHA:AES256-SHA:KRB5-DES-CBC3-MD5:KRB5-DES-CBC3-SHA:EDH-RSA-DES-CBC3-SHA:EDH-DSS-DES-CBC3-SHA:DES-CBC3-SHA:DES-CBC3-MD5:DHE-RSA-AES128-SHA:DHE-DSS-AES128-SHA:AES128-SHA:RC2-CBC-MD5:KRB5-RC4-MD5:KRB5-RC4-SHA:RC4-SHA:RC4-MD5:RC4-MD5:KRB5-DES-CBC-MD5:KRB5-DES-CBC-SHA:EDH-RSA-DES-CBC-SHA:EDH-DSS-DES-CBC-SHA:DES-CBC-SHA:DES-CBC-MD5:EXP-KRB5-RC2-CBC-MD5:EXP-KRB5-DES-CBC-MD5:EXP-KRB5-RC2-CBC-SHA:EXP-KRB5-DES-CBC-SHA:EXP-EDH-RSA-DES-CBC-SHA:EXP-EDH-DSS-DES-CBC-SHA:EXP-DES-CBC-SHA:EXP-RC2-CBC-MD5:EXP-RC2-CBC-MD5:EXP-KRB5-RC4-MD5:EXP-KRB5-RC4-SHA:EXP-RC4-MD5:EXP-RC4-MD5

要支持ssl连接, 数据库服务端和客户端都需要openssl包.

以CentOS 5.x 64为例 :

openssl-0.9.8e-20.el5

openssl-devel-0.9.8e-20.el5

默认情况下PostgreSQL 读取openssl的配置文件openssl.cnf, 在openssl version -d返回的目录中.

当然也可以使用OPENSSL_CONF环境变量读取指定的配置的文件.

PostgreSQL reads the system-wide OpenSSL configuration file. By default, this file is named openssl.cnf and is located in the directory reported by openssl version -d. This default can be overridden by setting environment variable OPENSSL_CONF to the name of the desired configuration file.

查看目录 :

pg93@db-172-16-3-33-> openssl version -d

OPENSSLDIR: "/etc/pki/tls"

pg93@db-172-16-3-33-> cd /etc/pki/tls

pg93@db-172-16-3-33-> ll

下面是引自网上的翻译：

1、如何知道我的OpenSSL运行版本

QUOTE:

# openssl version

OpenSSL 0.9.7a Feb 19 2003

-a参数可以获得更详细的信息：

QUOTE:

# openssl version -a

OpenSSL 0.9.7a Feb 19 2003

built on: Tue Oct 31 02:10:29 EST 2006

platform: linux-elf

options:  bn(64,32) md2(int) rc4(idx,int) des(ptr,risc1,16,long) blowfish(idx)

compiler: gcc -fPIC -DZLIB -DOPENSSL_THREADS -D_REENTRANT -DDSO_DLFCN -DHAVE_DLFCN_H -DKRB5_MIT -DOPENSSL_NO_ASM -DOPENSSL_NO_IDEA -DOPENSSL_NO_MDC2 -DOPENSSL_NO_RC5 -DOPENSSL_NO_EC -I/usr/kerberos/include -DL_ENDIAN -DTERMIO -Wall -O2 -g -march=i386 -mcpu=i686 -Wa,–noexecstack

OPENSSLDIR: “/usr/share/ssl”