PostgreSQL使用注意事項

1.log日誌配置
PostgreSQL的系統日誌和報錯日誌等，預設是直接輸出了，沒有寫入到檔案，如果你希望能查看這些日誌，需要修改postgresql.conf，如下：

log_destination = ’stderr’
logging_collector = on
log_directory = ‘pg_log’
log_min_duration_statement = 5000

上面這些參數的意思就是，把pgsql的日誌到輸出到pgsql資料目錄的pg_log目錄下，同時超過5000毫秒的操作將記錄。

 
2.使用者認證和監聽

PostgreSQL的使用者認證是通過pg_hba.conf來設定，預設都是trust，即本地帳戶能直接登入。顯然不安全，你可修改如下：

# “local” is for Unix domain socket connections only
local   all         all                                 md5
# IPv4 local connections:
host    all         all         127.0.0.1/32            md5
host    all         all         192.168.1.0/24          md5

上面這些參數的意思是，本地串連pgsql要求輸入密碼md5認證，192.168.1.xx 網段允許串連，密碼也需要通過md5認證。
如果需要從其他用戶端串連，還需要修改postgresql.conf的liston_addresses參數，這個參數和oracle的監聽的作用類似。
listen_addresses = ‘*’

 

3.密碼檔案

通過2設定成md5驗證後，雖然在安全方面有了提高，但是同時會造成一些麻煩，比如你的shell指令碼需要使用psql能直接連接，這種情況你可以使用密碼檔案。通過在使用者的根目錄下，建立一個.pgpass檔案。並將使用權限設定為0600。

檔案的格式如下：
hostname:port:database:username:password

 

4.psql常用容易混淆的命令

通過psql登入到PostgreSQL，有些命令可能和其他資料庫有些區別，這裡和mysql對比下，介紹幾個你很可能搞混的命令：
pgsql             mysql
/c                use
/q                quit
/d                desc

 

5.系統參數配置

PostgreSQL在安裝的時候，並沒有要求修改系統預設配置，那是因為其預設的記憶體等參數很小，不會超過系統的預設配置。例如你加大shared_buffers等參數的值時，基本都會超過預設系統參數。這時你需要修改這些配置
編輯 /etc/sysctl.conf，加入以下內容：
kernel.shmall = 3145728
kernel.shmmax = 12884901888
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144
存檔退出後運行這個指令檢查是否設定正確：
#sysctl -p

 

6.過程語言
PostgreSQL的預存程序和函數統一就叫function，當然傳統意思上預存程序和函數的區別也就是，一個必須傳回值，一個不需要。
目前在標準的 PostgreSQL 發布裡有四種過程語言可用：
PL/pgSQL，
PL/Tcl，
PL/Perl，
PL/Python。
預設是沒有安裝的，例如你需要安裝
CREATE [TRUSTED] [PROCEDURAL] LANGUAGE ‘language-name’
    HANDLER handler_function_name
    [VALIDATOR validator_function_name] ;

 

7.autovacuum

vacuum命令類似於Mysql裡的optimize等命令，當對大表進行這樣的操作，需要注意對業務的影響，應該是在業務比較閒置時候進行。
從 PostgreSQL 8.1 開始，系統帶有一個額外可選的 autovacuum 守護進程，用於自動執行 VACUUM 和 ANALYZE 命令。可以在postgresql.conf裡進行配置。

 

8.獲得對象建立語句

Oracle：
select dbms_metadata.get_ddl(’OBJECT_TYPE’,'OBJECT_NAME’,'OWNER’) from dual;

Mysql：
show create OBJECT OBJECT_NAME；

PostgreSQL獲得對象建立語句比較折騰，需要分別處理。
8.1 表
貌似只能用 pg_dump 匯出來，例如匯出test資料庫的t1表的結構
pg_dump -s -U test -t t1

8.2 過程和函數
pg_get_functiondef(oid)

8.3 觸發器
pg_get_triggerdef(oid)

8.4 索引
pg_get_indexdef(oid)

8.5 視圖
pg_get_viewdef(oid)

oid可以通過相應的系統資料表查出來，例如function可在pg_proc查到，注意oid是個隱藏列。

 

9.database、schema、objects

在PostgreSQL裡的database和Mysql裡的database不一樣，它有點和Oracle的執行個體類似，每個database之間是獨立的，在database和object之間

還有個schema。每個database可以建立多個schema,每個shcema又可以建立多個object。下列簡單列出之間的關係：

PostgreSQL: database–shcma–object
Mysql     : database–object
Oracle    : instance–schema–object 

 

10.pg_dump和pg_dumpall

這2個工具的區別在於一個是備份單個database，一個備份所有的database。用慣了mysqldump，pg_dumpall很容易被忽略，為何不把這2個工具整合到一個呢？

 

總的來說，個人覺得PostgreSQL很多方面和Oracle是比較相似的，特別多進程的方式，相比Mysql來說要健壯的多。不過國內的PostgreSQL應用貌似很少，相關中文的資料也不多。一般來說java對應oracle,php對應mysql，python對應postgresql，是否能說python在國內的普及還是很不夠呢？