PostgreSQL实例参考

陈景峰(netkiller)

前言

经过三个月的努力《PostgreSQL 实用实例参考》正式版终于推出了。因为最近换了工作，新公司的工作也很忙所以文档进展很慢，从最初几十页写到现在200页的文档，每天写文档的时间越来越少，有时一周也就只写2页，甚至一周一字未对。

正式版推出了，然后就是不断的修正。可能这段时间《PostgreSQL 实用实例参考》更新会更慢些。因为我还有其它文档要写：《OpenLDAP 文档》、《PHP + Corba + Python文档》、《JBuilder + Weblogic + PostgreSQL 开发EJB》。。。。。

文档中所有例子，都是在工作总结出来的，如有错误请指正。本人爱写错别字（哈哈）如果你发现了有错字，请发邮件给我netkiller(at)9812(dot)net修正文档。

300页之后不再推出HTML格式的文档了，之后的文档以PDF、PS（PostScript）格式为主，我是使用Microsoft Word写文档，处理300页的文档很困难，在保存文档或将doc文件转成其它格式的文件时经常会出现无响应。我也考虑过使用docbook / latex，或Page Maker。前者非所见即所得，要用户使用XML撰写，通过make一类的命令可以生成多种格式的文档，docbook也是UNIX手册的标准格式。后者Page Maker不用说了，Adobe出品，生成PDF更好些。

这是我第一次写一篇如此长的文档，没有经验，写的不好，不敢称为“书”，所以我叫它“文档”。

1.1 本文档的读者对象

文档面向有一定数据库基础用户。在这里我假设你对数据有一定认识，能够使用create创建数据与表，能够使用select、insert、update等语句操作数据库记录。

不管是谁，我希望这本文档都能对你有所帮助。

1.2 本文档主要内容

第一章主要介绍PostgreSQL

第二章是开发中遇到的一些问题

附录中一些SQL脚本文件，可供用户参考。

1.3 怎样使用本文档

边看、边做、边试验，然后总结，多动脑。有问题先查查这本文档，如果文档中没有提到，再考虑其它方式，或与我联系。

作者简介

作者信息：

陈景峰，昵称：netkiller, UNIX like爱好者,研究方向群集系统、网络安全、数据仓库与数据挖掘、LDAP、J2EE，Corba，企业解决方案。

主页地址：

http://www.9812.net/

ICQ:101888222

Yahoo:snetkiller

AIM:xnetkiller

网易泡泡：openunix@163.com

E-Mail: netkiller@9812.net

有问题最好给我发Email或去下面的Newsgroup里讨论

news://news.cdut.edu.cn/cn.lang.java

news://news.cdut.edu.cn/cn.lang.python

Web Newsgroup:

http://202.103.190.130:8080/news

我常去的BBS：

http://www.pgsqldb.org

http://www.chinaunix.com

http://www.linuxforum.net

第一章 PostgreSQL

PostgreSQL Wins Linux Journal Editors Choice Award
Posted on 2004-08-02
Posted by press at postgresql.org

PostgreSQL has won the 2004 Linux Journal Editors' Choice Award for the best DBMS!

Linux Journal's Editors' Choice Awards are well-known as the premiere forum recognizing outstanding product developments and achievements in the Linux market, and winners of the sixth annual awards are featured in the August 2004 issue of Linux Journal.

Check out the full article.

1 简介

我接触PostgreSQL是2000年，但项目中使用PostgreSQL是2003年，2000当时应该是5.x，6.x版本我并没有深入地研究这个数据库，还是主要使用MS Sql Server 7/2000 、Oracle 8。

因为很多企业难以支付MS Sql Server 7/2000 、Oracle 8这笔费用，所以Free Database是最佳选择。但大多免费的数据库，功能有限、性能也差，跟本不能满足我们的需求。

1.4 关于性能

有一段时间里我们使用MySQL，实在不好用，功能太少，它只实现了SQL92 中不到30%的功能。除了select、insert、update、delete还有什么功能？一味强调速度快，真的是这样吗？MySQL数据量增加很大时，速度下划很快。

几万条记录时速度最快，几十万记录时速度不同了，几百万时就开始慢了。PostgreSQL 随着数据量增大时，速度变化差距不象MySQL那么大。

有些朋友在网上说（触发器、游标、外键、视图）影响性能。这里要说明一下如果适当的使用视图、子查询、触发器、游标……会让你开发更轻松。

注：关于游标，很多SQL书中这样写“游标就是指向一行的指针”在PostgreSQL有些不同，它是返回一个结果集，对结果集next 操作返回一行。

Phpbuilder上有一篇文章是写PostgreSQL 与 MySQL 大家可以去看看。

1.5 为什么说postgresql是最先进的开源数据库？

1. 技术领先：
很多新技术都是它提出的
如：pl过程语言．在其它数据系统中都有自己的专用PL语言。而PostgreSQL中支持很多种PL语言（pl/tcl,pl/python,pl/perl,pl/php,pl/shell/pl/pgsql,pl/java.......）
还有面象对象(ORDBMS)他实现的也很早．
他的数据类型支持很全．如几何型,数组．．．在其它RDBMS中是没有的．
总是有新的技术、思想加入其中

2. 在开源ORDBMS中PostgreSQL功能最强．也最完善

1.6 PostgreSQL对SQL99的支持

SQL-3/SQL99	√
PRIMARY KEY主键	√
FOREIGN KEY外键	√
Schema 模式	√
TOAST大对象	√
View视图	√
正则表达式	√
subquery子查询	√
TRIGGER触发器	√
RULE规则	√
FUNCTION过程/函数	√
CURSOR游标	√
PLSQL 过程语言	√（PL/pgSQL,PL/Tcl,PL/Perl,PL/Python,plPHP等等）
OLTP表的锁定、事务隔离	√
权限	√（用户、组）
Object对象支持	√（ORDBMS）
	√

其它：

连接	进程方式
SSL	√
群集（HA，数据同步复制。。。）	√
ODBC	√
JDBC	√
裸设备	目前不支持
下面是一些限制：一行，一个表，一个库的最大尺寸是多少？
一个数据库最大尺寸？	无限制（存在 32TB 的数据库）
一个表的最大尺寸？	32TB
一行的最大尺寸？	1.6TB
一个字段的最大尺寸?	1GB
一个表里最大行数？	无限制
一个表里最大列数？	跟列类型有关,250-1600
一个表里的最大索引数量？	无限制

当然，实际上没有真正的无限制，还是要受可用磁盘空间、可用内存/交换区的制约。表的最大尺寸 32 TB 不需要操作系统对大文件的支持。大表用多个 1 GB 的文件存储，因此文件系统尺寸的限制是不重要的。如果缺省的块大小增长到 32K ，最大的表尺寸和最大列数可以增加。

这里引用http://www.pgsqldb.org/postgres-faq.html4.5详细请登录网站查看。

2 PostgreSQL 数据库

2.1 PostgreSQL分区

PostgreSQL 最好自己单独一个分区，如果你有两块硬盘建议你给它单独一块硬盘。

[chen@linux chen]$ df

Filesystem 1K-blocks Used Available Use% Mounted on

/dev/sda9 1004024 99892 853128 11% /

/dev/sda1 101089 9498 86372 10% /boot

/dev/sda2 120952116 7648124 107159936 7% /home

none 515400 0 515400 0% /dev/shm

/dev/sda10 2522048 33260 2360672 2% /tmp

/dev/sda7 5036284 2238244 2542208 47% /usr

/dev/sda6 5036284 1919140 2861312 41% /var

/dev/sda5 40313964 99444 38166636 1% /var/lib/pgsql

/dev/sda3 60476068 212532 57191508 1% /cvsroot

[chen@linux chen]$

[chen@linux chen]$ df -m

Filesystem 1M-blocks Used Available Use% Mounted on

/dev/sda9 980 98 833 11% /

/dev/sda1 99 10 84 10% /boot

/dev/sda2 118117 7469 104648 7% /home

none 503 0 503 0% /dev/shm

/dev/sda10 2463 33 2305 2% /tmp

/dev/sda7 4918 2186 2482 47% /usr

/dev/sda6 4918 1875 2794 41% /var

/dev/sda5 39369 98 37272 1% /var/lib/pgsql

/dev/sda3 59059 208 55851 1% /cvsroot

[chen@linux chen]$

2.2 RPM包安装

[root@linux software]# ls -1

postgresql-7.3.4-1PGDG.i386.rpm

postgresql-contrib-7.3.4-1PGDG.i386.rpm

postgresql-debuginfo-7.3.4-1PGDG.i386.rpm

postgresql-devel-7.3.4-1PGDG.i386.rpm

postgresql-docs-7.3.4-1PGDG.i386.rpm

postgresql-jdbc-7.3.4-1PGDG.i386.rpm

postgresql-libs-7.3.4-1PGDG.i386.rpm

postgresql-pl-7.3.4-1PGDG.i386.rpm

postgresql-python-7.3.4-1PGDG.i386.rpm

postgresql-server-7.3.4-1PGDG.i386.rpm

postgresql-tcl-7.3.4-1PGDG.i386.rpm

postgresql-test-7.3.4-1PGDG.i386.rpm

[root@linux software]# rpm -Uvh --nodeps `ls -1`

Preparing... ########################################### [100%]

1:postgresql-test ########################################### [ 8%]

2:postgresql ########################################### [ 17%]

3:postgresql-contrib ########################################### [ 25%]

4:postgresql-debuginfo ########################################### [ 33%]

5:postgresql-devel ########################################### [ 42%]

6:postgresql-docs ########################################### [ 50%]

7:postgresql-jdbc ########################################### [ 58%]

8:postgresql-libs ########################################### [ 67%]

9:postgresql-pl ########################################### [ 75%]

10:postgresql-python ########################################### [ 83%]

11:postgresql-server ########################################### [ 92%]

12:postgresql-tcl ########################################### [100%]

[root@linux software]# rpm -qa|grep postgre

postgresql-devel-7.3.4-1PGDG

postgresql-7.3.4-1PGDG

postgresql-python-7.3.4-1PGDG

postgresql-contrib-7.3.4-1PGDG

postgresql-jdbc-7.3.4-1PGDG

postgresql-server-7.3.4-1PGDG

postgresql-debuginfo-7.3.4-1PGDG

postgresql-libs-7.3.4-1PGDG

postgresql-tcl-7.3.4-1PGDG

postgresql-test-7.3.4-1PGDG

postgresql-pl-7.3.4-1PGDG

postgresql-docs-7.3.4-1PGDG

[root@linux software]#

[root@linux software]# service postgresql start

Starting postgresql service: [ OK ]

[root@linux software]# su postgres

bash-2.05b$ createdb

CREATE DATABASE

bash-2.05b$ psql

Welcome to psql 7.3.4, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms

\h for help with SQL commands

\? for help on internal slash commands

\g or terminate with semicolon to execute query

\q to quit

postgres=# \q

bash-2.05b$

bash-2.05b$ vi /var/lib/pgsql/data/postgresql.conf

#========================================================================

# Connection Parameters

#tcpip_socket = false

tcpip_socket = true

#ssl = false

#max_connections = 32

max_connections = 128

#superuser_reserved_connections = 2

#port = 5432

#hostname_lookup = false

#show_source_port = false

#unix_socket_directory = ''

#unix_socket_group = ''

#unix_socket_permissions = 0777 # octal

#virtual_host = ''

#krb_server_keyfile = ''

# Shared Memory Size

#shared_buffers = 64 # min max_connections*2 or 16, 8KB each

shared_buffers = 256 # min max_connections*2 or 16, 8KB each

#max_fsm_relations = 1000 # min 10, fsm is free space map, ~40 bytes

#max_fsm_pages = 10000 # min 1000, fsm is free space map, ~6 bytes

#max_locks_per_transaction = 64 # min 10

#wal_buffers = 8 # min 4, typically 8KB each

bash-2.05b$ vi /var/lib/pgsql/data/pg_hba.conf

host all all 127.0.0.1 255.255.255.255 md5

bash-2.05b$ psql

Welcome to psql 7.3.4, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms

\h for help with SQL commands

\? for help on internal slash commands

\g or terminate with semicolon to execute query

\q to quit

postgres=# CREATE USER netkiller WITH PASSWORD 'chen';

CREATE USER

postgres=# CREATE DATABASE netkiller WITH OWNER = netkiller TEMPLATE = template0 ENCODING = 'UNICODE';

CREATE DATABASE

postgres=# \du

List of database users

User name | User ID | Attributes

-----------+---------+----------------------------

netkiller | 100 |

postgres | 1 | superuser, create database

(2 rows)

postgres=# \l

List of databases

Name | Owner | Encoding

-----------+-----------+-----------

netkiller | netkiller | UNICODE

postgres | postgres | SQL_ASCII

template0 | postgres | SQL_ASCII

template1 | postgres | SQL_ASCII

(4 rows)

postgres=# \q

bash-2.05b$

bash-2.05b$ createlang plpgsql netkiller

bash-2.05b$

bash-2.05b$ exit

exit

[root@linux software]# service postgresql restart

[ OK ]

Starting postgresql service: [ OK ]

[root@linux software]#

[root@linux software]# psql -h127.0.0.1 -Unetkiller netkiller

Password:

Welcome to psql 7.3.4, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms

\h for help with SQL commands

\? for help on internal slash commands

\g or terminate with semicolon to execute query

\q to quit

netkiller=>

注意：

1. 程序安装我使用了一个小技巧。（我懒哈哈）rpm -Uvh --nodeps `ls -1`
安装一定要加—nodeps，ls -1 这里是减号，阿拉伯数字1，不是英文字母“l” (L)

2. postgres只能用于UNIX Domain Socket方式登陆(/tmp/.s.PGSQL.5432)，不能在TCP/IP Socket模式下登陆。

[root@linux software]# ls -la /tmp

total 68

drwxrwxrwt 11 root root 4096 Nov 11 16:29 .

drwxr-xr-x 22 root root 4096 Nov 5 14:49 ..

srwx------ 1 root nobody 0 Nov 5 11:34 .fam_socket

drwxrwxrwt 2 xfs xfs 4096 Nov 5 14:49 .font-unix

drwx------ 2 root root 4096 Nov 5 19:06 .gconfd

srw-rw-rw- 1 root root 0 Nov 5 14:49 .gdm_socket

drwxrwxrwx 2 bin bin 4096 Nov 5 14:49 .iroha_unix

drwx------ 2 root root 4096 Nov 5 19:14 kde-root

drwx------ 2 root root 16384 Nov 5 18:46 lost+found

drwxr-xr-x 2 root root 4096 Nov 5 18:55 .mozilla

drwx------ 2 root root 4096 Nov 5 11:38 orbit-root

drwxr-xr-x 2 root root 4096 Nov 5 19:14 .qt

-rw------- 1 root root 1024 Nov 5 18:52 .rnd

srwxrwxrwx 1 postgres postgres 0 Nov 11 16:29 .s.PGSQL.5432

-rw------- 1 postgres postgres 26 Nov 11 16:29 .s.PGSQL.5432.lock

-r--r--r-- 1 root root 11 Nov 5 14:49 .X0-lock

drwxrwxrwt 2 root root 4096 Nov 5 14:49 .X11-unix

[root@linux software]# file /tmp/.s.PGSQL.5432

/tmp/.s.PGSQL.5432: socket

使用file命令可以查看文件类型，所以/tmp/.s.PGSQL.5432显示类型为/tmp/.s.PGSQL.5432: socket

[root@linux software]# psql -h127.0.0.1 –Upostgres db会提示

Password:

psql: FATAL: Password authentication failed for user "postgres"

[root@linux software]# psql -h127.0.0.1 -Upostgres netkiller

Password:

psql: FATAL: Password authentication failed for user "postgres"

解决方法是创建一个用户。

3. 登陆提示
[root@linux software]# psql -h127.0.0.1 -Unetkiller netkiller

psql: FATAL: No pg_hba.conf entry for host 127.0.0.1, user netkiller, database netkiller

编辑/var/lib/pgsql/data/pg_hba.conf文件加入

host all all 127.0.0.1 255.255.255.255 md5

2.3 APT 安装

Apt 是Debian Linux安装风格

去下而网站可以找到APT软件包

http://www.rpmfind.net/

输入：apt 搜索即可

http://www.rpmfind.net/linux/rpm2html/search.php?query=apt

[root@linux root]# cd /usr/local/src/

[root@linux src]# wget ftp://194.199.20.114/linux/freshrpms/redhat/9/apt/apt-0.5.5cnc6-fr1.i386.rpm

[root@linux src]# rpm -ivh apt-0.5.5cnc6-fr1.i386.rpm

warning: apt-0.5.5cnc6-fr1.i386.rpm: V3 DSA signature: NOKEY, key ID e42d547b

Preparing... ########################################### [100%]

1:apt ########################################### [100%]

[root@linux src]# apt-get update

Get:1 http://ayo.freshrpms.net redhat/9/i386 release [1171B]

Fetched 1171B in 6s (170B/s)

Get:1 http://ayo.freshrpms.net redhat/9/i386/os pkglist [1357kB]

Get:2 http://ayo.freshrpms.net redhat/9/i386/os release [140B]

Get:3 http://ayo.freshrpms.net redhat/9/i386/updates pkglist [487kB]

Get:4 http://ayo.freshrpms.net redhat/9/i386/updates release [153B]

Get:5 http://ayo.freshrpms.net redhat/9/i386/freshrpms pkglist [151kB]

Get:6 http://ayo.freshrpms.net redhat/9/i386/freshrpms release [157B]

Fetched 1995kB in 2m41s (12.3kB/s)

Reading Package Lists... Done

Building Dependency Tree... Done

[root@linux src]#

[root@linux src]# apt-get check

Reading Package Lists... Done

Building Dependency Tree... Done

You might want to run `apt-get -f install' to correct these.

The following packages have unmet dependencies:

postgresql-python: Depends: mx but it is not installed

E: Unmet dependencies. Try using -f.

[root@linux src]#

[root@linux src]# apt-get -f install

Reading Package Lists... Done

Building Dependency Tree... Done

Correcting dependencies... Done

The following extra packages will be installed:

The following NEW packages will be installed:

0 packages upgraded, 1 newly installed, 0 removed and 146 not upgraded.

Need to get 609kB of archives.

After unpacking 2266kB of additional disk space will be used.

Do you want to continue? [Y/n]

Get:1 http://ayo.freshrpms.net redhat/9/i386/os mx 2.0.3-8 [609kB]

Fetched 609kB in 2m49s (3596B/s)

Executing RPM (-Uvh)...

Preparing... ########################################### [100%]

1:mx ########################################### [100%]

[root@linux src]#

[root@linux src]# apt-get install postgresql-server

如果是Debian Linux 系统直接