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 新增功能... 93

3.9.4       層次遞迴-分類目錄... 93

3.9.5       總結... 101

3.10        模式... 101

3.10.1     創建模式... 101

3.10.2     刪除模式... 101

3.10.3     模式搜索路徑... 102

4     實體關係(Entity-Relation... 104

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個陣列