postgresql連線問題(beta)

1. Cdbexception
這是一個連線數超過資料程式庫限制連線數的的問題。
首先檢視一下資料庫內，現在的連線數:
• postgres=# select count(1) from pg_stat_activity; #目前連線數
• postgres=# show max_connections; #系統最大連線數
• postgres=# show superuser_reserved_connections; #保留給superuser的連線數
解決方法：

• 增大postgresql.conf中的參數max_connections或superuser_reserved_connections 值(修改這兩個參數都需要重啟DB)
• 開發程式上檢查連線數是否正常關閉等，應急情況下，可把pg_stat_activity中IDLE的程式移除，kill procpid;


2. psql: could not connect to server: Connection refused (0x0000274D/10061)
這個問題一般是以下原因造成的：
• 伺服器沒起來，ps -ef|grep postgres檢視是否存在PG程式
• 監聽問題，cat postgresql.conf|grep listen 檢視監聽位址是否正確
• 服務端高階使用者能進去，其他用戶不行，檢查是否超出最大連線數限制
• 以上都沒問題，伺服器端能連進去，但用戶端不行，這時需要檢視pg_hba.conf檔案
• 以上都沒問題，檢查伺服器端的iptables,開啟防火牆的存取通訊埠

Postgresql pg_hba.conf

for Ubuntu 12.04, postgresql 9.1

檔案位置：/etc/postgresql/9.1/main/pg_hba.conf

pg_hba.conf修改後，需使用pg_ctl reload重新讀取pg_hba.conf檔，如果pg_ctl找不到資料庫，則用-D /.../pgsql/data/　指定資料庫目錄，或export PGDATA=/.../pgsql/data/　導入環境變數。

用來設定postgre資料庫的使用者存取權限, 檔案主要有5個欄位：

1. Type: host表遠端存取，local表本機端存取 
2. Database: 設定可存取的Database 
3. User: 設定可存取的使用者 
4. Address: 設定可存取之網域，此設定全部網域皆可存取 
5. Method: trust表不需認證，password表示需要密碼

TYPE定義了多種連接PostgreSQL的方式，分別是：

• local: 使用本地unix socket
• host: 使用TCP/IP連接（包括SSL和非SSL）
• host結合IPv4 address:使用IPv4方式
• host結合IPv6 address:則使用IPv6方式
• hostssl只能使用SSL TCP/IP連接
• hostnossl不能使用SSL TCP/IP連接

DATABASE指定符合條件的某個資料庫是多個資料庫，庫名間以逗號分隔。

All: 只有在沒有其他的符合條目時才代表「所有資料庫」如果有其他的符合條目則代表「除了該條之外的資料庫」，因為“all”的優先順序最低。如下例：

local    db1    user1    reject

local    all      all      ident

這兩條都是指定local訪問方式，因為前一條指定了特定的資料庫db1，所以後一條的all代表的是除了db1之外的資料庫，同理使用者的all也是這個道理。

USER指定哪個資料庫使用者（PostgreSQL正規的名稱是角色，role）。多個用戶以逗號分隔。

CIDR-ADDRESS項local方式不必填寫，該項可以是IPv4位址或IPv6位址，可以定義某台主機或某個網段。

METHOD指定如何處理用戶端的認證。常用的有ident，md5，password，trust，reject。

• dent是Linux下PostgreSQL預設的local認證方式，凡是能正確登錄伺服器的作業系統使用者（注：不是資料庫使用者）就能使用本使用者映射的資料庫使用者不需密碼登錄資料庫。用戶映射檔為pg_ident.conf，這個檔記錄著與作業系統使用者匹配的資料庫使用者，如果某作業系統使用者在本檔中沒有映射用戶，則預設的映射資料庫使用者與作業系統使用者同名。比如，伺服器上有名為user1的作業系統使用者，同時資料庫上也有同名的資料庫使用者，user1登錄作業系統後可以直接輸入psql，以user1資料庫使用者身份登錄資料庫且不需密碼。很多初學者都會遇到psql -U username登錄資料庫卻出現“username ident 認證失敗”的錯誤，明明資料庫使用者已經createuser。原因就在於此，使用了ident認證方式，卻沒有同名的作業系統使用者或沒有相應的映射用戶。解決方案：1、在pg_ident.conf中添加映射用戶；2、改變認證方式。
• md5是常用的密碼認證方式，如果你不使用ident，最好使用md5。密碼是以md5形式傳送給資料庫，較安全，且不需建立同名的作業系統使用者。
• password是以純文字密碼傳送給資料庫，建議不要在生產環境中使用。
• trust是只要知道資料庫用戶名就不需要密碼或ident就能登錄，建議不要在生產環境中使用。
• reject是拒絕認證。
• 本地使用psql登錄資料庫，是以unix通訊端的方式，附合local方式。
• 使用PGAdmin3或php登錄資料庫，不論是否本地均是以TCP/IP方式，符合host方式。

PgBouncer

PgBouncer 是 PostgreSQL 的輕量的連接池，支援三種模式。

1. Session pooling/會話連接池
   最禮貌的方法。在客戶端連接的時候，在它的連接生命期內，會給它賦予一個服務器連接。在客戶端斷開的時候，服務器連接會放回到連接池中。 
2. Transaction pooling/事務連接池
   服務器連接只有在一個事務裡的時候才賦予客戶端。在 PgBouncer 注意到事務結束的時候，服務器將會放回連接池中。這是一個 hack，因為它打破了應用對後段連接的看法。只有在應用配合這樣的使用模式，沒有使用會破壞這種使用模式的時候才能用這個連接方式。參閱下標獲取會破壞 這種模式的特性。 
3. Statement pooling/語句連接池
   最激進的模式。這是事務連接池的一個扭曲的變種 - 不允許多語句的事務。這就意味著是在客戶端強制“autocomit”模式，主要是給 PL/Proxy 用的。

setting in Ubuntu 12.04

• 安裝：sudo apt-get install pgbouncer
• 設定檔：位於/etc/pgbouncer資料夾下，pgbouncer.ini為主要設定檔，userlist.txt為可連線的使用者(在 pgbounce.ini中指定)。
• man pgbouncer可以看到manaul中提供的一個簡單配置的例子 。
[databases]
pg_template1 = host=127.0.0.1 dbname=template1
[pgbouncer]
pool_mode = session
listen_port = 6543
listen_addr = 127.0.0.1
auth_type = md5
auth_file = users.txt
logfile = pgbouncer.log
pidfile = pgbouncer.pid
admin_users = someuser
stats_users = stat_collector

 
• 上面的配置說明了該pgbouncer創建了針對127.0.0.1上的template1的一個連接池，該連接池對調用方的呈現的資料庫名稱是pg_template1,它映射到了template1上。所有訪問pbbouncer上的pg_template1的請求都會轉到template1上完成。
• pool_mode 指明了連接池的模型，pgbouncer目前支援三種連接池模型。分別是session, transaction和statment三個級別。 
•  a. session. 會話級連結。只有與當用戶端的會話結束時，pgbouncer才會收回已分配的連結 
•  b. transaction 事務級連接。當事務完成後，pgbouncer會回收已分配的連結。也就是說用戶端只是在事務中才能獨佔此連結，非事務的對資料庫的請求是沒有獨享的連結的。 
•  c. statement 語句級連結。任何對資料庫的請求完成後，pgbouncer都會回收連結。此種模式下，用戶端不能使用事務，否則會造成資料的不一致。 pgbouncer的預設設置是session連結。
• listen_port和listen_addr是pgbouncer監聽的位址和埠號。
• auth_type和auth_file是bppgbouncer用以完成用戶端身份認證。auth_file中保存用戶名和密碼，根據驗證方式(auth_type)的不同，auth_file的內容也有不同。 
•  md5: 基於md5的密碼驗證，
• auth_file中需要有普通文本和md5值兩種形式的密碼； 
•  crypt: 基於crypt的密碼驗證(man 3 crypt), auth_file必須包含文本密碼； 
•  plain: 明文驗證方式； 
•  trust: 不進行驗證，但auth_file依然需要保存用戶名； 
•  any: 也不進行驗證，而且auth_file中不需要保存用戶名了。但此種方式需要在pg_template1中明確說明用戶名進行真實資料庫的登錄。如: pg_template1 = host=127.0.0.1 user=exampleuser dbname=template1.否則會報錯的。 需要說明的是：auth_file中的用戶名、密碼都必須使用雙引號，否則還是報錯。
• logfile和pidfile分別保存log檔和pid檔的路徑。 
•  admin_users：列出哪些用戶可以登錄pgbouncer進行管理，以逗號進行分隔 stats_users：列出哪些用戶可以登錄pgbouncer進行唯讀操作，如可以列出伺服器狀態，訪問連結等，但是不能執行reload。

Postgresql 9.1安裝(Ubuntu 12.04)

安裝

• $sudo apt-get install postgresql
• #查詢已安裝的版本: psql -V 
• #改變postgres user密碼: sudo passwd postgres
• #變更為postgres user: su - postgres
• #進入psql console: psql postgres
• #上述三步驟可簡化為: sudo -u postgres psql postgres
• 在psql中修改postgres帳號的密碼： ALTER USER postgres WITH PASSWORD '';

設定

• 所有設定檔都在/etc/postgresql資料夾下
  sudo vim /etc/postgresql/9.1/main/postgresql.conf(主要設定檔)設定listen_address='localhost' (只有本機可連線)
  or listen_address='*' (本機所有介面均可連線)
• 启用密码验证:
  #password_encryption = on改为password_encryption = on 
• ph_hba.conf(可訪問的client ip range)
  在文档末尾加上以下内容
  # to allow your client visiting postgresql server
  host all all 0.0.0.0 0.0.0.0 md5
• share memory of os
  http://www.postgresql.org/docs/9.1/static/kernel-resources.html
  在/etc/sysctl.conf中設定
  kernels.shmmax=9663676416 #(9G, 9*2^9)
  kernels.shmall=2359296          #(9G/4096k)
• 完成後，重新啟動pgsql服務：service postgresql restart