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:

                    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