PostgreSQL实例参考

 

陈景峰(netkiller)

前言

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

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

 

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

 

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

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

1.1    本文档的读者对象

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

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

1.2    本文档主要内容

第一章    主要介绍PostgreSQL

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

 

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

1.3    怎样使用本文档

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

作者简介

作者信息:

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

主页地址:

http://www.9812.net/

 

ICQ:101888222

Yahoo:snetkiller

AIM:xnetkiller

网易泡泡:openunix@163.com

E-Mail: openunix@163.com

 

有问题最好给我发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

 


目录

前言... 2

1.1          本文档的读者对象... 2

1.2          本文档主要内容... 2

1.3          怎样使用本文档... 3

作者简介... 3

目录... 5

第一章 PostgreSQL. 10

1     简介... 10

1.4          关于性能... 10

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

1.6          PostgreSQLSQL99的支持... 11

2     PostgreSQL 数据库... 12

2.1          PostgreSQL分区... 12

2.2          RPM包安装... 13

2.3          APT 安装... 17

2.4          PostgreSQL 8.0 beta for windows版本安装... 19

2.4.1       运行pgAdmin III 20

2.4.2       psql控制台:... 20

2.4.3       ODBC. 21

2.4.4       Unix/Linux 登录到Windows. 23

2.4.5       Windows 登录到 Unix/Linux. 24

2.5          数据库备份方案... 24

2.5.1       备份数据库脚本... 24

2.5.2       下载备份脚本... 25

2.5.3       保证备份数据的安全-PGP/GPG加密... 26

2.6          备份计划... 41

2.6.1       服务器端计划... 41

2.6.2       客户端计划... 42

2.7          数据恢复... 42

2.8          性能提升... 43

2.8.1       共享内存... 43

2.8.2       最大连接... 44

2.8.3       vacuumdb. 50

2.8.4       数据库操作与性能... 50

2.8.5       硬件方面... 51

2.8.6       磁盘性能... 53

2.9          安全的TCP/IP联接... 56

2.9.1       使用SSL进行安全的TCP/IP联接... 56

2.9.2       使用SSH进行安全TCP/IP联接... 69

2.10        连接ipv6主机... 71

3     数据定义(DDL... 74

3.1          日期时间常量... 74

3.1.1       当前日期... 74

3.1.2       当前时间... 74

3.1.3       当前日期时间... 75

3.1.4       除去时区... 75

3.1.5       计算时间差... 75

3.1.6       计算时间和... 76

3.1.7       date_part 76

3.2          汉字做字段名... 77

3.3          “::”数据转换... 79

3.3.1       text to varchar 79

3.4          序列... 81

3.4.1       等差列... 81

3.4.2       “123456789…”. 82

3.4.3       “13579…”. 83

3.4.4       “246810…”. 84

3.4.5       n1+n2 85

3.5          约束... 85

3.6          检查约束... 85

3.7          非空约束... 87

3.8          唯一约束... 87

3.8.1       单字段约束... 87

3.8.2       多个字段组合约束... 87

3.8.3       唯一约束的注意事项... 89

3.9          主键/外键... 91

3.9.1       主键... 91

3.9.2       外键约束... 92

3.9.3       PostgreSQL 7.3.x 新增功能... 92

3.9.4       层次递归-分类目录... 93

3.9.5       总结... 101

3.10        模式... 101

3.10.1     创建模式... 101

3.10.2     删除模式... 101

3.10.3     模式搜索路径... 101

4     实体关系(Entity-Relation... 103

4.1          E-R图(Entity-Relation... 104

4.2          一对多关系... 105

4.3          多对多关系... 107

4.4          一对一关系... 109

4.5          引用完整性... 110

5     查询SQLDML... 111

5.1          子查询... 111

5.2          substring()函数截取部分汉字... 113

5.3          sum()使用技巧... 115

5.4          集合查询 (合并n个表)... 116

6     视图... 119

6.1          VIEW本使用实例... 119

6.2          使用HTML格式化VIEW的实例... 120

6.3          view中使用汉字做字段名... 124

6.4          取出字符如果超过20个在后尾加“…”... 125

6.5          视图中使用子查询... 126

7     过程与函数... 127

7.1          基本使用实例... 127

7.2          过程中使用Select Into. 128

7.3          返回integer 130

7.4          返回void. 130

7.5          返回结果集record. 131

7.6          例子... 132

7.7          shell 过程语言... 133

8     规则... 134

8.1          规则实例... 134

9     触发器... 137

9.1          一般用法... 137

9.2          多个触发器使用同一个过程... 137

9.3          时间调度触发器... 140

9.3.1       定时触发器... 141

9.3.2       周期触发器... 141

9.4          其它例子... 142

10           游标... 145

10.1        游标结果集... 146

10.2        例子... 147

11           事务处理... 148

11.1        批量插入、更新、删除... 148

11.1.1     批量插入操作-1. 148

11.2        保持数据完整-2. 149

12           用户权限... 150

12.1.1     ... 150

12.1.1.1    创建组... 150

12.1.1.2    删除组... 151

12.1.2     用户... 151

12.1.2.1    创建用户... 151

12.1.2.2    删除用户... 152

12.1.2.3    修改密码... 152

12.1.3     创建数据... 153

12.1.4     用户认证... 153

12.1.4.1    本地连接... 153

12.1.4.2    允许任何IP连接主机... 154

12.1.5     脚本例子... 154

12.1.6     权限... 155

13           其它技巧例子... 155

第二章 开发篇... 155

13.1        汉字编码问题... 155

13.2        JDBC. 155

13.2.1     Jsp/Java. 156

13.2.2     toChinese() 方法... 156

13.2.3     Unicode (UTF-8) 完全解决方案... 156

13.2.3.1   setCharacterEncoding() 方案... 156

13.2.3.2   Web.xml Filter过滤方案:... 159

13.2.3.3   Jdbc url charSet方案... 165

13.3        Tomcat JNDI Datasource 配置... 165

13.4        JDBC通过SSL安全连接数据库... 168

13.5        开发相关... 168

13.5.1     Create Java Entity Bean (not EJB CMP) 168

13.5.2     连接数据库... 169

13.5.3     处理SQL 语句... 180

13.5.4     处理HTML表格... 185

13.5.5     什么时候应该把文件存在数据库中... 213

14           PHP. 215

14.1        PHP 连接PostgreSQL. 215

14.2        set CLIENT_ENCODING TO 'GB18030';方案... 215

14.3        convert()方案... 225

14.4        PHP iconv() 函数方案... 225

14.5        在标准I/O上使用 Linux iconv 命令方案... 226

15           开发工具/开发环境... 231

15.1        Macromedia Dreamweaver MX 2004 JSP开发环境的配置... 231

15.2        Jcreator 240

15.3        Eclipse. 241

15.4        JBuilder + Weblogic + PostgreSQL开发环境... 242

15.5        GUI数据库管理与设计(建模)工具... 273

15.5.1     phpPgAdmin. 273

15.5.2     PgAdmin III 275

15.5.3     Case Studio 2. 276

15.5.4     PostgreSQL Manager 284

15.5.5     DeZign for Databases. 289

15.5.6     GUI工具比较... 291

16           FAQ.. 291

16.1        关于“”与null 291

16.2        Postgresql与其它数据库... 291

16.3        Putty. 294

16.3.1     Putty密钥认证... 294

16.3.2     中输入汉字的问题... 299

16.4        控制台下输入汉字... 303

16.5        PostgreSQL RPM 包安装后,为何没有5432端口... 303

16.6        PostgreSQL 7.4.2 rhel3(高级服务器版,俗称AS3) 306

16.7        Pureftpd pgsql认证模块... 306

16.8        Vsftpd pgsql认证... 306

16.9        OpenLDAP-PostgreSQL HOWTO.. 307

16.10      PostgreSQL 成功案例与解决方案... 307

17           附录... 307

17.1        实例... 307

17.2        实例... 315

17.3        安装脚本... 337

17.3.1     setenv.sh. 337

17.3.2     install.sh. 338

17.4        附件... 342

17.5        其它... 342

18           参考资料... 343

19           版本、声明... 343

 


 

第一章 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       简介

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

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

1.4    关于性能

有一段时间里我们使用MySQL,实在不好用,功能太少,它只实现了SQL92 中不到30%的功能。除了selectinsertupdatedelete还有什么功能?一味强调速度快,真的是这样吗?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.         在开源ORDBMSPostgreSQL功能最强.也最完善

1.6    PostgreSQLSQL99的支持

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`
安装一定要加—nodepsls -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:

Password:

Password:

Password:

Password:

Password:

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:

  mx

The following NEW packages will be installed:

  mx

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 系统直接

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

 

2.4    PostgreSQL 8.0 beta for windows版本安装

PostgreSQL 8.0 betawindows版本安装包下载地址:

http://laser.dyndns.zhengmai.net.cn/download/postgresql-8.0-beta1.msi

下载后双击postgresql开始安装,只下一步,下一步,即可。

 

配置D:\PostgreSQL\8.0-beta1\data\pg_hba.conf 文件,建议使用UltraEdit工具。

host    all         all         192.168.0.1       255.255.255.255   md5

 

 

注:我的环境是Windows XP SP2

启动PostgreSQL 8.0服务器:

Ø         开始à所有程序àPostgreSQL 8.0-beta1à Start service

停止PostgreSQL 8.0服务器:

Ø         开始à所有程序àPostgreSQL 8.0-beta1à Stop service

进入psql控制台:

Ø         开始à所有程序àPostgreSQL 8.0-beta1à psql to template1

2.4.1   运行pgAdmin III

Ø         开始à所有程序àPostgreSQL 8.0-beta1àpgAdmin III

 

pgAdmin III的一般操作顺序是:

1.         新建一个用户(用于tcp/ip socketPostgreSQL帐号,而非系统用户postgres是一个系统帐号,不能用于tcp/ip连接,它是用来管理数据的用户)

2.         新建一个表空间,用于存放数据

3.         新建一个数据库实例

4.         新建一个PL过程语言(一般是pl/pgsql

5.         新建一个模式(Schema)

6.         创建表,视图,触发器等等

 

2.4.2   psql控制台:

网上有很多朋友问我,PostgreSQL 8.0psql在那,他默认打开template1数据库,如何打开其它数据等等问题。

步骤,开始à所有程序àPostgreSQL 8.0-beta1à psql to template1进入PostgreSQL 8.0 psql控制台,输入postgres用户的密码登录。

使用“\l”列出数据

使用“\c”连接到其它数据库。
\c
数据库 数据库所有者,接提示输入密码即可。

 

Password:

Welcome to psql.exe 8.0.0beta1, the PostgreSQL interactive terminal.

 

Type:  \copyright for distribution terms

       \h for help with SQL commands

       \? for help with psql commands

       \g or terminate with semicolon to execute query

       \q to quit

 

template1=# \l

        List of databases

   Name    |  Owner   | Encoding

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

 netkiller | chen     | UNICODE

 template0 | postgres | UNICODE

 template1 | postgres | UNICODE

(3 rows)

 

template1=# \c netkiller chen

Password:

You are now connected to database "netkiller" as user "chen".

netkiller=#

 

2.4.3   ODBC

1.         开始à控制面板à管理工具à数据源 (ODBC)

2.         单击“添加”按钮

3.         列表中选择PostgreSQLà单击“完成”按钮

4.         单击Save保存

5.         单击“确定”按钮

2.4.4   Unix/Linux 登录到Windows

[root@linux root]# psql -h 192.168.0.254 -U chen 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=#

2.4.5   Windows 登录到 Unix/Linux

D:\PostgreSQL\8.0-beta1\bin>psql.exe -h 192.168.0.1 -U netkiller netkiller

Password:

Welcome to psql.exe 8.0.0beta1, the PostgreSQL interactive terminal.

 

Type:  \copyright for distribution terms

       \h for help with SQL commands

       \? for help with psql commands

       \g or terminate with semicolon to execute query

       \q to quit

 

netkiller=>

 

2.5    数据库备份方案

2.5.1   备份数据库脚本

脚本功能是,首先备份数据库、然后打包、压缩为tar.gz、最后上传到指定位置并删除临时文件。

[root@linux root]# cat backup.sh

#!/bin/bash

 

FTPHOST=ftp.9812.net

USER=netkiller

PASSWD=xxx

 

echo "Starting Backup PostgreSQL ... "

#big5 gb2312 gb18030 …

export PGCLIENTENCODING=gb18030

su - postgres -c pg_dumpall > pgsql-backup.`date +%Y-%m-%d.%H:%M:%S`.dmp

tar zcvf pgsql-backup.`date +%Y-%m-%d`.tar.gz *.dmp

 

echo "Upload File ..."

ftp -n ${FTPHOST} <<!

user ${USER} ${PASSWD}

binary

prompt

mkdir backup

cd backup

mput *.tar.gz

close

bye

!

echo "Remove temp file ..."

rm -rf pgsql-backup.*.dmp

rm -rf pgsql-backup.????-??-??.tar.gz

[root@linux root]#

 

 

如果您没有一台专用于备份数据的机器(有静态IP的机器)。上面的备份脚本可更改为:

[root@linux root]# cat backup.sh

#!/bin/bash

 

echo "Starting Backup PostgreSQL ... "

su - postgres -c pg_dumpall > pgsql-backup.`date +%Y-%m-%d.%H:%M:%S`.dmp

tar zcvf pgsql-backup.`date +%Y-%m-%d`.tar.gz *.dmp

 

echo "Remove temp file ..."

rm -rf pgsql-backup.*.dmp

[root@linux root]#

2.5.2   下载备份脚本

 

[root@linux root]# cat getbackup.sh

#!/bin/bash

 

FTPHOST=ftp.9812.net

USER=netkiller

PASSWD=xxx

 

wget ftp://${USER}:${PASSWD}@${FTPHOST}/backup/*

 

ftp -n ${FTPHOST} <<!

user ${USER} ${PASSWD}

binary

prompt

cd backup

mdelete *

close

bye

!

[root@linux root]#

2.5.3   保证备份数据的安全-PGP/GPG加密

数据库中的内容有些是不能提供给用户的,如其它用户的资料,密码。在数据库中的数据,你可以通过权限来限制用户操作。将数据库备份(导出)到本地SQL文本文件中(xxxx.sql包括DDLDML) ,一但备份落入他手,后果不可设想,他很容易得用你的数据,因为你备份的数据是文本文件,没有任何加密措施。

这里介绍GnuPG 以下简称GPGGPGPGP兼容。由于PGP使用了许多专利算法,属于美国加密出口限制之列。而GnuPGGPL软件。

GPG使用非对称加密算法,安全程度很高。所谓非对称加密算法,就是每一个用户都拥有一对密钥:公钥和私钥。其中,私钥由用户保存,公钥提供给internet上的用户。

设:

       陈景峰的帐号:chen

       小明的帐号:ming

 

以下为chen 帐号的操作:

1.         查看当前文件夹

[chen@linux chen]$ ls -la

total 56

drwx------    4 chen     chen         4096 Dec 12 20:38 .

drwxr-xr-x    7 root     root         4096 Nov 12 11:47 ..

-rw-------    1 chen     chen         4953 Dec 10 14:05 .bash_history

-rw-r--r--    1 chen     chen           24 Feb 11  2003 .bash_logout

-rw-r--r--    1 chen     chen          191 Feb 11  2003 .bash_profile

-rw-r--r--    1 chen     chen          124 Feb 11  2003 .bashrc

-rw-r--r--    1 chen     chen         5531 Feb  4  2003 .canna

-rw-r--r--    1 chen     chen          847 Feb 20  2003 .emacs

-rw-r--r--    1 chen     chen          120 Feb 27  2003 .gtkrc

drwxr-xr-x    3 chen     chen         4096 Aug 12  2002 .kde

-rw-------    1 chen     chen          594 Dec 10 09:38 .viminfo

drwxr-xr-x    2 chen     chen         4096 Nov  5 19:16 .xemacs

[chen@linux chen]$

 

2.         生成密钥(公钥、私钥)

使用GPG之前必须生成密钥(公钥、私钥)操作步骤。
# gpg --gen-key

[chen@linux chen]$ gpg --gen-key

gpg (GnuPG) 1.2.1; Copyright (C) 2002 Free Software Foundation, Inc.

This program comes with ABSOLUTELY NO WARRANTY.

This is free software, and you are welcome to redistribute it

under certain conditions. See the file COPYING for details.

 

gpg: WARNING: using insecure memory!

gpg: please see http://www.gnupg.org/faq.html for more information

gpg: /home/chen/.gnupg: directory created

gpg: new configuration file `/home/chen/.gnupg/gpg.conf' created

gpg: keyblock resource `/home/chen/.gnupg/secring.gpg': file open error

gpg: keyring `/home/chen/.gnupg/pubring.gpg' created

Please select what kind of key you want:

   (1) DSA and ElGamal (default)

   (2) DSA (sign only)

   (5) RSA (sign only)

Your selection? 回车

DSA keypair will have 1024 bits.

About to generate a new ELG-E keypair.

              minimum keysize is  768 bits

              default keysize is 1024 bits

    highest suggested keysize is 2048 bits

What keysize do you want? (1024) 回车

Requested keysize is 1024 bits

Please specify how long the key should be valid.

         0 = key does not expire

      <n>  = key expires in n days

      <n>w = key expires in n weeks

      <n>m = key expires in n months

      <n>y = key expires in n years

Key is valid for? (0) 回车

Key does not expire at all

Is this correct (y/n)? y

 

You need a User-ID to identify your key; the software constructs the user id

from Real Name, Comment and Email Address in this form:

    "Heinrich Heine (Der Dichter) <heinrichh@duesseldorf.de>"

 

Real name: netkiller

Email address: openunix@163.com

Comment: 陈景峰的密钥  (注:输入中文终端要支持UTF-8

You are using the `utf-8' character set.

You selected this USER-ID:

    "netkiller (陈景峰的密钥) <openunix@163.com>"

 

Change (N)ame, (C)omment, (E)mail or (O)kay/(Q)uit?o

 

Enter passphrase:输入密钥口令

Repeat passphrase:输入密钥口令

You need a Passphrase to protect your secret key.

 

We need to generate a lot of random bytes. It is a good idea to perform

some other action (type on the keyboard, move the mouse, utilize the

disks) during the prime generation; this gives the random number

generator a better chance to gain enough entropy.

+++++++++++++++.+++++++++++++++.+++++++++++++++.+++++++++++++++.+++++++++++++++.++++++++++++++++++++++++++++++++++++++++.++++++++++++++++++++.....>+++++..................+++++

 

Not enough random bytes available.  Please do some other work to give

the OS a chance to collect more entropy! (Need 290 more bytes)

We need to generate a lot of random bytes. It is a good idea to perform

some other action (type on the keyboard, move the mouse, utilize the

disks) during the prime generation; this gives the random number

generator a better chance to gain enough entropy.

..+++++.+++++.++++++++++.+++++++++++++++.+++++++++++++++++++++++++..+++++++++++++++.++++++++++++++++++++.+++++.+++++++++++++++.+++++...++++++++++>+++++............................................................>+++++.............>.+++++.....<+++++............+++++^^^

gpg: /home/chen/.gnupg/trustdb.gpg: trustdb created

public and secret key created and signed.

key marked as ultimately trusted.

 

pub  1024D/B00847C5 2003-12-12 netkiller (陈景峰的密钥) <openunix@163.com>

     Key fingerprint = 0058 5847 7598 556F AAFD  81A5 AC07 C873 B008 47C5

sub  1024g/0B70F0CB 2003-12-12

 

[chen@linux chen]$

 

3.         查看生成密钥

[chen@linux chen]$ ls -la

total 52

drwx------    5 chen     chen         4096 Dec 12 20:47 .

drwxr-xr-x    7 root     root         4096 Dec 12 20:44 ..

-rw-r--r--    1 chen     chen           24 Dec 12 20:44 .bash_logout

-rw-r--r--    1 chen     chen          191 Dec 12 20:44 .bash_profile

-rw-r--r--    1 chen     chen          124 Dec 12 20:44 .bashrc

-rw-r--r--    1 chen     chen         5531 Dec 12 20:44 .canna

-rw-r--r--    1 chen     chen          847 Dec 12 20:44 .emacs

drwx------    2 chen     chen         4096 Dec 12 20:52 .gnupg

-rw-r--r--    1 chen     chen          120 Dec 12 20:44 .gtkrc

drwxr-xr-x    3 chen     chen         4096 Dec 12 20:44 .kde

-rw-------    1 chen     chen           61 Dec 12 20:45 .Xauthority

drwxr-xr-x    2 chen     chen         4096 Dec 12 20:44 .xemacs

[chen@linux chen]$ ls .gnupg/

gpg.conf  pubring.gpg  pubring.gpg~  random_seed  secring.gpg  trustdb.gpg

 

 

4.         证书的回收

当您的密钥(gpg --gen-key)生成之后,建议您立即做一个公钥回收证书,如果您忘记了您的私钥口令或者您的私钥丢失或者被盗,您可以发布这个证书来声明以前的公钥不再有效。

gpg --output revoke.asc --gen-revoke netkiller netkiller 你在生成密钥时输入的Real name:

gpg --output revoke.asc --gen-revoke openunix@163.com (使用邮件地址也可以)

[chen@linux chen]$ gpg --output revoke.asc --gen-revoke netkiller

gpg: WARNING: using insecure memory!

gpg: please see http://www.gnupg.org/faq.html for more information

 

sec  1024D/B00847C5 2003-12-12   netkiller (陈景峰的密钥) <openunix@163.com>

 

Create a revocation certificate for this key? y

Please select the reason for the revocation:

  0 = No reason specified

  1 = Key has been compromised

  2 = Key is superseded

  3 = Key is no longer used

  Q = Cancel

(Probably you want to select 1 here)

Your decision?

Enter an optional description; end it with an empty line:

> :( cancel

> 

Reason for revocation: Key has been compromised

:( cancel

Is this okay?

Please select the reason for the revocation:

  0 = No reason specified

  1 = Key has been compromised

  2 = Key is superseded

  3 = Key is no longer used

  Q = Cancel

(Probably you want to select 1 here)

Enter an optional description; end it with an empty line:

> :( cancel

> 

Is this okay? y

 

You need a passphrase to unlock the secret key for

user: "netkiller (陈景峰的密钥) <openunix@163.com>"

1024-bit DSA key, ID B00847C5, created 2003-12-12

 

ASCII armored output forced.

Revocation certificate created.

 

Please move it to a medium which you can hide away; if Mallory gets

access to this certificate he can use it to make your key unusable.

It is smart to print this certificate and store it away, just in case

your media become unreadable.  But have some caution:  The print system of

your machine might store the data and make it available to others!

[chen@linux chen]$ ls

revoke.asc

[chen@linux chen]$ cat revoke.asc

-----BEGIN PGP PUBLIC KEY BLOCK-----

Version: GnuPG v1.2.1 (GNU/Linux)

Comment: A revocation certificate should follow

 

iFIEIBECABIFAj/Zv08LHQI6KCBjYW5jZWwACgkQrAfIc7AIR8X3agCcDBjqRkFx

QUzcZ/1Gyf1/jjFis04An2rYQz2XrCode08Y78Fj63RVNKD9

=ovDh

-----END PGP PUBLIC KEY BLOCK-----

[chen@linux chen]$

 

5.         密钥列表

gpg --list-key

[chen@linux chen]$ gpg --list-key

gpg: WARNING: using insecure memory!

gpg: please see http://www.gnupg.org/faq.html for more information

/home/chen/.gnupg/pubring.gpg

-----------------------------

pub  1024D/B00847C5 2003-12-12 netkiller (陈景峰的密钥) <openunix@163.com>

sub  1024g/0B70F0CB 2003-12-12

 

[chen@linux chen]$

 

6.         输出公钥

ASCII字符格式输出公钥:gpg --output netkiller.gpg --armor --export netkiller

以二进制格式输出公钥:gpg --output netkiller.gpg --export netkiller

下面是以ASCII字符格式输出(其实就是做了一下BASE64编码):

[chen@linux chen]$ gpg --output netkiller.gpg --armor --export netkiller

gpg: WARNING: using insecure memory!

gpg: please see http://www.gnupg.org/faq.html for more information

[chen@linux chen]$ ls

netkiller.gpg  revoke.asc

[chen@linux chen]$ cat netkiller.gpg

-----BEGIN PGP PUBLIC KEY BLOCK-----

Version: GnuPG v1.2.1 (GNU/Linux)

 

mQGiBD/ZuhgRBACBRuWYRtJ8+8VmnYUgNy7TS/nVl0sHrsGD2kgIWVUuZYgKSUoM

vT4MUHWdd52yesovAV61qsVCfUz+O76ovhQrUzv4jp+bkIOKcc7E07Z2MZmc1BqR

+Gavb3gsJM6DmOLcRiU0m3fqod1KCGFf8K6ZLQUhRJYWDI80KEgJqliG4wCgo2xn

5WS1CIGnvGDFUiGY6VhdamsD/jdiqSIcwFt2x6VMjzeWkHHM5wNYHuBJnp9DPd9g

rn3uEq+tSex8ZXRyzHGj+N4SKezhEYal1D762kDxjGYltk5Xce5dXQBn9fulEDhD

OzOp78GvIvJ/m33D/J6xECbXUz8XsFFhxJ6QnVh/RURY+EvHE1Tmz/fRG69Rc1Uc

JBqCA/0faHEkyDv+FWEsmFKjflDNqN5NHtdWzJZQZKD1Vb64oJ5CK6r2l+vmxbBr

fVpfk5OVXnfMSpLKc7aGA9X+mUMuNrGRNzzzsmVK6urWQovL/BfeukMgDBZXkLd8

fO7aA53XeBhmVC49atFPH8hsOeMdd0mombrzcvKczjMp0ThP9rQzbmV0a2lsbGVy

ICjpmYjmma/ls7DnmoTlr4bpkqUpIDxuZXRraWxsZXJAOTgxMi5uZXQ+iFkEExEC

ABkFAj/ZuhgECwcDAgMVAgMDFgIBAh4BAheAAAoJEKwHyHOwCEfFBqMAn0HoK9Xc

zvzVkFODVZPWUskzwAhqAJ4rbgYEjSN1/CrdUBzTMtecGu9P+7kBDQQ/2boaEAQA

zhoIDY866/GWUUpuarpVKcN1ijn+5M1Pr42vm2Z42ns4PZW3cagHJeIOuJ5R2Aw1

6V4zZwP5PcBScYxQpM0m0bVmTGp/suZmZ6/u3+ADgvJYSxAXdpzP0cL9rVRKqaPa

MKh+HOanAJ9tWcSy6KW83JKG2NS/0U6OSGGDSoNLElMAAwUD/iGBjPfXD5jsepg+

Z9J1RefM5/R1nnBEeOROnWyaczIU1okswlyluAthi+2+ijpEULaqSQ+ZjtuBjcMp

kE5UKKql6yBAk2CqJMVkVLlDbPFqbidkAqGp5riKWKc487jR6iZjIAhHvXL0xPIQ

erBmEpi4UT7RlaCAmYwvZ1nxGP3eiEYEGBECAAYFAj/ZuhoACgkQrAfIc7AIR8U0

xACfT5pZ+0YjSp9z0/9jPwDfhw7J1bcAnjqxP+uKfkuDHnXRyYFErTN+7iHE

=CII0

-----END PGP PUBLIC KEY BLOCK-----

[chen@linux chen]$

二进制格式输出:

[chen@linux chen]$ gpg --output bin-netkiller.gpg --export netkiller

gpg: WARNING: using insecure memory!

gpg: please see http://www.gnupg.org/faq.html for more information

[chen@linux chen]$ ls

bin-netkiller.gpg  netkiller.gpg  revoke.asc

[chen@linux chen]$

 

7.         使用file命令识别文件

[chen@linux chen]$ ls

bin-netkiller.gpg  netkiller.gpg  revoke.asc

[chen@linux chen]$ file bin-netkiller.gpg

bin-netkiller.gpg: data

[chen@linux chen]$ file netkiller.gpg

netkiller.gpg: PGP armored data public key block

[chen@linux chen]$ file revoke.asc

revoke.asc: PGP armored data public key block

[chen@linux chen]$

 

8.         发布公钥

你可以将你的公钥放在主页上下载,也可以mail给别人。

[chen@linux chen]$ pine

  PINE 4.44   MAIN MENU                             Folder: INBOX  No Messages

 

 

          ?     HELP               -  Get help using Pine

 

          C     COMPOSE MESSAGE    -  Compose and send a message

 

          I     MESSAGE INDEX      -  View messages in current folder

 

          L     FOLDER LIST        -  Select a folder to view

 

          A     ADDRESS BOOK       -  Update address book

 

          S     SETUP              -  Configure Pine Options

 

          Q     QUIT               -  Leave the Pine program

 

 

 

 

   Copyright 1989-2002.  PINE is a trademark of the University of Washington.

                    [Folder "INBOX" opened with 0 messages]

? Help                     P PrevCmd                 R RelNotes

O OTHER CMDS > [ListFldrs] N NextCmd                 K KBLock

 

  PINE 4.44   COMPOSE MESSAGE                       Folder: INBOX  No Messages

 

To      : openunix@163.com

Cc      :

Attchmnt:

Subject : 这是我的证书

----- Message Text -----

 

Attchmnt

 

 

 

 

 

^G Get Help  ^X Send      ^R Rich Hdr  ^Y PrvPg/Top ^K Cut Line  ^O Postpone

^C Cancel    ^D Del Char  ^J Attach    ^V NxtPg/End ^U UnDel Line^T To Files

光标至于Attchmnt:上按^J -> 再按 ^T

File to attach:

^G Get Help  ^T  To Files

^C Cancel    TAB Complete

 

   PINE 4.44                BROWSER    Dir: /home/chen

 

..             (parent dir)  .gnupg                (dir)

.kde                  (dir)  mail                  (dir)

.xemacs               (dir)  .addressbook           0  B

.addressbook.lu      2.3 KB  .bash_logout          24  B

.bash_profile        191  B  .bashrc              124  B

bin-netkiller.gpg    909  B  .canna               5.5 KB

.emacs               847  B  .gtkrc               120  B

netkiller.gpg        1.3 KB  .pinerc               14 KB

revoke.asc           275  B  .Xauthority           61  B

 

 

 

 

 

 

 

 

 

 

                        [ Searched to end of directory ]

? Get Help   E Exit Brwsr              -   Prev Pg  D Delete     C Copy

             S [Select]   W Where is   Spc Next Pg  R Rename     A Add

选择netkiller.gpg 回车

Attachment comment: my netkiller.gpg file

^G Get Help

^C Cancel

输入注释信息

  PINE 4.44   COMPOSE MESSAGE                       Folder: INBOX  No Messages

 

To      : openunix@163.com

Cc      :

Attchmnt: 1. /home/chen/netkiller.gpg (1.3 KB) "my netkiller.gpg file"

Subject : my netkiller.gpg file

----- Message Text -----

 

http://linux.9812.net

email:openunix@163.com

 

 

 

          [File /home/chen/netkiller.gpg attached as type TEXT/PLAIN]

^G Get Help  ^X Send      ^R Rich Hdr  ^Y PrvPg/Top ^K Cut Line  ^O Postpone

^C Cancel    ^D Del Char  ^J Attach    ^V NxtPg/End ^U UnDel Line^T To Files

 

Send message?y

 ? Help      Y [Yes]

^C Cancel    N No

选择y回车

  PINE 4.44   MAIN MENU                             Folder: INBOX  No Messages

 

 

  PINE 4.44   COMPOSE MESSAGE                       Folder: INBOX  No Messages

 

To      : openunix@163.com

Cc      :

Attchmnt: 1. /home/chen/netkiller.gpg (1.3 KB) "my netkiller.gpg file"

Subject : 这是我的证书

----- Message Text -----

 

Attchmnt

 

 

 

 

 

 

 

                          [Sending mail |     0%   |]

 

 

发送成功

  PINE 4.44   MAIN MENU                             Folder: INBOX  No Messages

 

 

          ?     HELP               -  Get help using Pine

 

          C     COMPOSE MESSAGE    -  Compose and send a message

 

          I     MESSAGE INDEX      -  View messages in current folder

 

          L     FOLDER LIST        -  Select a folder to view

 

          A     ADDRESS BOOK       -  Update address book

 

          S     SETUP              -  Configure Pine Options

 

          Q     QUIT               -  Leave the Pine program

 

 

 

 

   Copyright 1989-2002.  PINE is a trademark of the University of Washington.

                   [Message sent and copied to "sent-mail".]

? Help                     P PrevCmd                 R RelNotes

O OTHER CMDS > [ListFldrs] N NextCmd                 K KBLock

 

 

9.         将公钥给其它用户

[chen@linux chen]$ cp netkiller.gpg /tmp

 

以下是ming帐号的操作:

 

10.     获得公钥

[ming@linux ming]$ cp /tmp/netkiller.gpg .

[ming@linux ming]$ ls

netkiller.gpg

[ming@linux ming]$

 

11.     导入公钥

[ming@linux ming]$ gpg --import netkiller.gpg

gpg: WARNING: using insecure memory!

gpg: please see http://www.gnupg.org/faq.html for more information

gpg: /home/ming/.gnupg: directory created

gpg: new configuration file `/home/ming/.gnupg/gpg.conf' created

gpg: keyblock resource `/home/ming/.gnupg/secring.gpg': file open error

gpg: keyring `/home/ming/.gnupg/pubring.gpg' created

gpg: /home/ming/.gnupg/trustdb.gpg: trustdb created

gpg: key B00847C5: public key "netkiller (▒\x9\x8▒\x9▒▒\x9\x8▒\x8▒\x9▒) <openunix@163.com>" imported

gpg: Total number processed: 1

gpg:               imported: 1

[ming@linux ming]$ gpg --list-key

gpg: WARNING: using insecure memory!

gpg: please see http://www.gnupg.org/faq.html for more information

/home/ming/.gnupg/pubring.gpg

-----------------------------

pub  1024D/B00847C5 2003-12-12 netkiller (陈景峰的密钥) <openunix@163.com>

sub  1024g/0B70F0CB 2003-12-12

 

[ming@linux ming]$

 

12.     确认密钥

导入密钥以后,使用数字签名来验证此证书是否合法。

[ming@linux ming]$ gpg --fingerprint netkiller

gpg: WARNING: using insecure memory!

gpg: please see http://www.gnupg.org/faq.html for more information

pub  1024D/B00847C5 2003-12-12 netkiller (陈景峰的密钥) <openunix@163.com>

     Key fingerprint = 0058 5847 7598 556F AAFD  81A5 AC07 C873 B008 47C5

sub  1024g/0B70F0CB 2003-12-12

 

[ming@linux ming]$

 

13.     密钥签名

导入密钥之后,可以使用(gpg -—sign-key netkiller) 进行签名,签名的主要目的是证明您完全信任这个证书的合法性。

[ming@linux ming]$ gpg --gen-key

gpg (GnuPG) 1.2.1; Copyright (C) 2002 Free Software Foundation, Inc.

This program comes with ABSOLUTELY NO WARRANTY.

This is free software, and you are welcome to redistribute it

under certain conditions. See the file COPYING for details.

 

gpg: WARNING: using insecure memory!

gpg: please see http://www.gnupg.org/faq.html for more information

Please select what kind of key you want:

   (1) DSA and ElGamal (default)

   (2) DSA (sign only)

   (5) RSA (sign only)

Your selection?

DSA keypair will have 1024 bits.

About to generate a new ELG-E keypair.

              minimum keysize is  768 bits

              default keysize is 1024 bits

    highest suggested keysize is 2048 bits

What keysize do you want? (1024)

Requested keysize is 1024 bits

Please specify how long the key should be valid.

         0 = key does not expire

      <n>  = key expires in n days

      <n>w = key expires in n weeks

      <n>m = key expires in n months

      <n>y = key expires in n years

Key is valid for? (0)

Key does not expire at all

Is this correct (y/n)? y

 

You need a User-ID to identify your key; the software constructs the user id

from Real Name, Comment and Email Address in this form:

    "Heinrich Heine (Der Dichter) <heinrichh@duesseldorf.de>"

 

Real name: ming

Name must be at least 5 characters long

Real name: mings

Email address: mings@9812.net

Comment: I am ming

You selected this USER-ID:

    "mings (I am ming) <mings@9812.net>"

 

Change (N)ame, (C)omment, (E)mail or (O)kay/(Q)uit? o

You need a Passphrase to protect your secret key.

Enter passphrase:

 

passphrase not correctly repeated; try again.

We need to generate a lot of random bytes. It is a good idea to perform

some other action (type on the keyboard, move the mouse, utilize the

disks) during the prime generation; this gives the random number

generator a better chance to gain enough entropy.

+++++++++++++++.++++++++++++++++++++++++++++++.++++++++++....++++++++++.++++++++++++++++++++.+++++++++++++++..+++++++++++++++++++++++++.++++++++++.....>..+++++...........................+++++

We need to generate a lot of random bytes. It is a good idea to perform

some other action (type on the keyboard, move the mouse, utilize the

disks) during the prime generation; this gives the random number

generator a better chance to gain enough entropy.

+++++++++++++++++++++++++..++++++++++++++++++++.+++++++++++++++++++++++++++++++++++..+++++++++++++++...+++++++++++++++++++++++++++++++++++>+++++..................+++++^^^^^^^^^^^^^^^

public and secret key created and signed.

key marked as ultimately trusted.

 

pub  1024D/3D9CE6DF 2003-12-12 mings (I am ming) <mings@9812.net>

     Key fingerprint = 51C5 A223 98B8 A65F 4BF4  B610 4B80 D812 3D9C E6DF

sub  1024g/510C2A18 2003-12-12

[ming@linux ming]$ gpg --sign-key netkiller

gpg: WARNING: using insecure memory!

gpg: please see http://www.gnupg.org/faq.html for more information

 

pub  1024D/B00847C5  created: 2003-12-12 expires: never      trust: -/-

sub  1024g/0B70F0CB  created: 2003-12-12 expires: never

(1). netkiller (陈景峰的密钥) <openunix@163.com>

 

 

pub  1024D/B00847C5  created: 2003-12-12 expires: never      trust: -/-

 Primary key fingerprint: 0058 5847 7598 556F AAFD  81A5 AC07 C873 B008 47C5

 

     netkiller (陈景峰的密钥) <openunix@163.com>

 

How carefully have you verified the key you are about to sign actually belongs

to the person named above?  If you don't know what to answer, enter "0".

 

   (0) I will not answer. (default)

   (1) I have not checked at all.

   (2) I have done casual checking.

   (3) I have done very careful checking.

 

Your selection? 3

Are you really sure that you want to sign this key

with your key: "mings (I am ming) <mings@9812.net>"

 

I have checked this key very carefully.

 

Really sign? y

 

You need a passphrase to unlock the secret key for

user: "mings (I am ming) <mings@9812.net>"

1024-bit DSA key, ID 3D9CE6DF, created 2003-12-12

 

Enter passphrase: 注这里输入mings的口令

 

[ming@linux ming]$

[ming@linux ming]$ gpg --list-key

gpg: WARNING: using insecure memory!

gpg: please see http://www.gnupg.org/faq.html for more information

/home/ming/.gnupg/pubring.gpg

-----------------------------

pub  1024D/B00847C5 2003-12-12 netkiller (陈景峰的密钥) <openunix@163.com>

sub  1024g/0B70F0CB 2003-12-12

 

pub  1024D/3D9CE6DF 2003-12-12 mings (I am ming) <mings@9812.net>

sub  1024g/510C2A18 2003-12-12

 

[ming@linux ming]$

 

14.     检查签名

[chen@linux chen]$ gpg --check-sigs netkiller

gpg: WARNING: using insecure memory!

gpg: please see http://www.gnupg.org/faq.html for more information

pub  1024D/B00847C5 2003-12-12 netkiller (陈景峰的密钥) <openunix@163.com>

sig!3       B00847C5 2003-12-12   netkiller (陈景峰的密钥) <openunix@163.com>

sub  1024g/0B70F0CB 2003-12-12

sig!        B00847C5 2003-12-12   netkiller (陈景峰的密钥) <openunix@163.com>

[ming@linux ming]$ gpg --check-sigs netkiller

gpg: WARNING: using insecure memory!

gpg: please see http://www.gnupg.org/faq.html for more information

pub  1024D/B00847C5 2003-12-12 netkiller (陈景峰的密钥) <openunix@163.com>

sig!3       B00847C5 2003-12-12   netkiller (陈景峰的密钥) <openunix@163.com>

sig!        79F1102B 2003-12-12   mings (I am mings) <mings@9812.net>

sub  1024g/0B70F0CB 2003-12-12

sig!        B00847C5 2003-12-12   netkiller (陈景峰的密钥) <openunix@163.com>

 

[ming@linux ming]$

[chen@linux chen]$

 

[ming@linux ming]$ gpg --check-sigs netkiller

gpg: WARNING: using insecure memory!

gpg: please see http://www.gnupg.org/faq.html for more information

pub  1024D/B00847C5 2003-12-12 netkiller (陈景峰的密钥) <openunix@163.com>

sig!3       B00847C5 2003-12-12   netkiller (陈景峰的密钥) <openunix@163.com>

sub  1024g/0B70F0CB 2003-12-12

sig!        B00847C5 2003-12-12   netkiller (陈景峰的密钥) <openunix@163.com>

 

[ming@linux ming]$

 

15.     加密和解密

加密:

[ming@linux ming]$ pg_dump -Unetkiller -h127.0.0.1 >pgsql-dump.sql

Password:

[ming@linux ming]$

 

加签名:

[ming@linux ming]$ gpg -sear netkiller pgsql-dump.sql

gpg: WARNING: using insecure memory!

gpg: please see http://www.gnupg.org/faq.html for more information

 

You need a passphrase to unlock the secret key for

user: "mings (I am mings) <mings@9812.net>"

1024-bit DSA key, ID 79F1102B, created 2003-12-12

 

gpg: checking the trustdb

gpg: checking at depth 0 signed=1 ot(-/q/n/m/f/u)=0/0/0/0/0/1

gpg: checking at depth 1 signed=0 ot(-/q/n/m/f/u)=1/0/0/0/0/0

[ming@linux ming]$

 

不加签名:

[ming@linux ming]$ gpg -ear netkiller pgsql-dump.sql

gpg: WARNING: using insecure memory!

gpg: please see http://www.gnupg.org/faq.html for more information

[ming@linux ming]$ ls

netkiller.gpg  pgsql-dump.sql  pgsql-dump.sql.asc

[ming@linux ming]$

 

加密完成,将文件pgsql-dump.sql.asc发给chen (邮件,WEB/FTP下载。。。都可以,不用担心被其它人得到对你不利,现在这个文件已经加密了。)

以下为chen帐号解密操作:

[chen@linux chen]$ gpg -d pgsql-dump.sql.asc > pgsql-dump.sql

gpg: WARNING: using insecure memory!

gpg: please see http://www.gnupg.org/faq.html for more information

 

You need a passphrase to unlock the secret key for

user: "netkiller (陈景峰的密钥) <openunix@163.com>"

1024-bit ELG-E key, ID 0B70F0CB, created 2003-12-12 (main key ID B00847C5)

 

Enter passphrase:

gpg: encrypted with 1024-bit ELG-E key, ID 0B70F0CB, created 2003-12-12

      "netkiller (陈景峰的密钥) <openunix@163.com>"

[chen@linux chen]$

 

 

2.6    备份计划

2.6.1   服务器端计划

因为每天凌晨1:00-5:00这段时间访问的人比较少,所以我选择服务器端每天凌晨300开始备份,您也可以在其它时间段备份,根据您的需求而定。

[root@linux etc]# cat crontab

SHELL=/bin/bash

PATH=/sbin:/bin:/usr/sbin:/usr/bin

MAILTO=root

HOME=/

 

# run-parts

01 * * * * root run-parts /etc/cron.hourly

02 4 * * * root run-parts /etc/cron.daily

22 4 * * 0 root run-parts /etc/cron.weekly

42 4 1 * * root run-parts /etc/cron.monthly

0 3 * * * root /usr/local/backup/backup.sh

2.6.2   客户端计划

客户端每天零晨400点开始下载备份数据。为什么是400下载呢?因为服务器备份要一段时间,如果服务器还没有备份完成,这边是不能下载的。所以计划在3:00服务器开始备份,400时客户端开始下载已经备份好的数据。

[root@linux etc]# cat crontab

SHELL=/bin/bash

PATH=/sbin:/bin:/usr/sbin:/usr/bin

MAILTO=root

HOME=/

 

# run-parts

01 * * * * root run-parts /etc/cron.hourly

02 4 * * * root run-parts /etc/cron.daily

22 4 * * 0 root run-parts /etc/cron.weekly

42 4 1 * * root run-parts /etc/cron.monthly

0 4 * * * root /usr/local/backup/getbackup.sh

2.7    数据恢复

[root@linux root]# su postgres

bash-2.05b$ psql member -f pgsql-backup.xxxx-xx-xx.xx:xx:xx.dmp

 

2.8    性能提升

2.8.1   共享内存

2.2 内核里缺省的共享内存限制( SHMMAX SHMALL)都是 32 MB,但是你可以在 proc 文件系统里修改这些值(不用重起). 比如,要允许 128 MB

方法1

# echo 134217728 >/proc/sys/kernel/shmall

# echo 134217728 >/proc/sys/kernel/shmmax

[root@linux root]# cat /proc/sys/kernel/shmall

2097152

[root@linux root]# cat /proc/sys/kernel/shmmax

33554432

[root@linux root]# echo 134217728 >/proc/sys/kernel/shmall

[root@linux root]# echo 134217728 >/proc/sys/kernel/shmmax

[root@linux root]# cat /proc/sys/kernel/shmall

134217728

[root@linux root]# cat /proc/sys/kernel/shmmax

134217728

 

你可以把这些命令放到一个引导时运行的脚本中. rc.local文件

[root@linux root]# cat /etc/rc.d/rc.local

#!/bin/sh

#

# This script will be executed *after* all the other init scripts.

# You can put your own initialization stuff in here if you don't

# want to do the full Sys V style init stuff.

 

touch /var/lock/subsys/local

/usr/local/jakarta-tomcat/bin/startup.sh

/usr/local/apache/bin/apachectl start

echo 134217728 >/proc/sys/kernel/shmall

echo 134217728 >/proc/sys/kernel/shmmax

 

方法2,使用 sysctl 命令来控制这些参数.

[root@linux root]# sysctl -w kernel.shmall=134217728

kernel.shmall = 134217728

[root@linux root]# sysctl -w kernel.shmmax=134217728

kernel.shmmax = 134217728

[root@linux root]#

 

方法3,你可以在一个叫 /etc/sysctl.conf 的文件里面加下面这样的两行:

kernel.shmall = 134217728

kernel.shmmax = 134217728

[root@linux root]# cat /etc/sysctl.conf

# Kernel sysctl configuration file for Red Hat Linux

#

# For binary values, 0 is disabled, 1 is enabled.  See sysctl(8) and

# sysctl.conf(5) for more details.

 

# Controls IP packet forwarding

net.ipv4.ip_forward = 0

 

# Controls source route verification

net.ipv4.conf.default.rp_filter = 1

 

# Controls the System Request debugging functionality of the kernel

kernel.sysrq = 0

 

# Controls whether core dumps will append the PID to the core filename.

# Useful for debugging multi-threaded applications.

kernel.core_uses_pid = 1

kernel.shmall = 134217728

kernel.shmmax = 134217728

通常在引导的时候会处理这个文件,但你也可以稍后明确调用sysctl

2.8.2   最大连接

跟据你的需要来配置最大连接数,系统默认是32,配置需要修改两处。

max_connections = 100

shared_buffers = 200

shared_buffers = max_connections*2

[root@linux data]# cat postgresql.conf

#

# PostgreSQL configuration file

# -----------------------------

#

# This file consists of lines of the form:

#

#   name = value

#

# (The '=' is optional.) White space may be used. Comments are introduced

# with '#' anywhere on a line. The complete list of option names and

# allowed values can be found in the PostgreSQL documentation. The

# commented-out settings shown in this file represent the default values.

#

# Any option can also be given as a command line switch to the

# postmaster, e.g. 'postmaster -c log_connections=on'. Some options

# can be changed at run-time with the 'SET' SQL command.

#

# This file is read on postmaster startup and when the postmaster

# receives a SIGHUP. If you edit the file on a running system, you have

# to SIGHUP the postmaster for the changes to take effect, or use

# "pg_ctl reload".

 

 

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

 

 

#

#       Connection Parameters

#

#tcpip_socket = false

 

tcpip_socket = true

 

#ssl = false

#ssl = true

 

#max_connections = 32

max_connections = 100

#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 = 200            # 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

 

#

#       Non-shared Memory Sizes

#

#sort_mem = 1024                # min 64, size in KB

#vacuum_mem = 8192              # min 1024, size in KB

 

 

#

#       Write-ahead log (WAL)

#

#checkpoint_segments = 3        # in logfile segments, min 1, 16MB each

#checkpoint_timeout = 300       # range 30-3600, in seconds

#

#commit_delay = 0               # range 0-100000, in microseconds

#commit_siblings = 5            # range 1-1000

#

#fsync = true

#wal_sync_method = fsync        # the default varies across platforms:

#                               # fsync, fdatasync, open_sync, or open_datasync

#wal_debug = 0                  # range 0-16

 

 

#

#       Optimizer Parameters

#

#enable_seqscan = true

#enable_indexscan = true

#enable_tidscan = true

#enable_sort = true

#enable_nestloop = true

#enable_mergejoin = true

#enable_hashjoin = true

 

#effective_cache_size = 1000    # typically 8KB each

#random_page_cost = 4           # units are one sequential page fetch cost

#cpu_tuple_cost = 0.01          # (same)

#cpu_index_tuple_cost = 0.001   # (same)

#cpu_operator_cost = 0.0025     # (same)

 

#default_statistics_target = 10 # range 1-1000

 

#

#       GEQO Optimizer Parameters

#

#geqo = true

#geqo_selection_bias = 2.0      # range 1.5-2.0

#geqo_threshold = 11

#geqo_pool_size = 0             # default based on tables in statement,

                                # range 128-1024

#geqo_effort = 1

#geqo_generations = 0

#geqo_random_seed = -1          # auto-compute seed

 

 

#

#       Message display

#

#server_min_messages = notice   # Values, in order of decreasing detail:

                                #   debug5, debug4, debug3, debug2, debug1,

                                #   info, notice, warning, error, log, fatal,

                                #   panic

#client_min_messages = notice   # Values, in order of decreasing detail:

                                #   debug5, debug4, debug3, debug2, debug1,

                                #   log, info, notice, warning, error

#silent_mode = false

 

#log_connections = false

#log_pid = false

#log_statement = false

#log_duration = false

#log_timestamp = false

 

#log_min_error_statement = panic # Values in order of increasing severity:

                                 #   debug5, debug4, debug3, debug2, debug1,

                                 #   info, notice, warning, error, panic(off)

 

#debug_print_parse = false

#debug_print_rewritten = false

#debug_print_plan = false

#debug_pretty_print = false

 

#explain_pretty_print = true

 

# requires USE_ASSERT_CHECKING

#debug_assertions = true

 

 

#

#       Syslog

#

#syslog = 0                     # range 0-2

#syslog_facility = 'LOCAL0'

#syslog_ident = 'postgres'

 

 

#

#       Statistics

#

#show_parser_stats = false

#show_planner_stats = false

#show_executor_stats = false

#show_statement_stats = false

 

# requires BTREE_BUILD_STATS

#show_btree_build_stats = false

 

 

#

#       Access statistics collection

#

#stats_start_collector = true

#stats_reset_on_server_start = true

#stats_command_string = false

#stats_row_level = false

#stats_block_level = false

 

 

#

#       Lock Tracing

#

#trace_notify = false

 

# requires LOCK_DEBUG

#trace_locks = false

#trace_userlocks = false

#trace_lwlocks = false

#debug_deadlocks = false

#trace_lock_oidmin = 16384

#trace_lock_table = 0

 

 

#

#       Misc

#

#autocommit = true

#dynamic_library_path = '$libdir'

#search_path = '$user,public'

#datestyle = 'iso, us'

#timezone = unknown             # actually, defaults to TZ environment setting

#australian_timezones = false

#client_encoding = sql_ascii    # actually, defaults to database encoding

#authentication_timeout = 60    # 1-600, in seconds

#deadlock_timeout = 1000        # in milliseconds

#default_transaction_isolation = 'read committed'

#max_expr_depth = 10000         # min 10

#max_files_per_process = 1000   # min 25

#password_encryption = true

#sql_inheritance = true

#transform_null_equals = false

#statement_timeout = 0          # 0 is disabled, in milliseconds

#db_user_namespace = false

 

 

 

#

#       Locale settings

#

# (initialized by initdb -- may be changed)

LC_MESSAGES = 'en_US.UTF-8'

LC_MONETARY = 'en_US.UTF-8'

LC_NUMERIC = 'en_US.UTF-8'

LC_TIME = 'en_US.UTF-8'

 

 

重新启动数据:

[root@linux data]# service postgresql restart

                                                     [  OK  ]

Starting postgresql service:                               [  OK  ]

 

查看配置是否正确:

[root@linux root]# psql -Uchen member

Welcome to psql 7.3.3, 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

 

member=> show max_connections;

 max_connections

-----------------

 100

(1 row)

2.8.3   vacuumdb

数据库200优化、300备份、400下载备份数据

[root@linux etc]# cat crontab

SHELL=/bin/bash

PATH=/sbin:/bin:/usr/sbin:/usr/bin

MAILTO=root

HOME=/

 

# run-parts

01 * * * * root run-parts /etc/cron.hourly

02 4 * * * root run-parts /etc/cron.daily

22 4 * * 0 root run-parts /etc/cron.weekly

42 4 1 * * root run-parts /etc/cron.monthly

0 2 * * * root /usr/local/pgsql/optimize.sh

 

数据库vacuumdb优化脚本

[root@linux pgsql]# cat optimize.sh

#!/bin/bash

vacuumdb -hlocalhost -p5432 -Upostgres -a -f -z

[root@linux pgsql]#

2.8.4   数据库操作与性能

1.         分组插入数据

向数据库做大量Insert 操作时(注:非导入,在某些特殊环境中要做大量的插入操作,而   不是导入数据),如你有10000条记录要插入到数据库中,建议你将10000记录分组插入

第一组

 

begin;

insert into ……

insert into ……

insert into ……

…….

1000insert into

…….

insert into ……

commit;

第二组

begin;

insert into ……

…….

1000insert into

…….

insert into ……

commit;

第十组

begin;

insert into ……

…….

1000insert into

…….

insert into ……

commit;

 

2.         通过copy from插入数据

pg_copy_from

(PHP 4 >= 4.2.0)

 

pg_copy_from --  根据数组将记录插入表中

说明

bool pg_copy_from ( resource connection, string table_name, array rows [, string delimiter [, string null_as]])

pg_copy_from() 将数组 rows 的内容作为记录插入表中。它在内部使用了 COPY FROM SQL 命令来插入记录。如果成功则返回 TRUE,失败则返回 FALSE

                      

参见 pg_copy_to()

3.         操作之后使用重建索引
vacuumdb -hlocalhost -p5432 -Upostgres -a -f -z

2.8.5   硬件方面

1.         一般服务器

PC服务器有条件建议使用SATA(串行)硬盘。

没有条件可以买时下最快的ATA硬盘(也不是越快越好,还要稳定)

正常情况下几块ATA 66 (5400rpm)硬盘做RAID 0,要比一块ATA100(7200rpm)还要快。

RAID 是解决服务器硬盘瓶颈最佳方案。建议使用RAID0,RAID0速度最快,不安全,但速度诱人,只要做好备份,是没有问题的。
目前PC上ATA只能做RAID0(条带)和RAID1(镜像)。SATA可以做RAID0,1,5,10,50。不过我还没见过STAT 的RAID卡,深圳赛格也没得卖。

2.         高档服务器

高档服务器中主流使用SCSI硬盘,公司也出得起¥¥¥买。所以干脆一次就配置5块SCSI硬盘。4块盘做RAID5,剩余1块做热交换hotswap

因为SCSI性能稳定,如果不满足RAID5速度,可以做RAID0。

 

附表1

RAID级别

RAID 0

RAID 1

RAID 3

RAID 5

名称

条带

镜像

专用校验条带

分散校验条带

允许故障

冗余类型

副本

校验

校验

热备用操作

不可

可以

可以

可以

硬盘数量

一个以上

两个

三个以上

三个以上

可用容量

最大

最小

中间

中间

减少容量

50%

一个磁盘

一个磁盘

读性能

高(盘的数量决定)

中间

随机写性能

最高

中间

最低

连续写性能

最高

中间

最低

典型应用

无故障的迅速读写

允许故障的小檔、随机数据写入

允许故障的大檔、连续数据传输

允许故障的小檔、随机数据传输

可用容量

总的磁盘的容量

只能用磁盘容量的50%

n-1/n的磁盘容量。其中n为磁盘数

n-1/n的总磁盘容量。其中n为磁盘数

  附表2

RAID级别

RAID 10

RAID 30

RAID 50

名称

跨越镜像数组

跨越专用校验数组

跨越分散校验数组

允许故障

冗余类型

副本

校验

校验

热备用操作

可以

可以

可以

磁盘数量

 

 

 

跨越2个数组

4

6,8,10,12,1416

6,8,10,12,1416

跨越3个数组

6

9,1215

9,1215

跨越4个数组

8

1216

1216

可用容量

最小

中间

中间

减少容量

50%

每个数组中一个磁盘

每个数组中一个磁盘

读性能

中间

随机写性能

中间

最低

连续写性能

中间

最低

典型应用

允许故障高速度小文件、随机数据写入

允许故障高速度大文件、连续数据传输

允许故障高速度小文件、随机数据传输

可用容量

磁盘容量的50%

n-2/2的磁盘容量。其中n为磁盘数目

n-2/n的磁盘容量。其中n为磁盘数

 

3.         网络,光纤存储我没使用过,这里也不谈了。

2.8.6   磁盘性能

注:hdparm 有些参考只支持ATA硬盘。

[root@linux root]# hdparm

 

hdparm - get/set hard disk parameters - version v5.2

 

Usage:  hdparm  [options] [device] ..

 

Options:

 -a   get/set fs readahead

 -A   set drive read-lookahead flag (0/1)

 -b   get/set bus state (0 == off, 1 == on, 2 == tristate)

 -B   set Advanced Power Management setting (1-255)

 -c   get/set IDE 32-bit IO setting

 -C   check IDE power mode status

 -d   get/set using_dma flag

 -D   enable/disable drive defect-mgmt

 -E   set cd-rom drive speed

 -f   flush buffer cache for device on exit

 -g   display drive geometry

 -h   display terse usage information

 -i   display drive identification

 -I   detailed/current information directly from drive

 -Istdin  similar to -I, but wants /proc/ide/*/hd?/identify as input

 -k   get/set keep_settings_over_reset flag (0/1)

 -K   set drive keep_features_over_reset flag (0/1)

 -L   set drive doorlock (0/1) (removable harddisks only)

 -M   get/set acoustic management (0-254, 128: quiet, 254: fast) (EXPERIMENTAL)

 -m   get/set multiple sector count

 -n   get/set ignore-write-errors flag (0/1)

 -p   set PIO mode on IDE interface chipset (0,1,2,3,4,...)

 -P   set drive prefetch count

 -q   change next setting quietly

 -Q   get/set DMA tagged-queuing depth (if supported)

 -r   get/set readonly flag (DANGEROUS to set)

 -R   register an IDE interface (DANGEROUS)

 -S   set standby (spindown) timeout

 -t   perform device read timings

 -T   perform cache read timings

 -u   get/set unmaskirq flag (0/1)

 -U   un-register an IDE interface (DANGEROUS)

 -v   defaults; same as -mcudkrag for IDE drives

 -V   display program version and exit immediately

 -w   perform device reset (DANGEROUS)

 -W   set drive write-caching flag (0/1) (DANGEROUS)

 -x   tristate device for hotswap (0/1) (DANGEROUS)

 -X   set IDE xfer mode (DANGEROUS)

 -y   put IDE drive in standby mode

 -Y   put IDE drive to sleep

 -Z   disable Seagate auto-powersaving mode

 -z   re-read partition table

 

测试 /dev/hda 这块硬盘的cache与实际效能:

[root@linux root]# hdparm -Tt /dev/hda

 

/dev/hda:

 Timing buffer-cache reads:   128 MB in  0.26 seconds =492.31 MB/sec

 Timing buffered disk reads:  64 MB in  2.28 seconds = 28.07 MB/sec

[root@linux root]#

 

关闭 DMA 模式!

[root@linux root]# hdparm -d0 /dev/hda

 

/dev/hda:

 setting using_dma to 0 (off)

 using_dma    =  0 (off)

[root@linux root]#

 

开启 DMA 模式在 DMA 66 ,并且开启 32 位存取模式

[root@linux root]# hdparm -d1 -c3 -X66 /dev/hda

 

/dev/hda:

 setting 32-bit IO_support flag to 3

 setting using_dma to 1 (on)

 setting xfermode to 66 (UltraDMA mode2)

 IO_support   =  3 (32-bit w/sync)

 using_dma    =  1 (on)

[root@linux root]#

因为可能这个程序比较早,没有后续版本,所以他只支持到66

[root@linux root]# hdparm -d1 -c3 -X100 /dev/hda

 

/dev/hda:

 setting 32-bit IO_support flag to 3

 setting using_dma to 1 (on)

 setting xfermode to 100 (unknown, probably not valid)

 HDIO_DRIVE_CMD(setxfermode) failed: Input/output error

 IO_support   =  3 (32-bit w/sync)

 using_dma    =  1 (on)

[root@linux root]#

 

这是我的PC服务器A

       CPUP4 2.6G
      
内存:512MB

       硬盘:70GB ATA133

[root@linux root]# hdparm -d1 -Tt -c3 /dev/hda                                 

/dev/hda:

 setting 32-bit IO_support flag to 3

 setting using_dma to 1 (on)

 IO_support   =  3 (32-bit w/sync)

 using_dma    =  1 (on)

 Timing buffer-cache reads:   128 MB in  0.25 seconds =512.00 MB/sec

 Timing buffered disk reads:  64 MB in  2.28 seconds = 28.07 MB/sec

[root@linux root]#

这是一台深圳产的宝德PC服务器B

       CPUP4 2.4G

       内存:1GB

       ATA RAID 0 120GATA100*2

[root@linux root]# hdparm -d1 -Tt -c3 /dev/sda

 

/dev/sda:

 operation not supported on SCSI disks

[root@linux root]#

 

[root@linux root]# hdparm -Tt /dev/sda

 

/dev/sda:

 Timing buffer-cache reads:   128 MB in  0.33 seconds =392.46 MB/sec

 Timing buffered disk reads:  64 MB in  1.00 seconds = 63.87 MB/sec

[root@linux root]# hdparm -Tt /dev/sda

 

/dev/sda:

 Timing buffer-cache reads:   128 MB in  0.33 seconds =392.46 MB/sec

 Timing buffered disk reads:  64 MB in  1.02 seconds = 62.77 MB/sec

[root@linux root]#

 

上面两台PC服务器,A最近配置的,B是半年前配置的,从对比可以看出两块ATA100RAID0,与单块ATA133。前者慢,但我想如果用4块硬盘做RAID 0 性能一定会超过A

2.9    安全的TCP/IP联接

2.9.1   使用SSL进行安全的TCP/IP联接

1.         设置用户信息:

[root@linux8 root]# su - postgres

-bash-2.05b$ ls

data  initdb.i18n

-bash-2.05b$ cd data/

-bash-2.05b$ ls

base    pg_clog      pg_ident.conf  pg_xlog          postmaster.opts

global  pg_hba.conf  PG_VERSION     postgresql.conf  postmaster.pid

-bash-2.05b$ openssl req -new -text -out server.req

Using configuration from /usr/share/ssl/openssl.cnf

Generating a 1024 bit RSA private key

....++++++

............................................................++++++

writing new private key to 'privkey.pem'

Enter PEM pass phrase:

Verifying password - Enter PEM pass phrase:

-----

You are about to be asked to enter information that will be incorporated

into your certificate request.

What you are about to enter is what is called a Distinguished Name or a DN.

There are quite a few fields but you can leave some blank

For some fields there will be a default value,

If you enter '.', the field will be left blank.

-----

Country Name (2 letter code) [GB]:CN

State or Province Name (full name) [Berkshire]:Guang Zhou

Locality Name (eg, city) [Newbury]:Shen Zhen

Organization Name (eg, company) [My Company Ltd]:Open Source Organization

Organizational Unit Name (eg, section) []:technical

Common Name (eg, your name or your server's hostname) []:www.9812.net

Email Address []:openunix@163.com

 

Please enter the following 'extra' attributes

to be sent with your certificate request

A challenge password []:chen

An optional company name []:netkiller

-bash-2.05b$ ls

base    pg_clog      pg_ident.conf  pg_xlog          postmaster.opts  privkey.pem

global  pg_hba.conf  PG_VERSION     postgresql.conf  postmaster.pid   server.req

-bash-2.05b$

 

注意上面的server.req文件,我们来看看它的内容:

-bash-2.05b$ cat server.req

Certificate Request:

    Data:

        Version: 0 (0x0)

        Subject: C=CN, ST=Guang Zhou, L=Shen Zhen, O=Open Source Organization, OU=technical, CN=www.9812.net/Email=openunix@163.com

        Subject Public Key Info:

            Public Key Algorithm: rsaEncryption

            RSA Public Key: (1024 bit)

                Modulus (1024 bit):

                    00:a5:30:9a:ef:75:9f:40:40:ee:90:4e:06:f7:f7:

                    0b:de:97:d0:1a:2e:48:ef:4c:7b:c2:cd:f2:f4:30:

                    1b:f4:c7:9d:65:7a:53:d7:d7:7c:ea:25:8f:be:b0:

                    57:f5:89:91:2e:80:4c:ff:f1:96:1e:42:06:01:64:

                    9f:98:69:24:c1:7f:e6:0c:a5:ae:b9:9c:4c:29:db:

                    a3:a3:3d:76:da:89:c0:33:29:c5:a5:8b:7a:e1:e5:

                    f4:3b:f3:7d:54:d4:65:fa:c8:c0:1c:11:07:1c:24:

                    03:8e:f0:61:d9:70:cf:fa:dd:e2:04:4a:31:c2:63:

                    2a:5f:44:ec:48:68:30:44:8d

                Exponent: 65537 (0x10001)

        Attributes:

            challengePassword        :chen

            unstructuredName         :netkiller

    Signature Algorithm: md5WithRSAEncryption

        09:4a:1c:e5:87:7a:9c:6f:69:ed:cd:11:8d:b6:bc:da:e0:4a:

        f5:7a:33:70:0d:5f:28:63:82:79:39:6b:a5:ae:02:7b:87:cb:

        86:74:2e:2b:eb:ec:23:3b:dc:02:25:29:02:74:e7:92:76:ed:

        34:e1:63:e9:ef:dc:12:33:31:84:31:ce:b3:d4:f2:49:92:a5:

        2c:5e:0a:3d:73:f8:1f:95:8f:71:f9:2d:ee:eb:4a:9c:8c:13:

        a5:26:a2:d2:49:c3:7e:69:c7:1b:73:bb:59:8d:9c:bf:dd:ac:

        4b:c4:41:02:b1:3c:a6:c9:c9:eb:00:b3:75:2d:e2:ab:29:b3:

        85:75

-----BEGIN CERTIFICATE REQUEST-----

MIICFzCCAYACAQAwgacxCzAJBgNVBAYTAkNOMRMwEQYDVQQIEwpHdWFuZyBaaG91

MRIwEAYDVQQHEwlTaGVuIFpoZW4xITAfBgNVBAoTGE9wZW4gU291cmNlIE9yZ2Fu

aXphdGlvbjESMBAGA1UECxMJdGVjaG5pY2FsMRUwEwYDVQQDEwx3d3cuOTgxMi5u

ZXQxITAfBgkqhkiG9w0BCQEWEm5ldGtpbGxlckA5ODEyLm5ldDCBnzANBgkqhkiG

9w0BAQEFAAOBjQAwgYkCgYEApTCa73WfQEDukE4G9/cL3pfQGi5I70x7ws3y9DAb

9MedZXpT19d86iWPvrBX9YmRLoBM//GWHkIGAWSfmGkkwX/mDKWuuZxMKdujoz12

2onAMynFpYt64eX0O/N9VNRl+sjAHBEHHCQDjvBh2XDP+t3iBEoxwmMqX0TsSGgw

RI0CAwEAAaAvMBMGCSqGSIb3DQEJBzEGEwRjaGVuMBgGCSqGSIb3DQEJAjELEwlu

ZXRraWxsZXIwDQYJKoZIhvcNAQEEBQADgYEACUoc5Yd6nG9p7c0Rjba82uBK9Xoz

cA1fKGOCeTlrpa4Ce4fLhnQuK+vsIzvcAiUpAnTnknbtNOFj6e/cEjMxhDHOs9Ty

SZKlLF4KPXP4H5WPcfkt7utKnIwTpSai0knDfmnHG3O7WY2cv92sS8RBArE8psnJ

6wCzdS3iqymzhXU=

-----END CERTIFICATE REQUEST-----

 

2.         生产秘钥文件:

-bash-2.05b$ openssl rsa -in privkey.pem -out server.key

read RSA key

Enter PEM pass phrase:

writing RSA key

-bash-2.05b$ ls

base    pg_clog      pg_ident.conf  pg_xlog          postmaster.opts  privkey.pem  server.req

global  pg_hba.conf  PG_VERSION     postgresql.conf  postmaster.pid   server.key

-bash-2.05b$

 

注意上面的privkey.pem server.key文件,我们来看看它们的内容:

-bash-2.05b$ cat privkey.pem

-----BEGIN RSA PRIVATE KEY-----

Proc-Type: 4,ENCRYPTED

DEK-Info: DES-EDE3-CBC,EE59B06E786A2FCA

 

C8RnlMX5tF7CRdx/jxHk/2D4SUu+PVNfphwDbsytmUJIx5qMQAHxCy+NdIDZX9L/

AWIwaShdwFOaP6CMwrzBav54DW1/IlbF688X3DA6xUY1+ZvV4RU4t1O6EhEPINth

1KBqgtSw8lnu6HQa6aIFvZ4f/Wqluk04ylGe4CLLW1xPQ36ntw3tFXPm3eIFl3wQ

lNxYjNTxSjA9x5IBzyJpaJJk27f+/WJARDkFKOwUn9J71lPC5yYybv6IG65xFg6/

kpLqfzx/wAaJxReB/EP95jLVkEmzyi6rqzsBLLgAl6mxGGN5kT34lfK4v9xuRWRz

J2AlBJnloq8NTE48N2g7N1UqHl0r3nNkLdfYEeq6th7d12hiSAcGECvSfhlWirsx

sFYcrAhBGCK+4OXjn717AYeAYw+/JPrX0ZuDVFogVKNB9x/S15+y8yh2AgIUjpJ/

BOZ3LCxXyFznu4yBvxNoTOJT2xWuAXVk5AI3UftOfBAvRZdayAwh6LdoNG77ead1

hNwIAvS5LUiLG8KeAbQHlJuh51YCpmEBCsTqrZybMNoEAiCg0Gn/5tE5cfVmH3Ei

LjhCTtRJ6oGx6dsYaY4A1Jt1+B8DMNnRTez8NN5D2+4wasr4dTYwsRXRyqMCPZJH

+z8m6zautVoHlhGQhRxO4ZcBunyJrdW5XQBGfAcUbp1xORCvqP+SW8Z4wDyu2Sk+

MlxPL1T4P3xEANG7hOlsabBiQ2kyCq1iiJCHBlfXxIm86c1ffRYTrdB+PoFyyaII

ErS68kMbv+Y5Tr+X3Ml1AMNEEU5YAn/O1wSoL5Cz0nIpKeknKAl/vA==

-----END RSA PRIVATE KEY-----

 

-bash-2.05b$ cat server.key

-----BEGIN RSA PRIVATE KEY-----

MIICXAIBAAKBgQClMJrvdZ9AQO6QTgb39wvel9AaLkjvTHvCzfL0MBv0x51lelPX

13zqJY++sFf1iZEugEz/8ZYeQgYBZJ+YaSTBf+YMpa65nEwp26OjPXbaicAzKcWl

i3rh5fQ7831U1GX6yMAcEQccJAOO8GHZcM/63eIESjHCYypfROxIaDBEjQIDAQAB

AoGABMbGBByLkUkPXN7UtsDO+A29t7QU6c51Wamo18S4WjiXZYLG/9u8Qez6HhJt

SK1EpGqTT2dF5vQTxmCJeNe5d078YIFCbIQckgG2hLSsRyV8QclSguJLC5Tgvzua

tTFdVH50UbyAtkifiR3wt5qBuIjtxz/v0ePJ2EdhcdCAqQUCQQDUarpjOof/hTKb

wwOyJIVDycQs27dF+LiGD6YxD97WC6iZR5u7YukqzJk+GXi9EbjdQzybkp1oxDuF

LQAFXJoDAkEAxxVCo1MgYiKtc2lqSr/q2j1R//sPQq5ajv7pvU1WGhx3xS2iZt9l

/jzNx6ZUG7hxd5gi6G6I3UFAFoOLq06qLwJAPT7InvOxYqs0/FQuLJ77DaCPP5/a

KAKesYixklPRHEYgRpGvBUhvkjeLt6wAdAM4GhPY1cJgQGTUBIIFD4azoQJAVjap

xgrwoi78SFelVTupW9tkUGOL50eUJgrUdEsyd1pOr9AkXUJva9svDj/EesC0OqNi

sp9zm8VvGJDdAlGttwJBAMEnnl9ZGglIBRbS7srVLHhXFYs+xkQgTW6bvcQ+aW+G

MW/vpVcsFzSuaAtlBVoZ1ltCRGPSbVgQkp14yqGITQg=

-----END RSA PRIVATE KEY-----

 

上面的privkey.pem server.key文件内容一看就知道是BASE64编码的,我对它的内容也很好奇,将它解码看看内容是什么:

0?\[1][1]?u@N ?.H{峦螋0羟漞zS|?従痒鯄?€L‑Bdi$??ギ箿L)?v?)钮娲徨?T詄?$


a赏销葩
J1
*_Dh0D?
[1]



I\拊独硒椒?uY啄窜8梕偲A‑mH璂揙gE芵墄坠wN黗丅l??秳船G%|A蒖傗K ?1]T~tQ紑禜焿鸱殎笀砬?镅闵溪aq?[1]A詊篶:??
?
匔赡,E?? Gb?>x?C<洅汉??\?[1]A?B b"ijJ?Q?B罂廅榻MVw?沓?颓w?镴堓A@儖玁?[1]@=>葹蟊b?.,烕爮?([1]灡埍扴?F FHo?t
8
卣耟@d???@V6 .W;踕PcG? K2wZN$]Bok?
?胶来:矡so[1]Q[1]A?Y H翌收,xW? Mn?io?oW,4 eZ諿BDcXx省圡

看来是二制的,哈哈。

3.         产生证书文件:

-bash-2.05b$ openssl req -x509 -in server.req -text -key server.key -out server. crt

Using configuration from /usr/share/ssl/openssl.cnf

-bash-2.05b$

-bash-2.05b$ cat server.crt

Certificate:

    Data:

        Version: 3 (0x2)

        Serial Number: 0 (0x0)

        Signature Algorithm: md5WithRSAEncryption

        Issuer: C=CN, ST=Guang Zhou, L=Shen Zhen, O=Open Source Organization, OU=technical, CN=www.9812.net/Email=openunix@163.com

        Validity

            Not Before: Oct 25 01:13:05 2003 GMT

            Not After : Nov 24 01:13:05 2003 GMT

        Subject: C=CN, ST=Guang Zhou, L=Shen Zhen, O=Open Source Organization, OU=technical, CN=www.9812.net/Email=openunix@163.com

        Subject Public Key Info:

            Public Key Algorithm: rsaEncryption

            RSA Public Key: (1024 bit)

                Modulus (1024 bit):

                    00:a5:30:9a:ef:75:9f:40:40:ee:90:4e:06:f7:f7:

                    0b:de:97:d0:1a:2e:48:ef:4c:7b:c2:cd:f2:f4:30:

                    1b:f4:c7:9d:65:7a:53:d7:d7:7c:ea:25:8f:be:b0:

                    57:f5:89:91:2e:80:4c:ff:f1:96:1e:42:06:01:64:

                    9f:98:69:24:c1:7f:e6:0c:a5:ae:b9:9c:4c:29:db:

                    a3:a3:3d:76:da:89:c0:33:29:c5:a5:8b:7a:e1:e5: