postgresql 9.4.4 安裝

標籤：postgresql

因為最近要做個資料研究，需要把資料入庫，開來源資料庫中，同學們一般可能都會選擇mysql,不過mysql 的一些函數能是在太累，因此我這邊換成使用起來與oracle 更為相似的postgresql,近來這個資料庫也是極其熱門，上升速度很快。

下面做個編譯與安裝介紹，以下內容基本都來自德哥分享（http://blog.163.com/[email protected]/）的文檔,感謝德哥的分享。

Postgresql下載網址：

http://www.postgresql.org/ftp/source/

 

這裡我們選擇最新的穩定版版postgresql-9.4.4 的源碼版

https://ftp.postgresql.org/pub/source/v9.4.4/postgresql-9.4.4.tar.bz2

下載後解壓

tar jxvf postgresql-9.4.4.tar.bz2

因為postgresql不能用root 使用者啟動，需要為他重新建立一個使用者

建立使用者

useradd pg944

進入下載的目錄

cd postgresql-9.4.4

編譯，這裡設定安裝目錄

./configure --prefix=/home/pg944/pgsql

完成後執行

gmake worldgmake install-world

下面初始化資料庫相關配置

首先修改Linux核心相關參數調整

vi /etc/sysctl.conf

在最下面加上

kernel.shmmni = 4096kernel.sem = 50100 64128000 50100 1280fs.file-max = 7672460net.ipv4.ip_local_port_range = 9000 65000net.core.rmem_default = 1048576net.core.rmem_max = 4194304net.core.wmem_default = 262144net.core.wmem_max = 1048576

然後繼續修改

vi /etc/security/limits.conf

在最下面加上

* soft nofile 131072* hard nofile 131072* soft nproc 131072* hard nproc 131072* soft core unlimited* hard core unlimited* soft memlock 50000000* hard memlock 50000000

設定防火牆

vi /etc/sysconfig/iptables

加上連接埠,記得在連接埠22的那個設定下加，不要加到最下面

-A RH-Firewall-1-INPUT -p tcp -m tcp--dport 1999 -j ACCEPT

重啟防火牆

service iptables restart

現在初始化postgresql 使用者的相關配置

su - pg944

添加環境變數

vi ~/.bash_profile

加上

export PGPORT=1999 export PGDATA=/home/pg944/pg_rootexport LANG=en_US.utf8export PGHOME=/home/pg944/pgsqlexport LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATHexport DATE=`date +"%Y%m%d%H%M"`export PATH=$PGHOME/bin:$PATH:.export MANPATH=$PGHOME/share/man:$MANPATHexport PGUSER=postgresexport PGHOST=$PGDATAalias rm='rm  -i'alias ll='ls -lh'export PGDATABASE=postgres

儲存設定

source ~/.bash_profile

初始化資料庫

initdb -D $PGDATA -E UTF8 --locale=C  -U postgres -W

這裡要輸入密碼，和輸入確認密碼，輸錯會讓你重新初始化

 

測試下

[[email protected] ~]$ psql -Vpsql (PostgreSQL) 9.4.4

這個時候$PGDATA這裡就可以看到資料庫檔案了。下面挑幾個重要的說說

ll $PGDATAbase  -- 這個目錄對應pg_default資料表空間global  --這個目錄對應pg_global資料表空間,  存放叢集中的共用對象例pg_database表, (select  relkind,relname from  pg_class where reltablespace = (select oid from  pg_tablespace where spcname=‘pg_global’) order by 1) , 包含控制檔案等.pg_clog -- 儲存事務提交狀態資料pg_hba.conf -- 資料庫存取控制檔案pg_log -- 資料庫日誌目錄(根據配置定義,  可能沒有這個目錄)pg_multixact -- 共用行鎖的事務狀態資料PG_VERSION  -- 資料庫版本pg_xlog  -- 儲存WAL檔案postgresql.conf -- 設定檔postmaster.opts -- 記錄資料庫啟動時的命令列選項postmaster.pid -- 資料庫啟動的主進程資訊檔(包括$PGDATA目錄,  資料庫啟動時間, 監聽連接埠ipc資訊等)

繼續

cd $PGDATA

啟動資料庫前修改一下$PGDATA下面的pg_hba.conf和postgresql.conf

pg_hba.conf用於配置控制訪問資料庫的來源

這裡為了方便設定對所有地址開放，很不安全

host all all 0.0.0.0/0 md5

postgresql.conf是資料庫的主設定檔,  最好也調整一下Linux核心參數.

下面內容會比較多

vi postgresql.conf

*****************************************************

監聽IPv4的所有IP.

listen_addresses = ‘0.0.0.0‘

 

最大允許1000個串連(測試的話100夠了, 加大串連數同時需要調整shared buffer).

max_connections = 100

為超級使用者保留10個可用串連.

superuser_reserved_connections = 10

預設的unix socket檔案放在/tmp, 修改為$PGDATA, 以確保本地訪問的安全性.

unix_socket_directory = ‘.‘

 

預設的存取權限是0777, 修改為0700更安全.

unix_socket_permissions = 0700

 

TCP會話心跳包在Linux下面預設是2小時.  如果已經修改了系統的核心參數, 則不需要再修改這裡.

為防止用戶端和服務端之間的網路裝置主動關閉空閑TCP會話, 設定以下參數.

tcp_keepalives_idle = 60

tcp_keepalives_interval = 10

tcp_keepalives_count = 6

 

大的shared_buffers需要大的checkpoint_segments,同時需要申請更多的System V共用記憶體資源. 並且增加共用記憶體管理的開銷.

這個值不需要設的太大, 因為PostgreSQL還依賴作業系統的檔案系統cache來提高讀效能,  另外, 寫操作頻繁的資料庫這個設太大反而會增加checkpoint壓力.

在9.4版本中會增加mmap以及huge page table的支援以減少記憶體管理的開銷.

shared_buffers = 512MB

 

這個值越大, VACUUM, CREATE INDEX的操作越快, 當然大到一定程度瓶頸就不在記憶體了, 可能是CPU例如建立索引.

這個值是一個操作的記憶體使用量上限, 而不是一次性分配出去的. 並且需要注意如果開啟了autovacuum, 最大可能有

autovacuum_max_workers*maintenance_work_mem的記憶體被系統消耗掉.

maintenance_work_mem = 512MB

 

一般設定為比系統限制的略少,ulimit -a : stack size (kbytes, -s) 10240

max_stack_depth = 8MB

 

手動執行vacuum操作時, 預設是沒有停頓執行到底的, 為了防止VACUUM操作消耗太多資料庫伺服器硬體資源, 這個值是指vacuum在消耗多少資源後停頓多少時

間,以便其他的操作可以使用更多的硬體資源.

vacuum_cost_delay = 10ms

vacuum_cost_limit = 10000  # 1-10000 credits

 

預設bgwriter進程執行一次後會停頓200ms再被喚醒執行下一次操作, 當資料庫的寫操作很頻繁的時候, 200ms可能太長, 導致其他進程需要花費過多的時間來進行

bgwriter的操作. 短暫的停頓更利於將sharedbuffer中的髒塊flush到磁碟, 降低backend 主動flush 以申請共用記憶體的情形. 後面使用explain時會講到.

bgwriter_delay = 10ms

 

另外還有幾個和寫髒塊相關的參數, 即寫多少髒塊後開始休息.

如果需要做資料庫WAL記錄備份的話至少需要設定成archive層級,  如果需要做hot_standby那麼需要設定成hot_standby,由於這個值修改需要重啟資料庫, 所以先

設定成hot_standby比較好.  當然hot_standby意味著WAL記錄得更詳細,  如果沒有打算做hot_standby設定得越低效能越好.

wal_level = hot_standby

 

wal buffers預設是-1 根據shared_buffers的設定自動調整shared_buffers*3%.最大限制是XLOG的segment_size.

wal_buffers = 16384kB

 

多少個xlog file產生後開始checkpoint操作,

這個值越大, 允許shared_buffer中的被頻繁訪問的髒資料存放區得更久. 一定程度上可以提高資料庫效能. 但是太大的話會導致在資料庫發生checkpoint的時候需要

處理更多的髒資料帶來長時間的IO開銷(還要考慮bgwriter的存在).

太小的話會導致產生更多的WAL檔案 (因為full page writes=on, CHECKPOINT後的第一次塊的改變要寫全塊,checkpoint越頻繁, 越多的資料更新要寫全塊導致產

生更多WAL).

checkpoint_segments = 32

 

這個和checkpoint_segments的效果是一樣的,  只是觸發的條件是時間條件.

checkpoint_timeout = 5min

 

歸檔參數的修改也需要重啟資料庫, 所以就先開啟吧.

archive_mode = on

 

這個是歸檔調用的命令, 我這裡用date代替, 所以歸檔的時候調用的是輸出時間而不是拷貝wal檔案.

archive_command = ‘/bin/date‘ # ‘cp %p/arch/%f‘

 

如果要做hot standby這個必須大於0, 並且修改之後要重啟資料庫所以先設定為32.

表示允許建立多少個和流複製相關的串連.

max_wal_senders = 32

 

這是個standby 資料庫參數, 為了方便角色切換, 我一般是所有的資料庫都把他設定為on 的.

hot_standby = on

 

這個參數是說資料庫中隨機的PAGE訪問的開銷佔seq_page_cost的多少倍 , seq_page_cost預設是1. 其他的開銷都是seq_page_cost的倍數.

這些都用於基於成本的執行計畫選擇. 後面講成本因子的調教時會詳細說明.

random_page_cost = 2.0

 

effective_cache_size只是個度量值,  不是實際分配使用的記憶體值.

表示系統有多少記憶體可以作為作業系統的cache. 越大的話, 資料庫越傾向使用index這種適合random訪問的執行計畫.

一般設定為記憶體大小減去資料庫的shared_buffer再減去系統和其他軟體所需的記憶體.

effective_cache_size = 12000MB

 

下面是日誌輸出的配置.

log_destination = ‘csvlog‘

logging_collector = on

log_directory = ‘pg_log‘   #這裡建議做修改，但得提前建好目錄，並設定許可權

log_truncate_on_rotation = on

log_rotation_age = 1d

log_rotation_size = 10MB

 

這個參數調整的是記錄執行時間超過1秒的SQL到日誌中, 一般用於跟蹤哪些SQL執行時間長.

log_min_duration_statement = 1s

 

記錄每一次checkpoint到日誌中.

log_checkpoints = on

 

記錄鎖等待超過1秒的操作, 一般用於排查商務邏輯上的問題.

log_lock_waits = on

deadlock_timeout = 1s

 

記錄串連和連接埠串連, 可以反映短串連的問題, 同時也可以作為串連審計日誌.

log_connections = on

log_disconnections = on

 

開啟代碼位置資訊的輸出, 可以反映日誌資訊輸出自哪個代碼的什麼函數.

在會話中可以使用\set VERBOSITY verbose開啟

log_error_verbosity  = verbose

 

記錄DDL語句, 但是需要注意的是, 建立使用者, 修改密碼的語句也會被記錄, 所以敏感SQL執行前建議在會話中關閉這個審計.

log_statement = ‘ddl‘

 

這個原本是1024表示跟蹤的SQL在1024的地方截斷, 超過1024將無法顯示全SQL. 修改為2048會消耗更多的記憶體(基本可以忽略), 不過可以顯示更長的SQL.

track_activity_query_size = 2048

 

預設autovacuum就是開啟的, log_autovacuum_min_duration = 0記錄所有的autovacuum操作.

autovacuum = on

log_autovacuum_min_duration = 0

 

*****************************************************************

通過psql 命令登入資料庫

psql -h 192.168.137.3 -p 1999 -U postgres

 

由於我是專註開發，因此喜歡用IDE去寫sql,這裡有個postgresql第三方開發工具，類似pl/sql

http://www.pgadmin.org/download/windows.php

下載安裝，輸入資料庫相關連結

成功串連

點SQL表徵圖就可以開始寫SQL了

比如

下面建立一個資料表空間,這裡的地址對應伺服器裡的檔案夾。pg_test檔案目錄得提前建立。

create tablespace "pgtest"location '/home/pg944/pg_root/pg_test';

Ok,資料庫裝完，現在就可以愉快導資料進行開發了。

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。

postgresql 9.4.4 安裝