Postgresql database

Ubuntu PPA

/etc/passwd
# 可使用bash登入，家目錄在/var/lib/postgresql
postgres:x:125:131:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash
  • 設定檔：
/etc/postgresql/9.6/main
├── environment
├── pg_ctl.conf
├── pg_hba.conf
├── pg_ident.conf
├── postgresql.conf
└── start.conf

0 directories, 6 files

/etc/postgresql-common
├── createcluster.conf
├── pg_upgradecluster.d
├── root.crt
├── supported_versions
└── user_clusters
  • 預設的資料庫安裝路徑在/var/lib/postgresql/9.6/main中。
  • log檔位為/var/log/postgresql資料夾中。

環境變數

  • PGHOST: 資料庫的主機
  • PGPORT: 資料庫的連接埠
  • PGDATA: 資料庫在主機中的資料夾(應等同於initdb -D指定的路徑)

初始化資料庫

  • 以下步驟如果是使用apt-get方式安裝postgresql時可省略。
    • 使用postgres帳號，輸入 initdb -D /var/lib/postgresql/9.6/main ，創建資料庫資料。
    • 因為資料目錄包含所有存儲在資料庫裡的資料，所以出於安全考慮，這個目錄不能給任何非授權使用者訪問。 因此，initdb禁止除Postgres使用者帳戶以外的任何使用者訪問這個目錄(700)。

Linux kernel設定

  • http://www.postgres.cn/docs/9.4/kernel-resources.html

  • 共用記憶體和信號燈的正確叫法是"System V IPC" ，PostgreSQL只在Windows上自己提供這套機制的替換實現， 要運行PostgreSQL這些機制是必需的。

  • 共用記憶體大小設置可以通過/etc/sysctl.conf改變後，再使用 sudo sysctl -p 。

    • kernels.shmmax=9663676416 #(9G, 9*2^9)
    • kernels.shmall=2359296 #(9G/4096)

Linux overcommit機制

  • 在Linux 2.4 以及之後的版本裡，缺省的虛擬記憶體的行為不是對PostgreSQL最優的。 原因在於內核實現記憶體過提交的方法，如果PostgreSQL或其它進程的記憶體請求導致系統用光虛擬記憶體， 那麼內核可能會終止PostgreSQL的postmaster主要伺服器進程。

  • 如果發生了這樣的事情，你會看到像下面這樣的內核消息。

    • 這表明postgres因為記憶體壓力而終止了。儘管現有的資料連接將繼續正常運轉， 但是將無法接受新的連接。要想恢復，你應該重啟PostgreSQL。
    • 一個避免這個問題的方法是在一台你確信不會因為其它進程而耗盡記憶體的機器上運行PostgreSQL。 如果記憶體緊張，增加作業系統的交換空間有助於避免這個問題，因為OOM(out-of-memory) killer只有在實體記憶體和交換空間都耗盡時才會被調用。
    • 如果PostgreSQL本身是系統記憶體耗盡的原因，你可以通過改變你的配置來避免這個問題。 在某些情況下，調低記憶體相關的配置參數可能有所説明，尤其是shared_buffers 和work_mem。其他情況下， 這個問題可能是由於允許太多的連接連到資料庫伺服器引起的。在許多情況下， 減小max_connections並且改為使用外部連接池軟體會更好。
Out of Memory: Killed process 12345 (postgres).
  • 在 Linux 2.6 以及以後的版本裡，可以修改記憶體的行為，這樣它就不會再"過提交"記憶體。 儘管這個設置將不會完全阻止OOM killer 被調用，然是它將顯著地減少並且將因此導致更穩健的系統行為。 這通過用sysctl選取一個嚴格的過提交模式實現：
sysctl -w vm.overcommit_memory=2
  • 另外一種方法，不管改變或不改變vm.overcommit_memory都可以使用， 為postmaster進程設置進程相關的oom_score_adj值為-1000， 從而保證它不會成為OOM killer的目標。 最簡單的方法是在postmaster的啟動腳本裡在調用postmaster之前執行：
sudo echo -1000 > /proc/self/oom_score_adj

參數設定

  • 參數設定於/etc/postgresql/9.6/main/postgresql.conf文件

  • 參數是每條一行。選項名和值之間的等號是可選的。 空白（除了引號中參數值的空白）和空行被忽略。井號(#)引入注釋。 非簡單識別字或者數字必須用單引號包圍。 如果需要在參數值裡嵌入單引號，要麼寫兩個單引號(推薦方法)，要麼用反斜扛包圍。

# This is a comment
log_connections = yes
log_destination = 'syslog'
search_path = '"$user", public'
shared_buffers = 128MB

  • 所有參數名都是大小寫不敏感的。每個參數都可以接受五種類型之一： 布林、字串、整數、浮點數或枚舉。

  • 布林：值可以是(都是大小寫無關) on、off、true、 false、yes、no、 1、0或這些東西的任意清晰無歧義的首碼。

  • 字串: 通常，把值包含在[單引號]中，雙寫值中的單引號。如果值是簡單的數字或識別字， 那麼通常可以省略單引號。

  • 數值（整數或浮點數： 僅允許浮點數有小數點。不要使用千位分隔符號。不需要加引號。

  • 帶單位的數位： 一些數位參數有隱式的單位，因為它們描述記憶體或時間的數量。 單位可以是千位元組、塊（通常是8個千位元組）、毫秒、秒或分鐘。 這些設定的普通數值將使用該設定的缺省單位，可以在 pg_settings.unit中查看。 為了方便，可以明確的給出一個設定的單位，例如時間值'120 ms'， 它們將轉換為任何該參數的實際單位。請注意，要使用該特性， 必須將值寫作字串（加引號）。單位名是大小寫敏感的， 並且數值和單位之間可以有空格。

    • 有效的存儲單位是kB（千位元組）、 MB (百萬位元組)、GB（千百萬位元組） 和TB（兆百萬位元組）。存儲單元的乘數是1024，不是1000。
    • 有效的時間單位是ms（毫秒）、s（秒）、 min（分鐘）、h（小時） 和d（天）。

  • 枚舉: 枚舉類型的參數和字串參數的書寫方式相同，但是限制為一個值組當中的一個值。 允許的值可以在pg_settings.enumvals中找到。 枚舉參數值是不區分大小寫的。

postgresql.conf配置

配置檔案位址

  • data_directory (string) : 資料存儲使用的目錄。這個選項只能在伺服器啟動的時候設置。

  • hba_file (string) : 聲明基於主機的認證(HBA)設定檔(通常叫pg_hba.conf)。這個選項只能在伺服器啟動的時候設置。

  • ident_file (string) : 用戶名匹配的設定檔(通常叫pg_ident.conf)。 這個選項只能在伺服器啟動的時候設置。

  • external_pid_file (string) : 聲明可被伺服器管理程式使用的額外PID檔。這個選項只能在伺服器啟動的時候設置。

連接設置

  • listen_addresses (string) : 聲明伺服器監聽用戶端連接的TCP/IP位址。值是一個逗號分隔的主機名稱和/或數字IP位址。 特殊項*對應所有可用IP介面。 0.0.0.0允許監聽所有IPV4位址，並且::允許監聽所有IPV6位址。 如果這個列表是空的，那麼伺服器不會監聽任何IP介面， 這種情況下，只有Unix域通訊端可以用於連接資料庫。 缺省值localhost只允許進行本地"回環"連接。 用戶端認證(第 19 章)允許細細微性控制誰能訪問伺服器， listen_addresses控制嘗試連接哪個介面。 這個參數只能在伺服器啟動的時候設置。

  • port (integer) : 伺服器監聽的TCP埠；缺省是5432。請注意同一個埠號用於伺服器監聽的所有IP位址。 這個參數只能在伺服器啟動的時候設置。

  • max_connections (integer) : 允許和資料庫連接的最大併發連接數。缺省通常是100， 但是如果內核設置不支持這麼大(在initdb的時候判斷)，可能會比這個數少。 這個參數只能在伺服器啟動的時候設置。

    • 當運行一個備用伺服器，你必須將此參數設置為比主要伺服器上相同的或更高的值。否則， 不允許在備用伺服器上進行查詢。

  • superuser_reserved_connections (integer) : 決定為PostgreSQL超級用戶連接而保留的連接"槽位"。

    • 一次最多可以同時啟動max_connections個連接。在活躍的併發連數接到了max_connections 減去superuser_reserved_connections的時候，新的連接就只能由超級用戶發起了， 並且不接收新的複製連接。
    • 缺省值是3。這個值必須小於max_connections的值。 這個參數只能在伺服器啟動的時候設置。

  • unix_socket_directories (string) : 指定Unix域通訊端（S）的目錄， 並且伺服器監聽來自用戶端應用程式的連接。 多個通訊端可以通過列出多個用逗號分隔的目錄創建。 忽略條目之間的空白; 如果你需要包含空格或逗號的名字，增加帶有雙引號的目錄名。

    • 空值指定不監聽任何Unix域的通訊端， 在這種情況下，只有TCP/IP通訊端可以用於連接到伺服器。 預設值通常是/var/run/postgresql，但可以在編譯的時候改變。 這個參數只能在伺服器啟動的時候設置。
    • 除通訊端檔本身外，它被命名為 .s.PGSQL.nnnn，其中 nnnn是伺服器的埠號，普通檔 命名的.s.PGSQL.nnnn.lock 將在每個unix_socket_directories目錄中創建。永遠都不應該手動刪除這兩個檔。
    • 這個參數在Windows上是無關緊要的，它不具有Unix域通訊端。

  • unix_socket_group (string) : 設置Unix域通訊端的所屬組(通訊端的所屬用戶總是啟動伺服器的用戶)。 可以與選項unix_socket_permissions一起用於對通訊端進行存取控制。

    • 缺省是一個空字串， 表示當前伺服器用戶的缺省組。這個選項只能在伺服器啟動的時候設置。
    • 這個參數在Windows上是無關緊要的，它不具有Unix域通訊端。

  • unix_socket_permissions (integer) : 設置Unix域通訊端的存取權限。Unix域通訊端使用普通的Unix檔案系統許可權集。 這個參數值應該是數值的形式，也就是系統調用chmod和umask 接受的形式。如果使用自訂的八進制格式，數位必須以0開頭。

    • 缺省的許可權是0777，意思是任何人都可以連接。合理的候選是0770 (只有用戶和同組的人可以訪問， 又見unix_socket_group)和0700(只有用戶自己可以訪問)。
    • 請注意，對於Unix域通訊端，只有寫許可權有意義， 因此在設置和撤銷讀和執行許可權中沒有任何意義。
    • 這個存取控制機制與用戶認證毫無關係。
    • 這個參數只能在伺服器啟動的時候設置。

  • bonjour (boolean) : 通過Bonjour啟動廣播伺服器的存在。缺省是關閉的。 這個值只能在伺服器啟動的時候設置。

  • bonjour_name (string) : 聲明Bonjour伺服器名稱。

    • 空字串''(缺省值)表示使用電腦名。
    • 如果編譯時沒有打開Bonjour支持那麼將忽略這個參數。 這個值只能在伺服器啟動的時候設置。

  • tcp_keepalives_idle (integer) : 聲明TCP應該發送保持活躍資訊到用戶端後不活躍的秒數。

    • 零表示使用系統預設值。該參數僅在支援 TCP_KEEPIDLE或者TCP_KEEPALIVE符號以及 Windows的系統上出現;在其它系統中，它必須是零。
    • 在通過Unix域通訊端連接的會話中，這個參數被忽略，並且總是讀為零。
    • 在Windows中，0值將這個參數設置為2小時， 因為Windows不提供讀取系統預設值的方法。

  • tcp_keepalives_interval (integer) : 聲明TCP保持活躍資訊不被用戶端承認，應該被轉發的秒數。

    • 零使用系統預設值。 在那些支援TCP_KEEPINTVL符號以及Windows的系統上支援這些參數。 在其他系統上，這個參數必須為零。
    • 在通過Unix域通訊端連接的會話中，這個參數被忽略，並且總是讀為零。
    • 在Windows中，0值將這個參數設置為1秒， 因為Windows不提供讀取系統預設值的方法。

  • tcp_keepalives_count (integer) : 聲明伺服器到用戶端的連接被認為關閉之前，TCP保持活躍信號可以丟失的數量。

    • 零使用系統預設值。 在那些支援TCP_KEEPCNT的系統上支援這些參數。 在其他系統上，這個參數必須為零。
    • 在通過Unix域通訊端連接的會話中，這個選項被忽略，並且總是讀為零。
    • 在Windows上不支持這個參數，並且總是為零。

安全與認證

  • authentication_timeout (integer) : 完成用戶端認證的最長時間，以秒計。如果一個用戶端沒有在這段時間裡完成認證協議，伺服器將中斷連接。這樣就避免了出問題的用戶端無限制地佔據連接資源。

    • 缺省是60秒(1m)。 這個選項只能在命令列上或者在postgresql.conf裡設置。

  • ssl (boolean) : 啟用SSL連接。缺省是off。這個選項只能在伺服器啟動的時候設置。SSL通訊與TCP/IP 連接是唯一可能的。

  • ssl_ca_file (string) : 指定包含SSL伺服器憑證頒發機構（CA）的檔案名稱。 預設值為空，意味著沒有CA檔被載入， 並且不進行用戶端證書驗證。 （在以前的PostgreSQL版本中，此檔的名稱是作為root.crt的硬編碼。） 相對路徑是相對於資料目錄。這個參數只能在伺服器啟動進行設置。

  • ssl_cert_file (string) : 指定包含SSL伺服器憑證的檔案名稱。 預設值是server.crt。相對路徑是相對於資料目錄。這個參數只能在伺服器啟動時進行設置。

  • ssl_crl_file (string) : 指定包含SSL伺服器憑證撤銷列表(CRL)的檔案名稱。 預設值為空，意味著沒有CRL檔被載入。 （在PostgreSQL的早期版本中，該檔的名稱為root.crl的 硬編碼。）相對路徑是相對於資料目錄。這個參數只能在伺服器啟動時候進行設置。

  • ssl_key_file (string) : 指定包含SSL伺服器私密金鑰的檔案名稱。 預設值是server.key。相對路徑是 相對於資料目錄。這個參數只能在伺服器啟動時設置。

  • ssl_renegotiation_limit (integer) : 在工作階段金鑰發生重新商議之前，指定多少資料可以進行SSL-加密。 當大量流量被檢查的時候，但它也會產生大量性能損失，重新談判降低攻擊者密碼分析的機會 。總的發送和接收的流量用於檢查極限。如果該參數被設置為0， 禁用重新談判。預設值是512MB。

*　 ssl_ciphers (string) : 指定一列允許在安全連接上使用的SSL密碼組。參閱OpenSSL 報中的ciphers手冊頁獲取這個設置的語法，和一系列支援的值。

+ 缺省值是HIGH:MEDIUM:+3DES:!aNULL。 它通常是合理的，除非你有特殊的安全需求。
+ 缺省值的適用說明：
    - HIGH: 密碼組使用來自HIGH組的密碼（例如AES、Camellia、3DES）
    - MEDIUM: 密碼組使用來自MEDIUM組的密碼（例如RC4、SEED）
    - +3DES: OpenSSL對HIGH的預設順序是有問題的， 因為它認為+3DES比AES128高。這是錯誤的，因為+3DES提供的安全性比AES128要低。 +3DES重新排列了所有其他HIGH和MEDIUM密碼。
    - !aNULL: 禁用沒有認證的匿名密碼組。這樣的密碼組容易受到中間人的攻擊， 因此應該不要使用。
+ 可用的密碼組明細隨著OpenSSL的版本而變化，使用命令 openssl ciphers -v 'HIGH:MEDIUM:+3DES:!aNULL' 查看當前安裝的OpenSSL版本的密碼組明細。請注意， 這個列表基於伺服器秘鑰類型在運行時過濾。

  • ssl_prefer_server_ciphers (bool) : 聲明是否使用伺服器的SSL預設密碼，而不是用戶端的。缺省是true。

    • 較老的PostgreSQL版本沒有這個設置，並且總是使用用戶端的預設。 這個設置主要是為了向後相容。通常使用伺服器的預設更好一些， 因為伺服器更有可能適當的配置了。

  • ssl_ecdh_curve (string) : 聲明在ECDH秘鑰交換中要使用的curve的名字。 它需要所有連接用戶端的支援。它不需要和伺服器的Elliptic Curve秘鑰相同。 缺省是prime256v1。

    • 大多數curve的OpenSSL名: prime256v1 (NIST P-256)、 secp384r1 (NIST P-384)、 secp521r1 (NIST P-521)。
    • 使用命令openssl ecparam -list_curves 可以顯示所有可用的curve的列表。並不是所有的curve都可以在 TLS中使用。

  • password_encryption (boolean) : 在CREATE USER或者ALTER ROLE裡聲明了一個口令， 而又沒有寫ENCRYPTED或者UNENCRYPTED的時候，這個選項決定口令是否要加密。 缺省是on(加密口令)。

  • krb_server_keyfile (string) : 設置Kerberos伺服器鍵字檔的位置。 這個參數只能在postgresql.conf檔或者伺服器命令列中設置。

  • krb_caseins_users (boolean) : 設置GSSAPI用戶名是否大小寫無關。 缺省是off(大小寫相關)。 這個參數只能在postgresql.conf檔或者伺服器命令列中設置。

  • db_user_namespace (boolean) : 目前不建議使用，預設為關閉。

記憶體

  • shared_buffers (integer) : 設置資料庫伺服器將使用的共用記憶體緩衝區數量。缺省通常是128MB， 但是如果你的內核設置不支援這麼大(在/etc/sysctl.conf中修改)，那麼可以少些(在initdb的時候決定)。

    • 每個緩衝區大小的典型值是128KB，（BLCKSZ的非缺省值改變最小值）不過，這個數值比最小值大一些通常需要更好的性能。這個選項只能在伺服器啟動的時候設置。
    • 如果你有1GB或更多記憶體的專用資料庫伺服器， 對於shared_buffers合理的初始值是您的系統記憶體的25%。 + 在Windows上，shared_buffers大點的值不是很有效。 在Windows系統上shared_buffers的有用範圍一般是從64MB到512MB。

  • huge_pages (enum) : 啟用/禁用大記憶體頁(page size)的使用。有效值是try（缺省）、 on和off。

    • 目前，僅在Linux上支持這個特性。當設置為try時， 在其他系統上忽略該設置。
    • 大記憶體頁的使用，減小了頁表(page table)，節省了CPU花費在記憶體管理上的時間， 提高了性能。
    • huge_pages設置為try時， 伺服器將嘗試使用大頁面，如果失敗的話，再回滾使用普通配置。 設置為on時，未能使用大頁面將阻止伺服器啟動。 設置為off時，將不使用大頁面。

  • temp_buffers (integer) : 設置每個資料庫會話使用的臨時緩衝區的最大數目。這些都是會話的本地緩衝區， 只用于訪問臨時表。缺省是8MB。

    • 這個設置可以在獨立的會話內部設置， 但是只有在會話第一次使用臨時表的時候才能增長；企圖在該會話裡隨後改變該數值是無效的。
    • 一個會話將按照temp_buffers給出的限制，根據需要分配臨時緩衝區。如果在一個並不需要大量臨時緩衝區的會話裡設置一個大的數值，其開銷只是一個緩衝區描述符，或者說每個temp_buffers增加大概64位元組。不過， 如果一個緩衝區實際上被使用，那麼就會額外消耗8192位元組(或者說是BLCKSZ位元組)。

  • max_prepared_transactions (integer) : 設置可以同時處於"預備"狀態的事務的最大數目。

    • 把這個參數設置為零（這是缺省值）則關閉預備事務的特性。這個值只能在伺服器啟動的時候設置。
    • 如果你不打算使用預備事務，這個參數也可以設置為零。 避免預備事務的偶然建立。
    • 如果你使用它們， 你可能會需要把max_prepared_transactions設置成至少和max_connections 一樣大，以避免每個會話可以有預備事務掛起。
    • 當運行備庫伺服器時，你必須設置相同參數或者比主要伺服器上更高參數值。 否則，在備庫伺服器上不允許查詢。

  • work_mem (integer) : 內部排序操作和Hash表在開始使用臨時磁片檔之前使用的記憶體數目。 缺省數值是4MB。

    • 請注意對於複雜的查詢， 可能會同時併發運行好幾個排序或者散列操作；每個都會被批准使用這個參數聲明的這麼多記憶體， 然後才會開始求助於暫存檔案。
    • 同樣，好幾個正在運行的會話可能會同時進行排序操作。 因此使用的總記憶體可能是work_mem的好幾倍。
    • 當選擇這個值的時候，必須記住這個事實。 ORDER BY, DISTINCT和融合連接都要用到排序操作。 Hash表在散列連接、散列為基礎的聚合、散列為基礎的IN子查詢處理中都要用到。

  • maintenance_work_mem (integer) : 聲明在維護性操作(比如VACUUM, CREATE INDEX和ALTER TABLE ADD FOREIGN KEY)中使用的最大的記憶體數。 缺省是64MB。

    • 因為在一個資料庫會話裡， 任意時刻只有一個這樣的操作可以執行，並且一個資料庫安裝通常不會有太多這樣的工作併發執行，把這個數值設置得比work_mem更大是安全的。
    • 更大的設置可以改進清理和恢復資料庫轉儲的速度。
    • 請注意，當自動清理運行時， 這個記憶體會被分配最多autovacuum_max_workers次，所以要小心，不要設置預設值太高。這對於單獨設置autovacuum_work_mem來控制記憶體分配是有説明的。

  • autovacuum_work_mem (integer) : 聲明每個自動清理工作進程可使用的最大記憶體數。 缺省為-1，表明使用maintenance_work_mem的值。

    • 該設置對VACUUM在其他環境中運行時的行為沒有影響。

  • max_stack_depth (integer) : 聲明伺服器的執行堆疊的最大安全深度。 為此設置一個參數的原因是內核強制的實際堆疊尺寸(就是ulimit -s或者局部等效物的設置) 小於安全的一百萬位元組左右的範圍。

    • 需要這個安全界限是因為在伺服器裡，並非所有過程都檢查了堆疊深度，只是在可能遞規的過程，比如運算式計算這樣的過程裡面才進行檢查。
    • 缺省設置是2MB， 這個值相對比較小，不容易導致崩潰。但是，這個值可能太小了，以至於無法執行複雜的函數。
    • 把max_stack_depth參數設置得大於實際的內核限制意味著 一個正在運行的遞迴函數可能會導致一個獨立的伺服器進程的崩潰。
    • 在PostgreSQL能夠檢測內核限制的平臺上， 伺服器將不允許你將其設置為一個不安全的值。 因為並非所有平臺都能夠檢測，所以還是建議你在此設置一個明確的值。

  • dynamic_shared_memory_type (enum) : 聲明伺服器應該使用的動態共用記憶體實現。

    • 可能的值有posix （對於使用shm_open分配的POSIX共用記憶體）、sysv （對於通過shmget分配的System V共用記憶體）、windows （對於Windows共用記憶體）、mmap （對於使用存儲在資料目錄中的記憶體映射檔的模仿共用記憶體）和none （禁用此功能）。
    • 並不是在所有平臺上都支持所有的值；第一個支援的選項是平臺的預設值。
    • 通常不推薦使用mmap選項，它不是任何平臺的缺省值， 因為作業系統可能會反復的將修改了的頁面寫到磁片，增加系統I/O負載； 不過，在pg_dynshmem目錄存放在RAM磁片上時， 或者其他共用記憶體工具不可用時，用它來調試是不錯的。

硬碟

  • temp_file_limit (integer) : 指定會話可以使用暫存檔案的最大磁碟空間，如排序和雜湊暫存檔案，或持有游標的存儲檔。
    • 一個事務試圖超過這個限制將被取消。該值是指定的千位元組，並且-1（缺省）意味著沒有限制。 只有超級用戶可以更改此設置。
    • 此設置限制任何時刻通過暫存檔案使用給定PostgreSQL會話使用的總空間。 應當指出的是，使用顯式臨時表的磁碟空間， 而不是使用查詢執行的幕後暫存檔案， 並強調不影響這個限制。

內核資源的使用

  • max_files_per_process (integer) : 設置每個伺服器進程允許同時打開的最大檔數目。缺省是1000。
    • 如果內核強制一個合理的每進程限制，那麼你不用操心這個設置。
    • 但是在一些平臺上(特別是大多數BSD系統)， 內核允許獨立進程打開比個系統真正可以支援的數目大得多得檔數。 如果你發現有"Too many open files"這樣的失敗現像，那麼就嘗試縮小這個設置。 這個值只能在伺服器啟動的時候設置。

基於開銷的清理延遲

  • 在VACUUM和ANALYZE命令執行過程中，系統維護一個內部的計數器，跟蹤所執行的各種I/O操作的近似開銷。 如果累積的開銷達到了vacuum_cost_limit聲明的限制， 那麼執行這個操作的進程將睡眠vacuum_cost_delay指定的時間。 然後它會重置記數器然後繼續執行。
  • 這個特性的目的是允許管理員減少這些命令在併發活動的資料庫上的I/O影響。 比如，像VACUUM和ANALYZE這樣的維護命令並不需要迅速完成， 並且不希望它們嚴重干擾系統執行其它的資料庫操作。 基於開銷的清理延遲為管理員提供了一個實現這個目的的手段。

  • 這個特性缺省手動發出VACUUM命令是關閉的。要想打開它，把vacuum_cost_delay變數設置為一個非零值。

  • vacuum_cost_delay (integer) : 以毫秒計的時間長度，如果超過了開銷限制，那麼進程將睡眠一會兒。 缺省值0關閉基於開銷的清理延遲特性。正數值打開基於開銷的清理。

    • 不過，要注意在許多系統上，睡眠的有效解析度是10毫秒；把vacuum_cost_delay設置為一個不是10的整數倍的數值與 將它設置為下一個10的整數倍作用相同。
    • 當使用基於成本的清理，vacuum_cost_delay的適當值通常是相當小的，也許10或20毫秒。調節清理的資源消耗最好是通過改變其它清理開銷參數完成的。

  • vacuum_cost_page_hit (integer) : 清理一個在共用緩存裡找到的緩衝區的預計開銷。 它代表鎖住緩衝集區、查找共用的Hash表、掃描頁面內容的開銷。 缺省值是1。

  • vacuum_cost_page_miss (integer) : 清理一個要從磁片上讀取的緩衝區的預計開銷。 它代表鎖住緩衝集區、查找共用Hash表、從磁片讀取需要的資料塊、 掃描它的內容的開銷。缺省值是10。

  • vacuum_cost_page_dirty (integer) : 清理修改一個原先是乾淨的塊的預計開銷。 它代表把一個髒的磁片塊再次刷新到磁片上的額外開銷。 缺省值是20。

  • vacuum_cost_limit (integer) : 導致清理進程休眠的積累開銷。缺省是200。

後端寫進程

  • 從 PostgreSQL 8.0 開始，就有一個獨立的伺服器進程，叫做後端寫進程， 它唯一的功能就是發出寫"髒"共用緩衝區的命令。

    • 這麼做的目的是讓持有使用者查詢的伺服器進程應該很少或者幾乎不等待寫動作的發生， 因為後端寫進程會做這件事情。這樣的安排同樣也減少了檢查點造成的性能下降。
    • 後端寫進程將持續的把髒頁面刷新到磁片上，所以在檢查點到來的時候，只有幾個頁面需要刷新到磁片上。 但是這樣還是增加了 I/O 的總淨負荷，因為以前的檢查點間隔裡，一個重複弄髒的頁面可能只會沖刷一次， 而同一個間隔裡，後端寫進程可能會寫好幾次。
    • 在大多數情況下，連續的低負荷要比週期性的尖峰負荷好，但是在本節討論的參數可以用於按實際需要調節其行為。

  • bgwriter_delay (integer) : 聲明後端寫進程活躍輪回之間的延遲。在每個輪回裡，寫進程都會為一些髒的緩衝區發出寫操作(可以用下面的參數控制)。 然後它就休眠bgwriter_delay毫秒，然後重複動作。

    • 當在緩衝集區中沒有髒緩衝區時，但是，它會無論bgwriter_delay的值，進入一個較長的睡眠。 缺省值是200(200ms)。
    • 請注意在許多系統上，休眠延時的有效解析度是10毫秒； 因此，把bgwriter_delay設置為一個不是10的倍數的數值與設置為下一個10的倍數是一樣的效果。
    • 這個選項只能在伺服器啟動的時候或者在postgresql.conf檔裡設置。

  • bgwriter_lru_maxpages (integer) : 在每個輪回裡， 不超過這麼多個緩衝區將通過後端寫進程寫入磁片。

    • 設置為零啟動後端寫進程。（請注意檢查點，通過單獨的，專用輔助進程來管理，不受影響。） 缺省值是100。
    • 這個選項只能在伺服器命令列或者在postgresql.conf檔裡設置。

  • bgwriter_lru_multiplier (floating point) :寫在每一輪的髒緩衝區數目是根據通過最近幾輪伺服器處理所需的新的緩衝區數。 +最近平均需求乘以bgwriter_lru_multiplier到達將在下一輪中需要的緩衝區的數目的估計。 髒緩衝區寫入直到有許多乾淨的，可重複使用的緩衝區可用。 （但是，每回寫入不超過bgwriter_lru_maxpages的緩衝區。） 因此，1.0的設置表示寫入確切預測需要的緩衝區數量的"合適"策略。

    • 較大的值提供針對需求高峰一定的緩衝作用， 而較小的值故意留下伺服器進程完成寫入。 預設值是2.0。 這個參數只能在postgresql.conf檔或者伺服器命令列中設置。 小的bgwriter_lru_maxpages和 bgwriter_lru_multiplier減少後端寫進程導致的額外I/O負荷， 但是會有可能使伺服器進程不得不自己發出寫動作，降低查詢的交互性。

Asynchronous Behavior

  • effective_io_concurrency (integer) : 設置PostgreSQL預計可以同時執行的併發磁片的I/O運算元。

    • 增大該數值將增加任何單獨的PostgreSQL會話嘗試並行啟動的I/O運算元。 允許的範圍是1到1000，或者零禁用發出非同步I/O請求。目前，此設置只影響堆點陣圖掃描。

  • max_worker_processes (integer) : 設置系統可以支援的最大後端進程數量。 這個參數只能在伺服器啟動時設置。

    • 當運行備用伺服器時，這個參數值的設置必須等於或大於主要伺服器。 否則，將不允許在備用伺服器上查詢。

預寫式日誌

  • wal_level (enum) : wal_level決定有多少資訊被寫入到WAL中。 預設值是最小的，其中寫入唯一從崩潰或立即關機中恢復的所需資訊。

    • archive補充WAL歸檔需要的日誌記錄；
    • hot_standby進一步增加在備用伺服器上運行唯讀查詢所需的資訊；
    • 最終logical增加支援邏輯編碼所必需的資訊。
    • 每個級別都包括所有更低級別記錄的資訊。這個參數只能在伺服器啟動時設置。
    • fsync (boolean) : 如果打開這個選項，那麼PostgreSQL伺服器將在好幾個地方使用fsync()系統調用 (或等價調用，參見wal_sync_method)來確保更新已經物理上寫到磁片中。 這樣就保證了資料庫集群將在作業系統或者硬體崩潰的情況下恢復到一個一致的狀態。
      • 當關閉fsync時通常是性能利益，這可能會導致發生斷電或系統崩潰時不可恢復資料丟失。 如果你可以很容易的從外部資料中創建您的整個資料庫， 因此關閉fsync是明智的。
      • 關閉fsync安全情況的例子包括備份檔案中新的資料庫集群的初始載入，資料庫被丟棄和重新創建之後，使用資料庫集群批次處理資料，或者為唯讀資料庫克隆被頻繁重建並且不用於容錯移轉。為了關閉fsync高品質硬體本身是不充分的。
      • 當改變fsync從關閉到打開時，對於可靠的恢復，必須強制內核中所有被修改的緩衝區到持久存儲。當集群宕機或當 + fsync通過運行initdb--sync-only，sync， 卸載檔案系統，或重新啟動伺服器的時候，再完成這項工作。
      • 在許多情況下，關閉synchronous_commit 為非關鍵的事務可以提供關閉fsync的潛力性能優勢， 沒有資料損壞隨之而來的風險。
      • fsync只能在postgresql.conf檔裡或者伺服器命令列裡設置。 如果這個參數被關閉，那麼請考慮把full_page_writes也關閉了。

  • synchronous_commit (enum) : 命令返回"成功"指示給用戶端之前，指定是否事務提交將等待WAL記錄被寫入到磁片。 有效值是on,remote_write, local和off。

    • 預設情況下，安全設置是on。 當off時，當成功報告給用戶端， 並當該事務真正保證是安全的，不會在伺服器崩潰的時候，可以有一定的延時（最大 延遲wal_writer_delay的3倍）。 不同於fsync，將此參數設置為off不會產生任何資料庫不一致的風險： 作業系統或資料庫崩潰可能導致丟失一些最近提交的事務， 但資料庫狀態將是一樣的，正如該事務已經徹底終止。 因此，當性能比準確事務耐久性更重要時，關閉synchronous_commit是有效選擇。

  • wal_sync_method (enum) : 用來向磁片強制更新WAL資料的方法。如果fsync是關閉的， 那麼這個設置就沒有意義，因為所有WAL檔更新都不會強制輸出。 可能的值是：

    • open_datasync (用帶 O_DSYNC選項的open()打開WAL文件)
    • fdatasync(每次提交的時候都調用fdatasync())
    • fsync (每次提交的時候都調用fsync())
    • fsync_writethrough (每次提交的時候調用fsync()強制寫出任何磁片寫緩衝區)
    • open_sync(用帶O_SYNC選項的open()寫WAL文件)
    • 這個參數只能在postgresql.conf檔或伺服器命令列上設置。

  • full_page_writes (boolean) : 打開這個選項的時候，PostgreSQL伺服器在檢查點之後對頁面的第一次寫入時將整個頁面寫到 WAL 裡面。 這麼做是因為在作業系統崩潰過程中可能只有部分頁面寫入磁片， 從而導致在同一個頁面中包含新舊資料的混合。在崩潰後的恢復期間， 由於在WAL裡面存儲的行變化資訊不夠完整，因此無法完全恢復該頁。 把完整的頁面影像保存下來就可以保證正確存儲頁面， 代價是增加了寫入WAL的資料量。因為WAL重放總是從一個檢查點開始的， 所以在檢查點後每個頁面第一次改變的時候做WAL備份就足夠了。 因此，一個減小全頁面寫開銷的方法是增加檢查點的間隔參數值。

    • 把這個選項關閉會加快正常操作的速度， 但是可能導致系統崩潰後不可恢復的資料損壞或者無記載資料損壞， 它的危害類似於fsync，只是比較小而已。並且在建議參數相同的情況下關閉這個選項。
    • 關閉這個選項並不影響即時恢復(PITR)的WAL使用。
    • 這個選項只能在postgresql.conf檔裡或者伺服器命令列設置。缺省是on。

  • wal_log_hints (boolean) : 當這個參數是on時，PostgreSQL 伺服器在檢查點後第一次修改該頁面期間，將每個磁片頁的全部內容都寫入到WAL中， 即使沒有所謂的提示資訊的關鍵修改。

    • 如果啟用了資料校驗和，那麼總是WAL記錄提示資訊的更新，並且忽略此設置。 你可以使用這個設置測試如果你的資料庫啟用了資料校驗和， 那麼會有多少額外的WAL記錄發生。
    • 這個參數只能在伺服器啟動時設置。缺省值是off。

  • wal_buffers (integer) : 使用已經寫入磁片的WAL資料共用記憶體的數量。-1的默認設置選擇大小等於 shared_buffers的1/32nd(大約3%)， 但不小於64kB也不超過一個WAL段大小， 通常16MB。如果自動選擇過大或過小，則可以手動設置這個值。 但任何小於32kB的正值將 當作32kB處理。 這個參數只能在伺服器啟動時設置。

    • WAL緩衝區的內容每次事務提交時寫入到磁片， 這樣非常大的值不可能提供顯著的好處。但是，當有許多用戶端都同時提交時，設置該值至少為幾兆可以提高繁忙伺服器的寫入性能，在多數情況下，由-1的缺省設置選擇自動調整應給予合理的結果。

  • wal_writer_delay (integer) : 聲明WAL寫入進程的活動輪回的延遲。在每一輪回中寫入進程將刷新WAL到磁片。 然後，它會休眠wal_writer_delay毫秒，並重複。 預設值是200毫秒(200ms)。

    • 注意， 在許多系統上，睡眠延遲的有效解析度為10毫秒; 把wal_writer_delay的值 設置為一個不是10的倍數的數值與設置為下一個10的倍數是一樣的效果。 這個參數只能在postgresql.conf檔或伺服器命令列上設置。

  • commit_delay (integer) : commit_delay增加了時間延遲，在WAL刷新啟動之前，以微秒測量。 這可以提高通過單一WAL刷新提交大量事務的組提交輸送量。 如果系統負載足夠高，額外事務在給定時間間隔內成為提交。 然而，它也增加了每個WAL刷新的最多commit_delay微秒的延遲時間。

    • 但是如果沒有其它事務準備提交，那麼這個間隔就是在浪費時間。 如果至少commit_siblings個其它事務是活躍的，當刷新初始化的情況下。 則僅僅只執行一個延遲。 如果禁用fsync，則不執行任何延遲。 缺省commit_delay是零（無延遲）。只有超級用戶可以更改此設置。
    • 在執行commit_delay延遲的時候，要求最少同時打開的併發事務數目。 大一些的數值會導致在延遲期間另外一個事務準備好提交的可能性增大。 缺省是5。

檢查點

  • checkpoint_segments (integer) : 在自動的WAL檢查點之間的日誌檔段的最大數量(通常每個段16百萬位元組)。 缺省是3。增加這個參數可以增加崩潰恢復所需要的時間量。 這個選項只能在postgresql.conf檔裡或者伺服器命令列中設置。

  • checkpoint_timeout (integer) : 在自動WAL檢查點之間的最長時間，以秒計。缺省是5分鐘(5min)。 增加這個參數可以增加崩潰恢復所需要的時間量。 這個選項只能在postgresql.conf檔或者伺服器命令列中設置。

  • checkpoint_completion_target (floating point) : 聲明檢查點完成的目標，作為檢查點之間總時間的分數。缺省是0.5。 這個參數只能在postgresql.conf檔中或者伺服器命令列中設置。

  • checkpoint_warning (integer) : 如果由於填充檢查點段檔導致的檢查點發生時間間隔接近這個參數表示的秒數， 那麼就向伺服器日誌發送一個建議增加checkpoint_segments值的消息。 缺省是30秒(30s)。零則關閉警告。 如果checkpoint_timeout小於checkpoint_warning， 則不產生警告。這個選項只能在postgresql.conf檔裡或者伺服器命令列中設置。

歸檔

  • archive_mode (boolean) : 啟用archive_mode的時候， 已完成的WAL段通過設置archive_command 發送到歸檔存儲。 archive_mode和archive_command是獨立變數， 以致於archive_command不留歸檔模式而被改變。 這個參數只能在伺服器啟動時設置。 當wal_level設置為minimal時，則不啟用archive_mode。

  • archive_command (string) : 將一個完整的WAL檔序列歸檔的本地shell命令。 字串中任何%p都被要歸檔的檔的絕對路徑代替， 而任何%f都只被該檔案名代替(非絕對路徑都相對于集群的資料目錄)。 如果你需要在命令裡嵌入%字元就必須雙寫%%。 有一點很重要：這個命令必須是當且僅當成功的時候才返回零。

    • 這個參數只能在postgresql.conf檔或伺服器命令列上。 除非在伺服器啟動時開啟archive_mode，否則忽略它。 如果archive_command是一個空字串（缺省）而archive_mode已啟用，暫時禁用WAL歸檔， 但是伺服器仍繼續堆積WAL段檔期望迅速提供一個命令。 設置archive_command命令什麼也不做但返回true /bin/true (Windows上的REM)，有效地禁用歸檔， 但也為了歸檔恢復打破了所需WAL檔鏈，所以應該只在特殊情況下使用。

  • archive_timeout (integer) : archive_command僅在已完成的WAL段上調用。 因此，如果伺服器只產生很少的WAL流量(或產生流量的週期很長)， 那麼在完成事務以及安全歸檔存儲之間將有一個很長的延時。 為了限制未歸檔資料的逗留時間， 你可以強制伺服器以archive_timeout指定的秒數為週期切換到新的WAL段檔。

    • 當這個參數大於零時，伺服器將切換到新的段檔，無論從剩餘段檔切換過去多少秒， 並且有任何的資料庫活動，包含一個單獨的檢查點（增加checkpoint_timeout 將減少空閒系統上不必要的檢查）。
    • 注意，由於強制切換而提早關閉的歸檔檔仍然與完整的歸檔檔長度相同。 因此，將archive_timeout —設為很小的值是不明智的，它將導致佔用巨大的歸檔存儲空間。 將archive_timeout設置為60秒左右是比較合理的。 如果你想要拷貝主要伺服器資料更加快速，你應該考慮使用流複製，而不是歸檔。
    • 這個選項只能在postgresql.conf檔或者伺服器命令列裡設置。

Locale

  • 區域支援指的是應用中考慮字母、排序、數值格式化等與文化相關的問題。 PostgreSQL使用伺服器作業系統提供的標準ISO C 和POSIX區域機制。

  • 區域支援是在使用initdb創建一個資料庫集群的時候自動初始化的。 缺省時，initdb將會按照它的執行環境的區域設置初始化資料庫集群； 因此如果你的系統已經設置為你的資料庫集群想要的區域，那麼你就沒有什麼可幹的了。

    • 如果你想使用其它的區域(或者你還不知道你的系統設置的區域是什麼)， 那麼你可以用--locale命令列選項告訴initdb你需要的區域究竟是哪個。

  • 你的系統裡有哪些可用的區域設置，它們的名字是什麼， 這些資訊都取決於你的作業系統提供商提供了什麼以及你安裝了什麼東西。 在大多數系統上，命令 locale -a 將提供所有可用區域的一個清單。

  • 有時候，把幾種區域規則混合起來也很有用，比如，使用英語排序規則而用西班牙語消息。為了支援這些， 我們有一套區域子範疇用於控制區域規則的某一方面：

名稱 說明
LC_COLLATE 字串排序順序
LC_CTYPE 字元分類(什麼是字母?是否區分大小寫?)
LC_MESSAGES 消息的語言
LC_MONETARY 貨幣金額的格式
LC_NUMERIC 數值格式
LC_TIME 日期和時間格式

  • 這些範疇名轉換成initdb選項的名字以覆蓋某個特定範疇的區域選擇。 比如，要把區域設置為加拿大法語，但使用美國的貨幣格式化規則， 可以使用initdb --locale=fr_CA --lc-monetary=en_US。

    • 如果你想要你的系統表現得像沒有區域支援一樣，那麼使用特殊的區域C或POSIX。

  • 一些區域範疇的值必須在創建資料庫時固定下來。 您可以對不同的資料庫使用不同的設置，但一旦創建一個資料庫，你就再也不能更改它們了。 LC_COLLATE和LC_CTYPE就是這樣的範疇。 它們影響索引的排序順序，因此它們必需保持固定，否則在文本欄位上的索引將會崩潰。

locale影響範圍

  • 區域設置特別影響下面的 SQL 特性：
    • 查詢中使用ORDER BY或者對文本資料的標準比較操作符進行排序
    • upper, lower和initcap函數
    • 模式匹配運算子(LIKE, SIMILAR TO, 以及POSIX-風格的規則運算式)； 區域影響大小寫不敏感的匹配和通過字元分類規則運算式的字元分類。
    • to_char函數族
    • 使用LIKE子句的索引能力
  • PostgreSQL裡使用非C或者POSIX區域的缺點是性能影響。 它降低了字元處理的速度並阻止了在LIKE類查詢裡面普通索引的使用。 因此，應該只有在你實際上需要的時候才使用它。

charset支援

  • PostgreSQL中的字元集支援可以讓你以各種字元集存儲文本（也稱為編碼）， 包含單一位元組字元集，比如ISO-8859系列和多位元組字元集比如EUC(擴展Unix編碼)、 UTF-8、Mule國際編碼。

    • 所有字元集都可以被用戶端透明地使用。但是有一些不支持在伺服器上使用（即作為伺服器端編碼）。
    • 缺省的字元集是在使用initdb初始化資料庫集群的時候選擇的。 在你創建資料庫的時候是可以覆蓋這個缺省值的。因此，你可以有多個資料庫， 每個都有不同的字元集。

  • 支援的字元集列表

    • 通常我們使用utf-8支援。

  • 你可以在資料庫創建時指定非缺省編碼，但是指定的編碼必須與所選的區域相相容：

CREATE DATABASE korean WITH ENCODING 'EUC_KR' LC_COLLATE='ko_KR.euckr' LC_CTYPE='ko_KR.euckr' TEMPLATE=template0;

  • 資料庫的編碼是存儲在pg_database系統表中的。 你可以用psql的-l選項或\l 命令列出這些編碼。

  • PostgreSQL支援在伺服器和前端之間的自動編碼轉換。 轉換資訊在系統表pg_conversion中存儲。 PostgreSQL帶著一些預定義的轉換。

    • 伺服器端使用utf8時，可使用所有的轉換

  • 要想打開自動字元集轉換功能，你必須告訴PostgreSQL 你想在用戶端使用的字元集(編碼)。 你可以用好幾種方法實現這個目的。

    • 用psql裡的\encoding命令。 \encoding允許你動態修改用戶端編碼。 比如，把編碼改變為SJIS，鍵入： \encoding SJIS
    • 使用libpq 函數控制用戶端編碼。
    • 使用下面的SQL命令設置用戶端編碼: SET CLIENT_ENCODING TO 'value';
    • 使用標準的SQL語法SET NAMES達到這個目的: SET NAMES 'value';
    • 查詢當前用戶端編碼： SHOW client_encoding;
    • 使用PGCLIENTENCODING。如果在用戶端的環境裡定義了 PGCLIENTENCODING環境變數，那麼在與伺服器進行連接時將自動選擇這個用戶端編碼。 這個編碼隨後可以用上面談到的任何其它方法覆蓋。

參考資料

results matching ""

    No results matching ""